A suboptimal solution - not using the index.
(1) By johnfound on 2020-10-27 07:03:35 [link] [source]
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.
(2) By Gunter Hick (gunter_hick) on 2020-10-27 09:07:38 in reply to 1 [link] [source]
This is expected and follows from the documented rules. See https://sqlite.org/datatype3.html section 4.2: "If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand." so the condition "a (text affinity) = b (integer affinity)" is equivalent to the condition "cast(a as integer) = b" for values of a that "look like" a number. This precludes using the unique index on a. BTW: The condition "a = +b" also works, as "+b" has NO affinity and thus gets TEXT affinity applied.
(3) By johnfound on 2020-10-27 09:27:32 in reply to 2 [link] [source]
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.
(4) By Richard Damon (RichardDamon) on 2020-10-27 11:06:00 in reply to 3 [link] [source]
But you need to remember that cast(1 as integer) == cast("1" as text), so just because one field is alway a string doesn't mean it can't match a numeric value.
(5) By Gunter Hick (gunter_hick) on 2020-10-27 11:51:08 in reply to 4 [source]
Only if you do not have a field named "1" in the scope of the SELECT statement. Single quotes for text literals, double quotes for "funny" field names.
(6) By Richard Damon (RichardDamon) on 2020-10-27 11:55:10 in reply to 5 [link] [source]
Right, make that mistake too much since I grew up in C.
(7) By David Raymond (dvdraymond) on 2020-10-27 12:51:09 in reply to 4 [link] [source]
Remember that casting can do weird things, so whatever you try, make sure it's doing what you want. sqlite> create table t1 (a text unique); sqlite> create table t2 (b integer); sqlite> insert into t1 values ('1'), ('1.0'), ('1xyz'); sqlite> insert into t2 values (1); sqlite> select * from t2 left outer join t1 on a = b; QUERY PLAN |--SCAN TABLE t2 `--SCAN TABLE t1 b|a 1|1 1|1.0 sqlite> select * from t2 left outer join t1 on a = cast(b as text); QUERY PLAN |--SCAN TABLE t2 `--SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?) b|a 1|1 sqlite> select * from t2 left outer join t1 on cast(a as int) = b; QUERY PLAN |--SCAN TABLE t2 `--SCAN TABLE t1 b|a 1|1 1|1.0 1|1xyz
(8) By Keith Medcalf (kmedcalf) on 2020-10-27 13:31:26 in reply to 7 [link] [source]
I believe you would need the following to get correct results, assuming that the description of the constraints is accurate.
select * from t2 left join t1 on a == cast(b as text) and typeof(b)=='text';