Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Cannot Reproduce
-
5.3.12, 5.5, 10.0, 10.1
Description
MySQL has special logic for IS NULL operator with 0000-00-00 dates:
http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_is-null
For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:
SELECT * FROM tbl_name WHERE date_column IS NULL
However, it does not work with MERGE views (and derived_merge in 5.7).
MariaDB [test]> # This works:
|
MariaDB [test]> SELECT * FROM t1 WHERE d IS NULL;
|
+------------+
|
| d |
|
+------------+
|
| 0000-00-00 |
|
| 0000-00-00 |
|
+------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> SELECT * FROM v_temptable WHERE d IS NULL;
|
+------------+
|
| d |
|
+------------+
|
| 0000-00-00 |
|
| 0000-00-00 |
|
+------------+
|
2 rows in set (0.01 sec)
|
|
|
MariaDB [test]> # This does not work:
|
MariaDB [test]> SELECT * FROM v_merge WHERE d IS NULL;
|
Empty set (0.00 sec)
|
MariaDB [test]> # This does not work:
|
|
|
MariaDB [test]> SET optimizer_switch = 'derived_merge=on';
|
Query OK, 0 rows affected (0.00 sec)
|
MariaDB [test]> SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE d IS NULL;
|
Empty set (0.00 sec)
|
|
|
MariaDB [test]> # This works:
|
MariaDB [test]> SET optimizer_switch = 'derived_merge=off';
|
Query OK, 0 rows affected (0.00 sec)
|
MariaDB [test]> SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE d IS NULL;
|
+------------+
|
| d |
|
+------------+
|
| 0000-00-00 |
|
| 0000-00-00 |
|
+------------+
|
2 rows in set (0.01 sec)
|
|
Test case |
set sql_mode= ''; |
|
|
DROP TABLE IF EXISTS t1, v_merge, v_temptable; |
|
|
CREATE TABLE t1 (d DATE NOT NULL) ENGINE=MyISAM; |
INSERT INTO t1 VALUES ('0000-00-00'), ('0000-00-00'); |
|
|
CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM t1; |
CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT * FROM t1; |
|
|
# This works:
|
|
|
SELECT * FROM t1 WHERE d IS NULL; |
SELECT * FROM v_temptable WHERE d IS NULL; |
|
|
# This does not work: |
|
|
SELECT * FROM v_merge WHERE d IS NULL; |
|
|
|
|
# This does not work: |
|
|
SET optimizer_switch = 'derived_merge=on'; |
SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE d IS NULL; |
|
|
# This works:
|
|
|
SET optimizer_switch = 'derived_merge=off'; |
SELECT * FROM ( SELECT * FROM t1 ) AS sq WHERE d IS NULL; |