[CALCITE-3440] RelToSqlConverter does not properly alias ambiguous ORDER BY

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.22.0
• Assignee: Julian Hyde
• Reporter: Steven Talbot
• Created: 23/Oct/19 16:47
• Updated: 21/Jan/22 02:47
• Resolved: 24/Oct/19 23:32

Description

Best illustrated by a test that will fail if you put it in RelToSqlConverterTest

 

@Test public void testSelectQueryWithAmbiguousOrderByClause() {
  String query = "select \"product_id\" as \"p\", \"net_weight\" as \"product_id\"  from \"product\" order by 1";
  final String expected = "SELECT \"product_id\" AS \"p\", \"net_weight\" AS \"product_id\"\n"
          + "FROM \"foodmart\".\"product\"\n"
          + "ORDER BY \"product\".\"product_id\"";
  sql(query).ok(expected);
}

 

Kind of a strange case, but basically you select a column, alias it, and order by it. And then you select something else and alias it as that column. RelToSqlConverter will ORDER BY just the column, unaliased ("product_id"), which, at least on HyperSQL and MySQL where I tested, ends up ordering by the aliased expression ("net_weight" as "product_id") vs the column  (either "product"."product_id" or "p" would work in this case).

Issue Links

• Blocked: CALCITE-4987 JDBC adapter generates incorrect query when ORDER BY alias collides with field in SELECT ITEM
• is related to: CALCITE-2143 RelToSqlConverter produces incorrect SQL with aggregation
• relates to: CALCITE-3593 RelToSqlConverter changes target of ambiguous HAVING clause with a Project on Filter on Aggregate