[PATCH] ruleutils: Fix subqueries with shadowed aliases

Philip Dubé <philip.dub@microsoft.com>

From: Philip Dubé <Philip.Dub@microsoft.com>
To: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Date: 2019-06-03T21:42:50Z
Lists: pgsql-hackers

Attachments

Discovered while looking into issue here: https://github.com/citusdata/citus/pull/2733

For completeness I'll quote the example code to demonstrate the issue:

postgres=# create table events_table (id integer primary key, user_id integer); CREATE TABLE postgres=# create table users_table_ref (id integer primary key, value_2 integer); CREATE TABLE postgres=# create view asdf as SELECT r FROM
  (SELECT  user_id_deep, random() as r -- prevent pulling up the subquery
   FROM (events_table
         INNER JOIN
         users_table_ref ON (events_table.user_id = users_table_ref.value_2)) AS join_alias(user_id_dee
p)) AS bar,
     (events_table
      INNER JOIN
     users_table_ref ON (events_table.user_id = users_table_ref.value_2)) AS join_alias(user_id_deep) WHERE (bar.user_id_deep = join_alias.user_id_deep); CREATE VIEW postgres=# \d+ asdf
                                 View "public.asdf"
 Column |       Type       | Collation | Nullable | Default | Storage | Description 
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
--------+------------------+-----------+----------+---------+---------+-
 r      | double precision |           |          |         | plain   | View definition:
 SELECT bar.r
   FROM ( SELECT join_alias_1.user_id_deep,
            random() AS r
           FROM (events_table events_table_1
             JOIN users_table_ref users_table_ref_1 ON events_table_1.user_id = users_table_ref_1.value_2) join_alias(user_id_deep, user_id, id, value_2)) bar,
    (events_table
     JOIN users_table_ref ON events_table.user_id = users_table_ref.value_2) join_alias(user_id_deep, user_id, id, value_2)
  WHERE bar.user_id_deep = join_alias.user_id_deep;

Where the 2nd join_alias should be renamed to join_alias_1

Commits

  1. Fix incorrect printing of queries with duplicated join names.