On 22 Aug 2013, at 2:36pm, Igor Tandetnik <i...@tandetnik.org> wrote:
> On 8/22/2013 8:52 AM, Simon Slavin wrote: >> Nevertheless do you understand the point I'm trying to make -- that >> collations are a modifier for comparisons not individual values ? > > I do understand your point. I still don't understand how it's supposed to > apply in practice to a situation like > > create table t(x text collate nocase); > select * from t where x = 'a'; > > Here, at the time I specify "collate nocase", there is no comparison it could > apply to. So what is it a property of, in your thinking? COLLATE is a property of that column but at the table creation stage isn't used at all, it's just stored. However, if you later define an index which uses that column, the collation of the column is used as the collation for that element of the index, unless you override it in your index definition. I have no problem with any of this: it can proceed as before. Your second line doesn't specify a COLLATE operator in its expression, so I have no problem with it. > By what mechanism does it end up applying to x='a' comparison (I assume to do > want the statement to return rows both with 'a' and 'A' in column x)? How > would you modify the formal spec at http://sqlite.org/datatype3.html to lead > to your desired outcome? Precise wording matters. I don't understand why binary comparison operators are on that page at all. They aren't used directly as column definitions, only as parts of expressions, and expressions are defined on another page. If you remove mention of comparison operators from that page, the rest of that page is fine. Precise wording as you requested ? Remove all of section 6.1 apart from the last paragraph. > As I said, I could relate to your point of view better if you just did this: > inhttp://sqlite.org/datatype3.html section 6.1, replaced two occurrences of > "with precedence to the left operand" with "It's an error if two operands > have different collations". This keeps the existing, well defined mechanisms > intact, while neatly excluding the case you seem to find most objectionable. As I wrote originally, my problem is not with the use of COLLATE in column definitions, it's with its use in expressions. So my problem in documentation of SQLite doesn't come in datatype3, it comes in <http://www.sqlite.org/lang_expr.html> Step 1 would be remove all ability to specify collation applying to a single value. This is just two short paragraphs on that page (search for the word 'collate'). This would remove all trace of the use of COLLATE I have a problem with, i.e. where it can be used in such a way as to be applied to a single value and not a comparison. Step 2 would be to introduce a way of specifying a collation to be applied to the comparison operators: < <= > >= = == != <> IS IS NOT and the trinary operator BETWEEN. The phrasing should be used to indicate that a COLLATE is applied to an operator is a design question and best left to the developer team who will understand which form is easiest to parse and make sense of. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
