[ 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 // Updated based on Julian's comment. If write the following test in Calcite, {code:java} @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} The expected SQL query (thus the unparsed one) is {code:sql} SELECT `ITEM`.`NAME`, `RELATIONS`.* FROM `DFS`.`TMP` AS `ITEM` INNER JOIN (SELECT * FROM (UNNEST(`ITEM`.`RELATED`)) AS `I` (`RELS`)) AS `RELATIONS` ON TRUE"); {code} Note that there are extra parentheses around the UNNEST. And then the generated query won't pass Calcite's parser because the parentheses around the UNNEST. was: Reported from: https://issues.apache.org/jira/browse/DRILL-7722 // Updated based on Julian's comment. If write the following test in Calcite, @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); } Then SqlNode.toSqlString() generates {code:sql} SELECT `ITEM`.`NAME`, `RELATIONS`.* FROM `DFS`.`TMP` AS `ITEM` INNER JOIN (SELECT * FROM (UNNEST(`ITEM`.`RELATED`)) AS `I` (`RELS`)) AS `RELATIONS` ON TRUE"); {code} Note that there are extra parentheses around the UNNEST. And then the generated query won't pass Calcite's parser because the parentheses around the UNNEST. > 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 > // Updated based on Julian's comment. > If write the following test in Calcite, > {code:java} > @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} > The expected SQL query (thus the unparsed one) is > {code:sql} > SELECT `ITEM`.`NAME`, `RELATIONS`.* > FROM `DFS`.`TMP` AS `ITEM` > INNER JOIN (SELECT * > FROM (UNNEST(`ITEM`.`RELATED`)) AS `I` (`RELS`)) AS `RELATIONS` ON TRUE"); > {code} > Note that there are extra parentheses around the UNNEST. And then the > generated query won't pass Calcite's parser because the parentheses around > the UNNEST. -- This message was sent by Atlassian Jira (v8.3.4#803005)