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.)
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?
—Jens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
