[
https://issues.apache.org/jira/browse/CALCITE-4485?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17281445#comment-17281445
]
Julian Hyde commented on CALCITE-4485:
--------------------------------------
My position is that we should use commas where possible. Let's fix the bug, not
turn this into a debate on people's preferred style for writing SQL.
(Customizing the style of the generated SQL is a valid ask, but it should be an
orthogonal case.)
We can fix the bug without changing the SQL that we generate in most cases. All
we need to do is comply with the precedence/associativity of the operators we
generate; we do that all the time for expressions like +, -, *, AND, OR.
> 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)
