Combinations and consequences

Fellow Perth techie Scott Wesley sent me this interesting puzzle recently. He was using the long awaited feature of being (finally) able to assign a sequence value via the DEFAULT clause in a table definition.

The problem was … the sequence was NOT being assigned. And since that column was defined as NOT NULL, his application was breaking. We had some to-and-fro and finally I managed to reduce it down a very small test case, which I’ll build from scratch here.

SQL>
SQL> create sequence seq;

Sequence created.

SQL>
SQL> create table t1
  2    ( c1  number not null enable,
  3      id  number default seq.nextval not null enable
  4     );

Table created.

Here is the reason I wanted to blog about this particular example. Just like our own applications, the Oracle database is an application. A very large, impressive and complex application, but an application nonetheless. And generally, the places where (any) application code encounters problems is when separate components are brought together in ways that are not expected. For example, if my application has 5 components, then I probably only need 5 unit tests to prove that those 5 components work. But if I want to test the combinations of those 5 components interacting together, now I need 31 tests, and that is not considering the sequencing of those 5 components, in which case it explodes to near 200. Expand an application out to 1000’s of components, and it is impossible to prove every possible permutation of usage.

Which brings us back to the example Scott sent me. Let’s do some testing of INSERT-SELECT components in isolation.

SQL>
SQL> --
SQL> -- INSERT-SELECT is fine!
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select 11713
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p;

2 rows created.


SQL>
SQL> --
SQL> -- INSERT-SELECT with ORDER BY is fine!
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select 11713
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y;

2 rows created.


SQL>
SQL> --
SQL> -- INSERT-SELECT with a BIND is fine!
SQL> --
SQL>
SQL> variable x number
SQL> exec :x := 11713;

PL/SQL procedure successfully completed.

SQL>
SQL> insert into t1  (c1)
  2  select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p;

2 rows created.

But when we bring all those components together…

SQL> --
SQL> -- INSERT-SELECT with a BIND and ORDER BY .... uh oh
SQL> --
SQL>
SQL> insert into t1  (c1)
  2  select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y;
insert into t1  (c1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("MCDONAC"."T1"."ID")

Chatting to the Optimizer team in Oracle, its probable that this bug has been present almost all the way back to early 11g versions, and yet has never been noticed until Scott chanced upon it.

A simple workaround is to wrap the SELECT part within an WITH statement

SQL> insert into t1  (c1)
  2  with ttt as ( select :x
  3  from ( select 1 x, 2 y from dual union all select 3,4 from dual ) p
  4  order by x,y
  5  )
  6  select * from ttt;
 
2 rows created.

but the message of this post is not about this particular idiosyncrasy, but more to always be on the lookout for where you might be “mixing and matching” elements of the Oracle database application in ways that perhaps are not considered typical. That’s why you are most likely to hit boundary cases such as this one.