@Warren: I'm building a tool to test DBMS by automatically generating
queries and checking their results. Since the statement sequence was
generated automatically, it looks like artificial.
@Keith Thanks again for the explanation!
Best,
Manuel
On Thu, May 2, 2019 at 1:24 AM Keith Medcalf <kmedc...@dessus.com> wrote:
>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
