[CALCITE-5044] JDBC adapter generates integer literal in ORDER BY, which some dialects wrongly interpret as a reference to a field

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.31.0
• Labels: pull-request-available
• Assignee: Xurenhe
• Reporter: Xurenhe
• Created: 15/Mar/22 11:05
• Updated: 03/Aug/22 16:21
• Resolved: 25/Mar/22 02:48

Description

When there is a Sort RelNode that references an integer literal, the JDBC adapter converts this to a SQL ORDER BY clause that contains an integer literal as a sort key. Some dialects (most dialects, in fact) wrongly interpret as a reference to a field.

There is ambiguous in the rel-to-sql's converter when meeting `order by constant `.

Here is an example:

@Test void testOderByConstant() {
  final Function<RelBuilder, RelNode> relFn = b -> b
      .scan("EMP")
      .project(b.literal(1), b.field(1), b.field(2))
      .sort(RelCollations.of(ImmutableList.of(
          new RelFieldCollation(0), new RelFieldCollation(1))))
      .project(b.field(2), b.field(1))
      .build();
  final String expected = "";
  relFn(relFn).ok(expected);
} 

Return sql:

SELECT "JOB", "ENAME"
FROM "scott"."EMP"
ORDER BY 1, "ENAME" 

But, `ORDER BY 1` may mean 'sort by the first column in the select list'  because of SqlConformance#isSortByOrdinal.

I keep this the same semantics, by apply the rule of `CoreRules#SORT_REMOVE_CONSTANT_KEYS`

Should the rel_to_sql's converter support it natively?

CODE: https://github.com/wojustme/calcite/blob/6ad04e85875d9202e592a78cca8db807c5bde362/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1601

Issue Links

• links to: GitHub Pull Request #2749