Date: 2017-10-10 14:46:27 +0200
From: @swingbit
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: @mlkersten, @PedroTadim

Last updated: 2020-10-19 11:06:20 +0200

Comment 25686

Date: 2017-10-10 14:46:27 +0200
From: @swingbit

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.101 Safari/537.36
Build Identifier:

Date subtraction returns days:

sql>select cast('2017-10-10' as date) - cast('2017-10-01' as date);
+------+
| L4 |
+======+
| 9 |
+------+
1 tuple (1.234ms)

You would expect that reversing the operation behaves consistently.
So you would expect that 2017-10-01 + 9 = 2017-10-10.

But date addition does not work on days like subtraction, it works on seconds:

sql>select cast('2017-10-01' as date) + 9;
+------------+
| L3 |
+============+
| 2017-10-01 |
+------------+
1 tuple (1.269ms)

sql>select cast('2017-10-01' as date) + (9243600);
+------------+
| L3 |
+============+
| 2017-10-10 |
+------------+
1 tuple (1.420ms)

Reproducible: Always

$ mserver5 --version
MonetDB 5 server v11.27.6 (64-bit, 128-bit integers)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 15.6GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.41 2017-07-05 (compiled with 8.41)
openssl: OpenSSL 1.0.2k 26 Jan 2017 (compiled with OpenSSL 1.0.2k-fips 26 Jan 2017)
libxml2: 2.9.4 (compiled with 2.9.4)
Compiled by: roberto@photon.hq.spinque.com (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wduplicated-cond -Wnested-externs -Wunreachable-code
Linking : /usr/bin/ld -m elf_x86_64

Comment 25694

Date: 2017-10-12 08:54:44 +0200
From: @sjoerdmullender

It's even worse, I would say. The difference between two dates should be an interval (in this case, INTERVAL DAY), so when you add that interval back to the subtracted date, you should get the subtractee.
As in:
sql>select cast('2017-10-01' as date) + (cast('2017-10-10' as date) - cast('2017-10-01' as date));
But this result is also wrong.

This, at least, works:
sql>select cast('2017-10-01' as date) + cast(9 as interval day);
+------------+
| L4 |
+============+
| 2017-10-10 |
+------------+
1 tuple (2.077ms)

Comment 25698

Date: 2017-10-12 10:43:47 +0200
From: @swingbit

Yes, there seem to be two issues:

1. date - date returns an integer but should return an interval day.

2. date +/- integer should be interpreted as date +/- interval day, i.e. these should be equivalent:

select cast('2017-10-01' as date) + cast(9 as interval day);
select cast('2017-10-01' as date) + 9;

Comment 27599

Date: 2020-03-15 11:19:56 +0100
From: @mlkersten

Can this bug report being closed?

Comment 27600

Date: 2020-03-15 11:56:50 +0100
From: @swingbit

I just checked, the latest release still has the same issue.

Comment 27603

Date: 2020-03-17 14:01:05 +0100
From: MonetDB Mercurial Repository <>

Changeset a9d24362b701 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=a9d24362b701

Changeset description:

Added test for bug #6415

Comment 27604

Date: 2020-03-17 15:07:43 +0100
From: MonetDB Mercurial Repository <>

Changeset 18c78c0f2b12 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=18c78c0f2b12

Changeset description:

Partial fix for bug #6415, ie use second interval for difference between dates. We cannot use month interval because it doesn't fit for the difference

Comment 28142

Date: 2020-10-01 12:10:31 +0200
From: @swingbit

Hi Pedro,

I'm checking this on Oct2020, I'm still confused about it.

The date difference of the original query reports now a second interval, as you said:

sql>select cast('2017-10-10' as date) - cast('2017-10-01' as date);
+------------+
| %2 |
+============+
| 777600.000 |
+------------+
1 tuple

The first thing to note is that a trace shows a day_interval, not a second_interval:

sql>trace select cast('2017-10-10' as date) - cast('2017-10-01' as date);
+------------+
| %2 |
+============+
| 777600.000 |
+------------+
1 tuple
+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| usec | statement |
+======+==================================================================================================================================================+
| 5 | X_1=0@0:void := querylog.define("trace select cast(\'2017-10-10\' as date) - cast(\'2017-10-01\' as date);":str, "default_pipe":str, 8:int); |
| 120 | sql.resultSet(".%2":str, "%2":str, "day_interval":str, 4:int, 0:int, 9:int, 777600000:lng); |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 tuples

A second point that maybe I haven't grasped, is how to make the operation reversible.
I tried the following, but with no luck:

sql>select cast('2017-10-10' as date) - 777600;
SELECT: no such binary operator 'sql_sub(date,int)'

sql>select cast('2017-10-10' as date) - 777600.000;
SELECT: no such binary operator 'sql_sub(date,decimal)'

sql>select cast('2017-10-10' as date) - cast(777600 as interval second);
types int(20,0) and sec_interval(13,0) are not equal

sql>select cast('2017-10-10' as date) - cast(777600.000 as interval second);
types decimal(9,3) and sec_interval(13,0) are not equal

Comment 28143

Date: 2020-10-01 12:17:45 +0200
From: @swingbit

OK, please ignore the second point. I hadn't really used the interval syntax before, my bad. Here it is.

sql>select cast('2017-10-10' as date) - interval '777600' second;
+------------+
| %2 |
+============+
| 2017-10-01 |
+------------+
1 tuple

(although being able to cast would be really nice)

Then only the first point may be interesting