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

Hi Theodosiou,,

thank you very much for your answer
It seems that I ran into a tricky situation, but your explanation helped me
definitely about what is happening behind the curtains

Thanks again

Best regards,

Flavio

Il giorno ven 22 gen 2021 alle ore 15:49 Pantelis Theodosiou <
ypercube(at)gmail(dot)com> ha scritto:

>
>
> 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.
>