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

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.

[MDEV-8707] Wrong result for SELECT..WHERE varchar_column=DATE'2001-01-01' AND varchar_column='2001-01-01' Created: 2015-08-30 Updated: 2015-09-12 Resolved: 2015-08-31
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.0, 10.1
Fix Version/s:	10.1.7