[CALCITE-4820] INSERT Queries have keyword ROW inserted VALUES

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.27.0
• Component/s: jdbc-adapter
• Assignee: Unassigned
• Reporter: Charles Givre
• Created: 03/Oct/21 00:03
• Updated: 04/Oct/21 09:00

Description

When parsing INSERT queries, Calcite inserts the keyword ROW in the VALUES clause.  This causes queries to fail in MySQL and other databases.  

The function below produces the following output:

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')

Ideally, the end result should be a valid INSERT query for the dialect used, and without the ROW keyword in the VALUES clause.

 

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;
{{  }}}
}

Issue Links

• Blocked: DRILL-8005 Add Writer to JDBC Storage Plugin