Two join aliases with the same name fails to deparse accurately

[onderkalaci]

PostgreSQL parser already disallows the same join alias on the same level of the query such as:

SELECT *
FROM
      (events_table
          INNER JOIN 
         users_table_ref ON (events_table.user_id = users_table_ref.value_2)) AS join_alias(user_id_deep),
     (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);
ERROR:  table name "join_alias" specified more than once

In this case, we're discussing this even the aliases appear in different queries/subqueries.

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_deep)) 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);

Worker query:

...
LOG:  issuing COPY (SELECT worker_column_1 AS r FROM (SELECT bar.r AS worker_column_1 FROM (SELECT join_alias_1.user_id_deep, random() AS r FROM (public.events_table_102050 events_table_1 JOIN public.users_table_ref_102072 users_table_ref_1 ON ((events_table_1.user_id OPERATOR(pg_catalog.=) users_table_ref_1.value_2))) join_alias(user_id_deep, "time", event_type, value_2, value_3, value_4, user_id, time_1, value_1, value_2_1, value_3_1, value_4_1)) bar, (public.events_table_102050 events_table JOIN public.users_table_ref_102072 users_table_ref ON ((events_table.user_id OPERATOR(pg_catalog.=) users_table_ref.value_2))) join_alias(user_id_deep, "time", event_type, value_2, value_3, value_4, user_id, time_1, value_1, value_2_1, value_3_1, value_4_1) WHERE (bar.user_id_deep OPERATOR(pg_catalog.=) join_alias.user_id_deep)) worker_subquery) TO STDOUT
...
WARNING:  missing FROM-clause entry for table "join_alias_1"
...
ERROR:  failed to execute task 11

Note that when you create such a rule (e.g., CREATE RULE r1 AS ON INSERT table DO query) on non-distributed tables, the rule is saved correctly and works fine. So, it makes me think that the issue might be related to Citus' additional deparsing rules.

[serprex]

This script reproduces the issue. Will create a directory ~/tests/ws/joinaliasXXXX where XXXX is the port parameter. (this text is a spoiler tag)

#!/bin/bash

if [ -z "$1" ]
then exit "Supply port"
fi

WS=~/tests/ws/joinalias$1
rm -rf $WS

for i in 0 1 2
do initdb $WS/$i
sed "s/#shared_preload_libraries = ''/shared_preload_libraries = 'citus'/" -i $WS/$i/postgresql.conf
sed "s/#port = 5432/port = $(expr $1 + $i)/" -i $WS/$i/postgresql.conf
pg_ctl -D $WS/$i -l $WS/log start
psql -p $(expr $1 + $i) -d postgres -c "create extension citus;"
done

for i in 1 2
do psql -p $1 -d postgres -c "select * from master_add_node('localhost', $1 + $i);"
done

psql -p $1 -d postgres << EOF
create table events_table (id integer primary key, user_id integer);
create table users_table_ref (id integer primary key, value_2 integer);
select * from create_distributed_table('events_table', 'id');
select * from create_reference_table('users_table_ref');
EOF

psql -p $1 -d postgres << EOF
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_deep)) 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);
EOF

for i in 0 1 2
do pg_ctl -D $WS/$i stop
done

Relevant code likely steps on toes of #2435
I've tested that this issue still exists on unified_executor branch

[marcocitus]

Same or related bug:

CREATE TABLE test (id bigserial primary key, value text not null);
SELECT create_distributed_table('test','id');

SELECT * FROM (SELECT id, random() FROM (test a JOIN test b USING (id)) join_alias) bar, test join_alias WHERE bar.id = join_alias.id;
WARNING:  missing FROM-clause entry for table "join_alias_1"
...
LOG:  issuing COPY (SELECT worker_column_1 AS id, worker_column_2 AS random, worker_column_3 AS id, worker_column_4 AS value FROM (SELECT bar.id AS worker_column_1, bar.random AS worker_column_2, join_alias.id AS worker_column_3, join_alias.value AS worker_column_4 FROM (SELECT join_alias_1.id, random() AS random FROM (public.test_102892 a(id, value) JOIN public.test_102892 b(id, value) USING (id)) join_alias(id, value, value_1)) bar, public.test_102892 join_alias WHERE (bar.id OPERATOR(pg_catalog.=) join_alias.id)) worker_subquery) TO STDOUT
DETAIL:  on server localhost:9700
WARNING:  missing FROM-clause entry for table "join_alias_1"
ERROR:  failed to execute task 16

In that case, the problem seems to be in SELECT join_alias_1.id, random() AS random FROM (public.test_102892 a(id, value) JOIN public.test_102892 b(id, value) USING (id)) join_alias(id, value, value_1). Deparsing added join_alias_1 in the target list for the USING column, but the join alias itself did not change.