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