DB2 - Problem description
Problem IT18100 | Status: Closed |
AN SQL STATEMENT IN A PARTITIONED DATABASE ENV CONTAINING THE ROW_NUMBER() OVER() OPERATION MIGHT PRODUCE INCONSISTENT RESULTS | |
product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problem description: | |
An sql statement in a partitioned database environment containing the ROW_NUMBER() OVER() operation might produce inconsistent results because the ROW_NUMBER() operation is processed in parallel on the individual partitions on the broadcasted result set. A example statement : select T1.BIGINT_ID, count(*) FROM ( SELECT VARCHAR_KEY, ROW_NUMBER() OVER() AS BIGINT_ID FROM ( SELECT DISTINCT VARCHAR_KEY FROM MYTABLE ) ) T1, MYTABLE T2 where T1.VARCHAR_KEY = T2.VARCHAR_KEY group by T1.BIGINT_ID having count(*) > 1 This will have an execution plan showing : 11141 BTQ ( 11) | 1856.83 MDTQ ( 12) Where the resultset is pushed out to all partitions via the BTQ operation and where the row_number() operator is applied. A simplified example data set : TQ11 receives all matching rows and it will be assigning the rank to all rows as it receives them. say TQ11 on partition 0 receives row a b c d, it will assign these row_number() / rank value : a rownum 1 b rownum 2 c rownum 3 d rownum 4 but partition 1 might receive the rows in a different order : c b a d so on partition 1 the assigned rownumbers are different a rownum 3 b rownum 2 c rownum 1 d rownum 4 hence when the data is then rejoined with the base table, the result might be a 1 b 2 c 1 d 4 with incorrect duplicate values for the rownumber() column. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description. Install release and fixpak that has * * this fix. * **************************************************************** * RECOMMENDATION: * * Install release and fixpak that has this fix. * **************************************************************** | |
Local Fix: | |
none available. Install release and fixpak that has this fix. | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : IT17851 follow-up : | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 23.11.2016 30.05.2017 30.05.2017 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |