pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16834: where-in: if the nested query fails, the parent query returns all the records
5+ messages / 4 participants
[nested] [flat]

* BUG #16834: where-in: if the nested query fails, the parent query returns all the records
@ 2021-01-22 11:56  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 2 replies; 5+ messages in thread

From: PG Bug reporting form @ 2021-01-22 11:56 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: flavio.ricci82@gmail.com

The following bug has been logged on the website:

Bug reference:      16834
Logged by:          Flavio Ricci
Email address:      flavio.ricci82@gmail.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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records
@ 2021-01-22 14:38  Pantelis Theodosiou <ypercube@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 1 reply; 5+ messages in thread

From: Pantelis Theodosiou @ 2021-01-22 14:38 UTC (permalink / raw)
  To: flavio.ricci82@gmail.com; pgsql-bugs@lists.postgresql.org

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

> The following bug has been logged on the website:
>
> Bug reference:      16834
> Logged by:          Flavio Ricci
> Email address:      flavio.ricci82@gmail.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')

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records
@ 2021-01-22 14:48  Pantelis Theodosiou <ypercube@gmail.com>
  parent: Pantelis Theodosiou <ypercube@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Pantelis Theodosiou @ 2021-01-22 14:48 UTC (permalink / raw)
  To: flavio.ricci82@gmail.com; pgsql-bugs@lists.postgresql.org

On Fri, Jan 22, 2021 at 2:38 PM Pantelis Theodosiou <ypercube@gmail.com>
wrote:

>
>
> On Fri, Jan 22, 2021 at 12:57 PM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      16834
>> Logged by:          Flavio Ricci
>> Email address:      flavio.ricci82@gmail.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.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records
@ 2021-01-22 15:19  Flavio Ricci <flavio.ricci82@gmail.com>
  parent: Pantelis Theodosiou <ypercube@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Flavio Ricci @ 2021-01-22 15:19 UTC (permalink / raw)
  To: Pantelis Theodosiou <ypercube@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org

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@gmail.com> ha scritto:

>
>
> On Fri, Jan 22, 2021 at 2:38 PM Pantelis Theodosiou <ypercube@gmail.com>
> wrote:
>
>>
>>
>> On Fri, Jan 22, 2021 at 12:57 PM PG Bug reporting form <
>> noreply@postgresql.org> wrote:
>>
>>> The following bug has been logged on the website:
>>>
>>> Bug reference:      16834
>>> Logged by:          Flavio Ricci
>>> Email address:      flavio.ricci82@gmail.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.
>

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records
@ 2021-01-22 15:27  David G. Johnston <david.g.johnston@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 0 replies; 5+ messages in thread

From: David G. Johnston @ 2021-01-22 15:27 UTC (permalink / raw)
  To: flavio.ricci82@gmail.com <flavio.ricci82@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Friday, January 22, 2021, PG Bug reporting form <noreply@postgresql.org>
wrote:
>
>
>
> 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
>


https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_u...

David J.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2021-01-22 15:27 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-22 11:56 BUG #16834: where-in: if the nested query fails, the parent query returns all the records PG Bug reporting form <noreply@postgresql.org>
2021-01-22 14:38 ` Pantelis Theodosiou <ypercube@gmail.com>
2021-01-22 14:48   ` Pantelis Theodosiou <ypercube@gmail.com>
2021-01-22 15:19     ` Flavio Ricci <flavio.ricci82@gmail.com>
2021-01-22 15:27 ` David G. Johnston <david.g.johnston@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox