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
0001-ruleutils-Fix-subqueries-with-shadowed-aliases.patch
Description: 0001-ruleutils-Fix-subqueries-with-shadowed-aliases.patch
