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

JDBC adapter incorrectly adds ORDER BY columns to the SELECT list of generated SQL query

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.26.0
    • 1.30.0
    • core
    • jdk8

    Description

      RelToSqlConverter adds redundant order by output column for the result sql. In RelToSqlConverterTest, I add the following case

      @Test void testOrderByNotInSelectList() {
        String query = "select count(1)\n"
            + "from \"foodmart\".\"product\"\n"
            + "group by \"product_id\"\n"
            + "order by \"product_id\" desc\n";
        final String expected = "SELECT COUNT(1)\n"
            + "FROM \"foodmart\".\"product\"\n"
            + "GROUP BY \"product_id\"\n"
            + "ORDER BY \"product_id\" desc\n";
        sql(query).withBigQuery().ok(expected);
      }

      But I get the actual sql is

      SELECT COUNT(*), product_id
      FROM foodmart.product
      GROUP BY product_id
      ORDER BY product_id IS NULL DESC, product_id DESC 

      It has redundant product_id output column.

       

      Wage Through code, I find that the sort operator contains product_id. When I put a project with product_id only on the sort operator, the result sql has redundant subquery.

      LogicalProject(EXPR$0=[$0])
        LogicalSort(sort0=[$1], dir0=[DESC])
          LogicalProject(EXPR$0=[$1], product_id=[$0])
            LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
              LogicalProject(product_id=[$1])
                JdbcTableScan(table=[[foodmart, product]]) 
      SELECT `EXPR$0`
      FROM (SELECT COUNT(*) AS `EXPR$0`, product_id
      FROM foodmart.product
      GROUP BY product_id
      ORDER BY product_id IS NULL DESC, product_id DESC) AS t2 

       

       

      Attachments

        Issue Links

        Activity

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

          People

            Unassigned Unassigned
            yanjing.wang yanjing.wang
            Votes:
            0 Vote for this issue
            Watchers:
            5 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 - 1h 20m
                1h 20m

                Slack

                  Issue deployment