Date: 2014-09-04 11:26:30 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: -- development

Last updated: 2014-10-31 14:13:37 +0100

Comment 20123

Date: 2014-09-04 11:26:30 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Firefox/31.0
Build Identifier:

Running the query below, it fails with the error:

ERROR = !GDK reported error.
!BATproject: does not match always

But maybe once every ~20 attempts it succeeds!

Tested with the latest tip of Mercurial.

It fails with all the optimizers and with either nthreads=1 or nthreads>1.

Reproducible: Sometimes

Steps to Reproduce:

sql>\d test
CREATE TABLE "sys"."test" (
"zs" INTEGER,
"zd" INTEGER,
"ns" BIGINT,
"bs" BIGINT
);

sql>select count(*) from test;
+---------+
| L1 |
+=========+
| 1399341 |
+---------+
1 tuple (0.790ms)

Run the following query:

SELECT zs, zd, sum(bs) as bs, sum(ns) AS ns
FROM (
SELECT sum(bs) AS bs, sum(ns) AS ns, zd, zs
FROM test GROUP BY zd, zs
) AS query
GROUP BY zd, zs HAVING sum(ns) <> 0
ORDER BY bs, zd, zs DESC
LIMIT 30;

Actually, I tried to bisect to find the minimal length of the table that cause this bug, and it's around 300k rows (close to 297k, but I suspect it's highly dependant of my configuration). Under this size, the query runs fine. Above, it fails most of the time.

Comment 20124

Date: 2014-09-04 11:29:26 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

To clarify, because I think the reported version can't be set to Oct2014, this bug DOESN'T occur with Jan2014-SP3.

Comment 20125

Date: 2014-09-04 11:54:19 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

The EXPLAIN for the query:

function user.s2_2{autoCommit=true}(A0:lng):void;
X_3 := sql.mvc();
X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","test");
X_7 := sql.bind(X_3,"sys","test","ns",0);
(X_10,r1_10) := sql.bind(X_3,"sys","test","ns",2);
X_13 := sql.bind(X_3,"sys","test","ns",1);
X_15 := sql.delta(X_7,X_10,r1_10,X_13);
X_16 := algebra.leftfetchjoin(X_4,X_15);
X_17 := sql.bind(X_3,"sys","test","zs",0);
(X_19,r1_19) := sql.bind(X_3,"sys","test","zs",2);
X_21 := sql.bind(X_3,"sys","test","zs",1);
X_22 := sql.delta(X_17,X_19,r1_19,X_21);
X_23 := algebra.leftfetchjoin(X_4,X_22);
X_24 := sql.bind(X_3,"sys","test","zd",0);
(X_26,r1_26) := sql.bind(X_3,"sys","test","zd",2);
X_28 := sql.bind(X_3,"sys","test","zd",1);
X_29 := sql.delta(X_24,X_26,r1_26,X_28);
X_30 := algebra.leftfetchjoin(X_4,X_29);
(X_31,r1_31,r2_31) := group.subgroup(X_30);
(X_34,r1_34,r2_34) := group.subgroupdone(X_23,X_31);
X_37:bat[:oid,:lng] := aggr.subsum(X_16,X_34,r1_34,true,true);
X_39 := algebra.leftfetchjoin(r1_34,X_23);
X_40 := algebra.leftfetchjoin(r1_34,X_30);
X_51 := sql.bind(X_3,"sys","test","bs",0);
(X_56,r1_59) := sql.bind(X_3,"sys","test","bs",2);
X_59 := sql.bind(X_3,"sys","test","bs",1);
X_61 := sql.delta(X_51,X_56,r1_59,X_59);
X_62 := algebra.leftfetchjoin(X_4,X_61);
X_63:bat[:oid,:lng] := aggr.subsum(X_62,X_34,r1_34,true,true);
(X_41,r1_41,r2_41) := group.subgroup(X_40);
(X_44,r1_44,r2_44) := group.subgroupdone(X_39,X_41);
X_47:bat[:oid,:lng] := aggr.subsum(X_37,X_44,r1_44,true,true);
X_48 := algebra.subselect(X_47,A0,A0,true,true,true);
X_132 := algebra.leftfetchjoin(X_48,r1_44);
X_64:bat[:oid,:lng] := aggr.subsum(X_63,X_44,r1_44,true,true);
X_49:bat[:oid,:int] := algebra.leftfetchjoin(X_132,X_39);
X_50:bat[:oid,:int] := algebra.leftfetchjoin(X_132,X_40);
X_65 := algebra.leftfetchjoin(X_48,X_64);
(X_67,r1_74) := algebra.firstn(X_65,30:wrd,true,false);
(X_71,r1_80) := algebra.firstn(X_50,X_67,r1_74,30:wrd,true,false);
X_73 := algebra.firstn(X_49,X_71,r1_80,30:wrd,false,false);
X_74 := algebra.leftfetchjoin(X_73,X_49);
X_75 := algebra.leftfetchjoin(X_73,X_50);
X_76 := algebra.leftfetchjoin(X_73,X_65);
(X_77,r1_88,r2_88) := algebra.subsort(X_76,false,false);
(X_80,r1_91,r2_91) := algebra.subsort(X_75,r1_88,r2_88,false,false);
(X_83,r1_94,r2_94) := algebra.subsort(X_74,r1_91,r2_91,true,false);
X_86 := algebra.leftfetchjoin(r1_94,X_74);
X_88 := algebra.subslice(X_86,0:wrd,29:wrd);
X_89 := algebra.leftfetchjoin(X_88,X_86);
X_133 := algebra.leftfetchjoin(X_88,r1_94);
X_92:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_133,X_73,X_48,X_47);
X_91:bat[:oid,:lng] := algebra.leftfetchjoin(X_133,X_76);
X_90:bat[:oid,:int] := algebra.leftfetchjoin(X_133,X_75);
X_93 := sql.resultSet(4,1,X_89);
sql.rsColumn(X_93,"sys.query","zs","int",32,0,X_89);
sql.rsColumn(X_93,"sys.query","zd","int",32,0,X_90);
sql.rsColumn(X_93,"sys.L3","bs","bigint",64,0,X_91);
sql.rsColumn(X_93,"sys.L4","ns","bigint",64,0,X_92);
X_111 := io.stdout();
sql.exportResult(X_111,X_93);
end s2_2;

Comment 20126

Date: 2014-09-04 13:32:33 +0200
From: @sjoerdmullender

Given that the bug only occurs for larger number of rows, it may be it only triggers when mitosis/mergetable is in effect. You can try to reproduce the problem with a smaller number of rows but running the server with the --forcemito flag to force mitosis even for small sizes.

Comment 20127

Date: 2014-09-04 14:18:15 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

(In reply to comment 3)

Given that the bug only occurs for larger number of rows, it may be it only
triggers when mitosis/mergetable is in effect. You can try to reproduce the
problem with a smaller number of rows but running the server with the
--forcemito flag to force mitosis even for small sizes.

I'm a little confused.

Indeed, after trying with --forcemito, I noticed that up to ~2240 rows, the query succeed, and fails above that (so it is worse now.) But when I remove the option, it still fail at this limit (while it failed at around 300k as I reported earlier.) I'm assuming that some metadata have changed when trying this option.

Each time, I tests all the pipes (default, no_mitosis, minimal, sequential) and they either all passes or all fails.

Note: I never launch mserver5 directly. I don't know how to add extra options when started from monetdbd, so I renamed bin/mserver5 to bin/mserver5.bin and added a script as bin/server5 which run mserver5.bin with the extra --forcemito option.

Comment 20128

Date: 2014-09-04 14:20:44 +0200
From: @sjoerdmullender

I'm now looking into the bug. I can reproduce it, so that is good.
I don't think it actually has to do anything with mitosis, but with the new algebra.firstn implementation (see your trace, it occurs three times).

Comment 20129

Date: 2014-09-04 15:48:37 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=c4e0e31888f9

Changeset description:

Fix for bug #3560: last value in grouped first-N isn't necessarily highest.

Comment 20130

Date: 2014-09-04 15:49:45 +0200
From: @sjoerdmullender

Frédéric, can you try with the latest change? I think that should solve it.

Comment 20133

Date: 2014-09-04 16:57:20 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

(In reply to comment 7)

Frédéric, can you try with the latest change? I think that should solve it.

I confirm. It works perfectly now, with the same data. Thanks for the very quick fix!

Comment 20330

Date: 2014-10-31 14:13:37 +0100
From: @sjoerdmullender

Oct2014 has been released.