SQLite

When a column has type INT then that column is allowed to store text, but text should only be stored if it does not look like an integer. If the text looks like an integer then it should be stored as an integer.

Sometimes a table can be created that has an INT column that contains text that looks like an integer. The following code illustrates:

CREATE TABLE t1(a INT);
CREATE TABLE t2(x TEXT);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES('abc');
INSERT INTO t2 VALUES(2);
CREATE TABLE t3 AS SELECT * FROM t1 UNION SELECT * FROM t2;
SELECT * FROM t3
 WHERE typeof(a)='text'
   AND CAST(CAST(a AS int) AS text)==a;

The resulting table t3 contains two rows with text. The row that contains 'abc' is correct. But the row containing '2' really should contain a numeric 2.

This problem goes back to before version 3.5.1 (circa 2007). However, the problem became more acute with check-in [10d851353c2abead] (2014-01-02 - version 3.8.3) because that check-in optimized index creation to avoid "unnecessary" type checking. With version 3.8.3 and later, the following query gives a different answer with an index than it gives without the index:

CREATE TABLE t1(a INT);
CREATE TABLE t2(x TEXT);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES('abc');
INSERT INTO t2 VALUES(2);
CREATE TABLE t3 AS SELECT * FROM t1 UNION SELECT * FROM t2;
SELECT 'without-index', a FROM t3 WHERE a>9;
CREATE INDEX t3a ON t3(a);
SELECT 'with-index', a FROM t3 WHERE a>9;

Below is a simpler test case that further illustrates the problem. In this case, a table with a column of type TEXT ends up holding an INTEGER value, which is something that should never happen.

CREATE TABLE t1(a TEXT);
CREATE TABLE t2 AS SELECT * FROM t1 UNION ALL SELECT 123;
SELECT a, typeof(a) FROM t2;