pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Daniel Lopez <ridruejo@atm9.com.dtu.dk>
To: pgsql-sql@hub.org
Subject: weird exists behaviour
Date: Thu, 23 Sep 1999 01:33:52 +0000 ()
Message-ID: <199909230133.DAA16345@atm9.com.dtu.dk> (raw)


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!)



view thread (3+ messages)  latest in thread

Message-ID: <199909230133.DAA16345@atm9.com.dtu.dk>
Permalink:  ../199909230133.DAA16345@atm9.com.dtu.dk/
Also on:    postgresql.org/message-id/199909230133.DAA16345@atm9.com.dtu.dk

 · 

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: ridruejo@atm9.com.dtu.dk, pgsql-sql@hub.org
  Subject: Re: weird exists behaviour
  In-Reply-To: <199909230133.DAA16345@atm9.com.dtu.dk>
  References: <199909230133.DAA16345@atm9.com.dtu.dk>

* 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