Documentation Source Text

Check-in [e62a74f31a]
Login

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

Overview
Comment:Clarifications on affinity handling in the datatype3.html document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e62a74f31a5254f90cbc11186ea630743521768c
User & Date: drh 2009-12-31 14:47:09.000
Context
2009-12-31
15:28
Convert HTML ampersand-values "&lt;", "&gt;", and "&amp;" into "<", ">", and "&" prior to computing the hash for a requirement. (check-in: 8822607ccf user: drh tags: trunk)
14:47
Clarifications on affinity handling in the datatype3.html document. (check-in: e62a74f31a user: drh tags: trunk)
2009-12-30
18:17
The FLOATING POINT datatype has INTEGER affinity due to the "INT" at the end of "POINT". Make this clear in the datatype3.html documentation file. (check-in: 5c8750d0ff user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/datatype3.in.
︙︙
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353


354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375

376
377
378
379
380
381

382
383
384
385
386
387
388
389
390
391
392
393

<ul>
  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  ^(Note that if X and Y.Z 
  are column names, then +X and +Y.Z are considered expressions for the
  purpose of determining affinity.)^</p>

  <li><p>^(An expression of the form "CAST(&lt;expr&gt; TO &lt;type&gt;)"
  has an affinity that is the same as a column with a declared
  type of &lt;type&gt;)^

  <li><p>^Otherwise, an expression has NONE affinity.
</ul>

<tcl>hd_fragment compaff {comparison affinity rules}</tcl>
<h3>3.3 Type Conversions Prior To Comparison</h3>



<p>Storage class conversions of operands prior to a binary comparison
operation occur according to the following rules in the order shown:</p>

<ul>
<li><p>^If either operand has INTEGER, REAL or NUMERIC affinity and the
other operand does not, then NUMERIC
affinity is applied to the other operand.

<li><p>^If either operand has TEXT affinity and the other has no affinity,
then TEXT affinity is applied to the other operand.

<li><p>^Otherwise, no affinity is applied and both operands are compared
as is.</p>
</ul>

<p>In the comparison affinity rules above, to "apply affinity" means to
perform the storage class conversion if and only if the conversion is
reversible and lossless.</p>

<p>^(The expression "a BETWEEN b AND c" is treated as two separate
binary comparisons "a &gt;= b AND a &lt;= c", even if that means
different affinities are applied to 'a' in each of the comparisons.)^

^(Expressions of the form "a IN (SELECT b ....)" are handled is if
the expression where really "a=b".)^
^For example, if "b" is a column value
and "a" is an expression, then the affinity of "b" is applied to "a"
before any comparisons take place.
^(The expression "a IN (x, y, z)" as equivalent to "a = +x OR

a = +y OR a = +z".)^  ^The values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to be expressions, even if they
happen to be column values.  ^If the value to the left of the IN operator is
a column, then the affinity of that column is used.  ^If the left operand
of IN is an expression then no storage class conversions occur.
</p>

<h3>3.4 Comparison Example</h3>

^(<blockquote>
<pre>
CREATE TABLE t1(








|

|







>
>
|
|


|
|
|

|






<
<
<
<



>
|
<
<
<
|
|
>
|
|
|
<
<








337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370




371
372
373
374
375



376
377
378
379
380
381


382
383
384
385
386
387
388

<ul>
  <li><p>^An expression that is a simple reference to a column value
  has the same affinity as the column.
  ^(Note that if X and Y.Z 
  are column names, then +X and +Y.Z are considered expressions for the
  purpose of determining affinity.)^</p>

  <li><p>^(An expression of the form "CAST(<i>expr</i> TO <i>type</i>)"
  has an affinity that is the same as a column with a declared
  type of "<i>type</i>".)^

  <li><p>^Otherwise, an expression has NONE affinity.
</ul>

<tcl>hd_fragment compaff {comparison affinity rules}</tcl>
<h3>3.3 Type Conversions Prior To Comparison</h3>

<p>To "apply affinity" means to convert an operand to a particular storage
class if and only if the conversion is lossless and reversible.
^(Affinity is applied to operands of a comparison operator prior to
the comparison according to the following rules in the order shown:)^</p>

<ul>
<li><p>^If one operand has INTEGER, REAL or NUMERIC affinity
and the other operand as TEXT or NONE affinity
then NUMERIC affinity is applied to other operand.

<li><p>^If one operand has TEXT affinity and the other has NONE affinity,
then TEXT affinity is applied to the other operand.

<li><p>^Otherwise, no affinity is applied and both operands are compared
as is.</p>
</ul>





<p>^(The expression "a BETWEEN b AND c" is treated as two separate
binary comparisons "a &gt;= b AND a &lt;= c", even if that means
different affinities are applied to 'a' in each of the comparisons.)^
^(Datatype conversions in comparisons of the
form "x IN (SELECT y ...)" are handled is if



the comparison were really "x=y".)^
^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR
a = +y OR a = +z OR ...".)^  
^In other words, the values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to have no affinity, 
even if they happen to be column values or CAST expressions.  


</p>

<h3>3.4 Comparison Example</h3>

^(<blockquote>
<pre>
CREATE TABLE t1(
︙︙