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

Downloads: | Tarball | ZIP archive | SQL archive |

Timelines: | family | ancestors | descendants | both | trunk |

Files: | files | file ages | folders |

SHA3-256: |
e0f700bb6c8142c56a6cfd673a29bd2b |

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

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<40" are rewritten as "40>a". <h1>Operators</h1> <p>^(All mathematical operators (+, -, *, /, %, <<, >>, &, 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<40" are rewritten as "40>a". <h1>Operators</h1> <p>^(Mathematical operators (+, -, *, /, %, <<, >>, &, 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 (%, <<, >>, &, and |) expect INTEGER operands. For those operators, REAL operands are converted into INTEGER in the same way as a [CAST|CAST to INTEGER].)^ ^The <<, >>, &, 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 |