I was just refuting the claim that "the WHERE clause converts 'a' to 'A' in the database." It doesn't. So either the "change" was caused by a copy-paste error when creating the mail or by actually runing a (different) query that produces it.
Anyway, in the WHERE clause, the arguments to '<' are 'ltrim(col)' and 'b'. Neither the function ltrim() nor the literal 'b' have a collation, so the comparison proceeds using BINARY. Which is as documented. -----Ursprüngliche Nachricht----- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Dienstag, 11. November 2014 00:05 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation? On Mon, 10 Nov 2014 08:43:24 +0000 Hick Gunter <h...@scigames.at> wrote: > I get the following results for the second select: > > A > B > a (lowercase!!!) > > Are you sure you ran the exact query stated? I didn't run it. Your mail showed 3 uppercase letters: > > sqlite> SELECT * FROM test WHERE LTRIM(col)<'b'; > > A > > B > > A --jkl > > -----Ursprüngliche Nachricht----- > Von: James K. Lowden [mailto:jklow...@schemamania.org] > Gesendet: Samstag, 08. November 2014 01:52 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation? > > On Thu, 6 Nov 2014 17:02:26 -0500 > Richard Hipp <d...@sqlite.org> wrote: > > > > sqlite> INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a'); SELECT > > > sqlite> * FROM test; > > > b > > > A > > > B > > > a > ... > > > sqlite> SELECT * FROM test WHERE LTRIM(col)<'b'; > > > A > > > B > > > A > ... > > Works as designed. See > > https://www.sqlite.org/datatype3.html#collation and in particular > > the three rules under section 6.1. > > Aren't you overlooking the fact that the WHERE clause is changing not > only which rows are selected, but the *values* of those rows? > > I don't understand how any WHERE clause can convert 'a' to 'A' in the > database. I'm unable to find any suggestion of such in the collation > documentation. > > --jkl > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This communication (including any attachments) is intended for the use > of the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you > have received this communication in error, please immediately notify > the sender by return e-mail message and delete all copies of the > original communication. Thank you for your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
