Bug #41659 "Range checked for each record" is not used for conditions with outer query refs
Description:
"Range checked for each record" optimization is not used for conditions with outer query references, while it can be easily enabled for such cases.
How to repeat:
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int);
insert into t1 values (1),(2);
create table t3 (a int, b int, filler char(100), key(a), key(b));
insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
insert into t3 values (1,1,'data');
# Try a query with outer reference
mysql> explain select * from t1 where exists (select 1 from t3 where t3.a< t1.a);
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t3
type: index
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 1001
Extra: Using where; Using index
2 rows in set (0.02 sec)
# Now change the outer reference into a local one:
mysql> explain select * from t1 where exists (select 1 from t3, t1 X where X.a=t1.a and t3.a< X.a);
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: X
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t3
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 1001
Extra: Range checked for each record (index map: 0x1)
3 rows in set (0.02 sec)
Suggested fix:
Make range-checked-for-each-record handle outer references.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: "Range checked for each record" optimization is not used for conditions with outer query references, while it can be easily enabled for such cases. How to repeat: create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int); insert into t1 values (1),(2); create table t3 (a int, b int, filler char(100), key(a), key(b)); insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C; insert into t3 values (1,1,'data'); # Try a query with outer reference mysql> explain select * from t1 where exists (select 1 from t3 where t3.a< t1.a); *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t3 type: index possible_keys: a key: a key_len: 5 ref: NULL rows: 1001 Extra: Using where; Using index 2 rows in set (0.02 sec) # Now change the outer reference into a local one: mysql> explain select * from t1 where exists (select 1 from t3, t1 X where X.a=t1.a and t3.a< X.a); *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: X type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where *************************** 3. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t3 type: ALL possible_keys: a key: NULL key_len: NULL ref: NULL rows: 1001 Extra: Range checked for each record (index map: 0x1) 3 rows in set (0.02 sec) Suggested fix: Make range-checked-for-each-record handle outer references.