Re: [sqlite] Collating sequences. Was: Query problems
On Thu, Sep 5, 2013 at 9:40 AM, Marc L. Allen wrote: > The left-most of the first select? Or the second? Maybe I don't > understand 'left-most?' > The left-most SELECT statement in the query where the column has an explicit collating sequence. In other words: the first SELECT statement of the query that has an explicitly defined collating sequence. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collating sequences. Was: Query problems
The left-most of the first select? Or the second? Maybe I don't understand 'left-most?' -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 05, 2013 9:36 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Collating sequences. Was: Query problems On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik wrote: > On 9/5/2013 7:31 AM, Richard Hipp wrote: > >> There seems to be some misinformation circulating in this thread. >> Please let me try to clear up a few points. >> > > While we are at it, an interesting question was raised upthread. What > happens here: > > create table t1(x text collate c1); > create table t2(x text collate c2); > > select x from t1 > union > select x from t2 > order by x; > > Which collation is used by UNION to deduplicate? Which collation is > used by ORDER BY to sort? > The left-most. http://www.sqlite.org/src/artifact/8b148eb851f?ln=1582-1602 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collating sequences. Was: Query problems
On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik wrote: > On 9/5/2013 7:31 AM, Richard Hipp wrote: > >> There seems to be some misinformation circulating in this thread. Please >> let me try to clear up a few points. >> > > While we are at it, an interesting question was raised upthread. What > happens here: > > create table t1(x text collate c1); > create table t2(x text collate c2); > > select x from t1 > union > select x from t2 > order by x; > > Which collation is used by UNION to deduplicate? Which collation is used > by ORDER BY to sort? > The left-most. http://www.sqlite.org/src/artifact/8b148eb851f?ln=1582-1602 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collating sequences. Was: Query problems
On 9/5/2013 7:31 AM, Richard Hipp wrote: There seems to be some misinformation circulating in this thread. Please let me try to clear up a few points. While we are at it, an interesting question was raised upthread. What happens here: create table t1(x text collate c1); create table t2(x text collate c2); select x from t1 union select x from t2 order by x; Which collation is used by UNION to deduplicate? Which collation is used by ORDER BY to sort? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Collating sequences. Was: Query problems
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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
