You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by Yanjing Wang <zh...@gmail.com> on 2022/06/07 12:18:41 UTC

[DISCUSS] WithItem query parentheses

Hi community,

the test follows
  @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);
  }

when I change SqlPrettyWriter.config()
          .withAlwaysUseParentheses(true)
to
          .withAlwaysUseParentheses(false)

the test produces sql
WITH `EMP2` AS SELECT *
FROM `EMP`
UNION ALL
SELECT *
FROM `EMP` (SELECT *
FROM `EMP2`
UNION
SELECT *
FROM `EMP2`)

but the sql can't be parsed by calcite.

I find postgreSQL and BigQuery both need parentheses for the with item
query.

Should we always force the with item query to be wrapped by parentheses?

Re: [DISCUSS] WithItem query parentheses

Posted by Yanjing Wang <zh...@gmail.com>.

Thanks Julian for your nice guidance, I logged this bug onto CALCITE-5185
<https://issues.apache.org/jira/browse/CALCITE-5185>, and I will go to any
lengths to fix it soon.

Julian Hyde <jh...@gmail.com> 于2022年6月7日周二 22:28写道：

> Yes. The parentheses are required for correctness.  Please log a bug.
>
> While you’re there, it would be nice if there were a line break after each
> CTE. (Including if there are multiple CTEs.) Maybe the fix can address that
> too.
>
> Julian
>
> > On Jun 7, 2022, at 05:19, Yanjing Wang <zh...@gmail.com>
> wrote:
> >
> > Hi community,
> >
> > the test follows
> >  @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);
> >  }
> >
> > when I change SqlPrettyWriter.config()
> >          .withAlwaysUseParentheses(true)
> > to
> >          .withAlwaysUseParentheses(false)
> >
> > the test produces sql
> > WITH `EMP2` AS SELECT *
> > FROM `EMP`
> > UNION ALL
> > SELECT *
> > FROM `EMP` (SELECT *
> > FROM `EMP2`
> > UNION
> > SELECT *
> > FROM `EMP2`)
> >
> > but the sql can't be parsed by calcite.
> >
> > I find postgreSQL and BigQuery both need parentheses for the with item
> > query.
> >
> > Should we always force the with item query to be wrapped by parentheses?
>

Re: [DISCUSS] WithItem query parentheses

Posted by Julian Hyde <jh...@gmail.com>.

Yes. The parentheses are required for correctness.  Please log a bug. 

While you’re there, it would be nice if there were a line break after each CTE. (Including if there are multiple CTEs.) Maybe the fix can address that too. 

Julian

> On Jun 7, 2022, at 05:19, Yanjing Wang <zh...@gmail.com> wrote:
> 
> Hi community,
> 
> the test follows
>  @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);
>  }
> 
> when I change SqlPrettyWriter.config()
>          .withAlwaysUseParentheses(true)
> to
>          .withAlwaysUseParentheses(false)
> 
> the test produces sql
> WITH `EMP2` AS SELECT *
> FROM `EMP`
> UNION ALL
> SELECT *
> FROM `EMP` (SELECT *
> FROM `EMP2`
> UNION
> SELECT *
> FROM `EMP2`)
> 
> but the sql can't be parsed by calcite.
> 
> I find postgreSQL and BigQuery both need parentheses for the with item
> query.
> 
> Should we always force the with item query to be wrapped by parentheses?