When subtracting two columns of type "timestamp", no results are returned

[monetdb-team]

Date: 2012-11-19 19:46:52 +0100
From: Percy Wegmann <>
To: MonetDB5 devs <>
Version: 11.13.3 (Oct2012)
CC: @yzchang

Last updated: 2013-01-22 09:29:18 +0100

Comment 17954

Date: 2012-11-19 19:46:52 +0100
From: Percy Wegmann <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11
Build Identifier:

This problem is specific to subtracting a column from another column. If subtracting a timestamp literal from a column, things work.

Reproducible: Always

Steps to Reproduce:

percy@percy-sandbox:~$ uname -a
Linux percy-sandbox 3.2.0-33-generic 52-Ubuntu SMP Thu Oct 18 16:29:15 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

percy@percy-sandbox:~$ monetdbd -v
MonetDB Database Server v1.6 (Oct2012)

percy@percy-sandbox:~$ mclient -u monetdb -d click
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012)
Database: MonetDB v11.13.3 (Oct2012), 'mapi:monetdb://percy-sandbox:50000/click'
Type \q to quit, ? for a list of available commands
auto commit mode: on

sql>create table time_table (atime timestamp, btime timestamp, ctime date);
operation successful (33.944ms)

sql>insert into time_table values(timestamp '1970-JAN-1', timestamp '1980-DEC-31', date '2012-JAN-1');
1 affected row (20.649ms)

sql>select * from time_table;
+----------------------------+----------------------------+------------+
| atime | btime | ctime |
+============================+============================+============+
| 1970-01-01 000000000000 | 1980-12-31 000000000000 | 2012-01-01 |
+----------------------------+----------------------------+------------+
1 tuple (1.402ms)

sql>select btime <> atime from time_table;
+----------+
| <>_btime |
+==========+
| true |
+----------+
1 tuple (1.208ms)

sql>select btime - timestamp '1975-JAN-01' from time_table;
+---------------+
| sql_sub_btime |
+===============+
| 189302400000 |
+---------------+
1 tuple (1.389ms)

sql>select btime - atime from time_table;
+---------------+
| sql_sub_btime |
+===============+
+---------------+
0 tuples (1.450ms)

Actual Results:

The query that performs date subtraction between two columns returns 0 tuples.

Expected Results:

All queries return 1 tuple

Stefan Manegold investigated and concluded the following:

This seems to be a bug in batmtime.diff(), which appears to return an empty result on a non-empty input:

sql>trace select btime - atime from time_table;
+---------------+
| sql_sub_btime |
+===============+
+---------------+
0 tuples (29.511ms)
+-------+-----------------------------------------------------------------------------------------------------------------------+
| ticks | stmt |
+=======+=======================================================================================================================+
| 1 | X_26:bat[:oid,:lng] := nil:bat[:oid,:lng]; |
| 5 | X_2 := sql.mvc(); |
| 31 | X_18:bat[:oid,:timestamp] =<tmp_1301>[1] := sql.bind(X_2=0,"sys","time_table","atime",0); |
| 22 | X_8:bat[:oid,:timestamp] =<tmp_1113>[0] := sql.bind(X_2=0,"sys","time_table","btime",2); |
| 128 | X_12:bat[:oid,:timestamp] =<tmp_1114>[0] := sql.bind(X_2=0,"sys","time_table","btime",1); |
| 32 | X_23:bat[:oid,:timestamp] =<tmp_1114>[0] := sql.bind(X_2=0,"sys","time_table","atime",1); |
| 38 | X_3:bat[:oid,:timestamp] =<tmp_1314>[1] := sql.bind(X_2=0,"sys","time_table","btime",0); |
| 277 | X_20:bat[:oid,:timestamp] =<tmp_1113>[0] := sql.bind(X_2=0,"sys","time_table","atime",2); |
| 207 | X_15:bat[:oid,:oid] =<tmp_1313>[0] := sql.bind_dbat(X_2=0,"sys","time_table",1); |
| 14 | X_10=<tmp_1271>[1] := algebra.kdifference(X_3==nil:bat[:oid,:timestamp],X_8=="<tmp_1113>":bat[:oid,:timestamp][0]); |
| 15 | X_21=<tmp_1270>[1] := algebra.kdifference(X_18==nil:bat[:oid,:timestamp],X_20=="<tmp_1113>":bat[:oid,:timestamp][0]); |
| 21 | X_11=<tmp_1316>[1] := algebra.kunion(X_10==nil:timestamp,X_8==nil:bat[:oid,:timestamp]); |
| 5 | X_16=<tmpr_1313>[0] := bat.reverse(X_15=nil:bat[:oid,:oid]); |
| 22 | X_22=<tmp_1271>[1] := algebra.kunion(X_21==nil:timestamp,X_20==nil:bat[:oid,:timestamp]); |
| 15 | X_14=<tmp_1270>[1] := algebra.kunion(X_11==nil:timestamp,X_12==nil:bat[:oid,:timestamp]); |
| 11 | X_24=<tmp_1316>[1] := algebra.kunion(X_22==nil:timestamp,X_23==nil:bat[:oid,:timestamp]); |
| 7 | X_17=<tmp_1271>[1] := algebra.kdifference(X_14==nil:timestamp,X_16=<tmpr_1313>[0]); |
| 7 | X_25=<tmp_1270>[1] := algebra.kdifference(X_24==nil:timestamp,X_16=nil); |
| 9 | X_26:bat[:oid,:lng] =<tmp_1316>[0] := batmtime.diff(X_17==nil:timestamp,X_25==nil:timestamp); |
| 2301 | barrier X_44 := language.dataflow(); |
| 4 | X_28 := sql.resultSet(1,1,X_26=<tmp_1316>:bat[:oid,:lng][0]); |
| 5 | sql.rsColumn(X_28=5,"sys.","sql_sub_btime","bigint",64,0,X_26=nil:bat[:oid,:lng]); |
| 1 | X_34 := io.stdout(); |
| 13 | sql.exportResult(X_34=="104d2":streams,X_28=5); |
| 2 | end s7_1; |
| 2582 | X_5:void := user.s7_1(); |
+-------+-----------------------------------------------------------------------------------------------------------------------+
26 tuples (29.537ms)

Comment 17955

Date: 2012-11-20 09:50:11 +0100
From: @sjoerdmullender

Changeset 1d7a4799035d made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Set missing properties, chief of which the count.
This fixes bug #3190.

Comment 17956

Date: 2012-11-20 09:53:37 +0100
From: @sjoerdmullender

Changeset e123a2c7b7e1 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #3190.

Comment 17957

Date: 2012-11-20 09:55:26 +0100
From: @sjoerdmullender

Fixed. This will be in Oct2012-SP2, not in SP1 which is due out this week.

Comment 18384

Date: 2013-01-22 09:29:18 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.