Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32924

EXISTS-to-IN transformation can produce not-supported error

Details

    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.

      Attachments

        Activity

          psergei Sergei Petrunia added a comment -

          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.

          psergei Sergei Petrunia added a comment - 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.

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.