On 10 Jul 2014, at 11:47am, Clemens Ladisch <clem...@ladisch.de> wrote:
> This is indeed misleading. The result set actually has columns and > column names even when there are now rows in it, so this is guaranteed > to work even for empty result sets. However, all is not as you might expect: SQLite version 3.7.13 2012-07-17 17:46:21 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE TestA (a TEXT COLLATE NOCASE, b INTEGER, c REAL NOT NULL); sqlite> .schema CREATE TABLE TestA (a TEXT COLLATE NOCASE, b INTEGER, c REAL NOT NULL); sqlite> CREATE TABLE TestB AS SELECT *,35+5 FROM TestA; sqlite> .schema CREATE TABLE TestA (a TEXT COLLATE NOCASE, b INTEGER, c REAL NOT NULL); CREATE TABLE TestB(a TEXT,b INT,c REAL,"35+5"); sqlite> PRAGMA table_info(TestA); 0|a|TEXT|0||0 1|b|INTEGER|0||0 2|c|REAL|1||0 sqlite> PRAGMA table_info(TestB); 0|a|TEXT|0||0 1|b|INT|0||0 2|c|REAL|0||0 3|35+5||0||0 sqlite> INSERT INTO TestB (a,b,c) VALUES (8,8,8); sqlite> INSERT INTO TestB VALUES (9,9,9,9); sqlite> SELECT * FROM TestB; 8|8|8.0| 9|9|9.0|9 Note that although affinities are preserved when doing things this way, you lose COLLATE and constraint information, and that you can get some strange results if your "SELECT" is anything except "SELECT *". In other words, this is good for preserving values, but not good for preserving other elements of a table definition. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users