[CALCITE-4901] JDBC adapter incorrectly adds ORDER BY columns to the SELECT list of generated SQL query

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

Environment

jdk8

• Assignee: Unassigned
• Reporter: yanjing.wang
• Created: 24/Nov/21 12:27
• Updated: 19/Mar/22 09:20
• Resolved: 10/Jan/22 09:06

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 

 

 

Issue Links

• relates to: CALCITE-4998 Subquery Regression
• links to: GitHub Pull Request #2665