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

    XMLWordPrintable

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

          Activity

            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.