[MDEV-17411] Wrong WHERE optimization with simple CASE and searched CASE Created: 2018-10-09  Updated: 2018-10-11  Resolved: 2018-10-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.3.11

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16885 A method of CASE searched/simple dete... Closed

 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 |
 
+-------+------+-------------------------------------------------------------------------------+


Generated at Thu Feb 08 08:36:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.