This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 8fd8fcca02240b04b21fa76a8ca8e147dd227ed3 Author: Julian Hyde <[email protected]> AuthorDate: Wed Oct 23 13:21:16 2019 -0700 [CALCITE-3440] RelToSqlConverter does not properly alias ambiguous ORDER BY --- .../apache/calcite/rel/rel2sql/SqlImplementor.java | 43 +++++++++++++++++++++- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 34 +++++++++++++++++ 2 files changed, 75 insertions(+), 2 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java index 8795cbb..7198e49 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java @@ -474,6 +474,18 @@ public abstract class SqlImplementor { public abstract SqlNode field(int ordinal); + /** Creates a reference to a field to be used in an ORDER BY clause. + * + * <p>By default, it returns the same result as {@link #field}. + * + * <p>If the field has an alias, uses the alias. + * If the field is an unqualified column reference which is the same an + * alias, switches to a qualified column reference. + */ + public SqlNode orderField(int ordinal) { + return field(ordinal); + } + /** Converts an expression from {@link RexNode} to {@link SqlNode} * format. * @@ -927,7 +939,7 @@ public abstract class SqlImplementor { /** Converts a collation to an ORDER BY item. */ public SqlNode toSql(RelFieldCollation collation) { - SqlNode node = field(collation.getFieldIndex()); + SqlNode node = orderField(collation.getFieldIndex()); switch (collation.getDirection()) { case DESCENDING: case STRICTLY_DESCENDING: @@ -1149,7 +1161,7 @@ public abstract class SqlImplementor { clauseList.addAll(this.clauses); } clauseList.appendAll(clauses); - Context newContext; + final Context newContext; final SqlNodeList selectList = select.getSelectList(); if (selectList != null) { newContext = new Context(dialect, selectList.size()) { @@ -1161,6 +1173,33 @@ public abstract class SqlImplementor { } return selectItem; } + + @Override public SqlNode orderField(int ordinal) { + // If the field expression is an unqualified column identifier + // and matches a different alias, use an ordinal. + // For example, given + // SELECT deptno AS empno, empno AS x FROM emp ORDER BY emp.empno + // we generate + // SELECT deptno AS empno, empno AS x FROM emp ORDER BY 2 + // "ORDER BY empno" would give incorrect result; + // "ORDER BY x" is acceptable but is not preferred. + final SqlNode node = field(ordinal); + if (node instanceof SqlIdentifier + && ((SqlIdentifier) node).isSimple()) { + final String name = ((SqlIdentifier) node).getSimple(); + for (Ord<SqlNode> selectItem : Ord.zip(selectList)) { + if (selectItem.i != ordinal) { + final String alias = + SqlValidatorUtil.getAlias(selectItem.e, -1); + if (name.equalsIgnoreCase(alias)) { + return SqlLiteral.createExactNumeric( + Integer.toString(ordinal + 1), SqlParserPos.ZERO); + } + } + } + } + return node; + } }; } else { boolean qualified = diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java index d3045eb..3a1aa61 100644 --- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java @@ -824,6 +824,40 @@ public class RelToSqlConverterTest { sql(query).ok(expected); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-3440";>[CALCITE-3440] + * RelToSqlConverter does not properly alias ambiguous ORDER BY</a>. */ + @Test public void testOrderByColumnWithSameNameAsAlias() { + String query = "select \"product_id\" as \"p\",\n" + + " \"net_weight\" as \"product_id\"\n" + + "from \"product\"\n" + + "order by 1"; + final String expected = "SELECT \"product_id\" AS \"p\"," + + " \"net_weight\" AS \"product_id\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "ORDER BY 1"; + sql(query).ok(expected); + } + + @Test public void testOrderByColumnWithSameNameAsAlias2() { + // We use ordinal "2" because the column name "product_id" is obscured + // by alias "product_id". + String query = "select \"net_weight\" as \"product_id\",\n" + + " \"product_id\" as \"product_id\"\n" + + "from \"product\"\n" + + "order by \"product\".\"product_id\""; + final String expected = "SELECT \"net_weight\" AS \"product_id\"," + + " \"product_id\" AS \"product_id0\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "ORDER BY 2"; + final String expectedMysql = "SELECT `net_weight` AS `product_id`," + + " `product_id` AS `product_id0`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` IS NULL, 2"; + sql(query).ok(expected) + .withMysql().ok(expectedMysql); + } + @Test public void testHiveSelectCharset() { String query = "select \"hire_date\", cast(\"hire_date\" as varchar(10)) " + "from \"foodmart\".\"reserve_employee\"";
