Bug #48090 Index on DATE field affects behavior of query using 'YYYY-MM-DD 00:00:00'

• Submitted: 15 Oct 2009 16:06
• Modified: 15 Oct 2009 16:56
• Reporter: e kascic
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0.45
• OS: Linux
• CPU Architecture: Any

[15 Oct 2009 16:06] e kascic

Description:
When issuing a query against a DATE field like:

select *
from table_x
where date_field = 'YYYY-MM-DD 00:00:00'

If the date_field DOES NOT have an index, this query will return the same as:

select *
from table_x
where date_field = 'YYYY-MM-DD'

If the date_field DOES have an index, this query returns nothing.

How to repeat:
When issuing a query against a DATE field like:

select *
from table_x
where date_field = 'YYYY-MM-DD 00:00:00'

If the date_field DOES NOT have an index, this query will return the same as:

select *
from table_x
where date_field = 'YYYY-MM-DD'

If the date_field DOES have an index, this query returns nothing.

Suggested fix:
I would not expect an index to make a difference.

[15 Oct 2009 16:18] MySQL Verification Team

Please try with latest released version and comment the result here.

[15 Oct 2009 16:45] e kascic

my bad.  on 5.0.86 this issue doesn't appear to happen anymore.

[15 Oct 2009 16:56] MySQL Verification Team

Thank you for the feedback. Closed according last comment.