[MDEV-8699] Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x' - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1, 10.0, 5.5
Fix Version/s: 10.1.7
Component/s: Optimizer
Labels:
- propagation
- upstream-not-supported

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.

Gliffy Diagrams

Attachments

Issue Links

blocks

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

Closed

Activity

Hide

Permalink

Alexander Barkov added a comment - 2015-09-12 23:48

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

Show

Alexander Barkov added a comment - 2015-09-12 23:48 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')) | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'