Bug: In multi-level nested subquery, the field relationships of different tables are wrong.

[KKould]

Bug Report

It seems to project twice on temp table.
And I don't think it can handle more complex situations.

explain SELECT b FROM t1 WHERE a in (SELECT a FROM t2 where (select a from t1));
                               PLAN
-------------------------------------------------------------------
Projection [t1.b] [Project]                                      +
  LeftSemi Join On t1.a = (t2.a) as (_temp_table_2_.a) [HashJoin]+
    Scan t1 -> [a, b] [SeqScan]                                  +
    Projection [(t2.a) as (_temp_table_2_.a)] [Project]          +
      Projection [t2.a] [Project]                                +
        Inner Join Where _temp_table_1_.a [HashJoin]             +
          Scan t2 -> [a] [SeqScan]                               +
          Projection [(t2.a) as (_temp_table_1_.a)] [Project]    +
            Projection [t2.a] [Project]                          +
              Scan t1 -> [] [SeqScan]

I think every query using their own binder context may be a solution, but it may be a big work.

#168 (comment)

[KKould]

Is there a lighter solution? Because in issue: #162, Binder::bind also takes up a considerable part of the performance overhead. Can it be solved by using last_bind_table or something like that?

[crwen]

Is there a lighter solution? Because in issue: #162, Binder::bind also takes up a considerable part of the performance overhead. Can it be solved by using last_bind_table or something like that?

I'm not familiar with this. I just find duckdb have a parent field for this. It seems to be organized like the plan.

https://github.com/duckdb/duckdb/blob/main/src/include/duckdb/planner/binder.hpp#L188

https://github.com/duckdb/duckdb/blob/main/src/planner/binder/tableref/bind_subqueryref.cpp#L7C1-L31C2