Feat: Support correlated subqueries #192

Activity

1. KKould commented on Mar 30, 2024

   

   KKould

   on Mar 30, 2024

   Member

   More actions

   It seems that there is something wrong with HashJoin, I'm debugging now, I'll merge this first
2. KKould commented on Mar 30, 2024

   

   KKould

   on Mar 30, 2024

   Last edited by KKould

   Member

   More actions

   It seems that there is something wrong with HashJoin, I'm debugging now, I'll merge this first

   ops, I found the problem. In correlated subqueries, there is no left data in right.
   so this case will return empty data (wrong)

   let fnck_sql = DataBaseBuilder::path(temp_dir.path())
       .build()
       .await?;
   let _ = fnck_sql
       .run("create table t2(id int primary key, a int not null, b int not null);")
       .await?;
   let _ = fnck_sql
       .run("create table t3(id int primary key, a int not null, c int not null);")
       .await?;
   let _ = fnck_sql
       .run("insert into t2 values (0, 1, 2), (3, 4, 5), (8, 0, 0);")
       .await?;
   let _ = fnck_sql
       .run("insert into t3 values (0, 2, 2), (3, 8, 5);")
       .await?;
   let (schema, tuples) = fnck_sql.run("select id,a,b from t2 where id in (select a from t3 where a > t2.a);").await?;
   println!("{}", create_table(&schema, &tuples));

   there is only t3.a on the right but no t2.a, so filter always returns false

   +--------------------------------------------------------------------+
   | PLAN                                                               |
   +====================================================================+
   | Projection [t2.id, t2.a, t2.b] [Project]                           |
   |   LeftSemi Join On t2.id = (t3.a) as (_temp_table_0_.a) [HashJoin] |
   |     Scan t2 -> [id, a, b] [SeqScan]                                |
   |     Projection [(t3.a) as (_temp_table_0_.a)] [Project]            |
   |       Projection [t3.a] [Project]                                  |
   |         Filter (t3.a > t2.a), Is Having: false [Filter]            |
   |           Scan t3 -> [a] [SeqScan]                                 |
   +--------------------------------------------------------------------+

   I think this problem is caused by NormalizationRule: PushPredicateThroughJoin
3. crwen commented on Mar 30, 2024

   

   crwen

   on Mar 30, 2024

   MemberAuthor

   More actions

   I think this problem is caused by NormalizationRule: PushPredicateThroughJoin

   I don't think it would help. Filter is never pulled up since binding, so it won't be pushed down.

   👍React with 👍1Reacted by Kould
4. KKould commented on Mar 30, 2024

   

   KKould

   on Mar 30, 2024

   Member

   More actions

   yep, I made a mistake. This seems to be a feature that has not yet been implemented.
5. KKould

   assigned

   crwen

   on Mar 30, 2024
6. KKould

   added

   enhancementNew feature or requestNew feature or request

   bugSomething isn't workingSomething isn't working

   on Mar 30, 2024
7. KKould

   removed

   bugSomething isn't workingSomething isn't working

   on Dec 8, 2024