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

ENUM does not perform equal field propagation in numeric context

    XMLWordPrintable

Details

    Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a ENUM('5','6'));
      INSERT INTO t1 VALUES ('5'),('6');
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2 AND a<>1;
      SHOW WARNINGS;

      returns

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

      It should be safe to rewrite the condition to:

      SELECT * FROM t1 WHERE a=2 AND 2<>1;

      and then remove the "AND 2<>1" part as a true constant:

      SELECT * FROM t1 WHERE a=2;

      Another example:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a ENUM('a','b','100'));
      INSERT INTO t1 VALUES ('a'),('b'),('100');
      EXPLAIN EXTENDED
      SELECT * FROM t1 WHERE CASE a WHEN 3 THEN 1 ELSE 0 END AND a=3;

      returns

      +-------+------+----------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                          |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((case `test`.`t1`.`a` when 3 then 1 else 0 end) and (`test`.`t1`.`a` = 3)) |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------+

      It should be safe to propagate a=3 into CASE in this example.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.