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 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.40 OS:Linux (Debian 32/64 bit)
Assigned to: 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] Godofredo Miguel Solorzano
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.