Should db2 allow this table to be created

[Bernard Dhooghe]

Environment: DB2 UDB LUW 8.2

Following problem was submitted.

create table t1 (c1 char(10) not null)

insert into t1 values(default)

This will result in (db2 command line):

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,

TABLEID=7606, COLNO=0" is not allowed. SQLSTATE=23502
So the default of an insert is NULL in this case but it is not allowed
and the explanation of the error is quite clear:

"
o The update or insert value was DEFAULT, but the object column
was declared as NOT NULL without WITH DEFAULT in the table
definition. Consequently:

- A default value of NULL cannot be inserted into that
column.

"

Stretching this to it's limits:

should db2 allow to create a table not null without adding a default if
it assumes that the default will be NULL

or

is this a nice trick of db2 to avoid to insert a default (kind of: no
default clause but only possible for not null columns).

or

should there not be a clause: 'no default' , the real trouble is that
in case of static SQL (where the problem submission started from) , the
binder does not detect the statement will fail at runtime.
Bernard Dhooghe

[Serge Rielau]

Bernard Dhooghe wrote:

Environment: DB2 UDB LUW 8.2

Following problem was submitted.

create table t1 (c1 char(10) not null)

insert into t1 values(default)

This will result in (db2 command line):

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,

TABLEID=7606, COLNO=0" is not allowed. SQLSTATE=23502
So the default of an insert is NULL in this case but it is not allowed
and the explanation of the error is quite clear:

"
o The update or insert value was DEFAULT, but the object column
was declared as NOT NULL without WITH DEFAULT in the table
definition. Consequently:

- A default value of NULL cannot be inserted into that
column.

"

Stretching this to it's limits:

should db2 allow to create a table not null without adding a default if
it assumes that the default will be NULL

or

is this a nice trick of db2 to avoid to insert a default (kind of: no
default clause but only possible for not null columns).

or

should there not be a clause: 'no default' , the real trouble is that
in case of static SQL (where the problem submission started from) , the
binder does not detect the statement will fail at runtime.

In earlier version DB2 for LUW refused to compile that INSERT statement,
but consider this:
CREATE TRIGGER trg1 BEFORE INSERT ON T1 REFERENCING NEW AS N
FOR EACH ROW
SET n.c1 = COALECSE(n.c1, RAND() * 100);

Now your INSERT will succeed.
Now of course the CREATE TABLE doesn't know what your plans are for the
triggers and the INSERT should not be in the business of guessing what a
trigger would do.

from another point of view NOT NULL in the SQL Standard is defined as a
constraint. Constraints are checked at runtime after before triggers are
fired.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[shenanwei]

Use
create table t1 (c1 char(10) not null with default)
then when "insert into t1 values(default)"
DB2 will know what is the default value.
Serge Rielau wrote:

Bernard Dhooghe wrote:

Environment: DB2 UDB LUW 8.2

Following problem was submitted.

create table t1 (c1 char(10) not null)

insert into t1 values(default)

This will result in (db2 command line):

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,

TABLEID=7606, COLNO=0" is not allowed. SQLSTATE=23502
So the default of an insert is NULL in this case but it is not allowed
and the explanation of the error is quite clear:

"
o The update or insert value was DEFAULT, but the object column
was declared as NOT NULL without WITH DEFAULT in the table
definition. Consequently:

- A default value of NULL cannot be inserted into that
column.

"

Stretching this to it's limits:

should db2 allow to create a table not null without adding a default if
it assumes that the default will be NULL

or

is this a nice trick of db2 to avoid to insert a default (kind of: no
default clause but only possible for not null columns).

or

should there not be a clause: 'no default' , the real trouble is that
in case of static SQL (where the problem submission started from) , the
binder does not detect the statement will fail at runtime.

In earlier version DB2 for LUW refused to compile that INSERT statement,
but consider this:
CREATE TRIGGER trg1 BEFORE INSERT ON T1 REFERENCING NEW AS N
FOR EACH ROW
SET n.c1 = COALECSE(n.c1, RAND() * 100);

Now your INSERT will succeed.
Now of course the CREATE TABLE doesn't know what your plans are for the
triggers and the INSERT should not be in the business of guessing what a
trigger would do.

from another point of view NOT NULL in the SQL Standard is defined as a
constraint. Constraints are checked at runtime after before triggers are
fired.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Knut Stolze]

Bernard Dhooghe wrote:

Environment: DB2 UDB LUW 8.2

Following problem was submitted.

create table t1 (c1 char(10) not null)

insert into t1 values(default)

This will result in (db2 command line):

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,

TABLEID=7606, COLNO=0" is not allowed. SQLSTATE=23502
So the default of an insert is NULL in this case but it is not allowed
and the explanation of the error is quite clear:

"
o The update or insert value was DEFAULT, but the object column
was declared as NOT NULL without WITH DEFAULT in the table
definition. Consequently:

- A default value of NULL cannot be inserted into that
column.

"

Stretching this to it's limits:

should db2 allow to create a table not null without adding a default if
it assumes that the default will be NULL

No, there is no reason to be so restrictive there. Your applications could
always provide a value to be inserted, so that the NOT NULL constraint has
no effect on the apps.

or

is this a nice trick of db2 to avoid to insert a default (kind of: no
default clause but only possible for not null columns).

Default and NOT NULL are two different, orthogonal things. If you don't
specify an explicit default, then NULL is used. If that doesn't match with
your table definition, it is up to you how to handle it (in the app or via
triggers).

or

should there not be a clause: 'no default' , the real trouble is that
in case of static SQL (where the problem submission started from) , the
binder does not detect the statement will fail at runtime.

"No default" would mean (to me) that there is "no default value". An the
absence of a value is represented by NULL. So you will be exactly at the
same point again.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bernard Dhooghe]

Nice background information, thank you Serge.

Remark: So if no triggers are present for the table and a static insert
is made as described, the binder could check at bind time if the insert
is valid or not even if the run-tme check remains active as explained
in the answer.

Bernard Dhooghe

Serge Rielau wrote:

Bernard Dhooghe wrote:

Environment: DB2 UDB LUW 8.2

Following problem was submitted.

create table t1 (c1 char(10) not null)

insert into t1 values(default)

This will result in (db2 command line):

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,

TABLEID=7606, COLNO=0" is not allowed. SQLSTATE=23502
So the default of an insert is NULL in this case but it is not allowed
and the explanation of the error is quite clear:

"
o The update or insert value was DEFAULT, but the object column
was declared as NOT NULL without WITH DEFAULT in the table
definition. Consequently:

- A default value of NULL cannot be inserted into that
column.

"

Stretching this to it's limits:

should db2 allow to create a table not null without adding a default if
it assumes that the default will be NULL

or

is this a nice trick of db2 to avoid to insert a default (kind of: no
default clause but only possible for not null columns).

or

should there not be a clause: 'no default' , the real trouble is that
in case of static SQL (where the problem submission started from) , the
binder does not detect the statement will fail at runtime.

In earlier version DB2 for LUW refused to compile that INSERT statement,
but consider this:
CREATE TRIGGER trg1 BEFORE INSERT ON T1 REFERENCING NEW AS N
FOR EACH ROW
SET n.c1 = COALECSE(n.c1, RAND() * 100);

Now your INSERT will succeed.
Now of course the CREATE TABLE doesn't know what your plans are for the
triggers and the INSERT should not be in the business of guessing what a
trigger would do.

from another point of view NOT NULL in the SQL Standard is defined as a
constraint. Constraints are checked at runtime after before triggers are
fired.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

Bernard Dhooghe wrote:

Nice background information, thank you Serge.

Remark: So if no triggers are present for the table and a static insert
is made as described, the binder could check at bind time if the insert
is valid or not even if the run-tme check remains active as explained
in the answer.

DB2 for LUW compiles triggers into the insert statement (same with
check, RI, etc). So when you add/drop a trigger that will cause a rebind.
However the trigger could retrive the (not null) value from an external
UDF, completely outside the control of DB2. So whatever we do there is a
hole in what the compiler can control.
Further more, if one is very picky, the fact that NOT NULL is a
constraint, which must fire after the INSERT itself one can argue that
it must not mask any other errors which would show up prior. Let's say a
truncation or overflow in another column.
Most customers don't get excited about these sort of things, but from my
10 years in the SQL compiler I've learned the hard way that whenever the
language gets sloppy on the fringes it unravels like a badly knit
sweater. On the other hand then I could make a living explaining what
"LATERAL" and other gorpy stuff is all about. ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Bernard Dhooghe]

Great, thanks.

Remark: Maybe the binder could issue an error when there is no trigger
that could change the inserted value, in this case, 'default' which is,
as there is no default defined, a NULL value but this is not allowed by
the table definition/constraint. Kind of: trust but check. As early as
possible. And just issue a warning when there is a trigger that the
binder can not detect the result of.

Bernard Dhooghe

Serge Rielau wrote:

Bernard Dhooghe wrote:

Nice background information, thank you Serge.

Remark: So if no triggers are present for the table and a static insert
is made as described, the binder could check at bind time if the insert
is valid or not even if the run-tme check remains active as explained
in the answer.

DB2 for LUW compiles triggers into the insert statement (same with
check, RI, etc). So when you add/drop a trigger that will cause a rebind.
However the trigger could retrive the (not null) value from an external
UDF, completely outside the control of DB2. So whatever we do there is a
hole in what the compiler can control.
Further more, if one is very picky, the fact that NOT NULL is a
constraint, which must fire after the INSERT itself one can argue that
it must not mask any other errors which would show up prior. Let's say a
truncation or overflow in another column.
Most customers don't get excited about these sort of things, but from my
10 years in the SQL compiler I've learned the hard way that whenever the
language gets sloppy on the fringes it unravels like a badly knit
sweater. On the other hand then I could make a living explaining what
"LATERAL" and other gorpy stuff is all about. ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

Bernard Dhooghe wrote:

Great, thanks.

Remark: Maybe the binder could issue an error when there is no trigger
that could change the inserted value, in this case, 'default' which is,
as there is no default defined, a NULL value but this is not allowed by
the table definition/constraint. Kind of: trust but check. As early as
possible. And just issue a warning when there is a trigger that the
binder can not detect the result of.

We were considering it, but decided against it. Too twisted.
Note that DB2 is the exception when it comes to static SQL. Most DBMS
don't have the concept of a BIND statement and packages.
Being different in itself can be bad.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/