Affinity conversions and BLOB values

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

Affinity conversions and BLOB values

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

Re: Affinity conversions and BLOB values

Keith Medcalf

On Sunday, 9 June, 2019 05:20, Manuel Rigger <[hidden email]> 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         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     15    0                    00  Start at 15
1     OpenRead       0     2     0     1              00  root=2 iDb=0; t0
2     ColumnsUsed    0     0     0     1              00
3     Explain        3     0     0     SCAN TABLE t0 (~1048576 rows)  00
4     Noop           0     0     0                    00  Begin WHERE-loop0: t0
5     Rewind         0     13    0                    00
6       Noop           0     0     0                    00  Begin WHERE-core
7       Column         0     0     2                    00  r[2]=t0.c0
8       Column         0     0     3                    00  r[3]=t0.c0
9       Eq             3     1     2     (BINARY)       e2  r[1] = (r[2]==r[3])
10      ResultRow      1     1     0                    00  output=r[1]
11      Noop           0     0     0                    00  End WHERE-core
12    Next           0     6     0                    01
13    Noop           0     0     0                    00  End WHERE-loop0: t0
14    Halt           0     0     0                    00
15    Transaction    0     0     1     0              01  usesStmtJournal=0
16    Goto           0     1     0                    00
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Affinity conversions and BLOB values

Keith Medcalf
In reply to this post by Manuel Rigger

On Sunday, 9 June, 2019 05:20, Manuel Rigger <[hidden email]> 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 +<column> is different from <column> by itself generally speaking is in an ORDER BY clause.  This is because although the "value" is unchanged, +<column> is an expression whereas <column> is a reference to a column.  Thus the optimizer can use the bare reference to a column (<column>) during index selection but will not use an expression (+<column>).

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

Re: Affinity conversions and BLOB values

Manuel Rigger
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 <[hidden email]> wrote:

>
> On Sunday, 9 June, 2019 05:20, Manuel Rigger <[hidden email]>
> 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 +<column> is different from <column>
> by itself generally speaking is in an ORDER BY clause.  This is because
> although the "value" is unchanged, +<column> is an expression whereas
> <column> is a reference to a column.  Thus the optimizer can use the bare
> reference to a column (<column>) during index selection but will not use an
> expression (+<column>).
>
> ---
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: Affinity conversions and BLOB values

Keith Medcalf
On Sunday, 9 June, 2019 08:15, Manuel Rigger <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users