[MDEV-17411] Wrong WHERE optimization with simple CASE and searched CASE - Jira

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 |

+-------+------+-------------------------------------------------------------------------------+

relates to

MDEV-16885 A method of CASE searched/simple detection

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

MariaDB Server