Re: Problem with boolean WHERE-clause

On Thu, 2 Jan 2003, Christian Loth wrote:

> But somehow my statement doesn't seem to work
> (foreign keys ommitted for simplicity):
>
> crm=# create table a ( id int );
> CREATE
> crm=# create table b ( id int );
> CREATE
> crm=# create table c ( id int );
> CREATE
> crm=# create table ab ( id1 int, id2 int );
> CREATE
> crm=# create table ac ( id1 int, id2 int );
> CREATE
> crm=# insert into a values(1);
> INSERT 64600 1
> crm=# insert into b values(1);
> INSERT 64601 1
> crm=# -- no value in c
> crm=# insert into ab values(1,1);
> INSERT 64602 1
> crm=# select * from a where (a.id = ab.id1 and ab.id2 = 1);
> NOTICE:  Adding missing FROM-clause entry for table "ab"
>  id
> ----
>   1
> (1 row)
> crm=# -- this works fine
> crm=# select * from a where ((a.id = ab.id1 and ab.id2 = 1) or (a.id = ac.id1 and ac.id2 = 1));
> NOTICE:  Adding missing FROM-clause entry for table "ab"
> NOTICE:  Adding missing FROM-clause entry for table "ac"
>  id
> ----
> (0 rows)
> crm=# -- why doesn't this work? the first part is true, and the second
> crm=# -- part is connected with OR?

Read the notices, it's adding "ab" and "ac" to the from clause,
so it is as if you had done
select * from a, ab, ac where ...

If there are no rows in ac, the join returns no rows so there's no rows
for the where clause to act upon.  Technically the query is invalid,
but postgres is trying to guess what you wanted (which I think was
closer to:
select * from a where exists (select * from ab where a.id=ab.id1 and
 ab.id2=1) or exists (select * from ac where a.id=ac.id1 and ac.id2=1);
)