[MDEV-8741] Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010 - Jira

XML

Word

Printable

This script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT ZEROFILL);

INSERT INTO t1 VALUES (2010),(2020);

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;

SHOW WARNINGS;

returns

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

| Level | Code | Message                                                                               |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1) |

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

Notice, the a>=2010 part was simplified to constant, but the constant was not removed.

The same problems is repeatable with the YEAR data type:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a YEAR);

INSERT INTO t1 VALUES (2010),(2020);

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;

SHOW WARNINGS;

If I use "INT" instead of "INT ZEROFILL" or "YEAR", it works as expected:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (2010),(2020);

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;

SHOW WARNINGS;

returns:

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

| Level | Code | Message                                                                       |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2010) |

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

as expected.

blocks

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.