Oracle 10: Incomprehensible behaviour on INSERT into a view?

Question

we have a strange problem here, we can't explain to ourselves.

We have a view in an Oracle DB Version 10.2.0.5.8. The view uses an INSTEAD OF trigger.

This is the code for the trigger:

CREATE OR REPLACE TRIGGER V1_T1_BIUD
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON V1_T1
  FOR EACH ROW
DECLARE
  AnyId   NUMBER;
BEGIN
  IF INSERTING THEN
    INSERT INTO Table T1 (
       F1, F2, F3, F4, F5
    ) VALUES (
       :new.F1, :new.F2, :new.F3, :new.F4, :new.F5
    );
  ELSIF UPDATING THEN
    UPDATE T1 SET F1 = :new.F1,
                  F2 = :new.F2,
                  F3 = :new.F3,
                  F4 = :new.F4,
                  F5 = :new.F5
    WHERE F1 = :old.F1;
  ELSIF DELETING THEN
    DELETE FROM T1
    WHERE F1 = :old.F1;
  END IF;
END;
/

This is an example INSERT statement:

INSERT INTO V_T1 (
  F1, F2, F3, F4, F5
)
SELECT A.V, A.S, A.F, A.T, A.Z
FROM (
  SELECT 'E' V, 'N' S, 'ABC' F, 'E' T, 'E' Z FROM DUAL UNION ALL 
  SELECT 'E',   'Y',   'QWE',   'O',   'E'   FROM DUAL UNION ALL
  SELECT 'I',   'Y',   'GHJ',   'I',   'I'   FROM DUAL           
) A
ORDER BY 1, 2, 3;
COMMIT;

Pay attention to the ORDER BY clause at the end of the select. The result of this INSERT statement is something like this:

F1 F2 F3  F4 F5
---------------
E  N  ABC I  I 
E  Y  QWE I  I 
I  Y  GHJ I  I  

As you can see, the 4th and 5th column are incorrectly filled with the values of the last datarow in all other datarows.

If we change the INSERT statement like this:

INSERT INTO V_T1 (
  F1, F2, F3, F4, F5
)
SELECT A.V, A.S, A.F, A.T, A.Z
FROM (
  SELECT 'E' V, 'N' S, 'ABC' F, 'E' T, 'E' Z FROM DUAL UNION ALL 
  SELECT 'E',   'Y',   'QWE',   'O',   'E'   FROM DUAL UNION ALL
  SELECT 'I',   'Y',   'GHJ',   'I',   'I'   FROM DUAL           
) A
ORDER BY 1, 2, 3, 4, 5;
COMMIT;

the result is this:

F1 F2 F3  F4 F5
---------------
E  N  ABC E  E 
E  Y  QWE O  E 
I  Y  GHJ I  I

Again, pay attention to the ORDER BY clause, which now orders all rows instead of the first three in the first insert statement.

edit: If you omit the ORDER BY clause the result is also as expected (e. g. like in example 2).

Can someone explain this behaviour to me?

P. S. Concerning the comments:

I have not time to investigate or deliver any more infos on this topic today. I will create a complete example on our database and publish it here in the next few days. Thank you for your patience!

Answer 1

This does look like a bug, but I can't find an obvious match in the bug database (a few look possible, like 5842445, but are vague or don't quite line up). I can only make it happen with the trigger (so I assume your inserts being against T1 rather than V1_T1 are a transcription error); and only if F4 and F5 are CHAR not VARCHAR2:

create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3),
    f4 char(2), f5 char(2));

create view v1_t1 as select * from t1;

... and the instead of trigger exactly as shown in the question.

The :NEW values inside the trigger are wrong, according to DBMS_OUTPUT, but how that's affected by the column data type is something only Oracle would be able to figure out I think.

It also still happens in 11.2.0.3 (Linux). Interestingly if I change the UNION ALL to just UNION I get slightly different results; in 10g the two columns end up null, in 11g they have x:

insert into v1_t1 (
  F1, F2, F3, F4, F5
)
SELECT A.V, A.S, A.F, A.T, A.Z
FROM (
    SELECT 'E' V, 'N' S, 'ABC' F, 'E' T, 'E' Z FROM DUAL UNION
    SELECT 'E',   'Y',   'QWE',   'O',   'E'   FROM DUAL UNION
    SELECT 'I',   'Y',   'GHJ',   'I',   'I'   FROM DUAL
) A
ORDER BY 1, 2, 3;

3 rows created.

select * from v1_t1;

F1 F2 F3  F4 F5
-- -- --- -- --
E  N  ABC x  x
E  Y  QWE x  x
I  Y  GHJ x  x

... which is even stranger - looks like maybe a fix to some other bug has slightly affected this one.

So not really an answer; you'd need to rase a service request with Oracle, and I'm fairly sure they'd just tell you to remove the order by since it doesn't have any value, as you already know.

For Thilo; plan without any order by (11g):

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     3 |    51 |     9  (34)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | V1_T1 |       |       |            |          |
|   2 |   VIEW                   |       |     3 |    51 |     9  (34)| 00:00:01 |
|   3 |    SORT UNIQUE           |       |     3 |       |     9  (78)| 00:00:01 |
|   4 |     UNION-ALL            |       |       |       |            |          |
|   5 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
|   7 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

And plan with order by 1,2,3 or 1,2,3,4,5 - same plan hash value (11g):

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     3 |    51 |    10  (40)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | V1_T1 |       |       |            |          |
|   2 |   SORT ORDER BY          |       |     3 |    51 |    10  (40)| 00:00:01 |
|   3 |    VIEW                  |       |     3 |    51 |     9  (34)| 00:00:01 |
|   4 |     SORT UNIQUE          |       |     3 |       |     9  (78)| 00:00:01 |
|   5 |      UNION-ALL           |       |       |       |            |          |
|   6 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
|   8 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

And I see the same sort of corruption selecting from other tables, but only if the results in the subquery are unioned before being ordered; though then I get nulls rather than x. (I briefly wondered if the x was coming from dual itself, but dummy is upper-case X, and this shows lower-case x).

---

Following @Annjawn's comment, changing the insert from V1_T1 to a direct insert in T1 works fine (i.e. correct values inserted), and curiously has the same plan hash even though it shows the table name instead of the view in the Name column. Work with either UNION or UNION ALL, too, and in both 10gR2 and 11gR2. Seems to be the trigger that's confused by the union, I guess.

---

Further to the datatype point... the view has to have char columns, the table does not necessarily, which isn't really a surprise since the trigger on the view seems to be the problem. If I set the table up with char columns but cast them to varchar2 in the view then I don't see the problem:

create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3),
    f4 char(2), f5 char(2));

create view v1_t1 as select f1, f2, f3, cast(f4 as varchar(2)) f4,
    cast(f5 as varchar(2)) f5
from t1;

But If I do it the other way around it does exhibit the problem:

create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3),
    f4 varchar(2), f5 varchar(2));

create view v1_t1 as select f1, f2, f3, cast(f4 as char(2)) f4,
    cast(f5 as char(2)) f5
from t1;