db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Problem with CHECK constraints
Date Tue, 11 Nov 2008 18:22:02 GMT
I have stumbled on a problem with CHECK constraints and would like the 
community's advice about how to tackle it.

The problem affects CHECK constraints which invoke user-coded functions. 
The Derby behavior looks wrong to me but I'm having trouble figuring out 
what the correct behavior should be according to the ANSI/ISO spec.

Simply put, the problem is this: For unqualified function names, what 
schema should Derby use to resolve the function reference?

For instance, here's a problematic CHECK constraint created by user 

create table t_bp_2( a int, constraint t_bp_2_check check ( f_fp_minus( 
a ) < 0 ) );
grant insert on t_bp_2 to public;

This is what happens when user janet tries to insert into the table and 
run the CHECK constraint:

insert into test_dbo.t_bp_2( a ) values ( 100 );

ERROR 42Y03: 'F_FP_MINUS' is not recognized as a function or procedure.

That looks wrong to me. I have also verified that if the janet schema 
holds a function by that name, then that function will be compiled into 
the INSERT statement. This means that the CHECK constraint will produce 
different results, depending on which user runs the INSERT. This in turn 
violates the "retrospective determinacy" of CHECK constraints required 
by the ANSI/ISO standard, part 2,

1) section 11.9 (<check constraint definition>), syntax rule 5
2) same section, general rule 1
3) section 11.6 (<table constraint definition>), general rule 3
4) section 4.16 (Determinism)

Determinacy would be enforced if the function were bound to the same 
schema regardless of what user runs the INSERT statement. But what 
schema should that be for an unqualified function name:

A) The schema that holds the CHECK constraint?
B) The schema that holds the table?
C) The current schema when the CREATE TABLE statement was issued?

I would appreciate the community's feedback:

i) What should the correct schema be? It would be great if you could 
quote chapter and verse from the SQL standard.

ii) What does DB2 do in this situation?


View raw message