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

Last updated: 2015-08-28 13:42:53 +0200

Comment 21122

Date: 2015-08-12 13:09:18 +0200
From: Richard Hughes <<richard.monetdb>>

Build is Jul2015 92af99c05811

sql>create table foo (a timestamp,b timestamp);
operation successful (24.743ms)

sql>insert into foo values ('2000-1-1','2001-1-1');
1 affected row (22.329ms)

sql>select sum(b-a) from foo;
overflow in conversion of 31622400000 to bte.

sql>explain select sum(b-a) from foo;
+-----------------------------------------------------------------------------+
| mal |
+=============================================================================+
| function user.s17_1{autoCommit=true}():void; |
| X_32:void := querylog.define("explain select sum(b-a) from foo;","no_mi |
: tosis_pipe",22); :
| barrier X_45 := language.dataflow(); |
| X_2 := sql.mvc(); |
| X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","foo"); |
| X_6:bat[:oid,:timestamp] := sql.bind(X_2,"sys","foo","b",0); |
| (X_9,r1_9) := sql.bind(X_2,"sys","foo","b",2); |
| X_12:bat[:oid,:timestamp] := sql.bind(X_2,"sys","foo","b",1); |
| X_14 := sql.delta(X_6,X_9,r1_9,X_12); |
| X_15 := algebra.leftfetchjoin(X_3,X_14); |
| X_16:bat[:oid,:timestamp] := sql.bind(X_2,"sys","foo","a",0); |
| (X_18,r1_18) := sql.bind(X_2,"sys","foo","a",2); |
| X_20:bat[:oid,:timestamp] := sql.bind(X_2,"sys","foo","a",1); |
| X_21 := sql.delta(X_16,X_18,r1_18,X_20); |
| X_22 := algebra.leftfetchjoin(X_3,X_21); |
| X_23:bat[:oid,:lng] := batmtime.diff(X_15,X_22); |
| X_24 := batcalc.bte(X_23); |
| X_25:hge := aggr.sum(X_24); |
| language.pass(X_3); |
| exit X_45; |
| sql.resultSet("sys.L1","L1","hugeint",128,0,7,X_25); |
| end user.s17_1; |
+-----------------------------------------------------------------------------+
22 tuples (2.742ms)

This worked in Oct2014-SP4.

Comment 21123

Date: 2015-08-12 13:22:55 +0200
From: Richard Hughes <<richard.monetdb>>

Even simpler repro:

sql>select sum(i) from (select interval '1' day) x(i);
overflow in conversion of 86400000 to bte.

sql>explain select sum(i) from (select interval '1' day) x(i);
+-----------------------------------------------------------------------------+
| mal |
+=============================================================================+
| function user.s3_2{autoCommit=true}(A0:lng):void; |
| X_13:void := querylog.define("explain select sum(i) from (select interv |
: al \'1\' day) x(i);","default_pipe",7); :
| X_3 := sql.single(A0); |
| X_4 := batcalc.bte(X_3); |
| X_5:lng := aggr.sum(X_4); |
| sql.resultSet(".L1","L1","bigint",64,0,7,X_5); |
| end user.s3_2; |
| optimizer.mitosis() |
| optimizer.dataflow() |
+-----------------------------------------------------------------------------+
9 tuples (54.898ms)

Comment 21129

Date: 2015-08-12 14:24:38 +0200
From: MonetDB Mercurial Repository <>

Changeset 4bcdb422d056 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=4bcdb422d056

Changeset description:

added fix and test for bug #3785

Comment 21131

Date: 2015-08-12 14:29:30 +0200
From: @njnes

added missing sum operators in the catalog

Comment 21221

Date: 2015-08-28 13:42:53 +0200
From: @sjoerdmullender

Jul2015 has been released.