[
https://issues.apache.org/jira/browse/CALCITE-4485?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17281588#comment-17281588
]
Jan Soubusta commented on CALCITE-4485:
---------------------------------------
Julian,
thanks for quick response.
Regarding fix:
- COMMA JOIN preceding FULL OUTER JOIN must always be converted to CROSS JOIN
-- with COMMA JOIN query can produce wrong result set as demonstrated in the
attachment
- When table in any ANSI JOIN(INNER, OUTER) clause references a table on the
left side being part of COMMA JOIN, COMMA JOIN must be converted to CROSS JOIN
-- otherwise it produces the error mentioned in initial description of this
issue (relates to different precedence of comma join)
Btw, would you consider an option to introduce JoinRelType.CROSS? It would help
us to simplify our implementation.
> Invalid SQL being produced by RelToSqlConverter when the join condition is
> tautology
> ------------------------------------------------------------------------------------
>
> Key: CALCITE-4485
> URL: https://issues.apache.org/jira/browse/CALCITE-4485
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Ondřej Štumpf
> Priority: Major
> Attachments: comma_vs_joins_postgres.sql
>
>
> h5. Summary
> When using {{INNER}} join and a join condition that is always true, the
> {{RelToSqlConverter}} may generate an invalid SQL.
> h5. Reproducer (Kotlin; TPCH dataset)
> {code:java}
> val frameworkConfig = ...
> val relationalBuilder = RelBuilder.create(frameworkConfig)
> val rootRelationalNode = relationalBuilder
> .scan("tpch", "customer")
> .aggregate(
> relationalBuilder.groupKey(
> relationalBuilder.field("nation_name")
> ),
> relationalBuilder.count().`as`("cnt1")
> )
> .project(relationalBuilder.field("nation_name"),
> relationalBuilder.field("cnt1"))
> .`as`("cust")
> .scan("tpch", "lineitem")
> .aggregate(
> relationalBuilder.groupKey(),
> relationalBuilder.count().`as`("cnt2")
> )
> .project(relationalBuilder.field("cnt2"))
> .`as`("lineitem")
> .join(JoinRelType.INNER)
> .scan("tpch", "part")
> .join(
> JoinRelType.LEFT,
> relationalBuilder.call(
> SqlStdOperatorTable.EQUALS,
> relationalBuilder.field(2, "cust", "nation_name"),
> relationalBuilder.field(2, "part", "p_brand")
> )
> )
> .project(
> relationalBuilder.field("cust", "nation_name"),
> relationalBuilder.alias(
> relationalBuilder.call(
> SqlStdOperatorTable.MINUS,
> relationalBuilder.field("cnt1"),
> relationalBuilder.field("cnt2")
> ),
> "f1")
> )
> .build()
> println(
> RelToSqlConverter(SqlDialect.DatabaseProduct.POSTGRESQL.dialect)
> .visitRoot(rootRelationalNode)
> .asStatement()
> .toSqlString(SqlDialect.DatabaseProduct.POSTGRESQL.dialect)
> .sql
> )
> {code}
> h5. Result SQL
> {code:sql}
> SELECT "t"."c_name", "t"."cnt1" - "t0"."cnt2" AS "f1"
> FROM (SELECT "c_name", COUNT(*) AS "cnt1"
> FROM "tpch"."customer"
> GROUP BY "c_name") AS "t",
> (SELECT COUNT(*) AS "cnt2"
> FROM "tpch"."lineitem") AS "t0"
> LEFT JOIN "tpch"."part" ON "t"."c_name" = "part"."p_brand"
> {code}
> which produces error (on Postgres):
> {code}
> [42P01] ERROR: invalid reference to FROM-clause entry for table "t" Hint:
> There is an entry for table "t", but it cannot be referenced from this part
> of the query. Position: 265
> {code}
> h5. Expected SQL
> {code:sql}
> SELECT "t"."c_name", "t"."cnt1" - "t0"."cnt2" AS "f1"
> FROM (SELECT "c_name", COUNT(*) AS "cnt1"
> FROM "tpch"."customer"
> GROUP BY "c_name") AS "t" CROSS JOIN
> (SELECT COUNT(*) AS "cnt2"
> FROM "tpch"."lineitem") AS "t0"
> LEFT JOIN "tpch"."part" ON "t"."c_name" = "part"."p_brand"
> {code}
> h5. Suggestion for a fix
> The {{CROSS JOIN}} syntax was introduced in SQL 92 and seems to be widely
> supported by DB engines, therefore I suggest to change the default in
> {{SqlDialect#emulateJoinTypeForCrossJoin}} from {{JoinType.COMMA}} to
> {{JoinType.CROSS}}.
> I have checked all supported DB products from {{SqlDialect.DatabaseProduct}}
> and they all seem to support the {{CROSS JOIN}} syntax, except these, which I
> have not been able to verify: Infobright, Luciddb, Paraccel, Netezza. For
> sake of backward compatibility, I suggest to override the
> {{emulateJoinTypeForCrossJoin}} method in these dialects to behave as before,
> i.e. {{JoinType.COMMA}}.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
