[CALCITE-4463] JDBC adapter for Spark generates incorrect ORDER BY syntax

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.26.0
• Fix Version/s: 1.27.0
• Component/s: core
• Labels: pull-request-available

Environment

jvm: open-jdk8

• Assignee: Julian Hyde
• Reporter: yanjing.wang
• Created: 12/Jan/21 02:23
• Updated: 03/Jun/21 22:32
• Resolved: 13/Jan/21 20:37

Description

in SqlOrderBy$Operator class, the unparse method code "offset and fetch" hardly and close the door to transform my sql to "limit x offset y style".

 

 

my unparse sql code:

String sql = "select concat(a.id,'-',b.id) , a.name from xxx.bb limit 5";

SqlDialect SPARK = new
        SparkSqlDialect(SqlDialect.EMPTY_CONTEXT
        .withDatabaseProduct(SqlDialect.DatabaseProduct.SPARK)
        .withIdentifierQuoteString("`").withNullCollation(NullCollation.LOW)
        .withLiteralQuoteString("'").withLiteralEscapedQuoteString("''")
        .withUnquotedCasing(Casing.UNCHANGED).withQuotedCasing(Casing.UNCHANGED));

SqlParser.Config configBuilder =
        SqlParser.config()
                .withParserFactory(SqlBabelParserImpl.FACTORY)
                .withConformance(SqlConformanceEnum.LENIENT);

SqlParser sqlParser = SqlParser.create(sql, configBuilder);
try {
    SqlNode sqlNode = sqlParser.parseQuery();
    SqlString sqlString = sqlNode.toSqlString(SPARK);
    System.out.println(sqlString);
} catch (SqlParseException e) {
    e.printStackTrace();
}

result:

SELECT `CONCAT`(`A`.`ID`, '-', `B`.`ID`), `A`.`NAME` FROM `XXX`.`BB` FETCH NEXT 5 ROWS ONLY

the "limit 5" clause shouldn't be transformed to  "FETCH NEXT 5000 ROWS ONLY".

 

i dive into "parser.jj" file and find the following lines in "SqlSelect()" production.

{ return new SqlSelect(s.end(this), keywordList, new SqlNodeList(selectList, Span.of(selectList).pos()), fromClause, where, groupBy, having, windowDecls, null, null, null, new SqlNodeList(hints, getPos())); }

 

the "SqlSelect" sql node always receive null orderby, offset and fetch. so the "limit" clause will always be processed in  "SqlOrderBy" sql node, and the "unparse offset and fetch" can't be processed within specific sql dialect.

 

Issue Links

• links to: GitHub Pull Request #2324