home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IT04476 Status: Geschlossen

"LIMIT # OFFSET #" MAY RETURN MORE ROWS THAN EXPECTED WHEN
QUERIES CONTAIN UNION OR UNION ALL OPERATORS.

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
Queries containing UNION or UNION ALL operators with "LIMIT # 
OFFSET #" clause does not behave the same way as FETCH FIRST N 
ROWS ONLY. As such, under these conditions, it may return more 
rows than expected. 
 
Consider a query using the "FETCH FIRST 1 ROW ONLY" clause : 
> select 1 C1 from sysibm.sysdummy1 
  union all 
   select 2 C1 from sysibm.sysdummy1 
   ORDER BY C1 ASC 
   FETCH FIRST 1 ROWS ONLY 
 
Which returns: 
C1 
 
          1 
 
  1 record(s) selected. 
 
 
Using the same data with a "LIMIT 1 OFFSET 0" clause to mimic 
the "FETCH FIRST 1 ROWS ONLY" 
> select 1 C1 from sysibm.sysdummy1 
   union all 
   select 2 C1 from sysibm.sysdummy1 
   ORDER BY C1 ASC 
   LIMIT 1 OFFSET 0 
 
We get more rows than expected if this is used in conjunction 
with UNION or UNION ALL: 
 
C1 
 
          1 
          2 
 
  2 record(s) selected. 
 
> select 1 C1 from sysibm.sysdummy1 
   union all 
   select 2 C1 from sysibm.sysdummy1 
   ORDER BY C1 ASC 
   LIMIT 1 OFFSET 1 
 
C1 
 
          1 
          2 
 
  2 record(s) selected. 
 
 
The problem can be circumvented as follows : 
 
> select * from 
     ( select 1 C1 from sysibm.sysdummy1 
       union all 
       select 2 C1 from sysibm.sysdummy1 
       ORDER BY C1 ASC ) 
    LIMIT 1 OFFSET 0 
 
C1 
 
          1 
 
  1 record(s) selected.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 9.7 Fix Pack 11                           * 
****************************************************************
Local-Fix:
Lösung
first fixed in version 9.7 Fix Pack 11
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
18.09.2014
08.10.2015
08.10.2015
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP11
Problem behoben lt. FixList in der Version
9.7.0.11 FixList