3 Replies Latest reply: Dec 21, 2012 3:59 PM by orafad RSS

    Correlated subquery in delete statement predicate does not work.

    dgathman
      On Oracle XE 10g 10.2.01, if a correlated subquery in the predicate of a delete statement uses a column in the correlated record to compare against a column from a view that contains a union, and a cross join, it causes the delete not to work (0 rows deleted). Test case:

      CREATE TABLE A (ID NUMBER);

      CREATE TABLE C (ID NUMBER);
      ALTER TABLE C ADD CONSTRAINT XPKC PRIMARY KEY (ID);

      CREATE TABLE B (ID NUMBER, C_ID NUMBER);
      ALTER TABLE B ADD CONSTRAINT XPKB PRIMARY KEY (ID);
      CREATE INDEX XIF1B ON B(C_ID ASC);
      ALTER TABLE B ADD (CONSTRAINT FK_B FOREIGN KEY (C_ID) REFERENCES C (ID));

      INSERT INTO A VALUES (1);
      INSERT INTO C VALUES (1);
      INSERT INTO B VALUES (1,1);

      CREATE OR REPLACE FORCE VIEW ACL_VW (A_ID, C_ID) AS
      SELECT A.ID, A.ID
      FROM A
      UNION
      SELECT A.ID, C.ID
      FROM C
      CROSS JOIN A;

      --This select works:
      SELECT * FROM B
      WHERE id = 1
      AND EXISTS (SELECT 1 FROM ACL_VW WHERE C_ID = B.C_ID AND A_ID=1)

      --A delete using the same predicate does not work (results in 0 rows deleted)
      DELETE FROM B
      WHERE id = 1
      AND EXISTS (SELECT 1 FROM ACL_VW WHERE C_ID = B.C_ID AND A_ID=1)




      This works correctly on other 10G Oracle editions with the latest patchset 10.2.0.5. Besides going to 11G XE is there anyway to update 10G XE to 10.2.0.5?

      Thanks,
      Don