[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
- 0001-ruleutils-Fix-subqueries-with-shadowed-aliases.patch (application/octet-stream) patch 0001
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
-
Fix incorrect printing of queries with duplicated join names.
- 30d3df0a7b82 10.9 landed
- f95d8f81062a 11.4 landed
- 3d99a81397ab 12.0 landed
- bf612fd3a4d8 9.5.18 landed
- ad3e61b2809e 9.6.14 landed
- 8ace51a6b114 9.4.23 landed