[GitHub] [arrow-datafusion] alamb commented on a diff in pull request #2344: Add SQL planner support for EXISTS subqueries

[GitBox <gi...@apache.org>]

alamb commented on code in PR #2344:
URL: https://github.com/apache/arrow-datafusion/pull/2344#discussion_r859124195


##########
datafusion/core/src/sql/planner.rs:
##########
@@ -443,16 +463,21 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
         &self,
         t: TableWithJoins,
         ctes: &mut HashMap<String, LogicalPlan>,
+        outer_schema: Option<&DFSchema>,

Review Comment:
   Given that `outer` means something already in terms of joins, I wonder if a name like `outer_query_schema` might potentially cause less confusion



##########
datafusion/core/src/sql/planner.rs:
##########
@@ -4182,4 +4264,36 @@ mod tests {
             \n    TableScan: test projection=None";
         quick_test(sql, expected);
     }
+
+    #[test]
+    fn exists_subquery() {
+        let sql = "SELECT id FROM person p WHERE EXISTS \
+            (SELECT first_name FROM person \
+            WHERE last_name = p.last_name \
+            AND state = p.state)";
+
+        let expected = "Projection: #p.id\
+        \n  Filter: EXISTS (Subquery: Projection: #person.first_name\
+        \n  Filter: #person.last_name = #p.last_name AND #person.state = #p.state\
+        \n    TableScan: person projection=None)\
+        \n    SubqueryAlias: p\
+        \n      TableScan: person projection=None";
+        quick_test(sql, expected);
+    }
+
+    #[test]
+    fn exists_subquery_wildcard() {
+        let sql = "SELECT id FROM person p WHERE EXISTS \
+            (SELECT * FROM person \
+            WHERE last_name = p.last_name \
+            AND state = p.state)";
+
+        let expected = "Projection: #p.id\
+        \n  Filter: EXISTS (Subquery: Projection: #person.id, #person.first_name, #person.last_name, #person.age, #person.state, #person.salary, #person.birth_date, #person.😀\
+        \n  Filter: #person.last_name = #p.last_name AND #person.state = #p.state\
+        \n    TableScan: person projection=None)\
+        \n    SubqueryAlias: p\
+        \n      TableScan: person projection=None";

Review Comment:
   I am not sure what this plan is doing (as it seems to have a `LogicalPlan::Filter` node that has multiple inputs which I didn't think was possible 🤔 )
   
   With a correlated subquery like this I think the classic plan uses a `Semi-Join` like:
   
   ```
   "Projection: #p.id\
     Join(type=Semi): #person.last_name = #p.last_name AND #person.state = #p.state\
       TableScan: person projection=None)\
       SubqueryAlias: p\
         TableScan: person projection=None";
   ```
   
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org