From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-committers(at)lists(dot)postgresql(dot)org |
Subject: | pgsql: Fix mis-deparsing of ORDER BY lists when there is a name conflic |
Date: | 2024-08-29 17:24:28 |
Message-ID: | E1sjisl-0020NG-UL@gemulon.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers |
Fix mis-deparsing of ORDER BY lists when there is a name conflict.
If an ORDER BY item in SELECT is a bare identifier, the parser
first seeks it as an output column name of the SELECT (for SQL92
compatibility). However, ruleutils.c is expecting the SQL99
interpretation where such a name is an input column name. So it's
possible to produce an incorrect display of a view in the (admittedly
pretty ill-advised) case where some other column is renamed in the
SELECT output list to match an ORDER BY column.
This can be fixed by table-qualifying such names in the dumped
view text. To avoid cluttering less-ill-advised queries, we'd
like to do so only when there's an actual name conflict.
That requires passing the current get_query_def call's resultDesc
parameter down to get_variable, so that it can determine what
the output column names are. In hopes of reducing rather than
increasing notational clutter in ruleutils.c, I moved that value
into the deparse_context struct and removed it from the parameter
lists of get_query_def's other subroutines.
I made a few other cosmetic changes while at it:
* Likewise move the colNamesVisible parameter into deparse_context.
* Rename deparse_context's windowTList field to targetList,
since it's no longer used only in connection with WINDOW clauses.
* Replace the special_exprkind field with a bool inGroupBy,
since that was all it was being used for, and the apparent
flexibility of storing a ParseExprKind proved to be illusory.
(We need a separate varInOrderBy field to make this patch work.)
* Remove useless save/restore logic in get_select_query_def.
In principle, this bug is quite old. However, it seems unreachable
before 1b4d280ea, because before that the presence of "new" and "old"
entries in a view's rangetable caused us to always table-qualify every
Var reference in dumped views. Hence, back-patch to v16 where that
came in.
Per bug #18589 from Quynh Tran.
Discussion: https://postgr.es/m/18589-70091cb81db1a3f1@postgresql.org
Branch
------
REL_17_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/a7eb633563c6ba8857cf59c7b43b593614537617
Modified Files
--------------
src/backend/utils/adt/ruleutils.c | 250 ++++++++++++++++++------------
src/test/regress/expected/create_view.out | 51 +++++-
src/test/regress/sql/create_view.sql | 16 ++
3 files changed, 217 insertions(+), 100 deletions(-)
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2024-08-29 23:50:02 | pgsql: Rename pg_sequence_read_tuple() to pg_get_sequence_data() |
Previous Message | Masahiko Sawada | 2024-08-29 16:04:48 | Re: pgsql: Restrict accesses to non-system views and foreign tables during |