Bug #48901 With date field as key in query show diff with >='yyyymmdd' or >='yyyy-mm-dd'

• Submitted: 19 Nov 2009 12:17
• Modified: 24 Nov 2009 8:47
• Reporter: Peter Szekvolgyi
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.40
• OS: Linux (Debian 32/64 bit)
• CPU Architecture: Any

[19 Nov 2009 12:17] Peter Szekvolgyi

Description:
Both innoDB and MyIsam engine if we use date field as key and in the query use "date field>='20090912'" don't show data where date field is 2009-09-12.

It's works correctly with "=" or we don't use date field as key.

(me@home) [test]> describe testtable;
+--------+------------------+------+-----+------------+----------------+
| Field  | Type             | Null | Key | Default    | Extra          |
+--------+------------------+------+-----+------------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL       | auto_increment | 
| others | int(11)          | NO   | MUL | 0          |                | 
| datum  | date             | NO   | MUL | 0000-00-00 |                | 
+--------+------------------+------+-----+------------+----------------+

select * from testtable WHERE datum>='20090912';
DON'T show data record where datum is 2009-09-12.

select * from testtable WHERE datum>='2009-09-12';
Show data correctly where in datum 2009-09-12 or greater then.

select * from testtable WHERE datum='20090912';
select * from testtable WHERE datum='2009-09-12';
Show the same and correct data.

In MySSQL 4.1.15 and 5.1.34 both query works correctly.

Later i will attach generator script.

How to repeat:
Every time i use WHERE datum>='yyyymmdd' syntax in query. Where datum is date field as key.

[19 Nov 2009 12:18] Peter Szekvolgyi

table generator script

Attachment: generate.sh (application/octet-stream, text), 996 bytes.

[19 Nov 2009 12:22] Peter Szekvolgyi

Test case for generated table:
select * from testtable WHERE datum='20090912';
select * from testtable WHERE datum='2009-09-12';
Both works correctly.

select * from testtable WHERE datum>='20090912';
Don't show data where datum is 2009-09-12.
select * from testtable WHERE datum>='2009-09-12';
Show data correctly.

[19 Nov 2009 12:30] Peter Szekvolgyi

If we use date(datum)>=20090912 or datum>=20090912 the query works correctly.

[19 Nov 2009 15:11] MySQL Verification Team

Thank you for the bug report. Could you please attach here the output of the query which doesn't work for you?. I could'n repeat with current source server. Thanks in advance.

[24 Nov 2009 8:47] Peter Szekvolgyi

In 5.1.41 works without any errors.