Unexpected behaviour, definitely looks like a bug.

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Unexpected behaviour, definitely looks like a bug.

Leshchuk Aleksey
PostgresQL version 9.4.20

WITH test(id, not_id) AS (
SELECT * FROM (VALUES (1,1), (3,4)) AS t
),

test2(id1) AS (
SELECT * FROM (VALUES (2), (3) ) AS t
)
-- SELECT not_id FROM test2 -- ERROR column "not_id" does not exist

SELECT * FROM test WHERE id IN ( SELECT not_id FROM test2 ); -- <-- not_id selected from test table no ERROR, result is: 1,1


Aleksey Leshchuk
Ruby on Rails Chief Software Architect
  - o. +79262462693m. +79262462693
Skype: leshchuk

Aurea

Reply | Threaded
Open this post in threaded view
|

Re: Unexpected behaviour, definitely looks like a bug.

Nikolay Samokhvalov
Why do think there is a bug here?

On Tue, Apr 9, 2019 at 15:02 Leshchuk Aleksey <[hidden email]> wrote:
PostgresQL version 9.4.20

WITH test(id, not_id) AS (
SELECT * FROM (VALUES (1,1), (3,4)) AS t
),

test2(id1) AS (
SELECT * FROM (VALUES (2), (3) ) AS t
)
-- SELECT not_id FROM test2 -- ERROR column "not_id" does not exist

SELECT * FROM test WHERE id IN ( SELECT not_id FROM test2 ); -- <-- not_id selected from test table no ERROR, result is: 1,1


Aleksey Leshchuk
Ruby on Rails Chief Software Architect
  - o. +79262462693m. +79262462693
Skype: leshchuk

Aurea

Reply | Threaded
Open this post in threaded view
|

Re: Unexpected behaviour, definitely looks like a bug.

David G Johnston
In reply to this post by Leshchuk Aleksey
On Tue, Apr 9, 2019 at 3:02 PM Leshchuk Aleksey <[hidden email]> wrote:
PostgresQL version 9.4.20

WITH test(id, not_id) AS (
SELECT * FROM (VALUES (1,1), (3,4)) AS t
),

test2(id1) AS (
SELECT * FROM (VALUES (2), (3) ) AS t
)
-- SELECT not_id FROM test2 -- ERROR column "not_id" does not exist
SELECT * FROM test WHERE id IN ( SELECT not_id FROM test2 ); -- <-- not_id selected from test table no ERROR, result is: 1,1
You've introduced a correlated subquery due to insufficient use of table qualifiers on column names.


David J.