Documentation Source Text

Check-in [6fe30b1e8d]
Login

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

Overview
Comment:Fix incorrect documentation concerning affinity of UNION, INTERSECT, and EXCEPT.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6fe30b1e8dcac1e2b9f3494f49d02e3222fd69fa
User & Date: drh 2009-12-31 18:30:39.000
Context
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)
18:30
Fix incorrect documentation concerning affinity of UNION, INTERSECT, and EXCEPT. (check-in: 6fe30b1e8d user: drh tags: trunk)
16:06
Add HTML escapes back into requirement text when generating HTML in the requirements matrix. (check-in: ca108712a2 user: drh tags: trunk)
Changes
Unified Diff Show Whitespace Changes Patch
Changes to pages/datatype3.in.
︙︙
445
446
447
448
449
450
451
452
453
454

455
456


457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481

482
483
484
485
486
487
488
489


<p>^All of the result in the example are the same if the comparisons are
commuted - if expressions of the form "a&lt;40" are rewritten
as "40&gt;a".

<h2>4.0 Operators</h2>

<p>^(All mathematical operators (which is to say, all operators other
than the concatenation operator "||", LIKE, GLOB, MATCH, and REGEXP) 
apply NUMERIC affinity to both operands prior to being carried out.)^ 

^If one or both operands cannot be converted to NUMERIC then the result of the
mathematical operation is NULL.</p>



<h2>5.0 Sorting, Grouping and Compound SELECTs</h2>

<p>^When query results are sorted by an ORDER BY clause, values with storage
class NULL come first, followed by INTEGER and REAL values
interspersed in numeric order, followed by TEXT values in collating
sequence order, and finally BLOB values in memcmp() order.  ^No storage
class conversions occur before the sort.</p>

<p>^When grouping values with the GROUP BY clause values with
different storage classes are considered distinct, except for INTEGER
and REAL values which are considered equal if they are numerically
equal. ^No affinities are applied to any values as the result of a
GROUP by clause.</p>

<p>^The compound SELECT operators UNION,
INTERSECT and EXCEPT perform implicit comparisons between values.
^Before these comparisons are performed an affinity may be applied to
each value. ^The same affinity, if any, is applied to all values that
may be returned in a single column of the compound SELECT result set.
^The affinity applied is the affinity of the column returned by the
left most component SELECTs that has a column value (and not some
other kind of expression) in that position. ^If for a given compound
SELECT column none of the component SELECTs return a column value, no
affinity is applied to the values from that column before they are

compared.</p>

<tcl>hd_fragment collation {*collating sequence} \
                 {collating function} *collation *BINARY *NOCASE *RTRIM \
        {BINARY collating function} \
        {NOCASE collating function} \
        {RTRIM collating function}</tcl>
<h2>6.0 Collating Sequences</h2>








|
|
|
>
|
|
>
>

















<
<
<
<
<
<
<
|
>
|








445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476







477
478
479
480
481
482
483
484
485
486


<p>^All of the result in the example are the same if the comparisons are
commuted - if expressions of the form "a&lt;40" are rewritten
as "40&gt;a".

<h2>4.0 Operators</h2>

<p>^(All mathematical operators (+, -, *, /, %, &lt;&lt;, &gt;&gt;,
&amp;, and |)
cast both operands to the NUMERIC storage class prior to being carried out.)^
^The cast is carried through even if it is lossy and irreversible.
^A NULL operand on a mathematical operator yields a NULL result.
^(An operand on a mathematical operator that does not look in any way
numeric and is not NULL is converted to 0 or 0.0.)^
</p>

<h2>5.0 Sorting, Grouping and Compound SELECTs</h2>

<p>^When query results are sorted by an ORDER BY clause, values with storage
class NULL come first, followed by INTEGER and REAL values
interspersed in numeric order, followed by TEXT values in collating
sequence order, and finally BLOB values in memcmp() order.  ^No storage
class conversions occur before the sort.</p>

<p>^When grouping values with the GROUP BY clause values with
different storage classes are considered distinct, except for INTEGER
and REAL values which are considered equal if they are numerically
equal. ^No affinities are applied to any values as the result of a
GROUP by clause.</p>

<p>^The compound SELECT operators UNION,
INTERSECT and EXCEPT perform implicit comparisons between values.







^No affinity is applied to comparison operands for the implicit
comparisons associated with UNION, INTERSECT, or EXCEPT - the values
are compared as is.</p>

<tcl>hd_fragment collation {*collating sequence} \
                 {collating function} *collation *BINARY *NOCASE *RTRIM \
        {BINARY collating function} \
        {NOCASE collating function} \
        {RTRIM collating function}</tcl>
<h2>6.0 Collating Sequences</h2>
︙︙