MDEV-8740 Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011'

MDEV-8754 Wrong result for SELECT..WHERE year_field=2020 AND NULLIF(year_field,2010)='2020'
Problems:
1. Item_func_nullif stored a copy of args[0] in a private member m_args0_copy,
   which was invisible for the inherited Item_func menthods, like
   update_used_tables(). As a result, after equal field propagation
   things like Item_func_nullif::const_item() could return wrong result
   and a non-constant NULLIF() was erroneously treated as a constant
   at optimize_cond() time.
   Solution: removing m_args0_copy and storing the return value item
   in args[2] instead.
2. Equal field propagation did not work well for Item_fun_nullif.
   Solution: using ANY_SUBST for args[0] and args[1], as they are in
   comparison, and IDENTITY_SUBST for args[2], as it's not in comparison.

1 files changed, 56 insertions, 0 deletions

diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index e5ce6a10f09..04044602a68 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -1409,5 +1409,61 @@ Warnings:
 Note	1003	select isnull((case when convert(`test`.`t1`.`a` using utf8) = (_utf8'a' collate utf8_bin) then NULL else `test`.`t1`.`a` end)) AS `expr` from `test`.`t1`
 DROP TABLE t1;
 #
+# MDEV-8740 Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011'
+#
+CREATE TABLE t1 (a YEAR);
+INSERT INTO t1 VALUES (2010),(2011);
+SELECT a=10 AND NULLIF(a,2011.1)='2011' AS cond FROM t1;
+cond
+0
+0
+SELECT * FROM t1 WHERE a=10;
+a
+2010
+SELECT * FROM t1 WHERE NULLIF(a,2011.1)='2011';
+a
+SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';
+a
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)=CONCAT('2011',RAND());
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and (<cache>((case when 2010 = 2011 then NULL else '2010' end)) = concat('2011',rand())))
+DROP TABLE t1;
+#
+# MDEV-8754 Wrong result for SELECT..WHERE year_field=2020 AND NULLIF(year_field,2010)='2020'
+#
+CREATE TABLE t1 (a YEAR);
+INSERT INTO t1 VALUES (2010),(2020);
+SELECT * FROM t1 WHERE a=2020;
+a
+2020
+SELECT * FROM t1 WHERE NULLIF(a,2010)='2020';
+a
+2020
+SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020';
+a
+2020
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2020)
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)=CONCAT('2020',RAND());
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2020) and (<cache>((case when 2020 = 2010 then NULL else '2020' end)) = concat('2020',rand())))
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #