Description:
Hello,
Recently we find a case that result of a function produces different value when it's placed in select_list or where_clause of select statements.
How to repeat:
(1) create a table
CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `c0` decimal(10,0) unsigned zerofill DEFAULT '1240363572', `c1` varbinary(78) DEFAULT NULL, `c2` varchar(83) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, `c3` tinyint(1) DEFAULT '1', `c4` int DEFAULT NULL, `GDB_BID` int NOT NULL DEFAULT '-1', `GTID` bigint unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `c3` (`c3`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
(2) insert a record
insert into t1 values (1, 0498879434, NULL, 'xxxx', -128, NULL, -1, 4295476708);
(3) do some test about function NULLIF(ASCII))
mysql> select ((NULLIF(ASCII(-327253980), c2)) is NULL) expr from t1;
+------+
| expr |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
# well, now we can conclude that expression `(NULLIF(ASCII(-327253980), c2))` is not null
mysql> select 1 from t1 where (NULLIF(ASCII(-327253980), c2)) is NULL;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
# however, now it seems that expression `(NULLIF(ASCII(-327253980), c2))` produce a null result, conflicted with result of previous query
Suggested fix:
something is wrong with Item::cache_const_expr_transformer in sql optimizer
