Feature Request there is something wrong when binding correlated s

Feat: Support correlated subqueries about fncksql HOT 4 OPEN

crwen commented on June 2, 2024 1

Feat: Support correlated subqueries

from fncksql.

Comments (4)

crwen commented on June 2, 2024 1

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.

from fncksql.

KKould commented on June 2, 2024

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

from fncksql.

KKould commented on June 2, 2024

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]                                 |
+--------------------------------------------------------------------+