Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
Description
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a YEAR);
|
INSERT INTO t1 VALUES (2010),(2011);
|
SELECT a=10 AND NULLIF(a,2011.1)='2011' AS cond FROM t1;
|
returns
+------+
|
| cond |
|
+------+
|
| 0 |
|
| 0 |
|
+------+
|
Now if I put the same expression into WHERE:
SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';
|
it erroneously returns one row:
+------+
|
| a |
|
+------+
|
| 2010 |
|
+------+
|
The expected result is to have the expression evaluate into the same result in the SELECT list and in WHERE.
The problem is that Item_func_nullif::const_item() returns true and its val_real() is called from eval_const_cond().
In fact, it has constant items in args[0] and args[1]:
(gdb) p args[0]->const_item()
|
$14 = true
|
(gdb) p args[1]->const_item()
|
$15 = true
|
but the returned value referenced by m_args0_copy is not a constant item:
(gdb) p this->m_args0_copy
|
$17 = (Item_field *) 0x7fff98001668
|
The return argument should probably be stored in arg[2] instead, so the standard Item_func methods can see it and update Used_tables_cache taking into account the return value (not only the compared values).
MySQL-5.7.8 seems to return a correct result:
DROP TABLE IF EXISTS t1;CREATE TABLE t1 (a YEAR);INSERT INTO t1 VALUES (2010),(2011);SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';SHOW WARNINGS;but the EXPLAIN output is not really correct:
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------------------------------------------------------------------------+| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. || Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and (nullif(2010,2011) = '2011')) |+---------+------+---------------------------------------------------------------------------------------------------------------------------------+MariaDB returns a more correct result:
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and ((case when 2010 = 2011 then NULL else `test`.`t1`.`a` end) = '2011')) |+-------+------+---------------------------------------------------------------------------