| Bug #35083 | SELECT DATE(column) >= ... ON column column with NULL wrong results | ||
|---|---|---|---|
| Submitted: | 5 Mar 2008 15:04 | Modified: | 5 Mar 2008 19:27 |
| Reporter: | Łukasz Lato | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Mar 2008 15:11]
MySQL Verification Team
Thank you for the bug report. Are you using the latest released version? otherwise upgrade a try again. If you still have the same issue please provide the insert data, run the query within the mysql client and print here the result. Thanks in advance.
[5 Mar 2008 15:24]
Łukasz Lato
Insert data: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL auto_increment, `date` date default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci; INSERT INTO `test` VALUES (1,'2008-03-15'),(2,'2008-03-16'),(3,NULL),(4,'2008-03-17'),(5,'2008-03-01');
[5 Mar 2008 15:25]
Łukasz Lato
I use the latest stable version and promblem still exists
[5 Mar 2008 15:29]
Łukasz Lato
mysql> select date(date) >= date('2008-03-15 11:11:11') as r from test;
+------+
| r |
+------+
| 1 |
| 1 |
| NULL |
| NULL |
| NULL |
+------+
5 rows in set (0.00 sec)
mysql>
[5 Mar 2008 19:27]
Sveta Smirnova
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead. Thank you for your interest in MySQL. Duplicate of bug #29898
Description: I have table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL auto_increment, `date` date default NULL) ENGINE=InnoDB For example I have 5 rows in test table: (1, '2008-03-15'), (1, '2008-03-16'), (1, null), (1, '2008-03-17'), (1, '2008-03-18') When I execute this query: SELECT DATE(date) >= DATE('2008-03-15 11:25:38') as r from test; I have this results: |r| 1 1 null null null How to repeat: executing my query Suggested fix: I think this is correct results: |r| 1 1 null 1 1 I use: SELECT DATE(IFNULL(date, '0001-01-01')) >= DATE('2008-03-15 11:25:38') as r from test; to temporarly solve my problem.