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
