On 8/22/2013 2:10 PM, Simon Slavin wrote:
Sorry, hit 'send' too early.
On 22 Aug 2013, at 6:15pm, Igor Tandetnik <i...@tandetnik.org> wrote:
But again, by what formal mechanism does a property of the column affect the
behavior of the operator?
I see no reason for it to do that.
So to be clear:
create table t(x text collate nocase);
insert into t values ('A');
select count(*) from t where x = 'a';
Currently, this select statement returns 1. Is it your position that it
should return 0 instead? Somehow I doubt you would find many SQLite
users sympathetic to this position.
But my original problem, as stated, isn't with this. It's just with the way
that one can explicitly apply a correlation in an expression by using the
COLLATE operator.
And my point is that, once you have collations on columns and have to
deal with them anyway, it's not a problem to have collations on any
other expression: you just deal with them the exact same way.
Now, if your position is that comparison operators shouldn't pay
attention to collations associated with columns either, then the rest of
your argument becomes internally consistent. But I don't think I would
like to live in the world you envision. Consider my earlier example,
slightly modified:
create table t(x text primary key collate nocase);
select count(*) from t where x = 'a';
There is an implicit index on t(x collate nocase), thanks to the PRIMARY
KEY constraint. But the statement (in your interpretation) *cannot* use
that index, since the comparison uses the default BINARY collation, and
has to resort to full table scan instead. In order to take advantage of
the index, one must write
select count(*) from t where x =NOCASE 'a';
(or whatever the syntax would be for hanging a collation onto the
operator). I feel this is a) extremely inconvenient, and b) a severe
violation of the principle of least astonishment.
About your example where two seemingly equivalent statements like
select * from t where x = y;
select * from t where y = x;
may produce different results - I've already stated a few times that I
would be sympathetic to the argument that having two operands with
different collations could be considered a syntax error. With that in
place, both statements would be invalid, and the issue would fail to
arise. This can be easily accomplished with a small tweak to existing
rules, perhaps enabled with a pragma.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
