Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
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) |
|
+-------+------+--------------------------------------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} Propagation did not work, the condition was not simplified. |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} Propagation did not work, the condition was not simplified. |
| Description |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} Propagation did not work, the condition was not simplified. |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} Propagation did not work, the condition was not simplified. This happens because {{DECIMAL_VALIE}} is not handled in {{Item_param::clone_item}}. |
| Description |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} Propagation did not work, the condition was not simplified. This happens because {{DECIMAL_VALIE}} is not handled in {{Item_param::clone_item}}. |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10.00) | +-------+------+--------------------------------------------------------------------------------------+ {nofornat} |
| Description |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10.00) | +-------+------+--------------------------------------------------------------------------------------+ {nofornat} |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10.00) | +-------+------+--------------------------------------------------------------------------------------+ {noformat} |
| Link |
This issue relates to |
| Description |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10.00) | +-------+------+--------------------------------------------------------------------------------------+ {noformat} |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10.00) | +-------+------+--------------------------------------------------------------------------------------+ {noformat} This happens because {{DECIMAL_VALUE}} is not handled in {{Item_param::eq()}} |
| Description |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10.00) | +-------+------+--------------------------------------------------------------------------------------+ {noformat} This happens because {{DECIMAL_VALUE}} is not handled in {{Item_param::eq()}} |
I am testing equal expression propagation for various data types:
h3. INT {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine, the condition was simplified. h3. DOUBLE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10) | +-------+------+-----------------------------------------------------------------------------------+ {noformat} Works fine. h3. DATE {code:sql} 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; {code} {noformat} +-------+------+-----------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2001-01-01) | +-------+------+-----------------------------------------------------------------------------------------------+ {noformat} Works fine. h3. DECIMAL {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | 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)) | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ {noformat} 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: {code:sql} 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; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` + 1) = 10.00) | +-------+------+--------------------------------------------------------------------------------------+ {noformat} |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.2 [ 14601 ] |
| Summary | Equal condition propagation does not work for DECIMAL dynamic SQL parameter | Equal condition propagation does not work for DECIMAL and temporal dynamic SQL parameter |
| Summary | Equal condition propagation does not work for DECIMAL and temporal dynamic SQL parameter | Equal condition propagation does not work for DECIMAL and temporal dynamic SQL parameters |
| Link |
This issue relates to |
| Comment |
[ The same problem is repeatable for temporal types:
{code:sql} DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME); INSERT INTO t1 VALUES (1),(2),(3); EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIME'10:20:30',TIME'10:20:30'; SHOW WARNINGS; {code} {noformat} +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) <=> coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ {noformat} ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Affects Version/s | 10.4 [ 22408 ] |
| Component/s | Data types [ 13906 ] |
| Epic Link | MDEV-21071 [ 80504 ] |
| Workflow | MariaDB v3 [ 78463 ] | MariaDB v4 [ 140148 ] |