| From: | <Eugen(dot)Konkov(at)aldec(dot)com> | 
|---|---|
| To: | <pgsql-bugs(at)postgresql(dot)org> | 
| Subject: | The problem with FULL JOIN | 
| Date: | 2008-03-30 17:13:23 | 
| Message-ID: | 048f01c89289$5d464400$1200a8c0@kharkov.localhost | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-bugs | 
PROBLEM:
How to FULL JOIN groups=1 from table 'a' with groups=2 from table 'b' 
and exclude original NULL groups not thouse which FULL JOIN produce?
DESCRIPTION:
I have a schema which is attached at file '123':
while FULL JOIN ing I get:
postgres=# SELECT * FROM a FULL JOIN b ON a.num1 = b.num1;
 num1 | num2 | groups | num1 | num2 | groups
------+------+--------+------+------+--------
    1 |    1 |      1 |    1 |    1 |      1
    1 |    1 |      1 |    1 |    1 |      2
    1 |    1 |      2 |    1 |    1 |      1
    1 |    1 |      2 |    1 |    1 |      2
    2 |    2 |      1 |      |      |
    2 |    2 |      2 |      |      |
      |      |        |    3 |    3 |      1
      |      |        |    3 |    3 |      2
(8 rows)
All is ok here, 
BUT when I want to full join groups 1 from table a with groups 2 from table 2 I have get a PROBLEM
SELECT *
FROM a 
FULL OUTER JOIN b ON a.num1 = b.num1 
where (a.groups =1 or a.groups is NULL) and (b.groups=2 or b.groups is NULL)
 num1 | num2 | groups | num1 | num2 | groups
------+------+--------+------+------+--------
    1 |    1 |      1 |    1 |    1 |      2
    2 |    2 |      1 |      |      |
      |      |        |    3 |    3 |      2
(3 rows)
If table column 'groups' of table a and/or b has no NULL. I get what I want,
BUT when they have, expected result are differ from actual
INSERT INTO a values( 999,999, null);
INSERT INTO b value (999,999,null);
SELECT *
FROM a 
FULL OUTER JOIN b ON a.num1 = b.num1 
where (a.groups =1 or a.groups is NULL) and (b.groups=2 or b.groups is NULL)
 num1 | num2 | groups | num1 | num2 | groups
------+------+--------+------+------+--------
    1 |    1 |      1 |    1 |    1 |      2
    2 |    2 |      1 |      |      |
      |      |        |    3 |    3 |      2
  999 |  999 |        |  999 |  999 |
(4 rows)
Here I do not expect last row. 
It seem that it is IMPOSSIBLE to filter out rows with groups which have NULL values
When I write:
SELECT * FROM a  FULL OUTER JOIN b ON a.num1 = b.num1 
 where (a.groups =1) and (b.groups=2)
 num1 | num2 | groups | num1 | num2 | groups
------+------+--------+------+------+--------
    1 |    1 |      1 |    1 |    1 |      2
(1 row)
I lose rows which FULL JOIN must produce ((
http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html
>FULL OUTER JOIN
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added. 
How to FULL JOIN groups=1 from table 'a' with groups=2 from table 'b' 
and exclude original NULL groups not thouse which FULL JOIN produce?
| Attachment | Content-Type | Size | 
|---|---|---|
| 123 | application/octet-stream | 1.2 KB | 
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Gregory Stark | 2008-03-30 17:40:53 | Re: BUG #4069: Wrong tip | 
| Previous Message | Dave Page | 2008-03-30 15:02:56 | Re: BUG #4069: Wrong tip |