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? (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
