No, it is working perfectly.
The index cannot be used because the equal (=) operator is collate BINARY.
If you want the column C3 to be not case sensitive you declare the table thusly:
.eqp on
create table t1
(
c1 text,
c2 text,
c3 text collate nocase
);
create index IndxT1C3 on T1(C3);
insert into t1 values
('aa','bb','2016-01-01'),
('ab','bb','2016-01-02'),
('ac','bc','2016-01-03');
analyze;
select C1 from T1 where C3='2016-01-03';
--EQP-- 0,0,0,SEARCH TABLE T1 USING INDEX IndxT1C3 (c3=?)
ac
Alternatively, you can specify that you want the "=" operator to use NOCASE
collation rather than BINARY collation.
.eqp on
create table t1
(
c1 text,
c2 text,
c3 date
);
create index IndxT1C3 on T1(C3 collate nocase);
insert into t1 values
('aa','bb','2016-01-01'),
('ab','bb','2016-01-02'),
('ac','bc','2016-01-03');
analyze;
select C1 from T1 where C3='2016-01-03' collate nocase;
--EQP-- 0,0,0,SEARCH TABLE T1 USING INDEX IndxT1C3 (c3=?)
ac
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Denis Burke
> Sent: Saturday, 26 March, 2016 16:12
> To: SQLite mailing list
> Subject: [sqlite] Index Selection
>
> I apologize if I am missing something elementary here. I cannot
> understand
> why this index is not helpful.
>
> CREATE TABLE [T1](
> [C1] TEXT,
> [C2] TEXT,
> [C3] DATE);
>
> CREATE INDEX [IndxT1C3]
> ON [T1](
> [C3] COLLATE [NOCASE]);
>
> insert into t1 values
> ('aa','bb','2016-01-01'),
> ('ab','bb','2016-01-02'),
> ('ac','bc','2016-01-03');
>
> analyze;
>
> ----------
> after doing this:
> explain query plan
> select C1 from T1
> where C3='2016-01-02'
>
> ----> 0, 0, 0,0 SCAN TABLE T1
>
> and
>
> explain query plan
> select C1 from T1 indexed by IndxT1C3
> where C3='2016-01-02'
>
> ----> no query solution
>
>
> Why can this index not be used?
>
> Thank you,
> Denis Burke
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
