uname -a
Linux XXXX 2.4.21-27.ELsmp #1 SMP Wed Dec 1 21:59:02 EST 2004 i686 i686
i386 GNU/Linux

db2level
DB21085I  Instance "vhsinst" uses "32" bits and DB2 code release
"SQL08022"
with level identifier "03030106".
Informational tokens are "DB2 v8.1.0.89", "OD_14086", "MI00105_14086",
and
FixPak "9".
Product is installed at "/opt/IBM/db2/V8.1".

Greetings, I got a query that gives different results dependent of the
number of columns in the select part. I will report this to IBM, just
curious if anyone has a good explanation how this can happen

select a.* from nya.APPLICATION a
INNER JOIN nya.PERSON p
       on a.person_id = p.person_id
INNER JOIN nya.INTERIM_PERSON ip
       on a.person_id = ip.person_id
LEFT OUTER JOIN nya.PERSON_ADDRESS pa
       on pa.person_id = a.person_id
       and addresstype_id = '2'
WHERE a.admissionround_id = 'VT2006'
 AND p.deceased = 0
[...]
 745 record(s) selected.

but if I add pa.addresstype_id in the select clause, I only get one row

select a.*, pa.addresstype_id from nya.APPLICATION a
INNER JOIN nya.PERSON p
       on a.person_id = p.person_id
INNER JOIN nya.INTERIM_PERSON ip
       on a.person_id = ip.person_id
LEFT OUTER JOIN nya.PERSON_ADDRESS pa
       on pa.person_id = a.person_id
       and addresstype_id = '2'
WHERE a.admissionround_id = 'VT2006'
 AND p.deceased = 0
[...]
 1 record(s) selected.

Furthermore, if I move the deceased predicate to the join condition I
get the 745 rows again.

select a.*, pa.addresstype_id from nya.APPLICATION a
INNER JOIN nya.PERSON p
       on a.person_id = p.person_id
      AND p.deceased = 0
INNER JOIN nya.INTERIM_PERSON ip
       on a.person_id = ip.person_id
LEFT OUTER JOIN nya.PERSON_ADDRESS pa
       on pa.person_id = a.person_id
       and addresstype_id = '2'
WHERE a.admissionround_id = 'VT2006'
[...]
 745 record(s) selected.

Any ideas anyone? This is puzzling me, at first I thought that there
might be a corrupt index or something, but all look fine, and there is
no info in the db2diag.log

Kind regards
/Lennart