On Mon, 22 Nov 2004 05:28:52 -0800 (PST), Daniel K <[EMAIL PROTECTED]> wrote: > > > > --- Tiago Dionizio <[EMAIL PROTECTED]> wrote: > > > On Mon, 22 Nov 2004 08:00:08 -0500, D. Richard Hipp > > <[EMAIL PROTECTED]> wrote: > > > Tiago Dionizio wrote: > > > > > > > > The collation sequence "binary" was not being > > used when i declared the > > > > column a without a type, but when i included the > > *text* type the > > > > collation sequence was used to sort the result. > > Is this the expected > > > > behaviour? > > > > > > > > > > The collating sequence is only use to compare > > objects of type TEXT. > > > Numeric values always compare in numeric order. > > When you made the > > > column type TEXT, that coerced the numeric values > > into text values > > > and thus they began to be compared as TEXT instead > > of NUMERIC. > > > > > > See http://www.sqlite.org/datatype3.html > > > > I did look at that page, and the example 7.2 doesn't > > use any type in > > the create table statement only collating > > attributes: > > > > CREATE TABLE t1( > > a, -- default collation type > > BINARY > > b COLLATE BINARY, -- default collation type > > BINARY > > c COLLATE REVERSE, -- default collation type > > REVERSE > > d COLLATE NOCASE -- default collation type > > NOCASE > > ); > > > > I tried the previous test using "create table t(a > > collate binary)" but > > it wasn't used either. > > > > Maybe this could be explained in the documentation? > > Look at the top of that page, under "storage classes". > The reason the collation sequence was not used in > the example you give is that the values are being > stored as numbers, not text. Comparing two numbers > uses numeric comparison, regardless of the collation > sequence. > > When you declared the column type as TEXT, the values > were coerced (see the document section "column > affinity") to text. So the collation sequence was > used in the comparison. Consult the examples in > sections 2.2 and 3.1 for extra clarity on storage > classes and comparisons. >
Thank you both for the explanation. I understand it now, sometimes no matter how carefully i try to read the documentation i just miss something :) If i had read the following from section 7.2 i would have a better idea sooner: "The examples below identify the collation sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collation sequence used, in the case of numeric, blob or NULL values." Tiago
