Re: [sqlite] CHECK constraints and type affinity
FYI, for strict typing I prefer the ENFORCE public domain extension from
http://sqlite.mobigroup.ru/src/wiki?name=WClark
CREATE TABLE t (i enforce integer);
insert into t values('-5');
Error: t.i must be type 'integer'
This acts as (N INTEGER CHECK(TYPEOF(N) = 'integer') OR N IS NULL) but
error description is much helpful than "SQL error: constraint failed".
Did you try to debugging on database with a lot of constraints?..
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Agreed. I would hope that the application (at a higher level) is performing type checks on the input data before attempting any insertion... On Thu, Apr 29, 2010 at 8:28 AM, Igor Tandetnik wrote: > Alexey Pechnikov > wrote: > > Do you want produce SQL-injections security holes? When database > > engine can convert datatypes on demand we may quoting all values from > > web forms and all other external sources. > > Don't build SQL statements on the fly - use parameterized queries to > protect against SQL injections. This also eliminates most concerns about > type mismatches and such. When you bind a value to a parameter, you > explicitly specify the type of that value - you are unlikely to accidentally > pass a string when an int is needed, say (and if you do, it's probably a bug > in your program). > -- > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Paul Rigor Pre-doctoral BIT Fellow and Graduate Student Institute for Genomics and Bioinformatics Donald Bren School of Information and Computer Sciences University of California, Irvine http://www.ics.uci.edu/~prigor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Alexey Pechnikov wrote: > Do you want produce SQL-injections security holes? When database > engine can convert datatypes on demand we may quoting all values from > web forms and all other external sources. Don't build SQL statements on the fly - use parameterized queries to protect against SQL injections. This also eliminates most concerns about type mismatches and such. When you bind a value to a parameter, you explicitly specify the type of that value - you are unlikely to accidentally pass a string when an int is needed, say (and if you do, it's probably a bug in your program). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
I believe it isn't more complicated than check() constraints and hasn't such performance problem compared to check() constraints, not sure about your definition of "big and dirty schema". OTOH it has better error message... But anyway you have no other choice at this moment and there's no reason for arguing. Oh, no, you have another choice - take SQLite sources and change them so that check() constraints performed after affinity is applied. But I bet you won't choose that one. Pavel On Thu, Apr 29, 2010 at 10:07 AM, Alexey Pechnikov wrote: > This is just for fun, isn't it? It'is not more simple then cast(... as > ...) and has performance problem and produce big and dirty database > schema. > > 2010/4/29 Pavel Ivanov : >>> But how we can resolve this situation without >>> manual types casting? >> >> sqlite> create trigger tr before insert on t begin select raise(fail, >> 'Value must be positive') where typeof(new.n) != 'integer' or new.n < >> 0; end; > > -- > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
If you want only positive values:
sqlite> create table t3(n integer check(abs(round(n)) = n));
sqlite> insert into t3 values('-5');
Error: constraint failed
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
Sent: Thu 4/29/2010 9:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CHECK constraints and type affinity
This is just for fun, isn't it? It'is not more simple then cast(... as
...) and has performance problem and produce big and dirty database
schema.
2010/4/29 Pavel Ivanov :
>> But how we can resolve this situation without
>> manual types casting?
>
> sqlite> create trigger tr before insert on t begin select raise(fail,
> 'Value must be positive') where typeof(new.n) != 'integer' or new.n <
> 0; end;
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
I assume then that the constraint you're trying to satisfy is "don't insert a
non-integer string into an integer field", right?
Then do this:
sqlite> create table t2(n integer check(round(n) = n));
sqlite> insert into t2 values('-5');
sqlite> insert into t2 values('q');
Error: constraint failed
sqlite> insert into t2 values('5 with more stuff');
Error: constraint failed
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
Sent: Thu 4/29/2010 8:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CHECK constraints and type affinity
Do you want produce SQL-injections security holes? When database
engine can convert datatypes on demand we may quoting all values from
web forms and all other external sources. Note: variable binding is
impossible for dynamically created views, triggers, etc. And modern
languages can use string representation of variables in SQLite
bindings.
2010/4/29 Black, Michael (IS) :
> Get rid of the quotes in your values.
>
> sqlite> create table t2(n integer check(typeof(n)='integer'));
> sqlite> insert into t2 values('5');
> Error: constraint failed
> sqlite> insert into t2 values(5);
> sqlite> select n from t2;
> 5
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
This is just for fun, isn't it? It'is not more simple then cast(... as ...) and has performance problem and produce big and dirty database schema. 2010/4/29 Pavel Ivanov : >> But how we can resolve this situation without >> manual types casting? > > sqlite> create trigger tr before insert on t begin select raise(fail, > 'Value must be positive') where typeof(new.n) != 'integer' or new.n < > 0; end; -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Do you want produce SQL-injections security holes? When database
engine can convert datatypes on demand we may quoting all values from
web forms and all other external sources. Note: variable binding is
impossible for dynamically created views, triggers, etc. And modern
languages can use string representation of variables in SQLite
bindings.
2010/4/29 Black, Michael (IS) :
> Get rid of the quotes in your values.
>
> sqlite> create table t2(n integer check(typeof(n)='integer'));
> sqlite> insert into t2 values('5');
> Error: constraint failed
> sqlite> insert into t2 values(5);
> sqlite> select n from t2;
> 5
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Get rid of the quotes in your values.
sqlite> create table t2(n integer check(typeof(n)='integer'));
sqlite> insert into t2 values('5');
Error: constraint failed
sqlite> insert into t2 values(5);
sqlite> select n from t2;
5
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov
Sent: Thu 4/29/2010 8:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CHECK constraints and type affinity
Yes, I did understand. But how we can resolve this situation without
manual types casting? Now datatyping working correct only with manual
casting but it's hard to use:
sqlite> CREATE TABLE T2 (N INTEGER CHECK(TYPEOF(N) = 'integer'));
sqlite> INSERT INTO T2 VALUES('5');
Error: constraint failed
sqlite> INSERT INTO T2 VALUES(CAST('5' AS INTEGER));
2010/4/29 Igor Tandetnik :
> I wasn't proposing this as a solution. It was an experiment, an attempt to
> illustrate what was going on. I though I made it clear - I even show that
> negative values slip through this check ('q' would pass the original check,
> too). I can only assume you haven't actually read my message before jumping
> in to respond.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
> But how we can resolve this situation without
> manual types casting?
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (n integer);
sqlite> create trigger tr before insert on t begin select raise(fail,
'Value must be positive') where typeof(new.n) != 'integer' or new.n <
0; end;
sqlite> insert into t values (5);
sqlite> insert into t values (-5);
Error: Value must be positive
sqlite> insert into t values ('-5');
Error: Value must be positive
sqlite> insert into t values ('5');
sqlite> insert into t values ('q');
Error: Value must be positive
sqlite> select n, typeof(n) from t;
5|integer
5|integer
sqlite>
Pavel
On Thu, Apr 29, 2010 at 9:25 AM, Alexey Pechnikov
wrote:
> Yes, I did understand. But how we can resolve this situation without
> manual types casting? Now datatyping working correct only with manual
> casting but it's hard to use:
>
> sqlite> CREATE TABLE T2 (N INTEGER CHECK(TYPEOF(N) = 'integer'));
> sqlite> INSERT INTO T2 VALUES('5');
> Error: constraint failed
> sqlite> INSERT INTO T2 VALUES(CAST('5' AS INTEGER));
>
> 2010/4/29 Igor Tandetnik :
>> I wasn't proposing this as a solution. It was an experiment, an attempt to
>> illustrate what was going on. I though I made it clear - I even show that
>> negative values slip through this check ('q' would pass the original check,
>> too). I can only assume you haven't actually read my message before jumping
>> in to respond.
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Yes, I did understand. But how we can resolve this situation without
manual types casting? Now datatyping working correct only with manual
casting but it's hard to use:
sqlite> CREATE TABLE T2 (N INTEGER CHECK(TYPEOF(N) = 'integer'));
sqlite> INSERT INTO T2 VALUES('5');
Error: constraint failed
sqlite> INSERT INTO T2 VALUES(CAST('5' AS INTEGER));
2010/4/29 Igor Tandetnik :
> I wasn't proposing this as a solution. It was an experiment, an attempt to
> illustrate what was going on. I though I made it clear - I even show that
> negative values slip through this check ('q' would pass the original check,
> too). I can only assume you haven't actually read my message before jumping
> in to respond.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Paul Rigor (uci) wrote: > SQLite seems to be consistent if you actually provide the proper checks you > want to achieve. What's the deal again with prepending the check with "+"? The deal with "+" is my attempt to disprove the OP's suggestion that "the constraint is applied AFTER converting N to an integer." If that were the case, then unary plus would have had no effect. But it does have an effect, so the suggestion is not true. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Hey all,
SQLite seems to be consistent if you actually provide the proper checks you
want to achieve. What's the deal again with prepending the check with "+"?
sqlite> CREATE TABLE T2 (N INTEGER CHECK(N >= 0)
CHECK(TYPEOF(N)=='integer'));
sqlite> INSERT INTO T2 VALUES(-'q');
sqlite> INSERT INTO T2 VALUES('q');
Error: constraint failed
sqlite> INSERT INTO T2 VALUES('-q');
Error: constraint failed
sqlite> INSERT INTO T2 VALUES('-2');
Error: constraint failed
sqlite> INSERT INTO T2 VALUES('2');
Error: constraint failed
sqlite> INSERT INTO T2 VALUES(-'2');
Error: constraint failed
sqlite> INSERT INTO T2 VALUES(-2);
Error: constraint failed
sqlite> INSERT INTO T2 VALUES(2);
sqlite> SELECT N, TYPEOF(N) FROM T2;
0|integer
2|integer
#Suppress type coercion
sqlite> CREATE TABLE T3 (N INTEGER CHECK(+N >= 0)
CHECK(TYPEOF(N)=='integer'));
sqlite> INSERT INTO T3 VALUES('q');
Error: constraint failed
sqlite> INSERT INTO T3 VALUES('-q');
Error: constraint failed
sqlite> INSERT INTO T3 VALUES(-'q');
sqlite> INSERT INTO T3 VALUES(-'2');
Error: constraint failed
sqlite> INSERT INTO T3 VALUES(-'iii');
sqlite> INSERT INTO T3 VALUES(-2);
Error: constraint failed
sqlite> INSERT INTO T3 VALUES('-2');
Error: constraint failed
sqlite> INSERT INTO T3 VALUES('2');
Error: constraint failed
sqlite> SELECT N, TYPEOF(N) FROM T3;
0|integer
0|integer
On Thu, Apr 29, 2010 at 5:35 AM, Igor Tandetnik wrote:
> Alexey Pechnikov wrote:
> > It's bad solution:
> >
> > sqlite> CREATE TABLE T1 (N INTEGER CHECK(+N >= 0));
> > sqlite> INSERT INTO T1 VALUES('-5');
> > sqlite> INSERT INTO T1 VALUES('q');
> > sqlite> select N, typeof(N) from T1;
> > -5|integer
> > q|text
>
> I wasn't proposing this as a solution. It was an experiment, an attempt to
> illustrate what was going on. I though I made it clear - I even show that
> negative values slip through this check ('q' would pass the original check,
> too). I can only assume you haven't actually read my message before jumping
> in to respond.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Paul Rigor
Pre-doctoral BIT Fellow and Graduate Student
Institute for Genomics and Bioinformatics
Donald Bren School of Information and Computer Sciences
University of California, Irvine
http://www.ics.uci.edu/~prigor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Alexey Pechnikov wrote:
> It's bad solution:
>
> sqlite> CREATE TABLE T1 (N INTEGER CHECK(+N >= 0));
> sqlite> INSERT INTO T1 VALUES('-5');
> sqlite> INSERT INTO T1 VALUES('q');
> sqlite> select N, typeof(N) from T1;
> -5|integer
> q|text
I wasn't proposing this as a solution. It was an experiment, an attempt to
illustrate what was going on. I though I made it clear - I even show that
negative values slip through this check ('q' would pass the original check,
too). I can only assume you haven't actually read my message before jumping in
to respond.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Similar conversions problems we can see in triggers and constraints
too. See the first message in this topic. In TCL layer I did add the
"typing when possible" logic but how to do it here?
sqlite> INSERT INTO T2 VALUES('18');
In this expression value '18' may has more than single datatype in
different times - original type is 'text' and 'text'/'integer'/etc. in
dependence of the trigger or constraints checks and 'integer' after
inserting into table T2.
2010/4/29 Pavel Ivanov :
> Sorry, I cannot tell you anything about TCL - never used one. But I'm
> sure that it's some additional layer above SQLite. And if you find
> some inconsistencies there then it's inconsistencies in TCL, not in
> SQLite itself.
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Sorry, I cannot tell you anything about TCL - never used one. But I'm sure that it's some additional layer above SQLite. And if you find some inconsistencies there then it's inconsistencies in TCL, not in SQLite itself. Pavel On Thu, Apr 29, 2010 at 7:30 AM, Alexey Pechnikov wrote: > Link to mail-archive: > http://www.mail-archive.com/sqlite-users@sqlite.org/msg47382.html > > 2010/4/29 Pavel Ivanov : >> Sorry, I can't see your link from here and I don't understand what >> you're talking about. >> If you're trying to show me some email from this list either quote it >> or give a link from mail-archive.com. > > -- > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Link to mail-archive: http://www.mail-archive.com/sqlite-users@sqlite.org/msg47382.html 2010/4/29 Pavel Ivanov : > Sorry, I can't see your link from here and I don't understand what > you're talking about. > If you're trying to show me some email from this list either quote it > or give a link from mail-archive.com. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Sorry, I can't see your link from here and I don't understand what you're talking about. If you're trying to show me some email from this list either quote it or give a link from mail-archive.com. Pavel On Thu, Apr 29, 2010 at 7:12 AM, Alexey Pechnikov wrote: > See here: > http://readlist.com/lists/sqlite.org/sqlite-users/6/34050.html > > "works differently" from shell and from file - are you really think, it's > true? > May be you know different SQL standart which is dependence of the > _method_ of the starting SQL scripts?! > > 2010/4/29 Pavel Ivanov : >>> But the problem is internal logical inconsistency in datatyping. >> >> There's no internal inconsistency in SQLite. Everything is consistent >> and works using strict rules which are well explained in documentation >> and numerously discussed on this list. Just get used to the fact that >> SQLite works differently from other database engines. > > -- > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
See here: http://readlist.com/lists/sqlite.org/sqlite-users/6/34050.html "works differently" from shell and from file - are you really think, it's true? May be you know different SQL standart which is dependence of the _method_ of the starting SQL scripts?! 2010/4/29 Pavel Ivanov : >> But the problem is internal logical inconsistency in datatyping. > > There's no internal inconsistency in SQLite. Everything is consistent > and works using strict rules which are well explained in documentation > and numerously discussed on this list. Just get used to the fact that > SQLite works differently from other database engines. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
> But the problem is internal logical inconsistency in datatyping. There's no internal inconsistency in SQLite. Everything is consistent and works using strict rules which are well explained in documentation and numerously discussed on this list. Just get used to the fact that SQLite works differently from other database engines. Pavel On Thu, Apr 29, 2010 at 6:41 AM, Alexey Pechnikov wrote: > 2010/4/28 Dan Bishop : > ... >> Why the inconsistency? > > There are a lot of same problems. See > http://www.mail-archive.com/sqlite-users@sqlite.org/msg47832.html > sqlite> select 1='1'; > 0 > sqlite> create table test(a text); > sqlite> insert into test values (1); > sqlite> select * from test where a='1'; > 1 > sqlite> select * from test where a=1; > 1 > > So 1 can be equal to '1' and can be not. > > > My tests and dummy patch for tclsqlite > http://sqlite.mobigroup.ru/src/wiki?name=tclsqlite > But the problem is internal logical inconsistency in datatyping. As > example in PostgreSQL there is no this problem: > $ psql -h localhost --cluster 8.1/testing -U postgres template1 > > template1=> select 1='1'; > ?column? > -- > t > (1 row) > > -- > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
2010/4/28 Dan Bishop : ... > Why the inconsistency? There are a lot of same problems. See http://www.mail-archive.com/sqlite-users@sqlite.org/msg47832.html sqlite> select 1='1'; 0 sqlite> create table test(a text); sqlite> insert into test values (1); sqlite> select * from test where a='1'; 1 sqlite> select * from test where a=1; 1 So 1 can be equal to '1' and can be not. My tests and dummy patch for tclsqlite http://sqlite.mobigroup.ru/src/wiki?name=tclsqlite But the problem is internal logical inconsistency in datatyping. As example in PostgreSQL there is no this problem: $ psql -h localhost --cluster 8.1/testing -U postgres template1 template1=> select 1='1'; ?column? -- t (1 row) -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
It's bad solution:
sqlite> CREATE TABLE T1 (N INTEGER CHECK(+N >= 0));
sqlite> INSERT INTO T1 VALUES('-5');
sqlite> INSERT INTO T1 VALUES('q');
sqlite> select N, typeof(N) from T1;
-5|integer
q|text
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Black, Michael (IS) wrote: > Hmmm...when I get rid of the "+'" > CREATE TABLE T1 (N INTEGER CHECK(N >= 0)); > the constraint works > > Seems to me that "+N" is the same as "abs(N)". I'm not even sure of what the > intent of "+N" would be??? A unary plus in SQLite is a no-op, but it suppresses type coercion, and also may inhibit the use of an index (sometimes SQLite optimizer picks a suboptimal index for the query, and suppressing that index allows the optimizer to pick a different one). ColumnName is an expression with the affinity associated with the column; +ColumnName is an expression with the same value but no affinity. The difference is important in some cases. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Hmmm...when I get rid of the "+'"
CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
the constraint works
Seems to me that "+N" is the same as "abs(N)". I'm not even sure of what the
intent of "+N" would be???
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik
Sent: Wed 4/28/2010 7:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] CHECK constraints and type affinity
Dan Bishop wrote:
> If I write
>
> sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
>
> the constraint is applied AFTER converting N to an integer.
>
> sqlite> INSERT INTO T1 VALUES('42');
> sqlite> INSERT INTO T1 VALUES('-5');
> SQL error: constraint failed
A curious thing seems to happen. Inside CHECK constraint, a unique situation is
created that probably doesn't exist anywhere else (well, maybe within a
trigger; I'm too lazy to try and repro) - expression N has a value of type TEXT
(convertible to integer) but an INTEGER affinity. The comparison then appears
to coerce both operans to numbers first.
This example allows a negative value to slip past the check:
CREATE TABLE T1 (N INTEGER CHECK(+N >= 0));
INSERT INTO T1 VALUES('-5');
select N, typeof(N) from T1
-5 | integer
The unary plus suppresses type coercion, so '-5' is compared with 0, and any
string is considered greater than any number so the check succeeds. Then the
value is coerced to integer before storage. This proves that CHECK expression
is evaluated before converting the value for storage in all cases; it just so
happens that, in your example, the same conversion is performed when evaluating
the expression itself.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Dan Bishop wrote:
> If I write
>
> sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
>
> the constraint is applied AFTER converting N to an integer.
>
> sqlite> INSERT INTO T1 VALUES('42');
> sqlite> INSERT INTO T1 VALUES('-5');
> SQL error: constraint failed
A curious thing seems to happen. Inside CHECK constraint, a unique situation is
created that probably doesn't exist anywhere else (well, maybe within a
trigger; I'm too lazy to try and repro) - expression N has a value of type TEXT
(convertible to integer) but an INTEGER affinity. The comparison then appears
to coerce both operans to numbers first.
This example allows a negative value to slip past the check:
CREATE TABLE T1 (N INTEGER CHECK(+N >= 0));
INSERT INTO T1 VALUES('-5');
select N, typeof(N) from T1
-5 | integer
The unary plus suppresses type coercion, so '-5' is compared with 0, and any
string is considered greater than any number so the check succeeds. Then the
value is coerced to integer before storage. This proves that CHECK expression
is evaluated before converting the value for storage in all cases; it just so
happens that, in your example, the same conversion is performed when evaluating
the expression itself.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints and type affinity
Dan Bishop wrote:
> sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
>
> the constraint is applied AFTER converting N to an integer.
>
> sqlite> INSERT INTO T1 VALUES('42');
> sqlite> INSERT INTO T1 VALUES('-5');
> SQL error: constraint failed
How do you know? Both expressions below are true:
-5 < 0
'-5' < '0'
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CHECK constraints
> MS SQL 2000 wil alsol insert (5, NULL) Unless you additionally constrain the fields with "not null", but that's a violation the "not null" constraint, not the "x < y" constraint.
RE: [sqlite] CHECK constraints
Oracle 9.2 CREATE TABLE ex1( x INTEGER, y NUMBER(21,18), CHECK( xmailto:[EMAIL PROTECTED] Sent: 02 November 2005 23:31 To: sqlite-users@sqlite.org Subject: [sqlite] CHECK constraints In a CHECK constraint, if the expression is NULL (neither true nor false) does the constraint fail? Example: CREATE TABLE ex1( x INTEGER, y REAL, CHECK( x
Re: [sqlite] CHECK constraints
Hi, In DB2 7.x the insert statement is also ok. On 11/3/05, Nemanja Corlija <[EMAIL PROTECTED]> wrote: > > > Does the check constraint fail or not? Or do different > > database engines do different things? > In Firebird 1.5 it does fail. > > Though I agree with Darren's remarks, so not failing it seems to be > more flexible. > > -- > Nemanja Corlija <[EMAIL PROTECTED]> >
Re: [sqlite] CHECK constraints
> Does the check constraint fail or not? Or do different > database engines do different things? In Firebird 1.5 it does fail. Though I agree with Darren's remarks, so not failing it seems to be more flexible. -- Nemanja Corlija <[EMAIL PROTECTED]>
RE: [sqlite] CHECK constraints
Indeed. CREATE TABLE ex1( x INTEGER, y REAL, CHECK(xmailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 8:12 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] CHECK constraints *snip* At least in Oracle, no, your example insert works fine. If you want the insert to fail, you need to add a "not null" constraint as well. Nulls are always allowed unless you have a not null constraint. *snip* -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] CHECK constraints
On Wed, Nov 02, 2005 at 06:30:51PM -0500, [EMAIL PROTECTED] wrote: > In a CHECK constraint, if the expression is NULL (neither true > nor false) does the constraint fail? At least in Oracle, no, your example insert works fine. If you want the insert to fail, you need to add a "not null" constraint as well. Nulls are always allowed unless you have a not null constraint. I'm not certain, but I believe this is standard behavior in all SQL RDBMSs that support constraints, not just Oracle. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
RE: [sqlite] CHECK constraints
My information is that MySQL does not enforce check constraints - your
testing supports this.
Regards.
rayB
|-+>
| | "Marcus Welz"|
| | <[EMAIL PROTECTED]|
| | om> |
| ||
| | 03/11/2005 11:59 |
| | Please respond to|
| | sqlite-users |
| ||
|-+>
>--|
|
|
| To:
|
| cc:
|
| Subject: RE: [sqlite] CHECK constraints
|
>--|
To be honest, I didn't expect that either. I guess with NULL meaning
"absence of a value" the logic here is that since it cannot determine the
value of NULL, it let's it pass. Why that was chosen over failing the check
doesn't make sense to me.
MySQL 3.23.58 gives a syntax error on the table definition.
MySQL 4.0.24 inserts the record fine -- but it also inserts (5, 4). i.e. it
seems to ignore the check altogether.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 7:43 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] CHECK constraints
"Marcus Welz" <[EMAIL PROTECTED]> wrote:
> PostgreSQL 8.0 will happily insert (5, NULL).
>
Hmmm.. Not what I expected, nor what I implemented.
But the implementation is easily changed and there is
no point in trying to be "logical" about the behavior
of NULLs, I've learned. I will probably modify SQLite
to conform to PostgreSQL unless there is a serious
outcry against this, or unless someone learns that
PostgreSQL intends to change their behavior...
I'm eager to hear what other RDBMSes do.
--
D. Richard Hipp <[EMAIL PROTECTED]>
** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING *
*** Confidentiality and Privilege Notice ***
This e-mail is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone,
and you should destroy this message and kindly notify the sender by reply
e-mail. Confidentiality and legal privilege are not waived or lost by reason
of mistaken delivery to you.
Qantas Airways Limited
ABN 16 009 661 901
Visit Qantas online at http://qantas.com
Re: [sqlite] CHECK constraints
I'm not sure if this applies, but in my experience it is normal for a unique value constraint to be satisfied on columns with null values, as is a foreign key constraint, which is only evaluated on not-null values. Following that precedent, I would say that the CHECK constraint should pass if its expression results to null. If you're trying to enforce a certain kind of behaviour in a particular check constraint, you probably want to add some IS NULL expressions to explicitly declare the behaviour you want, to specify times when a null input would result in a check failure. -- Darren Duncan At 6:30 PM -0500 11/2/05, [EMAIL PROTECTED] wrote: In a CHECK constraint, if the expression is NULL (neither true nor false) does the constraint fail? Example: CREATE TABLE ex1( x INTEGER, y REAL, CHECK( x
RE: [sqlite] CHECK constraints
To be honest, I didn't expect that either. I guess with NULL meaning "absence of a value" the logic here is that since it cannot determine the value of NULL, it let's it pass. Why that was chosen over failing the check doesn't make sense to me. MySQL 3.23.58 gives a syntax error on the table definition. MySQL 4.0.24 inserts the record fine -- but it also inserts (5, 4). i.e. it seems to ignore the check altogether. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 7:43 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] CHECK constraints "Marcus Welz" <[EMAIL PROTECTED]> wrote: > PostgreSQL 8.0 will happily insert (5, NULL). > Hmmm.. Not what I expected, nor what I implemented. But the implementation is easily changed and there is no point in trying to be "logical" about the behavior of NULLs, I've learned. I will probably modify SQLite to conform to PostgreSQL unless there is a serious outcry against this, or unless someone learns that PostgreSQL intends to change their behavior... I'm eager to hear what other RDBMSes do. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] CHECK constraints
MS SQL 2000 wil alsol insert (5, NULL) From: "Marcus Welz" <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: Subject: RE: [sqlite] CHECK constraints Date: Wed, 2 Nov 2005 19:24:09 -0500 PostgreSQL 8.0 will happily insert (5, NULL). -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 6:31 PM To: sqlite-users@sqlite.org Subject: [sqlite] CHECK constraints In a CHECK constraint, if the expression is NULL (neither true nor false) does the constraint fail? Example: CREATE TABLE ex1( x INTEGER, y REAL, CHECK( x
Re: [sqlite] CHECK constraints
"Marcus Welz" <[EMAIL PROTECTED]> wrote: > PostgreSQL 8.0 will happily insert (5, NULL). > Hmmm.. Not what I expected, nor what I implemented. But the implementation is easily changed and there is no point in trying to be "logical" about the behavior of NULLs, I've learned. I will probably modify SQLite to conform to PostgreSQL unless there is a serious outcry against this, or unless someone learns that PostgreSQL intends to change their behavior... I'm eager to hear what other RDBMSes do. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] CHECK constraints
PostgreSQL 8.0 will happily insert (5, NULL). -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 6:31 PM To: sqlite-users@sqlite.org Subject: [sqlite] CHECK constraints In a CHECK constraint, if the expression is NULL (neither true nor false) does the constraint fail? Example: CREATE TABLE ex1( x INTEGER, y REAL, CHECK( x
