Problem with CHECK constraints

[Rick Hillegas <Ri...@Sun.COM>]

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 
test_dbo:

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?

Thanks,
-Rick

Re: Problem with CHECK constraints

[Rick Hillegas <Ri...@Sun.COM>]

Thanks for the sleuthing, Dag. This would be answer (C) in my original mail:

C) The current schema when the CREATE TABLE statement was issued?

Regards,
-Rick

Dag H. Wanvik wrote:
> Hi,
>
> I found this in section 4.27.2 Characteristics of SQL-invoked routines:
>
>   
>> If a <routine invocation> is contained in a <query expression> of a
>> view, a check constraint, or an assertion, the <triggered action> of a
>>     
>         ****************
>   
>> trigger, or in an <SQL-invoked routine>, then the subject routine for
>> that invocation is determined at the time the view is created, the
>> check constraint is defined, the assertion is created, the trigger is
>> created, or the SQL-invoked routine is created.
>>     
>
> So it would appear that Derby's present behavior is wrong.
>
> Thanks,
> Dag
>   

Re: Problem with CHECK constraints

["Dag H. Wanvik" <Da...@Sun.COM>]

Hi,

I found this in section 4.27.2 Characteristics of SQL-invoked routines:

> If a <routine invocation> is contained in a <query expression> of a
> view, a check constraint, or an assertion, the <triggered action> of a
        ****************
> trigger, or in an <SQL-invoked routine>, then the subject routine for
> that invocation is determined at the time the view is created, the
> check constraint is defined, the assertion is created, the trigger is
> created, or the SQL-invoked routine is created.

So it would appear that Derby's present behavior is wrong.

Thanks,
Dag