Bug #30956 Out-of-range time values treated differently in indexed and unindexed columns

• Submitted: 11 Sep 2007 16:06
• Modified: 11 Oct 2007 17:22
• Reporter: Rob Lewis (Candidate Quality Contributor)
• Status: No Feedback
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 4.1.22
• OS: MacOS (X Server 10.4.10)
• CPU Architecture: Any
• Tags: comparison, indexed, qc, time

[11 Sep 2007 16:06] Rob Lewis

Description:
The following statement works if the column date_field is not indexed:

SELECT date_field, contents FROM mytable WHERE date_field >= "2005-00-00 00:00:00" AND date_field <= "2005-12-31 99:99:99" ORDER BY UPPER(date_field)

However, if an index is created on date_field, the same statement returns 0 rows. 

The problem is in the time value of "99:99:99". If it is changed to "23:59:59" the statement works as expected. 

Either both versions should fail, or they should both work. 

How to repeat:
Perform a SELECT specifying a date/time value that contains "99:99:99", where the column is indexed. 

Suggested fix:
Generate an error for out-of-range time values. 

Alternatively, tolerate them in indexed columns as in unindexed columns.

[11 Sep 2007 17:22] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 4.1.23. In case of the same problem, please, send a complete test case that demonstrates it, with CREATE TABLE and INSERTs.

[11 Oct 2007 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".