Обсуждение: weird exists behaviour

Can someone explain this behaviour (bug? feature?) of EXISTS. 
(also, is EXISTS documented anywhere in the postgres
documentation?)



db=> create table t1 (f1 int4);
CREATE
db=> create table t2 (f2 int4);
CREATE
db=> insert into t1 values (1)
db-> ;
INSERT 6008882 1
db=> insert into t1 values (2);
INSERT 6008883 1
db=> insert into t2 values (1);
INSERT 6008884 1

db=> select * from t1;
f1
--12(2 rows) 
db=> select * from t2;
f2
--1
(1 row)


db=> select * from t1 where f1 not in (select * from t2);
f1
--
2
(1 row)
So  far  so good 


db=> select f1 from t1 where not exists (select * from t2 where t1.f1=t2.f2);
f1
--
2
(1 row)
So  far  so good 

db=> select * from t1 where not exists (select * from t1 where
t1.f1=t2.f2);
f1
--
(0 rows)

(why???? )


db=> select * from t1 where not exists (select * from t1 as t4  where
t1.f1=t2.f2);
f1
--
2
(1 row) 
(and surprisingly this works!)

Daniel Lopez <ridruejo@atm9.com.dtu.dk> writes:
> Can someone explain this behaviour (bug? feature?) of EXISTS. 

I think it's probably OK, as long as you remember that calling out
table names not present in the FROM clause implicitly creates another
FROM entry.

> db=> select * from t1 where not exists (select * from t1 where
> t1.f1=t2.f2);
> f1
> --
> (0 rows)
> (why???? )

Read it as

select * from t1 where not exists (select * from t1, t2 where t1.f1=t2.f2);

The inner select will produce the same result (namely a single row "1,1")
regardless of where the outer select is, because the inner select
doesn't depend on the outer at all.  So the EXISTS succeeds for every
row of the outer select, and you get no rows out.

> db=> select * from t1 where not exists (select * from t1 as t4  where
> t1.f1=t2.f2);
> f1
> --
> 2
> (1 row)
> (and surprisingly this works!)

Read it as

select * from t1 where not exists (select * from t1 as t4, t2  where               t1.f1=t2.f2);

Here, the t1.f1 in the inner WHERE represents the value from the
current row of the outer select (it doesn't mean the current row of
the inner select's t1 because you renamed that to t4 --- so t1 is
not known as a table name of the inner select).  Your inner select
is uselessly generating a join between t2 and the renamed t1, so
you get either 0 or 2 rows out of it --- but EXISTS doesn't care
about that.


Note to hackers: here is another example of people getting confused
by automatic addition of FROM clauses.  The same query can behave
differently depending on whether it is a sub-query or not: a free
table name might get bound to a table of the outer query, or generate
an implicit FROM clause in the standalone case, yielding very different
result sets.
        regards, tom lane

> Note to hackers: here is another example of people getting confused
> by automatic addition of FROM clauses.  The same query can behave
> differently depending on whether it is a sub-query or not: a free
> table name might get bound to a table of the outer query, or generate
> an implicit FROM clause in the standalone case, yielding very different
> result sets.

I think we need to issue an elog(NOTICE) when we create a table entry on
the fly.


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026