000001  # 2010 July 16
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # This file implements tests to verify that the "testable statements" in 
000013  # the lang_expr.html document are correct.
000014  #
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  source $testdir/malloc_common.tcl
000019  
000020  ifcapable !compound {
000021    finish_test
000022    return
000023  }
000024  
000025  proc do_expr_test {tn expr type value} {
000026    uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [
000027      list [list $type $value]
000028    ]
000029  }
000030  
000031  proc do_qexpr_test {tn expr value} {
000032    uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]
000033  }
000034  
000035  # Set up three global variables:
000036  #
000037  #   ::opname         An array mapping from SQL operator to an easy to parse
000038  #                    name. The names are used as part of test case names.
000039  #
000040  #   ::opprec         An array mapping from SQL operator to a numeric
000041  #                    precedence value. Operators that group more tightly
000042  #                    have lower numeric precedences.
000043  #
000044  #   ::oplist         A list of all SQL operators supported by SQLite.
000045  #
000046  foreach {op opn} {
000047        ||   cat     *   mul       /  div       %     mod       +      add
000048        -    sub     <<  lshift    >> rshift    &     bitand    |      bitor
000049        <    less    <=  lesseq    >  more      >=    moreeq    =      eq1
000050        ==   eq2     <>  ne1       != ne2       IS    is        LIKE   like
000051        GLOB glob    AND and       OR or        MATCH match     REGEXP regexp
000052        {IS NOT} isnt
000053  } {
000054    set ::opname($op) $opn
000055  }
000056  set oplist [list]
000057  foreach {prec opl} {
000058    1   ||
000059    2   {* / %}
000060    3   {+ -}
000061    4   {<< >> & |}
000062    5   {< <= > >=}
000063    6   {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
000064    7   AND
000065    8   OR
000066  } {
000067    foreach op $opl { 
000068      set ::opprec($op) $prec 
000069      lappend oplist $op
000070    }
000071  }
000072  
000073  
000074  # Hook in definitions of MATCH and REGEX. The following implementations
000075  # cause MATCH and REGEX to behave similarly to the == operator.
000076  #
000077  proc matchfunc {a b} { return [expr {$a==$b}] }
000078  proc regexfunc {a b} { return [expr {$a==$b}] }
000079  db func match  -argcount 2 matchfunc
000080  db func regexp -argcount 2 regexfunc
000081  
000082  #-------------------------------------------------------------------------
000083  # Test cases e_expr-1.* attempt to verify that all binary operators listed
000084  # in the documentation exist and that the relative precedences of the
000085  # operators are also as the documentation suggests.
000086  #
000087  # EVIDENCE-OF: R-15514-65163 SQLite understands the following binary
000088  # operators, in order from highest to lowest precedence: || * / % + -
000089  # << >> & | < <= > >= = == != <> IS IS
000090  # NOT IN LIKE GLOB MATCH REGEXP AND OR
000091  #
000092  # EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same
000093  # precedence as =.
000094  #
000095  
000096  unset -nocomplain untested
000097  foreach op1 $oplist {
000098    foreach op2 $oplist {
000099      set untested($op1,$op2) 1
000100      foreach {tn A B C} {
000101         1     22   45    66
000102         2      0    0     0
000103         3      0    0     1
000104         4      0    1     0
000105         5      0    1     1
000106         6      1    0     0
000107         7      1    0     1
000108         8      1    1     0
000109         9      1    1     1
000110        10      5    6     1
000111        11      1    5     6
000112        12      1    5     5
000113        13      5    5     1
000114  
000115        14      5    2     1
000116        15      1    4     1
000117        16     -1    0     1
000118        17      0    1    -1
000119  
000120      } {
000121        set testname "e_expr-1.$opname($op1).$opname($op2).$tn"
000122  
000123        # If $op2 groups more tightly than $op1, then the result
000124        # of executing $sql1 whould be the same as executing $sql3.
000125        # If $op1 groups more tightly, or if $op1 and $op2 have 
000126        # the same precedence, then executing $sql1 should return
000127        # the same value as $sql2.
000128        #
000129        set sql1 "SELECT $A $op1 $B $op2 $C"
000130        set sql2 "SELECT ($A $op1 $B) $op2 $C"
000131        set sql3 "SELECT $A $op1 ($B $op2 $C)"
000132  
000133        set a2 [db one $sql2]
000134        set a3 [db one $sql3]
000135  
000136        do_execsql_test $testname $sql1 [list [
000137          if {$opprec($op2) < $opprec($op1)} {set a3} {set a2}
000138        ]]
000139        if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) }
000140      }
000141    }
000142  }
000143  
000144  foreach op {* AND OR + || & |} { unset untested($op,$op) }
000145  unset untested(+,-)  ;#       Since    (a+b)-c == a+(b-c)
000146  unset untested(*,<<) ;#       Since    (a*b)<<c == a*(b<<c)
000147  
000148  do_test e_expr-1.1 { array names untested } {}
000149  
000150  # At one point, test 1.2.2 was failing. Instead of the correct result, it
000151  # was returning {1 1 0}. This would seem to indicate that LIKE has the
000152  # same precedence as '<'. Which is incorrect. It has lower precedence.
000153  #
000154  do_execsql_test e_expr-1.2.1 { 
000155    SELECT 0 < 2 LIKE 1,   (0 < 2) LIKE 1,   0 < (2 LIKE 1)
000156  } {1 1 0}
000157  do_execsql_test e_expr-1.2.2 { 
000158    SELECT 0 LIKE 0 < 2,   (0 LIKE 0) < 2,   0 LIKE (0 < 2)
000159  } {0 1 0}
000160  
000161  # Showing that LIKE and == have the same precedence
000162  #
000163  do_execsql_test e_expr-1.2.3 { 
000164    SELECT 2 LIKE 2 == 1,   (2 LIKE 2) == 1,    2 LIKE (2 == 1)
000165  } {1 1 0}
000166  do_execsql_test e_expr-1.2.4 { 
000167    SELECT 2 == 2 LIKE 1,   (2 == 2) LIKE 1,    2 == (2 LIKE 1)
000168  } {1 1 0}
000169  
000170  # Showing that < groups more tightly than == (< has higher precedence). 
000171  #
000172  do_execsql_test e_expr-1.2.5 { 
000173    SELECT 0 < 2 == 1,   (0 < 2) == 1,   0 < (2 == 1)
000174  } {1 1 0}
000175  do_execsql_test e_expr-1.6 { 
000176    SELECT 0 == 0 < 2,   (0 == 0) < 2,   0 == (0 < 2)
000177  } {0 1 0}
000178  
000179  #-------------------------------------------------------------------------
000180  # Check that the four unary prefix operators mentioned in the 
000181  # documentation exist.
000182  #
000183  # EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these:
000184  # - + ~ NOT
000185  #
000186  do_execsql_test e_expr-2.1 { SELECT -   10   } {-10}
000187  do_execsql_test e_expr-2.2 { SELECT +   10   } {10}
000188  do_execsql_test e_expr-2.3 { SELECT ~   10   } {-11}
000189  do_execsql_test e_expr-2.4 { SELECT NOT 10   } {0}
000190  
000191  #-------------------------------------------------------------------------
000192  # Tests for the two statements made regarding the unary + operator.
000193  #
000194  # EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op.
000195  #
000196  # EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers,
000197  # blobs or NULL and it always returns a result with the same value as
000198  # the operand.
000199  #
000200  foreach {tn literal type} {
000201    1     'helloworld'   text
000202    2     45             integer
000203    3     45.2           real
000204    4     45.0           real
000205    5     X'ABCDEF'      blob
000206    6     NULL           null
000207  } {
000208    set sql " SELECT quote( + $literal ), typeof( + $literal) "
000209    do_execsql_test e_expr-3.$tn $sql [list $literal $type]
000210  }
000211  
000212  #-------------------------------------------------------------------------
000213  # Check that both = and == are both acceptable as the "equals" operator.
000214  # Similarly, either != or <> work as the not-equals operator.
000215  #
000216  # EVIDENCE-OF: R-03679-60639 Equals can be either = or ==.
000217  #
000218  # EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or
000219  # <>.
000220  #
000221  foreach {tn literal different} {
000222    1   'helloworld'  '12345'
000223    2   22            23
000224    3   'xyz'         X'78797A'
000225    4   X'78797A00'   'xyz'
000226  } {
000227    do_execsql_test e_expr-4.$tn "
000228      SELECT $literal  = $literal,   $literal == $literal,
000229             $literal  = $different, $literal == $different,
000230             $literal  = NULL,       $literal == NULL,
000231             $literal != $literal,   $literal <> $literal,
000232             $literal != $different, $literal <> $different,
000233             $literal != NULL,       $literal != NULL
000234  
000235    " {1 1 0 0 {} {} 0 0 1 1 {} {}}
000236  }
000237  
000238  #-------------------------------------------------------------------------
000239  # Test the || operator.
000240  #
000241  # EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins
000242  # together the two strings of its operands.
000243  #
000244  foreach {tn a b} {
000245    1   'helloworld'  '12345'
000246    2   22            23
000247  } {
000248    set as [db one "SELECT $a"]
000249    set bs [db one "SELECT $b"]
000250    
000251    do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"]
000252  }
000253  
000254  #-------------------------------------------------------------------------
000255  # Test the % operator.
000256  #
000257  # EVIDENCE-OF: R-04223-04352 The operator % outputs the integer value of
000258  # its left operand modulo its right operand.
000259  #
000260  do_execsql_test e_expr-6.1 {SELECT  72%5}  {2}
000261  do_execsql_test e_expr-6.2 {SELECT  72%-5} {2}
000262  do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2}
000263  do_execsql_test e_expr-6.4 {SELECT -72%5}  {-2}
000264  do_execsql_test e_expr-6.5 {SELECT 72.35%5} {2.0}
000265  
000266  #-------------------------------------------------------------------------
000267  # Test that the results of all binary operators are either numeric or 
000268  # NULL, except for the || operator, which may evaluate to either a text
000269  # value or NULL.
000270  #
000271  # EVIDENCE-OF: R-20665-17792 The result of any binary operator is either
000272  # a numeric value or NULL, except for the || concatenation operator
000273  # which always evaluates to either NULL or a text value.
000274  #
000275  set literals {
000276    1 'abc'        2 'hexadecimal'       3 ''
000277    4 123          5 -123                6 0
000278    7 123.4        8 0.0                 9 -123.4
000279   10 X'ABCDEF'   11 X''                12 X'0000'
000280   13     NULL
000281  }
000282  foreach op $oplist {
000283    foreach {n1 rhs} $literals { 
000284    foreach {n2 lhs} $literals {
000285  
000286      set t [db one " SELECT typeof($lhs $op $rhs) "]
000287      do_test e_expr-7.$opname($op).$n1.$n2 {
000288        expr {
000289             ($op=="||" && ($t == "text" || $t == "null"))
000290          || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null"))
000291        }
000292      } 1
000293  
000294    }}
000295  }
000296  
000297  #-------------------------------------------------------------------------
000298  # Test the IS and IS NOT operators.
000299  #
000300  # EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and
000301  # != except when one or both of the operands are NULL.
000302  #
000303  # EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL,
000304  # then the IS operator evaluates to 1 (true) and the IS NOT operator
000305  # evaluates to 0 (false).
000306  #
000307  # EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is
000308  # not, then the IS operator evaluates to 0 (false) and the IS NOT
000309  # operator is 1 (true).
000310  #
000311  # EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT
000312  # expression to evaluate to NULL.
000313  #
000314  do_execsql_test e_expr-8.1.1  { SELECT NULL IS     NULL } {1}
000315  do_execsql_test e_expr-8.1.2  { SELECT 'ab' IS     NULL } {0}
000316  do_execsql_test e_expr-8.1.3  { SELECT NULL IS     'ab' } {0}
000317  do_execsql_test e_expr-8.1.4  { SELECT 'ab' IS     'ab' } {1}
000318  do_execsql_test e_expr-8.1.5  { SELECT NULL ==     NULL } {{}}
000319  do_execsql_test e_expr-8.1.6  { SELECT 'ab' ==     NULL } {{}}
000320  do_execsql_test e_expr-8.1.7  { SELECT NULL ==     'ab' } {{}}
000321  do_execsql_test e_expr-8.1.8  { SELECT 'ab' ==     'ab' } {1}
000322  do_execsql_test e_expr-8.1.9  { SELECT NULL IS NOT NULL } {0}
000323  do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1}
000324  do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1}
000325  do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0}
000326  do_execsql_test e_expr-8.1.13 { SELECT NULL !=     NULL } {{}}
000327  do_execsql_test e_expr-8.1.14 { SELECT 'ab' !=     NULL } {{}}
000328  do_execsql_test e_expr-8.1.15 { SELECT NULL !=     'ab' } {{}}
000329  do_execsql_test e_expr-8.1.16 { SELECT 'ab' !=     'ab' } {0}
000330  
000331  foreach {n1 rhs} $literals { 
000332    foreach {n2 lhs} $literals {
000333      if {$rhs!="NULL" && $lhs!="NULL"} {
000334        set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"]
000335      } else {
000336        set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \
000337                     [expr {$lhs!="NULL" || $rhs!="NULL"}]
000338        ]
000339      }
000340      set test e_expr-8.2.$n1.$n2
000341      do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq
000342      do_execsql_test $test.2 "
000343        SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL
000344      " {0 0}
000345    }
000346  }
000347  
000348  #-------------------------------------------------------------------------
000349  # Run some tests on the COLLATE "unary postfix operator".
000350  #
000351  # This collation sequence reverses both arguments before using 
000352  # [string compare] to compare them. For example, when comparing the
000353  # strings 'one' and 'four', return the result of:
000354  #   
000355  #   string compare eno ruof
000356  #
000357  proc reverse_str {zStr} {
000358    set out ""
000359    foreach c [split $zStr {}] { set out "${c}${out}" }
000360    set out
000361  }
000362  proc reverse_collate {zLeft zRight} {
000363    string compare [reverse_str $zLeft] [reverse_str $zRight]
000364  }
000365  db collate reverse reverse_collate
000366  
000367  # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
000368  # operator that assigns a collating sequence to an expression.
000369  #
000370  # EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher
000371  # precedence (binds more tightly) than any binary operator and any unary
000372  # prefix operator except "~".
000373  #
000374  do_execsql_test e_expr-9.1 { SELECT  'abcd' < 'bbbb'    COLLATE reverse } 0
000375  do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb')   COLLATE reverse } 1
000376  do_execsql_test e_expr-9.3 { SELECT  'abcd' <= 'bbbb'   COLLATE reverse } 0
000377  do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb')  COLLATE reverse } 1
000378  
000379  do_execsql_test e_expr-9.5 { SELECT  'abcd' > 'bbbb'    COLLATE reverse } 1
000380  do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb')   COLLATE reverse } 0
000381  do_execsql_test e_expr-9.7 { SELECT  'abcd' >= 'bbbb'   COLLATE reverse } 1
000382  do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb')  COLLATE reverse } 0
000383  
000384  do_execsql_test e_expr-9.10 { SELECT  'abcd' =  'ABCD'  COLLATE nocase } 1
000385  do_execsql_test e_expr-9.11 { SELECT ('abcd' =  'ABCD') COLLATE nocase } 0
000386  do_execsql_test e_expr-9.12 { SELECT  'abcd' == 'ABCD'  COLLATE nocase } 1
000387  do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0
000388  do_execsql_test e_expr-9.14 { SELECT  'abcd' IS 'ABCD'  COLLATE nocase } 1
000389  do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0
000390  
000391  do_execsql_test e_expr-9.16 { SELECT  'abcd' != 'ABCD'      COLLATE nocase } 0
000392  do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD')     COLLATE nocase } 1
000393  do_execsql_test e_expr-9.18 { SELECT  'abcd' <> 'ABCD'      COLLATE nocase } 0
000394  do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD')     COLLATE nocase } 1
000395  do_execsql_test e_expr-9.20 { SELECT  'abcd' IS NOT 'ABCD'  COLLATE nocase } 0
000396  do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1
000397  
000398  do_execsql_test e_expr-9.22 { 
000399    SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase 
000400  } 1
000401  do_execsql_test e_expr-9.23 { 
000402    SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase 
000403  } 0
000404  
000405  # EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE
000406  # operator overrides the collating sequence determined by the COLLATE
000407  # clause in a table column definition.
000408  #
000409  do_execsql_test e_expr-9.24 { 
000410    CREATE TABLE t24(a COLLATE NOCASE, b);
000411    INSERT INTO t24 VALUES('aaa', 1);
000412    INSERT INTO t24 VALUES('bbb', 2);
000413    INSERT INTO t24 VALUES('ccc', 3);
000414  } {}
000415  do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0}
000416  do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0}
000417  do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0}
000418  do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0}
000419  
000420  #-------------------------------------------------------------------------
000421  # Test statements related to literal values.
000422  #
000423  # EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating
000424  # point numbers, strings, BLOBs, or NULLs.
000425  #
000426  do_execsql_test e_expr-10.1.1 { SELECT typeof(5)       } {integer}
000427  do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1)     } {real}
000428  do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1')   } {text}
000429  do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob}
000430  do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL)    } {null}
000431  
000432  # "Scientific notation is supported for point literal values."
000433  #
000434  do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02)    } {real}
000435  do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5)       } {real}
000436  do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02            } {0.034}
000437  do_execsql_test e_expr-10.2.4 { SELECT 3e+4               } {30000.0}
000438  
000439  # EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing
000440  # the string in single quotes (').
000441  #
000442  # EVIDENCE-OF: R-07100-06606 A single quote within the string can be
000443  # encoded by putting two single quotes in a row - as in Pascal.
000444  #
000445  do_execsql_test e_expr-10.3.1 { SELECT 'is not' }         {{is not}}
000446  do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text}
000447  do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' }         {isn't}
000448  do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text}
000449  
000450  # EVIDENCE-OF: R-09593-03321 BLOB literals are string literals
000451  # containing hexadecimal data and preceded by a single "x" or "X"
000452  # character.
000453  #
000454  # EVIDENCE-OF: R-19836-11244 Example: X'53514C697465'
000455  #
000456  do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob
000457  do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob
000458  do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob
000459  do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob
000460  do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465')     } blob
000461  
000462  # EVIDENCE-OF: R-23914-51476 A literal value can also be the token
000463  # "NULL".
000464  #
000465  do_execsql_test e_expr-10.5.1 { SELECT NULL         } {{}}
000466  do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null}
000467  
000468  #-------------------------------------------------------------------------
000469  # Test statements related to bound parameters
000470  #
000471  
000472  proc parameter_test {tn sql params result} {
000473    set stmt [sqlite3_prepare_v2 db $sql -1]
000474  
000475    foreach {number name} $params {
000476      set nm [sqlite3_bind_parameter_name $stmt $number]
000477      do_test $tn.name.$number [list set {} $nm] $name
000478      sqlite3_bind_int $stmt $number [expr -1 * $number]
000479    }
000480  
000481    sqlite3_step $stmt
000482  
000483    set res [list]
000484    for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {
000485      lappend res [sqlite3_column_text $stmt $i]
000486    }
000487  
000488    set rc [sqlite3_finalize $stmt]
000489    do_test $tn.rc [list set {} $rc] SQLITE_OK
000490    do_test $tn.res [list set {} $res] $result
000491  }
000492  
000493  # EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN
000494  # holds a spot for the NNN-th parameter. NNN must be between 1 and
000495  # SQLITE_MAX_VARIABLE_NUMBER.
000496  #
000497  set mvn $SQLITE_MAX_VARIABLE_NUMBER
000498  parameter_test e_expr-11.1 "
000499    SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4
000500  "   "1 ?1  123 ?123 $mvn ?$mvn 4 ?4"   "-1 -123 -$mvn -123 -4"
000501  
000502  set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER"
000503  foreach {tn param_number} [list \
000504    2  0                                    \
000505    3  [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \
000506    4  [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \
000507    5  12345678903456789034567890234567890  \
000508    6  2147483648                           \
000509    7  2147483649                           \
000510    8  4294967296                           \
000511    9  4294967297                           \
000512    10 9223372036854775808                  \
000513    11 9223372036854775809                  \
000514    12 18446744073709551616                 \
000515    13 18446744073709551617                 \
000516  ] {
000517    do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg]
000518  }
000519  
000520  # EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a
000521  # number creates a parameter with a number one greater than the largest
000522  # parameter number already assigned.
000523  #
000524  # EVIDENCE-OF: R-42938-07030 If this means the parameter number is
000525  # greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error.
000526  #
000527  parameter_test e_expr-11.2.1 "SELECT ?"          {1 {}}       -1
000528  parameter_test e_expr-11.2.2 "SELECT ?, ?"       {1 {} 2 {}}  {-1 -2}
000529  parameter_test e_expr-11.2.3 "SELECT ?5, ?"      {5 ?5 6 {}}  {-5 -6}
000530  parameter_test e_expr-11.2.4 "SELECT ?, ?5"      {1 {} 5 ?5}  {-1 -5}
000531  parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" {
000532    1 {} 456 ?456 457 {}
000533  }  {-1 -456 -457}
000534  parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" {
000535    1 {} 456 ?456 4 ?4 457 {}
000536  }  {-1 -456 -4 -457}
000537  foreach {tn sql} [list                           \
000538    1  "SELECT ?$mvn, ?"                           \
000539    2  "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?"   \
000540    3  "SELECT ?[expr $mvn], ?5, ?6, ?"            \
000541  ] {
000542    do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}]
000543  }
000544  
000545  # EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name
000546  # holds a spot for a named parameter with the name :AAAA.
000547  #
000548  # Identifiers in SQLite consist of alphanumeric, '_' and '$' characters,
000549  # and any UTF characters with codepoints larger than 127 (non-ASCII 
000550  # characters).
000551  #
000552  parameter_test e_expr-11.2.1 {SELECT :AAAA}         {1 :AAAA}       -1
000553  parameter_test e_expr-11.2.2 {SELECT :123}          {1 :123}        -1
000554  parameter_test e_expr-11.2.3 {SELECT :__}           {1 :__}         -1
000555  parameter_test e_expr-11.2.4 {SELECT :_$_}          {1 :_$_}        -1
000556  parameter_test e_expr-11.2.5 "
000557    SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
000558  " "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
000559  parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1
000560  
000561  # EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon,
000562  # except that the name of the parameter created is @AAAA.
000563  #
000564  parameter_test e_expr-11.3.1 {SELECT @AAAA}         {1 @AAAA}       -1
000565  parameter_test e_expr-11.3.2 {SELECT @123}          {1 @123}        -1
000566  parameter_test e_expr-11.3.3 {SELECT @__}           {1 @__}         -1
000567  parameter_test e_expr-11.3.4 {SELECT @_$_}          {1 @_$_}        -1
000568  parameter_test e_expr-11.3.5 "
000569    SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
000570  " "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
000571  parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1
000572  
000573  # EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier
000574  # name also holds a spot for a named parameter with the name $AAAA.
000575  #
000576  # EVIDENCE-OF: R-55025-21042 The identifier name in this case can
000577  # include one or more occurrences of "::" and a suffix enclosed in
000578  # "(...)" containing any text at all.
000579  #
000580  # Note: Looks like an identifier cannot consist entirely of "::" 
000581  # characters or just a suffix. Also, the other named variable characters
000582  # (: and @) work the same way internally. Why not just document it that way?
000583  #
000584  parameter_test e_expr-11.4.1 {SELECT $AAAA}         {1 $AAAA}       -1
000585  parameter_test e_expr-11.4.2 {SELECT $123}          {1 $123}        -1
000586  parameter_test e_expr-11.4.3 {SELECT $__}           {1 $__}         -1
000587  parameter_test e_expr-11.4.4 {SELECT $_$_}          {1 $_$_}        -1
000588  parameter_test e_expr-11.4.5 "
000589    SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
000590  " "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
000591  parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1
000592  
000593  parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1
000594  parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1
000595  parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1
000596   
000597  # EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The
000598  # number assigned is one greater than the largest parameter number
000599  # already assigned.
000600  #
000601  # EVIDENCE-OF: R-42620-22184 If this means the parameter would be
000602  # assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an
000603  # error.
000604  #
000605  parameter_test e_expr-11.6.1 "SELECT ?, @abc"    {1 {} 2 @abc} {-1 -2}
000606  parameter_test e_expr-11.6.2 "SELECT ?123, :a1"  {123 ?123 124 :a1} {-123 -124}
000607  parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} {
000608    1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c
000609  } {-1 -8 -9 -10 -2 -11}
000610  foreach {tn sql} [list                           \
000611    1  "SELECT ?$mvn, \$::a"                       \
000612    2  "SELECT ?$mvn, ?4, @a1"                     \
000613    3  "SELECT ?[expr $mvn-2], :bag, @123, \$x"    \
000614  ] {
000615    do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}]
000616  }
000617  
000618  # EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values
000619  # using sqlite3_bind() are treated as NULL.
000620  #
000621  do_test e_expr-11.7.1 {
000622    set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1]
000623    sqlite3_step $stmt
000624  
000625    list [sqlite3_column_type $stmt 0] \
000626         [sqlite3_column_type $stmt 1] \
000627         [sqlite3_column_type $stmt 2] \
000628         [sqlite3_column_type $stmt 3] 
000629  } {NULL NULL NULL NULL}
000630  do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK
000631  
000632  #-------------------------------------------------------------------------
000633  # "Test" the syntax diagrams in lang_expr.html.
000634  #
000635  # -- syntax diagram signed-number
000636  #
000637  do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
000638  do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
000639  do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
000640  do_execsql_test e_expr-12.1.4 { 
000641    SELECT 1.4, +1.4, -1.4 
000642  } {1.4 1.4 -1.4}
000643  do_execsql_test e_expr-12.1.5 { 
000644    SELECT 1.5e+5, +1.5e+5, -1.5e+5 
000645  } {150000.0 150000.0 -150000.0}
000646  do_execsql_test e_expr-12.1.6 { 
000647    SELECT 0.0001, +0.0001, -0.0001 
000648  } {0.0001 0.0001 -0.0001}
000649  
000650  # -- syntax diagram literal-value
000651  #
000652  set sqlite_current_time 1
000653  do_execsql_test e_expr-12.2.1 {SELECT 123}               {123}
000654  do_execsql_test e_expr-12.2.2 {SELECT 123.4e05}          {12340000.0}
000655  do_execsql_test e_expr-12.2.3 {SELECT 'abcde'}           {abcde}
000656  do_execsql_test e_expr-12.2.4 {SELECT X'414243'}         {ABC}
000657  do_execsql_test e_expr-12.2.5 {SELECT NULL}              {{}}
000658  do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME}      {00:00:01}
000659  do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE}      {1970-01-01}
000660  do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
000661  set sqlite_current_time 0
000662  
000663  # -- syntax diagram expr
000664  #
000665  forcedelete test.db2
000666  execsql {
000667    ATTACH 'test.db2' AS dbname;
000668    CREATE TABLE dbname.tblname(cname);
000669  }
000670  
000671  proc glob {args} {return 1}
000672  db function glob glob
000673  db function match glob
000674  db function regexp glob
000675  
000676  foreach {tn expr} {
000677    1 123
000678    2 123.4e05
000679    3 'abcde'
000680    4 X'414243'
000681    5 NULL
000682    6 CURRENT_TIME
000683    7 CURRENT_DATE
000684    8 CURRENT_TIMESTAMP
000685  
000686    9 ?
000687   10 ?123
000688   11 @hello
000689   12 :world
000690   13 $tcl
000691   14 $tcl(array)
000692    
000693    15 cname
000694    16 tblname.cname
000695    17 dbname.tblname.cname
000696  
000697    18 "+ EXPR"
000698    19 "- EXPR"
000699    20 "NOT EXPR"
000700    21 "~ EXPR"
000701  
000702    22 "EXPR1 || EXPR2"
000703    23 "EXPR1 * EXPR2"
000704    24 "EXPR1 / EXPR2"
000705    25 "EXPR1 % EXPR2"
000706    26 "EXPR1 + EXPR2"
000707    27 "EXPR1 - EXPR2"
000708    28 "EXPR1 << EXPR2"
000709    29 "EXPR1 >> EXPR2"
000710    30 "EXPR1 & EXPR2"
000711    31 "EXPR1 | EXPR2"
000712    32 "EXPR1 < EXPR2"
000713    33 "EXPR1 <= EXPR2"
000714    34 "EXPR1 > EXPR2"
000715    35 "EXPR1 >= EXPR2"
000716    36 "EXPR1 = EXPR2"
000717    37 "EXPR1 == EXPR2"
000718    38 "EXPR1 != EXPR2"
000719    39 "EXPR1 <> EXPR2"
000720    40 "EXPR1 IS EXPR2"
000721    41 "EXPR1 IS NOT EXPR2"
000722    42 "EXPR1 AND EXPR2"
000723    43 "EXPR1 OR EXPR2"
000724   
000725    44 "count(*)"
000726    45 "count(DISTINCT EXPR)"
000727    46 "substr(EXPR, 10, 20)"
000728    47 "changes()"
000729   
000730    48 "( EXPR )"
000731   
000732    49 "CAST ( EXPR AS integer )"
000733    50 "CAST ( EXPR AS 'abcd' )"
000734    51 "CAST ( EXPR AS 'ab$ $cd' )"
000735   
000736    52 "EXPR COLLATE nocase"
000737    53 "EXPR COLLATE binary"
000738   
000739    54 "EXPR1 LIKE EXPR2"
000740    55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
000741    56 "EXPR1 GLOB EXPR2"
000742    57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
000743    58 "EXPR1 REGEXP EXPR2"
000744    59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
000745    60 "EXPR1 MATCH EXPR2"
000746    61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
000747    62 "EXPR1 NOT LIKE EXPR2"
000748    63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
000749    64 "EXPR1 NOT GLOB EXPR2"
000750    65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
000751    66 "EXPR1 NOT REGEXP EXPR2"
000752    67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
000753    68 "EXPR1 NOT MATCH EXPR2"
000754    69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"
000755   
000756    70 "EXPR ISNULL"
000757    71 "EXPR NOTNULL"
000758    72 "EXPR NOT NULL"
000759   
000760    73 "EXPR1 IS EXPR2"
000761    74 "EXPR1 IS NOT EXPR2"
000762  
000763    75 "EXPR NOT BETWEEN EXPR1 AND EXPR2"
000764    76 "EXPR BETWEEN EXPR1 AND EXPR2"
000765  
000766    77 "EXPR NOT IN (SELECT cname FROM tblname)"
000767    78 "EXPR NOT IN (1)"
000768    79 "EXPR NOT IN (1, 2, 3)"
000769    80 "EXPR NOT IN tblname"
000770    81 "EXPR NOT IN dbname.tblname"
000771    82 "EXPR IN (SELECT cname FROM tblname)"
000772    83 "EXPR IN (1)"
000773    84 "EXPR IN (1, 2, 3)"
000774    85 "EXPR IN tblname"
000775    86 "EXPR IN dbname.tblname"
000776  
000777    87 "EXISTS (SELECT cname FROM tblname)"
000778    88 "NOT EXISTS (SELECT cname FROM tblname)"
000779  
000780    89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
000781    90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END"
000782    91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
000783    92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
000784    93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
000785    94 "CASE WHEN EXPR1 THEN EXPR2 END"
000786    95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
000787    96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
000788  } {
000789  
000790    # If the expression string being parsed contains "EXPR2", then replace
000791    # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it 
000792    # contains "EXPR", then replace EXPR with an arbitrary SQL expression.
000793    # 
000794    set elist [list $expr]
000795    if {[string match *EXPR2* $expr]} {
000796      set elist [list]
000797      foreach {e1 e2} { cname "34+22" } {
000798        lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr]
000799      }
000800    } 
000801    if {[string match *EXPR* $expr]} {
000802      set elist2 [list]
000803      foreach el $elist {
000804        foreach e { cname "34+22" } {
000805          lappend elist2 [string map [list EXPR $e] $el]
000806        }
000807      }
000808      set elist $elist2
000809    }
000810  
000811    set x 0
000812    foreach e $elist {
000813      incr x
000814      do_test e_expr-12.3.$tn.$x { 
000815        set rc [catch { execsql "SELECT $e FROM tblname" } msg]
000816      } {0}
000817    }
000818  }
000819  
000820  # -- syntax diagram raise-function
000821  #
000822  foreach {tn raiseexpr} {
000823    1 "RAISE(IGNORE)"
000824    2 "RAISE(ROLLBACK, 'error message')"
000825    3 "RAISE(ABORT, 'error message')"
000826    4 "RAISE(FAIL, 'error message')"
000827  } {
000828    do_execsql_test e_expr-12.4.$tn "
000829      CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN
000830        SELECT $raiseexpr ;
000831      END;
000832    " {}
000833  }
000834  
000835  #-------------------------------------------------------------------------
000836  # Test the statements related to the BETWEEN operator.
000837  #
000838  # EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically
000839  # equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent
000840  # to "x>=y AND x<=z" except that with BETWEEN, the x expression is
000841  # only evaluated once.
000842  #
000843  db func x x
000844  proc x {} { incr ::xcount ; return [expr $::x] }
000845  foreach {tn x expr res nEval} {
000846    1  10  "x() >= 5 AND x() <= 15"  1  2
000847    2  10  "x() BETWEEN 5 AND 15"    1  1
000848  
000849    3   5  "x() >= 5 AND x() <= 5"   1  2
000850    4   5  "x() BETWEEN 5 AND 5"     1  1
000851  
000852    5   9  "(x(),8) >= (9,7) AND (x(),8)<=(9,10)"  1 2
000853    6   9  "(x(),8) BETWEEN (9,7) AND (9,10)"      1 1
000854  } {
000855    do_test e_expr-13.1.$tn {
000856      set ::xcount 0
000857      set a [execsql "SELECT $expr"]
000858      list $::xcount $a
000859    } [list $nEval $res]
000860  }
000861  
000862  # EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is
000863  # the same as the precedence as operators == and != and LIKE and groups
000864  # left to right.
000865  # 
000866  # Therefore, BETWEEN groups more tightly than operator "AND", but less
000867  # so than "<".
000868  #
000869  do_execsql_test e_expr-13.2.1  { SELECT 1 == 10 BETWEEN 0 AND 2   }  1
000870  do_execsql_test e_expr-13.2.2  { SELECT (1 == 10) BETWEEN 0 AND 2 }  1
000871  do_execsql_test e_expr-13.2.3  { SELECT 1 == (10 BETWEEN 0 AND 2) }  0
000872  do_execsql_test e_expr-13.2.4  { SELECT  6 BETWEEN 4 AND 8 == 1 }    1
000873  do_execsql_test e_expr-13.2.5  { SELECT (6 BETWEEN 4 AND 8) == 1 }   1
000874  do_execsql_test e_expr-13.2.6  { SELECT  6 BETWEEN 4 AND (8 == 1) }  0
000875  
000876  do_execsql_test e_expr-13.2.7  { SELECT  5 BETWEEN 0 AND 0  != 1 }   1
000877  do_execsql_test e_expr-13.2.8  { SELECT (5 BETWEEN 0 AND 0) != 1 }   1
000878  do_execsql_test e_expr-13.2.9  { SELECT  5 BETWEEN 0 AND (0 != 1) }  0
000879  do_execsql_test e_expr-13.2.10 { SELECT  1 != 0  BETWEEN 0 AND 2  }  1
000880  do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2  }  1
000881  do_execsql_test e_expr-13.2.12 { SELECT  1 != (0 BETWEEN 0 AND 2) }  0
000882  
000883  do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2   }  1
000884  do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 }  1
000885  do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) }  0
000886  do_execsql_test e_expr-13.2.16 { SELECT  6 BETWEEN 4 AND 8 LIKE 1   }  1
000887  do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1  }  1
000888  do_execsql_test e_expr-13.2.18 { SELECT  6 BETWEEN 4 AND (8 LIKE 1) }  0
000889  
000890  do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1   } 0
000891  do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0
000892  do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1
000893  do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0   } 0
000894  do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0
000895  do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1
000896  
000897  do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1   } 1
000898  do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1
000899  do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0
000900  do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3    } 0
000901  do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3)  } 0
000902  do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3  } 1
000903  
000904  #-------------------------------------------------------------------------
000905  # Test the statements related to the LIKE and GLOB operators.
000906  #
000907  # EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching
000908  # comparison.
000909  #
000910  # EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE
000911  # operator contains the pattern and the left hand operand contains the
000912  # string to match against the pattern.
000913  #
000914  do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0
000915  do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1
000916  
000917  # EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern
000918  # matches any sequence of zero or more characters in the string.
000919  #
000920  do_execsql_test e_expr-14.2.1 { SELECT 'abde'    LIKE 'ab%de' } 1
000921  do_execsql_test e_expr-14.2.2 { SELECT 'abXde'   LIKE 'ab%de' } 1
000922  do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1
000923  
000924  # EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern
000925  # matches any single character in the string.
000926  #
000927  do_execsql_test e_expr-14.3.1 { SELECT 'abde'    LIKE 'ab_de' } 0
000928  do_execsql_test e_expr-14.3.2 { SELECT 'abXde'   LIKE 'ab_de' } 1
000929  do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0
000930  
000931  # EVIDENCE-OF: R-59007-20454 Any other character matches itself or its
000932  # lower/upper case equivalent (i.e. case-insensitive matching).
000933  #
000934  do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1
000935  do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1
000936  do_execsql_test e_expr-14.4.3 { SELECT 'ac'  LIKE 'aBc' } 0
000937  
000938  # EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case
000939  # for ASCII characters by default.
000940  #
000941  # EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
000942  # default for unicode characters that are beyond the ASCII range.
000943  #
000944  # EVIDENCE-OF: R-44381-11669 the expression
000945  # 'a'&nbsp;LIKE&nbsp;'A' is TRUE but
000946  # '&aelig;'&nbsp;LIKE&nbsp;'&AElig;' is FALSE.
000947  #
000948  #   The restriction to ASCII characters does not apply if the ICU
000949  #   library is compiled in. When ICU is enabled SQLite does not act
000950  #   as it does "by default".
000951  #
000952  do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a'         } 1
000953  ifcapable !icu {
000954    do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0
000955  }
000956  
000957  # EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
000958  # then the expression following the ESCAPE keyword must evaluate to a
000959  # string consisting of a single character.
000960  #
000961  do_catchsql_test e_expr-14.6.1 { 
000962    SELECT 'A' LIKE 'a' ESCAPE '12' 
000963  } {1 {ESCAPE expression must be a single character}}
000964  do_catchsql_test e_expr-14.6.2 { 
000965    SELECT 'A' LIKE 'a' ESCAPE '' 
000966  } {1 {ESCAPE expression must be a single character}}
000967  do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' }    {0 1}
000968  do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1}
000969  
000970  # EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE
000971  # pattern to include literal percent or underscore characters.
000972  #
000973  # EVIDENCE-OF: R-13345-31830 The escape character followed by a percent
000974  # symbol (%), underscore (_), or a second instance of the escape
000975  # character itself matches a literal percent symbol, underscore, or a
000976  # single escape character, respectively.
000977  #
000978  do_execsql_test e_expr-14.7.1  { SELECT 'abc%'  LIKE 'abcX%' ESCAPE 'X' } 1
000979  do_execsql_test e_expr-14.7.2  { SELECT 'abc5'  LIKE 'abcX%' ESCAPE 'X' } 0
000980  do_execsql_test e_expr-14.7.3  { SELECT 'abc'   LIKE 'abcX%' ESCAPE 'X' } 0
000981  do_execsql_test e_expr-14.7.4  { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0
000982  do_execsql_test e_expr-14.7.5  { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0
000983  
000984  do_execsql_test e_expr-14.7.6  { SELECT 'abc_'  LIKE 'abcX_' ESCAPE 'X' } 1
000985  do_execsql_test e_expr-14.7.7  { SELECT 'abc5'  LIKE 'abcX_' ESCAPE 'X' } 0
000986  do_execsql_test e_expr-14.7.8  { SELECT 'abc'   LIKE 'abcX_' ESCAPE 'X' } 0
000987  do_execsql_test e_expr-14.7.9  { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0
000988  do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0
000989  
000990  do_execsql_test e_expr-14.7.11 { SELECT 'abcX'  LIKE 'abcXX' ESCAPE 'X' } 1
000991  do_execsql_test e_expr-14.7.12 { SELECT 'abc5'  LIKE 'abcXX' ESCAPE 'X' } 0
000992  do_execsql_test e_expr-14.7.13 { SELECT 'abc'   LIKE 'abcXX' ESCAPE 'X' } 0
000993  do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0
000994  
000995  # EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
000996  # calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
000997  #
000998  proc likefunc {args} {
000999    eval lappend ::likeargs $args
001000    return 1
001001  }
001002  db func like -argcount 2 likefunc
001003  db func like -argcount 3 likefunc
001004  set ::likeargs [list]
001005  do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
001006  do_test         e_expr-15.1.2 { set likeargs } {def abc}
001007  set ::likeargs [list]
001008  do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
001009  do_test         e_expr-15.1.4 { set likeargs } {def abc X}
001010  db close
001011  sqlite3 db test.db
001012  
001013  # EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case
001014  # sensitive using the case_sensitive_like pragma.
001015  #
001016  do_execsql_test e_expr-16.1.1  { SELECT 'abcxyz' LIKE 'ABC%' } 1
001017  do_execsql_test e_expr-16.1.1b { SELECT 'abc%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1
001018  do_execsql_test e_expr-16.1.2  { PRAGMA case_sensitive_like = 1 } {}
001019  do_execsql_test e_expr-16.1.3  { SELECT 'abcxyz' LIKE 'ABC%' } 0
001020  do_execsql_test e_expr-16.1.3b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 0
001021  do_execsql_test e_expr-16.1.4  { SELECT 'ABCxyz' LIKE 'ABC%' } 1
001022  do_execsql_test e_expr-16.1.4b { SELECT 'ABC%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1
001023  do_execsql_test e_expr-16.1.5  { PRAGMA case_sensitive_like = 0 } {}
001024  do_execsql_test e_expr-16.1.6  { SELECT 'abcxyz' LIKE 'ABC%' } 1
001025  do_execsql_test e_expr-16.1.6b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1
001026  do_execsql_test e_expr-16.1.7  { SELECT 'ABCxyz' LIKE 'ABC%' } 1
001027  do_execsql_test e_expr-16.1.7b { SELECT 'ABC%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1
001028  
001029  # EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but
001030  # uses the Unix file globbing syntax for its wildcards.
001031  #
001032  # EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE.
001033  #
001034  do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0
001035  do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1
001036  do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0
001037  do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1
001038  
001039  do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1
001040  do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0
001041  do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0
001042  
001043  # EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the
001044  # NOT keyword to invert the sense of the test.
001045  #
001046  do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1
001047  do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0
001048  do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0
001049  do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0
001050  do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1
001051  
001052  db nullvalue null
001053  do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null
001054  do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null
001055  do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null
001056  do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null
001057  db nullvalue {}
001058  
001059  # EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by
001060  # calling the function glob(Y,X) and can be modified by overriding that
001061  # function.
001062  proc globfunc {args} {
001063    eval lappend ::globargs $args
001064    return 1
001065  }
001066  db func glob -argcount 2 globfunc
001067  set ::globargs [list]
001068  do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1
001069  do_test         e_expr-17.3.2 { set globargs } {def abc}
001070  set ::globargs [list]
001071  do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0
001072  do_test         e_expr-17.3.4 { set globargs } {Y X}
001073  sqlite3 db test.db
001074  
001075  # EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
001076  # default and so use of the REGEXP operator will normally result in an
001077  # error message.
001078  #
001079  #   There is a regexp function if ICU is enabled though.
001080  #
001081  ifcapable !icu {
001082    do_catchsql_test e_expr-18.1.1 { 
001083      SELECT regexp('abc', 'def') 
001084    } {1 {no such function: regexp}}
001085    do_catchsql_test e_expr-18.1.2 { 
001086      SELECT 'abc' REGEXP 'def'
001087    } {1 {no such function: REGEXP}}
001088  }
001089  
001090  # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
001091  # the regexp() user function.
001092  #
001093  # EVIDENCE-OF: R-65524-61849 If an application-defined SQL function
001094  # named "regexp" is added at run-time, then the "X REGEXP Y" operator
001095  # will be implemented as a call to "regexp(Y,X)".
001096  #
001097  proc regexpfunc {args} {
001098    eval lappend ::regexpargs $args
001099    return 1
001100  }
001101  db func regexp -argcount 2 regexpfunc
001102  set ::regexpargs [list]
001103  do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
001104  do_test         e_expr-18.2.2 { set regexpargs } {def abc}
001105  set ::regexpargs [list]
001106  do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
001107  do_test         e_expr-18.2.4 { set regexpargs } {Y X}
001108  sqlite3 db test.db
001109  
001110  # EVIDENCE-OF: R-42037-37826 The default match() function implementation
001111  # raises an exception and is not really useful for anything.
001112  #
001113  do_catchsql_test e_expr-19.1.1 { 
001114    SELECT 'abc' MATCH 'def' 
001115  } {1 {unable to use function MATCH in the requested context}}
001116  do_catchsql_test e_expr-19.1.2 { 
001117    SELECT match('abc', 'def')
001118  } {1 {unable to use function MATCH in the requested context}}
001119  
001120  # EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
001121  # the match() application-defined function.
001122  #
001123  # EVIDENCE-OF: R-06021-09373 But extensions can override the match()
001124  # function with more helpful logic.
001125  #
001126  proc matchfunc {args} {
001127    eval lappend ::matchargs $args
001128    return 1
001129  }
001130  db func match -argcount 2 matchfunc
001131  set ::matchargs [list]
001132  do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
001133  do_test         e_expr-19.2.2 { set matchargs } {def abc}
001134  set ::matchargs [list]
001135  do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
001136  do_test         e_expr-19.2.4 { set matchargs } {Y X}
001137  sqlite3 db test.db
001138  
001139  #-------------------------------------------------------------------------
001140  # Test cases for the testable statements related to the CASE expression.
001141  #
001142  # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE
001143  # expression: those with a base expression and those without.
001144  #
001145  do_execsql_test e_expr-20.1 {
001146    SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
001147  } {true}
001148  do_execsql_test e_expr-20.2 {
001149    SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
001150  } {false}
001151  
001152  proc var {nm} {
001153    lappend ::varlist $nm
001154    return [set "::$nm"]
001155  }
001156  db func var var
001157  
001158  # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each
001159  # WHEN expression is evaluated and the result treated as a boolean,
001160  # starting with the leftmost and continuing to the right.
001161  #
001162  foreach {a b c} {0 0 0} break
001163  set varlist [list]
001164  do_execsql_test e_expr-21.1.1 {
001165    SELECT CASE WHEN var('a') THEN 'A' 
001166                WHEN var('b') THEN 'B' 
001167                WHEN var('c') THEN 'C' END
001168  } {{}}
001169  do_test e_expr-21.1.2 { set varlist } {a b c}
001170  set varlist [list]
001171  do_execsql_test e_expr-21.1.3 {
001172    SELECT CASE WHEN var('c') THEN 'C' 
001173                WHEN var('b') THEN 'B' 
001174                WHEN var('a') THEN 'A' 
001175                ELSE 'no result'
001176    END
001177  } {{no result}}
001178  do_test e_expr-21.1.4 { set varlist } {c b a}
001179  
001180  # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the
001181  # evaluation of the THEN expression that corresponds to the first WHEN
001182  # expression that evaluates to true.
001183  #
001184  foreach {a b c} {0 1 0} break
001185  do_execsql_test e_expr-21.2.1 {
001186    SELECT CASE WHEN var('a') THEN 'A' 
001187                WHEN var('b') THEN 'B' 
001188                WHEN var('c') THEN 'C' 
001189                ELSE 'no result'
001190    END
001191  } {B}
001192  foreach {a b c} {0 1 1} break
001193  do_execsql_test e_expr-21.2.2 {
001194    SELECT CASE WHEN var('a') THEN 'A' 
001195                WHEN var('b') THEN 'B' 
001196                WHEN var('c') THEN 'C'
001197                ELSE 'no result'
001198    END
001199  } {B}
001200  foreach {a b c} {0 0 1} break
001201  do_execsql_test e_expr-21.2.3 {
001202    SELECT CASE WHEN var('a') THEN 'A' 
001203                WHEN var('b') THEN 'B' 
001204                WHEN var('c') THEN 'C'
001205                ELSE 'no result'
001206    END
001207  } {C}
001208  
001209  # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions
001210  # evaluate to true, the result of evaluating the ELSE expression, if
001211  # any.
001212  #
001213  foreach {a b c} {0 0 0} break
001214  do_execsql_test e_expr-21.3.1 {
001215    SELECT CASE WHEN var('a') THEN 'A' 
001216                WHEN var('b') THEN 'B' 
001217                WHEN var('c') THEN 'C'
001218                ELSE 'no result'
001219    END
001220  } {{no result}}
001221  
001222  # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of
001223  # the WHEN expressions are true, then the overall result is NULL.
001224  #
001225  db nullvalue null
001226  do_execsql_test e_expr-21.3.2 {
001227    SELECT CASE WHEN var('a') THEN 'A' 
001228                WHEN var('b') THEN 'B' 
001229                WHEN var('c') THEN 'C'
001230    END
001231  } {null}
001232  db nullvalue {}
001233  
001234  # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when
001235  # evaluating WHEN terms.
001236  #
001237  do_execsql_test e_expr-21.4.1 {
001238    SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END
001239  } {B}
001240  do_execsql_test e_expr-21.4.2 {
001241    SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END
001242  } {C}
001243  
001244  # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base
001245  # expression is evaluated just once and the result is compared against
001246  # the evaluation of each WHEN expression from left to right.
001247  #
001248  # Note: This test case tests the "evaluated just once" part of the above
001249  # statement. Tests associated with the next two statements test that the
001250  # comparisons take place.
001251  #
001252  foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break
001253  set ::varlist [list]
001254  do_execsql_test e_expr-22.1.1 {
001255    SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END
001256  } {C}
001257  do_test e_expr-22.1.2 { set ::varlist } {a}
001258  
001259  # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the
001260  # evaluation of the THEN expression that corresponds to the first WHEN
001261  # expression for which the comparison is true.
001262  #
001263  do_execsql_test e_expr-22.2.1 {
001264    SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001265  } {B}
001266  do_execsql_test e_expr-22.2.2 {
001267    SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001268  } {A}
001269  
001270  # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions
001271  # evaluate to a value equal to the base expression, the result of
001272  # evaluating the ELSE expression, if any.
001273  #
001274  do_execsql_test e_expr-22.3.1 {
001275    SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END
001276  } {D}
001277  
001278  # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of
001279  # the WHEN expressions produce a result equal to the base expression,
001280  # the overall result is NULL.
001281  #
001282  do_execsql_test e_expr-22.4.1 {
001283    SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001284  } {{}}
001285  db nullvalue null
001286  do_execsql_test e_expr-22.4.2 {
001287    SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001288  } {null}
001289  db nullvalue {}
001290  
001291  # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a
001292  # WHEN expression, the same collating sequence, affinity, and
001293  # NULL-handling rules apply as if the base expression and WHEN
001294  # expression are respectively the left- and right-hand operands of an =
001295  # operator.
001296  #
001297  proc rev {str} {
001298    set ret ""
001299    set chars [split $str]
001300    for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
001301      append ret [lindex $chars $i]
001302    }
001303    set ret
001304  }
001305  proc reverse {lhs rhs} {
001306    string compare [rev $lhs] [rev $rhs]
001307  }
001308  db collate reverse reverse
001309  do_execsql_test e_expr-23.1.1 {
001310    CREATE TABLE t1(
001311      a TEXT     COLLATE NOCASE,
001312      b          COLLATE REVERSE,
001313      c INTEGER,
001314      d BLOB
001315    );
001316    INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
001317  } {}
001318  do_execsql_test e_expr-23.1.2 {
001319    SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
001320  } {B}
001321  do_execsql_test e_expr-23.1.3 {
001322    SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
001323  } {B}
001324  do_execsql_test e_expr-23.1.4 {
001325    SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
001326  } {B}
001327  do_execsql_test e_expr-23.1.5 {
001328    SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
001329  } {B}
001330  do_execsql_test e_expr-23.1.6 {
001331    SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
001332  } {B}
001333  do_execsql_test e_expr-23.1.7 {
001334    SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
001335  } {A}
001336  do_execsql_test e_expr-23.1.8 {
001337    SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
001338  } {B}
001339  do_execsql_test e_expr-23.1.9 {
001340    SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END
001341  } {B}
001342  
001343  # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the
001344  # result of the CASE is always the result of evaluating the ELSE
001345  # expression if it exists, or NULL if it does not.
001346  #
001347  do_execsql_test e_expr-24.1.1 {
001348    SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END;
001349  } {{}}
001350  do_execsql_test e_expr-24.1.2 {
001351    SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END;
001352  } {C}
001353  
001354  # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy,
001355  # or short-circuit, evaluation.
001356  #
001357  set varlist [list]
001358  foreach {a b c} {0 1 0} break
001359  do_execsql_test e_expr-25.1.1 {
001360    SELECT CASE WHEN var('a') THEN 'A' 
001361                WHEN var('b') THEN 'B' 
001362                WHEN var('c') THEN 'C' 
001363    END
001364  } {B}
001365  do_test e_expr-25.1.2 { set ::varlist } {a b}
001366  set varlist [list]
001367  do_execsql_test e_expr-25.1.3 {
001368    SELECT CASE '0' WHEN var('a') THEN 'A' 
001369                    WHEN var('b') THEN 'B' 
001370                    WHEN var('c') THEN 'C' 
001371    END
001372  } {A}
001373  do_test e_expr-25.1.4 { set ::varlist } {a}
001374  
001375  # EVIDENCE-OF: R-34773-62253 The only difference between the following
001376  # two CASE expressions is that the x expression is evaluated exactly
001377  # once in the first example but might be evaluated multiple times in the
001378  # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN
001379  # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
001380  #
001381  proc ceval {x} {
001382    incr ::evalcount
001383    return $x
001384  }
001385  db func ceval ceval
001386  set ::evalcount 0
001387  
001388  do_execsql_test e_expr-26.1.1 {
001389    CREATE TABLE t2(x, w1, r1, w2, r2, r3);
001390    INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3');
001391    INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3');
001392    INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3');
001393  } {}
001394  do_execsql_test e_expr-26.1.2 {
001395    SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
001396  } {R1 R2 R3}
001397  do_execsql_test e_expr-26.1.3 {
001398    SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2
001399  } {R1 R2 R3}
001400  
001401  do_execsql_test e_expr-26.1.4 {
001402    SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
001403  } {R1 R2 R3}
001404  do_test e_expr-26.1.5 { set ::evalcount } {3}
001405  set ::evalcount 0
001406  do_execsql_test e_expr-26.1.6 {
001407    SELECT CASE 
001408      WHEN ceval(x)=w1 THEN r1 
001409      WHEN ceval(x)=w2 THEN r2 
001410      ELSE r3 END 
001411    FROM t2
001412  } {R1 R2 R3}
001413  do_test e_expr-26.1.6 { set ::evalcount } {5}
001414  
001415  
001416  #-------------------------------------------------------------------------
001417  # Test statements related to CAST expressions.
001418  #
001419  # EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the
001420  # conversion that takes place when a column affinity is applied to a
001421  # value except that with the CAST operator the conversion always takes
001422  # place even if the conversion lossy and irreversible, whereas column
001423  # affinity only changes the data type of a value if the change is
001424  # lossless and reversible.
001425  #
001426  do_execsql_test e_expr-27.1.1 {
001427    CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
001428    INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
001429    SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
001430  } {blob UVU text 1.23abc real 4.5}
001431  do_execsql_test e_expr-27.1.2 {
001432    SELECT 
001433      typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
001434      typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
001435      typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
001436  } {text UVU real 1.23 integer 4}
001437  
001438  # EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the
001439  # result of the CAST expression is also NULL.
001440  #
001441  do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
001442  do_expr_test e_expr-27.2.2 { CAST(NULL AS text) }    null {}
001443  do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) }    null {}
001444  do_expr_test e_expr-27.2.4 { CAST(NULL AS number) }  null {}
001445  
001446  # EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result
001447  # is determined by applying the rules for determining column affinity to
001448  # the type-name.
001449  #
001450  # The R-29283-15561 requirement above is demonstrated by all of the 
001451  # subsequent e_expr-26 tests.
001452  #
001453  # EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no
001454  # affinity causes the value to be converted into a BLOB.
001455  #
001456  do_expr_test e_expr-27.3.1 { CAST('abc' AS blob)       } blob abc
001457  do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
001458  do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi
001459  
001460  # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
001461  # the value to TEXT in the encoding of the database connection, then
001462  # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
001463  #
001464  do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
001465  do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) }   X'343536'
001466  do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) }  X'312E3738'
001467  rename db db2
001468  sqlite3 db :memory:
001469  ifcapable {utf16} {
001470  db eval { PRAGMA encoding = 'utf-16le' }
001471  do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
001472  do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) }   X'340035003600'
001473  do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) }  X'31002E0037003800'
001474  }
001475  db close
001476  sqlite3 db :memory:
001477  db eval { PRAGMA encoding = 'utf-16be' }
001478  ifcapable {utf16} {
001479  do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
001480  do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) }   X'003400350036'
001481  do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) }  X'0031002E00370038'
001482  }
001483  db close
001484  rename db2 db
001485  
001486  # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
001487  # of bytes that make up the BLOB is interpreted as text encoded using
001488  # the database encoding.
001489  #
001490  do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
001491  do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
001492  rename db db2
001493  sqlite3 db :memory:
001494  db eval { PRAGMA encoding = 'utf-16le' }
001495  ifcapable {utf16} {
001496  do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
001497  do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
001498  }
001499  db close
001500  rename db2 db
001501  
001502  # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
001503  # renders the value as if via sqlite3_snprintf() except that the
001504  # resulting TEXT uses the encoding of the database connection.
001505  #
001506  do_expr_test e_expr-28.2.1 { CAST (1 AS text)   }     text 1
001507  do_expr_test e_expr-28.2.2 { CAST (45 AS text)  }     text 45
001508  do_expr_test e_expr-28.2.3 { CAST (-45 AS text) }     text -45
001509  do_expr_test e_expr-28.2.4 { CAST (8.8 AS text)    }  text 8.8
001510  do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) }  text 230000.0
001511  do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
001512  do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) }     text 0.0
001513  do_expr_test e_expr-28.2.7 { CAST (0 AS text) }       text 0
001514  
001515  # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
001516  # value is first converted to TEXT.
001517  #
001518  do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
001519  do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
001520  do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
001521  do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
001522  rename db db2
001523  sqlite3 db :memory:
001524  ifcapable {utf16} {
001525  db eval { PRAGMA encoding = 'utf-16le' }
001526  do_expr_test e_expr-29.1.5 { 
001527      CAST (X'31002E0032003300' AS REAL) } real 1.23
001528  do_expr_test e_expr-29.1.6 { 
001529      CAST (X'3200330030002E003000' AS REAL) } real 230.0
001530  do_expr_test e_expr-29.1.7 { 
001531      CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
001532  do_expr_test e_expr-29.1.8 { 
001533      CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
001534  }
001535  db close
001536  rename db2 db
001537  
001538  # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the
001539  # longest possible prefix of the value that can be interpreted as a real
001540  # number is extracted from the TEXT value and the remainder ignored.
001541  #
001542  do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23
001543  do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45
001544  do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212
001545  do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0
001546  
001547  # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are
001548  # ignored when converging from TEXT to REAL.
001549  #
001550  do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23
001551  do_expr_test e_expr-29.3.2 { CAST('    1.45.23abcd' AS REAL) } real 1.45
001552  do_expr_test e_expr-29.3.3 { CAST('   -2.12e-01ABC' AS REAL) } real -0.212
001553  do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0
001554  
001555  # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be
001556  # interpreted as a real number, the result of the conversion is 0.0.
001557  #
001558  do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0
001559  do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0
001560  do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0
001561  
001562  # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the
001563  # value is first converted to TEXT.
001564  #
001565  do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123
001566  do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678
001567  do_expr_test e_expr-30.1.3 { 
001568    CAST(X'31303030303030' AS INTEGER) 
001569  } integer 1000000
001570  do_expr_test e_expr-30.1.4 { 
001571    CAST(X'2D31313235383939393036383432363234' AS INTEGER) 
001572  } integer -1125899906842624
001573  
001574  rename db db2
001575  sqlite3 db :memory:
001576  ifcapable {utf16} {
001577  execsql { PRAGMA encoding = 'utf-16be' }
001578  do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123
001579  do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678
001580  do_expr_test e_expr-30.1.7 { 
001581    CAST(X'0031003000300030003000300030' AS INTEGER) 
001582  } integer 1000000
001583  do_expr_test e_expr-30.1.8 { 
001584    CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER) 
001585  } integer -1125899906842624
001586  }
001587  db close
001588  rename db2 db
001589  
001590  # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the
001591  # longest possible prefix of the value that can be interpreted as an
001592  # integer number is extracted from the TEXT value and the remainder
001593  # ignored.
001594  #
001595  do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123
001596  do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523
001597  do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2
001598  do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1
001599  
001600  # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when
001601  # converting from TEXT to INTEGER are ignored.
001602  #
001603  do_expr_test e_expr-30.3.1 { CAST('   123abcd' AS INT) } integer 123
001604  do_expr_test e_expr-30.3.2 { CAST('  14523abcd' AS INT) } integer 14523
001605  do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
001606  do_expr_test e_expr-30.3.4 { CAST('     1 2 3 4' AS INT) } integer 1
001607  
001608  # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
001609  # interpreted as an integer number, the result of the conversion is 0.
001610  #
001611  do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
001612  do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
001613  do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
001614  
001615  # EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal
001616  # integers only &mdash; conversion of hexadecimal integers stops at
001617  # the "x" in the "0x" prefix of the hexadecimal integer string and thus
001618  # result of the CAST is always zero.
001619  do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0
001620  do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0
001621  
001622  # EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER
001623  # results in the integer between the REAL value and zero that is closest
001624  # to the REAL value.
001625  #
001626  do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
001627  do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
001628  do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
001629  do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
001630  
001631  # EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest
001632  # possible signed integer (+9223372036854775807) then the result is the
001633  # greatest possible signed integer and if the REAL is less than the
001634  # least possible signed integer (-9223372036854775808) then the result
001635  # is the least possible signed integer.
001636  #
001637  do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807
001638  do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
001639  do_expr_test e_expr-31.2.3 { 
001640    CAST(-9223372036854775809.0 AS INT)
001641  } integer -9223372036854775808
001642  do_expr_test e_expr-31.2.4 { 
001643    CAST(9223372036854775809.0 AS INT)
001644  } integer 9223372036854775807
001645  
001646  
001647  # EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC
001648  # yields either an INTEGER or a REAL result.
001649  #
001650  # EVIDENCE-OF: R-48945-04866 If the input text looks like an integer
001651  # (there is no decimal point nor exponent) and the value is small enough
001652  # to fit in a 64-bit signed integer, then the result will be INTEGER.
001653  #
001654  # EVIDENCE-OF: R-47045-23194 Input text that looks like floating point
001655  # (there is a decimal point and/or an exponent) and the text describes a
001656  # value that can be losslessly converted back and forth between IEEE 754
001657  # 64-bit float and a 51-bit signed integer, then the result is INTEGER.
001658  #
001659  do_expr_test e_expr-32.1.1 { CAST('45'   AS NUMERIC)  } integer 45
001660  do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC)  } integer 45
001661  do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC)  } real 45.2
001662  do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
001663  do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
001664  do_expr_test e_expr-32.1.6 {CAST( '9.223372036e14' AS NUMERIC)} integer  922337203600000
001665  do_expr_test e_expr-32.1.7 {CAST('-9.223372036e14' AS NUMERIC)} integer -922337203600000
001666  do_test e_expr-32.1.8 {
001667    set expr {CAST( '9.223372036e15' AS NUMERIC)}
001668    db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value"  break;
001669    list $type $value
001670  } {real 9.22337e+15}
001671  do_test e_expr-32.1.9 {
001672    set expr {CAST('-9.223372036e15' AS NUMERIC)}
001673    db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value"  break;
001674    list $type $value
001675  } {real -9.22337e+15}
001676  
001677  # EVIDENCE-OF: R-50300-26941 Any text input that describes a value
001678  # outside the range of a 64-bit signed integer yields a REAL result.
001679  #
001680  do_expr_test e_expr-32.1.20 { CAST('9223372036854775807' AS numeric) } \
001681     integer 9223372036854775807
001682  do_expr_test e_expr-32.1.21 { CAST('9223372036854775808' AS numeric) } \
001683     real 9.22337203685478e+18
001684  do_expr_test e_expr-32.1.22 { CAST('-9223372036854775808' AS numeric) } \
001685     integer -9223372036854775808
001686  do_expr_test e_expr-32.1.23 { CAST('-9223372036854775809' AS numeric) } \
001687     real -9.22337203685478e+18
001688  
001689  # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
001690  # is a no-op, even if a real value could be losslessly converted to an
001691  # integer.
001692  #
001693  do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0
001694  do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5
001695  
001696  do_expr_test e_expr-32.2.3 { 
001697    CAST(-9223372036854775808 AS NUMERIC)
001698  } integer -9223372036854775808
001699  do_expr_test e_expr-32.2.4 { 
001700    CAST(9223372036854775807 AS NUMERIC)
001701  } integer 9223372036854775807
001702  do_expr_test e_expr-32.2.5 { 
001703    CAST('9223372036854775807 ' AS NUMERIC)
001704  } integer 9223372036854775807
001705  do_expr_test e_expr-32.2.6 { 
001706    CAST('   9223372036854775807   ' AS NUMERIC)
001707  } integer 9223372036854775807
001708  do_expr_test e_expr-32.2.7 { 
001709    CAST('  ' AS NUMERIC)
001710  } integer 0
001711  do_execsql_test e_expr-32.2.8 {
001712    WITH t1(x) AS (VALUES
001713       ('9000000000000000001'),
001714       ('9000000000000000001x'),
001715       ('9000000000000000001 '),
001716       (' 9000000000000000001 '),
001717       (' 9000000000000000001'),
001718       (' 9000000000000000001.'),
001719       ('9223372036854775807'),
001720       ('9223372036854775807 '),
001721       ('   9223372036854775807   '),
001722       ('9223372036854775808'),
001723       ('   9223372036854775808   '),
001724       ('9223372036854775807.0'),
001725       ('9223372036854775807e+0'),
001726       ('-5.0'),
001727       ('-5e+0'))
001728    SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1;
001729  } [list \
001730   integer 9000000000000000001 \
001731   integer 9000000000000000001 \
001732   integer 9000000000000000001 \
001733   integer 9000000000000000001 \
001734   integer 9000000000000000001 \
001735   real 9.0e+18 \
001736   integer 9223372036854775807 \
001737   integer 9223372036854775807 \
001738   integer 9223372036854775807 \
001739   real 9.22337203685478e+18 \
001740   real 9.22337203685478e+18 \
001741   real 9.22337203685478e+18 \
001742   real 9.22337203685478e+18 \
001743   integer -5 \
001744   integer -5 \
001745  ]
001746  
001747  # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
001748  # non-BLOB value into a BLOB and the result from casting any BLOB value
001749  # into a non-BLOB value may be different depending on whether the
001750  # database encoding is UTF-8, UTF-16be, or UTF-16le.
001751  #
001752  ifcapable {utf16} {
001753  sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' }
001754  sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' }
001755  sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' }
001756  foreach {tn castexpr differs} {
001757    1 { CAST(123 AS BLOB)    } 1
001758    2 { CAST('' AS BLOB)     } 0
001759    3 { CAST('abcd' AS BLOB) } 1
001760  
001761    4 { CAST(X'abcd' AS TEXT) } 1
001762    5 { CAST(X'' AS TEXT)     } 0
001763  } {
001764    set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"]
001765    set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"]
001766    set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"]
001767  
001768    if {$differs} {
001769      set res [expr {$r1!=$r2 && $r2!=$r3}]
001770    } else {
001771      set res [expr {$r1==$r2 && $r2==$r3}]
001772    }
001773  
001774    do_test e_expr-33.1.$tn {set res} 1
001775  }
001776  db1 close
001777  db2 close
001778  db3 close
001779  }
001780  
001781  #-------------------------------------------------------------------------
001782  # Test statements related to the EXISTS and NOT EXISTS operators.
001783  #
001784  catch { db close }
001785  forcedelete test.db
001786  sqlite3 db test.db
001787  
001788  do_execsql_test e_expr-34.1 {
001789    CREATE TABLE t1(a, b);
001790    INSERT INTO t1 VALUES(1, 2);
001791    INSERT INTO t1 VALUES(NULL, 2);
001792    INSERT INTO t1 VALUES(1, NULL);
001793    INSERT INTO t1 VALUES(NULL, NULL);
001794  } {}
001795  
001796  # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one
001797  # of the integer values 0 and 1.
001798  #
001799  # This statement is not tested by itself. Instead, all e_expr-34.* tests 
001800  # following this point explicitly test that specific invocations of EXISTS
001801  # return either integer 0 or integer 1.
001802  #
001803  
001804  # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified
001805  # as the right-hand operand of the EXISTS operator would return one or
001806  # more rows, then the EXISTS operator evaluates to 1.
001807  #
001808  foreach {tn expr} {
001809      1 { EXISTS ( SELECT a FROM t1 ) }
001810      2 { EXISTS ( SELECT b FROM t1 ) }
001811      3 { EXISTS ( SELECT 24 ) }
001812      4 { EXISTS ( SELECT NULL ) }
001813      5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) }
001814  } {
001815    do_expr_test e_expr-34.2.$tn $expr integer 1
001816  }
001817  
001818  # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no
001819  # rows at all, then the EXISTS operator evaluates to 0.
001820  #
001821  foreach {tn expr} {
001822      1 { EXISTS ( SELECT a FROM t1 WHERE 0) }
001823      2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) }
001824      3 { EXISTS ( SELECT 24 WHERE 0) }
001825      4 { EXISTS ( SELECT NULL WHERE 1=2) }
001826  } {
001827    do_expr_test e_expr-34.3.$tn $expr integer 0
001828  }
001829  
001830  # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned
001831  # by the SELECT statement (if any) and the specific values returned have
001832  # no effect on the results of the EXISTS operator.
001833  #
001834  foreach {tn expr res} {
001835      1 { EXISTS ( SELECT * FROM t1 ) }                          1
001836      2 { EXISTS ( SELECT *, *, * FROM t1 ) }                    1
001837      3 { EXISTS ( SELECT 24, 25 ) }                             1
001838      4 { EXISTS ( SELECT NULL, NULL, NULL ) }                   1
001839      5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) }   1
001840  
001841      6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) }                0
001842      7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) }         0
001843      8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) }                  0
001844      9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) }                0
001845  } {
001846    do_expr_test e_expr-34.4.$tn $expr integer $res
001847  }
001848  
001849  # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values
001850  # are not handled any differently from rows without NULL values.
001851  #
001852  foreach {tn e1 e2} {
001853    1 { EXISTS (SELECT 'not null') }    { EXISTS (SELECT NULL) }
001854    2 { EXISTS (SELECT NULL FROM t1) }  { EXISTS (SELECT 'bread' FROM t1) }
001855  } {
001856    set res [db one "SELECT $e1"]
001857    do_expr_test e_expr-34.5.${tn}a $e1 integer $res
001858    do_expr_test e_expr-34.5.${tn}b $e2 integer $res
001859  }
001860  
001861  #-------------------------------------------------------------------------
001862  # Test statements related to scalar sub-queries.
001863  #
001864  
001865  catch { db close }
001866  forcedelete test.db
001867  sqlite3 db test.db
001868  do_test e_expr-35.0 {
001869    execsql {
001870      CREATE TABLE t2(a, b);
001871      INSERT INTO t2 VALUES('one', 'two');
001872      INSERT INTO t2 VALUES('three', NULL);
001873      INSERT INTO t2 VALUES(4, 5.0);
001874    }
001875  } {}
001876  
001877  # EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses
001878  # is a subquery.
001879  #
001880  # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
001881  # aggregate and compound SELECT queries (queries with keywords like
001882  # UNION or EXCEPT) are allowed as scalar subqueries.
001883  #
001884  do_expr_test e_expr-35.1.1 { (SELECT 35)   } integer 35
001885  do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
001886  
001887  do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
001888  do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
001889  
001890  do_expr_test e_expr-35.1.5 { 
001891    (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
001892  } null {}
001893  do_expr_test e_expr-35.1.6 { 
001894    (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
001895  } integer 4
001896  
001897  # EVIDENCE-OF: R-22239-33740 A subquery that returns two or more columns
001898  # is a row value subquery and can only be used as the operand of a
001899  # comparison operator.
001900  #
001901  # The following block tests that errors are returned in a bunch of cases
001902  # where a subquery returns more than one column.
001903  #
001904  set M {/1 {sub-select returns [23] columns - expected 1}/}
001905  foreach {tn sql} {
001906    1     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
001907    2     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
001908    3     { SELECT (SELECT 1, 2) }
001909    4     { SELECT (SELECT NULL, NULL, NULL) }
001910    5     { SELECT (SELECT * FROM t2) }
001911    6     { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
001912  } {
001913    do_catchsql_test e_expr-35.2.$tn $sql $M
001914  }
001915  
001916  # EVIDENCE-OF: R-18318-14995 The value of a subquery expression is the
001917  # first row of the result from the enclosed SELECT statement.
001918  #
001919  do_execsql_test e_expr-36.3.1 {
001920    CREATE TABLE t4(x, y);
001921    INSERT INTO t4 VALUES(1, 'one');
001922    INSERT INTO t4 VALUES(2, 'two');
001923    INSERT INTO t4 VALUES(3, 'three');
001924  } {}
001925  
001926  foreach {tn expr restype resval} {
001927      2  { ( SELECT x FROM t4 ORDER BY x )      }        integer 1
001928      3  { ( SELECT x FROM t4 ORDER BY y )      }        integer 1
001929      4  { ( SELECT x FROM t4 ORDER BY x DESC ) }        integer 3
001930      5  { ( SELECT x FROM t4 ORDER BY y DESC ) }        integer 2
001931      6  { ( SELECT y FROM t4 ORDER BY y DESC ) }        text    two
001932  
001933      7  { ( SELECT sum(x) FROM t4 )           }         integer 6
001934      8  { ( SELECT group_concat(y,'') FROM t4 ) }       text    onetwothree
001935      9  { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 
001936  
001937  } {
001938    do_expr_test e_expr-36.3.$tn $expr $restype $resval
001939  }
001940  
001941  # EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL
001942  # if the enclosed SELECT statement returns no rows.
001943  #
001944  foreach {tn expr} {
001945      1  { ( SELECT x FROM t4 WHERE x>3 ORDER BY x )      }
001946      2  { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y )  }
001947  } {
001948    do_expr_test e_expr-36.4.$tn $expr null {}
001949  }
001950  
001951  # EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0,
001952  # 'english' and '0' are all considered to be false.
001953  #
001954  do_execsql_test e_expr-37.1 {
001955     SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END;
001956  } {false}
001957  do_execsql_test e_expr-37.2 {
001958     SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END;
001959  } {false}
001960  do_execsql_test e_expr-37.3 {
001961     SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END;
001962  } {false}
001963  do_execsql_test e_expr-37.4 {
001964     SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END;
001965  } {false}
001966  do_execsql_test e_expr-37.5 {
001967     SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END;
001968  } {false}
001969  
001970  # EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are
001971  # considered to be true.
001972  #
001973  do_execsql_test e_expr-37.6 {
001974     SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END;
001975  } {true}
001976  do_execsql_test e_expr-37.7 {
001977     SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END;
001978  } {true}
001979  do_execsql_test e_expr-37.8 {
001980     SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END;
001981  } {true}
001982  do_execsql_test e_expr-37.9 {
001983     SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END;
001984  } {true}
001985  do_execsql_test e_expr-37.10 {
001986     SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END;
001987  } {true}
001988  
001989  
001990  finish_test