There seems to be some misinformation circulating in this thread. Please
let me try to clear up a few points.
If you give a collating sequence to a column in a table definition, then
that collating sequence becomes the default for that column:
CREATE TABLE t1(pqr TEXT COLLATE xyzzy);
In the table above, the pqr column has a default collating sequence of
xyzzy. Any time you do a comparison against that column the default
collating sequence is used. So, for example, if you say:
SELECT * FROM t1 WHERE pqr=?1;
Then the xyzzy collating sequence is used to compare the input string bound
to ?1 against the value of the column pqr. It is *not* necessary to
explicitly add a COLLATE clause to the comparison:
SELECT * FROM t1 WHERE pq4=?1 COLLATE xyzzy;
Though not necessary, adding the COLLATE clause is harmless in this case.
If you create an index on a column, that index automatically uses the
default collating sequence, unless you specify an alternative. So, for
example, if you say:
CREATE INDEX t1pqr ON t1(pqr);
Then the xyzzy collating sequence is used for the index as well. It is
*not* necessary to explicitly add a COLLATE clause to the index:
CREATE INDEX t1pqr ON t1(pqr COLLATE xyzzy);
On the other hand, doing so is a harmless no-op.
Creating an index with a collating sequence that differs from the default
does not change the default collating sequence of the column. Hence, if
you say:
CREATE TABLE t2(mno TEXT);
CREATE INDEX t2mno1 ON t2(mno COLLATE xyzzy);
SELECT * FROM t2 WHERE mno=?2;
The comparison in the query uses the default collating sequence for the
column mno and hence cannot make use of the index. But if you query this
way:
SELECT * FROM t2 WHERE mno=?3 COLLATE xyzzy;
Then the collating sequence of the index matches the collating sequence of
the query and the index can be used.
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
