[
https://issues.apache.org/jira/browse/CALCITE-4485?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17278464#comment-17278464
]
Julian Hyde commented on CALCITE-4485:
--------------------------------------
I think the problem is that the comma has different precedence/associativity
than the CROSS JOIN operator. Therefore "a , b LEFT JOIN c" associates as "a ,
(b LEFT JOIN c)" whereas "a CROSS JOIN b LEFT JOIN c" associates as "(a CROSS
JOIN b) LEFT JOIN c".
I would like us to continue generating comma for CROSS JOIN whenever possible.
For most people, a "straightforward SQL statement" will have one, two or three
tables in the FROM clause, separated by commas, and join conditions in the
WHERE clause. We should continue to generate straightforward SQL wherever
possible.
To do this, we will need to recognize cases such as "a , b LEFT JOIN c", where
comma is followed by higher-precedence operator, and generate CROSS JOIN in
only those cases.
> 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
>
> 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)
