[CALCITE-5768] Rel-to-sql conversion fails to insert a subquery for some sort-by-ordinal queries

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.35.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Will Noble
• Reporter: Will Noble
• Created: 08/Jun/23 01:15
• Updated: 27/Jul/23 10:22
• Resolved: 08/Jun/23 21:23

Description

Currently, when given a rel expression like this:

LogicalProject(JOB=[$0])
  LogicalSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
    LogicalAggregate(group=[{2}], agg#0=[COUNT($1)])
      LogicalTableScan(table=[[scott, EMP]])

Calcite will convert to this invalid (ORDER BY 2 despite only one SELECT expression) SQL:

SELECT "JOB"
FROM "scott"."EMP"
GROUP BY "JOB"
ORDER BY "JOB", 2

When it should convert to this SQL with a sub-query:

SELECT "JOB"
FROM (SELECT "JOB", COUNT("ENAME") AS "$f1"
FROM "scott"."EMP"
GROUP BY "JOB"
ORDER BY "JOB", 2) AS "t0"

The problem is that the hasSortByOrdinal method sees a non-ordinal "JOB" in the ORDER BY clause and incorrectly says that there are no ordinals, due to an improper early return. It should only return early if the result is true. Otherwise, it should continue searching for ordinals.

When the method is fixed, a subquery is created.

Issue Links

• links to: GitHub Pull Request #3254