On Sunday, 9 June, 2019 08:15, Manuel Rigger <rigger.man...@gmail.com> wrote:
>Hi Keith, >I don't understand completely. So we agree that +c0 has no affinity. >However, you argue that c0 has BLOB affinity, if I understood >correctly. >Why is that? I'd assume that it has TEXT affinity, since the table >column is declared as TEXT. Since applying TEXT affinity seems to be >lossless, I would expect it to be performed. Both TEXT and BLOB are bags-of-bytes and there is no difference between them OTHER THAN that TEXT implies that the bag-of-bytes is a valid UTF-8 encoded unicode string (with no embedded nulls and with a trailing null at the end). The x'...' is explicitly a blob type. Conversions are not applied to blobs. See https://sqlite.org/datatype3.html Section 3.4 -- BLOBs are always stored as BLOBs regardless of column affinity. DELETE FROM t1; INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500'); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; blob|blob|blob|blob|blob BLOBS are stored in database columns as BLOBS (that is, with no affinity conversion applied and having no affinity). However TEXT will have affinity applied. sqlite> create table x(b BLOB, i integer, r real, n numeric, z); sqlite> insert into x values (x'313233', x'313233', x'313233', x'313233', x'313233'); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 13 0 00 Start at 13 1 OpenWrite 0 2 0 5 00 root=2 iDb=0; x 2 NewRowid 0 1 0 00 r[1]=rowid 3 Blob 3 2 0 123 00 r[2]=123 (len=3) 4 Blob 3 3 0 123 00 r[3]=123 (len=3) 5 Blob 3 4 0 123 00 r[4]=123 (len=3) 6 Blob 3 5 0 123 00 r[5]=123 (len=3) 7 Blob 3 6 0 123 00 r[6]=123 (len=3) 8 Noop 0 0 0 00 BEGIN: GenCnstCks(0,1,1,0,0) 9 MakeRecord 2 5 7 ADEC 00 r[7]=mkrec(r[2..6]) 10 Noop 0 0 0 00 END: GenCnstCks(0) 11 Insert 0 7 1 x 39 intkey=r[1] data=r[7] 12 Halt 0 0 0 00 13 Transaction 0 1 13 0 01 usesStmtJournal=0 14 Goto 0 1 0 00 sqlite> insert into x values ('123', '123', '123', '123', '123'); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 13 0 00 Start at 13 1 OpenWrite 0 2 0 5 00 root=2 iDb=0; x 2 NewRowid 0 1 0 00 r[1]=rowid 3 String8 0 2 0 123 00 r[2]='123' 4 String8 0 3 0 123 00 r[3]='123' 5 String8 0 4 0 123 00 r[4]='123' 6 String8 0 5 0 123 00 r[5]='123' 7 String8 0 6 0 123 00 r[6]='123' 8 Noop 0 0 0 00 BEGIN: GenCnstCks(0,1,1,0,0) 9 MakeRecord 2 5 7 ADEC 00 r[7]=mkrec(r[2..6]) 10 Noop 0 0 0 00 END: GenCnstCks(0) 11 Insert 0 7 1 x 39 intkey=r[1] data=r[7] 12 Halt 0 0 0 00 13 Transaction 0 1 13 0 01 usesStmtJournal=0 14 Goto 0 1 0 00 sqlite> select typeof(b), typeof(i), typeof(r), typeof(n), typeof(z) from x; blob|blob|blob|blob|blob text|integer|real|integer|text --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
