Affinity of expression indexes

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Affinity of expression indexes

Jens Alfke-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Affinity of expression indexes

Dan Kennedy-4
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 [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Affinity of expression indexes

Jens Alfke-2


> On Dec 13, 2018, at 5:13 AM, Dan Kennedy <[hidden email]> wrote:
>
> 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)

You’re right — a literal has no affinity according to section 3.2. That surprised me.

Thanks for the answers.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users