[
https://issues.apache.org/jira/browse/CALCITE-7537?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alessandro Solimando updated CALCITE-7537:
------------------------------------------
Description:
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, or if
the planner itself select a right-deep join tree as best plan.
*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("DEPT")
.scan("BONUS")
.scan("SALGRADE")
.join(JoinRelType.INNER)
.join(JoinRelType.INNER)
.build();
final String expectedPostgresql = "SELECT *\n"
+ "FROM \"scott\".\"DEPT\",\n"
+ "\"scott\".\"BONUS\",\n"
+ "\"scott\".\"SALGRADE\"";
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]
was:
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, or if
the planner itself select a right-deep join tree as best plan.
*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]
> 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, or if the planner itself select a right-deep join tree as best plan.
> *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("DEPT")
> .scan("BONUS")
> .scan("SALGRADE")
> .join(JoinRelType.INNER)
> .join(JoinRelType.INNER)
> .build();
> final String expectedPostgresql = "SELECT *\n"
> + "FROM \"scott\".\"DEPT\",\n"
> + "\"scott\".\"BONUS\",\n"
> + "\"scott\".\"SALGRADE\"";
> 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)
