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