SQLite Forum

Timeline
Login

2 forum posts by user johnfound

2020-10-27
09:27 Reply: A suboptimal solution - not using the index. (artifact: 7b19ab277a user: johnfound)

Actually, in my "real project" case from where I started the tests, the b field in most cases contains a number, but sometimes it contains a string.

And only in these rare cases the fields a and b can be equal.

07:03 Post: A suboptimal solution - not using the index. (artifact: 43b8646c01 user: johnfound)

Hello.

Today I discovered a small problem in one of my queries and trying to diagnose the problem, came to the following example:

create table t1(a text unique);
create table t2(b integer);

explain query plan
select * from 
  t2 left join t1 on a = b;

The result is:

SCAN TABLE t2
SCAN TABLE t1

Regardless of the existing index on t1.a; The fix is to explicitly cast the column b:

explain query plan
select * from 
  t2 left join t1 on a = cast(b as text);

Now the plan is as expected:

SCAN TABLE t2
SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)

So, IMHO, this behavior is pretty obscure. I have tried to search the documentation about type affinities, expressions, etc. But here the problem is in the query optimizer, not in the query result. Actually both variants work correctly, only the first is much slower.

Regards.