Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4463

JDBC adapter for Spark generates incorrect ORDER BY syntax

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.26.0
    • Fix Version/s: 1.27.0
    • Component/s: core
    • Environment:

      jvm: open-jdk8

      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.

       

        Attachments

        1. image-2021-01-12-10-06-29-813.png
          137 kB
          yanjing.wang

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                yanjing.wang yanjing.wang
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 20m
                  20m