--- 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.
__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
