Bug when creating a table via select?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug when creating a table via select?

Donald Shepherd
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug when creating a table via select?

J. King-3
On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd <[hidden email]> 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
>[hidden email]
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug when creating a table via select?

J. King-3
On July 15, 2019 12:01:00 a.m. EDT, "J. King" <[hidden email]> wrote:

>On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd
><[hidden email]> 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
>>[hidden email]
>>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
>[hidden email]
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug when creating a table via select?

Dominique Devienne
In reply to this post by J. King-3
On Mon, Jul 15, 2019 at 6:01 AM J. King <[hidden email]> wrote:

> On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd <
> [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users