Re: [I] Feature request: Support for lateral joins [datafusion]

["alamb (via GitHub)" <gi...@apache.org>]

alamb commented on issue #10048:
URL: https://github.com/apache/datafusion/issues/10048#issuecomment-2541729713

   There is some more discussion about this here: https://github.com/apache/datafusion/issues/13659#issuecomment-2541722186
   
   Summarizing: Postgres appears to plan this type of query as a NestedLoops join:
   
   ```sql
   postgres=# create table unnest_test (c1 int[]);
   CREATE TABLE
   postgres=# insert into unnest_test values (ARRAY [1,2,3]);
   INSERT 0 1
   postgres=# select * from unnest_test u, unnest(u.c1);
      c1    | unnest 
   ---------+--------
    {1,2,3} |      1
    {1,2,3} |      2
    {1,2,3} |      3
   (3 rows)
   ```
   
   ```sql
    Nested Loop  (cost=0.00..295.60 rows=13600 width=36)
      Output: u.c1, unnest.unnest
      ->  Seq Scan on public.unnest_test u  (cost=0.00..23.60 rows=1360 width=32)
            Output: u.c1
      ->  Function Scan on pg_catalog.unnest  (cost=0.00..0.10 rows=10 width=4)
            Output: unnest.unnest
            Function Call: unnest(u.c1)
   ```
   
   🤔 
   
   I think the postgres join operator re-evaluates the right input (`pg_catalog.unnest`) on each input row to the join which is how the lateral joins can refer to the outer query
   
   The current DataFusion join operators don't run the input over and over again -- they normally run each input and then combine them (they don't restart inputs)
   
   So in a query like
   ```sql
   select * from t1, unnest(t1.c1)
   ```
   
   I think we would need a (new) type of JoinExec that actually does run the outer query (aka `unnest(t1.c1)` for each input row of `t1`) 🤔 


-- 
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@datafusion.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org