Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4
Description
Look at testcase for MDEV-20557 in subselect_exists2in.result:
create table t1 (id int, id2 int); |
create table t2 (id int, id2 int, a int); |
create table t3 (id int); |
create table t4 (id int); |
select (select 1 from t1 where (exists |
(select 1 from t2 |
where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt |
from t3; |
ERROR 42000: This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' |
It is wrong that this query produces an error.
Running the select with optimizer_switch='exists_to_in=off' produces no error.
Also, swapping the order of conjuncts in the subquery's WHERE clause causes no error to be produced:
MariaDB [test]> select (select 1 from t1 where (exists
|
-> (select 1 from t2
|
-> where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt
|
-> from t3;
|
ERROR 1235 (42000): This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY'
|
 |
MariaDB [test]>
|
MariaDB [test]> select (select 1 from t1 where (exists
|
-> (select 1 from t2
|
-> where t2.id2 = t1.id2 and t2.a = (select t4.id from t4 where t4.id = t3.id)))) dt
|
-> from t3;
|
Empty set (0.001 sec)
|
The testcase is from MDEV-20557. But we've only pushed a testcase for that bug.
The patch that has introduced the problem is the fix for MDEV-23221.
That patch has added this logic into find_inner_outer_equalities():
+ if there is just one outer_expr=inner_expr pair, then outer_expr can have a
|
+ subselect in it. If there are many such pairs, then none of outer_expr can
|
+ have a subselect in it. If we allow this, the query will fail with an error:
|
+
|
+ This version of MariaDB doesn't yet support 'SUBQUERY in ROW in left
|
+ expression of IN/ALL/ANY'
|
The problem is that it doesn't implement it correctly. If the first equality has a subselect and the second one doesn't, the code in find_inner_outer_equalities() still allows this.