[jira] [Created] (CALCITE-4820) INSERT Queries have keyword ROW inserted VALUES

["Charles Givre (Jira)" <ji...@apache.org>]

Charles Givre created CALCITE-4820:
--------------------------------------

             Summary: INSERT Queries have keyword ROW inserted VALUES
                 Key: CALCITE-4820
                 URL: https://issues.apache.org/jira/browse/CALCITE-4820
             Project: Calcite
          Issue Type: Bug
          Components: jdbc-adapter
    Affects Versions: 1.27.0
            Reporter: Charles Givre


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



--
This message was sent by Atlassian Jira
(v8.3.4#803005)