[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16815922#comment-16815922
]
Chunwei Lei commented on CALCITE-1338:
--
[~julianhyde], PR was updated. Many thanks for your review.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16815823#comment-16815823
]
Julian Hyde commented on CALCITE-1338:
--
Looks good, but can you improve the organization of the code:
* {{containsNotStrongExpression}} could either be replaced with a one-line
{{project.getProjects().stream().anyMatch(...)} or moved to Strong:
{{Strong.anyStrong(project.getProjects())}}
* in the {{testJoinProjectTransposeN}} test cases, don't repeat the same
javadoc comment every time but indicate what is different about that particular
test
* move RexUtil.isStrong to Strong, and add an example to the javadoc
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16815045#comment-16815045
]
Chunwei Lei commented on CALCITE-1338:
--
Thanks [~julianhyde]. It is exactly what I need. PR was updated.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16814755#comment-16814755
]
Julian Hyde commented on CALCITE-1338:
--
I was wrong to use the word "strict" earlier. According to the [conventional
definition of strict|https://en.wikipedia.org/wiki/Strict_function], {{f(x,
y)}} is strict in {{x}} and {{y}} if it cannot return a result without
evaluating both {{x}} and {{y}}.
I invented a new term, *strong*. {{f(x, y)}} is strong in {{x}} and {{y}} if it
cannot return a not-null value without {{x}} and {{y}} being not-null.
Strong is well-tested. See RexProgramTest.testStrong. I think it's what you
need.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16814736#comment-16814736
]
Julian Hyde commented on CALCITE-1338:
--
How about using
[Strong|https://calcite.apache.org/apidocs/org/apache/calcite/plan/Strong.html]?
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16814089#comment-16814089
]
Chunwei Lei commented on CALCITE-1338:
--
I opened a PR: [https://github.com/apache/calcite/pull/1153].
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16813177#comment-16813177
]
Hongze Zhang commented on CALCITE-1338:
---
{quote}
Can I say that if one of nodes in the project is not-nullable, then we should
not pull up
{quote}
Sounds reasonable. It's more or less like the last case that Julian has listed.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16813107#comment-16813107
]
Chunwei Lei commented on CALCITE-1338:
--
Thanks [~zhztheplayer] and [~hyuan]. It is a good idea that use {{RexSimplify}}
to check if a Rex tree could be simplified to null literal within all input
references replaced by nulls.
Moreover, Can I say that if one of nodes in the project is not-nullable, then
we should not pull up? If yes, maybe we can simply use
_node.getType().isNullable()_ to ensure correctness.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16813013#comment-16813013
]
Haisheng Yuan commented on CALCITE-1338:
Good idea, that seems much simpler.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16813008#comment-16813008
]
Hongze Zhang commented on CALCITE-1338:
---
It sounds to be a little tricky if we add an {{Operator#isStrict()}} method and
use that method to check a whole Rex tree's strictness. Do you have a plan?
[~Chunwei Lei]
Or maybe we can just use {{RexSimplify}} to check if a Rex tree could be
simplified to null literal within all input references replaced by nulls? I
think that should be safe in most cases.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16812998#comment-16812998
]
Chunwei Lei commented on CALCITE-1338:
--
{quote}We may need a function in RexUtil to check the strictness of a RexNode.
Simply check literal or rexcall is not enough, e.g. 1+2. If there is no column
reference, do not pull up. We need to check the whole rex node tree.
{quote}
Make sense. Checking the whole rex node tree is necessary.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16812997#comment-16812997
]
Haisheng Yuan commented on CALCITE-1338:
I don't know if there is any better solution than adding strict field to
SqlOperator. So I agree.
We may need a function in RexUtil to check the strictness of a RexNode. Simply
check literal or rexcall is not enough, e.g. 1+2. If there is no column
reference, do not pull up. We need to check the whole rex node tree.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16812531#comment-16812531
]
Chunwei Lei commented on CALCITE-1338:
--
To resolve this, I would like to proposal:
* add a new method named isStrict in SqlOperator to indicate whether the
operator is strict or not
* add a check in JoinProjectTransposeRule to assure not transform when the
project has literal or non-strict call
The code looks like as follows:
{code:java}
if (includeOuter) {
Project project = null;
if (rightProj != null
&& joinType.generatesNullsOnRight()) {
project = rightProj;
} else if (leftProj != null && joinType.generatesNullsOnLeft()) {
project = leftProj;
}
if (project != null) {
for (RexNode node: project.getProjects()) {
if (node instanceof RexLiteral) {
return;
} else if (node instanceof RexCall) {
RexCall rexCall = (RexCall) node;
if (!rexCall.getOperator().isStrict()) {
return;
}
}
}
}
}
{code}
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Chunwei Lei
>Priority: Major
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807488#comment-16807488
]
Chunwei Lei commented on CALCITE-1338:
--
I will take a look into this since it will cause data error.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Priority: Major
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15433538#comment-15433538
]
Vineet Garg commented on CALCITE-1338:
--
Hi Julian,
Hive is hitting this issue as well (see HIVE-14594). Has there been any work
done on this yet ? Otherwise I'll take a look into this.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Yi Xinglu
>Assignee: Julian Hyde
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15403461#comment-15403461
]
YI XINGLU commented on CALCITE-1338:
Hi Julian, I agree with you. JoinProjectTransposeRule should check whether the
project expressions of its children contains RexLiteral before the
transformation including outer.
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: YI XINGLU
>Assignee: Julian Hyde
>
> h3. SQL:
> {code:borderStyle=solid}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {code}
> h3. Selected rule set:
> {code:borderStyle=solid}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {code}
> h3. Optimized logical plan:
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. The right logical plan should be
> {code:borderStyle=solid}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> h3. Summary
> The RexLiteral project expression will make logical plan get different
> results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[ https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15403435#comment-15403435 ] Julian Hyde commented on CALCITE-1338: -- OK, I saw you changed the description back again. I added the "noformat" tags so that we can actually see the indentation in JIRA. > JoinProjectTransposeRule makes wrong transformation when the right child of > left outer join has a RexLiteral project expression. > > > Key: CALCITE-1338 > URL: https://issues.apache.org/jira/browse/CALCITE-1338 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: YI XINGLU >Assignee: Julian Hyde > > # SQL: > SELECT * > FROM dept a > LEFT JOIN ( > SELECT b.name, 1 FROM dept b > ) AS b > ON a.name=b.name > # Selected rule set: > SubQueryRemoveRule.JOIN > JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER > # Optimized logical plan: > LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) > LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], > EXPR$1=[CAST($3):INTEGER]) > LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1]) > LogicalJoin(condition=[=($3, $1)], joinType=[left]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > # The right logical plan should be > LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3]) > LogicalJoin(condition=[=($2, $1)], joinType=[left]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > LogicalProject(NAME=[$1], EXPR$1=[1]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > # Summary > The RexLiteral project expression will make logical plan get different > results when it's right child or parent node of left outer join. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (CALCITE-1338) JoinProjectTransposeRule makes wrong transformation when the right child of left outer join has a RexLiteral project expression.
[
https://issues.apache.org/jira/browse/CALCITE-1338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15403286#comment-15403286
]
Julian Hyde commented on CALCITE-1338:
--
It seems that JoinProjectTransposeRule simply not fire in this case. It
shouldn't pull an expression through a the null-generating side of an outer
join. Do you agree?
> JoinProjectTransposeRule makes wrong transformation when the right child of
> left outer join has a RexLiteral project expression.
>
>
> Key: CALCITE-1338
> URL: https://issues.apache.org/jira/browse/CALCITE-1338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: YI XINGLU
>Assignee: Julian Hyde
>
> SQL:
> {noformat}
> SELECT *
> FROM dept a
> LEFT JOIN (
> SELECT b.name, 1 FROM dept b
> ) AS b
> ON a.name=b.name
> {noformat}
> Selected rule set:
> {noformat}
> SubQueryRemoveRule.JOIN
> JoinProjectTransposeRule.RIGHT_PROJECT_INCLUDE_OUTER
> {noformat}
> Optimized logical plan:
> {noformat}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2],
> EXPR$1=[CAST($3):INTEGER])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$3], EXPR$1=[1])
> LogicalJoin(condition=[=($3, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
> The right logical plan should be
> {noformat}
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[$2], EXPR$1=[$3])
> LogicalJoin(condition=[=($2, $1)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(NAME=[$1], EXPR$1=[1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
> In summary, the RexLiteral project expression will make logical plan get
> different results when it's right child or parent node of left outer join.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
