SQL case, This feature is not implemented: Physical plan does not support logical expression EXISTS (<subquery>) #5789

Activity

1. jiangzhx

   added

   bugSomething isn't workingSomething isn't working

   on Mar 30, 2023
2. jiangzhx

   closed this as not plannedon Mar 30, 2023
3. jiangzhx

   reopened this on Mar 30, 2023
4. jiangzhx commented on Mar 30, 2023

   

   jiangzhx

   on Mar 30, 2023

   Last edited by jiangzhx

   ContributorAuthor

   More actions

   i'm not sure,does this pr #5419 cause this issue.
5. alamb commented on Mar 30, 2023

   

   alamb

   on Mar 30, 2023

   Contributor

   More actions

   I agree that #5419 likely caused this issue.

   I think the fix is to simply ignore such errors when creating pruning predicates (aka when the predicate is not supported).

   What do you think @crepererum ?
6. jiangzhx

   changed the title [-]This feature is not implemented: Physical plan does not support logical expression EXISTS (<subquery>)[/-] [+]SQL case, This feature is not implemented: Physical plan does not support logical expression EXISTS (<subquery>)[/+] on Mar 31, 2023
7. crepererum commented on Mar 31, 2023

   

   crepererum

   on Mar 31, 2023

   Contributor

   More actions

   To me this looks like a bug. Who's trying to push down / apply a sub-query predicate to a parquet file read? Shouldn't the logical optimizer remove these kind of expressions?
8. jiangzhx commented on Apr 6, 2023

   

   jiangzhx

   on Apr 6, 2023

   Last edited by jiangzhx

   ContributorAuthor

   More actions

   @crepererum I think you're right, just your PR let this issue expose.

   these Exists, InSubquery, ScalarSubquery Expr did not complete the corresponding processing
   in the create_physical_expr method of planner.rs.
   https://github.com/apache/arrow-datafusion/blob/a1c60a1ba98e089d7551637f2a78663e66772d88/datafusion/physical-expr/src/planner.rs#L501-L503

   Before your PR takes effect, it also does not actually handle the subquery scene at datasource
   https://github.com/apache/arrow-datafusion/blob/a1c60a1ba98e089d7551637f2a78663e66772d88/datafusion/core/src/datasource/file_format/mod.rs#L83-L89

   so, i think the way is to optimize non-correlated subquery at decorrelate_where_exists optimizer
   https://github.com/apache/arrow-datafusion/blob/a1c60a1ba98e089d7551637f2a78663e66772d88/datafusion/optimizer/src/decorrelate_where_exists.rs#L185-L191

   the current decorrelate_where_exists only optimize

   SELECT t1.id FROM t1
   WHERE exists
   (
      SELECT t2.id FROM t2 WHERE t1.id = t2.id
   )
   /// and optimizes it into:
   SELECT t1.id
   FROM t1 LEFT SEMI
   JOIN t2
   ON t1.id = t2.id
   

   we need process ScalarSubquery in the create_physical_expr and then rewrite non-correlated exists subquery to ScalarSubquery

   WHERE EXISTS (SELECT A FROM TABLE B WHERE COL1 > 10)
   ///will be rewritten to
   WHERE (SELECT 1 FROM (SELECT A FROM TABLE B WHERE COL1 > 10) LIMIT 1) IS NOT NULL
   
9. alamb commented on Feb 2, 2024

   

   alamb

   on Feb 2, 2024

   Contributor

   More actions

   For the record this still happens:

   (venv-310) andrewlamb@Andrews-MacBook-Pro:~/Downloads$ datafusion-cli
   DataFusion CLI v35.0.0
   ❯ CREATE EXTERNAL TABLE t1 (a INT, b INT) STORED AS CSV LOCATION 'data.csv';
   
   0 rows in set. Query took 0.030 seconds.
   
   ❯ CREATE EXTERNAL TABLE t2 (a INT, b INT) STORED AS CSV LOCATION 'data.csv';
   0 rows in set. Query took 0.001 seconds.
   
   ❯ SELECT a, b FROM t1 WHERE EXISTS (SELECT count(*) FROM t2);
   
   This feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false })
   ❯
   
10. alamb commented on Oct 18, 2024

    

    alamb

    on Oct 18, 2024

    Last edited by alamb

    Contributor

    More actions

    This now works in datafusion 43

    > SELECT a, b FROM t1 WHERE EXISTS (SELECT count(*) FROM t2);
    
    +---+---+
    | a | b |
    +---+---+
    +---+---+
    0 row(s) fetched.
    Elapsed 0.002 seconds.
11. alamb

    closed this as completedon Oct 18, 2024