Thread: BUG #15128: Erroneous inner query is executing with wrong results
The following bug has been logged on the website: Bug reference: 15128 Logged by: Yossi Eilaty Email address: yossi.eilaty@gong.io PostgreSQL version: 9.6.2 Operating system: x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2 Description: Consider the following tables: CREATE TABLE a ( r BIGINT, y BIGINT ); CREATE TABLE b ( x BIGINT, y BIGINT ); with the following data: INSERT INTO a (r, y) VALUES (1, 1); INSERT INTO a (r, y) VALUES (2, 2); INSERT INTO b (x, y) VALUES (1, 1); INSERT INTO b (x, y) VALUES (2, 2); If I run the following query: SELECT y FROM b WHERE x IN (SELECT x FROM (SELECT r FROM a WHERE y = 1) the_x); I get, as a result, the y value of all rows of table b, which is wrong since the inner query SELECT x FROM (SELECT r FROM a WHERE y = 1) the_x is wrong and if run solely returns the error "[42703] ERROR: column "x" does not exist", which is correct. The same thing happens for the following query: SELECT y FROM b WHERE x IN (SELECT x FROM (SELECT x FROM a WHERE y = 1) the_x); where, again, the innermost query is wrong (there's no x in a).
Re: BUG #15128: Erroneous inner query is executing with wrong results
From
"David G. Johnston"
Date:
On Thursday, March 22, 2018, PG Bug reporting form <noreply@postgresql.org> wrote:
where, again, the innermost query is wrong (there's no x in a).
This is a correlated subquery working as designed.
David J.