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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0, 10.1, 10.2
    • 10.4.0
    • Optimizer
    • 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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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