SQLite
View Ticket
Not logged in
Ticket UUID: f2ad7de056ab1dc9200d5c364952ac29d7fb035f
Title: Corrupt table created by CREATE TABLE AS followed by compound SELECT
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2015-05-20 17:36:43
Version Found In: 3.8.10.1
User Comments:
drh added on 2015-05-20 14:04:47: (text/x-fossil-wiki)
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:

<blockquote><verbatim>
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;
</verbatim></blockquote>

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:

<blockquote><verbatim>
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;
</verbatim></blockquote>

drh added on 2015-05-20 15:52:59: (text/x-fossil-wiki)
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.

<blockquote><verbatim>
CREATE TABLE t1(a TEXT);
CREATE TABLE t2 AS SELECT * FROM t1 UNION ALL SELECT 123;
SELECT a, typeof(a) FROM t2;
</verbatim></blockquote>