Horton

Thread

Commits

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

  1. [PATCH] ruleutils: Fix subqueries with shadowed aliases

    Philip Dubé <philip.dub@microsoft.com> — 2019-06-03T21:42:50Z

    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

  2. Re: [PATCH] ruleutils: Fix subqueries with shadowed aliases

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-06-12T23:46:13Z

    =?iso-8859-1?Q?Philip_Dub=E9?= <Philip.Dub@microsoft.com> writes:
> 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:
> ...
> Where the 2nd join_alias should be renamed to join_alias_1

Good catch!  The proposed test case is less good though, because
it doesn't actually exercise the bug, ie the test case passes
with or without the code change.  (You also stuck it into the
middle of a bunch of not-very-related test cases.)  I adapted
your example into a better test case and pushed it.  Thanks
for the report and fix.

			regards, tom lane