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

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0, 10.1, 10.2
    • Fix Version/s: 10.4.0
    • Component/s: Optimizer
    • Labels:
      None

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (1),(2),(3);
      EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1,1;
      SHOW WARNINGS;
      

      +-------+------+--------------------------------------------------------+
      | Level | Code | Message                                                |
      +-------+------+--------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |
      +-------+------+--------------------------------------------------------+
      

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DOUBLE);
      INSERT INTO t1 VALUES (1),(2),(3);
      EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1e0,1e0;
      SHOW WARNINGS;
      

      +-------+------+--------------------------------------------------------+
      | Level | Code | Message                                                |
      +-------+------+--------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |
      +-------+------+--------------------------------------------------------+
      

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL(10,1));
      INSERT INTO t1 VALUES (1),(2),(3);
      EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.0;
      SHOW WARNINGS;
      

      +-------+------+------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                    |
      +-------+------+------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((1.0 + `test`.`t1`.`a`) <=> (1.0 + `test`.`t1`.`a`)) |
      +-------+------+------------------------------------------------------------------------------------------------------------+
      

      Notice, the true conditions where correctly eliminated for INT and DOUBLE, but not for DECIMAL.
      This happens because DECIMAL_VALUE is not handled in Item_param::eq() or Item_param::value_eq() in 10.3

      The problem is also repeatable with a parameter on the left side and a literal on the right side:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL(10,1));
      INSERT INTO t1 VALUES (1),(2),(3);
      EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.0;
      SHOW WARNINGS;
      

      +-------+------+------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                              |
      +-------+------+------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1.0 + `test`.`t1`.`a` <=> 1.0 + `test`.`t1`.`a` |
      +-------+------+------------------------------------------------------------------------------------------------------+
      

      This happens for the same reason: DECIMAL_VALUE is not handler in Item_param::eq() / Item_param::value_eq().

      Note, if I now put the listeral on the left side and the parameter in the right side, it works fine:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL(10,1));
      INSERT INTO t1 VALUES (1),(2),(3);
      EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.0;
      SHOW WARNINGS;
      

      +-------+------+--------------------------------------------------------+
      | Level | Code | Message                                                |
      +-------+------+--------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |
      +-------+------+--------------------------------------------------------+
      

      Now execution goes through Item_decimal::eq() which correctly detects an Item_param with the same decimal value as equal.

      Summary:

      • Propagation does not work for two parameters
      • Propagation works with a literal on the left side and a parameter on the right side
      • Propagation does not work with a parameter on the left side and a literal on the right side.

      This happens because Item_param::eq() and Item_literal::eq() are not symmetric for DECIMAL.

        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:
                  Resolved: