| Bug #43589 | Innodb returns zero values for calendar types with IS NULL | ||
|---|---|---|---|
| Submitted: | 12 Mar 2009 11:53 | Modified: | 12 Mar 2009 13:16 |
| Reporter: | Nidhi Shrotriya | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 6.0.11-bzr | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Mar 2009 11:54]
Nidhi Shrotriya
Test Case
Attachment: update_calendar_bug_innodb.test (application/octet-stream, text), 1.01 KiB.
[12 Mar 2009 13:16]
Valeriy Kravchuk
Thank you for the problem report. As this happens only when "Using index condition" optimization is used on InnoDB table:
mysql> CREATE TABLE t1(c1 DATE NOT NULL PRIMARY KEY, c2 DATE NULL, c3 INT, INDEX
idx2(c2));
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08'
,NULL,5);
Query OK, 2 rows affected, 1 warning (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> SELECT * FROM t1 WHERE c2 IS NULL;
+------------+------------+------+
| c1 | c2 | c3 |
+------------+------------+------+
| 2001-01-08 | 0000-00-00 | 5 |
+------------+------------+------+
1 row in set (0.24 sec)
mysql> SELECT * FROM t1;
+------------+------------+------+
| c1 | c2 | c3 |
+------------+------------+------+
| 2001-01-06 | 2001-01-07 | 4 |
| 2001-01-08 | NULL | 5 |
+------------+------------+------+
2 rows in set (0.00 sec)
mysql> explain SELECT * FROM t1 WHERE c2 IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: idx2
key: idx2
key_len: 4
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)
I'd say it is, indeed, a duplicate of bug #43249.
Description: SELECT * FROM t1 WHERE c2 IS NULL; c1 c2 c3 2001-01-06 00:00:00 0000-00-00 00:00:00 4 2001-01-08 00:00:00 NULL 5 With the test case attached. It also appears at different place with other test case as CREATE TABLE t1(c1 DATE NOT NULL PRIMARY KEY, c2 DATE NULL, c3 INT, INDEX idx2(c2)); INSERT INTO t1 VALUES('2001-01-06','2001-01-07 09:30:00',4),('2001-01-08',NULL,5); SELECT * FROM t1 WHERE c2 IS NULL; ---problem appears here UPDATE t1 SET c2=NULL WHERE c1='2001-01-06 00:00:00'; SELECT * FROM t1 WHERE c2 IS NULL; Also looks like all calendar types are affected. It looks similar to bug # 43249, though the scenario is different so please mark it duplicate if it is same issue. How to repeat: Please find attached the test case file or a simplest case as mentioned in the description, though it appears at different places.