pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
weird exists behaviour
3+ messages / 3 participants
[nested] [flat]

* weird exists behaviour
@ 1999-09-23 01:33 Daniel Lopez <ridruejo@atm9.com.dtu.dk>
  1999-09-23 15:16 ` Re: [SQL] weird exists behaviour Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Daniel Lopez @ 1999-09-23 01:33 UTC (permalink / raw)
  To: pgsql-sql@hub.org


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
--
 1
 2
 (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!)



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] weird exists behaviour
  1999-09-23 01:33 weird exists behaviour Daniel Lopez <ridruejo@atm9.com.dtu.dk>
@ 1999-09-23 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-09-23 15:40   ` Re: [SQL] weird exists behaviour Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 1999-09-23 15:16 UTC (permalink / raw)
  To: Daniel Lopez <ridruejo@atm9.com.dtu.dk>; +Cc: pgsql-sql@hub.org

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] weird exists behaviour
  1999-09-23 01:33 weird exists behaviour Daniel Lopez <ridruejo@atm9.com.dtu.dk>
  1999-09-23 15:16 ` Re: [SQL] weird exists behaviour Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-09-23 15:40   ` Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Bruce Momjian @ 1999-09-23 15:40 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Daniel Lopez <ridruejo@atm9.com.dtu.dk>; pgsql-sql@hub.org

> 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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-09-23 15:40 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-23 01:33 weird exists behaviour Daniel Lopez <ridruejo@atm9.com.dtu.dk>
1999-09-23 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-23 15:40   ` Bruce Momjian <maillist@candle.pha.pa.us>

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