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 |