Lukas Eder Lukas Eder - 2 months ago 11
SQL Question

Derby's handling of NULL values

I am new to Derby and I noticed that I face similar problems as when using the DB2 RDBMS as far as

null
values are concerned. The Derby documentation states, that a
null
value must have a type associated with it (something that DB2 finally got rid of in version 9.7):

http://db.apache.org/derby/docs/10.7/ref/crefsqlj21305.html

Now, I am trying to find a general solution to this problem here as this will be a part of my database abstraction library jOOQ. The below example just documents the problem. Think of any other (more complex) example. The following doesn't work:

insert into T_AUTHOR (
ID, FIRST_NAME, LAST_NAME,
DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS)
select
1000, 'Lukas', 'Eder',
'1981-07-10', null, null
from SYSIBM.SYSDUMMY1


Neither does this (which is what is actually done by jOOQ):

insert into T_AUTHOR (
ID, FIRST_NAME, LAST_NAME,
DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS)
select ?, ?, ?, ?, ?, ?
from SYSIBM.SYSDUMMY1


Because the two
null
values have no type associated with it. The solution would be to write something like this:

insert into T_AUTHOR (
ID, FIRST_NAME, LAST_NAME,
DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS)
select
1000, 'Lukas', 'Eder',
'1981-07-10', cast(null as int), cast(null as varchar(500))
from SYSIBM.SYSDUMMY1


Or like this, respectively

insert into T_AUTHOR (
ID, FIRST_NAME, LAST_NAME,
DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS)
select
?, ?, ?, ?, cast(? as int), cast(? as varchar(500))
from SYSIBM.SYSDUMMY1


But very often, in Java, the type that
null
should be cast to is unknown:


  • In this example, the types could be derived from the insert clause, but that might prove to be complicated or impossible for more general use-cases.

  • In other examples, I could just pick any type for the cast (e.g. always casting to
    int
    ), but that wouldn't work in this example, as you cannot put a
    cast(null as int)
    value into
    ADDRESS
    .

  • With HSQLDB (another candidate for this problem), I can simply write
    cast(null as object)
    which will work in most cases. But Derby does not have an
    object
    type.



This problem has been annoying me with DB2 before and I haven't found a solution yet. Does anyone know of a stable, and general solution to this problem for any of these RDBMS?


  • Derby

  • DB2


Answer

If you use the VALUES clause on your INSERT, you don't have to cast the NULL values:

insert into T_AUTHOR (
  ID, FIRST_NAME, LAST_NAME, 
  DATE_OF_BIRTH, YEAR_OF_BIRTH, ADDRESS) 
VALUES ( 
  1000, 'Lukas', 'Eder', 
  '1981-07-10', null, null 
);

This will work like you expect (i.e. the database can determine that the NULLs correspond to an integer and varchar(500). This works in both DB2 and Derby (and should work in pretty much any other database engine, as well).

You can use VALUES with parameter markers as well, without having to CAST them.

The reason that you have to cast when issuing an insert into ... select from statement is because the SELECT portion takes precedence -- the select statement returns certain data types, regardless of whether they are compatible with the table you're trying to insert them in to. If they aren't compatible, you will either get an error (with strongly typed database engines like DB2 <= 9.5) or the engine will do implicit type conversion (when possible).