Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
Description
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.
Attachments
Issue Links
- relates to
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-