Combinations and consequences

Posted by

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.

One comment

  1. Hi

    One other this kind of error is when using constraint based on virtual column during array insert and table interval partitioned by virtual column.
    Too many features used…

    drop table testx;
    create table testx (
    PARTITION_DATE DATE GENERATED ALWAYS AS (GREATEST(“TIMECREATED”,NVL(“LOAD_DATE”,”TIMECREATED”),NVL(“UNLOAD_DATE”,”TIMECREATED”))) NOT NULL,
    LOAD_DATE DATE,
    UNLOAD_DATE DATE,
    TIMECREATED DATE NOT NULL
    )
    PARTITION BY RANGE (PARTITION_DATE)
    INTERVAL( NUMTOYMINTERVAL(1,’MONTH’))
    (
    PARTITION DEFAULT_PARTITION VALUES LESS THAN (TO_DATE(‘ 2020-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
    );

    ALTER TABLE testx ADD (
    CONSTRAINT testx_PARTITION_LOADDATE
    CHECK (nvl(LOAD_DATE, TIMECREATED) <= PARTITION_DATE),
    CONSTRAINT testx_PARTITION_TIMECREA
    CHECK (TIMECREATED <= PARTITION_DATE),
    CONSTRAINT testx_PARTITION_UNLOAD
    CHECK (nvl(UNLOAD_DATE,TIMECREATED) <= PARTITION_DATE));

    insert into testx( load_date, unload_date, timecreated) select
    to_date('2020-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),
    to_date('2020-03-14 00:00:00','yyyy-mm-dd hh24:mi:ss') + rownum ,
    to_date('2020-03-15 00:00:00','yyyy-mm-dd hh24:mi:ss') + rownum
    from dual connect by level < 40;
    ORA-02290: check constraint (TESTX_PARTITION_TIMECREA) violated

    These kind of problems occuring when combining several features are sometimes real nuisance when one tries to invent new ways to solve problems.

    But maybe someday these get corrected…

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.