pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Lopez <ridruejo@atm9.com.dtu.dk>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] weird exists behaviour
Date: Thu, 23 Sep 1999 11:16:07 -0400
Message-ID: <14389.938099767@sss.pgh.pa.us> (raw)
In-Reply-To: <199909230133.DAA16345@atm9.com.dtu.dk>

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



view thread (3+ messages)  latest in thread

Message-ID: <14389.938099767@sss.pgh.pa.us>
Permalink:  ../14389.938099767@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/14389.938099767@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, ridruejo@atm9.com.dtu.dk, pgsql-sql@hub.org
  Subject: Re: [SQL] weird exists behaviour
  In-Reply-To: <14389.938099767@sss.pgh.pa.us>
  References: <199909230133.DAA16345@atm9.com.dtu.dk> <14389.938099767@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox