pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #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>
2021-01-22 14:38 ` Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records Pantelis Theodosiou <ypercube@gmail.com>
2021-01-22 15:27 ` Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records David G. Johnston <david.g.johnston@gmail.com>
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 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 ` Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records Pantelis Theodosiou <ypercube@gmail.com>
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 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 ` Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records Pantelis Theodosiou <ypercube@gmail.com>
@ 2021-01-22 14:48 ` Pantelis Theodosiou <ypercube@gmail.com>
2021-01-22 15:19 ` Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records Flavio Ricci <flavio.ricci82@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 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 ` Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records Pantelis Theodosiou <ypercube@gmail.com>
2021-01-22 14:48 ` Re: BUG #16834: where-in: if the nested query fails, the parent query returns all the records Pantelis Theodosiou <ypercube@gmail.com>
@ 2021-01-22 15:19 ` Flavio Ricci <flavio.ricci82@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 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 15:27 ` David G. Johnston <david.g.johnston@gmail.com>
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