Re: [sqlite] Check constrain execution timing change? (Now a bug)
> On Tue, Feb 4, 2020 at 5:38 PM Doug wrote:
> > > You're twisting my point here. I obviously want the reverse,
> > > I want the database types to "drive" the binding done. 1-to-1.
> > > Because even if binding a different type would work, via
> SQLite's
> > > own implicit conversion, I don't want that, because it's
> hiding a
> > > bug in the code most likely instead. --DD
>
> > Is the code inadvertently putting quotes (') around in integer
> value [...]?
>
> I'm talking about "real" binding here:
> https://www.sqlite.org/c3ref/bind_blob.html
> In C/C++, you could mess up your col indexes when binding, or bind
> incorrectly for some other reason, and "strong static typing" is more
> likely to find those, via SQL failures, than SQLite's default
> flexible-typing, that accepts any value in any typed column,
> unless you have these explicit CHECK+typeof constraints. --DD
So you are talking about a bug in your code where you inadvertently called:
sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
instead of
sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
and you want SQLite to tell you about it.
I have a hard time seeing how you could make that kind of coding error, given
the different parameters and types in the calls.
Doug
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On Tue, Feb 4, 2020 at 5:38 PM Doug wrote:
> > You're twisting my point here. I obviously want the reverse,
> > I want the database types to "drive" the binding done. 1-to-1.
> > Because even if binding a different type would work, via SQLite's
> > own implicit conversion, I don't want that, because it's hiding a
> > bug in the code most likely instead. --DD
> Is the code inadvertently putting quotes (') around in integer value [...]?
I'm talking about "real" binding here:
https://www.sqlite.org/c3ref/bind_blob.html
In C/C++, you could mess up your col indexes when binding, or bind incorrectly
for some other reason, and "strong static typing" is more likely to
find those, via
SQL failures, than SQLite's default flexible-typing, that accepts any
value in any typed column,
unless you have these explicit CHECK+typeof constraints. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
> You're twisting my point here. I obviously want the reverse,
> I want the database types to "drive" the binding done. 1-to-1.
> Because even if binding a different type would work, via SQLite's
> own implicit conversion, I don't want that, because it's hiding a
> bug in the code most likely instead. --DD
WRT the code that the bug is in: I'm assuming that your code is creating text
SQL statements which it passes to some process, right? The "binding" you
mention is confusing me. You can't be using query.addBindValue() because the
type is coerced to match the column type.
So, if you are generating text SQL statements: Is the code inadvertently
putting quotes (') around in integer value or is the user entering a string and
your code is taking that input and slapping it into a SQL INSERT statement?
Please explain your possible code "bug".
Doug
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On Mon, Feb 3, 2020 at 6:42 PM James K. Lowden wrote: > Do you want to force applications to "pre-convert" values the DBMS can > convert implicitly? Yes, that's exactly what I want James. I want the enforce the bind-value type to be an exact match for the column value type. I could before, via CHECK+typeof(), so that's still breaking BC, even though I accept that now that I was explained things. > Do you want binding choices in the application to > drive the datatype in the database, or do you want the database to > enforce types? You're twisting my point here. I obviously want the reverse, I want the database types to "drive" the binding done. 1-to-1. Because even if binding a different type would work, via SQLite's own implicit conversion, I don't want that, because it's hiding a bug in the code most likely instead. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
> You say that you want to prevent the use of the string literal '123' > for inserting into the integer field x. That will no longer be > possible in SQLite beginning with 3.32.0 (assuming the change > currently on trunk goes through.) > But, why do you want to do that? You are right. I apologize for my first excitement. The new behavior is correct and consistent to other RDBMs. Sometimes one misses the forest for the trees :-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On Mon, 3 Feb 2020 10:45:50 +0100
Dominique Devienne wrote:
> On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp wrote:
>
> > On 2/1/20, Thomas Kurz wrote:
> > > Does this mean there will be no possibility to prevent inserting
> > > a string into an integer column anymore?
> > >
> > > create table x (x integer check (typeof(x) == 'integer'));
> > > insert into x values ('1');
> > >
> > > --> will pass in future versions???
> >
> > I think that is what it means. yes.
>
> Wow... I haven't caught up on this thread, but that's really really
> bad IMHO
...
> the fact we can no longer do that would be a real shame. I wonder
> where this is coming from... --DD
It's a good thing, really! The rule would be that the provided value is
converted to the column's type before inserting. If it can't be
converted, it's still an error. If it can, great. I think you'll find
that's the behavior of most SQL DBMS implementations.
After all, of what significance is the type of the provided argument?
Do you want to force applications to "pre-convert" values the DBMS can
convert implicitly? Do you want binding choices in the application to
drive the datatype in the database, or do you want the database to
enforce types?
--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On Mon, Feb 3, 2020 at 5:35 PM Richard Hipp wrote:
> On 2/3/20, Dominique Devienne wrote:
> > On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp wrote:
>
> This is the SQL:
>
> CREATE TABLE t1(x INT CHECK(typeof(x)=='integer'));
> INSERT INTO t1(x) VALUES('123');
>
> You say that you want to prevent the use of the string literal '123'
> for inserting into the integer field x. That will no longer be
> possible in SQLite beginning with 3.32.0 (assuming the change
> currently on trunk goes through.)
>
> But, why do you want to do that? How do you prevent the use of a
> string literal to initialize an integer field in MySQL, PosgreSQL, SQL
> Server, and Oracle - all of which accept and run the SQL above
> (without the CHECK constraint) with no errors?
Right. Implicit conversion also happen in these other DBs (I just
checked Oracle,
but I trust you're way more qualified to assert that me).
> If your goal is to prevent an actual string from being stored in the
> "x" column, then the legacy CHECK constraint still works for that.
> The following insert still fails:
>
>INSERT INTO t1(x) VALUES('xyzzy');
Right again. It fails with "ORA-01722: invalid number" on Oracle.
(no need for a CHECK constraint of course)
> But, you will no longer be allowed to prevent the type coercion that
> forces the '123' value into an integer 123, I think. At least, I do
> not see a way to do that on trunk right now.
OK. I was more thinking of the '123' staying as text-typed in the DB.
But if it is coerced into the column's type (well, "affinity", not type per se),
then whether the value is bound as a string or a integer should be immaterial.
I still think my code shouldn't be binding values of a type different
than the column's,
and would still greatly prefer "strong *static* typing", which I
emulated with CHECK typeof(),
since it smells like a bug in the code IMHO, but as long as the stored
value is "OK", sure
that makes little differences in the end.
So now that I understand the better, so be it I guess.
I'm sure you have a good reason to make that change, despite the
surprising break in BC for SQLite.
Thanks for taking the time to spell it out for me. --DD
PS: I still wish for a pragma for strong static typing (no need for
CHECK typeof()),
and now also wish for that to happen even before implicit
conversions. But I've long
accepted this is unlikely to ever happen :(
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On 2/3/20, Dominique Devienne wrote:
> On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp wrote:
>
>> On 2/1/20, Thomas Kurz wrote:
>> >
>> > create table x (x integer check (typeof(x) == 'integer'));
>> > insert into x values ('1');
>> >
>> > --> will pass in future versions???
>>
>> I think that is what it means. yes.
>
> Wow... I haven't caught up on this thread, but that's really really bad
> IMHO,
> and would consider that a serious regression. I've been enforcing
> "strong-typing",
> (or "inflexible-typing" if you prefer Richard) for many schemas, and
> the fact we can
> no longer do that would be a real shame. I wonder where this is coming
> from... --DD
This is the SQL:
CREATE TABLE t1(x INT CHECK(typeof(x)=='integer'));
INSERT INTO t1(x) VALUES('123');
You say that you want to prevent the use of the string literal '123'
for inserting into the integer field x. That will no longer be
possible in SQLite beginning with 3.32.0 (assuming the change
currently on trunk goes through.)
But, why do you want to do that? How do you prevent the use of a
string literal to initialize an integer field in MySQL, PosgreSQL, SQL
Server, and Oracle - all of which accept and run the SQL above
(without the CHECK constraint) with no errors?
If your goal is to prevent an actual string from being stored in the
"x" column, then the legacy CHECK constraint still works for that.
The following insert still fails:
INSERT INTO t1(x) VALUES('xyzzy');
But, you will no longer be allowed to prevent the type coercion that
forces the '123' value into an integer 123, I think. At least, I do
not see a way to do that on trunk right now.
I have put a "Pre-release Snapshot" of the latest code on the Download
page to try to make it easier for people to try out this new change.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp wrote:
> On 2/1/20, Thomas Kurz wrote:
> > Does this mean there will be no possibility to prevent inserting a string
> > into an integer column anymore?
> >
> > create table x (x integer check (typeof(x) == 'integer'));
> > insert into x values ('1');
> >
> > --> will pass in future versions???
>
> I think that is what it means. yes.
Wow... I haven't caught up on this thread, but that's really really bad IMHO,
and would consider that a serious regression. I've been enforcing
"strong-typing",
(or "inflexible-typing" if you prefer Richard) for many schemas, and
the fact we can
no longer do that would be a real shame. I wonder where this is coming
from... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On 2/2/20, Thomas Kurz wrote:
> And are there any consequences for something like
>
>> create table x (x text check (typeof(x) == 'text'));
>> insert into x values ('1');
>
Not that I know of.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
And are there any consequences for something like
> create table x (x text check (typeof(x) == 'text'));
> insert into x values ('1');
?
- Original Message -
From: Richard Hipp
To: SQLite mailing list
Sent: Sunday, February 2, 2020, 00:50:34
Subject: [sqlite] Check constrain execution timing change? (Now a bug)
On 2/1/20, Thomas Kurz wrote:
> Does this mean there will be no possibility to prevent inserting a string
> into an integer column anymore?
> create table x (x integer check (typeof(x) == 'integer'));
> insert into x values ('1');
> --> will pass in future versions???
I think that is what it means. yes.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On 2/1/20, Thomas Kurz wrote:
> Does this mean there will be no possibility to prevent inserting a string
> into an integer column anymore?
>
> create table x (x integer check (typeof(x) == 'integer'));
> insert into x values ('1');
>
> --> will pass in future versions???
I think that is what it means. yes.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
Does this mean there will be no possibility to prevent inserting a string into
an integer column anymore?
create table x (x integer check (typeof(x) == 'integer'));
insert into x values ('1');
--> will pass in future versions???
- Original Message -
From: Richard Hipp
To: SQLite mailing list
Sent: Saturday, February 1, 2020, 00:09:07
Subject: [sqlite] Check constrain execution timing change? (Now a bug)
On 1/31/20, Keith Medcalf wrote:
> That would elevate this to the status of a bug since it should be impossible
> to do this.
It is also not something that is fixable, so the solution will likely
be to simply document it.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On 1 Feb 2020, at 1:28am, Simon Slavin 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On 31 Jan 2020, at 10:51pm, Richard Hipp 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On 1/31/20, Keith Medcalf wrote: > > That would elevate this to the status of a bug since it should be impossible > to do this. > It is also not something that is fixable, so the solution will likely be to simply document it. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On Friday, 31 January, 2020 14:39, Simon Slavin wrote: >On 31 Jan 2020, at 9:27pm, Keith Medcalf 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
INSERT INTO t1 VALUES('123');
PRAGMA integrity_check;
On 1/31/20, Graham Holden wrote:
> Friday, January 31, 2020, 9:39:07 PM, Simon Slavin
> wrote:
>
>> On 31 Jan 2020, at 9:27pm, Keith Medcalf 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
Friday, January 31, 2020, 9:39:07 PM, Simon Slavin wrote: > On 31 Jan 2020, at 9:27pm, Keith Medcalf 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On 31 Jan 2020, at 9:27pm, Keith Medcalf 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On Friday, 31 January, 2020 13:58, Richard Hipp wrote:
>On 1/31/20, Keith Medcalf 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
>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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On 1/31/20, Keith Medcalf 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Check constrain execution timing change?
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
