Re: [SQL] weird exists behaviour

Daniel Lopez <ridruejo(at)atm9(dot)com(dot)dtu(dot)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