On July 15, 2019 12:01:00 a.m. EDT, "J. King" <jk...@jkingweb.ca> wrote: >On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd ><donald.sheph...@gmail.com> wrote: >>Somewhat bizarrely only "BLOB" affinity doesn't make it from the >>original >>table to the new table when using the "select" syntax to create the >new >>table. Even items with aliased affinities (VARTEXT, or something that >>defaults to NUMERIC) comes across as the base affinity but at least >>have an >>affinity. >> >>This is simple to reproduce: >> >>sqlite> .version >>SQLite 3.29.0 2019-07-10 17:32:03 >>fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6 >>zlib version 1.2.11 >>gcc-5.2.0 >>sqlite> create table x(a int, b text, c real, d blob, e vartext, f >>garbage); >>sqlite> pragma table_info(x); >>0|a|int|0||0 >>1|b|text|0||0 >>2|c|real|0||0 >>3|d|blob|0||0 >>4|e|vartext|0||0 >>5|f|garbage|0||0 >>sqlite> create table y as select * from x; >>sqlite> pragma table_info(y); >>0|a|INT|0||0 >>1|b|TEXT|0||0 >>2|c|REAL|0||0 >>3|d||0||0 >>4|e|TEXT|0||0 >>5|f|NUM|0||0 >>sqlite> select * from sqlite_master; >>table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f >>garbage) >>table|y|y|3|CREATE TABLE y( >> a INT, >> b TEXT, >> c REAL, >> d, >> e TEXT, >> f NUM >>) >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >Blobs have no affinity. The result you're seeing is correct, just >represented in a surprising way. >-- >J. King >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
I misspoke. Blobs have an affinity historically called NONE (which is distinct from no affinity, but that's not relevant here). Presumably SQLite represents the BLOB affinity as null as a means of saying "NONE", again for historical reasons. -- J. King _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
