`SELECT DISTINCT` with Delimited Alias in `ORDER BY` Errors (Case Sensitivity)

[jimsimon-wk]

A query using SELECT DISTINCT fails when attempting to order by a column alias that is a delimited identifier (e.g., "x"), even though the unquoted ORDER BY expression (X) should resolve to it via case-insensitive matching.

This specific issue—the failure to resolve the case-insensitive ORDER BY expression to the alias in a DISTINCT context—was previously addressed and fixed in PR #4233.

I suspect this to be a side effect of the modifications to Identifier#getCanonicalValue introduced by PR #6550, which appears to have broken the case-insensitive matching for delimited aliases in the ORDER BY clause.

The following minimal, reproducible example demonstrates the failure:

SELECT DISTINCT a as "x" FROM (VALUES 2, 1, 2) t(a) ORDER BY X

Expected Behavior
The query should execute resolving the identifier in the ORDER BY scope to reference the alias in the SELECT, regardless of case.

Actual Behavior

line 1:1: For SELECT DISTINCT, ORDER BY expressions must appear in select list

Here's patch for a reproducing test

diff --git a/core/trino-main/src/test/java/io/trino/sql/query/TestDistinctWithOrderBy.java b/core/trino-main/src/test/java/io/trino/sql/query/TestDistinctWithOrderBy.java
index 68b7447f8fd..867142211fc 100644
--- a/core/trino-main/src/test/java/io/trino/sql/query/TestDistinctWithOrderBy.java
+++ b/core/trino-main/src/test/java/io/trino/sql/query/TestDistinctWithOrderBy.java
@@ -72,6 +72,9 @@ public class TestDistinctWithOrderBy
         assertThat(assertions.query("SELECT DISTINCT a, b a FROM (VALUES (2, 10), (1, 20), (2, 10)) T(a, b) ORDER BY T.a"))
                 .ordered()
                 .matches("VALUES (1, 20), (2, 10)");
+
+        assertThat(assertions.query("SELECT DISTINCT a as \"x\" FROM (VALUES 2, 1, 2) t(a) ORDER BY X"))
+            .matches("VALUES 1, 2");
     }
 
     @Test

[findepi]

cc @Praveen2112

[Praveen2112]

@jimsimon-wk When we provide any object name (table, schema, columns or even alias) with a delimited identifier - the case sensitivity for the object will be preserved - The fix in the PR focused on case insensitive matching for non delimited identifiers i.e for queries SELECT DISTINCT a as X FROM (VALUES 2, 1, 2) t(a) ORDER BY x - in this case the column alias and the column mention in order by clause matches i.e both are equivalent to UPPER_CASE.

But for a query like SELECT DISTINCT a as "x" FROM (VALUES 2, 1, 2) t(a) ORDER BY X - but when as alias is with delimited then its case sensitivity aspect has to be maintained which is as per the SQL specifications.

The expected behaviour as per the Specification is that

The query should execute resolving the identifier in the ORDER BY scope to reference the alias in the SELECT, regardless of case provided either of identifiers are not delimited.

[Praveen2112]

I'll be closing this issue - Feel free to open it up or join our slack channel for further discussion.

[findepi]

-- this query projects `x` column
SELECT DISTINCT a as "x" FROM (VALUES 2, 1, 2) t(a);

-- the `x` column can be explicitly selected later using `X` undelimited identifier
SELECT X FROM (
    SELECT DISTINCT a as "x" FROM (VALUES 2, 1, 2) t(a)
);

-- it should also be possible to sort over that column, using `X` undelimited identifier again
SELECT DISTINCT a as "x" FROM (VALUES 2, 1, 2) t(a)
ORDER BY X;

[codluca]

At this moment, this test fails (line 1:1: For SELECT DISTINCT, ORDER BY expressions must appear in select list):

        assertThat(assertions.query("SELECT DISTINCT a AS x FROM (VALUES 2, 1, 2) t(a) ORDER BY \"x\""))
                .ordered()
                .matches("VALUES 1, 2");

x is treated as upper case: X
"x" is treated as it is: x

[codluca]

And, this test succeeds for SELECT (as opposed to SELECT DISTINCT):

for (String alias : new String[] {"x", "\"x\"", "X", "\"X\""}) {
    for (String orderBy : new String[] {"x", "\"x\"", "X", "\"X\""}) {
        assertThat(assertions.query("SELECT a AS " + alias + " FROM (VALUES 2, 1, 2) t(a) ORDER BY " + orderBy))
                .matches("VALUES 2, 1, 2");
    }
}

x, "x", X and "X" are treated as the same identifier.

[kasiafi]

The behavior described in the issue is actually correct. When resolving references, the canonical values of identifiers should be used. For X the canonical value is the uppercase X, while for "x" the canonical value is the lowercase x -- it's due to the quotes, otherwise it would be upper-cased.
As the result, there indeed is mismatch which should cause the query to fail.

I see that this is incoherent with Trino behavior in other contexts. However, it is the other contexts that behave incorrectly.

To make matters worse, I found that when there are multiple columns in SELECT DISTINCT, the ORDER BY columns are resolved case-insensitive...

trino:tpch> SELECT DISTINCT a as "x", b as X FROM (VALUES (2, 'x'), (1, 'y'), (2, 'z')) t(a, b) ORDER BY X;
Query 20251017_184847_00030_25mmt failed: line 1:94: Column 'x' is ambiguous

[kasiafi]

Investigating why the above happens:

• ORDER BY is analyzed against the SELECT expressions here. It is done case-insensitive, so for a query

SELECT DISTINCT a as "x", b as X FROM (VALUES (2, 'x'), (1, 'y'), (2, 'z')) t(a, b) ORDER BY X;

a as "x", b as X appear as ambiguous columns, and the query fails.

• however, the query with one selected column

SELECT DISTINCT a as "x" FROM (VALUES 2, 1, 2) t(a) ORDER BY X

passes this point, and then goes through a dedicated check for DISTINCT queries which uses the proper identifier semantics (here).