query bailing out after one row returned?

[lennart]

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

[Serge Rielau]

le*****@kommunicera.umea.se wrote:

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

Please open a PMR.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab