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

Wrong WHERE optimization with simple CASE and searched CASE

    XMLWordPrintable

    Details

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

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

                Dates

                Created:
                Updated:
                Resolved: