Corrupt mitosis plan with group-by query

[monetdb-team]

Date: 2013-07-30 16:55:34 +0200
From: @drstmane
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2013-09-27 13:47:16 +0200

Comment 18945

Date: 2013-07-30 16:55:34 +0200
From: @drstmane

The following group-by query result in a corrupt plan when mitosis is enabled (with little sample data, --forcemito is required):

create table error (x int, y int, intensity int);
copy 2 records into error from stdin delimiters ',','\n';
1,2,3
4,5,6

select
tilex,
tiley,
intensity,
count(*) as count
from (
select
floor(x/16) as tilex,
floor(y/16) as tiley,
intensity
from
error
) as image
group by
tilex,
tiley,
intensity
;

results in

TypeException:user.s0_1[58]:'_65' may not be used before being initialized
TypeException:user.s0_1[45]:'_65' may not be used before being initialized
TypeException:user.s0_1[43]:'_65' may not be used before being initialized
TypeException:user.s0_1[43]:'_65' may not be used before being initialized

var X_65 occurs indeed only once in the EXPLAIN output:

$ grep _65 error.explain
| (X_68,r1_68,r2_68) := group.subgroup(X_45,X_65);

$ grep -9 _65 error.explain
| X_169:bat[:oid,:int] := sql.bind(X_4,"sys","error","intensity",0,0,2); |
| X_170:bat[:oid,:int] := sql.bind(X_4,"sys","error","intensity",0,1,2); |
| (X_171:bat[:oid,:oid] ,X_172:bat[:oid,:int] ) := sql.bind(X_4,"sys","error","intensity",2,0,2); |
| (X_173:bat[:oid,:oid] ,X_174:bat[:oid,:int] ) := sql.bind(X_4,"sys","error","intensity",2,1,2); |
| X_56 := sql.bind(X_4,"sys","error","intensity",1); |
| X_193 := sql.delta(X_169,X_171,X_172); |
| X_194 := sql.delta(X_170,X_173,X_174,X_56); |
| X_195 := algebra.leftfetchjoin(X_154,X_193); |
| X_196 := algebra.leftfetchjoin(X_156,X_194); |

| (X_68,r1_68,r2_68) := group.subgroup(X_45,X_65); |

| (X_71,r1_71,r2_71) := group.subgroupdone(X_25,X_68); |
| X_77 := algebra.leftfetchjoin(r1_71,X_25); |
| X_85 := algebra.leftfetchjoin(r1_71,X_45); |
| X_59 := mat.pack(X_195,X_196); |
| X_93 := algebra.leftfetchjoin(r1_71,X_59); |
| X_99:bat[:oid,:wrd] := aggr.subcount(X_71,X_71,r1_71,false); |
| X_107 := sql.resultSet(4,1,X_77); |
| sql.rsColumn(X_107,"sys.image","tilex","real",24,0,X_77); |
| sql.rsColumn(X_107,"sys.image","tiley","real",24,0,X_85); |

Comment 18946

Date: 2013-07-30 20:20:51 +0200
From: @njnes

sovled by handling batmmath in mitosis/mergetable

Comment 18950

Date: 2013-07-30 22:44:05 +0200
From: MonetDB Mercurial Repository <>

Changeset ce1402f55e09 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=ce1402f55e09

Changeset description:

fixed bug #3330, ie handle batmmath in mitosis