[MDEV-11361] Equal condition propagation does not work for DECIMAL and temporal dynamic SQL parameters - Jira

XML

Word

Printable

I am testing equal expression propagation for various data types:

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.

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.

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.

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) |

+-------+------+--------------------------------------------------------------------------------------+

relates to

MDEV-4912 Data type plugin API version 1

MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters

MDEV-16426 Optimizer erroneously treats equal constants of different formats as same

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.