Dear Igor,
I was reading with great interest your debate with Simon. I have come to
respect your opinion by reading your comments. I am not a moderator, but trying
to imply that many SQLite users are on your side reduced your image in my head.
I left USSR (where such arguments were plenty) and would prefer to stay away
from it. I block my eyes and ears so that I can not see/hear further arguments
even if they are correct and even if I would agree with them if I heard them.
This is my psychological defense.
On the issue. I find Simon's example
select * from t where x = y;
select * from t where y = x;
extremely disturbing. One has to remember implied rules to interpret this
statement and/or to compose it properly. Spelling everything out is long, I
agree, but makes things clear. However, his example is no different from yours
because
select count(*) from t where x = 'a';
select count(*) from t where 'a'=x;
works only thanks to 'a' having not collation attached and SQLite using one
from x instead. This is exactly the point Simon is making: collations are
"attached" to values, not to operations. From Simon's description, this
"attachment" takes place on a higher level in SQLite since deep inside
(comparisons) it is attached to the operator. Thus SQLite transfers collate
rules from values to operations somewhere on the high level. This transfer is
the issue that Simon points out.
Logically, I agree with Simon, collate modifier in table definition describes
how indices should be built, nothing more. Comparisons, as in these examples
should specify which collation is to be used and if it is different from one
used for indexing, then yes, Igor, index can not be used. I believe this is the
present behavior already.
I do not think Simon asks to change the way collations work. He is asking to
make clear how they work. I think Simon is asking for clarity so that error
like "(x collate A) = (y collate B)" can not take place. This is similar to
JOIN operation, which requires two tables to have common column. Using this
analogy, I would write:
select * from t WHERE x = y USING NOCASE
Roman
________________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Igor Tandetnik [i...@tandetnik.org]
Sent: Thursday, August 22, 2013 3:53 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BETWEEN and explicit collation assignment
On 8/22/2013 3:12 PM, Simon Slavin wrote:
>
> On 22 Aug 2013, at 8:04pm, Igor Tandetnik <i...@tandetnik.org> wrote:
>> [snip]
>
> I pretty much agree with everything you wrote there. But it has nothing to
> do with my original objection which was the explicit use of a COLLATE
> operator inside an expression.
If you agree with everything I wrote, then I don't understand what
exactly you find wrong with the explicit use of a COLLATE operator
inside an expression. Is there something that can be done your way but
can't be done the current way? Is there some bad outcome that's possible
the current way but prevented your way? Do you just prefer your (as yet
unspecified) syntax on purely aesthetic grounds?
Let's assume, for the sake of argument, that the case of "(x collate A)
= (y collate B)" and similar instances of mismatched collations are made
an error (I've conceded that it may be a good idea, so there's no reason
to re-tread this ground).
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
