timestamp + month interval generates bogus MAL?

[monetdb-team]

Date: 2014-07-10 17:29:14 +0200
From: Richard Hughes <<richard.monetdb>>
To: SQL devs <>
Version: 11.17.17 (Jan2014-SP2)
CC: @njnes

Last updated: 2014-10-31 14:14:34 +0100

Comment 19897

Date: 2014-07-10 17:29:14 +0200
From: Richard Hughes <<richard.monetdb>>

sql>select timestamp '1-1-1'+tmonthinterval '1' month from (select 201412+7) as t(tmonth);
TypeException:user.s27_3[12]:'mtime.timestamp_add_month_interval' undefined in:
_19:any := mtime.timestamp_add_month_interval(_9:timestamp, _18:lng)
program contains errors

sql>explain select timestamp '1-1-1'+tmonthinterval '1' month from (select 201412+7) as t(tmonth);
TypeException:user.s28_3[12]:'mtime.timestamp_add_month_interval' undefined in:
_19:any := mtime.timestamp_add_month_interval(_9:timestamp, _18:lng)
+-----------------------------------------------------------------------------+
| mal |
+=============================================================================+
| function user.s28_3{autoCommit=true}(A0:timestamp,A1:int,A2:int,A3:sht,A4:l |
: ng):void; :
| sql.mvc(); |
| A0; |
| calc.timestamp(X_8,7); |
| A1; |
| A2; |
| A3; |
| calc.(X_12,X_13); |
| calc.lng(X_14); |
| A4; |
| calc.+(X_15,X_16); |
| calc.(X_11,X_17); |
| mtime.timestamp_add_month_interval(X_9,X_18); |
| sql.exportValue(1,".L","sql_add_single_value","timestamp",7,0,12,X_19," |
: "); :
| end s28_3; |
| querylog.define("explain select timestamp \'1-1-1\'+tmonthinterval \' |
: 1\' month from (select 201412+7) as t(tmonth);","default_pipe") :
+-----------------------------------------------------------------------------+

Notice that the MAL doesn't contain any variable names. This seems...curious...to me.

Expected result:
+----------------------------+
| single_value |
+============================+
| 2014-08-01 000000000000 |
+----------------------------+

The code works if I use interval '1' day (or any interval shorter than a month) instead. Likewise '1' year doesn't work. Basically timestamp_add_month_interval() appears broken and timestamp_add_msec_interval() seems fine. In this case. Simpler queries like "select timestamp '1-1-1' + 5 * interval '1' month" work.

The back story (probably irrelevant to finding & fixing the bug):

I'm trying to aggregate a large table containing a timestamp column by the year+month of that column, and ended up with a query something like:

select timestamp '1-1-1'+(tmonth-1)interval '1' month,count() from (select "year"(t)*12+"month"(t) from data) as t(tmonth) group by tmonth;

If you can suggest a more cunning way to implement that query then I'm all ears.

Comment 19898

Date: 2014-07-11 12:31:43 +0200
From: Richard Hughes <<richard.monetdb>>

Another data point (possibly simpler to debug):

Works:
select interval '1' month*("year"(t)) from data;

Does not work:
select interval '1' month*("year"(t)*1) from data;
TypeException:user.s45_2[26]:'bat.insert' undefined in: _76:any := bat.insert(_68:bat[:oid,:int], _74:oid, _73:lng)

Comment 20285

Date: 2014-10-11 18:57:06 +0200
From: @njnes

added the missing mal interface (with lng type for months)

Comment 20379

Date: 2014-10-31 14:14:34 +0100
From: @sjoerdmullender

Oct2014 has been released.