We encountered the following on our testserver (a w2k box running db2 8.2).
A table T1 has 54 columns and contains about 500 thousand rows.
For analyzing the content, we created the following statement:
select distinct 'column1' as C1, char(T1.column1) as C2 from T1 UNION
select distinct 'column2' as C1, char(T1.column2) as C2 from T1 UNION
select distinct 'column53' as C1, char(T1.column53) as C2 from T1 UNION
select distinct 'column54' as C1, char(T1.column54) as C2 from T1
This statement is to produce a list of the distinct values for each column, including null-values, looking something like this:
Each separate union clause executes sucessful and returns at least one row for each column in T1.
The entire statement processes without any error, but when reviewing the result not all clauses seem to have been processed (eg the 'column53' result is not included but executed seperately it returns 3 rows).
If we change the order of the clauses, the results vary but always there are entire 'columnXX' missing.
However If we use UNION ALL, the statement returns the full result we expected.
Considering the nature of the query and the uniqueness of the C1 values for each clause, using UNION seemed the logical choice above UNION ALL.
And even when using union, we should have expected at least one 'column53' row in the results.
So, what could cause this statement to execute without any error, but still return an incomplete resultset?
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v220.127.116.115", "s040812", "WR21342", and FixPak"7".
The cause was traced back to an odbc connection setting (set by the application) wich was translated by the driver to a FETCH FIRST n ROWS clause that was added to the sql statement.
This shouldn't be much of an issue if it were not that in case of using UNION, the fetch first did not apply to the final resultset, but to the entire internal processing ending up with gaps in the final resultset.
Whether this is working as designed or not is yet to be figured out...
you can experience this weird behaviour by executing folowing statement:
fetch first 4 rows only