Changeset: 4046430d77b3 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=4046430d77b3 Modified Files: monetdb5/modules/mal/array.mx sql/scripts/29_array.sql sql/test/sciql/Tests/01_create_01.stable.out sql/test/sciql/Tests/07_tiling_01.sql sql/test/sciql/Tests/07_tiling_01.stable.out sql/test/sciql/Tests/07_tiling_02.sql sql/test/sciql/Tests/07_tiling_02.stable.out sql/test/sciql/Tests/07_tiling_03.sql sql/test/sciql/Tests/07_tiling_03.stable.out sql/test/sciql/Tests/07_tiling_04.sql sql/test/sciql/Tests/07_tiling_04.stable.out Branch: sciql Log Message:
added the COUNT function over the array tiles. extended existing tests with count() approved expected changes in the stable outputs corrected stable outputs of 07_tiling_04.sql to reveal the problem in the current code in handling the group by criterion "[*]". This shall be fixed for the XLDB2012 demo... diffs (truncated from 3567 to 300 lines): diff --git a/monetdb5/modules/mal/array.mx b/monetdb5/modules/mal/array.mx --- a/monetdb5/modules/mal/array.mx +++ b/monetdb5/modules/mal/array.mx @@ -97,6 +97,10 @@ pattern max(val:@1, any...):@1 address ARRAYtilesMax_@1 comment "Construct the maximum over all tiles"; +pattern cnt(val:@1, any...):lng +address ARRAYtilesCnt_@1 +comment "Construct the count over all tiles"; + module batarray; # The BAT version of the AGGR functions over array tiles @@ -119,6 +123,10 @@ comment "Construct the minimum over all pattern max(val:bat[:oid,:@1], any...):bat[:oid,:@1] address ARRAYtilesMax_@1 comment "Construct the maximum over all tiles"; + +pattern cnt(val:bat[:oid,:@1], any...):bat[:oid,:lng] +address ARRAYtilesCnt_@1 +comment "Construct the count over all tiles"; @ @mal @:tilesAggrDecl(bte)@ @@ -160,6 +168,7 @@ array_export str ARRAYtilesSum_@1(Client array_export str ARRAYtilesAvg_@1(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci); array_export str ARRAYtilesMin_@1(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci); array_export str ARRAYtilesMax_@1(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci); +array_export str ARRAYtilesCnt_@1(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci); @h @:array_defs(bte)@ @:array_defs(sht)@ @@ -357,7 +366,7 @@ ARRAYfiller(Client cntxt, MalBlkPtr mb, @= tilesAggr /* - * array.[sum|avg] inputs: + * array.[sum|avg|cnt] inputs: * (cell_val:BAT, * dim_1:BAT,tile_member_offset_dim_1:BAT,size_dim_1, ..., * dim_n:BAT,tile_member_offset_dim_n:BAT,size_dim_n) @@ -516,6 +525,12 @@ ARRAYtiles@4_@1(Client cntxt, MalBlkPtr @:tilesAggr(lng,lng,dbl,Avg,avg, (dbl)sum / (dbl)cnt )@ @:tilesAggr(flt,dbl,dbl,Avg,avg, (dbl)sum / (dbl)cnt )@ @:tilesAggr(dbl,dbl,dbl,Avg,avg, (dbl)sum / (dbl)cnt )@ +@:tilesAggr(bte,lng,lng,Cnt,cnt, (lng)cnt )@ +@:tilesAggr(sht,lng,lng,Cnt,cnt, (lng)cnt )@ +@:tilesAggr(int,lng,lng,Cnt,cnt, (lng)cnt )@ +@:tilesAggr(lng,lng,lng,Cnt,cnt, (lng)cnt )@ +@:tilesAggr(flt,dbl,lng,Cnt,cnt, (lng)cnt )@ +@:tilesAggr(dbl,dbl,lng,Cnt,cnt, (lng)cnt )@ @= tilesMinMax /* diff --git a/sql/scripts/29_array.sql b/sql/scripts/29_array.sql --- a/sql/scripts/29_array.sql +++ b/sql/scripts/29_array.sql @@ -36,27 +36,19 @@ create function array_series1("start" bi create function array_series1("start" real, step real, stop real, N integer, M integer) returns table (dimval real) external name "array".series; create function array_series1("start" double, step double, stop double, N integer, M integer) returns table (dimval double) external name "array".series; -create function array_filler(cnt bigint, val tinyint) returns table (id bigint, cellval tinyint) external name "array".filler_; -create function array_filler(cnt bigint, val smallint) returns table (id bigint, cellval smallint) external name "array".filler_; -create function array_filler(cnt bigint, val integer) returns table (id bigint, cellval integer) external name "array".filler_; -create function array_filler(cnt bigint, val bigint) returns table (id bigint, cellval bigint) external name "array".filler_; -create function array_filler(cnt bigint, val real) returns table (id bigint, cellval real) external name "array".filler_; -create function array_filler(cnt bigint, val double) returns table (id bigint, cellval double) external name "array".filler_; -create function array_filler(cnt bigint, val date) returns table (id bigint, vals date) external name "array".filler_; -create function array_filler(cnt bigint, val time) returns table (id bigint, vals time) external name "array".filler_; -create function array_filler(cnt bigint, val timestamp) returns table (id bigint, vals timestamp) external name "array".filler_; -create function array_filler(cnt bigint, val char(2048)) returns table (id bigint, vals char(2048)) external name "array".filler_; -create function array_filler(cnt bigint, val varchar(2048)) returns table (id bigint, vals varchar(2048)) external name "array".filler_; -create function array_filler(cnt bigint, val blob) returns table (id bigint, vals blob) external name "array".filler_; -create function array_filler(cnt bigint, val clob) returns table (id bigint, vals clob) external name "array".filler_; - ---create function array_avg(val int, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns table (a double) external name "array".avg; ---create function array_avg(val real, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns table (a real) external name "array".avg; ---create function array_avg(val double, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns table (a double) external name "array".avg; - ---create function array_sum(val int, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns table (s bigint) external name "array".sum; ---create function array_sum(val real, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns table (s double) external name "array".sum; ---create function array_sum(val double, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns table (s double) external name "array".sum; +create function array_filler(count bigint, val tinyint) returns table (id bigint, cellval tinyint) external name "array".filler_; +create function array_filler(count bigint, val smallint) returns table (id bigint, cellval smallint) external name "array".filler_; +create function array_filler(count bigint, val integer) returns table (id bigint, cellval integer) external name "array".filler_; +create function array_filler(count bigint, val bigint) returns table (id bigint, cellval bigint) external name "array".filler_; +create function array_filler(count bigint, val real) returns table (id bigint, cellval real) external name "array".filler_; +create function array_filler(count bigint, val double) returns table (id bigint, cellval double) external name "array".filler_; +create function array_filler(count bigint, val date) returns table (id bigint, vals date) external name "array".filler_; +create function array_filler(count bigint, val time) returns table (id bigint, vals time) external name "array".filler_; +create function array_filler(count bigint, val timestamp) returns table (id bigint, vals timestamp) external name "array".filler_; +create function array_filler(count bigint, val char(2048)) returns table (id bigint, vals char(2048)) external name "array".filler_; +create function array_filler(count bigint, val varchar(2048)) returns table (id bigint, vals varchar(2048)) external name "array".filler_; +create function array_filler(count bigint, val blob) returns table (id bigint, vals blob) external name "array".filler_; +create function array_filler(count bigint, val clob) returns table (id bigint, vals clob) external name "array".filler_; create function array_avg(val tinyint, dim1 int, offsets1 int, size1 int) returns double external name "array".avg; create function array_avg(val smallint, dim1 int, offsets1 int, size1 int) returns double external name "array".avg; @@ -86,6 +78,13 @@ create function array_max(val bigint, create function array_max(val real, dim1 int, offsets1 int, size1 int) returns real external name "array".max; create function array_max(val double, dim1 int, offsets1 int, size1 int) returns double external name "array".max; +create function array_count(val tinyint, dim1 int, offsets1 int, size1 int) returns bigint external name "array".cnt; +create function array_count(val smallint, dim1 int, offsets1 int, size1 int) returns bigint external name "array".cnt; +create function array_count(val int, dim1 int, offsets1 int, size1 int) returns bigint external name "array".cnt; +create function array_count(val bigint, dim1 int, offsets1 int, size1 int) returns bigint external name "array".cnt; +create function array_count(val real, dim1 int, offsets1 int, size1 int) returns bigint external name "array".cnt; +create function array_count(val double, dim1 int, offsets1 int, size1 int) returns bigint external name "array".cnt; + create function array_avg(val tinyint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns double external name "array".avg; create function array_avg(val smallint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns double external name "array".avg; create function array_avg(val int, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns double external name "array".avg; @@ -114,6 +113,13 @@ create function array_max(val bigint, create function array_max(val real, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns real external name "array".max; create function array_max(val double, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns double external name "array".max; +create function array_count(val tinyint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns bigint external name "array".cnt; +create function array_count(val smallint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns bigint external name "array".cnt; +create function array_count(val int, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns bigint external name "array".cnt; +create function array_count(val bigint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns bigint external name "array".cnt; +create function array_count(val real, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns bigint external name "array".cnt; +create function array_count(val double, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int) returns bigint external name "array".cnt; + create function array_avg(val tinyint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns double external name "array".avg; create function array_avg(val smallint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns double external name "array".avg; create function array_avg(val int, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns double external name "array".avg; @@ -142,3 +148,10 @@ create function array_max(val bigint, create function array_max(val real, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns real external name "array".max; create function array_max(val double, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns double external name "array".max; +create function array_count(val tinyint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns bigint external name "array".cnt; +create function array_count(val smallint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns bigint external name "array".cnt; +create function array_count(val int, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns bigint external name "array".cnt; +create function array_count(val bigint, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns bigint external name "array".cnt; +create function array_count(val real, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns bigint external name "array".cnt; +create function array_count(val double, dim1 int, offsets1 int, size1 int, dim2 int, offsets2 int, size2 int, dim3 int, offsets3 int, size3 int) returns bigint external name "array".cnt; + diff --git a/sql/test/sciql/Tests/01_create_01.stable.out b/sql/test/sciql/Tests/01_create_01.stable.out --- a/sql/test/sciql/Tests/01_create_01.stable.out +++ b/sql/test/sciql/Tests/01_create_01.stable.out @@ -66,9 +66,9 @@ Ready. % id, name, schema_id, query, type, system, commit_action, readonly, fixed_array, nr_dimensions # name % int, varchar, int, varchar, smallint, boolean, smallint, boolean, boolean, int # type % 4, 4, 4, 0, 1, 5, 1, 5, 5, 1 # length -[ 6849, "ary", 2000, NULL, 7, false, 0, false, true, 2 ] -[ 6854, "ary1", 2000, NULL, 7, false, 0, false, false, 2 ] -[ 6860, "ary2", 2000, NULL, 7, false, 0, false, false, 3 ] +[ 7029, "ary", 2000, NULL, 7, false, 0, false, true, 2 ] +[ 7034, "ary1", 2000, NULL, 7, false, 0, false, false, 2 ] +[ 7040, "ary2", 2000, NULL, 7, false, 0, false, false, 3 ] #DROP ARRAY ary; # 13:50:46 > diff --git a/sql/test/sciql/Tests/07_tiling_01.sql b/sql/test/sciql/Tests/07_tiling_01.sql --- a/sql/test/sciql/Tests/07_tiling_01.sql +++ b/sql/test/sciql/Tests/07_tiling_01.sql @@ -3,6 +3,7 @@ select x, y, avg(v) from a group by a[x select x, y, sum(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, min(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, max(v) from a group by a[x-1:x+2][y-2:y+1]; +select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; drop array a; create array a (x int dimension[4], y int dimension[4], v smallint default 37); @@ -10,6 +11,7 @@ select x, y, avg(v) from a group by a[x select x, y, sum(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, min(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, max(v) from a group by a[x-1:x+2][y-2:y+1]; +select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; drop array a; create array a (x int dimension[4], y int dimension[4], v integer default 37); @@ -17,6 +19,7 @@ select x, y, avg(v) from a group by a[x select x, y, sum(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, min(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, max(v) from a group by a[x-1:x+2][y-2:y+1]; +select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; drop array a; create array a (x int dimension[4], y int dimension[4], v bigint default 37); @@ -24,6 +27,7 @@ select x, y, avg(v) from a group by a[x select x, y, sum(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, min(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, max(v) from a group by a[x-1:x+2][y-2:y+1]; +select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; drop array a; create array a (x int dimension[4], y int dimension[4], v real default 37); @@ -31,6 +35,7 @@ select x, y, avg(v) from a group by a[x select x, y, sum(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, min(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, max(v) from a group by a[x-1:x+2][y-2:y+1]; +select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; drop array a; create array a (x int dimension[4], y int dimension[4], v double default 37); @@ -38,5 +43,6 @@ select x, y, avg(v) from a group by a[x select x, y, sum(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, min(v) from a group by a[x-1:x+2][y-2:y+1]; select x, y, max(v) from a group by a[x-1:x+2][y-2:y+1]; +select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; drop array a; diff --git a/sql/test/sciql/Tests/07_tiling_01.stable.out b/sql/test/sciql/Tests/07_tiling_01.stable.out --- a/sql/test/sciql/Tests/07_tiling_01.stable.out +++ b/sql/test/sciql/Tests/07_tiling_01.stable.out @@ -130,6 +130,27 @@ Ready. [ 3, 1, 37 ] [ 3, 2, 37 ] [ 3, 3, 37 ] +#select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; +% sys.a, sys.a, sys. # table_name +% x, y, L1 # name +% int, int, bigint # type +% 1, 1, 1 # length +[ 0, 0, 2 ] +[ 0, 1, 4 ] +[ 0, 2, 6 ] +[ 0, 3, 6 ] +[ 1, 0, 3 ] +[ 1, 1, 6 ] +[ 1, 2, 9 ] +[ 1, 3, 9 ] +[ 2, 0, 3 ] +[ 2, 1, 6 ] +[ 2, 2, 9 ] +[ 2, 3, 9 ] +[ 3, 0, 2 ] +[ 3, 1, 4 ] +[ 3, 2, 6 ] +[ 3, 3, 6 ] #drop array a; #create array a (x int dimension[4], y int dimension[4], v smallint default 37); #select x, y, avg(v) from a group by a[x-1:x+2][y-2:y+1]; @@ -216,6 +237,27 @@ Ready. [ 3, 1, 37 ] [ 3, 2, 37 ] [ 3, 3, 37 ] +#select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; +% sys.a, sys.a, sys. # table_name +% x, y, L1 # name +% int, int, bigint # type +% 1, 1, 1 # length +[ 0, 0, 2 ] +[ 0, 1, 4 ] +[ 0, 2, 6 ] +[ 0, 3, 6 ] +[ 1, 0, 3 ] +[ 1, 1, 6 ] +[ 1, 2, 9 ] +[ 1, 3, 9 ] +[ 2, 0, 3 ] +[ 2, 1, 6 ] +[ 2, 2, 9 ] +[ 2, 3, 9 ] +[ 3, 0, 2 ] +[ 3, 1, 4 ] +[ 3, 2, 6 ] +[ 3, 3, 6 ] #drop array a; #create array a (x int dimension[4], y int dimension[4], v integer default 37); #select x, y, avg(v) from a group by a[x-1:x+2][y-2:y+1]; @@ -302,6 +344,27 @@ Ready. [ 3, 1, 37 ] [ 3, 2, 37 ] [ 3, 3, 37 ] +#select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; +% sys.a, sys.a, sys. # table_name +% x, y, L1 # name +% int, int, bigint # type +% 1, 1, 1 # length +[ 0, 0, 2 ] +[ 0, 1, 4 ] +[ 0, 2, 6 ] +[ 0, 3, 6 ] +[ 1, 0, 3 ] +[ 1, 1, 6 ] +[ 1, 2, 9 ] +[ 1, 3, 9 ] +[ 2, 0, 3 ] +[ 2, 1, 6 ] +[ 2, 2, 9 ] +[ 2, 3, 9 ] +[ 3, 0, 2 ] +[ 3, 1, 4 ] +[ 3, 2, 6 ] +[ 3, 3, 6 ] #drop array a; #create array a (x int dimension[4], y int dimension[4], v bigint default 37); #select x, y, avg(v) from a group by a[x-1:x+2][y-2:y+1]; @@ -388,6 +451,27 @@ Ready. [ 3, 1, 37 ] [ 3, 2, 37 ] [ 3, 3, 37 ] +#select x, y,count(v) from a group by a[x-1:x+2][y-2:y+1]; +% sys.a, sys.a, sys. # table_name +% x, y, L1 # name +% int, int, bigint # type +% 1, 1, 1 # length _______________________________________________ Checkin-list mailing list Checkin-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/checkin-list