Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.6
-
None
-
None
Description
Test case:
SET optimizer_switch='exists_to_in=on'; |
|
CREATE TABLE t1 (a INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1),(2); |
|
CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; |
|
CREATE TABLE t2 (b INT) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (2),(3); |
|
SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a ); |
|
PREPARE stmt FROM "SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a )"; |
EXECUTE stmt; |
EXECUTE stmt; |
Results:
MariaDB [test]> SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a ); |
+------+ |
| a |
|
+------+ |
| 2 |
|
+------+ |
1 row in set (0.01 sec) |
|
MariaDB [test]> PREPARE stmt FROM "SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a );"; |
Query OK, 0 rows affected (0.00 sec) |
Statement prepared
|
|
MariaDB [test]> EXECUTE stmt; |
+------+ |
| a |
|
+------+ |
| 2 |
|
+------+ |
1 row in set (0.00 sec) |
|
MariaDB [test]> EXECUTE stmt; |
Empty set (0.00 sec) |
Also reproducible with a subquery instead of the view.