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

Wrong WHERE optimization with simple CASE and searched CASE

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.3.11
    • Optimizer
    • None

    Description

      WHERE optimization does not seem to distinguish between a simple CASE and a searched CASE with the same arguments.

      I create and populate a table:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT, b INT, KEY(a));
      INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
      

      A query with a simple CASE:

      SELECT * FROM t1 WHERE CASE a WHEN b THEN 1 END=1;
      

      +------+------+
      | a    | b    |
      +------+------+
      |    1 |    1 |
      |    2 |    2 |
      |    3 |    3 |
      +------+------+
      

      returns 3 rows.

      A query with a searched CASE:

      SELECT * FROM t1 WHERE CASE WHEN a THEN b ELSE 1 END=3;
      

      +------+------+
      | a    | b    |
      +------+------+
      |    3 |    3 |
      +------+------+
      

      returns one row.

      Now I combine both conditions using AND:

      SELECT * FROM t1 WHERE
        CASE a WHEN b THEN 1 END=1
      AND
        CASE WHEN a THEN b ELSE 1 END=3;
      

      Empty set (0.00 sec)
      

      The expected result is to return one row, as in the second query.

      The condition was erroneously optimized to WHERE 0;

      EXPLAIN EXTENDED
      SELECT * FROM t1 WHERE
        CASE a WHEN b THEN 1 END=1
      AND
        CASE WHEN a THEN b ELSE 1 END=3;
      SHOW WARNINGS;
      

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

      Attachments

        Issue Links

          Activity

            People

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