inconsistent/strange handling of invalid dates (e.g. 2013-02-29) in where clause

[monetdb-team]

Date: 2013-11-29 10:17:00 +0100
From: Will M <<will.muldrew>>
To: SQL devs <>
Version: 11.15.17 (Feb2013-SP5)
CC: @njnes

Last updated: 2014-02-20 15:02:38 +0100

Comment 19360

Date: 2013-11-29 10:17:00 +0100
From: Will M <<will.muldrew>>

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

If querying with an invalid calendar date results are strange and inconsistent. I think this should be a parse error. e.g. In a similar situation MSSQL says:

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

While it should be hard to come up with bogus dates in a fully integrated system, if using SQL interactively it's quite easy to do this and accidentally issue huge queries, or get confusing results!

Reproducible: Always

Steps to Reproduce:

sql>create table datebug (time timestamp, foo INT);
operation successful (15.898ms)
sql>insert into datebug values ('2013-01-01 00:00:00', 1);
1 affected row (3.944ms)
sql>insert into datebug values ('2013-02-01 00:00:00', 2);
1 affected row (5.756ms)
sql>insert into datebug values ('2013-03-01 00:00:00', 3);
1 affected row (3.311ms)
sql>insert into datebug values ('2013-04-01 00:00:00', 4);
1 affected row (3.452ms)
sql>insert into datebug values ('2013-05-01 00:00:00', 5);
1 affected row (3.299ms)
sql>insert into datebug values ('2013-06-01 00:00:00', 6);
1 affected row (3.464ms)
sql>select * from datebug where time < '2013-02-28';
+----------------------------+------+
| time | foo |
+============================+======+
| 2013-01-01 000000000000 | 1 |
| 2013-02-01 000000000000 | 2 |
+----------------------------+------+
2 tuples (2.295ms)
sql>select * from datebug where time < '2013-02-29';
+------+-----+
| time | foo |
+======+=====+
+------+-----+
0 tuples (2.055ms)
sql>select * from datebug where time > '2013-01-01' and time < '2013-02-30';
+----------------------------+------+
| time | foo |
+============================+======+
| 2013-02-01 000000000000 | 2 |
| 2013-03-01 000000000000 | 3 |
| 2013-04-01 000000000000 | 4 |
| 2013-05-01 000000000000 | 5 |
| 2013-06-01 000000000000 | 6 |
+----------------------------+------+
5 tuples (3.176ms)
sql>select * from datebug where time > '2013-01-01' and time < 'foobar';
timestamp (foobar) has incorrect format

Actual Results:

As above

Expected Results:

Some sort of parsing error (e.g. like the 'timestamp (foobar) has incorrect format' error)

Comment 19364

Date: 2013-12-01 13:20:26 +0100
From: @njnes

properly return error on incorrect dates (not just nil)

Comment 19365

Date: 2013-12-01 13:21:47 +0100
From: MonetDB Mercurial Repository <>

Changeset 42a22ed752bf 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=42a22ed752bf

Changeset description:

fixed bug #3401 and added test for it.
The date_tostr now returns 0 (ie no characters parsed) on error.

Comment 19601

Date: 2014-02-20 15:02:38 +0100
From: @sjoerdmullender

Jan2014 has been released.