Check constrain execution timing change?

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Check constrain execution timing change?

Keith Medcalf

I believe that when inserting a row into a table the CHECK constraints (which includes any NOT NULL constraint) are checked at the wrong time, or at least with the wrong data.  The check should occur AFTER defaults and column affinity is applied before the data record is stored, meaning that the constraints should apply to the row as actually stored.  Consider:

create table x (x integer default ('1') check (typeof(x) == 'integer'));
insert into x values (1);
insert into x values ('1');
insert into x default values;

The last two statements raise constraint errors, even though the values that end up in the database would in fact pass the constraint:

create table x (x integer default ('1'));
insert into x values (1);
insert into x values ('1');
insert into x default values;
select x, typeof(x) from x;

1|integer
1|integer
1|integer

Similarly for the NOT NULL column constraint.  If should apply to the data actually stored, not the contents of the 'insert' the value may be modified before it is actually inserted (the working of the PRIMARY KEY on a ROWID table, or a default clause, for example) may modify the value before it is stored.

Presently, the NOT NULL column constraint is to the input data, and not to the row actually stored:

sqlite> create table x(x integer not null default (1));
sqlite> insert into x values (NULL);
Error: NOT NULL constraint failed: x.x

which also raises a NOT NULL constraint error even though the value stored will not be NULL and therefore passes the constraint.

I do not know exactly where before triggers fire, but they should probably fire directly on the input data after affinity is applied and before the check contraints run.  You could then use before triggers to limit or require specifying NULL inputs on an insert even if those NULL values would be changed to a default or computed value afterwards.

ie, the processing for inserting a record should be:

collect input data row
apply column affinity
fire before triggers
apply defaults, generated always, rowid etc.
apply column affinity to above columns
run constraints
store actul row
fire after triggers

I don't know if this would constitute a breaking change, but I don't think so ...

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check constrain execution timing change?

Richard Hipp-3
On 1/31/20, Keith Medcalf <[hidden email]> wrote:
>
> The check should occur AFTER defaults and
> column affinity is applied before the data record is stored
>

Why do you think this?  Is it documented somewhere?  I ask because
your test case gives the same answer (doing the first insert but
failing the other two) for every version of SQLite I checked from
trunk going back to 3.1.0 (2005-01-21).  Hence, for backwards
compatibility, even it is documented to do something differently, I
should probably change the documentation rather than the behavior.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check constrain execution timing change?

Keith Medcalf
In reply to this post by Keith Medcalf
>collect input data row
>apply column affinity
>fire before triggers
>apply defaults, generated always, rowid etc.
>apply column affinity to above columns
>run constraints
>store actul row
>fire after triggers

Actually, with generated columns it is a bit more complicated.  I think:

collect input data row
run generated column logic including not allowing specification of values for generated columns
apply column affinity
fire before triggers
apply defaults, rowid etc.
apply column affinity to defaults, rowid, etc. changed above
run constraints
store actul row
fire after triggers

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>collect input data row
>apply column affinity
>fire before triggers
>apply defaults, generated always, rowid etc.
>apply column affinity to above columns
>run constraints
>store actul row
>fire after triggers




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check constrain execution timing change?

Keith Medcalf
In reply to this post by Richard Hipp-3

On Friday, 31 January, 2020 13:58, Richard Hipp <[hidden email]> wrote:

>On 1/31/20, Keith Medcalf <[hidden email]> wrote:

>> The check should occur AFTER defaults and
>> column affinity is applied before the data record is stored

>Why do you think this?  Is it documented somewhere?  I ask because
>your test case gives the same answer (doing the first insert but
>failing the other two) for every version of SQLite I checked from
>trunk going back to 3.1.0 (2005-01-21).  Hence, for backwards
>compatibility, even it is documented to do something differently, I
>should probably change the documentation rather than the behavior.

You are absolutely correct in that this is the way SQLite has worked forever, and is the way it is documented (or not, I am not sure).  However, this means that the result of a CHECK/NOT NULL contstraint differs between an INSERT and when run on existing data (via pragma integrity_check).  This means that, for example, the construct

CHECK (typeof(x) in ('integer', 'real'))

enforces different table invariants between UPDATE/INSERT and when the data is already in the table since in the case of INSERT/UPDATE it checks the type of the value input to the UPDATE/INSERT rather than the value actually stored and prohibits the INSERT/UPDATE of values that would pass the table checks later.

You are however correct that this is an "application consistency" problem more than an SQLite problem and it is a lot of change for little actual benefit.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check constrain execution timing change?

Simon Slavin-3
On 31 Jan 2020, at 9:27pm, Keith Medcalf <[hidden email]> wrote:

> You are however correct that this is an "application consistency" problem more than an SQLite problem and it is a lot of change for little actual benefit.

How about this ?

A program (possibly part of the SQLite precompiled suite, possibly from a third party) goes through any SQLite database as an integrity checker.  One of its jobs is to check that column constraints are not violated.  This cannot possible be wrong.  A SQLite database with violated CHECK clauses is, by definition, corrupt.

However, because the checks are performed on the values input, not the values stored, some data in the database does violate a CHECK constraint.

Can this happen, given the behaviour Keith identified ?  If so, I would say that something is wrong.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check constrain execution timing change?

Graham Holden
Friday, January 31, 2020, 9:39:07 PM, Simon Slavin <[hidden email]> wrote:

> On 31 Jan 2020, at 9:27pm, Keith Medcalf <[hidden email]> wrote:

>> You are however correct that this is an "application consistency"
>> problem more than an SQLite problem and it is a lot of change for
>> little actual benefit.  

> How about this ?

> A program (possibly part of the SQLite precompiled suite, possibly
> from a third party) goes through any SQLite database as an integrity
> checker.  One of its jobs is to check that column constraints  
> are not violated.  This cannot possible be wrong.  A SQLite database
> with violated CHECK clauses is, by definition, corrupt.

> However, because the checks are performed on the values input, not
> the values stored, some data in the database does violate a CHECK
> constraint.  

> Can this happen, given the behaviour Keith identified ?  If so, I
> would say that something is wrong.

Probably not, at least not with the example he used.

In Keith's example (assuming I understand it correctly), you are
stopped from inserting/updating something that -- if you did the
insertion/update WITHOUT the checks -- would be stored in such a
way that it would later pass "pragma integrity_check" if the checks
were in place.

So, inserting '1' (a string) into a field with integer affinity, but
no constraints would be allowed, and result in 1 (an integer) being
stored.

If that column were to magically gain a "check (typeof(x) == 'integer')"
constraint, it would pass "pragma integrity_check" (because by now it
contains an integer, not the string that was originally inserted).

If you now repeated the original insert (of the string '1') with the
check constraint in place it will now REJECT the insertion, because
the type-of-the-thing-being-inserted doesn't meet the constraint (it's
a string).

So it doesn't allow you to create an inconsistent database (as defined
by check constraints), but does stop some ways of inserting/modifying
data that would have created valid data if the checks weren't there.

Graham


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check constrain execution timing change?

Richard Hipp-3
CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
INSERT INTO t1 VALUES('123');
PRAGMA integrity_check;


On 1/31/20, Graham Holden <[hidden email]> wrote:

> Friday, January 31, 2020, 9:39:07 PM, Simon Slavin <[hidden email]>
> wrote:
>
>> On 31 Jan 2020, at 9:27pm, Keith Medcalf <[hidden email]> wrote:
>
>>> You are however correct that this is an "application consistency"
>>> problem more than an SQLite problem and it is a lot of change for
>>> little actual benefit.
>
>> How about this ?
>
>> A program (possibly part of the SQLite precompiled suite, possibly
>> from a third party) goes through any SQLite database as an integrity
>> checker.  One of its jobs is to check that column constraints
>> are not violated.  This cannot possible be wrong.  A SQLite database
>> with violated CHECK clauses is, by definition, corrupt.
>
>> However, because the checks are performed on the values input, not
>> the values stored, some data in the database does violate a CHECK
>> constraint.
>
>> Can this happen, given the behaviour Keith identified ?  If so, I
>> would say that something is wrong.
>
> Probably not, at least not with the example he used.
>
> In Keith's example (assuming I understand it correctly), you are
> stopped from inserting/updating something that -- if you did the
> insertion/update WITHOUT the checks -- would be stored in such a
> way that it would later pass "pragma integrity_check" if the checks
> were in place.
>
> So, inserting '1' (a string) into a field with integer affinity, but
> no constraints would be allowed, and result in 1 (an integer) being
> stored.
>
> If that column were to magically gain a "check (typeof(x) == 'integer')"
> constraint, it would pass "pragma integrity_check" (because by now it
> contains an integer, not the string that was originally inserted).
>
> If you now repeated the original insert (of the string '1') with the
> check constraint in place it will now REJECT the insertion, because
> the type-of-the-thing-being-inserted doesn't meet the constraint (it's
> a string).
>
> So it doesn't allow you to create an inconsistent database (as defined
> by check constraints), but does stop some ways of inserting/modifying
> data that would have created valid data if the checks weren't there.
>
> Graham
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check constrain execution timing change?

Simon Slavin-3
On 31 Jan 2020, at 10:51pm, Richard Hipp <[hidden email]> wrote:

> CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
> INSERT INTO t1 VALUES('123');
> PRAGMA integrity_check;

Short and sweet.  That's what I was talking about.

How do you feel about that ?  Should a programmer be able to create a failure in integrity_check that easily ?  It's just two legit lines of SQL.  My gut feeling is that it points to something wrong with SQLite.  But there are lot of people here who know more about it than me.

Would be interesting to see an example which depends only on features of SQL, avoiding typeof().  Might make it more obvious whether it's a bug.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check constrain execution timing change?

Simon Slavin-3
On 1 Feb 2020, at 1:28am, Simon Slavin <[hidden email]> wrote:

> How do you feel about that ?

Sorry, I didn't read the rest of the list first.  I see it's already answered.  Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users