Documentation Source Text

Check-in [e0f700bb6c]
Login

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

Overview
Comment:Clarify how mathematical operators function given string or blob operands. Ticket https://www.sqlite.org/src/tktview/1819598c09
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e0f700bb6c8142c56a6cfd673a29bd2b7b1d8590847b03665965911c6d2c2629
User & Date: drh 2019-06-11 13:40:01
Context
2019-06-11
15:54
Clarify the semantics of a CAST to NUMERIC. Ticket https://www.sqlite.org/src/tktview/afdc5a29dc check-in: 7a51b32537 user: drh tags: trunk
13:40
Clarify how mathematical operators function given string or blob operands. Ticket https://www.sqlite.org/src/tktview/1819598c09 check-in: e0f700bb6c user: drh tags: trunk
02:59
Augment the affinity rules to state that the affinity of a COLLATE operator is the same as the affinity of its left-hand side. Ticket https://www.sqlite.org/src/tktview/d60b3cd7cb check-in: 722f0828b3 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

522
523
524
525
526
527
528
529
530
531









532






533
534
535

536
537
538
539
540
541
542

<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".

<h1>Operators</h1>

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

<h1>Sorting, Grouping and Compound SELECTs</h1>

<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







|
|
|
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>



>







522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558

<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".

<h1>Operators</h1>

<p>^(Mathematical operators (+, -, *, /, %, &lt;&lt;, &gt;&gt;,
&amp;, and |) interpret both operands as if they were numbers.
STRING or BLOB operands automatically convert into REAL or INTEGER values.)^
^(If the STRING or BLOB looks like a real number (if it has a decimal point
or an exponent) or if the value is outside the range that can be represented
as a 64-bit signed integer, then it converts to REAL.  Otherwise the operand
converts to INTEGER.)^
^(The implied type conversion of mathematical operands is slightly different
from [CAST|CAST to NUMERIC] in that string and BLOB values that
look like real numbers but have no fractional part are kept as REAL
instead of being converted into INTEGER as they would be for [CAST|CAST to NUMERIC].)^
^The conversion from STRING or BLOB into REAL or INTEGER is performed
even if it is lossy and irreversible.
^(Some mathematical operators (%, &lt;&lt;, &gt;&gt;, &amp;, and |) expect
INTEGER operands.  For those operators, REAL operands are converted into INTEGER
in the same way as a [CAST|CAST to INTEGER].)^
^The &lt;&lt;, &gt;&gt;, &amp;, and | operators always return an INTEGER (or NULL)
result, but the % operator returns either INTEGER or REAL (or NULL)
depending on the type of its operands.
^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.)^
^Division by zero gives a result of NULL.
</p>

<h1>Sorting, Grouping and Compound SELECTs</h1>

<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