How Can Inclusion/Exclusion of Columns Affect Number Rows Returned?

[esmith2112]

I have a situation that I can't explain. Boiled down to its essence, I
have a query of the form

SELECT A.COL1, A.COL2, B.COL1
FROM A
LEFT JOIN B ON A.KEY = B.KEY

This query produces 5383 rows of output. Because B.COL1 was defined as
VARCHAR(2000), it was making my ouput file too hard to browse so I
eliminated it from the select clause.

SELECT A.COL1, A.COL2
FROM A
LEFT JOIN B ON A.KEY = B.KEY

Now I get only 4459 rows returned. I am confused. I thought the joins
(and any subsequent filtering) would determine the number of rows.

What am I missing here?

Platform is AIX 4.3 + DB2 7.2 FP10.

Thanks,
Evan

[Serge Rielau]

esmith2112 wrote:

I have a situation that I can't explain. Boiled down to its essence, I
have a query of the form

SELECT A.COL1, A.COL2, B.COL1
FROM A
LEFT JOIN B ON A.KEY = B.KEY

This query produces 5383 rows of output. Because B.COL1 was defined as
VARCHAR(2000), it was making my ouput file too hard to browse so I
eliminated it from the select clause.

SELECT A.COL1, A.COL2
FROM A
LEFT JOIN B ON A.KEY = B.KEY

Now I get only 4459 rows returned. I am confused. I thought the joins
(and any subsequent filtering) would determine the number of rows.

What am I missing here?

Platform is AIX 4.3 + DB2 7.2 FP10.

DB2 V7? Ehem, and and outdated fixpack at that...
Anyway, the only explanation I have which does not involve a bug could
be that you have an RI relationship between the two keys and it has been
corrupted because SET INTEGRITY as run using the UNCHECKED option.
If you have such an RI, perform SET INTEGRITY to verify your data is the
way DB2 thinks it is.
I'm basing my assumption on the fact that DB2 has dropped the join
assuming it is key preserving. And that.. was wrong. There WERE
duplicate B.KEYs

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[esmith2112]

Serge Rielau wrote:

Platform is AIX 4.3 + DB2 7.2 FP10.

DB2 V7? Ehem, and and outdated fixpack at that...
Anyway, the only explanation I have which does not involve a bug could
be that you have an RI relationship between the two keys and it has been
corrupted because SET INTEGRITY as run using the UNCHECKED option.
If you have such an RI, perform SET INTEGRITY to verify your data is the
way DB2 thinks it is.
I'm basing my assumption on the fact that DB2 has dropped the join
assuming it is key preserving. And that.. was wrong. There WERE
duplicate B.KEYs

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

V7, yes. The road to upgrade has been difficult with ties to legacy
applications that have proven to be upgrade-resistent. With that
said...

The relationship between A to B is one-to-many. There is a FK
relationship in place.

I tried experiementing with SET INTEGRITY. I also dropped all
constraints at one point. Nothing altered the crazy results. I used the
explain facility to generate the plans. The only difference between the
two is that the query that fetches the extra column shows the FETCH in
addition to an IXSCAN. The other has just the IXSCAN.

Being out of support for so long has made us largely self-reliant on
problem solving but this one has our heads spinning. I will continue to
test. Is it possible that higher level FixPak will solve the problem?

Thanks,
Evan

[xiva]

Hi,Evan,

Why not take a look the different records between these 2 result set?
I'm interested about the issue, if the data are not sensitive, can you send me the ixf format of these 2 tables to my e-mail box? I will import them into my UDB 8.2 PE environment and have a test, and then send the result back to you. :) My mail box is: [e-mail].

Thanks,
xiva

[Serge Rielau]

esmith2112 wrote:

Serge Rielau wrote:

Platform is AIX 4.3 + DB2 7.2 FP10.

DB2 V7? Ehem, and and outdated fixpack at that...
Anyway, the only explanation I have which does not involve a bug could
be that you have an RI relationship between the two keys and it has been
corrupted because SET INTEGRITY as run using the UNCHECKED option.
If you have such an RI, perform SET INTEGRITY to verify your data is the
way DB2 thinks it is.
I'm basing my assumption on the fact that DB2 has dropped the join
assuming it is key preserving. And that.. was wrong. There WERE
duplicate B.KEYs

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

V7, yes. The road to upgrade has been difficult with ties to legacy
applications that have proven to be upgrade-resistent. With that
said...

The relationship between A to B is one-to-many. There is a FK
relationship in place.

I tried experiementing with SET INTEGRITY. I also dropped all
constraints at one point. Nothing altered the crazy results. I used the
explain facility to generate the plans. The only difference between the
two is that the query that fetches the extra column shows the FETCH in
addition to an IXSCAN. The other has just the IXSCAN.

Being out of support for so long has made us largely self-reliant on
problem solving but this one has our heads spinning. I will continue to
test. Is it possible that higher level FixPak will solve the problem?

Yes, but if it does it should be in the APAR list. If things went as
they should, it should have been flagged as HIPER High Pervasive.
So peruse that list.

Take a look at the explains for the two queries to validate whether the
join partner was dropped.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/