Re: [sqlite] Affinity conversions and BLOB values
On Sunday, 9 June, 2019 08:15, Manuel Rigger 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 p1p2p3p4 p5 comment
- - -- -
0 Init 0 13000 Start at 13
1 OpenWrite 0 2 0 5 00 root=2 iDb=0; x
2 NewRowid 0 1 000 r[1]=rowid
3 Blob 3 2 0 12300 r[2]=123 (len=3)
4 Blob 3 3 0 12300 r[3]=123 (len=3)
5 Blob 3 4 0 12300 r[4]=123 (len=3)
6 Blob 3 5 0 12300 r[5]=123 (len=3)
7 Blob 3 6 0 12300 r[6]=123 (len=3)
8 Noop 0 0 000 BEGIN:
GenCnstCks(0,1,1,0,0)
9 MakeRecord 2 5 7 ADEC 00 r[7]=mkrec(r[2..6])
10Noop 0 0 000 END: GenCnstCks(0)
11Insert 0 7 1 x 39 intkey=r[1] data=r[7]
12Halt 0 0 000
13Transaction0 1 130 01 usesStmtJournal=0
14Goto 0 1 000
sqlite> insert into x values ('123', '123', '123', '123', '123');
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Init 0 13000 Start at 13
1 OpenWrite 0 2 0 5 00 root=2 iDb=0; x
2 NewRowid 0 1 000 r[1]=rowid
3 String80 2 0 12300 r[2]='123'
4 String80 3 0 12300 r[3]='123'
5 String80 4 0 12300 r[4]='123'
6 String80 5 0 12300 r[5]='123'
7 String80 6 0 12300 r[6]='123'
8 Noop 0 0 000 BEGIN:
GenCnstCks(0,1,1,0,0)
9 MakeRecord 2 5 7 ADEC 00 r[7]=mkrec(r[2..6])
10Noop 0 0 000 END: GenCnstCks(0)
11Insert 0 7 1 x 39 intkey=r[1] data=r[7]
12Halt 0 0 000
13Transaction0 1 130 01 usesStmtJournal=0
14Goto 0 1 000
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
Re: [sqlite] Affinity conversions and BLOB values
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. Best, Manuel On Sun, Jun 9, 2019 at 3:51 PM Keith Medcalf wrote: > > On Sunday, 9 June, 2019 05:20, Manuel Rigger > wrote: > > >CREATE TABLE t0(c0 TEXT); > >INSERT INTO t0(c0) VALUES (x'41'); -- 'A' if converted to TEXT > >SELECT (+ c0) IS c0 FROM t0; -- expected: 0, actual: 1 > > Note also that the only place where + is different from > by itself generally speaking is in an ORDER BY clause. This is because > although the "value" is unchanged, + is an expression whereas > is a reference to a column. Thus the optimizer can use the bare > reference to a column () during index selection but will not use an > expression (+). > > --- > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Affinity conversions and BLOB values
On Sunday, 9 June, 2019 05:20, Manuel Rigger wrote: >CREATE TABLE t0(c0 TEXT); >INSERT INTO t0(c0) VALUES (x'41'); -- 'A' if converted to TEXT >SELECT (+ c0) IS c0 FROM t0; -- expected: 0, actual: 1 Note also that the only place where + is different from by itself generally speaking is in an ORDER BY clause. This is because although the "value" is unchanged, + is an expression whereas is a reference to a column. Thus the optimizer can use the bare reference to a column () during index selection but will not use an expression (+). --- 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
Re: [sqlite] Affinity conversions and BLOB values
On Sunday, 9 June, 2019 05:20, Manuel Rigger wrote: >Consider the following example: >CREATE TABLE t0(c0 TEXT); >INSERT INTO t0(c0) VALUES (x'41'); -- 'A' if converted to TEXT >SELECT (+ c0) IS c0 FROM t0; -- expected: 0, actual: 1 >I would expect that a row with a value 0 is returned. I suspect that >this is a misunderstanding on my side, rather than a bug, which is >why I didn't directly open a bug report. Quite correct, this is not a bug. The +c0 is an expression having no affinity. A binary comparison of the expression +c0 (which means simply to change the type to an expression and the affinity to none) to the value of the column c0 which has affinity blob (but the same binary representation) results in an "equal" result. (IS is the same as == but with nulls comparing equal). Remember that "no affinity" and "affinity none" are the same as "blob affinity", the former cases having been renamed to the latter because "affinity none" was such a difficult concept. sqlite> select typeof(c0) from t0; blob sqlite> .eqp full sqlite> select (+c0) IS c0 from t0; QUERY PLAN `--SCAN TABLE t0 (~1048576 rows) addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 15000 Start at 15 1 OpenRead 0 2 0 1 00 root=2 iDb=0; t0 2 ColumnsUsed0 0 0 1 00 3 Explain3 0 0 SCAN TABLE t0 (~1048576 rows) 00 4 Noop 0 0 000 Begin WHERE-loop0: t0 5 Rewind 0 13000 6 Noop 0 0 000 Begin WHERE-core 7 Column 0 0 200 r[2]=t0.c0 8 Column 0 0 300 r[3]=t0.c0 9 Eq 3 1 2 (BINARY) e2 r[1] = (r[2]==r[3]) 10 ResultRow 1 1 000 output=r[1] 11 Noop 0 0 000 End WHERE-core 12Next 0 6 001 13Noop 0 0 000 End WHERE-loop0: t0 14Halt 0 0 000 15Transaction0 0 1 0 01 usesStmtJournal=0 16Goto 0 1 000 1 --- 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
[sqlite] Affinity conversions and BLOB values
Hi everyone, Consider the following example: CREATE TABLE t0(c0 TEXT); INSERT INTO t0(c0) VALUES (x'41'); -- 'A' if converted to TEXT SELECT (+ c0) IS c0 FROM t0; -- expected: 0, actual: 1 I would expect that a row with a value 0 is returned. I suspect that this is a misunderstanding on my side, rather than a bug, which is why I didn't directly open a bug report. Here is my reasoning: The storage class of c0 is TEXT, and since x'41' is a BLOB value, the value is not converted to TEXT, since BLOB values are never converted when storing them. (+ c0) has no type affinity, while c0 has a type affinity of TEXT. The documentation states that if "one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand." Thus, I would expect that TEXT affinity is applied to (+ c0). Now, it is not very clear to me whether an affinity conversion should be performed. I would expect that x'41' is converted to 'A', since a conversion back to the binary value would be possible (i.e., lossless and reversible). In that case, a TEXT value would be compared with a BLOB value, and the documentation states that "A TEXT value is less than a BLOB value", so this expression should yield 0. One explanation for the actual behavior could be that conversions of BLOB values are always considered lossy, which would be somehow counter-intuitive (and not documented). An alternative explanation that I could think of is that if one operand has TEXT affinity, and the other has no affinity, then TEXT affinity is applied to *both* the operands, which would imply that the documentation is incorrect for this case. This btw also applies to NUMERIC storage types and BLOB values. Looking forward to your thoughts/an explanation! Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
