Dangerous Naming Confusion

Good evening,

Please see my gist at
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.

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