[MDEV-8699] Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x' Created: 2015-08-29  Updated: 2015-09-12  Resolved: 2015-09-07

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 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-not-supported

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

 Description   

Equal field propagation does not work well in a number of cases.

Trailing garbage in string literals

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DATE);
 
INSERT INTO t1 VALUES ('2001-01-01');
 
SELECT * FROM t1 WHERE HEX(a) !='323030312D30312D3031';

returns empty set.
Now If I make the condition even stronger:

SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031' AND a='2001-01-01x';

it erroneously returns one row:

+------------+
 
| a          |
 
+------------+
 
| 2001-01-01 |
 
+------------+

Trailing fractional digits in string literals

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a TIME);
 
INSERT INTO t1 VALUES ('00:00:00');
 
SELECT * FROM t1 WHERE LENGTH(a)!=8;

returns empty set.
Now if I make the condition even stronger:

SELECT a, LENGTH(a) FROM t1 WHERE LENGTH(a)!=8 AND a='00:00:00.000000';

it erroneously returns one row:

+----------+-----------+
 
| a        | LENGTH(a) |
 
+----------+-----------+
 
| 00:00:00 |         8 |
 
+----------+-----------+

Trailing fractional digits in temporal literals

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a TIME);
 
INSERT INTO t1 VALUES ('00:00:00');
 
SELECT * FROM t1 WHERE LENGTH(a)!=8;
 
SELECT * FROM t1 WHERE LENGTH(a)!=8 AND a=TIME'00:00:00.000000';

The first SELECT query returns empty set, the second SELECT query erroneously returns one row.

Leading spaces in string literals

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DATE);
 
INSERT INTO t1 VALUES ('2001-01-01');
 
SELECT * FROM t1 WHERE LENGTH(a)=11;
 
SELECT * FROM t1 WHERE LENGTH(a)=11 AND a=' 2001-01-01';

The first query correctly returns empty set, the second query erroneously returns one row.

Numeric format in string literals

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DATE);
 
INSERT INTO t1 VALUES ('2001-01-01');
 
SELECT * FROM t1 WHERE LENGTH(a)=8;
 
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='20010101';

The first query correctly returns empty set, the second query erroneously returns one row.

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

After the fix, this script in MariaDB 

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a TIME);
 
INSERT INTO t1 VALUES ('00:00:00');
 
SELECT * FROM t1 WHERE LENGTH(a)!=8;
 
SELECT a, LENGTH(a) FROM t1 WHERE LENGTH(a)!=8 AND a='00:00:00.000000';
 
EXPLAIN EXTENDED SELECT a, LENGTH(a) FROM t1 WHERE LENGTH(a)!=8 AND a='00:00:00.000000';
 
SHOW WARNINGS;

returns

+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

MySQL-5.7.8 does not support propagation in the same query:

+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 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`,length(`test`.`t1`.`a`) AS `LENGTH(a)` from `test`.`t1` where ((length(`test`.`t1`.`a`) <> 8) and (`test`.`t1`.`a` = '00:00:00')) |
 
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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