|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1,1;
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |
|
+-------+------+--------------------------------------------------------+
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DOUBLE);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1e0,1e0;
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |
|
+-------+------+--------------------------------------------------------+
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(10,1));
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.0;
|
SHOW WARNINGS;
|
+-------+------+------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((1.0 + `test`.`t1`.`a`) <=> (1.0 + `test`.`t1`.`a`)) |
|
+-------+------+------------------------------------------------------------------------------------------------------------+
|
Notice, the true conditions where correctly eliminated for INT and DOUBLE, but not for DECIMAL.
This happens because DECIMAL_VALUE is not handled in Item_param::eq() or Item_param::value_eq() in 10.3
The problem is also repeatable with a parameter on the left side and a literal on the right side:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(10,1));
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.0;
|
SHOW WARNINGS;
|
+-------+------+------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1.0 + `test`.`t1`.`a` <=> 1.0 + `test`.`t1`.`a` |
|
+-------+------+------------------------------------------------------------------------------------------------------+
|
This happens for the same reason: DECIMAL_VALUE is not handler in Item_param::eq() / Item_param::value_eq().
Note, if I now put the listeral on the left side and the parameter in the right side, it works fine:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DECIMAL(10,1));
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.0;
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |
|
+-------+------+--------------------------------------------------------+
|
Now execution goes through Item_decimal::eq() which correctly detects an Item_param with the same decimal value as equal.
Summary:
- Propagation does not work for two parameters
- Propagation works with a literal on the left side and a parameter on the right side
- Propagation does not work with a parameter on the left side and a literal on the right side.
This happens because Item_param::eq() and Item_literal::eq() are not symmetric for DECIMAL.
|