On Thu, 22 Aug 2013 13:15:19 -0400
Igor Tandetnik <i...@tandetnik.org> wrote:
> On 8/22/2013 11:49 AM, Simon Slavin wrote:
> > Step 1 would be remove all ability to specify collation applying to a
> > single value.
>
> But it already applies, implicitly, to a single value that happens to be
> a column name. It seems you want to preserve that, right?
Surely not!
A column name is not a value, but a label for a set.
The collation associated with a column applies to each subset
(of that set) whose cardinality is exactly two, and creates an
ordering on the set for which the column name is a label.
But let's bring the thread back to the original problem.
What Simon Slavin seems (to me) to be pointing out is that the
counter-intuitive behaviour observed by the OP (Clemens Ladisch)
needs either to be corrected or explicitly documented; he also
seems to be trying to find a way to meet this need.
> Hi,
>
> the documentation says (on <http://www.sqlite.org/datatype3.html#collation>):
>
> | The expression "x BETWEEN y and z" is logically equivalent to two
> | comparisons "x >= y AND x <= z" and works with respect to collating
> | functions as if it were two separate comparisons.
>
> However, this is not true when the first operator has an explicit
> collation assignment:
>
> SQLite version 3.7.17 2013-05-20 00:56:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(x);
> sqlite> insert into t values('a');
> sqlite> insert into t values('A');
> sqlite> select * from t where x collate nocase between 'a' and 'b';
> a
> sqlite> select * from t where x collate nocase >= 'a' and x collate nocase <=
> 'b';
> a
> A
>
> It works only on the second and third operators:
>
> sqlite> select * from t where x between 'a' collate nocase and 'b' collate
> nocase;
> a
> A
>
> And adding it to the first operator breaks it again:
> sqlite> select * from t where x collate nocase between 'a' collate nocase and
> 'b' collate nocase;
> a
[I would use "operand" where Clemens uses "operator".]
This behaviour is inconsistent with the documentation (Rule 1 of
section 6.1 of http://www.sqlite.org/datatype3.html#collation) because
(a) the first operand of the BETWEEN operator is precisely the left
operand of each of the two comparisons to which the BETWEEN operator
is described as being equivalent, and (b) rule 1 just mentioned gives
precedence to the explicit collating function associated with the left
operand of a comparison. It should not therefore be necessary to
declare a collation for the second and third operands of BETWEEN; one
should rather be able to rely on the collation declared for the first
one. The observed behaviour indicates that precisely the opposite is
true in practice.
AFAICS, either the code or the documentation is broken, and either one
needs to be corrected.
Best regards,
Niall O'Reilly
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
