Re: Calcite to re-write SQL-to-SQL

[Mihai Budiu]

What do you mean "fails?"

Mihai
________________________________
From: Yarin Benado <ya...@bena.do>
Sent: Thursday, August 22, 2024 8:25 AM
To: dev@calcite.apache.org <dev@calcite.apache.org>
Subject: Re: Calcite to re-write SQL-to-SQL

Here's an example of my desired plan transformation:

Transforming the Project fails, since the Filter RowType is not equivalent
to the input (as now we're joining with another table and have a different
row type)

=== Input Logical Plan:
LogicalProject(ID=[$0], NAME=[$1], ACCOUNT_NAME_STUB=[$2])
  LogicalFilter(condition=[AND(>($1, 100), >($2,
CAST('2021-01-01'):TIMESTAMP_TZ(0) NOT NULL))])
    LogicalTableScan(table=[[contact]])


=== Output Logical Plan:
LogicalProject(ID=[$0], NAME=[$1], NAME0=[$11])
  LogicalFilter(condition=[AND(>($1, 100), >($2,
CAST('2021-01-01'):TIMESTAMP_TZ(0) NOT NULL))])
    LogicalJoin(condition=[=($9, $0)], joinType=[inner])
      LogicalTableScan(table=[[contact]])
      LogicalTableScan(table=[[account]])

On Thu, Aug 22, 2024 at 5:28 PM suibianwanwan33 <suibianwanwa...@foxmail.com>
wrote:

> Hi, I don't understand how you apply this rule. But for a Project, the
> projects expression needs to be type-aligned to the rowType, and each
> project expression input reference needs to be compatible with the output
> of its input.
>
>
> Suibianwanwan
> ------------------&nbsp;原始邮件&nbsp;------------------
> 发件人:
>                                                   "dev"
>                                                                 <
> ya...@bena.do&gt;;
> 发送时间:&nbsp;2024年8月22日(星期四) 晚上6:58
> 收件人:&nbsp;"dev"<dev@calcite.apache.org&gt;;
>
> 主题:&nbsp;Calcite to re-write SQL-to-SQL
>
>
>
> Hi,
>
> I'd like to utilize Calcite to re-write SQL queries based on a few simple
> (yet complex to implement) rules. My goal is to transform a logical plan,
> and get back the SQL using RelToSqlConverter
>
> &nbsp;i.e:
>
> in the following query:
> SELECT name, Account.name from Contact
>
> Account.name is a "parent-child" association of Contact table, and the
> query has to be re-written as:
>
> SELECT name, account.name from Contact
> JOIN Account on account.id = contact.account_id
>
> The issue i'm facing here is that the transformed LogicalProject node
> doesn't pass the equivalence check due to the changed Project input.
>
> I have more rules I'd like to apply - but they all tend to change the plan
> in a way that the result will be the same, but the structure of the plan is
> different.