You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Christophe Le Saëc <ch...@gmail.com> on 2022/10/10 08:24:21 UTC
SqlMerge
According to Sql Merge definition
<https://en.wikipedia.org/wiki/Merge_(SQL)> (at least on wikipedia), the
insert part is a classical insert statement :
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES
(value1 [, value2 ...]);
The SqlMerge class, on toSqlString; add extra parenthesis before VALUES
INSERT (column1 [, column2 ...]) *(*VALUES (value1 [, value2 ...])*)*
And it's confirm by unit test
<https://github.com/apache/calcite/blob/main/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java#L4651-L4652>
Which expect
+ "WHEN NOT MATCHED THEN INSERT (`NAME`, `DEPT`, `SALARY`) "
+ "(VALUES (ROW(`T`.`NAME`, 10, (`T`.`SALARY` * 0.15))))";
When i try to run thus kind of generated MERGE statement with H2SqlDialect,
it does not work (*JdbcSQLSyntaxErrorException: Syntax error in SQL
statement*) until i remove this extra parenthesis manually (on step by step
debug).
As i'm a newbie at calcite, i don't know if it's me who using calcite
wrongly or if it's a bug ?
Regards,
Christophe.
Re: SqlMerge
Posted by Christophe Le Saëc <ch...@gmail.com>.
So, the PR is done <https://github.com/apache/calcite/pull/2936>.
Le mar. 11 oct. 2022 à 11:09, Christophe Le Saëc <ch...@gmail.com> a
écrit :
> I made this JIRA <https://issues.apache.org/jira/browse/CALCITE-5326>
> with as details as possible.
>
> Le lun. 10 oct. 2022 à 14:45, Benchao Li <li...@apache.org> a écrit :
>
>> Christophe,
>>
>> Thanks for reaching out. This might be a bug, please log a Jira issue.
>>
>> Recently there are several issues[1][2][3] which we found that some places
>> are adding
>> redundant parentheses which are not necessary. We firstly only treated
>> these issues
>> are improvements, but we found that some of them are not correct in some
>> SQL dialects,
>> your case might be one of them.
>>
>> [1] https://issues.apache.org/jira/browse/CALCITE-5299
>> [2] https://issues.apache.org/jira/browse/CALCITE-5265
>> [3] https://issues.apache.org/jira/browse/CALCITE-5252
>>
>> Christophe Le Saëc <ch...@gmail.com> 于2022年10月10日周一 16:24写道：
>>
>> > According to Sql Merge definition
>> > <https://en.wikipedia.org/wiki/Merge_(SQL)> (at least on wikipedia),
>> the
>> > insert part is a classical insert statement :
>> >
>> > WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES
>> > (value1 [, value2 ...]);
>> >
>> > The SqlMerge class, on toSqlString; add extra parenthesis before VALUES
>> >
>> > INSERT (column1 [, column2 ...]) *(*VALUES (value1 [, value2 ...])*)*
>> >
>> > And it's confirm by unit test
>> > <
>> >
>> https://github.com/apache/calcite/blob/main/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java#L4651-L4652
>> > >
>> >
>> > Which expect
>> >
>> > + "WHEN NOT MATCHED THEN INSERT (`NAME`, `DEPT`, `SALARY`) "
>> >
>> > + "(VALUES (ROW(`T`.`NAME`, 10, (`T`.`SALARY` * 0.15))))";
>> >
>> > When i try to run thus kind of generated MERGE statement with
>> H2SqlDialect,
>> > it does not work (*JdbcSQLSyntaxErrorException: Syntax error in SQL
>> > statement*) until i remove this extra parenthesis manually (on step by
>> step
>> > debug).
>> > As i'm a newbie at calcite, i don't know if it's me who using calcite
>> > wrongly or if it's a bug ?
>> >
>> > Regards,
>> > Christophe.
>> >
>>
>>
>> --
>>
>> Best,
>> Benchao Li
>>
>
Re: SqlMerge
Posted by Christophe Le Saëc <ch...@gmail.com>.
I made this JIRA <https://issues.apache.org/jira/browse/CALCITE-5326> with
as details as possible.
Le lun. 10 oct. 2022 à 14:45, Benchao Li <li...@apache.org> a écrit :
> Christophe,
>
> Thanks for reaching out. This might be a bug, please log a Jira issue.
>
> Recently there are several issues[1][2][3] which we found that some places
> are adding
> redundant parentheses which are not necessary. We firstly only treated
> these issues
> are improvements, but we found that some of them are not correct in some
> SQL dialects,
> your case might be one of them.
>
> [1] https://issues.apache.org/jira/browse/CALCITE-5299
> [2] https://issues.apache.org/jira/browse/CALCITE-5265
> [3] https://issues.apache.org/jira/browse/CALCITE-5252
>
> Christophe Le Saëc <ch...@gmail.com> 于2022年10月10日周一 16:24写道：
>
> > According to Sql Merge definition
> > <https://en.wikipedia.org/wiki/Merge_(SQL)> (at least on wikipedia), the
> > insert part is a classical insert statement :
> >
> > WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES
> > (value1 [, value2 ...]);
> >
> > The SqlMerge class, on toSqlString; add extra parenthesis before VALUES
> >
> > INSERT (column1 [, column2 ...]) *(*VALUES (value1 [, value2 ...])*)*
> >
> > And it's confirm by unit test
> > <
> >
> https://github.com/apache/calcite/blob/main/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java#L4651-L4652
> > >
> >
> > Which expect
> >
> > + "WHEN NOT MATCHED THEN INSERT (`NAME`, `DEPT`, `SALARY`) "
> >
> > + "(VALUES (ROW(`T`.`NAME`, 10, (`T`.`SALARY` * 0.15))))";
> >
> > When i try to run thus kind of generated MERGE statement with
> H2SqlDialect,
> > it does not work (*JdbcSQLSyntaxErrorException: Syntax error in SQL
> > statement*) until i remove this extra parenthesis manually (on step by
> step
> > debug).
> > As i'm a newbie at calcite, i don't know if it's me who using calcite
> > wrongly or if it's a bug ?
> >
> > Regards,
> > Christophe.
> >
>
>
> --
>
> Best,
> Benchao Li
>
Re: SqlMerge
Posted by Benchao Li <li...@apache.org>.
Christophe,
Thanks for reaching out. This might be a bug, please log a Jira issue.
Recently there are several issues[1][2][3] which we found that some places
are adding
redundant parentheses which are not necessary. We firstly only treated
these issues
are improvements, but we found that some of them are not correct in some
SQL dialects,
your case might be one of them.
[1] https://issues.apache.org/jira/browse/CALCITE-5299
[2] https://issues.apache.org/jira/browse/CALCITE-5265
[3] https://issues.apache.org/jira/browse/CALCITE-5252
Christophe Le Saëc <ch...@gmail.com> 于2022年10月10日周一 16:24写道：
> According to Sql Merge definition
> <https://en.wikipedia.org/wiki/Merge_(SQL)> (at least on wikipedia), the
> insert part is a classical insert statement :
>
> WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES
> (value1 [, value2 ...]);
>
> The SqlMerge class, on toSqlString; add extra parenthesis before VALUES
>
> INSERT (column1 [, column2 ...]) *(*VALUES (value1 [, value2 ...])*)*
>
> And it's confirm by unit test
> <
> https://github.com/apache/calcite/blob/main/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java#L4651-L4652
> >
>
> Which expect
>
> + "WHEN NOT MATCHED THEN INSERT (`NAME`, `DEPT`, `SALARY`) "
>
> + "(VALUES (ROW(`T`.`NAME`, 10, (`T`.`SALARY` * 0.15))))";
>
> When i try to run thus kind of generated MERGE statement with H2SqlDialect,
> it does not work (*JdbcSQLSyntaxErrorException: Syntax error in SQL
> statement*) until i remove this extra parenthesis manually (on step by step
> debug).
> As i'm a newbie at calcite, i don't know if it's me who using calcite
> wrongly or if it's a bug ?
>
> Regards,
> Christophe.
>
--
Best,
Benchao Li