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)
