oh god. So if I write "a" to an int column it turns into 97? or does it just return a text instead next time, which the first example seems to imply?
both are kind of horrible
The edge case I hit was when it did look like an integer, but had leading zeroes that were important:
sqlite> create table example(a int, b text);
sqlite> insert into example values ('00123', '00123');
sqlite> select * from example;
123|00123
sqlite> create table example(a int);
sqlite> insert into example values('00123');
sqlite> insert into example values('abcdef');
sqlite> select * from example;
+--------+
| a |
+--------+
| 123 |
| abcdef |
+--------+
Whereas in most other RDBMS implementations this is at best a warning:
mysql> create table example(test int);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into example values ('00123');
Query OK, 1 row affected (0.03 sec)
mysql> insert into example values ('abcdef');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from example;
+------+
| test |
+------+
| 123 |
| 0 |
+------+
3 rows in set (0.02 sec)
But your initial example only shows standard casting behavior in common with other RMDBS implementations.
Is there a type in e.g. Postgresql that would let you store '000123', give you numeric sorting (000123 > 1), and still return the leading zero characters? AFAIK that doesn't exist but I'm not that familiar with DB types.
And yep, it's all my fault for the column definition. Still an annoying surprise, esp since in this specific case, the surprise happened years after I screwed up.
"In SQLite, the datatype of a value is associated with the value itself, not with its container."
And on the topic of specifically inserting "a" into an INTEGER column:
"If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT"