Alessandro Solimando created CALCITE-7537:
---------------------------------------------
Summary: 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
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)
