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

On Fri, Jan 22, 2021 at 2:38 PM Pantelis Theodosiou <ypercube(at)gmail(dot)com>
wrote:

>
>
> 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')
>

To be accurate the rows of table a that are returned are the ones wit
non-null values in field_only_in_parent_table and only if table b has at
least one row.
If table b has 0 rows, then the query returns no rows.