DB2 - Problembeschreibung
Problem IT27705 | Status: Geschlossen |
MERGE STATEMENT INCORRECTLY UPDATES TARGET ROW MULTIPLE TIMES. THE STATEMENT SHOULD HAVE RETURNED ERROR -788 | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
Data corruption is possible with MERGE statement using DISTINCT. Statement should have returned SQL0788N. Example: create table S (C1 smallint, C2 varchar(10) ); create table T (C1 smallint, C2 varchar(10) ); insert into S values (1, 'A'); insert into S values (2, 'B'); insert into S values (3, 'C'); insert into S values (3, 'C2'); insert into S values (4, 'D'); insert into T values (1, 'A'); insert into T values (2, 'B'); insert into T values (3, 'C'); insert into T values (4, 'D'); db2 => merge into T using (select distinct c1, c2 from S) S on T.C1 = S.C1 when matched then update set T.C2 = T.C2 || '-' || S.C2 DB20000I The SQL command completed successfully. Both target rows with C1=3 are updated. The above statement should get error -788. db2 => select * from T; C1 C2 ------ ---------- 1 A-A 2 B-B 3 C-C2 4 D-D 4 record(s) selected. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * See Error Description, Please, install this fix. * **************************************************************** | |
Local-Fix: | |
If the DISTINCT clause is not strictly necessary, remove it. Otherwise, ensure that each column in the source table-reference is also included in the MERGE search condition, e.g. for the example above, add predicate T.C2=S.C2: merge into T using (select distinct c1, c2 from S) S on T.C1 = S.C1 and T.C2 = S.C2 when matched then update set T.C2 = T.C2 || '-' || S.C2; | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : Nachfolger : IT27712 | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 09.01.2019 28.06.2020 28.06.2020 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version |