Documentation Source Text

<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(

<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(