On 22 Aug 2013, at 2:55am, James K. Lowden <jklow...@schemamania.org> wrote:
> 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.
Good. Because it's nonsense. What it implies is that COLLAGE is a unary
postfix operator that assigns a collating sequence to an expression. In other
words that "ABC" is an expression, and you can assign a collation to it, and
that "3F" is another expression and you can assign a different collation to
that, and that having done so that testing to see whether they equal one
another makes some kind of sense. Which it obviously doesn't.
Yet the above syntax is what the documentation for 'expr' says should be
allowed. Simpler still, it implies that
"ABC" COLLATE COL1
means something. It doesn't. Collation in SQLite makes sense only when you
compare two things. That's how the function works: you have to feed it two
values, you can't call it with just one.
> 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.)
I feel that this is even more proof that collation type is not a property of a
value. Trying to figure out the consequences of a value having inherent
collation leads to the sort of problem you describe.
> 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 )
Again, you are trying to apply a collation to a value. And because BETWEEN
takes three operands you are being given the opportunity to apply three
collations for one condition. Which is nonsense. So the syntax shouldn't
allow it or even suggest it would work. You should be casting the 'BETWEEN',
not the operands you're supplying to it.
One problem is that your example uses a collation which can be neatly expressed
as a unary function call. This is a legitimate collation but it's too simple
to force you to think the problem through. Consider instead the following
collation:
Collation 'muchStronger':
If a = b return 0, else ...
If b is 'the hulk' return -1, else ...
If a is 'the hulk' return 1, else ...
return 0
(translation: people are roughly as strong as one-another, except for The Hulk
who is stronger than everyone except himself)
This is a legitimate collation under SQLite rules, but you cannot apply it
without knowing both of the values. It is a better example than NOCASE for
thinking about the problem as long as you don't turn it into a unary function.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
