BUG in INSERT .. SELECT with nested objects

View All Public Scripts Login to Run Script

Script Name BUG in INSERT .. SELECT with nested objects
Visibility Unlisted - anyone with the share link can access
Description This script demonstrates a bug found in Oracle SQL where data in inserted columns is incorrect. This occurs only when using nested object as element of group by expression. No errors are thrown which is a SCARY! There are workarounds that allow for usage of nested object attributes in group by with correct results when inserting into table. Engineers that are ot aware of the bug might cause damage and loss to database data due to this bug.
Area SQL General / Data Manipulation
Contributor jacek.gebal@fmr.com
Created Sunday April 05, 2020

Statement 1

setup objects

setup objects

create or replace type nested_item force as object ( 
  some_id   integer, 
  unit_cost number 
);

Type created.

Statement 2

setup objects

setup objects

create or replace type item force as object ( 
  id       integer, 
  sub_item nested_item 
);

Type created.

Statement 3

setup objects

setup objects

create or replace type items force as table of item;

Type created.

Statement 4

data generator function

data generator function

create or replace function get_items( 
  p_how_many positiven := 10 
) return items is 
  l_items items := items( ); 
begin 
  for i in 1 .. p_how_many loop 
    l_items.extend; 
    l_items( l_items.last ) := 
      item( 
        id => i, 
        sub_item => 
          nested_item( 
            some_id => mod( i - 1, 5 ) + 1, 
            unit_cost => i / 10 
            ) 
        ); 
  end loop; 
  return l_items; 
end;

Function created.

Statement 5

Query produces expected results

base query and proper results

select 
  objs.id, 
  objs.sub_item.some_id, 
  sum( objs.sub_item.unit_cost ) 
  from table (get_items( 10 ) ) objs 
 group by 
   objs.id, 
   objs.sub_item.some_id 
 order by 
   objs.id, 
   objs.sub_item.some_id


ID SUB_ITEM.SOME_ID SUM(OBJS.SUB_ITEM.UNIT_COST)
1 1 .1
2 2 .2
3 3 .3
4 4 .4
5 5 .5
6 1 .6
7 2 .7
8 3 .8
9 4 .9
10 5 1

10 rows selected.

ID	SUB_ITEM.SOME_ID	SUM(OBJS.SUB_ITEM.UNIT_COST)
1	1	.1
2	2	.2
3	3	.3
4	4	.4
5	5	.5
6	1	.6
7	2	.7
8	3	.8
9	4	.9
10	5	1

Statement 6

Target table for insert statement

Target table for insert statement

create table target ( 
  test_case_number integer, 
  item_id          integer, 
  some_id          integer, 
  some_value       number 
)

Table created.

Statement 7

insert from base query gives proper results

insert from base query

insert into target ( 
  test_case_number, 
  item_id, 
  some_id, 
  some_value 
  ) 
select 
  1                              as test_case_number, 
  objs.id                        as id, 
  objs.sub_item.some_id          as some_id, 
  sum( objs.sub_item.unit_cost ) as some_value 
  from table (get_items( 10 ) ) objs 
 group by 
   objs.id, 
   objs.sub_item.some_id

10 row(s) inserted.

Statement 8
```
commit
```
```
Statement processed.
```

Statement 9

ALL GOOD

check query results

select 
  item_id, 
  some_id, 
  some_value 
  from target 
 where test_case_number = 1 
 order by 
   item_id, 
   some_id


ITEM_ID SOME_ID SOME_VALUE
1 1 .1
2 2 .2
3 3 .3
4 4 .4
5 5 .5
6 1 .6
7 2 .7
8 3 .8
9 4 .9
10 5 1

10 rows selected.

ITEM_ID	SOME_ID	SOME_VALUE
1	1	.1
2	2	.2
3	3	.3
4	4	.4
5	5	.5
6	1	.6
7	2	.7
8	3	.8
9	4	.9
10	5	1

Statement 10

Create additional table

create table lookup ( 
  some_id    integer, 
  multiplier number, 
  constraint lookup_pk primary key ( some_id ) 
)

Table created.

Statement 11

Populate additional table

Populate additional table

insert into lookup ( 
  some_id, multiplier 
) 
select rownum, 6 - rownum 
  from dual connect by level <= 5

5 row(s) inserted.

Statement 12
```
commit
```
```
Statement processed.
```

Statement 13

select * from lookup


SOME_ID MULTIPLIER
1 5
2 4
3 3
4 2
5 1

5 rows selected.

SOME_ID	MULTIPLIER
1	5
2	4
3	3
4	2
5	1

Statement 14

This is the query that was supposed to be used for populating data in target table. Note the data in column SOME_ID

Target query

select 
  objs.id as id, 
  objs.sub_item.some_id as some_id, 
  sum( objs.sub_item.unit_cost * lookup.multiplier ) as some_value 
  from 
    table (get_items( 10 )) objs 
    join lookup 
    on lookup.some_id = objs.sub_item.some_id 
 group by objs.id, objs.sub_item.some_id 
 order by 
   objs.id, 
   objs.sub_item.some_id


ID SOME_ID SOME_VALUE
1 1 .5
2 2 .8
3 3 .9
4 4 .8
5 5 .5
6 1 3
7 2 2.8
8 3 2.4
9 4 1.8
10 5 1

10 rows selected.

ID	SOME_ID	SOME_VALUE
1	1	.5
2	2	.8
3	3	.9
4	4	.8
5	5	.5
6	1	3
7	2	2.8
8	3	2.4
9	4	1.8
10	5	1

Statement 15

Data inserted is not correct. Column SOME_ID is not populated correctly.

BUG - insert as select

insert into target ( 
  test_case_number, 
  item_id, 
  some_id, 
  some_value 
  ) 
select 
  2 as test_case_number, 
  objs.id as id, 
  objs.sub_item.some_id as some_id, 
  sum( objs.sub_item.unit_cost * lookup.multiplier ) as some_value 
  from 
    table (get_items( 10 )) objs 
    join lookup 
    on lookup.some_id = objs.sub_item.some_id 
 group by objs.id, objs.sub_item.some_id 
 order by 
   objs.id, 
   objs.sub_item.some_id

10 row(s) inserted.

Statement 16
```
commit
```
```
Statement processed.
```

Statement 17

Data populated is not correct - column SOME_ID contains the same value across all rows.

Data populated is not correct

select 
  item_id, 
  some_id, 
  some_value 
  from target 
 where test_case_number = 2 
 order by 
   item_id, 
   some_id


ITEM_ID SOME_ID SOME_VALUE
1 5 .5
2 5 .8
3 5 .9
4 5 .8
5 5 .5
6 5 3
7 5 2.8
8 5 2.4
9 5 1.8
10 5 1

10 rows selected.

ITEM_ID	SOME_ID	SOME_VALUE
1	5	.5
2	5	.8
3	5	.9
4	5	.8
5	5	.5
6	5	3
7	5	2.8
8	5	2.4
9	5	1.8
10	5	1

Statement 18

Encapsulating table of objects in an inline view and introducing column aliases for queried attributes works around that bug.

Workaround - use inline view with column aliases

insert into target (
  test_case_number,
  item_id,
  some_id,
  some_value
  )
select
  3 as test_case_number,
  objs.id,
  objs.some_id,
  sum( objs.unit_cost * lookup.multiplier ) as some_value
  from (
    select
      o.id as id,
      o.sub_item.some_id as some_id,
      o.sub_item.unit_cost as unit_cost
     from table ( get_items( 10 ) ) o
    ) objs
    join lookup
    on lookup.some_id = objs.some_id
 group by objs.id, objs.some_id
 order by
   objs.id,
   objs.some_id

10 row(s) inserted.

Statement 19
```
commit
```
```
Statement processed.
```

Statement 20

Inserted data is correct again when inserted using query with inline view.

select 
  item_id, 
  some_id, 
  some_value 
  from target 
 where test_case_number = 3 
 order by 
   item_id, 
   some_id


ITEM_ID SOME_ID SOME_VALUE
1 1 .5
2 2 .8
3 3 .9
4 4 .8
5 5 .5
6 1 3
7 2 2.8
8 3 2.4
9 4 1.8
10 5 1

10 rows selected.

ITEM_ID	SOME_ID	SOME_VALUE
1	1	.5
2	2	.8
3	3	.9
4	4	.8
5	5	.5
6	1	3
7	2	2.8
8	3	2.4
9	4	1.8
10	5	1

Statement 21

Another alternative workaround is encapsulating the whole select statement into a named sub-query using WITH clause and explicit ORDER BY in the named subquery.

Workaround - Named sub-query with ORDER BY

insert into target ( 
  test_case_number, 
  item_id, 
  some_id, 
  some_value 
  ) 
  with 
    input_data as ( 
    select 
      4 as test_case_number, 
      objs.id, 
      objs.sub_item.some_id, 
      sum( objs.sub_item.unit_cost * lookup.multiplier ) 
      from 
        table (get_items( 10 )) objs 
        join lookup 
        on lookup.some_id = objs.sub_item.some_id 
     group by objs.id, objs.sub_item.some_id 
     order by objs.id, objs.sub_item.some_id  
    ) 
select * 
  from input_data

10 row(s) inserted.

Statement 22
```
commit
```
```
Statement processed.
```

Statement 23

Inserted data is correct when inserted using named sub-query and explicit ORDER BY clause.

select 
  item_id, 
  some_id, 
  some_value 
  from target 
 where test_case_number = 4 
 order by 
   item_id, 
   some_id


ITEM_ID SOME_ID SOME_VALUE
1 1 .5
2 2 .8
3 3 .9
4 4 .8
5 5 .5
6 1 3
7 2 2.8
8 3 2.4
9 4 1.8
10 5 1

10 rows selected.

ITEM_ID	SOME_ID	SOME_VALUE
1	1	.5
2	2	.8
3	3	.9
4	4	.8
5	5	.5
6	1	3
7	2	2.8
8	3	2.4
9	4	1.8
10	5	1

Statement 24

Bug still occurs when WITH clause doesn't contain ORDER BY

BUG - Named sub-query without ORDER BY

insert into target ( 
  test_case_number, 
  item_id, 
  some_id, 
  some_value 
  ) 
  with 
    input_data as ( 
    select 
      5 as test_case_number, 
      objs.id, 
      objs.sub_item.some_id, 
      sum( objs.sub_item.unit_cost * lookup.multiplier ) 
      from 
        table (get_items( 10 )) objs 
        join lookup 
        on lookup.some_id = objs.sub_item.some_id 
     group by objs.id, objs.sub_item.some_id 
    ) 
select * 
  from input_data 
 order by 1, 2

10 row(s) inserted.

Statement 25
```
commit
```
```
Statement processed.
```

Statement 26

Data populated is not correct - column SOME_ID contains the same value across all rows.

select 
  item_id, 
  some_id, 
  some_value 
  from target 
 where test_case_number = 5 
 order by 
   item_id, 
   some_id


ITEM_ID SOME_ID SOME_VALUE
1 5 .5
2 5 .8
3 5 .9
4 5 .8
5 5 .5
6 5 3
7 5 2.8
8 5 2.4
9 5 1.8
10 5 1

10 rows selected.

ITEM_ID	SOME_ID	SOME_VALUE
1	5	.5
2	5	.8
3	5	.9
4	5	.8
5	5	.5
6	5	3
7	5	2.8
8	5	2.4
9	5	1.8
10	5	1

Statement 27

Workaround - Named sub-query without ORDER BY and with MATERIALIZE hint

insert into target ( 
  test_case_number, 
  item_id, 
  some_id, 
  some_value 
  ) 
  with 
    input_data as ( 
    select /*+ materialize */ 
      6 as test_case_number, 
      objs.id, 
      objs.sub_item.some_id, 
      sum( objs.sub_item.unit_cost * lookup.multiplier ) 
      from 
        table (get_items( 10 )) objs 
        join lookup 
        on lookup.some_id = objs.sub_item.some_id 
     group by objs.id, objs.sub_item.some_id 
    ) 
select * 
  from input_data 
 order by 1, 2

10 row(s) inserted.

Statement 28
```
commit
```
```
Statement processed.
```

Statement 29

Inserted data is correct again when inserted using query with inline view.

select 
  item_id, 
  some_id, 
  some_value 
  from target 
 where test_case_number = 6 
 order by 
   item_id, 
   some_id


ITEM_ID SOME_ID SOME_VALUE
1 1 .5
2 2 .8
3 3 .9
4 4 .8
5 5 .5
6 1 3
7 2 2.8
8 3 2.4
9 4 1.8
10 5 1

10 rows selected.

ITEM_ID	SOME_ID	SOME_VALUE
1	1	.5
2	2	.8
3	3	.9
4	4	.8
5	5	.5
6	1	3
7	2	2.8
8	3	2.4
9	4	1.8
10	5	1

Statement 30

drop function get_items

Function dropped.

Statement 31
```
drop type items
```
```
Type dropped.
```
Statement 32
```
drop type item
```
```
Type dropped.
```
Statement 33
```
drop type nested_item
```
```
Type dropped.
```
Statement 34
```
drop table lookup
```
```
Table dropped.
```
Statement 35
```
drop table target
```
```
Table dropped.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database