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

JDBC adapter for Spark generates incorrect ORDER BY syntax

Rank to TopRank to BottomBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersConvert to sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.26.0
    • 1.27.0
    • core
    • 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

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            julianhyde Julian Hyde
            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

                Slack

                  Issue deployment