[MDEV-8707] Wrong result for SELECT..WHERE varchar_column=DATE'2001-01-01' AND varchar_column='2001-01-01' - Jira

XML

Word

Printable

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(40));

INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-01x');

SELECT * FROM t1 WHERE a=DATE'2001-01-01' AND a='2001-01-01';

returns:

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

| a           |

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

| 2001-01-01  |

| 2001-01-01x |

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

The second row is obviously wrong.

If I write WHERE the other way round:

SELECT * FROM t1 WHERE a='2001-01-01' AND a=DATE'2001-01-01';

it works fine and correctly returns one row:

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

| a          |

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

| 2001-01-01 |

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

The same problem is repeatable with ENUM type:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a ENUM('2001-01-01','2001-01-01x'));

INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-01x');

SELECT * FROM t1 WHERE a=DATE'2001-01-01' AND a='2001-01-01';

SELECT * FROM t1 WHERE a='2001-01-01' AND a=DATE'2001-01-01';

Another example using ROW syntax:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(20));

INSERT INTO t1 VALUES ('2001-01-01x'),('2001-01-01');

SELECT * FROM t1 WHERE (a,a)=('2001-01-01x',DATE'2001-01-01');

SELECT * FROM t1 WHERE (a,a)=(DATE'2001-01-01','2001-01-01x')

The first query correctly returns one row, the second query erroneously returns two rows.

Another example:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(40),b VARCHAR(40));

INSERT INTO t1 VALUES ('2001-01-01','2001-01-01x');

SELECT * FROM t1 WHERE a=b AND a=DATE'2001-01-01';

returns:

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

| a          | b           |

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

| 2001-01-01 | 2001-01-01x |

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

This is wrong. The expected result is empty set.

The same problem happens with ENUM:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a ENUM('2001-01-01','2001-01-01x'),b ENUM('2001-01-01','2001-01-01x'));

INSERT INTO t1 VALUES ('2001-01-01','2001-01-01x');

SELECT * FROM t1 WHERE a=b AND a=DATE'2001-01-01';

It erroneously returns one row. The expected result it to return no rows.

blocks

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