Re: Dangerous Naming Confusion

On 3/29/21 3:00 PM, Don Seiler wrote:
> Good evening,
>
> Please see my gist at
> https://gist.github.com/dtseiler/9ef0a5e2b1e0efc6a13d5661436d4056
> <https://gist.github.com/dtseiler/9ef0a5e2b1e0efc6a13d5661436d4056> for
> a complete test case.
>
> I tested this on PG 12.6 and 13.2 and observed the same on both.
>
> We were expecting the queries that use dts_temp to only return 3 rows.
> However the subquery starting at line 36 returns ALL 250,000 rows from
> dts_orders. Note that the "order_id" field doesn't exist in the dts_temp
> table, so I'm assuming PG is using the "order_id" field from the
> dts_orders table. If I use explicit table references like in the query
> at line 48, then I get the error I would expect that the "order_id"
> column doesn't exist in dts_temp.
>
> When I use the actual column name "a" for dts_temp, then I get the 3
> rows back as expected.
>
> I'm wondering if this is expected behavior that PG uses the
> dts_orders.order_id value in the subquery "select order_id from
> dts_temp" when dts_temp doesn't have its own order_id column. I would
> have expected an error that the column doesn't exist. Seems very
> counter-intuitive to think PG would use a column from a different table.

See:

https://www.postgresql.org/message-id/Pine.LNX.4.56.0308011345320.881@krusty.credativ.de

>
> This issue was discovered today when this logic was used in an UPDATE
> and ended up locking all rows in a 5M row table and brought many apps to
> a grinding halt. Thankfully it was caught and killed before it actually
> updated anything.
>
> Thanks,
> Don.
> --
> Don Seiler
> www.seiler.us <http://www.seiler.us>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com