SQLite

View Ticket
Login
Plaintext
2015-05-20
17:36
Fix the initialization logic in CREATE TABLE AS so that the correct affinities are applied to all values being inserted into the new table, even if the RHS is a compound SELECT. Fix for ticket [f2ad7de056ab1dc9200]. (check-in: 6a0cf3ce user: drh tags: trunk)
17:36 • Fixed ticket [f2ad7de0]: Corrupt table created by CREATE TABLE AS followed by compound SELECT plus 3 other changes (artifact: 81fc82f6 user: drh)
17:25
Add a test case to verify that CREATE TABLE AS does not store INT values in TEXT columns. Ticket [f2ad7de056ab1dc92]. (Closed-Leaf check-in: 0e45e8f1 user: drh tags: create-table-as-type-fix)
15:52 • Ticket [f2ad7de0] Corrupt table created by CREATE TABLE AS followed by compound SELECT status still Open with 6 other changes (artifact: 8126358c user: drh)
15:51
A proposed fix for the problem of CREATE TABLE AS generating a table that has INTEGER values in a TEXT column. Ticket [f2ad7de056ab1dc92]. (check-in: d5e2c1fc user: drh tags: create-table-as-type-fix)
14:04 • New ticket [f2ad7de0] Corrupt table created by CREATE TABLE AS followed by compound SELECT. (artifact: 86aa4e22 user: drh)
Ticket Hash: 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:

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;

drh added on 2015-05-20 15:52:59:

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;