Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Monetdb crashes, on query #6483

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Monetdb crashes, on query #6483

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working major SQL
Milestone
Ancient Release

Comments

@monetdb-team
Copy link

Date: 2017-12-04 13:13:26 +0100
From: Manuel <>
To: SQL devs <>
Version: 11.27.9 (Jul2017-SP2)

Last updated: 2018-02-12 16:12:20 +0100

Comment 25943

Date: 2017-12-04 13:13:26 +0100
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.94 Safari/537.36
Build Identifier:

This is the table used in my tests

CREATE TABLE "sys"."unitTestDontDelete" (
"A" VARCHAR(255),
"B" BIGINT,
"C" DOUBLE,
"D" TIMESTAMP,
"id" BIGINT NOT NULL,
CONSTRAINT ""unitTestDontDelete"_PK" PRIMARY KEY ("id")
);
INSERT INTO "sys"."unitTestDontDelete" VALUES (NULL, NULL, NULL, NULL, 0);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 0, 0.5, '2013-06-10 11:10:10.000000', 1);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 1, 1.5, '2013-06-11 12:11:11.000000', 2);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 2, 2.5, '2013-06-12 13:12:12.000000', 3);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 3, 3.5, '2013-06-13 14:13:13.000000', 4);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 4, 4.5, '2013-06-14 15:14:14.000000', 5);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 5, 5.5, '2013-06-15 16:15:15.000000', 6);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 6, 6.5, '2013-06-16 17:16:16.000000', 7);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat2', 7, 7.5, '2013-06-17 18:17:17.000000', 8);
INSERT INTO "sys"."unitTestDontDelete" VALUES ('Cat1', 8, 8.5, '2013-06-18 19:18:18.000000', 9);

running the following query will cause monetdb to crash. It does not happen all the time, but
It's easy to reproduce if the query is run multiple times in a short time.

SELECT
"cpt_field_name" AS "crx_field_name" ,
"cpu_field_value" AS "cry_field_value" ,
"cpv_measure_name" AS "crz_measure_name" ,
"cpw_measure_value" AS "cs0_measure_value",
"cpx_d__unique_values_count_" AS "cs1_d__unique_values_count_"
FROM
(
SELECT
"trv"."crq___if__12_appd_src_det_" ,
"trv"."cpt_field_name" ,
"trv"."cpu_field_value" ,
"trv"."cpv_measure_name" ,
"trv"."cpw_measure_value" ,
"trv"."cpx_d__unique_values_count_",
"trv"."cpy___if__0_field_value_" ,
"trv"."cpz___if__1_field_value_" ,
"trv"."cq0___if__2_field_value_" ,
"trv"."cqi___if__3_field_value_" ,
"trv"."cqj___if__4_field_value_" ,
"trv"."cqk___if__5_field_value_" ,
"trv"."cr2___if__6_field_value_" ,
"trv"."cr3___if__7_field_value_" ,
"trv"."cr4___if__8_field_value_" ,
"trv"."crm___if__9_field_value_" ,
"trv"."crn___if__10_field_value_" ,
"trv"."cro___if__11_field_value_"
FROM
(
(
SELECT
0.0 AS "crq___if__12_appd_src_det_" ,
"trr"."cpt_field_name" AS "cpt_field_name" ,
"trr"."cpu_field_value" AS "cpu_field_value" ,
"trr"."cpv_measure_name" AS "cpv_measure_name" ,
"trr"."cpw_measure_value" AS "cpw_measure_value" ,
"trr"."cpx_d__unique_values_count_" AS "cpx_d__unique_values_count_",
"trr"."cpy___if__0_field_value_" AS "cpy___if__0_field_value_" ,
"trr"."cpz___if__1_field_value_" AS "cpz___if__1_field_value_" ,
"trr"."cq0___if__2_field_value_" AS "cq0___if__2_field_value_" ,
CAST(NULL AS bigint) AS "cqi___if__3_field_value_" ,
CAST(NULL AS bigint) AS "cqj___if__4_field_value_" ,
CAST(NULL AS bigint) AS "cqk___if__5_field_value_" ,
CAST(NULL AS VARCHAR(255)) AS "cr2___if__6_field_value_" ,
CAST(NULL AS VARCHAR(255)) AS "cr3___if__7_field_value_" ,
CAST(NULL AS VARCHAR(255)) AS "cr4___if__8_field_value_" ,
CAST(NULL AS bigint) AS "crm___if__9_field_value_" ,
CAST(NULL AS bigint) AS "crn___if__10_field_value_" ,
CAST(NULL AS bigint) AS "cro___if__11_field_value_"
FROM
(
SELECT
"cpn_field_name" AS "cpt_field_name" ,
"cpo_field_value" AS "cpu_field_value" ,
"cpp_measure_name" AS "cpv_measure_name" ,
"cpq_measure_value" AS "cpw_measure_value" ,
"cpr_d__unique_values_count_" AS "cpx_d__unique_values_count_",
"cpo_field_value" AS "cpy___if__0_field_value_" ,
"cpo_field_value" AS "cpz___if__1_field_value_" ,
"cpo_field_value" AS "cq0___if__2_field_value_"
FROM
(
SELECT
'A' AS "cpn_field_name" ,
"cpk_a_2" AS "cpo_field_value" ,
'B (MEAN)' AS "cpp_measure_name" ,
"cpm_b__mean__2" AS "cpq_measure_value",
"cpl_d__unique_values_count_" AS "cpr_d__unique_values_count_"
FROM
(
SELECT
"tpj"."A" AS "cpk_a_2" ,
COUNT(DISTINCT "tpj"."D") AS "cpl_d__unique_values_count_",
AVG("tpj"."B") AS "cpm_b__mean__2"
FROM
(
SELECT
"tpi"."A",
"tpi"."B",
"tpi"."C",
"tpi"."D",
"tpi"."id"
FROM
"unitTestDontDelete" AS "tpi") AS "tpj"
GROUP BY
"tpj"."A") AS "tps") AS "tq1") AS "trr")

             UNION ALL
                     (
                             SELECT
                                     1.0                                 AS "crq___if__12_appd_src_det_" ,
                                     "trs"."cqd_field_name"              AS "cpt_field_name"             ,
                                     CAST("cqe_field_value" AS CLOB)     AS "cpu_field_value"            ,
                                     "trs"."cqf_measure_name"            AS "cpv_measure_name"           ,
                                     "trs"."cqg_measure_value"           AS "cpw_measure_value"          ,
                                     "trs"."cqh_d__unique_values_count_" AS "cpx_d__unique_values_count_",
                                     CAST(NULL AS VARCHAR(255))          AS "cpy___if__0_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cpz___if__1_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cq0___if__2_field_value_"   ,
                                     "trs"."cqi___if__3_field_value_"    AS "cqi___if__3_field_value_"   ,
                                     "trs"."cqj___if__4_field_value_"    AS "cqj___if__4_field_value_"   ,
                                     "trs"."cqk___if__5_field_value_"    AS "cqk___if__5_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cr2___if__6_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cr3___if__7_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cr4___if__8_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "crm___if__9_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "crn___if__10_field_value_"  ,
                                     CAST(NULL AS bigint)                AS "cro___if__11_field_value_"
                             FROM
                                     (
                                             SELECT
                                                     "cq7_field_name"              AS "cqd_field_name"             ,
                                                     "cq8_field_value"             AS "cqe_field_value"            ,
                                                     "cq9_measure_name"            AS "cqf_measure_name"           ,
                                                     "cqa_measure_value"           AS "cqg_measure_value"          ,
                                                     "cqb_d__unique_values_count_" AS "cqh_d__unique_values_count_",
                                                     "cq8_field_value"             AS "cqi___if__3_field_value_"   ,
                                                     "cq8_field_value"             AS "cqj___if__4_field_value_"   ,
                                                     "cq8_field_value"             AS "cqk___if__5_field_value_"
                                             FROM
                                                     (
                                                             SELECT
                                                                     'B'                           AS "cq7_field_name"   ,
                                                                     "cq4_b_2"                     AS "cq8_field_value"  ,
                                                                     'B (MEAN)'                    AS "cq9_measure_name" ,
                                                                     "cq6_b__mean__2"              AS "cqa_measure_value",
                                                                     "cq5_d__unique_values_count_" AS "cqb_d__unique_values_count_"
                                                             FROM
                                                                     (
                                                                             SELECT
                                                                                     "tq3"."B"                 AS "cq4_b_2"                    ,
                                                                                     COUNT(DISTINCT "tq3"."D") AS "cq5_d__unique_values_count_",
                                                                                     AVG("tq3"."B")            AS "cq6_b__mean__2"
                                                                             FROM
                                                                                     (
                                                                                             SELECT
                                                                                                     "tq2"."A",
                                                                                                     "tq2"."B",
                                                                                                     "tq2"."C",
                                                                                                     "tq2"."D",
                                                                                                     "tq2"."id"
                                                                                             FROM
                                                                                                     "unitTestDontDelete" AS "tq2") AS "tq3"
                                                                             GROUP BY
                                                                                     "tq3"."B") AS "tqc") AS "tql") AS "trs")
             
             UNION ALL
                     (
                             SELECT
                                     2.0                                 AS "crq___if__12_appd_src_det_" ,
                                     "trt"."cqx_field_name"              AS "cpt_field_name"             ,
                                     "trt"."cqy_field_value"             AS "cpu_field_value"            ,
                                     "trt"."cqz_measure_name"            AS "cpv_measure_name"           ,
                                     "trt"."cr0_measure_value"           AS "cpw_measure_value"          ,
                                     "trt"."cr1_d__unique_values_count_" AS "cpx_d__unique_values_count_",
                                     CAST(NULL AS VARCHAR(255))          AS "cpy___if__0_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cpz___if__1_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cq0___if__2_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "cqi___if__3_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "cqj___if__4_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "cqk___if__5_field_value_"   ,
                                     "trt"."cr2___if__6_field_value_"    AS "cr2___if__6_field_value_"   ,
                                     "trt"."cr3___if__7_field_value_"    AS "cr3___if__7_field_value_"   ,
                                     "trt"."cr4___if__8_field_value_"    AS "cr4___if__8_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "crm___if__9_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "crn___if__10_field_value_"  ,
                                     CAST(NULL AS bigint)                AS "cro___if__11_field_value_"
                             FROM
                                     (
                                             SELECT
                                                     "cqr_field_name"              AS "cqx_field_name"             ,
                                                     "cqs_field_value"             AS "cqy_field_value"            ,
                                                     "cqt_measure_name"            AS "cqz_measure_name"           ,
                                                     "cqu_measure_value"           AS "cr0_measure_value"          ,
                                                     "cqv_d__unique_values_count_" AS "cr1_d__unique_values_count_",
                                                     "cqs_field_value"             AS "cr2___if__6_field_value_"   ,
                                                     "cqs_field_value"             AS "cr3___if__7_field_value_"   ,
                                                     "cqs_field_value"             AS "cr4___if__8_field_value_"
                                             FROM
                                                     (
                                                             SELECT
                                                                     'A'                           AS "cqr_field_name"   ,
                                                                     "cqo_a_2"                     AS "cqs_field_value"  ,
                                                                     'RECORD_COUNT'                AS "cqt_measure_name" ,
                                                                     "cqq_record_count_2"          AS "cqu_measure_value",
                                                                     "cqp_d__unique_values_count_" AS "cqv_d__unique_values_count_"
                                                             FROM
                                                                     (
                                                                             SELECT
                                                                                     "tqn"."A"                 AS "cqo_a_2"                    ,
                                                                                     COUNT(DISTINCT "tqn"."D") AS "cqp_d__unique_values_count_",
                                                                                     COUNT(*)                  AS "cqq_record_count_2"
                                                                             FROM
                                                                                     (
                                                                                             SELECT
                                                                                                     "tqm"."A",
                                                                                                     "tqm"."B",
                                                                                                     "tqm"."C",
                                                                                                     "tqm"."D",
                                                                                                     "tqm"."id"
                                                                                             FROM
                                                                                                     "unitTestDontDelete" AS "tqm") AS "tqn"
                                                                             GROUP BY
                                                                                     "tqn"."A") AS "tqw") AS "tr5") AS "trt")
             
             UNION ALL
                     (
                             SELECT
                                     3.0                                 AS "crq___if__12_appd_src_det_" ,
                                     "tru"."crh_field_name"              AS "cpt_field_name"             ,
                                     CAST("cri_field_value" AS CLOB)     AS "cpu_field_value"            ,
                                     "tru"."crj_measure_name"            AS "cpv_measure_name"           ,
                                     "tru"."crk_measure_value"           AS "cpw_measure_value"          ,
                                     "tru"."crl_d__unique_values_count_" AS "cpx_d__unique_values_count_",
                                     CAST(NULL AS VARCHAR(255))          AS "cpy___if__0_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cpz___if__1_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cq0___if__2_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "cqi___if__3_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "cqj___if__4_field_value_"   ,
                                     CAST(NULL AS bigint)                AS "cqk___if__5_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cr2___if__6_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cr3___if__7_field_value_"   ,
                                     CAST(NULL AS VARCHAR(255))          AS "cr4___if__8_field_value_"   ,
                                     "tru"."crm___if__9_field_value_"    AS "crm___if__9_field_value_"   ,
                                     "tru"."crn___if__10_field_value_"   AS "crn___if__10_field_value_"  ,
                                     "tru"."cro___if__11_field_value_"   AS "cro___if__11_field_value_"
                             FROM
                                     (
                                             SELECT
                                                     "crb_field_name"              AS "crh_field_name"             ,
                                                     "crc_field_value"             AS "cri_field_value"            ,
                                                     "crd_measure_name"            AS "crj_measure_name"           ,
                                                     "cre_measure_value"           AS "crk_measure_value"          ,
                                                     "crf_d__unique_values_count_" AS "crl_d__unique_values_count_",
                                                     "crc_field_value"             AS "crm___if__9_field_value_"   ,
                                                     "crc_field_value"             AS "crn___if__10_field_value_"  ,
                                                     "crc_field_value"             AS "cro___if__11_field_value_"
                                             FROM
                                                     (
                                                             SELECT
                                                                     'B'                           AS "crb_field_name"   ,
                                                                     "cr8_b_2"                     AS "crc_field_value"  ,
                                                                     'RECORD_COUNT'                AS "crd_measure_name" ,
                                                                     "cra_record_count_2"          AS "cre_measure_value",
                                                                     "cr9_d__unique_values_count_" AS "crf_d__unique_values_count_"
                                                             FROM
                                                                     (
                                                                             SELECT
                                                                                     "tr7"."B"                 AS "cr8_b_2"                    ,
                                                                                     COUNT(DISTINCT "tr7"."D") AS "cr9_d__unique_values_count_",
                                                                                     COUNT(*)                  AS "cra_record_count_2"
                                                                             FROM
                                                                                     (
                                                                                             SELECT
                                                                                                     "tr6"."A",
                                                                                                     "tr6"."B",
                                                                                                     "tr6"."C",
                                                                                                     "tr6"."D",
                                                                                                     "tr6"."id"
                                                                                             FROM
                                                                                                     "unitTestDontDelete" AS "tr6") AS "tr7"
                                                                             GROUP BY
                                                                                     "tr7"."B") AS "trg") AS "trp") AS "tru")) AS "trv") AS "trw"

ORDER BY
"crq___if__12_appd_src_det_" ASC,
"cpy___if__0_field_value_" ASC ,
"cqi___if__3_field_value_" ASC ,
"cr2___if__6_field_value_" ASC ,
"crm___if__9_field_value_" ASC limit 200000

Reproducible: Always

Steps to Reproduce:

1.Import the table
2. Run the query (multiple times if necessary)

Comment 25944

Date: 2017-12-04 15:53:15 +0100
From: @sjoerdmullender

I can reproduce this with the Jul2017 SP2 release, but not with the upcoming SP3. I'm pretty sure the fix was in changeset 870aa0af7e1a
@monetdb-team monetdb-team added bug Something isn't working major SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working major SQL
Projects
None yet
Milestone
Ancient Release
Development

No branches or pull requests
2 participants
@sjoerdmullender @monetdb-team