Query failures on order by on union

[monetdb-team]

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

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

Comment 25942

Date: 2017-12-04 12:23:38 +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 test table used during 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);

The following query

SELECT
"ctf_measure_1_name" AS "cup_measure_1_name" ,
"ctg_measure_2_name" AS "cuq_measure_2_name" ,
"cth_measure_1_value" AS "cur_measure_1_value",
"cti_measure_2_value" AS "cus_measure_2_value",
"ctj_record_count" AS "cut_record_count"
FROM
(
SELECT
"tun"."cui___if__0_appd_src_det_",
"tun"."ctf_measure_1_name" ,
"tun"."ctg_measure_2_name" ,
"tun"."cth_measure_1_value" ,
"tun"."cti_measure_2_value" ,
"tun"."ctj_record_count"
FROM
(
(
SELECT
0.0 AS "cui___if__0_appd_src_det_",
"tuj"."ctf_measure_1_name" AS "ctf_measure_1_name" ,
"tuj"."ctg_measure_2_name" AS "ctg_measure_2_name" ,
"tuj"."cth_measure_1_value" AS "cth_measure_1_value" ,
"tuj"."cti_measure_2_value" AS "cti_measure_2_value" ,
"tuj"."ctj_record_count" AS "ctj_record_count"
FROM
(
SELECT
'A (UNIQUE_VALUES_COUNT)' AS "ctf_measure_1_name" ,
'C (SUM)' AS "ctg_measure_2_name" ,
"cte_a__unique_values_count__2" AS "cth_measure_1_value",
"ctd_c__sum__2" AS "cti_measure_2_value",
"ctc_record_count" AS "ctj_record_count"
FROM
(
SELECT
COUNT(*) AS "ctc_record_count",
SUM("ttb"."C") AS "ctd_c__sum__2" ,
COUNT(DISTINCT "ttb"."A") AS "cte_a__unique_values_count__2"
FROM
(
SELECT
"tta"."A",
"tta"."B",
"tta"."C",
"tta"."D",
"tta"."id"
FROM
"unitTestDontDelete" AS "tta") AS "ttb") AS "ttk") AS "tuj")

             UNION ALL
                     (
                             SELECT
                                     1.0                                             AS "cui___if__0_appd_src_det_",
                                     "tuk"."ctq_measure_1_name"                      AS "ctf_measure_1_name"       ,
                                     "tuk"."ctr_measure_2_name"                      AS "ctg_measure_2_name"       ,
                                     "tuk"."cts_measure_1_value"                     AS "cth_measure_1_value"      ,
                                     CAST("ctt_measure_2_value" AS DOUBLE PRECISION) AS "cti_measure_2_value"      ,
                                     "tuk"."ctu_record_count"                        AS "ctj_record_count"
                             FROM
                                     (
                                             SELECT
                                                     'A (UNIQUE_VALUES_COUNT)'       AS "ctq_measure_1_name" ,
                                                     'RECORD_COUNT'                  AS "ctr_measure_2_name" ,
                                                     "ctp_a__unique_values_count__2" AS "cts_measure_1_value",
                                                     "cto_record_count_2"            AS "ctt_measure_2_value",
                                                     "ctn_record_count"              AS "ctu_record_count"
                                             FROM
                                                     (
                                                             SELECT
                                                                     COUNT(*)                  AS "ctn_record_count"  ,
                                                                     COUNT(*)                  AS "cto_record_count_2",
                                                                     COUNT(DISTINCT "ttm"."A") AS "ctp_a__unique_values_count__2"
                                                             FROM
                                                                     (
                                                                             SELECT
                                                                                     "ttl"."A",
                                                                                     "ttl"."B",
                                                                                     "ttl"."C",
                                                                                     "ttl"."D",
                                                                                     "ttl"."id"
                                                                             FROM
                                                                                     "unitTestDontDelete" AS "ttl") AS "ttm") AS "ttv") AS "tuk")
             
             ) AS "tun") AS "tuo"

ORDER BY CASE WHEN "ctf_measure_1_name" = 'A (UNIQUE_VALUES_COUNT)' THEN 0 WHEN "ctf_measure_1_name" = 'B (SUM)' THEN 1 WHEN "ctf_measure_1_name" IS NULL THEN 2 ELSE 3 END ASC,
"ctf_measure_1_name" ASC ,
"ctf_measure_1_name" ASC ,
CASE WHEN "ctg_measure_2_name" = 'C (SUM)' THEN 0 WHEN "ctg_measure_2_name" = 'RECORD_COUNT' THEN 1 WHEN "ctg_measure_2_name" IS NULL THEN 2 ELSE 3 END ASC ,
"ctg_measure_2_name" ASC ,
"ctg_measure_2_name" ASC ,
"cui___if__0_appd_src_det_" ASC limit 200000

fails with the error:

Error: could not allocate space
SQLState: HY001
ErrorCode: 0
Error: COLnew:tt error
SQLState: 22000
ErrorCode: 0

I have plenty of disk space and available ram (32GB)

Reproducible: Always

Steps to Reproduce:

1.Create the table unitTestDontDelete with the records in description
2. run the query in description

Actual Results:

Error: could not allocate space
SQLState: HY001
ErrorCode: 0
Error: COLnew:tt error
SQLState: 22000
ErrorCode: 0

Comment 25945

Date: 2017-12-04 16:02:26 +0100
From: @sjoerdmullender

Why did you close this bug? It's not been fixed.

Comment 25946

Date: 2017-12-04 16:46:50 +0100
From: Manuel <>

I realised it is caused by a duplicate column in the order by clause. Feel free to re-open it, if relevant.

thanks,

Manuel