|
I am testing equal expression propagation for various data types:
INT
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2),(3),(4);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE a+1=? AND a+1<=10' USING 10;
|
SHOW WARNINGS;
|
+-------+------+-----------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) |
|
+-------+------+-----------------------------------------------------------------------------------+
|
Works fine, the condition was simplified.
DOUBLE
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE);
|
INSERT INTO t1 VALUES (1),(2),(3),(4);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE a+1=? AND a+1<=10' USING 10e0;
|
SHOW WARNINGS;
|
+-------+------+-----------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) |
|
+-------+------+-----------------------------------------------------------------------------------+
|
Works fine.
DATE
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DATE);
|
INSERT INTO t1 VALUES (1),(2),(3),(4);
|
EXECUTE IMMEDIATE "EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=? AND COALESCE(a) <=DATE'2001-01-01'" USING DATE'2001-01-01';
|
SHOW WARNINGS;
|
+-------+------+-----------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) |
|
+-------+------+-----------------------------------------------------------------------------------------------+
|
Works fine.
DECIMAL
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(10,2));
|
INSERT INTO t1 VALUES (1),(2),(3),(4);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE a+1=? AND a+1<=10' USING 10.00;
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (((`test`.`t1`.`a` + 1) = 10.00) and ((`test`.`t1`.`a` + 1) <= 10)) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
|
Propagation did not work, the condition was not simplified.
This happens because DECIMAL_VALIE is not handled in Item_param::clone_item.
Note, if I change the parameter to an explicit DECIMAL literal, it correctly propagates equal conditions:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(10,2));
|
INSERT INTO t1 VALUES (1),(2),(3),(4);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE a+1=10.00 AND a+1<=10';
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10.00) |
|
+-------+------+--------------------------------------------------------------------------------------+
|
|