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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users