Again, I think this is a problem with applying affinity when the index is
created. The result of applying real affinity to the string '+/' should
probably be the string '+/' not the real value 0. On the gripping hand, '+/'
looks like a number with "crud" at the end of the string. I believe this is
documented somewhere for the application of numeric affinity to a string that
looks like a number but with crud at the end.
sqlite> select cast('27 dollars' as real);
27.0
sqlite> select cast('+/' as real);
0.0
Although c0 is stored in the table as TEXT, the application of real affinity to
the string '+/' when building the index results in a real(0).
The COLLATE NOCASE simply allows the LIKE operator to use the index (since
using an index for LIKE in the default case insensitive mode requires an index
with COLLATE NOCASE). Since the index is incorrect (the index key is real(0)
not text('+/') the index lookup fails.
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Manuel Rigger
>Sent: Wednesday, 1 May, 2019 15:31
>To: SQLite mailing list
>Subject: Re: [sqlite] COLLATE NOCASE index on REAL column
>malfunctions
>
>I'm very sorry, after finding the issue using the latest stable Linux
>version, I accidentally used an outdated version (3.24.0) to produce
>a
>minimal failing case. Here is a reduced example that triggers the bug
>on
>the latest stable [1] and snapshot [2] versions:
>
>CREATE TABLE test (c0 REAL);
>CREATE INDEX index_0 ON test(c0 COLLATE NOCASE);
>INSERT INTO test(c0) VALUES ('+/');
>SELECT * FROM test WHERE (c0 LIKE '+/');
>
>Best,
>Manuel
>
>
>[1] 3.28.0 2019-04-16 19:49:53
>884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
>[2] 3.29.0 2019-04-27 20:30:19
>50fe48458942fa7a6bcc76316c6321f95b23dc34f2f8e0a483826483b2fb16f6
>
>On Wed, May 1, 2019 at 9:55 PM Warren Young <war...@etr-usa.com>
>wrote:
>
>> On May 1, 2019, at 1:18 PM, Richard Hipp <d...@sqlite.org> wrote:
>> >
>> > I am unable to reproduce the observed behavior.
>>
>> Nor I, on 3.28.0 release with our custom build.
>>
>> Thank you for providing a simple test case, Manuel: it helps
>greatly!
>>
>> > What version of
>> > SQLite are you testing with? Are you compiling it yourself? If
>so,
>> > what compile-time options do you use?
>>
>> Also, what is your locale setting? The fact that COLLATE NOCASE
>affects
>> it makes this smell like a locale/i18n issue, to me.
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
