[MDEV-8741] Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010 Created: 2015-09-03  Updated: 2015-09-12  Resolved: 2015-09-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.1.7

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: propagation, upstream

Issue Links:
Blocks
blocks MDEV-8728 Fix a number of problems in equal fie... Closed

 Description   

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.

 Comments   
Comment by Alexander Barkov [ 2015-09-12 ]

MySQL-5.7.8 does not seem to remove the constant part (the result of propagation) from the WHERE condition:

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                                                                                                           |
 
+---------+------+-------------------------------------------------------------------------------------------------------------------+
 
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                 |
 
| Note    | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and (2010 >= 2010)) |
 
+---------+------+-------------------------------------------------------------------------------------------------------------------

Generated at Thu Feb 08 07:29:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.