You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by suibianwanwan33 <su...@foxmail.com> on 2024/08/22 16:57:07 UTC

回复: Calcite to re-write SQL-to-SQL

If you only want to output the fields of the contact table, you can add a Project above join, and only output the fields of the concat table.


Suibianwanwan




------------------&nbsp;原始邮件&nbsp;------------------
发件人:                                                                                                                        "dev"                                                                                    <yarin@bena.do&gt;;
发送时间:&nbsp;2024年8月22日(星期四) 晚上11:25
收件人:&nbsp;"dev"<dev@calcite.apache.org&gt;;

主题:&nbsp;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])
&nbsp; LogicalFilter(condition=[AND(&gt;($1, 100), &gt;($2,
CAST('2021-01-01'):TIMESTAMP_TZ(0) NOT NULL))])
&nbsp;&nbsp;&nbsp; LogicalTableScan(table=[[contact]])


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

On Thu, Aug 22, 2024 at 5:28 PM suibianwanwan33 <suibianwanwan33@foxmail.com&gt;
wrote:

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