[#MDEV-8601] Wrong result: special IS NULL logic for 0000-00-00 does not work with merge views and FROM SQ with derived

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;

[MDEV-8601] Wrong result: special IS NULL logic for 0000-00-00 does not work with merge views and FROM SQ with derived_merge Created: 2015-08-11 Updated: 2022-11-25 Resolved: 2022-11-24
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.3.12, 5.5, 10.0, 10.1
Fix Version/s:	N/A

[MDEV-8601] Wrong result: special IS NULL logic for 0000-00-00 does not work with merge views and FROM SQ with derived_merge Created: 2015-08-11 Updated: 2022-11-25 Resolved: 2022-11-24