[ https://issues.apache.org/jira/browse/CALCITE-4033?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rui Wang updated CALCITE-4033: ------------------------------ Description: 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. was: 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 item JOIN LATERAL( SELECT * FROM (UNNEST(item.related)) i(rels) ) relation ON TRUE").ok("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. > 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)