[jira] [Comment Edited] (CALCITE-4033) SqlNode.toSqlString generates wrong query for syntax "literal(select * from unnest(...))"

["Rui Wang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4033?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17134964#comment-17134964 ] 

Rui Wang edited comment on CALCITE-4033 at 6/14/20, 5:41 AM:
-------------------------------------------------------------

Opened a PR: https://github.com/apache/calcite/pull/2025


I didn't find an easy to to configure per operator `alwaysUseParentheses` because `SqlWriterConfig.alwaysUseParentheses()` does not accept operator as parameter. 

I ended up with checking UNNEST in `SqlCall.unparse()`.

 


was (Author: amaliujia):
Opened a PR: https://github.com/apache/calcite/pull/2025


I didn't find an easy to to configure per operator `alwaysUseParentheses` because `SqlWriterConfig.alwaysUseParentheses()` does not accept operator as parameter. 

I ended up with checking UNNEST in `SqlCall.unparse().

 

> 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
>            Assignee: Rui Wang
>            Priority: Major
>          Time Spent: 20m
>  Remaining Estimate: 0h
>
> 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)