[SOLVED] Apache calcite adds ASYMMETRIC keyword for a between query

Apache calcite adds ASYMMETRIC keyword for a between query

I am using apache calcite 1.34.0 for converting ANSI SQL to Snowflake SQL for a use case. I am having issues for ANSI SQL queries containing 'BETWEEN' keyword.

The code below does the conversion from ANSI SQL to SNOWFLAKE SQL.

SqlParser sqlParser = SqlParser.create(ansiSql, SqlParser.configBuilder().setConfig(SqlParser.Config.DEFAULT).setLex(Lex.MYSQL_ANSI)
          .setConformance(SqlConformanceEnum.STRICT_99).build());
try {
       // Parse the ANSI SQL query
       SqlNode sqlNode = sqlParser.parseQuery();

       // Transform the parsed query to Snowflake SQL
       SqlDialect snowflakeDialect = new SqlDialect(SqlDialect.EMPTY_CONTEXT
                                                      .withDatabaseProduct(SqlDialect.DatabaseProduct.SNOWFLAKE));
       String snowflakeSql = sqlNode.toSqlString(snowflakeDialect).getSql();
       executeQuery(snowflakeSql);
} catch (Exception e) {
       e.printStackTrace();
}

For an ANSI SQL query SELECT * FROM DEPT WHERE DNO BETWEEN 1 AND 5, the value of sqlNode comes out like this SELECT * FROM DEPTWHEREDNO BETWEEN ASYMMETRIC 1 AND 5 and ultimately the corresponding snowflakeSql assumes this value SELECT * FROM DEPT WHERE DNO BETWEEN ASYMMETRIC 1 AND 5.

Since the ASYMMETRIC keyword is not supported by SNOWFLAKE SQL, upon executing this query, it throws below exception.

net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
    at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:144)
    at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77)
    at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:468)
    at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:374)
    at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:481)
    at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:199)
    at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:133)
    at net.snowflake.client.core.SFStatement.execute(SFStatement.java:767)
    at net.snowflake.client.core.SFStatement.execute(SFStatement.java:676)
    at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:274)
    at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery(SnowflakeStatementV1.java:140)

Is there any workaround for the same?

Solution

There is a bug logged for this: CALCITE-4471. There seems to be an incomplete pull request to fix it.