Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11361

Equal condition propagation does not work for DECIMAL and temporal dynamic SQL parameters

Details

    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

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16426 Optimizer erroneously treats equal constants of different formats as same

          • Major - Major loss of function.
          • Closed

          Activity

            There are no comments yet on this issue.

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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