Hi Dudes,

    II2.5 axp.osf
    Can you use views in outer joins? I would have assumed so.

    But I have a case where I use  a view and a left outer join to
replace a not exists style query. Its syntactically okay but produces a
wrong result.

    If I replace the view with a table generated from the view. The
left outer join works correctly.

    If I use the view with the equivelent not exists query the correct
result is generated.

Query with view is:
select count(*)
   from profile_map_node_others pmv left outer join descriptor_value
dv
       on pmv.value_id=dv.value_id
   where pmv.value_id != 0 and
   pmv.profile_map_id=1422726 and dv.value_id is null;
(Which incorrectly returns 0)

Query with not exists is:
select count(*)
   from profile_map_node_others pmv
   where pmv.value_id != 0 and
   pmv.profile_map_id=1422726 and
   not exists (
       select 1 from descriptor_value dv
       where pmv.value_id=dv.value_id
       );
(Which returns the correct answer 1)

And the table equivalent is:
create table fred as select * from profile_map_node_others;
select count(*)
   from fred pmv left outer join descriptor_value dv
       on pmv.value_id=dv.value_id
   where pmv.value_id != 0 and
   pmv.profile_map_id=1422726 and dv.value_id is null;
(Which returns the correct answer 1)

Have I stuffed something up?

Martin Bowes
--
Random Farscape Quote #3:
John - If this is part of some sick experiment, I'm going to bill them for
      the therapy.