|
Problem IT04476
|
Status: Closed |
"LIMIT # OFFSET #" MAY RETURN MORE ROWS THAN EXPECTED WHEN QUERIES CONTAIN UNION OR UNION ALL OPERATORS. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* USERS AFFECTED: *
* all *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to version 9.7 Fix Pack 11 *
**************************************************************** |
| Local Fix: |
|
| Solution |
first fixed in version 9.7 Fix Pack 11 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.09.2014
08.10.2015
08.10.2015 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP11
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.11
|
 |
