You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Charles Givre <cg...@gmail.com> on 2021/10/01 19:07:47 UTC
INSERT Query Question
Hello Calcite team,
I have a quick question. I'm looking to take an INSERT query, parse it and convert it into the dialect of various databases. The Inserts will not be complicated. For instance:
INSERT INTO mysql_test.data_types
VALUES(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
Converted into:
Postgres: INSERT INTO "postgresl_test"."data_types"
VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
MySQL: INSERT INTO `mysql_test`.`data_types`
VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
MSSQL: INSERT INTO [mssql_test].[data_types]
VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
I wrote a function that does this, however as you'll note above, that Calcite is inserting the word ROW before each row to be inserted and this is not correct SQL.
public static String cleanQuery(String query, SqlDialect dialect) {
SqlParser.Config sqlParserConfig = SqlParser.configBuilder()
.setParserFactory(SqlDdlParserImpl.FACTORY)
.setConformance(SqlConformanceEnum.MYSQL_5)
.setCaseSensitive(true)
.setLex(Lex.MYSQL)
.build();
try {
SqlNode node = SqlParser.create(query, sqlParserConfig).parseQuery();
return node.toSqlString(dialect).getSql();
} catch (SqlParseException e) {
return null;
}
}
Is there some way to configure Calcite not to insert the word ROW? Thanks!
-- C
Re: INSERT Query Question
Posted by Charles Givre <cg...@gmail.com>.
Hi Julian,
Thanks for your response. I created CALCITE-4820 (https://issues.apache.org/jira/browse/CALCITE-4820 <https://issues.apache.org/jira/browse/CALCITE-4820>) to address this. With that said, do you think there's a configuration setting somewhere that I missed that removes the ROW from the query? It appears in the test that you sent (RelToSqlConverterTest.testRowValueExpression) that the queries are being generated without the ROW keyword, so my initial hunch is that I missed a step or config somewhere.
Thanks!
-- C
> On Oct 2, 2021, at 1:02 AM, Julian Hyde <jh...@gmail.com> wrote:
>
> I’m not sure whether ROW is ‘correct’ SQL, but I agree that it’s not idiomatic SQL to use ROW inside a VALUES inside an INSERT.
>
> SQL generation is controlled by the SqlDialect class. Typically we add tests to RelToSqlConverterTest. https://issues.apache.org/jira/browse/CALCITE-3344 <https://issues.apache.org/jira/browse/CALCITE-3344> is a good example of this kind of change.
>
> I see that RelToSqlConverterTest.testRowValueExpression has a few examples of this kind of query. Maybe it needs to be expanded for other dialects.
>
> Can you log a JIRA case for this?
>
> Julian
>
>
>
>> On Oct 1, 2021, at 2:07 PM, Charles Givre <cg...@gmail.com> wrote:
>>
>> Hello Calcite team,
>> I have a quick question. I'm looking to take an INSERT query, parse it and convert it into the dialect of various databases. The Inserts will not be complicated. For instance:
>>
>> INSERT INTO mysql_test.data_types
>> VALUES(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
>>
>> Converted into:
>>
>> Postgres: INSERT INTO "postgresl_test"."data_types"
>> VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
>> MySQL: INSERT INTO `mysql_test`.`data_types`
>> VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
>> MSSQL: INSERT INTO [mssql_test].[data_types]
>> VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
>>
>> I wrote a function that does this, however as you'll note above, that Calcite is inserting the word ROW before each row to be inserted and this is not correct SQL.
>>
>> public static String cleanQuery(String query, SqlDialect dialect) {
>> SqlParser.Config sqlParserConfig = SqlParser.configBuilder()
>> .setParserFactory(SqlDdlParserImpl.FACTORY)
>> .setConformance(SqlConformanceEnum.MYSQL_5)
>> .setCaseSensitive(true)
>> .setLex(Lex.MYSQL)
>> .build();
>>
>> try {
>> SqlNode node = SqlParser.create(query, sqlParserConfig).parseQuery();
>> return node.toSqlString(dialect).getSql();
>> } catch (SqlParseException e) {
>> return null;
>> }
>> }
>>
>> Is there some way to configure Calcite not to insert the word ROW? Thanks!
>> -- C
>
Re: INSERT Query Question
Posted by Julian Hyde <jh...@gmail.com>.
I’m not sure whether ROW is ‘correct’ SQL, but I agree that it’s not idiomatic SQL to use ROW inside a VALUES inside an INSERT.
SQL generation is controlled by the SqlDialect class. Typically we add tests to RelToSqlConverterTest. https://issues.apache.org/jira/browse/CALCITE-3344 <https://issues.apache.org/jira/browse/CALCITE-3344> is a good example of this kind of change.
I see that RelToSqlConverterTest.testRowValueExpression has a few examples of this kind of query. Maybe it needs to be expanded for other dialects.
Can you log a JIRA case for this?
Julian
> On Oct 1, 2021, at 2:07 PM, Charles Givre <cg...@gmail.com> wrote:
>
> Hello Calcite team,
> I have a quick question. I'm looking to take an INSERT query, parse it and convert it into the dialect of various databases. The Inserts will not be complicated. For instance:
>
> INSERT INTO mysql_test.data_types
> VALUES(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
>
> Converted into:
>
> Postgres: INSERT INTO "postgresl_test"."data_types"
> VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
> MySQL: INSERT INTO `mysql_test`.`data_types`
> VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
> MSSQL: INSERT INTO [mssql_test].[data_types]
> VALUES ROW(1, 2, 3.0, 4.0, '5.0', '2020-12-31', '12:00:00', '2015-12-30 17:55:55')
>
> I wrote a function that does this, however as you'll note above, that Calcite is inserting the word ROW before each row to be inserted and this is not correct SQL.
>
> public static String cleanQuery(String query, SqlDialect dialect) {
> SqlParser.Config sqlParserConfig = SqlParser.configBuilder()
> .setParserFactory(SqlDdlParserImpl.FACTORY)
> .setConformance(SqlConformanceEnum.MYSQL_5)
> .setCaseSensitive(true)
> .setLex(Lex.MYSQL)
> .build();
>
> try {
> SqlNode node = SqlParser.create(query, sqlParserConfig).parseQuery();
> return node.toSqlString(dialect).getSql();
> } catch (SqlParseException e) {
> return null;
> }
> }
>
> Is there some way to configure Calcite not to insert the word ROW? Thanks!
> -- C