Strange behavior

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Strange behavior

Olivier Leprêtre

Hi,

 

I’m surprised by this behavior I noticed in pgadmin3 and postgresql 9.6

 

Suppose those two tables

create table test1 (v1 text)

create table test2 (v2 text)

insert into test1 (v1) values ('A')

insert into test2 (v2) values ('B')

 

query select v1 from test2 return v2 column doesn’t exists. Right.

 

But the following query does not return any error but only an empty result set.

select v1 from test1 where v1 not in (select v1 from test2)

 

In other words, a wrong query returns a valid result. This happens because v1 is a column from test1, (select vx from test2) will return an error as expected.

 

Did I missed something ?

 

Thanks,

 

Olivier


Garanti sans virus. www.avast.com
Reply | Threaded
Open this post in threaded view
|

Re: Strange behavior

Francisco Olarte
Olivier:

On Sat, Oct 10, 2020 at 6:13 PM Olivier Leprêtre <[hidden email]> wrote:

> I’m surprised by this behavior I noticed in pgadmin3 and postgresql 9.6
...

> select v1 from test1 where v1 not in (select v1 from test2)


This is called a correlated subquery ( google and search for it, it is
even in wikipedia ). It has many uses.

Basically, it refers to the v1 from the outside query.

Get in the habit of using (potentially aliased ) column names whenever
you have any moderately complex query, i.e. if the inner v1 would have
been v2 ( due to a typo ), writing your query as :

select t1.v1 from test1 as t1 wher t1.v1 not in ( select t2.v1 from
test2 as t2 )

Would have caught it.

Francisco Olarte.


Reply | Threaded
Open this post in threaded view
|

Re: Strange behavior

David G Johnston
In reply to this post by Olivier Leprêtre
On Sat, Oct 10, 2020 at 9:13 AM Olivier Leprêtre <[hidden email]> wrote:

This has nothing to do with pgAdmin, or any other client interface.

In other words, a wrong query returns a valid result. This happens because v1 is a column from test1, (select vx from test2) will return an error as expected.


I am pondering trying to get the FAQ entry incorporated into the actual documentation.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Strange behavior

Tom Lane-2
"David G. Johnston" <[hidden email]> writes:
> I am pondering trying to get the FAQ entry incorporated into the actual
> documentation.

Not sure how much it'd help, but we have warnings against mistakes
that are far less common than this one, so sure why not.

Taking a quick gander at the docs, it seems like the thing to do would be
to add something to the head text in 9.23. Subquery Expressions, and then
remove any duplicative statements from the individual 9.23.x subsections.

                        regards, tom lane