pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedweird exists behaviour
3+ messages / 3 participants
[nested] [flat]
* weird exists behaviour
@ 1999-09-23 01:33 Daniel Lopez <ridruejo@atm9.com.dtu.dk>
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 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Lopez <ridruejo@atm9.com.dtu.dk>
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 15:40 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.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