From 6267be460ab5147e3bc0fffd03e690b3650f2fe2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 12 Jan 2018 15:51:10 -0800 Subject: Fixed mdev-14911: zero_date is considered as NULL, depending on optimizer_switch For DATE and DATETIME columns defined as NOT NULL, "date_notnull IS NULL" has to be modified to: "date_notnull IS NULL OR date_notnull == 0" if date_notnull is from an inner table of outer join); "date_notnull == 0" - otherwise. This must hold for such columns of mergeable views and derived tables as well. So far the code did the above re-writing only for columns of base tables and temporary tables. --- mysql-test/t/func_isnull.test | 48 ++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 47 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test index 326574e0515..4c59fa3cbe8 100644 --- a/mysql-test/t/func_isnull.test +++ b/mysql-test/t/func_isnull.test @@ -38,5 +38,51 @@ INSERT INTO t1( id ) VALUES ( NULL ); SELECT t1.id FROM t1 WHERE (id is not null and id is null ); DROP TABLE t1; -# End of 5.1 tests +--echo # End of 5.1 tests +--echo # +--echo # MDEV-14911: IS NULL for field from mergeable view +--echo # + +CREATE TABLE t1 (d1 datetime NOT NULL); +INSERT INTO t1 VALUES + ('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36'); + +SELECT * FROM t1; +SELECT * FROM t1 WHERE d1 IS NULL; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE d1 IS NULL; +SELECT count(*) FROM t1 WHERE d1 IS NULL; + +CREATE VIEW v1 AS (SELECT * FROM t1); +SELECT * FROM v1; +SELECT * FROM v1 WHERE d1 IS NULL; +EXPLAIN EXTENDED SELECT * FROM v1 WHERE d1 IS NULL; +SELECT count(*) FROM v1 WHERE d1 IS NULL; + +SET @save_optimizer_switch=@@optimizer_switch; + +SET SESSION optimizer_switch='derived_merge=off'; +SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL; +SET SESSION optimizer_switch='derived_merge=on'; +SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL; + +SET optimizer_switch=@save_optimizer_switch; + +CREATE TABLE t2 (d1 datetime NOT NULL); +INSERT INTO t2 VALUES + ('1980-09-03 20:49:36'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36'); + +SELECT * FROM t2 LEFT JOIN t1 ON t2.d1=t1.d1 WHERE t1.d1 IS NULL; +EXPLAIN EXTENDED +SELECT * FROM t2 LEFT JOIN t1 ON t2.d1=t1.d1 WHERE t1.d1 IS NULL; + +SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL; +EXPLAIN EXTENDED +SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL; + +DROP VIEW v1; +DROP TABLE t1,t2; + +--echo # +--echo # End of 5.5 tests +--echo # -- cgit v1.2.1