On Sunday, 9 June, 2019 05:20, Manuel Rigger <rigger.man...@gmail.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
