Using table alias to qualify column name in ORDER BY results in error on SELECT DISTINCT

[joelind]

Describe the bug

When using SELECT DISTINCT with a table alias, we get a syntax error telling us that ORDER BY expressions must appear in select list, even though the column is in the select list (but is aliased).

Error message/log

psql -h localhost -p 4566 -U root -d dev <<SQL
with integers as (
  select 1 as numeral, 'first' as ordinal
)

select distinct numeral, ordinal
from integers i
order by i.numeral
SQL
ERROR:  Failed to run the query

Caused by:
  Invalid input syntax: for SELECT DISTINCT, ORDER BY expressions must appear in select list

To Reproduce

This works:

psql -h localhost -p 4566 -U root -d dev <<SQL
with integers as (
  select 1 as numeral, 'first' as ordinal
)

select distinct numeral, ordinal
from integers i
order by numeral
SQL
 numeral | ordinal
---------+---------
       1 | first
(1 row)

Whereas this doesn't (only difference is that we use order by *i*.numeral:

psql -h localhost -p 4566 -U root -d dev <<SQL
with integers as (
  select 1 as numeral, 'first' as ordinal
)

select distinct numeral, ordinal
from integers i
order by i.numeral
SQL
ERROR:  Failed to run the query

Caused by:
  Invalid input syntax: for SELECT DISTINCT, ORDER BY expressions must appear in select list

I also tried ensuring that it's the literal same expression in the select list, but that fails as well:

psql -h localhost -p 4566 -U root -d dev <<SQL
with integers as (
  select 1 as numeral, 'first' as ordinal
)

select distinct i.numeral, ordinal
from integers i
order by i.numeral
SQL
ERROR:  Failed to run the query

Caused by:
  Invalid input syntax: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Expected behavior

I expected to be able to use the table alias to qualify the column name in the ORDER BY clause of my query.

How did you deploy RisingWave?

Both via docker-compose.yml locally, and RisingWave Cloud.

The version of RisingWave

 psql -h localhost -p 4566 -U root -d dev <<SQL
select version()
SQL
                                    version
--------------------------------------------------------------------------------
 PostgreSQL 13.14.0-RisingWave-1.8.1 (5b6bef82dc55638c024190654c7ef9896e21687d)
(1 row)

Additional context

No response

[xiangjinwu]

Thanks for reporting the issue. We will try to get it fixed.

However, the preferred syntax of ORDER BY is to use the unqualified output name, rather than a (qualified or unqualified) input name.

select distinct numeral as column_a, ordinal
from integers i
order by column_a; -- This is referring to the output after distinct.

select distinct numeral as column_a, ordinal
from integers i
order by numeral; -- This now fails similar to `i.numeral` because RisingWave fails to recognize it is same column as `column_a`

[joelind]

Thanks - good to know!