You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by luoc <lu...@qq.com.INVALID> on 2021/10/11 08:30:15 UTC
Disable the subquery
Hello Calcite team,
I am going to develop a new feature on my project with the Calcite. But got the following issues :
Input SQL :
SELECT A.N_NAME, B.R_NAME FROM V1.NATION A LEFT JOIN V1.REGION B ON A.N_REGIONKEY = B.R_REGIONKEY WHERE B.R_NAME = 'ASIA';
Actual SQL (send to DB) :
SELECT "NATION0"."N_NAME" AS "n_name", "t"."R_NAME" AS "r_name"
FROM "V1"."NATION" AS "NATION0"
INNER JOIN (SELECT *
FROM "V1"."REGION"
WHERE "R_NAME" = 'ASIA') AS "t" ON "NATION0"."N_REGIONKEY" = "t"."R_REGIONKEY";
If possible, How can I disable the join auto-convert to the subquery syntax? Because the backend DB does not support the subquery (but the `left join` is supported). Add the specified rule or rewrite the `JdbcJoinRule` class ?
Thanks for your time.
回复： Disable the subquery
Posted by luoc <lu...@qq.com.INVALID>.
Tao, I think, yes. the code is that :
```
JdbcImplementor.Result result = jdbcImplementor.visitChild(0, input.accept(SubsetRemover.INSTANCE));
sql = result.asStatement().toSqlString(dialect).getSql();
```
Any help or pointer would be appreciated. Thanks.
------------------ 原始邮件 ------------------
发件人: "dev" <taojiatao@gmail.com>;
发送时间: 2021年10月12日(星期二) 下午3:26
收件人: "dev"<dev@calcite.apache.org>;
主题: Re: Disable the subquery
Do you use the RelToSqlConverter to convert plan to sql?
Regards!
Aron Tao
luoc <luocooong@qq.com.invalid> 于2021年10月11日周一 下午4:30写道：
> Hello Calcite team,
>
>
> &nbsp; I am going to develop a new feature on my project with the Calcite.
> But got the following issues :
>
>
> Input SQL :
>
>
> SELECT A.N_NAME, B.R_NAME FROM V1.NATION A LEFT JOIN V1.REGION B ON
> A.N_REGIONKEY = B.R_REGIONKEY WHERE B.R_NAME = 'ASIA';
>
>
> Actual SQL (send to DB) :
>
>
> SELECT "NATION0"."N_NAME" AS "n_name", "t"."R_NAME" AS "r_name"
> FROM "V1"."NATION" AS "NATION0"
> INNER JOIN (SELECT *
> FROM "V1"."REGION"
> WHERE "R_NAME" = 'ASIA') AS "t" ON "NATION0"."N_REGIONKEY" =
> "t"."R_REGIONKEY";
>
>
> If possible, How can I disable the join auto-convert to the subquery
> syntax? Because the backend DB does not support the subquery (but the `left
> join` is supported). Add the specified rule or rewrite the `JdbcJoinRule`
> class ?
>
>
> Thanks for your time.
回复： Disable the subquery
Posted by luoc <lu...@qq.com.INVALID>.
Hi, Julian. Thank you for the information. I also want to know that :
1. If the final sql is converted to the sub-query, Does it mean that there exist a conversion rule (Join) ?
2. Will there be other built-in rules cause this result besides JoinConditionPushRule ?
------------------ 原始邮件 ------------------
发件人: "dev" <jhyde.apache@gmail.com>;
发送时间: 2021年10月12日(星期二) 下午3:34
收件人: "dev"<dev@calcite.apache.org>;
主题: Re: Disable the subquery
Have you considered disabling JoinConditionPushRule?
> On Oct 12, 2021, at 12:26 AM, JiaTao Tao <taojiatao@gmail.com> wrote:
>
> Do you use the RelToSqlConverter to convert plan to sql?
>
> Regards!
>
> Aron Tao
>
>
> luoc <luocooong@qq.com.invalid> 于2021年10月11日周一 下午4:30写道：
>
>> Hello Calcite team,
>>
>>
>> &nbsp; I am going to develop a new feature on my project with the Calcite.
>> But got the following issues :
>>
>>
>> Input SQL :
>>
>>
>> SELECT A.N_NAME, B.R_NAME FROM V1.NATION A LEFT JOIN V1.REGION B ON
>> A.N_REGIONKEY = B.R_REGIONKEY WHERE B.R_NAME = 'ASIA';
>>
>>
>> Actual SQL (send to DB) :
>>
>>
>> SELECT "NATION0"."N_NAME" AS "n_name", "t"."R_NAME" AS "r_name"
>> FROM "V1"."NATION" AS "NATION0"
>> INNER JOIN (SELECT *
>> FROM "V1"."REGION"
>> WHERE "R_NAME" = 'ASIA') AS "t" ON "NATION0"."N_REGIONKEY" =
>> "t"."R_REGIONKEY";
>>
>>
>> If possible, How can I disable the join auto-convert to the subquery
>> syntax? Because the backend DB does not support the subquery (but the `left
>> join` is supported). Add the specified rule or rewrite the `JdbcJoinRule`
>> class ?
>>
>>
>> Thanks for your time.
Re: Disable the subquery
Posted by Julian Hyde <jh...@gmail.com>.
Have you considered disabling JoinConditionPushRule?
> On Oct 12, 2021, at 12:26 AM, JiaTao Tao <ta...@gmail.com> wrote:
>
> Do you use the RelToSqlConverter to convert plan to sql?
>
> Regards!
>
> Aron Tao
>
>
> luoc <lu...@qq.com.invalid> 于2021年10月11日周一 下午4:30写道：
>
>> Hello Calcite team,
>>
>>
>> I am going to develop a new feature on my project with the Calcite.
>> But got the following issues :
>>
>>
>> Input SQL :
>>
>>
>> SELECT A.N_NAME, B.R_NAME FROM V1.NATION A LEFT JOIN V1.REGION B ON
>> A.N_REGIONKEY = B.R_REGIONKEY WHERE B.R_NAME = 'ASIA';
>>
>>
>> Actual SQL (send to DB) :
>>
>>
>> SELECT "NATION0"."N_NAME" AS "n_name", "t"."R_NAME" AS "r_name"
>> FROM "V1"."NATION" AS "NATION0"
>> INNER JOIN (SELECT *
>> FROM "V1"."REGION"
>> WHERE "R_NAME" = 'ASIA') AS "t" ON "NATION0"."N_REGIONKEY" =
>> "t"."R_REGIONKEY";
>>
>>
>> If possible, How can I disable the join auto-convert to the subquery
>> syntax? Because the backend DB does not support the subquery (but the `left
>> join` is supported). Add the specified rule or rewrite the `JdbcJoinRule`
>> class ?
>>
>>
>> Thanks for your time.
Re: Disable the subquery
Posted by JiaTao Tao <ta...@gmail.com>.
Do you use the RelToSqlConverter to convert plan to sql?
Regards!
Aron Tao
luoc <lu...@qq.com.invalid> 于2021年10月11日周一 下午4:30写道：
> Hello Calcite team,
>
>
> I am going to develop a new feature on my project with the Calcite.
> But got the following issues :
>
>
> Input SQL :
>
>
> SELECT A.N_NAME, B.R_NAME FROM V1.NATION A LEFT JOIN V1.REGION B ON
> A.N_REGIONKEY = B.R_REGIONKEY WHERE B.R_NAME = 'ASIA';
>
>
> Actual SQL (send to DB) :
>
>
> SELECT "NATION0"."N_NAME" AS "n_name", "t"."R_NAME" AS "r_name"
> FROM "V1"."NATION" AS "NATION0"
> INNER JOIN (SELECT *
> FROM "V1"."REGION"
> WHERE "R_NAME" = 'ASIA') AS "t" ON "NATION0"."N_REGIONKEY" =
> "t"."R_REGIONKEY";
>
>
> If possible, How can I disable the join auto-convert to the subquery
> syntax? Because the backend DB does not support the subquery (but the `left
> join` is supported). Add the specified rule or rewrite the `JdbcJoinRule`
> class ?
>
>
> Thanks for your time.