On 06/03/2013 10:30 AM, Dominique Devienne wrote:
On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca>
wrote:
Off topic, I'd love a way to request strong typing for a column (so that
attempts to store 'abc' into an int column would fail). You can emulate it
with a pair of before/update triggers (select raise(...) where
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect,
most of the times I've been bitten by type mismatches were probably either
due to this bug or (more likely) due to my not specifying any affinity at
all and then being surprised when 1 != '1'.
You don't have to use triggers, you can use a check constraint instead
(simpler, but also perhaps faster as well?).
If you do, you loose some of the implicit type conversions SQLite does,
based on type affinity, so the "1" no longer gets converted to 1.
I also would prefer a strong-typing mode though (as an opt-in pragma for
example), rather than adding a bunch of check constraints, and built-in
strong-typing would likely be faster. But Dr. Hipp prefers dynamic typing,
so dynamic typing it is :).
--DD
sqlite> create table t (id number);
sqlite> insert into t values (1);
sqlite> insert into t values ("1");
sqlite> insert into t values ("1x");
sqlite> select id, typeof(id) from t;
1|integer
1|integer
1x|text
sqlite> create table tt (id number check (typeof(id) = 'integer'));
sqlite> insert into tt values (1);
sqlite> insert into tt values ("1");
Error: constraint failed
sqlite> insert into tt values ("1x");
Error: constraint failed
sqlite> select id, typeof(id) from tt;
1|integer
// recent SQLite versions also now report the name of the constraint that
failed, if available.
sqlite> create table ttt (id number, constraint id_is_integer check
(typeof(id) = 'integer'));
sqlite> insert into ttt values (1);
sqlite> insert into ttt values ("1");
Error: constraint id_is_integer failed
I would argue that, if a column has type affinity, CHECK should work
with the value that would actually get stored, not the one that was
assigned. Otherwise you get situations where the non-checked table ended
up with an integer inside even though the checked table rejected the
change. This doesn't usually show up because most check constraints on
numeric fields use operators that try to convert strings to numbers
(though the way they do it varies).
Looking at this some more, I'd say there are several inconsistent
behaviors here. Most are strange type conversion behaviors that may or
may not be intentional, but two definitely look like bugs:
1. Predicates can behave differently for WHERE vs CHECK (see table t3
in the typescript below).
2. The type of x (as reported by typeof, but also visible in other
ways) can change depending on how x has been used before (see query
before table t4 in the typescript and the check constraint in table t8).
In any case, the following (see table t8) seems to enforce integer-ness
properly: check(x+0=x and cast(x as integer)=x)
It exploits several behaviors:
1. The expression "x+0" causes all numeric strings to be converted to
numbers (real or int)
2. Comparing the result against x discards garbage like '3bc' or 'abc'
that would otherwise slip through.
3. The numeric type of "x+0" changes the type of x to numeric, so that
values like '30e-1' typecast properly afterward.
4. Comparing the output of the cast against x discards reals values,
leaving only ints.
You have to evaluate #1 before #3, though, or the whole thing falls
apart... which is scary and brittle.
The VDBE changes from 13 to 20 instructions, with the following bit added:
4|Integer|1|2|0||00|
5|Integer|0|4|0||00|
6|Add|4|2|3||00|
7|Ne|2|11|3|collseq(BINARY)|64|
8|SCopy|2|3|0||00|
9|ToInt|3|0|0||00|
10|Eq|2|12|3|collseq(BINARY)|6b|
11|Halt|19|2|0||00|
Proper support for strong typing would be vastly more efficient; a
single OP_MustBeInt should work beautifully for integers, and similar
opcodes could be defined easily for the remaining types.
Meanwhile, the trigger is bulkier to code up and more expensive at
runtime, but much less likely to break in subtle ways when some future
release of sqlite3 starts reordering predicate expressions.
Ryan
---- begin typescript ---
sqlite> create table t1(x);
sqlite> insert into t1 values(3);
sqlite> insert into t1 values('3');
sqlite> insert into t1 values(3.0);
sqlite> insert into t1 values('3.0');
sqlite> insert into t1 values(30e-1);
sqlite> insert into t1 values('30e-1');
sqlite> insert into t1 values(3.3);
sqlite> insert into t1 values('3.3');
sqlite> insert into t1 values(3e-1);
sqlite> insert into t1 values('3e-1');
sqlite> insert into t1 values('3bc');
sqlite> insert into t1 values('abc');
sqlite> select x,typeof(x) from t1;
3|integer
3|text
3.0|real
3.0|text
3.0|real
30e-1|text
3.3|real
3.3|text
0.3|real
3e-1|text
3bc|text
abc|text
sqlite> -- so far so good
sqlite>
sqlite> create table t2(x integer);
sqlite> insert into t2 select x from t1;
sqlite> select x,typeof(x) from t2;
3|integer
3|integer
3|integer
3|integer
3|integer
3|integer
3.3|real
3.3|real
0.3|real
0.3|real
3bc|text
abc|text
sqlite> -- still makes sense
sqlite>
sqlite> select x,typeof(x) from t1 where x=3;
3|integer
3.0|real
3.0|real
sqlite> -- why does 3.0=3 but '3'!=3 ?
sqlite>
sqlite> create table t3(x integer check(x=3));
sqlite> insert into t3 values(3);
sqlite> insert into t3 values('3');
sqlite> insert into t3 values(3.0);
sqlite> insert into t3 values('3.0');
sqlite> insert into t3 values(30e-1);
sqlite> insert into t3 values('30e-1');
sqlite> insert into t3 values(3.3);
Error: constraint failed
sqlite> insert into t3 values('3.3');
Error: constraint failed
sqlite> insert into t3 values(3e-1);
Error: constraint failed
sqlite> insert into t3 values('3e-1');
Error: constraint failed
sqlite> insert into t3 values('3bc');
Error: constraint failed
sqlite> insert into t3 values('abc');
Error: constraint failed
sqlite> select x,typeof(x) from t3;
3|integer
3|integer
3|integer
3|integer
3|integer
3|integer
sqlite> -- select had false negatives, but check works fine
sqlite>
sqlite> select x,typeof(x),x+0 y from t1 where y=x;
3|integer|3
3|text|3
3.0|real|3.0
3.0|text|3.0
3.0|real|3.0
30e-1|text|3.0
3.3|real|3.3
3.3|text|3.3
0.3|real|0.3
3e-1|text|0.3
sqlite> -- makes sense
sqlite>
sqlite> select x,typeof(x) y from t1 where x+0=x and y='integer';
3|integer
3|text
3|integer
3|text
sqlite> -- wha??? is y='integer' or not???
sqlite>
sqlite> create table t4(x integer check(x+0=x));
sqlite> insert into t4 values(3);
sqlite> insert into t4 values('3');
sqlite> insert into t4 values(3.0);
sqlite> insert into t4 values('3.0');
sqlite> insert into t4 values(30e-1);
sqlite> insert into t4 values('30e-1');
sqlite> insert into t4 values(3.3);
sqlite> insert into t4 values('3.3');
sqlite> insert into t4 values(3e-1);
sqlite> insert into t4 values('3e-1');
sqlite> insert into t4 values('3bc');
Error: constraint failed
sqlite> insert into t4 values('abc');
Error: constraint failed
sqlite> select x,typeof(x) from t4;
3|integer
3|integer
3|integer
3|integer
3|integer
3|integer
3.3|real
3.3|real
0.3|real
0.3|real
sqlite> -- check behaves the same as select
sqlite>
sqlite> select x,typeof(x),typeof(x+0) y from t1 where y='integer';
3|integer|integer
3|text|integer
3bc|text|integer
abc|text|integer
sqlite> -- both false positives and negatives
sqlite>
sqlite> create table t5(x integer check(typeof(x+0)='integer'));
sqlite> insert into t5 values(3);
sqlite> insert into t5 values('3');
sqlite> insert into t5 values(3.0);
Error: constraint failed
sqlite> insert into t5 values('3.0');
Error: constraint failed
sqlite> insert into t5 values(30e-1);
Error: constraint failed
sqlite> insert into t5 values('30e-1');
Error: constraint failed
sqlite> insert into t5 values(3.3);
Error: constraint failed
sqlite> insert into t5 values('3.3');
Error: constraint failed
sqlite> insert into t5 values(3e-1);
Error: constraint failed
sqlite> insert into t5 values('3e-1');
Error: constraint failed
sqlite> insert into t5 values('3bc');
sqlite> insert into t5 values('abc');
sqlite> select x,typeof(x) from t5;
3|integer
3|integer
3bc|text
abc|text
sqlite> -- check behaves the same as the select
sqlite>
sqlite> select x,typeof(x) from t1 where x+0=x and typeof(x)='integer';
3|integer
3|text
sqlite> -- typeof(x) returns different answers for the same column
sqlite>
sqlite> create table t6(x integer check(x+0=x and typeof(x)='integer'));
sqlite> insert into t6 values(3);
sqlite> insert into t6 values('3');
sqlite> insert into t6 values(3.0);
Error: constraint failed
sqlite> insert into t6 values('3.0');
Error: constraint failed
sqlite> insert into t6 values(30e-1);
Error: constraint failed
sqlite> insert into t6 values('30e-1');
Error: constraint failed
sqlite> insert into t6 values(3.3);
Error: constraint failed
sqlite> insert into t6 values('3.3');
Error: constraint failed
sqlite> insert into t6 values(3e-1);
Error: constraint failed
sqlite> insert into t6 values('3e-1');
Error: constraint failed
sqlite> insert into t6 values('3bc');
Error: constraint failed
sqlite> insert into t6 values('abc');
Error: constraint failed
sqlite> select x,typeof(x) from t6;
3|integer
3|integer
sqlite> -- check behaves same as select
sqlite>
sqlite> select x,typeof(x),cast(x as integer) y from t1 where y=3;
3|integer|3
3|text|3
3.0|real|3
3.0|text|3
3.0|real|3
3.3|real|3
3.3|text|3
3e-1|text|3
3bc|text|3
sqlite> -- '30e-1' didn't make it, but '3e-1' and '3bc' did
sqlite>
sqlite> select x,typeof(x) from t1 where cast(x as integer)=x;
3|integer
3|text
3.0|real
3.0|text
3.0|real
sqlite> -- almost works, but rejects '30e-1'
sqlite>
sqlite> create table t7(x integer check(cast(x as integer)=x));
sqlite> insert into t7 values(3);
sqlite> insert into t7 values('3');
sqlite> insert into t7 values(3.0);
sqlite> insert into t7 values('3.0');
sqlite> insert into t7 values(30e-1);
sqlite> insert into t7 values('30e-1');
Error: constraint failed
sqlite> insert into t7 values(3.3);
Error: constraint failed
sqlite> insert into t7 values('3.3');
Error: constraint failed
sqlite> insert into t7 values(3e-1);
Error: constraint failed
sqlite> insert into t7 values('3e-1');
Error: constraint failed
sqlite> insert into t7 values('3bc');
Error: constraint failed
sqlite> insert into t7 values('abc');
Error: constraint failed
sqlite> select x,typeof(x) from t7;
3|integer
3|integer
3|integer
3|integer
3|integer
sqlite> -- almost: no false positives but rejects '30e-1'
sqlite>
sqlite> select x,typeof(x) from t1 where x+0=x and cast(x as integer)=x;
3|integer
3|text
3.0|real
3.0|text
3.0|real
30e-1|text
sqlite> -- heh. dirty hack, but it works.
sqlite> select x,typeof(x) from t1 where cast(x as integer)=x and x+0=x;
3|integer
3|text
3.0|real
3.0|text
3.0|real
sqlite> -- ... as long as nobody reorders the two operations
sqlite>
sqlite> create table t8(x integer check(x+0=x and cast(x as integer)=x));
sqlite> insert into t8 values(3);
sqlite> insert into t8 values('3');
sqlite> insert into t8 values(3.0);
sqlite> insert into t8 values('3.0');
sqlite> insert into t8 values(30e-1);
sqlite> insert into t8 values('30e-1');
sqlite> insert into t8 values(3.3);
Error: constraint failed
sqlite> insert into t8 values('3.3');
Error: constraint failed
sqlite> insert into t8 values(3e-1);
Error: constraint failed
sqlite> insert into t8 values('3e-1');
Error: constraint failed
sqlite> insert into t8 values('3bc');
Error: constraint failed
sqlite> insert into t8 values('abc');
Error: constraint failed
sqlite> select x,typeof(x) from t8;
3|integer
3|integer
3|integer
3|integer
3|integer
3|integer
sqlite> -- works as a check constraint, too.
sqlite>
sqlite> create table t9(x integer);
sqlite> create trigger t9t before insert on t9 begin
...> select raise(FAIL, 'integers only!') where
typeof(new.x)!='integer';
...> end;
sqlite> insert into t9 values(3);
sqlite> insert into t9 values('3');
sqlite> insert into t9 values(3.0);
sqlite> insert into t9 values('3.0');
sqlite> insert into t9 values(30e-1);
sqlite> insert into t9 values('30e-1');
sqlite> insert into t9 values(3.3);
Error: integers only!
sqlite> insert into t9 values('3.3');
Error: integers only!
sqlite> insert into t9 values(3e-1);
Error: integers only!
sqlite> insert into t9 values('3e-1');
Error: integers only!
sqlite> insert into t9 values('3bc');
Error: integers only!
sqlite> insert into t9 values('abc');
Error: integers only!
sqlite> select x,typeof(x) from t9;
3|integer
3|integer
3|integer
3|integer
3|integer
3|integer
sqlite> -- or, relax and let the trigger do it all for you
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
