[MDEV-32924] EXISTS-to-IN transformation can produce not-supported error Created: 2023-12-01  Updated: 2023-12-05

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: 10.4, 10.11

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: optimizer-easy


 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.

 Comments   
Comment by Sergei Petrunia [ 2023-12-01 ]

Suppose we have 

EXISTS  (
 
  SELECT 1
 
  FROM t1
 
  WHERE
 
    t1.col1 = outer_tbl.col1 AND --(1)
 
    t1.col2 = (SELECT ... FROM t2 WHERE ... outer_tbl.col2...)  AND -- (2)
 
    t1.col3 = (SELECT ... FROM t3 WHERE ... outer_tbl.col3...)  AND -- (3)
 
    t1.col4 = outer_tbl.col4 --(4)

In this case, EXISTS-to-IN can pick either:

  • (1) and (4) - everything that doesn't have a subquery.
  • just (2)
  • just (3)
    .

Its hard to make a cost-based choice here. and should we bother?

  • Solution #1: assume the first conjuncts are more important. Put conjuncts into the WHERE honoring the "either take one expression with subselect or any expressions without subselects".
  • Solution #2: prefer non-subselect equalities.

Will raise this on the optimizer call.

Generated at Thu Feb 08 10:35:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.