Hello,
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I have a strange behaviour with a MERGE INTO statement.
I have a query that update COMPANY_ROLE_PERMISSION rows to the new version of a configuration if and only if this COMPANY_ROLE_PERMISSION belong to the new configuration. In the other case, the COMPANY_ROLE_PERMISSION is deleted.
The query is the following :
MERGE INTO COMPANY_ROLE_PERMISSION crp USING (
SELECT DISTINCT crp.IDCOMPANYROLEPERMISSION AS CRPID, crp.IDCTXROLETYPEPERMISSION AS CURRENTID, ncrtp.IDCTXROLETYPEPERMISSION AS NEWID
FROM COMPANY_ROLE_PERMISSION crp
INNER JOIN CTX_ROLE_TYPE_PERMISSION crtp ON crtp.IDCTXROLETYPEPERMISSION=crp.IDCTXROLETYPEPERMISSION
INNER JOIN COMMON_CONTEXT comc ON comc.IDCOMMONCONTEXT=crtp.IDCOMMONPERMISSION
INNER JOIN CTX_ROLE_TYPE crt ON crt.IDCTXROLETYPE=crtp.IDCTXROLETYPE
INNER JOIN COMPANY_CTX_COMMON_CTX cccc ON cccc.IDCOMPANYCTXCOMMONCTX=crt.IDCOMPANYCTXCOMMONCTX
INNER JOIN COMPANY_CONTEXT cc ON cc.IDCOMPANYCONTEXT=cccc.IDCOMPANYCONTEXT
LEFT OUTER JOIN CTX_ROLE_TYPE ncrt ON ncrt.IDCTXROLETYPE_PREVIOUS=crt.IDCTXROLETYPE
LEFT OUTER JOIN CTX_ROLE_TYPE_PERMISSION ncrtp ON ncrtp.IDCTXROLETYPE=ncrt.IDCTXROLETYPE
LEFT OUTER JOIN COMMON_CONTEXT ncomc ON ncomc.IDCOMMONCONTEXT=ncrtp.IDCOMMONPERMISSION
WHERE cc.IDCOMPANY = :companyId AND (comc.CODE=ncomc.CODE OR ncomc.CODE IS NULL) ) TMP
ON (crp.IDCOMPANYROLEPERMISSION = TMP.CRPID)
WHEN MATCHED THEN UPDATE SET crp.IDCTXROLETYPEPERMISSION=TMP.NEWID
DELETE WHERE crp.IDCTXROLETYPEPERMISSION=TMP.CURRENTID
I have simplified a bit the query with a custom dataset (even with this version, the issue remains): the LEFT OUTER JOIN has always data on the right side so NEWID is never null. The COMPANY_ROLE_PERMISSION always belong to a new configuration.
Line 14 crp.IDCTXROLETYPEPERMISSION is updated with the new configuration ID.
Line 15 crp is deleted if and only if the configuration ID (IDCTXROLETYPEPERMISSION) is equal to the old ID (CURRENTID). It should not happens because the ID is updated at line 14 with the new ID.
My issue is that the where of the delete clause is executed and all the updated rows are deleted. If I remove DISTINCT (the dataset contains no duplicate) or if I add an ORDER BY (whatever the criterion) the statement behave correctly.
My understanding is that the subquery is executed a second time for the delete clause but on the updated data.
Help will be welcome
Thanks,
Nicolas
EDIT with scenario to reproduce the issue:
DML to create and insert data
DROP TABLE T1;
DROP TABLE IT1;
DROP TABLE OT1;
CREATE TABLE T1 (
ID INTEGER,
V INTEGER,
PIVOT INTEGER
);
CREATE TABLE IT1 (
ID INTEGER
);
CREATE TABLE OT1 (
ID INTEGER,
FOO INTEGER,
NV INTEGER
);
INSERT INTO T1 (ID,V,PIVOT) VALUES (1,1,1);
INSERT INTO T1 (ID,V,PIVOT) VALUES (2,1,1);
INSERT INTO IT1 (ID) VALUES (1);
INSERT INTO IT1 (ID) VALUES (2);
INSERT INTO OT1 (ID,NV,FOO) VALUES (1,2,0);
INSERT INTO OT1 (ID,NV,FOO) VALUES (2,2,0);
commit;
The query
MERGE INTO T1 TARGET USING (
SELECT DISTINCT T1.ID,T1.V, OT1.NV
FROM T1
INNER JOIN IT1 ON T1.ID = IT1.ID
LEFT OUTER JOIN OT1 ON OT1.ID = IT1.ID
WHERE T1.PIVOT = 1 or OT1.FOO=40) SRC
ON (SRC.ID = TARGET.ID)
WHEN MATCHED THEN
UPDATE SET TARGET.V=SRC.NV
DELETE WHERE TARGET.V=SRC.V;
commit;
Before the merge T1 contains
At the end of the merge T1 must contains
But T1 is empty
Note the OT1.FOO=40 which is useless but seems to create the issue.