Probably is documented so I'm going to read carefully, anyway if you do this
CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName
VARCHAR(255),Plate VARCHAR(255));
CREATE INDEX car_plates_plate on car_plates(Plate);
PRAGMA case_sensitive_like=ON;
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate
WHERE (Plate LIKE '*552*'); //OK
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate
WHERE (Plate LIKE '__552*'); //Error: no query solution
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate
WHERE (Plate LIKE '_*552*'); //Error: no query solution
explain query plan SELECT * FROM car_plates INDEXED BY car_plates_plate
WHERE (Plate LIKE '*_552*'); /OK
I think is a strange behavior, but I'm going to read documentation.
PS.I think another strange behavior is that if you use default COLLATION
for the column and the default car_sensitive_like you will get always
the slowest result in LIKE queries
Selea s.r.l.
Michele Pradella R&D
SELEA s.r.l.
Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.pradella at selea.com* <mailto:michele.pradella at selea.com>
*http://www.selea.com*
Il 13/02/2016 11.43, R Smith ha scritto:
>
>
> On 2016/02/12 6:38 PM, Michele Pradella wrote:
>> Already solved with UNION of SELECT
>>
>
> It isn't solved, it is circumvented by trial and error without
> understanding why.
>
> I'm sure that works ok for you in this case, but the point is if you
> do study those documents a bit more, you may grasp the reason too, and
> perhaps make much better/faster queries in future without spending
> hours on trial and error. Save yourself a lot of time, we think.
>
>
>
>> Il 12/02/2016 17.03, Richard Hipp ha scritto:
>>> ...//so that it is easier to understand. Perhaps a table that shows the
>>> various combinations of COLLATE and PRAGMA case_sensitive_like work
>>> together. I dunno. I'll take the action item to revisit the
>>> documentation and try to improve it.
>
> @Dr.Hipp: Would it be possible / difficult to perhaps improve the like
> algorithm and pragma case-insensitive_like to take 3 values, such that
> 0 = OFF, 1 = ON and 2 = AS PER COLUMN DEF?
>
> I realize the like function maybe doesn't know which column, nor is
> the like operation guaranteed to happen on a column. Maybe there is
> another way? Perhaps only from inside the query planner when making
> plan decisions? (Currently it compares the ci-ness during planning to
> decide on best index to use, if I'm not mistaken).
>
> What I have done is simply added a custom LIKE function that takes a
> second parameter for case sensitivity to avoid pragma-juggling - but
> by this, the query planner is none the wiser
>
> I find myself sometimes needing the above, though not often enough to
> warrant requesting the feature, but it seems now maybe others have the
> need too.
>
> Thanks,
> Ryan
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
