[jira] [Created] (CALCITE-5185) The required parentheses for WITH ITEM QUERY are missing when SqlPrettyWriter.config().withAlwaysUseParentheses(false)

["yanjing.wang (Jira)" <ji...@apache.org>]

yanjing.wang created CALCITE-5185:
-------------------------------------

             Summary: The required parentheses for WITH ITEM QUERY are missing when SqlPrettyWriter.config().withAlwaysUseParentheses(false)
                 Key: CALCITE-5185
                 URL: https://issues.apache.org/jira/browse/CALCITE-5185
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.30.0
            Reporter: yanjing.wang
            Assignee: yanjing.wang


Add the following test to SqlParserTest
{code:java}
  @Test void testWithUnion2() {
    // Per the standard WITH ... SELECT ... UNION is valid even without parens.
    final String sql = "with emp2 as (select * from emp union all select * from emp)\n"
        + "select * from emp2\n"
        + "union\n"
        + "select * from emp2\n";
    final String expected = "WITH `EMP2` AS (SELECT *\n"
        + "FROM `EMP`\n"
        + "UNION ALL\n"
        + "SELECT *\n"
        + "FROM `EMP`) (SELECT *\n"
        + "FROM `EMP2`\n"
        + "UNION\n"
        + "SELECT *\n"
        + "FROM `EMP2`)";
    sql(sql).ok(expected);
  } {code}
when I change SqlPrettyWriter.config().withAlwaysUseParentheses(true) to           .withAlwaysUseParentheses(false), the actual sql is
{code:java}
WITH `EMP2` AS SELECT *
FROM `EMP`
UNION ALL
SELECT *
FROM `EMP` (SELECT *
FROM `EMP2`
UNION
SELECT *
FROM `EMP2`) {code}
the sql can't be parsed by Calcite, PostgreSQL, BigQuery because WITH ITEM QUERY isn't wrapped by parentheses.

In addition, It should be better to issue a newline between WITH ITEM QUERYs and QUERY BODY.



--
This message was sent by Atlassian Jira
(v8.20.7#820007)