[MDEV-8748] ENUM does not perform equal field propagation in numeric context - Jira

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.

relates to

MDEV-8728 Fix a number of problems in equal field and equal expression propagation

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

MariaDB Server