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

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

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

Keith Medcalf

On Friday, 31 January, 2020 14:39, 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.

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
[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? (Now a bug)

Richard Hipp-3
On 1/31/20, Keith Medcalf <[hidden email]> 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
[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? (Now a bug)

Thomas Kurz
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 <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Saturday, February 1, 2020, 00:09:07
Subject: [sqlite] Check constrain execution timing change? (Now a bug)

On 1/31/20, Keith Medcalf <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
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? (Now a bug)

Richard Hipp-3
On 2/1/20, Thomas Kurz <[hidden email]> 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
[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? (Now a bug)

Thomas Kurz
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 <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Sunday, February 2, 2020, 00:50:34
Subject: [sqlite] Check constrain execution timing change? (Now a bug)

On 2/1/20, Thomas Kurz <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
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? (Now a bug)

Richard Hipp-3
On 2/2/20, Thomas Kurz <[hidden email]> 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
[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? (Now a bug)

Dominique Devienne
In reply to this post by Richard Hipp-3
On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp <[hidden email]> wrote:

> On 2/1/20, Thomas Kurz <[hidden email]> 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
[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? (Now a bug)

Richard Hipp-3
On 2/3/20, Dominique Devienne <[hidden email]> wrote:

> On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp <[hidden email]> wrote:
>
>> On 2/1/20, Thomas Kurz <[hidden email]> 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
[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? (Now a bug)

Dominique Devienne
On Mon, Feb 3, 2020 at 5:35 PM Richard Hipp <[hidden email]> wrote:

> On 2/3/20, Dominique Devienne <[hidden email]> wrote:
> > On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp <[hidden email]> 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
[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? (Now a bug)

James K. Lowden
In reply to this post by Dominique Devienne
On Mon, 3 Feb 2020 10:45:50 +0100
Dominique Devienne <[hidden email]> wrote:

> On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp <[hidden email]> wrote:
>
> > On 2/1/20, Thomas Kurz <[hidden email]> 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
[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? (Now a bug)

Thomas Kurz
In reply to this post by Richard Hipp-3
> 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
[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? (Now a bug)

Dominique Devienne
In reply to this post by James K. Lowden
On Mon, Feb 3, 2020 at 6:42 PM James K. Lowden <[hidden email]> 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
[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? (Now a bug)

Doug
> 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
[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? (Now a bug)

Dominique Devienne
On Tue, Feb 4, 2020 at 5:38 PM Doug <[hidden email]> 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
[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? (Now a bug)

Doug
> On Tue, Feb 4, 2020 at 5:38 PM Doug <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users