pgsql: Fix mis-deparsing of ORDER BY lists when there is a name conflic - Mailing list pgsql-committers

From Tom Lane
Subject pgsql: Fix mis-deparsing of ORDER BY lists when there is a name conflic
Date
Msg-id E1sjisl-0020NJ-V5@gemulon.postgresql.org
Whole thread Raw
List 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_16_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/9fe6319dcc1060c9b65d13f61440bfcfc772803a

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, 218 insertions(+), 99 deletions(-)


pgsql-committers by date:

Previous
From: Peter Eisentraut
Date:
Subject: pgsql: Message style improvements
Next
From: Michael Paquier
Date:
Subject: pgsql: Rename pg_sequence_read_tuple() to pg_get_sequence_data()