You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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)
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.
The text was updated successfully, but these errors were encountered: