[
https://issues.apache.org/jira/browse/CALCITE-5907?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yunhong Zheng updated CALCITE-5907:
-----------------------------------
Description:
As FLINK-27402 shown. If we have a table MyTable(a Int, b Boolean, c String).
Calcite will not simplify this case ( c is Varchar type while SqlLiteral is
boolean):
{code:java}
SELECT * FROM MyTable WHERE c = true;{code}
As the logical plan is :
{code:java}
LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
+- LogicalProject(inputs=[0..2])
+- LogicalFilter(condition=[=($2, true)])
+- LogicalTableScan(table=[[default_catalog, default_database,
MyTable]]){code}
However, Calcite will simplify this case while simplifyAnd :
{code:java}
SELECT * FROM MyTable WHERE b = true and c = true;{code}
As the logical plan is shown below: 'b = true' and 'c = true' both were
simplified to 'b' and 'c':
{code:java}
LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
+- LogicalProject(inputs=[0..2])
+- LogicalFilter(condition=[AND($1, $2)])
+- LogicalTableScan(table=[[default_catalog, default_database,
MyTable]]){code}
This may cause error because of filter condition is a Varchar type literal.
After reading Calcite's code, I found that. The logic of RexSimplify.implify()
and RexSimplify.implifyAnd() is different, where the logic of
RexSimplify.implifyAnd() is problematic:
{code:java}
// Simplify BOOLEAN expressions if possible
while (term.getKind() == SqlKind.EQUALS) {
RexCall call = (RexCall) term;
if (call.getOperands().get(0).isAlwaysTrue()) {
term = call.getOperands().get(1);
terms.set(i, term);
continue;
} else if (call.getOperands().get(1).isAlwaysTrue()) {
term = call.getOperands().get(0);
terms.set(i, term);
continue;
}
break;
} {code}
The above code cannot make such a simple judgment, as there may not be an
implicit conversion to ensure that the types on both sides of the condition are
consistent.
was:
As [FLINK-27402|https://issues.apache.org/jira/browse/FLINK-27402] shown. If
we have a table MyTable(a Int, b Boolean, c String). Calcite will not
simplify this case ( c is Varchar type while SqlLiteral is boolean):
{code:java}
SELECT * FROM MyTable WHERE c = true;{code}
As the logical plan is :
{code:java}
LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
+- LogicalProject(inputs=[0..2])
+- LogicalFilter(condition=[=($2, true)])
+- LogicalTableScan(table=[[default_catalog, default_database,
MyTable]]){code}
However, Calcite will simplify this case while simplifyAnd :
{code:java}
SELECT * FROM MyTable WHERE b = true and c = true;{code}
As the logical plan is shown below: 'b = true' and 'c = true' both were
simplified to 'b' and 'c':
{code:java}
LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
+- LogicalProject(inputs=[0..2])
+- LogicalFilter(condition=[AND($1, $2)])
+- LogicalTableScan(table=[[default_catalog, default_database,
MyTable]]){code}
This may cause error because of filter condition is a Varchar type literal.
After reading Calcite's code, I found that. The logic of RexSimplify.implify()
and RexSimplify.implifyAnd() is different, where the logic of
RexSimplify.implifyAnd() is problematic:
{code:java}
// Simplify BOOLEAN expressions if possible
while (term.getKind() == SqlKind.EQUALS) {
RexCall call = (RexCall) term;
if (call.getOperands().get(0).isAlwaysTrue()) {
term = call.getOperands().get(1);
terms.set(i, term);
continue;
} else if (call.getOperands().get(1).isAlwaysTrue()) {
term = call.getOperands().get(0);
terms.set(i, term);
continue;
}
break;
} {code}
The above code cannot make such a simple judgment, as there may not be an
implicit conversion to ensure that the types on both sides of the condition are
consistent.
> Unexpected boolean expression simplification for And expression
> ---------------------------------------------------------------
>
> Key: CALCITE-5907
> URL: https://issues.apache.org/jira/browse/CALCITE-5907
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.35.0
> Reporter: Yunhong Zheng
> Priority: Major
> Fix For: 1.36.0
>
>
> As FLINK-27402 shown. If we have a table MyTable(a Int, b Boolean, c
> String). Calcite will not simplify this case ( c is Varchar type while
> SqlLiteral is boolean):
> {code:java}
> SELECT * FROM MyTable WHERE c = true;{code}
> As the logical plan is :
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
> +- LogicalFilter(condition=[=($2, true)])
> +- LogicalTableScan(table=[[default_catalog, default_database,
> MyTable]]){code}
> However, Calcite will simplify this case while simplifyAnd :
> {code:java}
> SELECT * FROM MyTable WHERE b = true and c = true;{code}
> As the logical plan is shown below: 'b = true' and 'c = true' both were
> simplified to 'b' and 'c':
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
> +- LogicalFilter(condition=[AND($1, $2)])
> +- LogicalTableScan(table=[[default_catalog, default_database,
> MyTable]]){code}
> This may cause error because of filter condition is a Varchar type literal.
>
> After reading Calcite's code, I found that. The logic of
> RexSimplify.implify() and RexSimplify.implifyAnd() is different, where the
> logic of RexSimplify.implifyAnd() is problematic:
> {code:java}
> // Simplify BOOLEAN expressions if possible
> while (term.getKind() == SqlKind.EQUALS) {
> RexCall call = (RexCall) term;
> if (call.getOperands().get(0).isAlwaysTrue()) {
> term = call.getOperands().get(1);
> terms.set(i, term);
> continue;
> } else if (call.getOperands().get(1).isAlwaysTrue()) {
> term = call.getOperands().get(0);
> terms.set(i, term);
> continue;
> }
> break;
> } {code}
> The above code cannot make such a simple judgment, as there may not be an
> implicit conversion to ensure that the types on both sides of the condition
> are consistent.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
