From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | tlhquynh(at)gmail(dot)com |
Subject: | BUG #18589: pg_get_viewdef returns wrong query |
Date: | 2024-08-26 06:42:36 |
Message-ID: | 18589-70091cb81db1a3f1@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18589
Logged by: Quynh Tran
Email address: tlhquynh(at)gmail(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2024-08-26 08:39:41 | BUG #18590: during pg14 to pg15 migration , old passwords not migrated to scram-sha from md5 |
Previous Message | Maxim Boguk | 2024-08-25 20:31:44 | Re: BUG #18588: Cannot force/let database use parallel execution in simple case. |