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
