| Bug #35083 | SELECT DATE(column) >= ... ON column column with NULL wrong results | ||
|---|---|---|---|
| Submitted: | 5 Mar 2008 16:04 | Modified: | 5 Mar 2008 20:27 |
| Reporter: | Łukasz Lato | ||
| Status: | Duplicate | ||
| Category: | Server: Parser | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | Any |
| Assigned to: | Target Version: | ||
[5 Mar 2008 16:11]
Miguel Solorzano
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 16: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 16:25]
Łukasz Lato
I use the latest stable version and promblem still exists
[5 Mar 2008 16: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 20: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.