Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
None
Description
Found this bug while working on MDEV-6990 and MDEV-6989.
Item_func_ifnull::fix_length_and_dec() has this code:
m_args0_copy->cmp_context= args[1]->cmp_context=
|
item_cmp_type(m_args0_copy->result_type(), args[1]->result_type());
|
It is pointless to set cmp_context for m_args0_copy, because it does not take part it any comparison. It's only used to return a value.
It should be args[0]->cmp_context set instead.
Because args[0]->cmp_context is not set there is a difference in:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a YEAR);
|
INSERT INTO t1 VALUES (2010),(2020);
|
SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL;
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL;
|
SHOW WARNINGS;
|
and
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a YEAR);
|
INSERT INTO t1 VALUES (2010),(2020);
|
SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL;
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL;
|
SHOW WARNINGS;
|
The first script returns:
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and isnull(nullif(2010,`test`.`t1`.`a`))) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
|
The second script returns:
+-------+------+---------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1) |
|
+-------+------+---------------------------------------------------------------------------------------+
|
In the first example with IFNULL, can_change_cond_ref_to_const() fails to optimize the condition.
In the second example with CASE, can_change_cond_ref_to_const() correctly optimizes away the second part of the condition.
Additionally, the code in Item_func_ifnull::fix_length_and_dec() and in Item_bool_func2::fix_length_and_dec() is not identical in more pieces, which exposes in this behaviour difference.
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT NULLIF(TIMESTAMP'2001-01-01 00:00:00',1) AS a;
|
returns no warnings.
While this one:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT CASE WHEN TIMESTAMP'2001-01-01 00:00:00'=1 THEN NULL ELSE TIMESTAMP'2001-01-01 00:00:00' END AS a;
|
correctly returns a warning:
mysql> SHOW WARNINGS;
|
+---------+------+-------------------------------+
|
| Level | Code | Message |
|
+---------+------+-------------------------------+
|
| Warning | 1292 | Incorrect datetime value: '1' |
|
+---------+------+-------------------------------+
|