It's because column2 is a column I created artificially to allow the validation of a query. I normally perform a transformation (RelNode => RelNode) that removes any reference to that column. The problem is that when I convert this Logical Plan back to SQL, it contains references to that column and the execution engine should not see that column and throws an error.
LogicalProject(column1=[$1], column10=[$4]) LogicalJoin(condition=[=($2, $4)], joinType=[inner]) LogicalProject(column2=[$0], column1=[$1], $f2=[LOWER($1)]) LogicalTableScan(table=[[t1]]) LogicalTableScan(table=[[t2]]) My problem is the added field column2=[$0] in the LogicalProject. Since it's not contributing to the query, it should not be there. On Tue, Oct 3, 2023 at 3:24 PM Julian Hyde <jhyde.apa...@gmail.com> wrote: > Are you worried that $3 in the first plan has become $4 in the second plan? > > That’s easily explained because the intermediate table has an extra > column, LOWER($1). That pushes up the offset of all columns coming from the > right-hand table. > > > On Oct 3, 2023, at 3:14 PM, Guillaume Masse < > masse.guilla...@narrative.io.INVALID> wrote: > > > > Hi All, > > > > I have two tables: t1 and t2 both have a column1 of type t and a column2 > > that I'm not using > > I have a function f: t -> t defined in the catalog > > > > After validation if I print the logical plan I get the following result: > > > > select > > t1.column1, > > t2.column1 > > from company_data."1" t1 > > join company_data."2" t2 > > on t1.column1 = t2.column1 > > > > LogicalProject(column1=[$1], column10=[$3]) > > LogicalJoin(condition=[=($1, $3)], joinType=[inner]) > > LogicalTableScan(table=[[t1]]) > > LogicalTableScan(table=[[t2]]) > > > > > > select > > t1.column1, > > t2.column1 > > from company_data."1" t1 > > join company_data."2" t2 > > on lower(t1.column1) = t2.column1 > > > > LogicalProject(column1=[$1], column10=[$4]) > > LogicalJoin(condition=[=($2, $4)], joinType=[inner]) > > LogicalProject(column2=[$0], column1=[$1], $f2=[LOWER($1)]) > > LogicalTableScan(table=[[t1]]) > > LogicalTableScan(table=[[t2]]) > > > > > > I'm a bit surprise by the second logical plan because it projects > column2. > > > > 1) I'm wondering if this is a bug. > > > > 2) If it's not a bug, let's say it was injected by the validator to > > simplify the validation process, is there a way to remove it? It feels a > > bit weird, because I would have to rewrite all the offsets when I'm > > reconstructing the tree: > > > > LogicalProject(column1=[$0], column10=[$3]) > > LogicalJoin(condition=[=($1, $3)], joinType=[inner]) > > LogicalProject(column1=[$0], $f2=[LOWER($1)]) > > LogicalTableScan(table=[[t1]]) > > LogicalTableScan(table=[[t2]]) > > > > > > Thank You! > > > > -- > > Guillaume Massé > > [Gee-OHM] > > (马赛卫) > >