[PG Bug reporting form <noreply@postgresql.org>]

The following bug has been logged on the website:

Bug reference:      18589
Logged by:          Quynh Tran
Email address:      tlhquynh@gmail.com
PostgreSQL version: 16.3
Operating system:   cloud.google.com/container-optimized-os
Description:        

Hi,

In PostgreSQL 16.3, after I created a view by a statement like 
"create view kvview as select key as value, value as key from kv order by
value",
I retrieved a different definition from information_schema.views
"SELECT key AS value, value AS key FROM kv ORDER BY key".
I expect ORDER BY column be value.

I tried the same in PostgreSQL 14.12 and 15.7 and got correct equivalent
definitions.
"SELECT keyvalue.key AS value, keyvalue.value AS key FROM keyvalue ORDER BY
keyvalue.key"

So this seems to be a regression.

Below are steps to reproduce in PostgreSQL 16.3:
> create table kv (key int primary key, value text);
Query OK, 0 rows affected (5.27 sec)

> insert into kv values (1, 'z'), (2, 'z'), (3, 'y');
Query OK, 3 rows affected (0.40 sec)

> select key as value, value as key from kv order by value;  -- This is what
we want to see.
+-------+-----+
| value | key |
+-------+-----+
| 1     | z   |
| 2     | z   |
| 3     | y   |
+-------+-----+
3 rows in set (4.04 msecs)

> create view kvview as select key as value, value as key from kv order by
value;  -- Create view with same definition as the query above.
Query OK, 0 rows affected (11.00 sec)

> select * from kvview;  -- View also has correct result.
+-------+-----+
| value | key |
+-------+-----+
| 1     | z   |
| 2     | z   |
| 3     | y   |
+-------+-----+
3 rows in set (4.05 msecs)

> select table_name, view_definition from information_schema.views;  -- But
information_schema displays the wrong view definition!
+------------+------------------------------------------------------------+
| table_name | view_definition                                           
|
+------------+------------------------------------------------------------+
| kvview | SELECT key AS value, value AS key FROM kv ORDER BY key |
+------------+------------------------------------------------------------+
1 rows in set (11.67 msecs)

> select key as value, value as key from kv order by key;  -- The view
definition would give the wrong result if that were what was actually
executed.
+-------+-----+
| value | key |
+-------+-----+
| 3     | y   |
| 1     | z   |
| 2     | z   |
+-------+-----+
3 rows in set (16.76 msecs)