Details
Major
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.
Gliffy Diagrams
Attachments
Issue Links
- blocks
-
MDEV-8728 Fix a number of problems in equal field and equal expression propagation
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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:
Equal fields propagation did not work.
The expected result is: