Looks like when it goes and makes the table it doesn't give it an explicit "blob" type, as you would think from the phrase "When an expression is a simple reference to a column of a real table (not a VIEW or subquery) then the expression has the same affinity as the table column." It gives it no explicit type at all. However, according to...
http://www.sqlite.org/datatype3.html#determination_of_column_affinity "3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB." ...that lack of any explicit column type will results in an implicit blob affinity. So I guess it still winds up as blob in the end, but in a roundabout way. Though it doesn't show up in things like pragma table_info. SQLite version 3.23.0 2018-04-02 11:04:16 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo (i int, nu numeric, r real, t text, b blob, n); sqlite> create table bar as select i, nu, r, t, b, n from foo; sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|foo|foo|2|CREATE TABLE foo (i int, nu numeric, r real, t text, b blob, n) table|bar|bar|3|CREATE TABLE bar( i INT, nu NUM, r REAL, t TEXT, b, n ) sqlite> pragma table_info(foo); cid|name|type|notnull|dflt_value|pk 0|i|int|0||0 1|nu|numeric|0||0 2|r|real|0||0 3|t|text|0||0 4|b|blob|0||0 5|n||0||0 sqlite> pragma table_info(bar); cid|name|type|notnull|dflt_value|pk 0|i|INT|0||0 1|nu|NUM|0||0 2|r|REAL|0||0 3|t|TEXT|0||0 4|b||0||0 5|n||0||0 -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John McMahon Sent: Wednesday, April 04, 2018 3:54 PM To: SQLite Users Subject: [sqlite] Documentation Query/Correction In documentation for version 3.21.0: in datatypes3.html ... 3. Type Affinity ... Each column in an SQLite 3 database is assigned one of the following type affinities: TEXT NUMERIC INTEGER REAL BLOB (Historical note: The "BLOB" type affinity used to be called "NONE". But that term was easy to confuse with "no affinity" and so it was renamed.) and in lang_createtable.html ... CREATE TABLE ... AS SELECT Statements ... The declared type of each column is determined by the expression affinity of the corresponding expression in the result set of the SELECT statement, as follows: Expression Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT" REAL "REAL" NONE "" (empty string) In the Expression Affinity table above, should the Expression Affinity 'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical note:' as per section '3. Type Affinity' in datatypes.html above. NOTE: I have checked the current on line documents and they match the above. For consideration. Regards, John -- Regards John McMahon li...@jspect.fastmail.fm _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
