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.