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

"b and g must be aligned" from complex group/union query #3787

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

"b and g must be aligned" from complex group/union query #3787

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

Comments

@monetdb-team
Copy link

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

Date: 2015-08-12 17:44:10 +0200
From: Richard Hughes <<richard.monetdb>>
To: SQL devs <>
Version: 11.21.1 (Jul2015)
CC: @njnes

Last updated: 2015-08-28 13:41:23 +0200

Comment 21132

Date: 2015-08-12 17:44:10 +0200
From: Richard Hughes <<richard.monetdb>>

Build is Jul2015 8b38c01061c5

sql>create table foo (a smallint);
operation successful (18.292ms)

sql>select count(y),sum(z) from (select '',false,a=200 from foo union all select '',null,a=200 from foo) as t(x,y,z) group by x;
b and g must be aligned

sql>plan select count(y),sum(z) from (select '',false,a=200 from foo union all select '',null,a=200 from foo) as t(x,y,z) group by x;
+-----------------------------------------------------------------------------+
| rel |
+=============================================================================+
| project ( |
| | group by ( |
| | | union ( |
| | | | group by ( |
| | | | | project ( |
| | | | | | project ( |
| | | | | | | table(sys.foo) [ foo.a ] COUNT |
| | | | | | ) [ char "" as L1.L1, boolean "false" as L2.L2, sys.=(foo.a, smal |
: lint "200") as L5.L5 ] :
| | | | | ) [ L1.L1 as t.x, L2.L2 as t.y, L5.L5 as t.z ] |
| | | | ) [ t.x ] [ t.x, sys.count no nil (t.y) as L7.L7, sys.sum no nil (tin |
: yint[t.z] as t.z) as L10.L10 ], :
| | | | group by ( |
| | | | | project ( |
| | | | | | project ( |
| | | | | | | table(sys.foo) [ foo.a ] COUNT |
| | | | | | ) [ char "" as L3.L3, any "NULL" as L4.L4, sys.=(foo.a, smallint |
: "200") as L6.L6 ] :
| | | | | ) [ L3.L3 as t.x, boolean[L4.L4] as t.y, L6.L6 as t.z ] |
| | | | ) [ t.x ] [ t.x, sys.count no nil (t.y) as L7.L7, sys.sum no nil (tin |
: yint[t.z] as t.z) as L10.L10 ] :
| | | ) [ t.x, L7.L7, L10.L10 ] |
| | ) [ t.x ] [ t.x, sys.sum no nil (L7.L7) as L7.L7, sys.sum no nil (L10.L10 |
: ) as L10.L10 ] :
| ) [ L7 as L7.L7, L10 as L10.L10 ] |
+-----------------------------------------------------------------------------+
20 tuples (1.286ms)

sql>explain select count(y),sum(z) from (select '',false,a=200 from foo union all select '',null,a=200 from foo) as t(x,y,z) group by x;
+-----------------------------------------------------------------------------+
| mal |
+=============================================================================+
| function user.s15_1{autoCommit=true}(A0:str,A1:bit,A2:sht,A3:str,A4:void,A5 |
: :sht):void; :
| X_90:void := querylog.define("explain select count(y),sum(z) from (sele |
: ct \'\',false,a=200 from foo union all select \'\',null,a=200 from foo) :
: as t(x,y,z) group by x;","default_pipe",66); :
| barrier X_110 := language.dataflow(); |
| X_65 := bat.new(nil:oid,nil:str); |
| X_71 := bat.append(X_65,".L7"); |
| X_81 := bat.append(X_71,".L10"); |
| X_66 := bat.new(nil:oid,nil:str); |
| X_73 := bat.append(X_66,"L7"); |
| X_83 := bat.append(X_73,"L10"); |
| X_67 := bat.new(nil:oid,nil:str); |
| X_75 := bat.append(X_67,"wrd"); |
| X_85 := bat.append(X_75,"hugeint"); |
| X_68 := bat.new(nil:oid,nil:int); |
| X_77 := bat.append(X_68,64); |
| X_87 := bat.append(X_77,128); |
| X_70 := bat.new(nil:oid,nil:int); |
| X_79 := bat.append(X_70,0); |
| X_89 := bat.append(X_79,0); |
| X_9:bat[:oid,:wrd] := bat.new(nil:oid,nil:wrd); |
| X_8 := sql.mvc(); |
| X_12:bat[:oid,:oid] := sql.tid(X_8,"sys","foo"); |
| X_15:bat[:oid,:sht] := sql.bind(X_8,"sys","foo","a",0); |
| (X_18,r1_18) := sql.bind(X_8,"sys","foo","a",2); |
| X_21:bat[:oid,:sht] := sql.bind(X_8,"sys","foo","a",1); |
| X_23 := sql.delta(X_15,X_18,r1_18,X_21); |
| X_24 := algebra.leftfetchjoin(X_12,X_23); |
| X_25 := algebra.project(X_24,A1); |
| X_26 := algebra.project(X_24,A0); |
| (X_27,r1_29,r2_29) := group.subgroupdone(X_26); |
| X_30:bat[:oid,:wrd] := aggr.subcount(X_25,X_27,r1_29,true); |
| X_45 := algebra.leftfetchjoin(r1_29,X_26); |
| X_55:bat[:oid,:bit] := batcalc.==(X_24,A2); |
| X_56 := batcalc.bte(X_55); |
| X_57:bat[:oid,:hge] := aggr.subsum(X_56,X_27,r1_29,true,true); |
| X_31 := bat.append(X_9,X_30,true); |
| X_33 := sql.single(nil:bit); |
| X_34 := batcalc.bit(X_33); |
| X_35 := algebra.project(X_24,A3); |
| (X_36,r1_47,r2_47) := group.subgroupdone(X_35); |
| X_39:bat[:oid,:wrd] := aggr.subcount(X_34,X_36,r1_47,true); |
| X_47 := algebra.leftfetchjoin(r1_47,X_35); |
| X_59:bat[:oid,:bit] := batcalc.==(X_24,A5); |
| X_60 := batcalc.bte(X_59); |
| X_61:bat[:oid,:hge] := aggr.subsum(X_60,X_36,r1_47,true,true); |
| X_41 := bat.append(X_31,X_39,true); |
| X_42:bat[:oid,:str] := bat.new(nil:oid,nil:str); |
| X_46 := bat.append(X_42,X_45,true); |
| X_48 := bat.append(X_46,X_47,true); |
| (X_49,r1_60,r2_60) := group.subgroupdone(X_48); |
| X_52:bat[:oid,:wrd] := aggr.subsum(X_41,X_49,r1_60,true,true); |
| X_53:bat[:oid,:hge] := bat.new(nil:oid,nil:hge); |
| X_58 := bat.append(X_53,X_57,true); |
| X_62 := bat.append(X_58,X_61,true); |
| X_63:bat[:oid,:hge] := aggr.subsum(X_62,X_49,r1_60,true,true); |
| language.pass(X_26); |
| language.pass(X_27); |
| language.pass(r1_29); |
| language.pass(X_35); |
| language.pass(X_24); |
| language.pass(X_36); |
| language.pass(r1_47); |
| language.pass(X_49); |
| language.pass(r1_60); |
| exit X_110; |
| sql.resultSet(X_81,X_83,X_85,X_87,X_89,X_52,X_63); |
| end user.s15_1; |
+-----------------------------------------------------------------------------+
66 tuples (6.596ms)

Bizarrely, that's the simplest I can get the query; all further reductions I've tried from there make the problem go away.

I think that formally I should have used "cast(null as boolean)" instead of just plain "null", but this query works on Oct2014-SP4.

Comment 21145

Date: 2015-08-14 12:39:13 +0200
From: @njnes

fixed handling of the auto-coersion of the NULL value into a boolean.

Comment 21146

Date: 2015-08-14 12:42:22 +0200
From: MonetDB Mercurial Repository <>

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

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

Changeset description:

added test for Bug #3787

Comment 21147

Date: 2015-08-14 12:42:36 +0200
From: MonetDB Mercurial Repository <>

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

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

Changeset description:

fixed bug #3787, ie handle NULL cast properly

Comment 21176

Date: 2015-08-28 13:41:23 +0200
From: @sjoerdmullender

Jul2015 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