SQLite index usage with different type affinity
<p>I have a simple table "tags" containing a key and a value column. The key is always a string, the value can be either string, int64 or a double value.</p>

<p>I do not have any real data at this point to test with. But I'm curious about the index usage of the value column. I've defined the column as TEXT type - is SQLite still able to use the index on the value column when an int64 or double type is bound to the statement?</p>

<p>Here is the test table:</p>

<pre><code>CREATE TABLE "tags" ("key" TEXT,"value" TEXT DEFAULT (null) );
INSERT INTO "tags" VALUES('test','test');
INSERT INTO "tags" VALUES('testint','1');
INSERT INTO "tags" VALUES('testdouble','2.0');
</code></pre>

<p>I see additional "Integer" and "Affinity" entries when analyzing the query via:</p>

<pre><code>explain SELECT value FROM tags where key = "testint" and value &gt;= 1
</code></pre>

<p>But I do not see any difference in index usage otherwise (e.g. idxgt is always used). But I'd rather like to have a definite answer rather than relying on wrong assumption with the small test data. </p>