Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.6
-
Fix Version/s: 10.0.11
-
Component/s: None
-
Labels: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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It is correct explain:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2` `t2x` join `test`.`t2` `t2y`) where 1
It is second execution one:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2` `t2x` join `test`.`t2` `t2y`) where ((`test`.`t1`.`a` = 1) and (`test`.`t2y`.`b` = 1))
The problem is in rolling back changes after we changed equations in WHERE with constants to remove it.
( (1=1) turns to (1=t1.a))
it is sent for review
Materialization used to execute IN (got from exists).