File:  [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test / e_expr.test
Revision 1.1.1.1 (vendor branch): download - view: text, annotated - select for diffs - revision graph
Tue Feb 21 17:04:16 2012 UTC (12 years, 10 months ago) by misho
Branches: sqlite3, MAIN
CVS tags: v3_7_10, HEAD
sqlite3

    1: # 2010 July 16
    2: #
    3: # The author disclaims copyright to this source code.  In place of
    4: # a legal notice, here is a blessing:
    5: #
    6: #    May you do good and not evil.
    7: #    May you find forgiveness for yourself and forgive others.
    8: #    May you share freely, never taking more than you give.
    9: #
   10: #***********************************************************************
   11: #
   12: # This file implements tests to verify that the "testable statements" in 
   13: # the lang_expr.html document are correct.
   14: #
   15: 
   16: set testdir [file dirname $argv0]
   17: source $testdir/tester.tcl
   18: source $testdir/malloc_common.tcl
   19: 
   20: 
   21: proc do_expr_test {tn expr type value} {
   22:   uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [
   23:     list [list $type $value]
   24:   ]
   25: }
   26: 
   27: proc do_qexpr_test {tn expr value} {
   28:   uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]
   29: }
   30: 
   31: # Set up three global variables:
   32: #
   33: #   ::opname         An array mapping from SQL operator to an easy to parse
   34: #                    name. The names are used as part of test case names.
   35: #
   36: #   ::opprec         An array mapping from SQL operator to a numeric
   37: #                    precedence value. Operators that group more tightly
   38: #                    have lower numeric precedences.
   39: #
   40: #   ::oplist         A list of all SQL operators supported by SQLite.
   41: #
   42: foreach {op opn} {
   43:       ||   cat     *   mul       /  div       %     mod       +      add
   44:       -    sub     <<  lshift    >> rshift    &     bitand    |      bitor
   45:       <    less    <=  lesseq    >  more      >=    moreeq    =      eq1
   46:       ==   eq2     <>  ne1       != ne2       IS    is        LIKE   like
   47:       GLOB glob    AND and       OR or        MATCH match     REGEXP regexp
   48:       {IS NOT} isnt
   49: } {
   50:   set ::opname($op) $opn
   51: }
   52: set oplist [list]
   53: foreach {prec opl} {
   54:   1   ||
   55:   2   {* / %}
   56:   3   {+ -}
   57:   4   {<< >> & |}
   58:   5   {< <= > >=}
   59:   6   {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
   60:   7   AND
   61:   8   OR
   62: } {
   63:   foreach op $opl { 
   64:     set ::opprec($op) $prec 
   65:     lappend oplist $op
   66:   }
   67: }
   68: 
   69: 
   70: # Hook in definitions of MATCH and REGEX. The following implementations
   71: # cause MATCH and REGEX to behave similarly to the == operator.
   72: #
   73: proc matchfunc {a b} { return [expr {$a==$b}] }
   74: proc regexfunc {a b} { return [expr {$a==$b}] }
   75: db func match  -argcount 2 matchfunc
   76: db func regexp -argcount 2 regexfunc
   77: 
   78: #-------------------------------------------------------------------------
   79: # Test cases e_expr-1.* attempt to verify that all binary operators listed
   80: # in the documentation exist and that the relative precedences of the
   81: # operators are also as the documentation suggests.
   82: #
   83: # EVIDENCE-OF: R-15514-65163 SQLite understands the following binary
   84: # operators, in order from highest to lowest precedence: || * / % + -
   85: # << >> & | < <= > >= = == != <> IS IS
   86: # NOT IN LIKE GLOB MATCH REGEXP AND OR
   87: #
   88: # EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same
   89: # precedence as =.
   90: #
   91: 
   92: unset -nocomplain untested
   93: foreach op1 $oplist {
   94:   foreach op2 $oplist {
   95:     set untested($op1,$op2) 1
   96:     foreach {tn A B C} {
   97:        1     22   45    66
   98:        2      0    0     0
   99:        3      0    0     1
  100:        4      0    1     0
  101:        5      0    1     1
  102:        6      1    0     0
  103:        7      1    0     1
  104:        8      1    1     0
  105:        9      1    1     1
  106:       10      5    6     1
  107:       11      1    5     6
  108:       12      1    5     5
  109:       13      5    5     1
  110: 
  111:       14      5    2     1
  112:       15      1    4     1
  113:       16     -1    0     1
  114:       17      0    1    -1
  115: 
  116:     } {
  117:       set testname "e_expr-1.$opname($op1).$opname($op2).$tn"
  118: 
  119:       # If $op2 groups more tightly than $op1, then the result
  120:       # of executing $sql1 whould be the same as executing $sql3.
  121:       # If $op1 groups more tightly, or if $op1 and $op2 have 
  122:       # the same precedence, then executing $sql1 should return
  123:       # the same value as $sql2.
  124:       #
  125:       set sql1 "SELECT $A $op1 $B $op2 $C"
  126:       set sql2 "SELECT ($A $op1 $B) $op2 $C"
  127:       set sql3 "SELECT $A $op1 ($B $op2 $C)"
  128: 
  129:       set a2 [db one $sql2]
  130:       set a3 [db one $sql3]
  131: 
  132:       do_execsql_test $testname $sql1 [list [
  133:         if {$opprec($op2) < $opprec($op1)} {set a3} {set a2}
  134:       ]]
  135:       if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) }
  136:     }
  137:   }
  138: }
  139: 
  140: foreach op {* AND OR + || & |} { unset untested($op,$op) }
  141: unset untested(+,-)  ;#       Since    (a+b)-c == a+(b-c)
  142: unset untested(*,<<) ;#       Since    (a*b)<<c == a*(b<<c)
  143: 
  144: do_test e_expr-1.1 { array names untested } {}
  145: 
  146: # At one point, test 1.2.2 was failing. Instead of the correct result, it
  147: # was returning {1 1 0}. This would seem to indicate that LIKE has the
  148: # same precedence as '<'. Which is incorrect. It has lower precedence.
  149: #
  150: do_execsql_test e_expr-1.2.1 { 
  151:   SELECT 0 < 2 LIKE 1,   (0 < 2) LIKE 1,   0 < (2 LIKE 1)
  152: } {1 1 0}
  153: do_execsql_test e_expr-1.2.2 { 
  154:   SELECT 0 LIKE 0 < 2,   (0 LIKE 0) < 2,   0 LIKE (0 < 2)
  155: } {0 1 0}
  156: 
  157: # Showing that LIKE and == have the same precedence
  158: #
  159: do_execsql_test e_expr-1.2.3 { 
  160:   SELECT 2 LIKE 2 == 1,   (2 LIKE 2) == 1,    2 LIKE (2 == 1)
  161: } {1 1 0}
  162: do_execsql_test e_expr-1.2.4 { 
  163:   SELECT 2 == 2 LIKE 1,   (2 == 2) LIKE 1,    2 == (2 LIKE 1)
  164: } {1 1 0}
  165: 
  166: # Showing that < groups more tightly than == (< has higher precedence). 
  167: #
  168: do_execsql_test e_expr-1.2.5 { 
  169:   SELECT 0 < 2 == 1,   (0 < 2) == 1,   0 < (2 == 1)
  170: } {1 1 0}
  171: do_execsql_test e_expr-1.6 { 
  172:   SELECT 0 == 0 < 2,   (0 == 0) < 2,   0 == (0 < 2)
  173: } {0 1 0}
  174: 
  175: #-------------------------------------------------------------------------
  176: # Check that the four unary prefix operators mentioned in the 
  177: # documentation exist.
  178: #
  179: # EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these:
  180: # - + ~ NOT
  181: #
  182: do_execsql_test e_expr-2.1 { SELECT -   10   } {-10}
  183: do_execsql_test e_expr-2.2 { SELECT +   10   } {10}
  184: do_execsql_test e_expr-2.3 { SELECT ~   10   } {-11}
  185: do_execsql_test e_expr-2.4 { SELECT NOT 10   } {0}
  186: 
  187: #-------------------------------------------------------------------------
  188: # Tests for the two statements made regarding the unary + operator.
  189: #
  190: # EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op.
  191: #
  192: # EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers,
  193: # blobs or NULL and it always returns a result with the same value as
  194: # the operand.
  195: #
  196: foreach {tn literal type} {
  197:   1     'helloworld'   text
  198:   2     45             integer
  199:   3     45.2           real
  200:   4     45.0           real
  201:   5     X'ABCDEF'      blob
  202:   6     NULL           null
  203: } {
  204:   set sql " SELECT quote( + $literal ), typeof( + $literal) "
  205:   do_execsql_test e_expr-3.$tn $sql [list $literal $type]
  206: }
  207: 
  208: #-------------------------------------------------------------------------
  209: # Check that both = and == are both acceptable as the "equals" operator.
  210: # Similarly, either != or <> work as the not-equals operator.
  211: #
  212: # EVIDENCE-OF: R-03679-60639 Equals can be either = or ==.
  213: #
  214: # EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or
  215: # <>.
  216: #
  217: foreach {tn literal different} {
  218:   1   'helloworld'  '12345'
  219:   2   22            23
  220:   3   'xyz'         X'78797A'
  221:   4   X'78797A00'   'xyz'
  222: } {
  223:   do_execsql_test e_expr-4.$tn "
  224:     SELECT $literal  = $literal,   $literal == $literal,
  225:            $literal  = $different, $literal == $different,
  226:            $literal  = NULL,       $literal == NULL,
  227:            $literal != $literal,   $literal <> $literal,
  228:            $literal != $different, $literal <> $different,
  229:            $literal != NULL,       $literal != NULL
  230: 
  231:   " {1 1 0 0 {} {} 0 0 1 1 {} {}}
  232: }
  233: 
  234: #-------------------------------------------------------------------------
  235: # Test the || operator.
  236: #
  237: # EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins
  238: # together the two strings of its operands.
  239: #
  240: foreach {tn a b} {
  241:   1   'helloworld'  '12345'
  242:   2   22            23
  243: } {
  244:   set as [db one "SELECT $a"]
  245:   set bs [db one "SELECT $b"]
  246:   
  247:   do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"]
  248: }
  249: 
  250: #-------------------------------------------------------------------------
  251: # Test the % operator.
  252: #
  253: # EVIDENCE-OF: R-08914-63790 The operator % outputs the value of its
  254: # left operand modulo its right operand.
  255: #
  256: do_execsql_test e_expr-6.1 {SELECT  72%5}  {2}
  257: do_execsql_test e_expr-6.2 {SELECT  72%-5} {2}
  258: do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2}
  259: do_execsql_test e_expr-6.4 {SELECT -72%5}  {-2}
  260: 
  261: #-------------------------------------------------------------------------
  262: # Test that the results of all binary operators are either numeric or 
  263: # NULL, except for the || operator, which may evaluate to either a text
  264: # value or NULL.
  265: #
  266: # EVIDENCE-OF: R-20665-17792 The result of any binary operator is either
  267: # a numeric value or NULL, except for the || concatenation operator
  268: # which always evaluates to either NULL or a text value.
  269: #
  270: set literals {
  271:   1 'abc'        2 'hexadecimal'       3 ''
  272:   4 123          5 -123                6 0
  273:   7 123.4        8 0.0                 9 -123.4
  274:  10 X'ABCDEF'   11 X''                12 X'0000'
  275:  13     NULL
  276: }
  277: foreach op $oplist {
  278:   foreach {n1 rhs} $literals { 
  279:   foreach {n2 lhs} $literals {
  280: 
  281:     set t [db one " SELECT typeof($lhs $op $rhs) "]
  282:     do_test e_expr-7.$opname($op).$n1.$n2 {
  283:       expr {
  284:            ($op=="||" && ($t == "text" || $t == "null"))
  285:         || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null"))
  286:       }
  287:     } 1
  288: 
  289:   }}
  290: }
  291: 
  292: #-------------------------------------------------------------------------
  293: # Test the IS and IS NOT operators.
  294: #
  295: # EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and
  296: # != except when one or both of the operands are NULL.
  297: #
  298: # EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL,
  299: # then the IS operator evaluates to 1 (true) and the IS NOT operator
  300: # evaluates to 0 (false).
  301: #
  302: # EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is
  303: # not, then the IS operator evaluates to 0 (false) and the IS NOT
  304: # operator is 1 (true).
  305: #
  306: # EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT
  307: # expression to evaluate to NULL.
  308: #
  309: do_execsql_test e_expr-8.1.1  { SELECT NULL IS     NULL } {1}
  310: do_execsql_test e_expr-8.1.2  { SELECT 'ab' IS     NULL } {0}
  311: do_execsql_test e_expr-8.1.3  { SELECT NULL IS     'ab' } {0}
  312: do_execsql_test e_expr-8.1.4  { SELECT 'ab' IS     'ab' } {1}
  313: do_execsql_test e_expr-8.1.5  { SELECT NULL ==     NULL } {{}}
  314: do_execsql_test e_expr-8.1.6  { SELECT 'ab' ==     NULL } {{}}
  315: do_execsql_test e_expr-8.1.7  { SELECT NULL ==     'ab' } {{}}
  316: do_execsql_test e_expr-8.1.8  { SELECT 'ab' ==     'ab' } {1}
  317: do_execsql_test e_expr-8.1.9  { SELECT NULL IS NOT NULL } {0}
  318: do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1}
  319: do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1}
  320: do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0}
  321: do_execsql_test e_expr-8.1.13 { SELECT NULL !=     NULL } {{}}
  322: do_execsql_test e_expr-8.1.14 { SELECT 'ab' !=     NULL } {{}}
  323: do_execsql_test e_expr-8.1.15 { SELECT NULL !=     'ab' } {{}}
  324: do_execsql_test e_expr-8.1.16 { SELECT 'ab' !=     'ab' } {0}
  325: 
  326: foreach {n1 rhs} $literals { 
  327:   foreach {n2 lhs} $literals {
  328:     if {$rhs!="NULL" && $lhs!="NULL"} {
  329:       set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"]
  330:     } else {
  331:       set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \
  332:                    [expr {$lhs!="NULL" || $rhs!="NULL"}]
  333:       ]
  334:     }
  335:     set test e_expr-8.2.$n1.$n2
  336:     do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq
  337:     do_execsql_test $test.2 "
  338:       SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL
  339:     " {0 0}
  340:   }
  341: }
  342: 
  343: #-------------------------------------------------------------------------
  344: # Run some tests on the COLLATE "unary postfix operator".
  345: #
  346: # This collation sequence reverses both arguments before using 
  347: # [string compare] to compare them. For example, when comparing the
  348: # strings 'one' and 'four', return the result of:
  349: #   
  350: #   string compare eno ruof
  351: #
  352: proc reverse_str {zStr} {
  353:   set out ""
  354:   foreach c [split $zStr {}] { set out "${c}${out}" }
  355:   set out
  356: }
  357: proc reverse_collate {zLeft zRight} {
  358:   string compare [reverse_str $zLeft] [reverse_str $zRight]
  359: }
  360: db collate reverse reverse_collate
  361: 
  362: # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
  363: # operator that assigns a collating sequence to an expression.
  364: #
  365: # EVIDENCE-OF: R-23441-22541 The COLLATE operator has a higher
  366: # precedence (binds more tightly) than any prefix unary operator or any
  367: # binary operator.
  368: #
  369: do_execsql_test e_expr-9.1 { SELECT  'abcd' < 'bbbb'    COLLATE reverse } 0
  370: do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb')   COLLATE reverse } 1
  371: do_execsql_test e_expr-9.3 { SELECT  'abcd' <= 'bbbb'   COLLATE reverse } 0
  372: do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb')  COLLATE reverse } 1
  373: 
  374: do_execsql_test e_expr-9.5 { SELECT  'abcd' > 'bbbb'    COLLATE reverse } 1
  375: do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb')   COLLATE reverse } 0
  376: do_execsql_test e_expr-9.7 { SELECT  'abcd' >= 'bbbb'   COLLATE reverse } 1
  377: do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb')  COLLATE reverse } 0
  378: 
  379: do_execsql_test e_expr-9.10 { SELECT  'abcd' =  'ABCD'  COLLATE nocase } 1
  380: do_execsql_test e_expr-9.11 { SELECT ('abcd' =  'ABCD') COLLATE nocase } 0
  381: do_execsql_test e_expr-9.12 { SELECT  'abcd' == 'ABCD'  COLLATE nocase } 1
  382: do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0
  383: do_execsql_test e_expr-9.14 { SELECT  'abcd' IS 'ABCD'  COLLATE nocase } 1
  384: do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0
  385: 
  386: do_execsql_test e_expr-9.16 { SELECT  'abcd' != 'ABCD'      COLLATE nocase } 0
  387: do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD')     COLLATE nocase } 1
  388: do_execsql_test e_expr-9.18 { SELECT  'abcd' <> 'ABCD'      COLLATE nocase } 0
  389: do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD')     COLLATE nocase } 1
  390: do_execsql_test e_expr-9.20 { SELECT  'abcd' IS NOT 'ABCD'  COLLATE nocase } 0
  391: do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1
  392: 
  393: do_execsql_test e_expr-9.22 { 
  394:   SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase 
  395: } 1
  396: do_execsql_test e_expr-9.23 { 
  397:   SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase 
  398: } 0
  399: 
  400: # EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE
  401: # operator overrides the collating sequence determined by the COLLATE
  402: # clause in a table column definition.
  403: #
  404: do_execsql_test e_expr-9.24 { 
  405:   CREATE TABLE t24(a COLLATE NOCASE, b);
  406:   INSERT INTO t24 VALUES('aaa', 1);
  407:   INSERT INTO t24 VALUES('bbb', 2);
  408:   INSERT INTO t24 VALUES('ccc', 3);
  409: } {}
  410: do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0}
  411: do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0}
  412: do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0}
  413: do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0}
  414: 
  415: #-------------------------------------------------------------------------
  416: # Test statements related to literal values.
  417: #
  418: # EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating
  419: # point numbers, strings, BLOBs, or NULLs.
  420: #
  421: do_execsql_test e_expr-10.1.1 { SELECT typeof(5)       } {integer}
  422: do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1)     } {real}
  423: do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1')   } {text}
  424: do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob}
  425: do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL)    } {null}
  426: 
  427: # "Scientific notation is supported for point literal values."
  428: #
  429: do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02)    } {real}
  430: do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5)       } {real}
  431: do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02            } {0.034}
  432: do_execsql_test e_expr-10.2.4 { SELECT 3e+4               } {30000.0}
  433: 
  434: # EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing
  435: # the string in single quotes (').
  436: #
  437: # EVIDENCE-OF: R-07100-06606 A single quote within the string can be
  438: # encoded by putting two single quotes in a row - as in Pascal.
  439: #
  440: do_execsql_test e_expr-10.3.1 { SELECT 'is not' }         {{is not}}
  441: do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text}
  442: do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' }         {isn't}
  443: do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text}
  444: 
  445: # EVIDENCE-OF: R-09593-03321 BLOB literals are string literals
  446: # containing hexadecimal data and preceded by a single "x" or "X"
  447: # character.
  448: #
  449: # EVIDENCE-OF: R-39344-59787 For example: X'53514C697465'
  450: #
  451: do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob
  452: do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob
  453: do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob
  454: do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob
  455: do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465')     } blob
  456: 
  457: # EVIDENCE-OF: R-23914-51476 A literal value can also be the token
  458: # "NULL".
  459: #
  460: do_execsql_test e_expr-10.5.1 { SELECT NULL         } {{}}
  461: do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null}
  462: 
  463: #-------------------------------------------------------------------------
  464: # Test statements related to bound parameters
  465: #
  466: 
  467: proc parameter_test {tn sql params result} {
  468:   set stmt [sqlite3_prepare_v2 db $sql -1]
  469: 
  470:   foreach {number name} $params {
  471:     set nm [sqlite3_bind_parameter_name $stmt $number]
  472:     do_test $tn.name.$number [list set {} $nm] $name
  473:     sqlite3_bind_int $stmt $number [expr -1 * $number]
  474:   }
  475: 
  476:   sqlite3_step $stmt
  477: 
  478:   set res [list]
  479:   for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {
  480:     lappend res [sqlite3_column_text $stmt $i]
  481:   }
  482: 
  483:   set rc [sqlite3_finalize $stmt]
  484:   do_test $tn.rc [list set {} $rc] SQLITE_OK
  485:   do_test $tn.res [list set {} $res] $result
  486: }
  487: 
  488: # EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN
  489: # holds a spot for the NNN-th parameter. NNN must be between 1 and
  490: # SQLITE_MAX_VARIABLE_NUMBER.
  491: #
  492: set mvn $SQLITE_MAX_VARIABLE_NUMBER
  493: parameter_test e_expr-11.1 "
  494:   SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4
  495: "   "1 ?1  123 ?123 $mvn ?$mvn 4 ?4"   "-1 -123 -$mvn -123 -4"
  496: 
  497: set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER"
  498: foreach {tn param_number} [list \
  499:   2  0                                    \
  500:   3  [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \
  501:   4  [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \
  502:   5  12345678903456789034567890234567890  \
  503:   6  2147483648                           \
  504:   7  2147483649                           \
  505:   8  4294967296                           \
  506:   9  4294967297                           \
  507:   10 9223372036854775808                  \
  508:   11 9223372036854775809                  \
  509:   12 18446744073709551616                 \
  510:   13 18446744073709551617                 \
  511: ] {
  512:   do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg]
  513: }
  514: 
  515: # EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a
  516: # number creates a parameter with a number one greater than the largest
  517: # parameter number already assigned.
  518: #
  519: # EVIDENCE-OF: R-42938-07030 If this means the parameter number is
  520: # greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error.
  521: #
  522: parameter_test e_expr-11.2.1 "SELECT ?"          {1 {}}       -1
  523: parameter_test e_expr-11.2.2 "SELECT ?, ?"       {1 {} 2 {}}  {-1 -2}
  524: parameter_test e_expr-11.2.3 "SELECT ?5, ?"      {5 ?5 6 {}}  {-5 -6}
  525: parameter_test e_expr-11.2.4 "SELECT ?, ?5"      {1 {} 5 ?5}  {-1 -5}
  526: parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" {
  527:   1 {} 456 ?456 457 {}
  528: }  {-1 -456 -457}
  529: parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" {
  530:   1 {} 456 ?456 4 ?4 457 {}
  531: }  {-1 -456 -4 -457}
  532: foreach {tn sql} [list                           \
  533:   1  "SELECT ?$mvn, ?"                           \
  534:   2  "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?"   \
  535:   3  "SELECT ?[expr $mvn], ?5, ?6, ?"            \
  536: ] {
  537:   do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}]
  538: }
  539: 
  540: # EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name
  541: # holds a spot for a named parameter with the name :AAAA.
  542: #
  543: # Identifiers in SQLite consist of alphanumeric, '_' and '$' characters,
  544: # and any UTF characters with codepoints larger than 127 (non-ASCII 
  545: # characters).
  546: #
  547: parameter_test e_expr-11.2.1 {SELECT :AAAA}         {1 :AAAA}       -1
  548: parameter_test e_expr-11.2.2 {SELECT :123}          {1 :123}        -1
  549: parameter_test e_expr-11.2.3 {SELECT :__}           {1 :__}         -1
  550: parameter_test e_expr-11.2.4 {SELECT :_$_}          {1 :_$_}        -1
  551: parameter_test e_expr-11.2.5 "
  552:   SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
  553: " "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
  554: parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1
  555: 
  556: # EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon,
  557: # except that the name of the parameter created is @AAAA.
  558: #
  559: parameter_test e_expr-11.3.1 {SELECT @AAAA}         {1 @AAAA}       -1
  560: parameter_test e_expr-11.3.2 {SELECT @123}          {1 @123}        -1
  561: parameter_test e_expr-11.3.3 {SELECT @__}           {1 @__}         -1
  562: parameter_test e_expr-11.3.4 {SELECT @_$_}          {1 @_$_}        -1
  563: parameter_test e_expr-11.3.5 "
  564:   SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
  565: " "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
  566: parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1
  567: 
  568: # EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier
  569: # name also holds a spot for a named parameter with the name $AAAA.
  570: #
  571: # EVIDENCE-OF: R-55025-21042 The identifier name in this case can
  572: # include one or more occurrences of "::" and a suffix enclosed in
  573: # "(...)" containing any text at all.
  574: #
  575: # Note: Looks like an identifier cannot consist entirely of "::" 
  576: # characters or just a suffix. Also, the other named variable characters
  577: # (: and @) work the same way internally. Why not just document it that way?
  578: #
  579: parameter_test e_expr-11.4.1 {SELECT $AAAA}         {1 $AAAA}       -1
  580: parameter_test e_expr-11.4.2 {SELECT $123}          {1 $123}        -1
  581: parameter_test e_expr-11.4.3 {SELECT $__}           {1 $__}         -1
  582: parameter_test e_expr-11.4.4 {SELECT $_$_}          {1 $_$_}        -1
  583: parameter_test e_expr-11.4.5 "
  584:   SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
  585: " "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
  586: parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1
  587: 
  588: parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1
  589: parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1
  590: parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1
  591:  
  592: # EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The
  593: # number assigned is one greater than the largest parameter number
  594: # already assigned.
  595: #
  596: # EVIDENCE-OF: R-42620-22184 If this means the parameter would be
  597: # assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an
  598: # error.
  599: #
  600: parameter_test e_expr-11.6.1 "SELECT ?, @abc"    {1 {} 2 @abc} {-1 -2}
  601: parameter_test e_expr-11.6.2 "SELECT ?123, :a1"  {123 ?123 124 :a1} {-123 -124}
  602: parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} {
  603:   1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c
  604: } {-1 -8 -9 -10 -2 -11}
  605: foreach {tn sql} [list                           \
  606:   1  "SELECT ?$mvn, \$::a"                       \
  607:   2  "SELECT ?$mvn, ?4, @a1"                     \
  608:   3  "SELECT ?[expr $mvn-2], :bag, @123, \$x"    \
  609: ] {
  610:   do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}]
  611: }
  612: 
  613: # EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values
  614: # using sqlite3_bind() are treated as NULL.
  615: #
  616: do_test e_expr-11.7.1 {
  617:   set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1]
  618:   sqlite3_step $stmt
  619: 
  620:   list [sqlite3_column_type $stmt 0] \
  621:        [sqlite3_column_type $stmt 1] \
  622:        [sqlite3_column_type $stmt 2] \
  623:        [sqlite3_column_type $stmt 3] 
  624: } {NULL NULL NULL NULL}
  625: do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK
  626: 
  627: #-------------------------------------------------------------------------
  628: # "Test" the syntax diagrams in lang_expr.html.
  629: #
  630: # EVIDENCE-OF: R-02989-21050 -- syntax diagram signed-number
  631: #
  632: do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
  633: do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
  634: do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
  635: do_execsql_test e_expr-12.1.4 { 
  636:   SELECT 1.4, +1.4, -1.4 
  637: } {1.4 1.4 -1.4}
  638: do_execsql_test e_expr-12.1.5 { 
  639:   SELECT 1.5e+5, +1.5e+5, -1.5e+5 
  640: } {150000.0 150000.0 -150000.0}
  641: do_execsql_test e_expr-12.1.6 { 
  642:   SELECT 0.0001, +0.0001, -0.0001 
  643: } {0.0001 0.0001 -0.0001}
  644: 
  645: # EVIDENCE-OF: R-43188-60852 -- syntax diagram literal-value
  646: #
  647: set sqlite_current_time 1
  648: do_execsql_test e_expr-12.2.1 {SELECT 123}               {123}
  649: do_execsql_test e_expr-12.2.2 {SELECT 123.4e05}          {12340000.0}
  650: do_execsql_test e_expr-12.2.3 {SELECT 'abcde'}           {abcde}
  651: do_execsql_test e_expr-12.2.4 {SELECT X'414243'}         {ABC}
  652: do_execsql_test e_expr-12.2.5 {SELECT NULL}              {{}}
  653: do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME}      {00:00:01}
  654: do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE}      {1970-01-01}
  655: do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
  656: set sqlite_current_time 0
  657: 
  658: # EVIDENCE-OF: R-50544-32159 -- syntax diagram expr
  659: #
  660: forcedelete test.db2
  661: execsql {
  662:   ATTACH 'test.db2' AS dbname;
  663:   CREATE TABLE dbname.tblname(cname);
  664: }
  665: 
  666: proc glob {args} {return 1}
  667: db function glob glob
  668: db function match glob
  669: db function regexp glob
  670: 
  671: foreach {tn expr} {
  672:   1 123
  673:   2 123.4e05
  674:   3 'abcde'
  675:   4 X'414243'
  676:   5 NULL
  677:   6 CURRENT_TIME
  678:   7 CURRENT_DATE
  679:   8 CURRENT_TIMESTAMP
  680: 
  681:   9 ?
  682:  10 ?123
  683:  11 @hello
  684:  12 :world
  685:  13 $tcl
  686:  14 $tcl(array)
  687:   
  688:   15 cname
  689:   16 tblname.cname
  690:   17 dbname.tblname.cname
  691: 
  692:   18 "+ EXPR"
  693:   19 "- EXPR"
  694:   20 "NOT EXPR"
  695:   21 "~ EXPR"
  696: 
  697:   22 "EXPR1 || EXPR2"
  698:   23 "EXPR1 * EXPR2"
  699:   24 "EXPR1 / EXPR2"
  700:   25 "EXPR1 % EXPR2"
  701:   26 "EXPR1 + EXPR2"
  702:   27 "EXPR1 - EXPR2"
  703:   28 "EXPR1 << EXPR2"
  704:   29 "EXPR1 >> EXPR2"
  705:   30 "EXPR1 & EXPR2"
  706:   31 "EXPR1 | EXPR2"
  707:   32 "EXPR1 < EXPR2"
  708:   33 "EXPR1 <= EXPR2"
  709:   34 "EXPR1 > EXPR2"
  710:   35 "EXPR1 >= EXPR2"
  711:   36 "EXPR1 = EXPR2"
  712:   37 "EXPR1 == EXPR2"
  713:   38 "EXPR1 != EXPR2"
  714:   39 "EXPR1 <> EXPR2"
  715:   40 "EXPR1 IS EXPR2"
  716:   41 "EXPR1 IS NOT EXPR2"
  717:   42 "EXPR1 AND EXPR2"
  718:   43 "EXPR1 OR EXPR2"
  719:  
  720:   44 "count(*)"
  721:   45 "count(DISTINCT EXPR)"
  722:   46 "substr(EXPR, 10, 20)"
  723:   47 "changes()"
  724:  
  725:   48 "( EXPR )"
  726:  
  727:   49 "CAST ( EXPR AS integer )"
  728:   50 "CAST ( EXPR AS 'abcd' )"
  729:   51 "CAST ( EXPR AS 'ab$ $cd' )"
  730:  
  731:   52 "EXPR COLLATE nocase"
  732:   53 "EXPR COLLATE binary"
  733:  
  734:   54 "EXPR1 LIKE EXPR2"
  735:   55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
  736:   56 "EXPR1 GLOB EXPR2"
  737:   57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
  738:   58 "EXPR1 REGEXP EXPR2"
  739:   59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
  740:   60 "EXPR1 MATCH EXPR2"
  741:   61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
  742:   62 "EXPR1 NOT LIKE EXPR2"
  743:   63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
  744:   64 "EXPR1 NOT GLOB EXPR2"
  745:   65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
  746:   66 "EXPR1 NOT REGEXP EXPR2"
  747:   67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
  748:   68 "EXPR1 NOT MATCH EXPR2"
  749:   69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"
  750:  
  751:   70 "EXPR ISNULL"
  752:   71 "EXPR NOTNULL"
  753:   72 "EXPR NOT NULL"
  754:  
  755:   73 "EXPR1 IS EXPR2"
  756:   74 "EXPR1 IS NOT EXPR2"
  757: 
  758:   75 "EXPR NOT BETWEEN EXPR1 AND EXPR2"
  759:   76 "EXPR BETWEEN EXPR1 AND EXPR2"
  760: 
  761:   77 "EXPR NOT IN (SELECT cname FROM tblname)"
  762:   78 "EXPR NOT IN (1)"
  763:   79 "EXPR NOT IN (1, 2, 3)"
  764:   80 "EXPR NOT IN tblname"
  765:   81 "EXPR NOT IN dbname.tblname"
  766:   82 "EXPR IN (SELECT cname FROM tblname)"
  767:   83 "EXPR IN (1)"
  768:   84 "EXPR IN (1, 2, 3)"
  769:   85 "EXPR IN tblname"
  770:   86 "EXPR IN dbname.tblname"
  771: 
  772:   87 "EXISTS (SELECT cname FROM tblname)"
  773:   88 "NOT EXISTS (SELECT cname FROM tblname)"
  774: 
  775:   89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
  776:   90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END"
  777:   91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
  778:   92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
  779:   93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
  780:   94 "CASE WHEN EXPR1 THEN EXPR2 END"
  781:   95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
  782:   96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
  783: } {
  784: 
  785:   # If the expression string being parsed contains "EXPR2", then replace
  786:   # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it 
  787:   # contains "EXPR", then replace EXPR with an arbitrary SQL expression.
  788:   # 
  789:   set elist [list $expr]
  790:   if {[string match *EXPR2* $expr]} {
  791:     set elist [list]
  792:     foreach {e1 e2} { cname "34+22" } {
  793:       lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr]
  794:     }
  795:   } 
  796:   if {[string match *EXPR* $expr]} {
  797:     set elist2 [list]
  798:     foreach el $elist {
  799:       foreach e { cname "34+22" } {
  800:         lappend elist2 [string map [list EXPR $e] $el]
  801:       }
  802:     }
  803:     set elist $elist2
  804:   }
  805: 
  806:   set x 0
  807:   foreach e $elist {
  808:     incr x
  809:     do_test e_expr-12.3.$tn.$x { 
  810:       set rc [catch { execsql "SELECT $e FROM tblname" } msg]
  811:     } {0}
  812:   }
  813: }
  814: 
  815: # EVIDENCE-OF: R-39820-63916 -- syntax diagram raise-function
  816: #
  817: foreach {tn raiseexpr} {
  818:   1 "RAISE(IGNORE)"
  819:   2 "RAISE(ROLLBACK, 'error message')"
  820:   3 "RAISE(ABORT, 'error message')"
  821:   4 "RAISE(FAIL, 'error message')"
  822: } {
  823:   do_execsql_test e_expr-12.4.$tn "
  824:     CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN
  825:       SELECT $raiseexpr ;
  826:     END;
  827:   " {}
  828: }
  829: 
  830: #-------------------------------------------------------------------------
  831: # Test the statements related to the BETWEEN operator.
  832: #
  833: # EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically
  834: # equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent
  835: # to "x>=y AND x<=z" except that with BETWEEN, the x expression is
  836: # only evaluated once.
  837: #
  838: db func x x
  839: proc x {} { incr ::xcount ; return [expr $::x] }
  840: foreach {tn x expr res nEval} {
  841:   1  10  "x() >= 5 AND x() <= 15"  1  2
  842:   2  10  "x() BETWEEN 5 AND 15"    1  1
  843: 
  844:   3   5  "x() >= 5 AND x() <= 5"   1  2
  845:   4   5  "x() BETWEEN 5 AND 5"     1  1
  846: } {
  847:   do_test e_expr-13.1.$tn {
  848:     set ::xcount 0
  849:     set a [execsql "SELECT $expr"]
  850:     list $::xcount $a
  851:   } [list $nEval $res]
  852: }
  853: 
  854: # EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is
  855: # the same as the precedence as operators == and != and LIKE and groups
  856: # left to right.
  857: # 
  858: # Therefore, BETWEEN groups more tightly than operator "AND", but less
  859: # so than "<".
  860: #
  861: do_execsql_test e_expr-13.2.1  { SELECT 1 == 10 BETWEEN 0 AND 2   }  1
  862: do_execsql_test e_expr-13.2.2  { SELECT (1 == 10) BETWEEN 0 AND 2 }  1
  863: do_execsql_test e_expr-13.2.3  { SELECT 1 == (10 BETWEEN 0 AND 2) }  0
  864: do_execsql_test e_expr-13.2.4  { SELECT  6 BETWEEN 4 AND 8 == 1 }    1
  865: do_execsql_test e_expr-13.2.5  { SELECT (6 BETWEEN 4 AND 8) == 1 }   1
  866: do_execsql_test e_expr-13.2.6  { SELECT  6 BETWEEN 4 AND (8 == 1) }  0
  867: 
  868: do_execsql_test e_expr-13.2.7  { SELECT  5 BETWEEN 0 AND 0  != 1 }   1
  869: do_execsql_test e_expr-13.2.8  { SELECT (5 BETWEEN 0 AND 0) != 1 }   1
  870: do_execsql_test e_expr-13.2.9  { SELECT  5 BETWEEN 0 AND (0 != 1) }  0
  871: do_execsql_test e_expr-13.2.10 { SELECT  1 != 0  BETWEEN 0 AND 2  }  1
  872: do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2  }  1
  873: do_execsql_test e_expr-13.2.12 { SELECT  1 != (0 BETWEEN 0 AND 2) }  0
  874: 
  875: do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2   }  1
  876: do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 }  1
  877: do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) }  0
  878: do_execsql_test e_expr-13.2.16 { SELECT  6 BETWEEN 4 AND 8 LIKE 1   }  1
  879: do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1  }  1
  880: do_execsql_test e_expr-13.2.18 { SELECT  6 BETWEEN 4 AND (8 LIKE 1) }  0
  881: 
  882: do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1   } 0
  883: do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0
  884: do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1
  885: do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0   } 0
  886: do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0
  887: do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1
  888: 
  889: do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1   } 1
  890: do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1
  891: do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0
  892: do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3    } 0
  893: do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3)  } 0
  894: do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3  } 1
  895: 
  896: #-------------------------------------------------------------------------
  897: # Test the statements related to the LIKE and GLOB operators.
  898: #
  899: # EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching
  900: # comparison.
  901: #
  902: # EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE
  903: # operator contains the pattern and the left hand operand contains the
  904: # string to match against the pattern.
  905: #
  906: do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0
  907: do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1
  908: 
  909: # EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern
  910: # matches any sequence of zero or more characters in the string.
  911: #
  912: do_execsql_test e_expr-14.2.1 { SELECT 'abde'    LIKE 'ab%de' } 1
  913: do_execsql_test e_expr-14.2.2 { SELECT 'abXde'   LIKE 'ab%de' } 1
  914: do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1
  915: 
  916: # EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern
  917: # matches any single character in the string.
  918: #
  919: do_execsql_test e_expr-14.3.1 { SELECT 'abde'    LIKE 'ab_de' } 0
  920: do_execsql_test e_expr-14.3.2 { SELECT 'abXde'   LIKE 'ab_de' } 1
  921: do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0
  922: 
  923: # EVIDENCE-OF: R-59007-20454 Any other character matches itself or its
  924: # lower/upper case equivalent (i.e. case-insensitive matching).
  925: #
  926: do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1
  927: do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1
  928: do_execsql_test e_expr-14.4.3 { SELECT 'ac'  LIKE 'aBc' } 0
  929: 
  930: # EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case
  931: # for ASCII characters by default.
  932: #
  933: # EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
  934: # default for unicode characters that are beyond the ASCII range.
  935: #
  936: # EVIDENCE-OF: R-44381-11669 the expression
  937: # 'a'&nbsp;LIKE&nbsp;'A' is TRUE but
  938: # '&aelig;'&nbsp;LIKE&nbsp;'&AElig;' is FALSE.
  939: #
  940: #   The restriction to ASCII characters does not apply if the ICU
  941: #   library is compiled in. When ICU is enabled SQLite does not act
  942: #   as it does "by default".
  943: #
  944: do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a'         } 1
  945: ifcapable !icu {
  946:   do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0
  947: }
  948: 
  949: # EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
  950: # then the expression following the ESCAPE keyword must evaluate to a
  951: # string consisting of a single character.
  952: #
  953: do_catchsql_test e_expr-14.6.1 { 
  954:   SELECT 'A' LIKE 'a' ESCAPE '12' 
  955: } {1 {ESCAPE expression must be a single character}}
  956: do_catchsql_test e_expr-14.6.2 { 
  957:   SELECT 'A' LIKE 'a' ESCAPE '' 
  958: } {1 {ESCAPE expression must be a single character}}
  959: do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' }    {0 1}
  960: do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1}
  961: 
  962: # EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE
  963: # pattern to include literal percent or underscore characters.
  964: #
  965: # EVIDENCE-OF: R-13345-31830 The escape character followed by a percent
  966: # symbol (%), underscore (_), or a second instance of the escape
  967: # character itself matches a literal percent symbol, underscore, or a
  968: # single escape character, respectively.
  969: #
  970: do_execsql_test e_expr-14.7.1  { SELECT 'abc%'  LIKE 'abcX%' ESCAPE 'X' } 1
  971: do_execsql_test e_expr-14.7.2  { SELECT 'abc5'  LIKE 'abcX%' ESCAPE 'X' } 0
  972: do_execsql_test e_expr-14.7.3  { SELECT 'abc'   LIKE 'abcX%' ESCAPE 'X' } 0
  973: do_execsql_test e_expr-14.7.4  { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0
  974: do_execsql_test e_expr-14.7.5  { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0
  975: 
  976: do_execsql_test e_expr-14.7.6  { SELECT 'abc_'  LIKE 'abcX_' ESCAPE 'X' } 1
  977: do_execsql_test e_expr-14.7.7  { SELECT 'abc5'  LIKE 'abcX_' ESCAPE 'X' } 0
  978: do_execsql_test e_expr-14.7.8  { SELECT 'abc'   LIKE 'abcX_' ESCAPE 'X' } 0
  979: do_execsql_test e_expr-14.7.9  { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0
  980: do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0
  981: 
  982: do_execsql_test e_expr-14.7.11 { SELECT 'abcX'  LIKE 'abcXX' ESCAPE 'X' } 1
  983: do_execsql_test e_expr-14.7.12 { SELECT 'abc5'  LIKE 'abcXX' ESCAPE 'X' } 0
  984: do_execsql_test e_expr-14.7.13 { SELECT 'abc'   LIKE 'abcXX' ESCAPE 'X' } 0
  985: do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0
  986: 
  987: # EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
  988: # calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
  989: #
  990: proc likefunc {args} {
  991:   eval lappend ::likeargs $args
  992:   return 1
  993: }
  994: db func like -argcount 2 likefunc
  995: db func like -argcount 3 likefunc
  996: set ::likeargs [list]
  997: do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
  998: do_test         e_expr-15.1.2 { set likeargs } {def abc}
  999: set ::likeargs [list]
 1000: do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
 1001: do_test         e_expr-15.1.4 { set likeargs } {def abc X}
 1002: db close
 1003: sqlite3 db test.db
 1004: 
 1005: # EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case
 1006: # sensitive using the case_sensitive_like pragma.
 1007: #
 1008: do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1
 1009: do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {}
 1010: do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0
 1011: do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
 1012: do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {}
 1013: do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1
 1014: do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
 1015: 
 1016: # EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but
 1017: # uses the Unix file globbing syntax for its wildcards.
 1018: #
 1019: # EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE.
 1020: #
 1021: do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0
 1022: do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1
 1023: do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0
 1024: do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1
 1025: 
 1026: do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1
 1027: do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0
 1028: do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0
 1029: 
 1030: # EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the
 1031: # NOT keyword to invert the sense of the test.
 1032: #
 1033: do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1
 1034: do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0
 1035: do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0
 1036: do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0
 1037: do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1
 1038: 
 1039: db nullvalue null
 1040: do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null
 1041: do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null
 1042: do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null
 1043: do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null
 1044: db nullvalue {}
 1045: 
 1046: # EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by
 1047: # calling the function glob(Y,X) and can be modified by overriding that
 1048: # function.
 1049: proc globfunc {args} {
 1050:   eval lappend ::globargs $args
 1051:   return 1
 1052: }
 1053: db func glob -argcount 2 globfunc
 1054: set ::globargs [list]
 1055: do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1
 1056: do_test         e_expr-17.3.2 { set globargs } {def abc}
 1057: set ::globargs [list]
 1058: do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0
 1059: do_test         e_expr-17.3.4 { set globargs } {Y X}
 1060: sqlite3 db test.db
 1061: 
 1062: # EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
 1063: # default and so use of the REGEXP operator will normally result in an
 1064: # error message.
 1065: #
 1066: #   There is a regexp function if ICU is enabled though.
 1067: #
 1068: ifcapable !icu {
 1069:   do_catchsql_test e_expr-18.1.1 { 
 1070:     SELECT regexp('abc', 'def') 
 1071:   } {1 {no such function: regexp}}
 1072:   do_catchsql_test e_expr-18.1.2 { 
 1073:     SELECT 'abc' REGEXP 'def'
 1074:   } {1 {no such function: REGEXP}}
 1075: }
 1076: 
 1077: # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
 1078: # the regexp() user function.
 1079: #
 1080: # EVIDENCE-OF: R-57289-13578 If a application-defined SQL function named
 1081: # "regexp" is added at run-time, that function will be called in order
 1082: # to implement the REGEXP operator.
 1083: #
 1084: proc regexpfunc {args} {
 1085:   eval lappend ::regexpargs $args
 1086:   return 1
 1087: }
 1088: db func regexp -argcount 2 regexpfunc
 1089: set ::regexpargs [list]
 1090: do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
 1091: do_test         e_expr-18.2.2 { set regexpargs } {def abc}
 1092: set ::regexpargs [list]
 1093: do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
 1094: do_test         e_expr-18.2.4 { set regexpargs } {Y X}
 1095: sqlite3 db test.db
 1096: 
 1097: # EVIDENCE-OF: R-42037-37826 The default match() function implementation
 1098: # raises an exception and is not really useful for anything.
 1099: #
 1100: do_catchsql_test e_expr-19.1.1 { 
 1101:   SELECT 'abc' MATCH 'def' 
 1102: } {1 {unable to use function MATCH in the requested context}}
 1103: do_catchsql_test e_expr-19.1.2 { 
 1104:   SELECT match('abc', 'def')
 1105: } {1 {unable to use function MATCH in the requested context}}
 1106: 
 1107: # EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
 1108: # the match() application-defined function.
 1109: #
 1110: # EVIDENCE-OF: R-06021-09373 But extensions can override the match()
 1111: # function with more helpful logic.
 1112: #
 1113: proc matchfunc {args} {
 1114:   eval lappend ::matchargs $args
 1115:   return 1
 1116: }
 1117: db func match -argcount 2 matchfunc
 1118: set ::matchargs [list]
 1119: do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
 1120: do_test         e_expr-19.2.2 { set matchargs } {def abc}
 1121: set ::matchargs [list]
 1122: do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
 1123: do_test         e_expr-19.2.4 { set matchargs } {Y X}
 1124: sqlite3 db test.db
 1125: 
 1126: #-------------------------------------------------------------------------
 1127: # Test cases for the testable statements related to the CASE expression.
 1128: #
 1129: # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE
 1130: # expression: those with a base expression and those without.
 1131: #
 1132: do_execsql_test e_expr-20.1 {
 1133:   SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
 1134: } {true}
 1135: do_execsql_test e_expr-20.2 {
 1136:   SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
 1137: } {false}
 1138: 
 1139: proc var {nm} {
 1140:   lappend ::varlist $nm
 1141:   return [set "::$nm"]
 1142: }
 1143: db func var var
 1144: 
 1145: # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each
 1146: # WHEN expression is evaluated and the result treated as a boolean,
 1147: # starting with the leftmost and continuing to the right.
 1148: #
 1149: foreach {a b c} {0 0 0} break
 1150: set varlist [list]
 1151: do_execsql_test e_expr-21.1.1 {
 1152:   SELECT CASE WHEN var('a') THEN 'A' 
 1153:               WHEN var('b') THEN 'B' 
 1154:               WHEN var('c') THEN 'C' END
 1155: } {{}}
 1156: do_test e_expr-21.1.2 { set varlist } {a b c}
 1157: set varlist [list]
 1158: do_execsql_test e_expr-21.1.3 {
 1159:   SELECT CASE WHEN var('c') THEN 'C' 
 1160:               WHEN var('b') THEN 'B' 
 1161:               WHEN var('a') THEN 'A' 
 1162:               ELSE 'no result'
 1163:   END
 1164: } {{no result}}
 1165: do_test e_expr-21.1.4 { set varlist } {c b a}
 1166: 
 1167: # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the
 1168: # evaluation of the THEN expression that corresponds to the first WHEN
 1169: # expression that evaluates to true.
 1170: #
 1171: foreach {a b c} {0 1 0} break
 1172: do_execsql_test e_expr-21.2.1 {
 1173:   SELECT CASE WHEN var('a') THEN 'A' 
 1174:               WHEN var('b') THEN 'B' 
 1175:               WHEN var('c') THEN 'C' 
 1176:               ELSE 'no result'
 1177:   END
 1178: } {B}
 1179: foreach {a b c} {0 1 1} break
 1180: do_execsql_test e_expr-21.2.2 {
 1181:   SELECT CASE WHEN var('a') THEN 'A' 
 1182:               WHEN var('b') THEN 'B' 
 1183:               WHEN var('c') THEN 'C'
 1184:               ELSE 'no result'
 1185:   END
 1186: } {B}
 1187: foreach {a b c} {0 0 1} break
 1188: do_execsql_test e_expr-21.2.3 {
 1189:   SELECT CASE WHEN var('a') THEN 'A' 
 1190:               WHEN var('b') THEN 'B' 
 1191:               WHEN var('c') THEN 'C'
 1192:               ELSE 'no result'
 1193:   END
 1194: } {C}
 1195: 
 1196: # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions
 1197: # evaluate to true, the result of evaluating the ELSE expression, if
 1198: # any.
 1199: #
 1200: foreach {a b c} {0 0 0} break
 1201: do_execsql_test e_expr-21.3.1 {
 1202:   SELECT CASE WHEN var('a') THEN 'A' 
 1203:               WHEN var('b') THEN 'B' 
 1204:               WHEN var('c') THEN 'C'
 1205:               ELSE 'no result'
 1206:   END
 1207: } {{no result}}
 1208: 
 1209: # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of
 1210: # the WHEN expressions are true, then the overall result is NULL.
 1211: #
 1212: db nullvalue null
 1213: do_execsql_test e_expr-21.3.2 {
 1214:   SELECT CASE WHEN var('a') THEN 'A' 
 1215:               WHEN var('b') THEN 'B' 
 1216:               WHEN var('c') THEN 'C'
 1217:   END
 1218: } {null}
 1219: db nullvalue {}
 1220: 
 1221: # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when
 1222: # evaluating WHEN terms.
 1223: #
 1224: do_execsql_test e_expr-21.4.1 {
 1225:   SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END
 1226: } {B}
 1227: do_execsql_test e_expr-21.4.2 {
 1228:   SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END
 1229: } {C}
 1230: 
 1231: # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base
 1232: # expression is evaluated just once and the result is compared against
 1233: # the evaluation of each WHEN expression from left to right.
 1234: #
 1235: # Note: This test case tests the "evaluated just once" part of the above
 1236: # statement. Tests associated with the next two statements test that the
 1237: # comparisons take place.
 1238: #
 1239: foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break
 1240: set ::varlist [list]
 1241: do_execsql_test e_expr-22.1.1 {
 1242:   SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END
 1243: } {C}
 1244: do_test e_expr-22.1.2 { set ::varlist } {a}
 1245: 
 1246: # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the
 1247: # evaluation of the THEN expression that corresponds to the first WHEN
 1248: # expression for which the comparison is true.
 1249: #
 1250: do_execsql_test e_expr-22.2.1 {
 1251:   SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
 1252: } {B}
 1253: do_execsql_test e_expr-22.2.2 {
 1254:   SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
 1255: } {A}
 1256: 
 1257: # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions
 1258: # evaluate to a value equal to the base expression, the result of
 1259: # evaluating the ELSE expression, if any.
 1260: #
 1261: do_execsql_test e_expr-22.3.1 {
 1262:   SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END
 1263: } {D}
 1264: 
 1265: # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of
 1266: # the WHEN expressions produce a result equal to the base expression,
 1267: # the overall result is NULL.
 1268: #
 1269: do_execsql_test e_expr-22.4.1 {
 1270:   SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
 1271: } {{}}
 1272: db nullvalue null
 1273: do_execsql_test e_expr-22.4.2 {
 1274:   SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
 1275: } {null}
 1276: db nullvalue {}
 1277: 
 1278: # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a
 1279: # WHEN expression, the same collating sequence, affinity, and
 1280: # NULL-handling rules apply as if the base expression and WHEN
 1281: # expression are respectively the left- and right-hand operands of an =
 1282: # operator.
 1283: #
 1284: proc rev {str} {
 1285:   set ret ""
 1286:   set chars [split $str]
 1287:   for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
 1288:     append ret [lindex $chars $i]
 1289:   }
 1290:   set ret
 1291: }
 1292: proc reverse {lhs rhs} {
 1293:   string compare [rev $lhs] [ref $rhs]
 1294: }
 1295: db collate reverse reverse
 1296: do_execsql_test e_expr-23.1.1 {
 1297:   CREATE TABLE t1(
 1298:     a TEXT     COLLATE NOCASE,
 1299:     b          COLLATE REVERSE,
 1300:     c INTEGER,
 1301:     d BLOB
 1302:   );
 1303:   INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
 1304: } {}
 1305: do_execsql_test e_expr-23.1.2 {
 1306:   SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
 1307: } {B}
 1308: do_execsql_test e_expr-23.1.3 {
 1309:   SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
 1310: } {B}
 1311: do_execsql_test e_expr-23.1.4 {
 1312:   SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
 1313: } {B}
 1314: do_execsql_test e_expr-23.1.5 {
 1315:   SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
 1316: } {A}
 1317: do_execsql_test e_expr-23.1.6 {
 1318:   SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
 1319: } {B}
 1320: do_execsql_test e_expr-23.1.7 {
 1321:   SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
 1322: } {A}
 1323: do_execsql_test e_expr-23.1.8 {
 1324:   SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
 1325: } {B}
 1326: do_execsql_test e_expr-23.1.9 {
 1327:   SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END
 1328: } {B}
 1329: 
 1330: # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the
 1331: # result of the CASE is always the result of evaluating the ELSE
 1332: # expression if it exists, or NULL if it does not.
 1333: #
 1334: do_execsql_test e_expr-24.1.1 {
 1335:   SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END;
 1336: } {{}}
 1337: do_execsql_test e_expr-24.1.2 {
 1338:   SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END;
 1339: } {C}
 1340: 
 1341: # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy,
 1342: # or short-circuit, evaluation.
 1343: #
 1344: set varlist [list]
 1345: foreach {a b c} {0 1 0} break
 1346: do_execsql_test e_expr-25.1.1 {
 1347:   SELECT CASE WHEN var('a') THEN 'A' 
 1348:               WHEN var('b') THEN 'B' 
 1349:               WHEN var('c') THEN 'C' 
 1350:   END
 1351: } {B}
 1352: do_test e_expr-25.1.2 { set ::varlist } {a b}
 1353: set varlist [list]
 1354: do_execsql_test e_expr-25.1.3 {
 1355:   SELECT CASE '0' WHEN var('a') THEN 'A' 
 1356:                   WHEN var('b') THEN 'B' 
 1357:                   WHEN var('c') THEN 'C' 
 1358:   END
 1359: } {A}
 1360: do_test e_expr-25.1.4 { set ::varlist } {a}
 1361: 
 1362: # EVIDENCE-OF: R-34773-62253 The only difference between the following
 1363: # two CASE expressions is that the x expression is evaluated exactly
 1364: # once in the first example but might be evaluated multiple times in the
 1365: # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN
 1366: # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
 1367: #
 1368: proc ceval {x} {
 1369:   incr ::evalcount
 1370:   return $x
 1371: }
 1372: db func ceval ceval
 1373: set ::evalcount 0
 1374: 
 1375: do_execsql_test e_expr-26.1.1 {
 1376:   CREATE TABLE t2(x, w1, r1, w2, r2, r3);
 1377:   INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3');
 1378:   INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3');
 1379:   INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3');
 1380: } {}
 1381: do_execsql_test e_expr-26.1.2 {
 1382:   SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
 1383: } {R1 R2 R3}
 1384: do_execsql_test e_expr-26.1.3 {
 1385:   SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2
 1386: } {R1 R2 R3}
 1387: 
 1388: do_execsql_test e_expr-26.1.4 {
 1389:   SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
 1390: } {R1 R2 R3}
 1391: do_test e_expr-26.1.5 { set ::evalcount } {3}
 1392: set ::evalcount 0
 1393: do_execsql_test e_expr-26.1.6 {
 1394:   SELECT CASE 
 1395:     WHEN ceval(x)=w1 THEN r1 
 1396:     WHEN ceval(x)=w2 THEN r2 
 1397:     ELSE r3 END 
 1398:   FROM t2
 1399: } {R1 R2 R3}
 1400: do_test e_expr-26.1.6 { set ::evalcount } {5}
 1401: 
 1402: 
 1403: #-------------------------------------------------------------------------
 1404: # Test statements related to CAST expressions.
 1405: #
 1406: # EVIDENCE-OF: R-65079-31758 Application of a CAST expression is
 1407: # different to application of a column affinity, as with a CAST
 1408: # expression the storage class conversion is forced even if it is lossy
 1409: # and irrreversible.
 1410: #
 1411: do_execsql_test e_expr-27.1.1 {
 1412:   CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
 1413:   INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
 1414:   SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
 1415: } {blob UVU text 1.23abc real 4.5}
 1416: do_execsql_test e_expr-27.1.2 {
 1417:   SELECT 
 1418:     typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
 1419:     typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
 1420:     typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
 1421: } {text UVU real 1.23 integer 4}
 1422: 
 1423: # EVIDENCE-OF: R-27225-65050 If the value of <expr> is NULL, then
 1424: # the result of the CAST expression is also NULL.
 1425: #
 1426: do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
 1427: do_expr_test e_expr-27.2.2 { CAST(NULL AS text) }    null {}
 1428: do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) }    null {}
 1429: do_expr_test e_expr-27.2.4 { CAST(NULL AS number) }  null {}
 1430: 
 1431: # EVIDENCE-OF: R-31076-23575 Casting a value to a <type-name> with
 1432: # no affinity causes the value to be converted into a BLOB.
 1433: #
 1434: do_expr_test e_expr-27.3.1 { CAST('abc' AS blob)       } blob abc
 1435: do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
 1436: do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi
 1437: 
 1438: # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
 1439: # the value to TEXT in the encoding of the database connection, then
 1440: # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
 1441: #
 1442: do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
 1443: do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) }   X'343536'
 1444: do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) }  X'312E3738'
 1445: rename db db2
 1446: sqlite3 db :memory:
 1447: ifcapable {utf16} {
 1448: db eval { PRAGMA encoding = 'utf-16le' }
 1449: do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
 1450: do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) }   X'340035003600'
 1451: do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) }  X'31002E0037003800'
 1452: }
 1453: db close
 1454: sqlite3 db :memory:
 1455: db eval { PRAGMA encoding = 'utf-16be' }
 1456: ifcapable {utf16} {
 1457: do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
 1458: do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) }   X'003400350036'
 1459: do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) }  X'0031002E00370038'
 1460: }
 1461: db close
 1462: rename db2 db
 1463: 
 1464: # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
 1465: # of bytes that make up the BLOB is interpreted as text encoded using
 1466: # the database encoding.
 1467: #
 1468: do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
 1469: do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
 1470: rename db db2
 1471: sqlite3 db :memory:
 1472: db eval { PRAGMA encoding = 'utf-16le' }
 1473: ifcapable {utf16} {
 1474: do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
 1475: do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
 1476: }
 1477: db close
 1478: rename db2 db
 1479: 
 1480: # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
 1481: # renders the value as if via sqlite3_snprintf() except that the
 1482: # resulting TEXT uses the encoding of the database connection.
 1483: #
 1484: do_expr_test e_expr-28.2.1 { CAST (1 AS text)   }     text 1
 1485: do_expr_test e_expr-28.2.2 { CAST (45 AS text)  }     text 45
 1486: do_expr_test e_expr-28.2.3 { CAST (-45 AS text) }     text -45
 1487: do_expr_test e_expr-28.2.4 { CAST (8.8 AS text)    }  text 8.8
 1488: do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) }  text 230000.0
 1489: do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
 1490: do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) }     text 0.0
 1491: do_expr_test e_expr-28.2.7 { CAST (0 AS text) }       text 0
 1492: 
 1493: # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
 1494: # value is first converted to TEXT.
 1495: #
 1496: do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
 1497: do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
 1498: do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
 1499: do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
 1500: rename db db2
 1501: sqlite3 db :memory:
 1502: ifcapable {utf16} {
 1503: db eval { PRAGMA encoding = 'utf-16le' }
 1504: do_expr_test e_expr-29.1.5 { 
 1505:     CAST (X'31002E0032003300' AS REAL) } real 1.23
 1506: do_expr_test e_expr-29.1.6 { 
 1507:     CAST (X'3200330030002E003000' AS REAL) } real 230.0
 1508: do_expr_test e_expr-29.1.7 { 
 1509:     CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
 1510: do_expr_test e_expr-29.1.8 { 
 1511:     CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
 1512: }
 1513: db close
 1514: rename db2 db
 1515: 
 1516: # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the
 1517: # longest possible prefix of the value that can be interpreted as a real
 1518: # number is extracted from the TEXT value and the remainder ignored.
 1519: #
 1520: do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23
 1521: do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45
 1522: do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212
 1523: do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0
 1524: 
 1525: # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are
 1526: # ignored when converging from TEXT to REAL.
 1527: #
 1528: do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23
 1529: do_expr_test e_expr-29.3.2 { CAST('    1.45.23abcd' AS REAL) } real 1.45
 1530: do_expr_test e_expr-29.3.3 { CAST('   -2.12e-01ABC' AS REAL) } real -0.212
 1531: do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0
 1532: 
 1533: # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be
 1534: # interpreted as a real number, the result of the conversion is 0.0.
 1535: #
 1536: do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0
 1537: do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0
 1538: do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0
 1539: 
 1540: # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the
 1541: # value is first converted to TEXT.
 1542: #
 1543: do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123
 1544: do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678
 1545: do_expr_test e_expr-30.1.3 { 
 1546:   CAST(X'31303030303030' AS INTEGER) 
 1547: } integer 1000000
 1548: do_expr_test e_expr-30.1.4 { 
 1549:   CAST(X'2D31313235383939393036383432363234' AS INTEGER) 
 1550: } integer -1125899906842624
 1551: 
 1552: rename db db2
 1553: sqlite3 db :memory:
 1554: ifcapable {utf16} {
 1555: execsql { PRAGMA encoding = 'utf-16be' }
 1556: do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123
 1557: do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678
 1558: do_expr_test e_expr-30.1.7 { 
 1559:   CAST(X'0031003000300030003000300030' AS INTEGER) 
 1560: } integer 1000000
 1561: do_expr_test e_expr-30.1.8 { 
 1562:   CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER) 
 1563: } integer -1125899906842624
 1564: }
 1565: db close
 1566: rename db2 db
 1567: 
 1568: # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the
 1569: # longest possible prefix of the value that can be interpreted as an
 1570: # integer number is extracted from the TEXT value and the remainder
 1571: # ignored.
 1572: #
 1573: do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123
 1574: do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523
 1575: do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2
 1576: do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1
 1577: 
 1578: # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when
 1579: # converting from TEXT to INTEGER are ignored.
 1580: #
 1581: do_expr_test e_expr-30.3.1 { CAST('   123abcd' AS INT) } integer 123
 1582: do_expr_test e_expr-30.3.2 { CAST('  14523abcd' AS INT) } integer 14523
 1583: do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
 1584: do_expr_test e_expr-30.3.4 { CAST('     1 2 3 4' AS INT) } integer 1
 1585: 
 1586: # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
 1587: # interpreted as an integer number, the result of the conversion is 0.
 1588: #
 1589: do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
 1590: do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
 1591: do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
 1592: 
 1593: # EVIDENCE-OF: R-00741-38776 A cast of a REAL value into an INTEGER will
 1594: # truncate the fractional part of the REAL.
 1595: #
 1596: do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
 1597: do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
 1598: do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
 1599: do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
 1600: 
 1601: # EVIDENCE-OF: R-49503-28105 If a REAL is too large to be represented as
 1602: # an INTEGER then the result of the cast is the largest negative
 1603: # integer: -9223372036854775808.
 1604: #
 1605: do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer -9223372036854775808
 1606: do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
 1607: do_expr_test e_expr-31.2.3 { 
 1608:   CAST(-9223372036854775809.0 AS INT)
 1609: } integer -9223372036854775808
 1610: do_expr_test e_expr-31.2.4 { 
 1611:   CAST(9223372036854775809.0 AS INT)
 1612: } integer -9223372036854775808
 1613: 
 1614: 
 1615: # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
 1616: # first does a forced conversion into REAL but then further converts the
 1617: # result into INTEGER if and only if the conversion from REAL to INTEGER
 1618: # is lossless and reversible.
 1619: #
 1620: do_expr_test e_expr-32.1.1 { CAST('45'   AS NUMERIC)  } integer 45
 1621: do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC)  } integer 45
 1622: do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC)  } real 45.2
 1623: do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
 1624: do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
 1625: 
 1626: # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
 1627: # is a no-op, even if a real value could be losslessly converted to an
 1628: # integer.
 1629: #
 1630: do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0
 1631: do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5
 1632: 
 1633: do_expr_test e_expr-32.2.3 { 
 1634:   CAST(-9223372036854775808 AS NUMERIC)
 1635: } integer -9223372036854775808
 1636: do_expr_test e_expr-32.2.4 { 
 1637:   CAST(9223372036854775807 AS NUMERIC)
 1638: } integer 9223372036854775807
 1639: 
 1640: # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
 1641: # non-BLOB value into a BLOB and the result from casting any BLOB value
 1642: # into a non-BLOB value may be different depending on whether the
 1643: # database encoding is UTF-8, UTF-16be, or UTF-16le.
 1644: #
 1645: ifcapable {utf16} {
 1646: sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' }
 1647: sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' }
 1648: sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' }
 1649: foreach {tn castexpr differs} {
 1650:   1 { CAST(123 AS BLOB)    } 1
 1651:   2 { CAST('' AS BLOB)     } 0
 1652:   3 { CAST('abcd' AS BLOB) } 1
 1653: 
 1654:   4 { CAST(X'abcd' AS TEXT) } 1
 1655:   5 { CAST(X'' AS TEXT)     } 0
 1656: } {
 1657:   set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"]
 1658:   set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"]
 1659:   set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"]
 1660: 
 1661:   if {$differs} {
 1662:     set res [expr {$r1!=$r2 && $r2!=$r3}]
 1663:   } else {
 1664:     set res [expr {$r1==$r2 && $r2==$r3}]
 1665:   }
 1666: 
 1667:   do_test e_expr-33.1.$tn {set res} 1
 1668: }
 1669: db1 close
 1670: db2 close
 1671: db3 close
 1672: }
 1673: 
 1674: #-------------------------------------------------------------------------
 1675: # Test statements related to the EXISTS and NOT EXISTS operators.
 1676: #
 1677: catch { db close }
 1678: forcedelete test.db
 1679: sqlite3 db test.db
 1680: 
 1681: do_execsql_test e_expr-34.1 {
 1682:   CREATE TABLE t1(a, b);
 1683:   INSERT INTO t1 VALUES(1, 2);
 1684:   INSERT INTO t1 VALUES(NULL, 2);
 1685:   INSERT INTO t1 VALUES(1, NULL);
 1686:   INSERT INTO t1 VALUES(NULL, NULL);
 1687: } {}
 1688: 
 1689: # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one
 1690: # of the integer values 0 and 1.
 1691: #
 1692: # This statement is not tested by itself. Instead, all e_expr-34.* tests 
 1693: # following this point explicitly test that specific invocations of EXISTS
 1694: # return either integer 0 or integer 1.
 1695: #
 1696: 
 1697: # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified
 1698: # as the right-hand operand of the EXISTS operator would return one or
 1699: # more rows, then the EXISTS operator evaluates to 1.
 1700: #
 1701: foreach {tn expr} {
 1702:     1 { EXISTS ( SELECT a FROM t1 ) }
 1703:     2 { EXISTS ( SELECT b FROM t1 ) }
 1704:     3 { EXISTS ( SELECT 24 ) }
 1705:     4 { EXISTS ( SELECT NULL ) }
 1706:     5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) }
 1707: } {
 1708:   do_expr_test e_expr-34.2.$tn $expr integer 1
 1709: }
 1710: 
 1711: # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no
 1712: # rows at all, then the EXISTS operator evaluates to 0.
 1713: #
 1714: foreach {tn expr} {
 1715:     1 { EXISTS ( SELECT a FROM t1 WHERE 0) }
 1716:     2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) }
 1717:     3 { EXISTS ( SELECT 24 WHERE 0) }
 1718:     4 { EXISTS ( SELECT NULL WHERE 1=2) }
 1719: } {
 1720:   do_expr_test e_expr-34.3.$tn $expr integer 0
 1721: }
 1722: 
 1723: # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned
 1724: # by the SELECT statement (if any) and the specific values returned have
 1725: # no effect on the results of the EXISTS operator.
 1726: #
 1727: foreach {tn expr res} {
 1728:     1 { EXISTS ( SELECT * FROM t1 ) }                          1
 1729:     2 { EXISTS ( SELECT *, *, * FROM t1 ) }                    1
 1730:     3 { EXISTS ( SELECT 24, 25 ) }                             1
 1731:     4 { EXISTS ( SELECT NULL, NULL, NULL ) }                   1
 1732:     5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) }   1
 1733: 
 1734:     6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) }                0
 1735:     7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) }         0
 1736:     8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) }                  0
 1737:     9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) }                0
 1738: } {
 1739:   do_expr_test e_expr-34.4.$tn $expr integer $res
 1740: }
 1741: 
 1742: # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values
 1743: # are not handled any differently from rows without NULL values.
 1744: #
 1745: foreach {tn e1 e2} {
 1746:   1 { EXISTS (SELECT 'not null') }    { EXISTS (SELECT NULL) }
 1747:   2 { EXISTS (SELECT NULL FROM t1) }  { EXISTS (SELECT 'bread' FROM t1) }
 1748: } {
 1749:   set res [db one "SELECT $e1"]
 1750:   do_expr_test e_expr-34.5.${tn}a $e1 integer $res
 1751:   do_expr_test e_expr-34.5.${tn}b $e2 integer $res
 1752: }
 1753: 
 1754: #-------------------------------------------------------------------------
 1755: # Test statements related to scalar sub-queries.
 1756: #
 1757: 
 1758: catch { db close }
 1759: forcedelete test.db
 1760: sqlite3 db test.db
 1761: do_test e_expr-35.0 {
 1762:   execsql {
 1763:     CREATE TABLE t2(a, b);
 1764:     INSERT INTO t2 VALUES('one', 'two');
 1765:     INSERT INTO t2 VALUES('three', NULL);
 1766:     INSERT INTO t2 VALUES(4, 5.0);
 1767:   }
 1768: } {}
 1769: 
 1770: # EVIDENCE-OF: R-00980-39256 A SELECT statement enclosed in parentheses
 1771: # may appear as a scalar quantity.
 1772: #
 1773: # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
 1774: # aggregate and compound SELECT queries (queries with keywords like
 1775: # UNION or EXCEPT) are allowed as scalar subqueries.
 1776: #
 1777: do_expr_test e_expr-35.1.1 { (SELECT 35)   } integer 35
 1778: do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
 1779: 
 1780: do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
 1781: do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
 1782: 
 1783: do_expr_test e_expr-35.1.5 { 
 1784:   (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
 1785: } null {}
 1786: do_expr_test e_expr-35.1.6 { 
 1787:   (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
 1788: } integer 4
 1789: 
 1790: # EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must
 1791: # return a result set with a single column.
 1792: #
 1793: # The following block tests that errors are returned in a bunch of cases
 1794: # where a subquery returns more than one column.
 1795: #
 1796: set M {only a single result allowed for a SELECT that is part of an expression}
 1797: foreach {tn sql} {
 1798:   1     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
 1799:   2     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
 1800:   3     { SELECT (SELECT 1, 2) }
 1801:   4     { SELECT (SELECT NULL, NULL, NULL) }
 1802:   5     { SELECT (SELECT * FROM t2) }
 1803:   6     { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
 1804: } {
 1805:   do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M]
 1806: }
 1807: 
 1808: # EVIDENCE-OF: R-35764-28041 The result of the expression is the value
 1809: # of the only column in the first row returned by the SELECT statement.
 1810: #
 1811: # EVIDENCE-OF: R-41898-06686 If the SELECT yields more than one result
 1812: # row, all rows after the first are ignored.
 1813: #
 1814: do_execsql_test e_expr-36.3.1 {
 1815:   CREATE TABLE t4(x, y);
 1816:   INSERT INTO t4 VALUES(1, 'one');
 1817:   INSERT INTO t4 VALUES(2, 'two');
 1818:   INSERT INTO t4 VALUES(3, 'three');
 1819: } {}
 1820: 
 1821: foreach {tn expr restype resval} {
 1822:     2  { ( SELECT x FROM t4 ORDER BY x )      }        integer 1
 1823:     3  { ( SELECT x FROM t4 ORDER BY y )      }        integer 1
 1824:     4  { ( SELECT x FROM t4 ORDER BY x DESC ) }        integer 3
 1825:     5  { ( SELECT x FROM t4 ORDER BY y DESC ) }        integer 2
 1826:     6  { ( SELECT y FROM t4 ORDER BY y DESC ) }        text    two
 1827: 
 1828:     7  { ( SELECT sum(x) FROM t4 )           }         integer 6
 1829:     8  { ( SELECT group_concat(y,'') FROM t4 ) }       text    onetwothree
 1830:     9  { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 
 1831: 
 1832: } {
 1833:   do_expr_test e_expr-36.3.$tn $expr $restype $resval
 1834: }
 1835: 
 1836: # EVIDENCE-OF: R-25492-41572 If the SELECT yields no rows, then the
 1837: # value of the expression is NULL.
 1838: #
 1839: foreach {tn expr} {
 1840:     1  { ( SELECT x FROM t4 WHERE x>3 ORDER BY x )      }
 1841:     2  { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y )  }
 1842: } {
 1843:   do_expr_test e_expr-36.4.$tn $expr null {}
 1844: }
 1845: 
 1846: 
 1847: finish_test

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>