Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Description
CREATE TABLE t1 (a varchar(1)); |
INSERT INTO t1 VALUES ('m'),('v'); |
|
CREATE TABLE t2 (b int, c varchar(1)); |
INSERT INTO t2 VALUES (2,'m'),(1,'x'); |
|
CREATE VIEW v2 AS SELECT * FROM t2; |
|
PREPARE stmt FROM "SELECT STRAIGHT_JOIN * FROM t1 WHERE EXISTS ( SELECT b FROM v2 WHERE c = t1.a )"; |
EXECUTE stmt; |
EXECUTE stmt; |
|
# Cleanup
|
DROP VIEW v2; |
DROP TABLE t1, t2; |
10.3 0ca3aaa7 |
EXECUTE stmt; |
a
|
m
|
EXECUTE stmt; |
a
|
The query is expected to return the row.
If we change the query to EXPLAIN EXTENDED, we'll see
EXECUTE stmt; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where |
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 |
Warnings:
|
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 |
Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`c` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c`)))) |
EXECUTE stmt; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where |
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 |
Warnings:
|
Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`b`)))) |
Note `c` changing to `b`.
Attachments
Issue Links
- is blocked by
-
MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS
- Stalled