Date: 2016-03-02 23:37:51 +0100
From: @yzchang
To: SQL devs <>
Version: 11.21.13 (Jul2015-SP2)
CC: @swingbit, @yzchang

Last updated: 2016-03-25 09:59:15 +0100

Comment 21852

Date: 2016-03-02 23:37:51 +0100
From: @yzchang

Try the following query set:

CREATE TABLE foo (dat1 DATE, dat2 DATE);
INSERT INTO foo VALUES ('2016-01-01', '2016-01-02');
INSERT INTO foo VALUES ('2016-01-01', '2016-01-01');
INSERT INTO foo VALUES ('2016-01-01', '2016-01-31');
SELECT (dat2-dat1), (dat2-dat1) < 10 FROM foo;

The final SELECT query produces this result:

sql>SELECT (dat2-dat1), (dat2-dat1) < 10 FROM foo;
+------+----------------+
| L1 | <_sql_sub_dat2 |
+======+================+
| 1 | true |
| 0 | true |
| 30 | true |
+------+----------------+
3 tuples (3.446ms)

The comparison result of the last tuple should be false.

Comment 21853

Date: 2016-03-02 23:42:14 +0100
From: MonetDB Mercurial Repository <>

Changeset c6ea0ebc35d0 made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for Bug #3940

Comment 21856

Date: 2016-03-03 11:36:06 +0100
From: MonetDB Mercurial Repository <>

Changeset 2737878355ca 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=2737878355ca

Changeset description:

use int for date difference, fixes bug #3940

Comment 21857

Date: 2016-03-03 11:51:55 +0100
From: @swingbit

Is this the same as https://www.monetdb.org/bugzilla/show_bug.cgi?id=3917 ?

Comment 21861

Date: 2016-03-03 18:05:18 +0100
From: @yzchang

Hai Roberto,

You are right, this is caused by the same problem as Bug #3917.

I don't completely understand why things have been implemented that way, but I got the following from Niels explanation earlier today:

• intervals are only stored as one of two types of intervals (don't know which ones), which isn't enough to capture all types of intervals.

• in the SELECT query in this bug, the number 10 is for some reason (I didn't get) regarded as a year interval. Hence, it's converted to 10*12 months, which is indeed larger than 30 months.

• until better alternatives are found, we now return an INT for date difference. At least, this doesn't break any existing tests, while returning the expected results for the date difference comparison here.

With this change, both queries in Bug #3917 now return 365. So probably, that one can be also marked as RESOLVED.

Comment 21862

Date: 2016-03-03 18:10:16 +0100
From: @yzchang

By the way, as expected, before Niels' fix (Changeset 2737878355ca), the query returns expected results, if one explicitly defines the number 10 as a month interval:

sql>SELECT (dat2-dat1), (dat2-dat1) < interval '10' month FROM foo;
+------+----------------+
| L1 | <_sql_sub_dat2 |
+======+================+
| 1 | true |
| 0 | true |
| 30 | false |
+------+----------------+
3 tuples (1.564ms)

Comment 21958

Date: 2016-03-25 09:59:15 +0100
From: @sjoerdmullender

Jul2015-SP3 has been released.