I agree. Here's verification with a version number:
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a integer primary key);
sqlite> insert into t values (1), (3), (5);
sqlite> select count(*) from t where a>0;
sqlite> select count(*) from t where (a,1)>(0,0);
sqlite> select * from t WHERE (a,0)>(0,0);
sqlite> SELECT (3,0) > (0,0);
sqlite> select a,typeof(a) FROM t;
sqlite> select 3,typeof(3);
On 2/13/18, Simon Slavin <[hidden email]> wrote:
> On 13 Feb 2018, at 5:32pm, x <[hidden email]> wrote:
>> Surely it should be 3 in both cases?????
> I agree. Here's verification with a version number:
It's used to change the expression from a column reference to something
that is computed, which means that SQLite cannot use an index for it
(unless you created an expression index on +a):
> Thanks. That's interesting. But then, why use it in this context?
> Why DRH wants to purposely bypass the index in this case?
> How is that relevant to testing tuple / row-values comparisons? --DD
In the original example, column a is the PRIMARY KEY. SQLite automatically makes an index for the PRIMARY KEY. So SQLIte might use that index if there's a WHERE clause on a. In the case of the ticket DRH wants to avoid this, since the bug shows up only when an index is not used.