On 12/13/2018 07:41 AM, Jens Alfke wrote:
Consider CREATE INDEX foo_idx ON tbl (myfunction(a)); where
‘myfunction’ is a deterministic C function I’ve registered with the
SQLite connection (and ‘a’ is a column of ‘tbl’ of course.)
SQLite has no idea what data type(s) ‘myfunction’ returns, and it
might well return different data types for different inputs. So
unlike a column index, there’s no natural type affinity.
Question: In such an index, does SQLite assume no affinity and just
compare the different types using the rules in section 4.1 of
“Datatypes In SQLite Version 3”? (I.e. numbers are compared as
numbers and sort before any strings, etc.)
Yes. Exactly.
In that case, I’m confused how this interacts with ‘applying
affinity’ in a query as described in section 4.2. For example, let’s
say that ‘myfunction’ always returns a number. In that case,
‘foo_idx’ will be sorted numerically. But if I do a query like SELECT
* FROM tbl WHERE myfunction(a) > ‘dog’; then the rules say that text
affinity will be applied to the function call since the other side of
the comparison is a string. In that case, the numbers it returns will
be interpreted as strings. That leads to an entirely different
sorting order, so the index can’t be used. But how does SQLite know
that?
Each column of each index has an associated affinity. As does each
comparison expression in an SQL statement. An index can only used with a
comparison if the affinities match.
In this case, the indexed expression has "no affinity", so values are
stored in the index as is. The expression 'dog' also has no affinity (I
think you're mistaken as to the rules - but please correct me if that's
not the case), so the comparison also has no affinity (equivalent to
BLOB affinity) and no type coercion is applied to the operands. Hence
the index can be used for queries like:
SELECT * FROM tbl WHERE myfunction(a) > ‘dog’;
However, if 'dog' were replaced by an expression that does have an affinity:
SELECT * FROM tbl WHERE myfunction(a) > CAST(‘dog’ AS TEXT);
then the index could not be used, as the affinity of the comparison
would be TEXT, not "no affinity" or BLOB.
Dan.
—Jens _______________________________________________ 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
