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 <[email protected]> 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 <[email protected]> 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 >
