On Wed, 21 Aug 2013 20:26:30 +0100
Simon Slavin <slav...@bigfraud.org> wrote:
> My problem is not with COLLATE in general. It's with expressions.
> Table definitions are fine. Index definitions are fine. It's purely
> that my understanding of the documention says that something like this
>
> "ABC" COLLATE COL1 = "3F" COLLATE COL2
(I don't understand what that syntax is supposed to mean. AFAIK, the
only operands to COLLATE are BINARY, NOCASE, and RTRIM.)
> should be allowed, and I don't agree.
You're quite right to say that collation is property of the comparison,
not the data. It's obvious from very fact that the same data can be
compared in different ways.
A column's collation in CREATE TABLE is a property of the column, not
of the data. That is, it's a property of the data *type*, the domain
to which the values in the column belong. It informs the system how to
compare two values in the *same* column.
The question arises: how to compare two columns with different
collations? Because they're drawn from different domains -- per the
column defintions -- they have different types. To compare two
different types requires conversion. That conversion may be implicit
or explicit. (And it many fail; some types are incommensuate.)
The question then becomes whether to require explicit conversion and,
if so, what syntax would be clearest. I think you'd say, and I agree,
that implicit conversion isn't attractive because any choice would be
idiosyncratic, and such choices are traps for the unwary.
Regarding syntax, the current situation is less than idea. I tried the
OP's query and got different results:
SQLite version 3.7.13 2012-06-11 02:05:22
...
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';
x
----------
a
A
Just for giggles, I tried another variation:
sqlite> select * from t where x between 'a' and 'b' collate nocase;
x
----------
a
The COLLATE operator is the sole postfix operator in SQLite's SQL, and
weird because it appears late but binds early. I would suggest
instead it mimic or become part of CAST, or become its own operator.
For example:
1. where COLLATE( x AS NOCASE )
between COLLATE( 'a' AS NOCASE )
and COLLATE( 'b' AS NOCASE )
Making it part of CAST emphasizes the fact that we're dealing in types:
2. where CAST( x as COLLATE NOCASE )
between CASE( 'a' AS COLLATE NOCASE )
and CASE( 'b' AS COLLATE NOCASE )
But isn't this clearer in any case (no pun intended)?
3. where upper(x) between 'A' and 'B'
which suggests that each collation could become an operator in its own
right:
4. where NOCASE(x) between NOCASE('a') and NOCASE('b')
because it allows for future expansion as more collations are added.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
