On Wed, Mar 6, 2013 at 4:20 PM, Petite Abeille <petite.abei...@gmail.com> wrote:
> All good. Small sanity check though:
>
> select 2 = cast( '2.1' as integer );
>> 1
>
> Hmmm….
>
>
> select 2 = cast( '2abc' as integer );
>> 1
>
> Whaaaaat?!? Oh… "When casting a TEXT value to INTEGER, the longest possible
> prefix of the value that can be interpreted as an integer number is extracted
> from the TEXT value and the remainder ignored. "… ah… ok… weird…
Actually, while this is true here, it doesn't render the CHECK
constraint I mentioned earlier unsafe:
sqlite> CREATE TABLE toy1(a INTEGER);
sqlite> CREATE TABLE toy2(a INTEGER CHECK(typeof(a) = 'integer'));
sqlite> CREATE TABLE toy3(a INTEGER CHECK(a = CAST(a AS INTEGER)));
sqlite> INSERT INTO toy1 VALUES ('2a');
sqlite> INSERT INTO toy2 VALUES ('2a');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES ('2a');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES ('2.1');
Error: constraint failed
sqlite> INSERT INTO toy3 VALUES (2);
sqlite>
sqlite> INSERT INTO toy1 VALUES (2.1);
sqlite> INSERT INTO toy1 VALUES ('2a');
sqlite> INSERT INTO toy1 VALUES ('2.1');
sqlite> SELECT a, quote(a), typeof(a) FROM toy1;
2.1|2.1|real
2a|'2a'|text
2.1|2.1|real
sqlite>
The reason for this: the CAST didn't work the same as affinity type
conversions. The value of [a] fails to get converted to integer when
it's '2a', '2.1', or 2.1, causing the equality conversion to fail.
This works well for INTEGER, REAL, and NUMERIC (check for yourself).
For BLOB and TEXT CHECK(a = CAST(a AS ...)) has the same effect as
CHECK(typeof(a) = '...'). If you use "a NONE CHECK(a = CAST(a AS
NONE))" that seems to work as if one has used NUMERIC.
In conclusion, if you want to allow affine type conversions on INSERT,
but not disallow values that cannot be so converted, then
CHECK(my_column = CAST(my_column AS <TYPE>)) works. And if you want
to disallow values of incorrect types even when type conversion is
possible then use CHECK(typeof(my_column = <TYPE>). That's pretty
cool, IMO.
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
