Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4
-
Unexpected results
Description
All rows of t2 set b=1 and function f1() returns 1, 2, 3, ... on successive calls. The subquery returns 1 and the query must return t1.a=1.
create table t1 (a int); |
insert into t1 values (1),(2); |
create table t2 (b int); |
insert into t2 select 1 from seq_1_to_80; |
delimiter |
|
create function f1() returns int not deterministic |
begin |
set @calls= @calls + 1; |
return @calls; |
end| |
delimiter ;
|
|
|
set @calls= 0; |
select t1.a from t1 where t1.a in (select b from t2 where f1() > 1); |
select @calls; |
set optimizer_switch='firstmatch=off,loosescan=off'; |
set @calls= 0; |
select t1.a from t1 where t1.a in (select b from t2 where f1() > 1); |
select @calls; |
|
With the default optimizer_switch the query returns 1. But with firstmatch=off,loosescan=off the query returns the empty set. Under either configuration the result should be 1.
The equivalent scenario on MySQL 9.6.0 produces the correct result of 1 for both cases:
create table t1 (a int); |
insert into t1 values (1),(2); |
create table t2 (b int); |
insert into t2 with recursive s(n) as (select 1 union all select n+1 from s where n<80) select 1 from s; |
create table t3 (c int); |
insert into t3 values (1); |
delimiter |
|
create function f1() returns int not deterministic no sql |
begin |
set @calls= @calls + 1; |
return @calls; |
end| |
delimiter ;
|
|
|
set @calls= 0; |
select t1.a from t1 where t1.a in (select b from t2 where f1() > 1); |
select @calls; |
set optimizer_switch='firstmatch=off,loosescan=off'; |
set @calls= 0; |
select t1.a from t1 where t1.a in (select b from t2 where f1() > 1); |
select @calls; |
