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

XML

Word

Printable

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 |

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

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 |

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

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.

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.

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.

blocks

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