Documentation Source Text

Check-in [490c97c96c]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to the datatype3.html document to fix issues found while writing tests against that document.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 490c97c96c63bc2ef16de6839dd3fce826c41747
User & Date: drh 2010-01-01 14:46:51
Context
2010-01-02
03:20
Add ACTION and NO to the list of keywords in the lang_keywords.html document. Reformat the keyword lists into testable statements. check-in: 8cad2470c0 user: drh tags: trunk
2010-01-01
14:46
Updates to the datatype3.html document to fix issues found while writing tests against that document. check-in: 490c97c96c user: drh tags: trunk
2009-12-31
22:14
Add the "any" column to the requirement matrix summary screen. The box goes green if all requirements are covered by any combination of TCL, TH3, our source code evidence. check-in: 1c49dd8699 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

136
137
138
139
140
141
142
143
144
145

146
147
148
149
150
151
152
...
545
546
547
548
549
550
551
552
553
554

555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570

571
572
573
574
575





576
577
578


579
580
581

582
583
584


585
586
587


588
589
590


591
592
593


594
595
596
597

598
599

600

601
602
603

604
605
606

607
608
609
610
611


612
<p>^A column that uses INTEGER affinity behaves the same as a column
with NUMERIC affinity.  The difference between INTEGER and NUMERIC affinity
is only evident in a [CAST expression].</p>

<p>^A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation.  (^As an internal optimization, small floating point
values with no fractional component are stored on
disk as integers in order to take up less space and are 
automatically converted back into floating point as the value is read out.

^This optimization is completely invisible at the SQL level and can only
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity NONE does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>

................................................................................
^(The expression "x IN (SELECT y ...)" is handled in the same way as the
expression "x = y" for the purposes of determining the collating sequence.)^
^(The collating sequence used for expressions of the form 
"x IN (y, z, ...)" is the collating sequence of x.)^
</p>  

<p>
^An ORDER BY clause that is part of a [SELECT]
statement may be assigned a collating sequence to be used for the sort
operation explicitly. ^In this case the explicit collating sequence is

always used.  ^Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the collating sequence of the column is used to
determine sort order. ^If the expression is not a column, then the BINARY
collating sequence is used.
</p>  

<h3>6.2 Collation Sequence Examples</h3>
<p>
The examples below identify the collating 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
collating sequence used, in the case of numeric, blob or NULL values.
</p>
^(<blockquote>
<PRE>
CREATE TABLE t1(

    a,                 -- collating sequence BINARY
    b COLLATE BINARY,  -- collating sequence BINARY
    c COLLATE RTRIM,   -- collating sequence RTRIM
    d COLLATE NOCASE   -- collating sequence NOCASE
);






-- Text comparison is performed using the BINARY collating sequence.
SELECT a = b FROM t1;



-- Text comparison is performed using the RTRIM collating sequence.
SELECT a = b COLLATE RTRIM FROM t1;


-- Text comparison is performed using the NOCASE collating sequence.
SELECT d = a FROM t1;



-- Text comparison is performed using the BINARY collating sequence.
SELECT a = d FROM t1;



-- Text comparison is performed using the RTRIM collating sequence.
SELECT 'abc' = c FROM t1;



-- Text comparison is performed using the RTRIM collating sequence.
SELECT c = 'abc' FROM t1;



-- Grouping is performed using the NOCASE collating sequence (i.e. values
-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;


-- Grouping is performed using the BINARY collating sequence.

SELECT count(*) GROUP BY (d || '') FROM t1;


-- Sorting is performed using the RTRIM collating sequence.
SELECT * FROM t1 ORDER BY c;


-- Sorting is performed using the BINARY collating sequence.
SELECT * FROM t1 ORDER BY (c || '');


-- Sorting is performed using the NOCASE collating sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;

</PRE>


</blockquote>)^







|
|
|
>







 







|
|
|
>
|

|
|










|

>
|
|
|
|

>
>
>
>
>
|
|
<
>
>

|
|
>

|
<
>
>

|
<
>
>

|
<
>
>

|
<
>
>

|
|
|
>

|
>
|
>

|
|
>

|
|
>

|
|
<
<
>
>

136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
...
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585

586
587
588
589
590
591
592
593

594
595
596
597

598
599
600
601

602
603
604
605

606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628


629
630
631
<p>^A column that uses INTEGER affinity behaves the same as a column
with NUMERIC affinity.  The difference between INTEGER and NUMERIC affinity
is only evident in a [CAST expression].</p>

<p>^A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
representation.  (^As an internal optimization, small floating point
values with no fractional component and stored in columns with REAL
affinity are written to disk as integers in order to take up less 
space and are automatically converted back into floating point as
the value is read out.
^This optimization is completely invisible at the SQL level and can only
be detected by examining the raw bits of the database file.)</p>

<p>^A column with affinity NONE does not prefer one storage class over
another and no attempt is made to coerce data from one storage class into
another.</p>

................................................................................
^(The expression "x IN (SELECT y ...)" is handled in the same way as the
expression "x = y" for the purposes of determining the collating sequence.)^
^(The collating sequence used for expressions of the form 
"x IN (y, z, ...)" is the collating sequence of x.)^
</p>  

<p>
^Terms of the ORDER BY clause that is part of a [SELECT]
statement may be assigned a collating sequence using the 
[COLLATE operator], in which case the specified collating function is
used for sorting.
^Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the collating sequence of the column is used to
determine sort order. ^If the expression is not a column and has no
COLLATE clause, then the BINARY collating sequence is used.
</p>  

<h3>6.2 Collation Sequence Examples</h3>
<p>
The examples below identify the collating 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
collating sequence used, in the case of numeric, blob or NULL values.
</p>
^(<blockquote>
<pre>
CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);
                   /* x   a     b     c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');
 
/* Text comparison a=b is performed using the BINARY collating sequence. */

SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */

SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */

SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */

SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */

SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

/* Sorting of (c||'') is performed using the BINARY collating sequence. */
SELECT x FROM t1 ORDER BY (c||''), x;
--result 4 2 3 1

/* Sorting of column c is performed using the NOCASE collating sequence. */
SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;


--result 2 4 3 1
</pre>
</blockquote>)^