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