Skip site navigation (1) Skip section navigation (2)

Re: correlated delete with 'in' and 'left outer join'

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: mike(at)linkify(dot)com, pgsql-general(at)postgresql(dot)org
Subject: Re: correlated delete with 'in' and 'left outer join'
Date: 2004-02-27 16:02:36
Message-ID: 403F6A1C.30501@mascari.com (view raw or flat)
Thread:
Lists: pgsql-general
Stephan Szabo wrote:
> On Fri, 27 Feb 2004, Mike Mascari wrote:
> 
>>To do what I think you believe to be happening w.r.t. outer joins,
>>you'd have to have a subquery like:
>>
>>[test(at)lexus] select a.fookey
>>test-# FROM
>>test-# (SELECT foo.key AS fookey, bar.key as barkey FROM foo LEFT
>>OUTER JOIN bar ON foo.key = bar.key) AS a
>>test-# WHERE a.barkey IS NULL;
> 
> This AFAICS is pretty much what he did, except that he didn't alias the
> join which is okay I believe.  He had one condition in on and two
> conditions in where.
> 
> The original subquery looked like:
> select distinct e.ItemID from LogEvent e left outer join Item i
> on e.ItemID = i.ItemID where e.EventType != 'i' and i.ItemID is null

That is indeed the original subquery. But the 'i.ItemID is null' 
condition doesn't change the IN list one iota. He was somehow 
expecting the subquery to yield records internally like:

1	NULL
2	NULL
3	3

and simultaneously have the condition 'i.ItemID is null' eliminate 
the third tuple. But that is not how the left outer join executes. 
The 'i.ItemID is null' condition is evaluated, probably always to 
false, which ensures that the left outer join will never find a 
matching row from the 'Item' relation and, if queried not as a 
subquery but stand-alone as:

select distinct e.ItemID, i.ItemID
from LogEvent e left outer join Item i on e.ItemID = i.ItemID
where e.EventType != 'i' and i.ItemID is null

would always yield a relation of the form:

e.ItemID	NULL

for every e.ItemID whose e.EventType != 'i'. That ain't right.

Another example:

[test(at)lexus] select * from foo;
  key
-----
    1
    3
(2 rows)

[test(at)lexus] select * from bar;
  key | value
-----+-------
    1 | Mike
    2 | Joe
(2 rows)

[test(at)lexus] select foo.key, bar.key from foo left outer join bar on 
foo.key = bar.key and bar.key is null;
  key | key
-----+-----
    1 |
    3 |
(2 rows)

[test(at)lexus] select foo.key, bar.key from foo left outer join bar on 
foo.key = bar.key;
  key | key
-----+-----
    1 |   1
    3 |
(2 rows)

[test(at)lexus] select a.fookey, a.barkey from (select foo.key as 
fookey, bar.key as barkey from foo left outer join bar on foo.key = 
bar.key) as a where a.barkey is null;
  fookey | barkey
--------+--------
       3 |
(1 row)


Mike Mascari


In response to

Responses

pgsql-general by date

Next:From: Bill MoranDate: 2004-02-27 16:11:28
Subject: field must appear in the GROUP BY clause or be used in an aggregate function?
Previous:From: jwsackstederDate: 2004-02-27 16:01:54
Subject: compartmentalizing users

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group