Re: Check constrain execution timing change? (Now a bug)

On Friday, 31 January, 2020 14:39, Simon Slavin <[email protected]> wrote:

>On 31 Jan 2020, at 9:27pm, Keith Medcalf <[email protected]> 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.

pragma integrity_check and pragma quick_check already do this.

>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.

Yes.  It is possible to "craft" a table and a check constraint such that the INSERT passes the constraint yet the integrity check fails:

sqlite> create table x(x text not null check (typeof(x) == 'integer'));
sqlite> insert into x values (1);
sqlite> select x, typeof(x) from x;
1|text
sqlite> pragma integrity_check;
CHECK constraint failed in x

That would elevate this to the status of a bug since it should be impossible to do this.

-- 
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users