Strange behavior
Hi,
Im 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 doesnt 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
--
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
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.
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.
"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