Date: 2015-03-13 12:30:10 +0100
From: Richard Hughes <<richard.monetdb>>
To: SQL devs <>
Version: 11.19.9 (Oct2014-SP2)
CC: @njnes

Last updated: 2019-06-06 13:17:07 +0200

Comment 20713

Date: 2015-03-13 12:30:10 +0100
From: Richard Hughes <<richard.monetdb>>

Build is Oct2014 e58372859532

sql>set optimizer='minimal_pipe';
operation successful (0.837ms)

sql>create table foo (a int);
operation successful (1.389ms)

sql>insert into foo values (1),(2),(3);
3 affected rows (0.771ms)

sql>select 100*v from (select sum(1) as v from foo group by a) as t;
TypeException:user.s5_1[19]:'bat.insert' undefined in: _62:any := bat.insert(_52
:bat[:oid,:sht], _60:oid, _59:lng)
program contains errors

sql>explain select 100v from (select sum(1) as v from foo group by a) as t;
TypeException:user.s6_1[19]:'bat.insert' undefined in: _62:any := bat.insert(_52
:bat[:oid,:sht], _60:oid, _59:lng)
+-----------------------------------------------------------------------------+
| mal |
+=============================================================================+
| function user.s6_1{autoCommit=true}(A0:lng,A1:bte):void; |
| X_4 := sql.mvc(); |
| X_5 := A0; |
| X_6 := calc.sht(X_5); |
| X_7:bat[:oid,:oid] := sql.tid(X_4,"sys","foo"); |
| X_10:bat[:oid,:int] := sql.bind(X_4,"sys","foo","a",0); |
| (X_13,r1_13) := sql.bind(X_4,"sys","foo","a",2); |
| X_16:bat[:oid,:int] := sql.bind(X_4,"sys","foo","a",1); |
| X_18 := sql.delta(X_10,X_13,r1_13,X_16); |
| X_19 := algebra.leftfetchjoin(X_7,X_18); |
| X_20 := A1; |
| X_21 := algebra.project(X_19,X_20); |
| (X_22,r1_22,r2_22) := group.subgroupdone(X_19); |
| X_25:bat[:oid,:lng] := aggr.subsum(X_21,X_22,r1_22,true,true); |
| X_50 := bat.reverse(X_25); |
| X_52 := bat.new(nil:oid,nil:sht); |
| barrier (X_56,X_57) := iterator.new(X_25); |
| X_59 := calc.(X_6,X_57); |
| X_60 := algebra.fetch(X_50,X_56); |
| bat.insert(X_52,X_60,X_59); |
| redo (X_56,X_57) := iterator.next(X_25); |
| exit (X_56,X_57); |
| X_26:bat[:oid,:sht] := X_52; |
| X_29 := sql.resultSet(1,1,X_26); |
| sql.rsColumn(X_29,".L1","sql_mul_single_value","smallint",16,0,X_26); |
| X_35 := io.stdout(); |
| sql.exportResult(X_35,X_29); |
| end s6_1; |
| querylog.define("explain select 100*v from (select sum(1) as v from foo g |
: roup by a) as t;","minimal_pipe") :
+-----------------------------------------------------------------------------+
29 tuples (2.504ms)

That's as reduced as I can get the query while still reproducing it. I haven't done any further investigation because I found a different way of writing the original query.

Comment 20718

Date: 2015-03-18 15:16:57 +0100
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed bug #3687, ie use correct result type for aggregates

Comment 20719

Date: 2015-03-18 15:17:55 +0100
From: @njnes

fixed in stable, ie only reset the result type of aggregate functions of special types (like decimal and timestamps)

Comment 20736

Date: 2015-03-21 17:04:32 +0100
From: MonetDB Mercurial Repository <>

Changeset 66fe85a5c3f2 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

incorrect_result_type.Bug-3687: approving 128-bit result:
aggregations (here: sum()) always yield largest available result type
in order to "avoid" (actually: postpone) overflow.

Comment 27026

Date: 2019-06-06 13:17:07 +0200
From: MonetDB Mercurial Repository <>

Changeset 715c9a5ee107 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=715c9a5ee107

Changeset description:

Eliminated *.int128 output files (by adding cast(... as bigint))
Also extended test: incorrect_result_type.Bug-3687