On 8/22/2013 11:49 AM, Simon Slavin wrote:
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
So sometimes it's a property of a column, and other times it's a
property of a comparison operator? I predict you are going to have a
hard time describing this notion in a formal spec.
Your second line doesn't specify a COLLATE operator in its expression, so I
have no problem with it.
But again, by what formal mechanism does a property of the column affect
the behavior of the operator? And if you are OK with it doing that, then
what's wrong with the existing model, which is based on this very
approach (collation is a property of an expression; behavior of an
operator depends on collations associated with its operands)?
Let's put it this way: why should there be a fundamental difference
between an expression (x) where x is a column declared with COLLATE
NOCASE clause, and an expression ('a' COLLATE NOCASE)? What purpose
would such a distinction serve? Why exactly are you OK with the former,
but not the latter?
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.
But again, I assume you do want the expression (x='a') to sometimes
evaluate to true when column x contains the value 'A', and other times
evaluate to false. How would this happen? It doesn't matter which page
describes the behavior: if it's not
http://www.sqlite.org/datatype3.html, then I imagine it would be
somewhere on http://www.sqlite.org/lang_expr.html. What should it say?
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.
But it already applies, implicitly, to a single value that happens to be
a column name. It seems you want to preserve that, right?
This is just two short paragraphs on that page (search for the word 'collate').
One of those paragraphs says: "See the detailed discussion on collating
sequences in the Datatype In SQLite3 document for additional
information." You claim that you want to excise that very discussion
from "datatypes" article - doesn't that mean that you would have to move
it here, rather than incorporating it by reference? The behavior must be
described *somewhere*.
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.
However, a collation would still apply to a single value that happens to
be a column name, and comparison operators where such a value is an
operand would have to take that into account somehow, wouldn't they? You
are not saying that (x='a') should always use BINARY collation
regardless of how column x was declared, are you? And if you define some
mechanism by which the comparison takes the collation of x into account,
then I don't understand why it's fundamentally awful and wrong to extend
that same mechanism to the expression (('A' collate nocase) = 'a').
You instead suggest the invention of a separate mechanism whereby the
collation is assigned directly to the operator rather than inferred from
its operands; but you also need to keep the original mechanism around.
So now you have to specify two mechanisms, plus the interaction between
them. See http://en.wikipedia.org/wiki/Occam's_razor : "entities must
not be multiplied beyond necessity."
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
