Re: [sqlite] CHECK constraints and type affinity

[Alexey Pechnikov]

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

[Paul Rigor (uci)]

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

[Igor Tandetnik]

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

[Pavel Ivanov]

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

[Black, Michael (IS)]

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 <paiva...@gmail.com>:
>> 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

[Black, Michael (IS)]

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) <michael.bla...@ngc.com>:
> 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

[Alexey Pechnikov]

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

[Alexey Pechnikov]

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

[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
 
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 <itandet...@mvps.org>:
> 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

[Pavel Ivanov]

> 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

[Alexey Pechnikov]

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

[Igor Tandetnik]

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

[Paul Rigor (uci)]

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

[Igor Tandetnik]

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

[Alexey Pechnikov]

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

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


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

[Alexey Pechnikov]

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

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


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

[Alexey Pechnikov]

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

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


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

[Alexey Pechnikov]

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

[Alexey Pechnikov]

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

[Igor Tandetnik]

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

[Black, Michael (IS)]

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

[Igor Tandetnik]

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

[Igor Tandetnik]

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