Results 1 to 6 of 6

Thread: UNION issue

  1. #1
    Join Date
    Mar 2005
    Posts
    7

    Unanswered: UNION issue

    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".
    Last edited by ihendr; 01-17-06 at 11:53.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What happens if you remove DISTINCT and keep UNION?

  3. #3
    Join Date
    Mar 2005
    Posts
    7
    omitting DISTINCT and keeping UNION makes no difference in the results

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ihendr
    omitting DISTINCT and keeping UNION makes no difference in the results

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

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2005
    Posts
    7

    the cause

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •