Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
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.
Attachments
Issue Links
- blocks
-
-
- Closed
-
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')) |+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+