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