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

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: Prepared Statements
    • Labels:
      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

            Activity

              People

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

                Dates

                • Created:
                  Updated: