| Bug #32291 | SELECT query returns empty result when datetime field contains nulls | ||
|---|---|---|---|
| Submitted: | 12 Nov 2007 18:29 | Modified: | 12 Nov 2007 18:48 |
| Reporter: | Maciej Tomaka | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.45 | OS: | Linux (tested on Ubuntu 6.06 and rhel 5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | date, datetime, null, SELECT | ||
[12 Nov 2007 18:32]
Maciej Tomaka
Tested on engines InnoDB and MyIsam.
[12 Nov 2007 18:48]
MySQL Verification Team
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 bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
[miguel@skybr 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.52-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM tmp where DATE(time) = '2007-11-12';
+---------------------+
| time |
+---------------------+
| 2007-11-12 15:11:22 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tmp;
+---------------------+
| time |
+---------------------+
| NULL |
| 2007-11-12 15:11:22 |
+---------------------+
2 rows in set (0.00 sec)
mysql>
Description: Query on the table with field datetime containing NULL fails when WHERE caluse contains DATE(time) = some_time It returns empty result. SELECT * FROM tmp WHERE DATE(time) = '2007-11-12'; returns no results SELECT * FROM tmp WHERE time IS NULL AND DATE(time) = '2007-11-12'; returns good results - but those two queries should return the same results. How to repeat: mysql> CREATE TABLE tmp (time datetime); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO tmp VALUES (NULL), ('2007-11-12 15:11:22'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tmp where DATE(time) = '2007-11-12'; Empty set (0.00 sec) -- FAILED! but mysql> SELECT * FROM tmp where time IS NOT NULL AND DATE(time) = '2007-11-12'; +---------------------+ | time | +---------------------+ | 2007-11-12 15:11:22 | +---------------------+ 1 row in set (0.00 sec) The contents of table: mysql> SELECT * FROM tmp; +---------------------+ | time | +---------------------+ | NULL | | 2007-11-12 15:11:22 | +---------------------+ 2 rows in set (0.00 sec) Suggested fix: I don't know the fix. But interesting is : mysql> SELECT * FROM tmp where CAST(DATE(time) AS DATETIME) = '2007-11-12'; +---------------------+ | time | +---------------------+ | 2007-11-12 15:11:22 | +---------------------+ 1 row in set (0.00 sec) It may be related to recent changes in 5.0.45 in DATE, DATETIME comparing code.