MySQL Bugs: #1448: date parsing fails, and fails to complain about it

Bug #1448	date parsing fails, and fails to complain about it
Submitted:	30 Sep 2003 13:49	Modified:	2 Dec 2003 11:02
Reporter:	Dave Dyer
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.0.15	OS:	Microsoft Windows (windows 2000)
Assigned to:	Dmitri Lenev	Target Version:

Description:
 I used date ranges of the form

  WHERE date>20030930000000 and date<20030930240000

that is, a well formed date with 24 hours of time added.  I realize there
are recommended and supported ways of doing this, but UNTIL TODAY it worked,
so I thought it was within the accepted format of date specifiers.

 However, today the above date interval fails, and also fails to complain
about the failure.  It probably just fell into some hole in the date parsing
logic.

 -- So choose your bug.  If dates of the form 20030920240000 are acceptable,
they should always work.  If they are unacceptable, they should never work.
It is unacceptable that that work most of the time.

How to repeat:
mysql> create table sample ( now timestamp );
mysql> insert into sample set now=20030930120000;
mysql> insert into sample set now=20030929120000;
mysql> select * from sample where now>20030930000000 and now<20030930235959;
+----------------+
| now            |
+----------------+
| 20030930120000 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from sample where now>20030930000000 and now<20030930240000;
Empty set (0.00 sec)

mysql> 
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample where now>20030929000000 and now<20030929235959;
+----------------+
| now            |
+----------------+
| 20030929120000 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from sample where now>20030929000000 and now<20030929240000;
+----------------+
| now            |
+----------------+
| 20030929120000 |
+----------------+
1 row in set (0.00 sec)

Suggested fix:
either reject "out of spec" dates, or make sure the dates you accept work
correctly.

You are right. At least one of results must be assumed wrong. Both now<20030930240000; and
now<20030929240000; should either succeed or failed.

As you said there are better ways to seek for same result, but this doesn't make current
befaviour correct :)

Manual says that numbers or strings representing illegal timestamps which is converted to
timestamp value should be converted to special 0 value.
The same should happen when you are comparing number or string with timestamp.

So both of your queries should return empty sets.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

ChangeSet 1.1579.2.1 2003/12/02 20:25:45 dlenev@mysql.com
  Fix for Bug #1448 "Date parsing fails, and fails to complain 
  about it". Now numbers representing illegal timestamps are 
  converted to 0 value if they are stored as timestamp or 
  datetime. This behaviour is consistent with manual and with 
  behaviour of string -> timestamp conversion.