Documentation Source Text

Check-in [6c47d70dc0]
Login

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

Overview
Comment:Modifications to documentation for CASE expressions.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6c47d70dc09d5c97c70b38c60e9c0ff641f12cfa
User & Date: dan 2010-08-24 13:11:33
Context
2010-08-26
19:07
Changes to CAST expression documentation to make for more testable statements. check-in: 6c20f79cbc user: dan tags: trunk
2010-08-24
13:11
Modifications to documentation for CASE expressions. check-in: 6c47d70dc0 user: dan tags: trunk
2010-08-23
22:51
Preparation for the 3.7.2 release. check-in: 7bf266483d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

  1453   1453   that with BETWEEN, the <i>x</i> expression is only evaluated once.)^
  1454   1454   ^The precedence of the BETWEEN operator is the same as the precedence
  1455   1455   as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.
  1456   1456   
  1457   1457   <tcl>hd_fragment case {CASE expression}</tcl>
  1458   1458   <h3>The CASE expression</h3>
  1459   1459   <p>A CASE expression serves a role similar to IF-THEN-ELSE in other
  1460         -programming languages.  ^WHEN expressions are evaluated from left to
  1461         -right until one is found that is true, at which point the corresponding
  1462         -THEN term becomes the result.  ^If no WHEN expression is true then the ELSE 
  1463         -clause determines the result or the result is NULL if there is no ELSE clause.
  1464         -</p>
         1460  +programming languages.  
  1465   1461   
  1466   1462   <p>The optional expression that occurs in between the CASE keyword and the
  1467         -first WHEN keyword is the "base" expression.  ^There are two basic forms
  1468         -of a CASE expression: those with and without a base expression.
  1469         -^In a CASE without a base expression, each WHEN expression is evaluated
  1470         -as a boolean and the overall result is determined by first WHEN expression
  1471         -that is true.
  1472         -^In a CASE with a base expression, the base expression is evaluated just
  1473         -once and the result is compared against each WHEN expression until a 
  1474         -match is found.
  1475         -^When comparing a base expression against a WHEN expression, the same
         1463  +first WHEN keyword is called the "base" expression. ^There are two basic forms
         1464  +of the CASE expression: those with a base expression and those without.
         1465  +
         1466  +<p>^In a CASE without a base expression, each WHEN expression is evaluated
         1467  +and the result treated as a boolean, starting with the leftmost and continuing
         1468  +to the right. ^The result of the CASE expression is the evaluation of the THEN
         1469  +expression that corresponds to the first WHEN expression that evaluates to
         1470  +true. ^Or, if none of the WHEN expressions evaluate to true, the result of
         1471  +evaluating the ELSE expression, if any. ^If there is no ELSE expression and
         1472  +none of the WHEN expressions are true, then the overall result is NULL.
         1473  +
         1474  +<p>^A NULL result is considered untrue when evaluating WHEN terms.
         1475  +
         1476  +<p>^In a CASE with a base expression, the base expression is evaluated just
         1477  +once and the result is compared against the evaluation of each WHEN 
         1478  +expression from left to right. ^The result of the CASE expression is the 
         1479  +evaluation of the THEN expression that corresponds to the first WHEN
         1480  +expression for which the comparison is true. ^Or, if none of the WHEN
         1481  +expressions evaluate to a value equal to the base expression, the result
         1482  +of evaluating the ELSE expression, if any. ^If there is no ELSE expression and
         1483  +none of the WHEN expressions produce a result equal to the base expression,
         1484  +the overall result is NULL.
         1485  +
         1486  +<p>^When comparing a base expression against a WHEN expression, the same
  1476   1487   collating sequence, affinity, and NULL-handling rules apply as if the
  1477   1488   base expression and WHEN expression are respectively the left- and
  1478         -right-hand operands of an <big><b>=</b></big> operator.</p>
         1489  +right-hand operands of an <big><b>=</b></big> operator.</p> ^If the base 
         1490  +expression is NULL then the result of the CASE is always the result 
         1491  +of evaluating the ELSE expression if it exists, or NULL if it does not.
  1479   1492   
  1480         -<p>^(Assuming the subexpressions have no side-effects, the following
  1481         -two expressions are equivalent:</p>
         1493  +<p>^Both forms of the CASE expression use lazy, or short-circuit, 
         1494  +evaluation.
  1482   1495   
  1483         -<ul>
         1496  +<p>^(The only difference between the following two CASE expressions is that 
         1497  +the <i>x</i> expression is evaluated exactly once in the first example but 
         1498  +might be evaluated multiple times in the second:
         1499  +
         1500  +<ul><pre>
  1484   1501   <li>CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
  1485   1502   <li>CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
  1486         -</ul>)^
         1503  +</pre></ul>)^
  1487   1504   
  1488         -<p>^The only difference between the two CASE expressions shown above
  1489         -is that the <i>x</i> expression is evaluated
  1490         -exactly once in the first example but might be evaluated multiple times
  1491         -in the second.</p>
  1492         -
  1493         -<p>^A NULL result is considered false when evaluating WHEN terms.
  1494         -^If the base expression is NULL then the result of the CASE is the
  1495         -result of the ELSE expression if it exists, or NULL if the ELSE clause
  1496         -is omitted.</p>
  1497   1505   
  1498   1506   <tcl>hd_fragment in_op {IN operator} {NOT IN operator}</tcl>
  1499   1507   <h3>The IN and NOT IN operators</h3>
  1500   1508   <p>^The IN and NOT IN operators take a single scalar operand on the
  1501   1509   left and a vector operand on the right
  1502   1510   formed by an explicit list of zero or more scalars or by a 
  1503   1511   single subquery.