Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
Description
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (10),(11),(12);
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND IF(a=10,1,0)=1;
|
SHOW WARNINGS;
|
returns
+-------+------+-----------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 10) |
|
+-------+------+-----------------------------------------------------------------------------+
|
I.e. the field equal value of 10 was propagated into IF, then the condition part with IF was evaluated as a constant and removed from the condition.
If I use VARCHAR in the same scenario:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10));
|
INSERT INTO t1 VALUES ('10'),('11'),('12');
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='10' AND IF(a='10',1,0)=1;
|
SHOW WARNINGS;
|
it returns:
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '10') and (if((`test`.`t1`.`a` = '10'),1,0) = 1)) |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------+
|
Propagation did not work.
Tracing in gdb shows the following:
(gdb) b Item_field::subst_argument_checker(unsigned char**)
|
|
|
Breakpoint 3 at 0x555555c46d98: file /home/bar/maria-git/server/sql/item.cc, line 5361.
|
(gdb) c
|
Continuing.
|
|
|
Breakpoint 3, Item_field::subst_argument_checker (this=0x7fff9c007f70,
|
arg=0x7fffd9f04a00) at /home/bar/maria-git/server/sql/item.cc:5361
|
5361 return *arg &&
|
(gdb) p (Subst_constraint) *arg
|
$3 = Item::IDENTITY_SUBST
|
I.e. Item_field::subst_argument_checker() is called with a wrong Subst_constaint value.
The correct value should be Item::ANY_SUBST, because the field appears in a comparison context.
Attachments
Issue Links
- blocks
-
-
- Closed
-
Activity
After the fix this script in MariaDB:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10));
|
INSERT INTO t1 VALUES ('10'),('11'),('12');
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='10' AND IF(a='10',1,0)=1;
|
SHOW WARNINGS;
|
returns
+-------+------+-------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = '10') |
|
+-------+------+-------------------------------------------------------------------------------+
|
MySQL-5.7.8 does not support propagation in the same scenario:
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|
| 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` = '10') and (if((`test`.`t1`.`a` = '10'),1,0) = 1)) |
|
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
|
Another example:
DROP TABLE IF EXISTS t1;CREATE TABLE t1 (a VARCHAR(10));INSERT INTO t1 VALUES ('10'),('11'),('12');EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='10' AND CASE WHEN a='10' THEN 1 ELSE 0 END;SHOW WARNINGS;returns:
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '10') and (case when (`test`.`t1`.`a` = '10') then 1 else 0 end)) |+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------+Equal fields propagation did not work.
The expected result is:
+-------+------+-------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------------------------------------------------+| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = '10') |+-------+------+-------------------------------------------------------------------------------+