Hi there,
Sorry but I've got yet another issue to discuss today, this time that
on schema search path. In fact it may not be a bug, but it may be
worth a note in the documentation.
It seems that if the table in SELECT FROM has an explicit schema
specifier, further references to the same table name will implicitly
inherit it. E.g., this query will be valid because the second
reference will be to foo.bar not public.bar:
SELECT * FROM foo.bar WHERE bar.a=1;
^^^ this means foo.bar
Here is a more complex case where I initially came across this issue:
psql (8.4.4)
Type "help" for help.
pgsql=# show search_path;
search_path
----------------
"$user",public
(1 row)
pgsql=# create table public.tbl_bar (a int);
CREATE TABLE
pgsql=# create schema sch_foo;
CREATE SCHEMA
pgsql=# create table sch_foo.tbl_bar (a int);
CREATE TABLE
pgsql=# insert into public.tbl_bar (a) values (1);
INSERT 0 1
pgsql=# insert into sch_foo.tbl_bar (a) values (2);
INSERT 0 1
pgsql=# select a from tbl_bar where not exists (select a from
sch_foo.tbl_bar where tbl_bar.a=sch_foo.tbl_bar.a);
a
---
(0 rows)
pgsql=# select a from tbl_bar where not exists (select a from
sch_foo.tbl_bar where public.tbl_bar.a=sch_foo.tbl_bar.a);
a
---
1
(1 row)
As just shown, this can be even more confusing with nested queries.
Do you think it's a feature or a bug? :-)
Thanks!
Yar
