Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8707

Wrong result for SELECT..WHERE varchar_column=DATE'2001-01-01' AND varchar_column='2001-01-01'

        Details

          Description

          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                blocks

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8728 Fix a number of problems in equal field and equal expression propagation

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                  Activity

                  There are no comments yet on this issue.

                    People

                    • Assignee:
                      bar Alexander Barkov
                      Reporter:
                      bar Alexander Barkov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: