[
https://issues.apache.org/jira/browse/CALCITE-6413?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17907175#comment-17907175
]
Mihai Budiu commented on CALCITE-6413:
--------------------------------------
Here is a summary of the problem: consider the following natural join:
{code:sql}
CREATE TABLE t1(c0 VARCHAR);
CREATE TABLE t2(c0 DECIMAL(19, 9));
SELECT * FROM t1 NATURAL JOIN t2;
{code}
The query plan is the following:
{code}
LogicalProject(c0=[COALESCE(CAST($0):DECIMAL(19, 9), $1)]), id = 113
LogicalJoin(condition=[=($0, $1)], joinType=[inner]), id = 111
LogicalTableScan(table=[[schema, t1]]), id = 59
LogicalTableScan(table=[[schema, t2]]), id = 61
{code}
Compare this plan with the plan of an equivalent query without a NATURAL JOIN:
{code:sql}
SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0
{code}
{code}
LogicalProject(c0=[$0], c00=[$2]), id = 72
LogicalJoin(condition=[=($1, $3)], joinType=[inner]), id = 71
LogicalProject(c0=[$0], c00=[CAST($0):DECIMAL(28, 10)]), id = 68
LogicalTableScan(table=[[schema, t1]]), id = 59
LogicalProject(c0=[$0], c00=[CAST($0):DECIMAL(28, 10)]), id = 70
LogicalTableScan(table=[[schema, t2]]), id = 61
{code}
The casts required by the equality tests are missing in the NATURAL JOIN plan.
The solution may be to have the validator expand NATURAL JOINs into INNER JOINs.
> SqlValidator does not invoke TypeCoercionImpl::binaryComparisonCoercion for
> both NATURAL and USING join conditions
> -------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6413
> URL: https://issues.apache.org/jira/browse/CALCITE-6413
> Project: Calcite
> Issue Type: Improvement
> Reporter: Maksim Zhuravkov
> Assignee: Pavel Pereslegin
> Priority: Minor
>
> This can be observed by adding these test cases to `SqlToRelConverterTest`:
> 1. Join condition ON expression
> {code:java}
> @Test void test1() {
> final String sql = "select * from emp JOIN (VALUES ('XXX')) t(deptno) ON
> emp.deptno = t.deptno";
> sql(sql).ok();
> }
> {code}
> 2. Common columns (USING/NATURAL) (since they both share the same code path
> for building join condition)
> {code:java}
> @Test void test2() {
> final String sql = "select * from emp JOIN (VALUES ('XXX')) t(deptno)
> USING (deptno)";
> sql(sql).ok();
> }
> {code}
> When test 1 runs, the SqlValidator calls
> TypeCoercionImpl::binaryComparisonCoercion
> When test 2 runs, the SqlValidator does not call
> TypeCoercionImpl::binaryComparisonCoercion.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
