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]
> > (马赛卫)
>
>
