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

Error "BATproject: does not match always" with subselect/groupby/having #3560

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

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant