Postgres Pro
Downloads
Home   >   mailing lists

Thread: Strange behavior

Strange behavior

From
Olivier Leprêtre
Date:

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

Re: Strange behavior

From
Francisco Olarte
Date:
Olivier:

On Sat, Oct 10, 2020 at 6:13 PM Olivier Leprêtre <o.lepretre@gmail.com> 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.

Re: Strange behavior

From
"David G. Johnston"
Date:
On Sat, Oct 10, 2020 at 9:13 AM Olivier Leprêtre <o.lepretre@gmail.com> 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.


https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_using_the_wrong_name_in_a_subquery.3F
I am pondering trying to get the FAQ entry incorporated into the actual documentation.

David J.

Re: Strange behavior

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> 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