Re: [sqlite] Bug when creating a table via select?
On Mon, Jul 15, 2019 at 6:01 AM J. King wrote: > On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd < > donald.sheph...@gmail.com> wrote: > >sqlite> create table x(a int, b text, c real, d blob, e vartext, > fgarbage); > >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 > > Blobs have no affinity. The result you're seeing is correct, just > represented in a surprising way. > Still. The fact garbage is mapped to NUM, and BLOB to nothing, is at the very least "surprising", despite being "correct" as per SQLite's "flexible typing" as DRH puts it. Definitely worthy of the "new" quirks.html page though IMHO. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug when creating a table via select?
On July 15, 2019 12:01:00 a.m. EDT, "J. King" wrote: >On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd > 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
Re: [sqlite] Bug when creating a table via select?
On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd 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
[sqlite] Bug when creating a table via select?
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
