[
https://issues.apache.org/jira/browse/CALCITE-4033?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17121437#comment-17121437
]
Julian Hyde commented on CALCITE-4033:
--------------------------------------
One problem is that SqlParserTest converts the SqlNode AST to SQL using maximal
parentheses. (This is useful for testing because it shows up bugs with operator
associativity.) But it parenthesizes UNNEST, which it should not, because
UNNEST is a table expression not an ordinary expression.
Calcite's SQL parser ought to be able to handle parenthesized table expressions
but it is a long-standing bug that it can't. So when it sees parentheses in the
FROM clause it thinks it is about to see a sub-query, and UNNEST is not a
sub-query.
The solution would be to make {{SqlWriterConfig.alwaysUseParentheses()}} leave
UNNEST without parentheses.
As I suspected, the problem shows up without LATERAL; it is sufficient for
UNNEST to be in a sub-query:
{code}
@Test void testUnnestWithoutLateral() {
final String sql1 = ""
+ "SELECT\n"
+ " item.name,\n"
+ " relations.*\n"
+ "FROM dfs.tmp item\n"
+ "JOIN (\n"
+ " SELECT * FROM UNNEST(item.related) i(rels)\n"
+ ") relations\n"
+ "ON TRUE";
final String expected = "SELECT `ITEM`.`NAME`, `RELATIONS`.*\n"
+ "FROM `DFS`.`TMP` AS `ITEM`\n"
+ "INNER JOIN (SELECT *\n"
+ "FROM (UNNEST(`ITEM`.`RELATED`)) AS `I` (`RELS`)) AS `RELATIONS` ON
TRUE";
sql(sql1).ok(expected);
}
{code}
> SqlNode.toSqlString generates wrong query for syntax "literal(select * from
> unnest(...))"
> -----------------------------------------------------------------------------------------
>
> Key: CALCITE-4033
> URL: https://issues.apache.org/jira/browse/CALCITE-4033
> Project: Calcite
> Issue Type: Bug
> Reporter: Rui Wang
> Priority: Major
>
> Reported from: https://issues.apache.org/jira/browse/DRILL-7722
> If use this query
> {code:sql}
> SELECT
> item.name,
> relations.*
> FROM dfs.tmp item
> JOIN LATERAL(
> SELECT * FROM UNNEST(item.related) i(rels)
> ) relations
> ON TRUE
> {code}
> Then SqlNode.toSqlString() generates
> {code:sql}
> SELECT `ITEM`.`NAME`, `RELATIONS`.*
> FROM `DFS`.`TMP` AS `ITEM`
> INNER JOIN LATERAL (SELECT *
> FROM (UNNEST(`ITEM`.`RELATED`)) AS `I` (`RELS`)) AS `RELATIONS` ON TRUE");
> {code}
> Note that there are extra parentheses around the UNNEST, and make the
> generated query unparsable.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
