X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id ECCB83B47D6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Dec 2004 15:46:14 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 74817-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 15 Dec 2004 15:46:00 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 87B0E3B31FA
	for <pgsql-sql@postgresql.org>; Wed, 15 Dec 2004 15:46:03 +0000 (GMT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id BF44735AEF; Wed, 15 Dec 2004 07:46:01 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id BDBBE35AEE; Wed, 15 Dec 2004 07:46:01 -0800 (PST)
Date: Wed, 15 Dec 2004 07:46:01 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Marian POPESCU <softexpert@libertysurf.fr>
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue?
In-Reply-To: <41C053D1.5060508@libertysurf.fr>
Message-ID: <20041215073608.J44037@megazone.bigpanda.com>
References: <41C053D1.5060508@libertysurf.fr>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200412/93
X-Sequence-Number: 19713


On Wed, 15 Dec 2004, Marian POPESCU wrote:

> Hi,
>
> I have a problem with this join query:
>
> <sql>
> SELECT
> 	CASE WHEN (UR.id_rights IS NULL) THEN '0' ELSE UR.id_rights END as
> id_rights,
> 	CASE WHEN (UR.r_category IS NULL) THEN 'CMP' ELSE UR.r_category END as
> category,
> 	U.id as id_user,
> 	U.username
> FROM mgw_users U LEFT OUTER JOIN mgw_cnt_users_rights UR ON (U.id =
> UR.r_id_user)
> WHERE (U."level" = 9)
> AND (
>    ((UR.r_id_object = 5) OR (UR.r_id_object IS NULL))
>     AND
>    ((UR.r_category = 'CMP') OR (UR.r_category IS NULL))
> )
> ORDER BY U.username;
> </sql>
>
> I get this result and I expect something else:
> <result>
> 0;"CMP";1;"admin"
> 0;"CMP";4;"user2"
> </result>

Which appears to me to be correct for the above on the data you gave.
The outer join results in a set like:
 id | r_id_object | r_category
----+-------------+------------
  1 |             |
  2 |           8 | CMP
  2 |           7 | CMP
  2 |           8 | CNT
  3 |           8 | CMP
  4 |             |
Which then is filtered by the where clause.  All the id=2 and id=3 rows
fail the filter. Outer joins do not provide a NULL extended row if the
join condition succeeds on some rows.

> I would like to obtain
> <result>
> 0;"CMP";1;"admin"
> 0;"CMP";2;"user0"
> 0;"CMP";3;"user1"
> 0;"CMP";4;"user2"
> </result>

I'm not sure exactly what you want actually.  The case when on r_category
seems redundant since you're asking for only rows that have 'CMP' or NULL
and are making the latter into the former.

In general, I think you need to consider moving some of your conditions on
UR into the ON clause like ON (U.id = UR.r_id_user and ur.r_id_object=5
...) in which case rows in UR that fail the extra conditions don't prevent
a NULL extending row from being produced.