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:
C1 C2
-------- ---
Column1 null
Column1 ABC
Column2 AbD
…
Column53 null
Column53 AAA
Column53 DDD
Column54 xXX

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?

Thanks,
Ihendr

----
added info:
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.7.445", "s040812", "WR21342", and FixPak"7".

What happens if you remove DISTINCT and keep UNION?

omitting DISTINCT and keeping UNION makes no difference in the results

First, you should be using UNION ALL, UNION is meant to remove duplicates and sort the results, which you don't need.

And are you saying that UNION ALL does return you everything and UNION doesn't?

That's bizzare. What's the benefit of this resultset btw? If you hade 1 column with a high cardinality, your result set would be huge...suporsed yo udidn't add count(*) as well.

It might not make a difference in the RS, but it would force DB2 to do alot more work....

thx for the replies

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:

values (1,2)
UNION
values (1,2)
UNION
values (3,2)
UNION
values (4,2)
UNION
values (5,2)
fetch first 4 rows only

should expect to return 4 rows

1 2
3 2
4 2
5 2

but only returns

1 2
3 2
4 2