Crash with nested order by/ limit offset

[monetdb-team]

Date: 2017-11-28 11:58:24 +0100
From: Manuel <>
To: SQL devs <>
Version: 11.27.9 (Jul2017-SP2)
CC: @njnes

Last updated: 2017-12-14 14:46:02 +0100

Comment 25921

Date: 2017-11-28 11:58:24 +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:

I have had several crashes when trying to execute queries involving nested order by/ limit offset.

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 crashes:

select "A" as "cb_a", "B" as "cc_b", "C" as "cd_c", "D" as "ce_d" from (
select "t8"."A", "t8"."B", "t8"."C", "t8"."D", "t8"."id" from (
select "t7"."A", "t7"."B", "t7"."C", "t7"."D", "t7"."id" from "unitTestDontDelete" as "t7"
) as "t8" order by "A" desc, "id" asc limit 4 offset 1
) as "ta" order by "B" asc, "A" desc, "id" asc limit 1

Reproducible: Always

Steps to Reproduce:

1. Import the table executing the sql in details
2. Execute the query:

select "A" as "cb_a", "B" as "cc_b", "C" as "cd_c", "D" as "ce_d" from (
select "t8"."A", "t8"."B", "t8"."C", "t8"."D", "t8"."id" from (
select "t7"."A", "t7"."B", "t7"."C", "t7"."D", "t7"."id" from "unitTestDontDelete" as "t7"
) as "t8" order by "A" desc, "id" asc limit 4 offset 1
) as "ta" order by "B" asc, "A" desc, "id" asc limit 1

Actual Results:

Monetdb crashes

Comment 25927

Date: 2017-11-29 16:11:32 +0100
From: MonetDB Mercurial Repository <>

Changeset 43eb145717a3 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=43eb145717a3

Changeset description:

add test for bug #6478

Comment 25929

Date: 2017-11-29 16:19:23 +0100
From: @njnes

fixed crash in firstn with distinct

Comment 25930

Date: 2017-11-29 18:55:05 +0100
From: MonetDB Mercurial Repository <>

Changeset 870aa0af7e1a made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=870aa0af7e1a

Changeset description:

Fixed some thinkos in BATfirstn_unique_with_groups.
- Make sure *lastp and *lastgp are assigned a value.
- Don't blindly assume there is a candidate list if there are groups.
- Copy correct bit of candidate list, or create a dense bat referring
  to correct part of input bat.
This fixes bug #6478.