SQLite Forum

This is a problem with [type affinity][1].

[1]: https://www.sqlite.org/datatype3.html#type_affinity

The constant 1 has a type affinity of 'none'.
The subquery '(SELECT rowid FROM ...)' has a type affinity of 'integer'.
The t.dict1 has a type affinity of 'blob'.

You can drive an index that has type affinity 'blob' with a value that
has a type affinity of 'none', but not one that has a type affinity
of 'integer'.  The reason you cannot drive a blob index with an integer
value is that the index (or column t.dict1 in this case) might contain
a string value that looks like a integer:  '1' instead of 1.  But there
is no way to check for that efficiently using the index.

Two possible solutions:

  1.  Specify an INT type on the t.dict column.

  2.  Add a "+" sign before the "rowid" on the subquery, or before the
      subquery itself, to force the expression to use affinity 'none'.

So either:

~~~~~
CREATE TABLE dict(field1);
CREATE TABLE t(field1,dict1 INT REFERENCES dict(rowid),dict2, UNIQUE(dict1,dict2));
                    --------^^^
EXPLAIN QUERY PLAN
UPDATE t SET field1=1 WHERE dict1=(SELECT rowid FROM dict WHERE field1='z') AND dict2=2;
~~~~~

Or this:

~~~~~
CREATE TABLE dict(field1);
CREATE TABLE t(field1,dict1 INT REFERENCES dict(rowid),dict2, UNIQUE(dict1,dict2));
EXPLAIN QUERY PLAN
UPDATE t SET field1=1 WHERE dict1=(SELECT +rowid FROM dict WHERE field1='z') AND dict2=2;
                                      ----^
~~~~~
Or this:

~~~~~
CREATE TABLE dict(field1);
CREATE TABLE t(field1,dict1 INT REFERENCES dict(rowid),dict2, UNIQUE(dict1,dict2));
EXPLAIN QUERY PLAN
UPDATE t SET field1=1 WHERE dict1=+(SELECT rowid FROM dict WHERE field1='z') AND dict2=2;
                              ----^
~~~~~