Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records

On Fri, Jan 22, 2021 at 12:57 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16834
> Logged by: Flavio Ricci
> Email address: flavio(dot)ricci82(at)gmail(dot)com
> PostgreSQL version: 11.9
> Operating system: x86_64-pc-linux-gnu
> Description:
>
> Hi,
>
> I have found out that in a where-in query if the nested query fails, the
> parent query returns all the records if the where condition field matches
> with the selected field in the inner query.
> Example:
> SELECT *
> FROM table_a
> WHERE field_only_in_parent_table IN (
> SELECT field_only_in_parent_table
> FROM table_b
> WHERE name = 'John Doe')
>
> If you run only the nested query it fails because the field does not exist
> for table_b
> If you run all the query, it returns all the records of table_a
>
> Regards,
>
> Flavio
>

This is not a bug. Returning all records of the parent table is the correct
result, due to scope resolution.

The column field_only_in_parent_table does not have a table prefix so it is
first checked whether it's a column of the immediate tables (table_b here).
Since the column is not there, the next level tables are checked (table_a
here). It is a there so the query is executed as:

SELECT *
FROM table_a
WHERE field_only_in_parent_table IN (
SELECT table_a.field_only_in_parent_table
FROM table_b
WHERE name = 'John Doe')