Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8
-
None
-
None
Description
Two queries that are nearly identical and should be logically equivalent produce different result sets. The issue occurs when a WHERE clause contains an IN condition checking against a subquery on a column with a UNIQUE index.
MariaDB [test]> CREATE TABLE t170 (c2 YEAR, UNIQUE (c2));
|
Query OK, 0 rows affected (0.024 sec)
|
|
|
MariaDB [test]> INSERT t170 () VALUES ('2001');
|
Query OK, 1 row affected (0.007 sec)
|
|
|
MariaDB [test]> SELECT * FROM t170 WHERE (((0 <= (SELECT 682110)) IN (SELECT t170.c2 ca11 FROM t170)));
|
+------+
|
| c2 |
|
+------+
|
| 2001 |
|
+------+
|
1 row in set (0.002 sec)
|
|
|
MariaDB [test]> SELECT * FROM t170 WHERE (((0 <= (SELECT 682110 FROM t170)) IN (SELECT t170.c2 ca11 FROM t170)));
|
Empty set (0.001 sec)
|
|
|
MariaDB [test]> SELECT (((0 <= (SELECT 682110 FROM t170)) IN (SELECT t170.c2 ca11 FROM t170))) FROM t170;
|
+--------------------------------------------------------------------------+
|
| (((0 <= (SELECT 682110 FROM t170)) IN (SELECT t170.c2 ca11 FROM t170))) |
|
+--------------------------------------------------------------------------+
|
| 1 |
|
+--------------------------------------------------------------------------+
|
1 row in set (0.002 sec)
|