[
https://issues.apache.org/jira/browse/CALCITE-7537?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18082376#comment-18082376
]
Mihai Budiu commented on CALCITE-7537:
--------------------------------------
The optimizer can produce right-deep join trees
> Invalid Postgres SQL generated for right-deep comma join trees
> --------------------------------------------------------------
>
> Key: CALCITE-7537
> URL: https://issues.apache.org/jira/browse/CALCITE-7537
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Reporter: Alessandro Solimando
> Assignee: Alessandro Solimando
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> When RelToSqlConverter encounters a right-deep join tree where all joins are
> cross joins (INNER JOIN with TRUE condition), it correctly identifies the
> tree as a comma join but generates invalid SQL with extra parentheses around
> the right side.
> For example, given a right-deep tree Join(A, Join(B, C)) with all-true
> conditions, the converter generates:
> {noformat}
> SELECT *
> FROM "A", ("B", "C"){noformat}
> while it could generate a flat form like:
> {noformat}
> SELECT *
> FROM "A","B","C"{noformat}
> Since the parenthesized form FROM "A", ("B", "C") is invalid in PostgreSQL
> (syntax error: *Query Error:* syntax error at or near ","), and the flat
> version seems to be supported more broadly.
> Left-deep trees (which the SQL parser produces) are unaffected, so this only
> manifests when the relational plan comes from a non-SQL source (e.g.
> Substrait, or manual RelBuilder construction) that produces right-deep join
> trees.
> *Root cause*
> _SqlJoin.SqlJoinOperator.unparse()_ passes _getRightPrec()_ (19) as the
> _leftPrec_ when unparsing the right child. Since the join operator is
> left-associative with precedence 18, 19 > 18 triggers parenthesization. Comma
> join is associative, so parentheses on the right child are never really
> needed.
> *Fix*
> For comma joins, pass _getLeftPrec()_ instead of _getRightPrec()_ when
> unparsing the right child.
> *Minimal reproducer*
> This can be run by adding it to {_}RelToSqlConverterTest{_}:
> {code:java}
> @Test void testCommaCrossJoin3wayRightDeep() {
> final Function<RelBuilder, RelNode> relFn = b ->
> b.scan("EMP")
> .scan("DEPT")
> .scan("BONUS")
> .join(JoinRelType.INNER)
> .join(JoinRelType.INNER)
> .build();
> final String expectedPostgresql = "SELECT *\n"
> + "FROM \"scott\".\"EMP\",\n"
> + "\"scott\".\"DEPT\",\n"
> + "\"scott\".\"BONUS\"";
> relFn(relFn)
> .withPostgresql().ok(expectedPostgresql);
> }{code}
> Extra (DB Fiddle to see this shape passing with MySQL and failing with
> Postgres):
> * [MySQL|https://www.db-fiddle.com/#&togetherjs=1rQH0sckmf]
> * [Postgres|https://www.db-fiddle.com/#&togetherjs=chn5bQc4vl]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
