469,890 Members | 2,091 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

query bailing out after one row returned?

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

Nov 30 '05 #1
1 1288
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
Nov 30 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Joe Stanton | last post: by
4 posts views Thread by Chris Tremblay | last post: by
4 posts views Thread by Jonesgj | last post: by
reply views Thread by lennart | last post: by
5 posts views Thread by Vincent | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.