SQLite Forum: A suboptimal solution

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.