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.2 { PRAGMA case_sensitive_like = 1 } {}
001018  do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0
001019  do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
001020  do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {}
001021  do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1
001022  do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
001023  
001024  # EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but
001025  # uses the Unix file globbing syntax for its wildcards.
001026  #
001027  # EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE.
001028  #
001029  do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0
001030  do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1
001031  do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0
001032  do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1
001033  
001034  do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1
001035  do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0
001036  do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0
001037  
001038  # EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the
001039  # NOT keyword to invert the sense of the test.
001040  #
001041  do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1
001042  do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0
001043  do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0
001044  do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0
001045  do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1
001046  
001047  db nullvalue null
001048  do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null
001049  do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null
001050  do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null
001051  do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null
001052  db nullvalue {}
001053  
001054  # EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by
001055  # calling the function glob(Y,X) and can be modified by overriding that
001056  # function.
001057  proc globfunc {args} {
001058    eval lappend ::globargs $args
001059    return 1
001060  }
001061  db func glob -argcount 2 globfunc
001062  set ::globargs [list]
001063  do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1
001064  do_test         e_expr-17.3.2 { set globargs } {def abc}
001065  set ::globargs [list]
001066  do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0
001067  do_test         e_expr-17.3.4 { set globargs } {Y X}
001068  sqlite3 db test.db
001069  
001070  # EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
001071  # default and so use of the REGEXP operator will normally result in an
001072  # error message.
001073  #
001074  #   There is a regexp function if ICU is enabled though.
001075  #
001076  ifcapable !icu {
001077    do_catchsql_test e_expr-18.1.1 { 
001078      SELECT regexp('abc', 'def') 
001079    } {1 {no such function: regexp}}
001080    do_catchsql_test e_expr-18.1.2 { 
001081      SELECT 'abc' REGEXP 'def'
001082    } {1 {no such function: REGEXP}}
001083  }
001084  
001085  # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
001086  # the regexp() user function.
001087  #
001088  # EVIDENCE-OF: R-65524-61849 If an application-defined SQL function
001089  # named "regexp" is added at run-time, then the "X REGEXP Y" operator
001090  # will be implemented as a call to "regexp(Y,X)".
001091  #
001092  proc regexpfunc {args} {
001093    eval lappend ::regexpargs $args
001094    return 1
001095  }
001096  db func regexp -argcount 2 regexpfunc
001097  set ::regexpargs [list]
001098  do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
001099  do_test         e_expr-18.2.2 { set regexpargs } {def abc}
001100  set ::regexpargs [list]
001101  do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
001102  do_test         e_expr-18.2.4 { set regexpargs } {Y X}
001103  sqlite3 db test.db
001104  
001105  # EVIDENCE-OF: R-42037-37826 The default match() function implementation
001106  # raises an exception and is not really useful for anything.
001107  #
001108  do_catchsql_test e_expr-19.1.1 { 
001109    SELECT 'abc' MATCH 'def' 
001110  } {1 {unable to use function MATCH in the requested context}}
001111  do_catchsql_test e_expr-19.1.2 { 
001112    SELECT match('abc', 'def')
001113  } {1 {unable to use function MATCH in the requested context}}
001114  
001115  # EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
001116  # the match() application-defined function.
001117  #
001118  # EVIDENCE-OF: R-06021-09373 But extensions can override the match()
001119  # function with more helpful logic.
001120  #
001121  proc matchfunc {args} {
001122    eval lappend ::matchargs $args
001123    return 1
001124  }
001125  db func match -argcount 2 matchfunc
001126  set ::matchargs [list]
001127  do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
001128  do_test         e_expr-19.2.2 { set matchargs } {def abc}
001129  set ::matchargs [list]
001130  do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
001131  do_test         e_expr-19.2.4 { set matchargs } {Y X}
001132  sqlite3 db test.db
001133  
001134  #-------------------------------------------------------------------------
001135  # Test cases for the testable statements related to the CASE expression.
001136  #
001137  # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE
001138  # expression: those with a base expression and those without.
001139  #
001140  do_execsql_test e_expr-20.1 {
001141    SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
001142  } {true}
001143  do_execsql_test e_expr-20.2 {
001144    SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
001145  } {false}
001146  
001147  proc var {nm} {
001148    lappend ::varlist $nm
001149    return [set "::$nm"]
001150  }
001151  db func var var
001152  
001153  # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each
001154  # WHEN expression is evaluated and the result treated as a boolean,
001155  # starting with the leftmost and continuing to the right.
001156  #
001157  foreach {a b c} {0 0 0} break
001158  set varlist [list]
001159  do_execsql_test e_expr-21.1.1 {
001160    SELECT CASE WHEN var('a') THEN 'A' 
001161                WHEN var('b') THEN 'B' 
001162                WHEN var('c') THEN 'C' END
001163  } {{}}
001164  do_test e_expr-21.1.2 { set varlist } {a b c}
001165  set varlist [list]
001166  do_execsql_test e_expr-21.1.3 {
001167    SELECT CASE WHEN var('c') THEN 'C' 
001168                WHEN var('b') THEN 'B' 
001169                WHEN var('a') THEN 'A' 
001170                ELSE 'no result'
001171    END
001172  } {{no result}}
001173  do_test e_expr-21.1.4 { set varlist } {c b a}
001174  
001175  # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the
001176  # evaluation of the THEN expression that corresponds to the first WHEN
001177  # expression that evaluates to true.
001178  #
001179  foreach {a b c} {0 1 0} break
001180  do_execsql_test e_expr-21.2.1 {
001181    SELECT CASE WHEN var('a') THEN 'A' 
001182                WHEN var('b') THEN 'B' 
001183                WHEN var('c') THEN 'C' 
001184                ELSE 'no result'
001185    END
001186  } {B}
001187  foreach {a b c} {0 1 1} break
001188  do_execsql_test e_expr-21.2.2 {
001189    SELECT CASE WHEN var('a') THEN 'A' 
001190                WHEN var('b') THEN 'B' 
001191                WHEN var('c') THEN 'C'
001192                ELSE 'no result'
001193    END
001194  } {B}
001195  foreach {a b c} {0 0 1} break
001196  do_execsql_test e_expr-21.2.3 {
001197    SELECT CASE WHEN var('a') THEN 'A' 
001198                WHEN var('b') THEN 'B' 
001199                WHEN var('c') THEN 'C'
001200                ELSE 'no result'
001201    END
001202  } {C}
001203  
001204  # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions
001205  # evaluate to true, the result of evaluating the ELSE expression, if
001206  # any.
001207  #
001208  foreach {a b c} {0 0 0} break
001209  do_execsql_test e_expr-21.3.1 {
001210    SELECT CASE WHEN var('a') THEN 'A' 
001211                WHEN var('b') THEN 'B' 
001212                WHEN var('c') THEN 'C'
001213                ELSE 'no result'
001214    END
001215  } {{no result}}
001216  
001217  # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of
001218  # the WHEN expressions are true, then the overall result is NULL.
001219  #
001220  db nullvalue null
001221  do_execsql_test e_expr-21.3.2 {
001222    SELECT CASE WHEN var('a') THEN 'A' 
001223                WHEN var('b') THEN 'B' 
001224                WHEN var('c') THEN 'C'
001225    END
001226  } {null}
001227  db nullvalue {}
001228  
001229  # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when
001230  # evaluating WHEN terms.
001231  #
001232  do_execsql_test e_expr-21.4.1 {
001233    SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END
001234  } {B}
001235  do_execsql_test e_expr-21.4.2 {
001236    SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END
001237  } {C}
001238  
001239  # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base
001240  # expression is evaluated just once and the result is compared against
001241  # the evaluation of each WHEN expression from left to right.
001242  #
001243  # Note: This test case tests the "evaluated just once" part of the above
001244  # statement. Tests associated with the next two statements test that the
001245  # comparisons take place.
001246  #
001247  foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break
001248  set ::varlist [list]
001249  do_execsql_test e_expr-22.1.1 {
001250    SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END
001251  } {C}
001252  do_test e_expr-22.1.2 { set ::varlist } {a}
001253  
001254  # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the
001255  # evaluation of the THEN expression that corresponds to the first WHEN
001256  # expression for which the comparison is true.
001257  #
001258  do_execsql_test e_expr-22.2.1 {
001259    SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001260  } {B}
001261  do_execsql_test e_expr-22.2.2 {
001262    SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001263  } {A}
001264  
001265  # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions
001266  # evaluate to a value equal to the base expression, the result of
001267  # evaluating the ELSE expression, if any.
001268  #
001269  do_execsql_test e_expr-22.3.1 {
001270    SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END
001271  } {D}
001272  
001273  # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of
001274  # the WHEN expressions produce a result equal to the base expression,
001275  # the overall result is NULL.
001276  #
001277  do_execsql_test e_expr-22.4.1 {
001278    SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001279  } {{}}
001280  db nullvalue null
001281  do_execsql_test e_expr-22.4.2 {
001282    SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
001283  } {null}
001284  db nullvalue {}
001285  
001286  # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a
001287  # WHEN expression, the same collating sequence, affinity, and
001288  # NULL-handling rules apply as if the base expression and WHEN
001289  # expression are respectively the left- and right-hand operands of an =
001290  # operator.
001291  #
001292  proc rev {str} {
001293    set ret ""
001294    set chars [split $str]
001295    for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
001296      append ret [lindex $chars $i]
001297    }
001298    set ret
001299  }
001300  proc reverse {lhs rhs} {
001301    string compare [rev $lhs] [rev $rhs]
001302  }
001303  db collate reverse reverse
001304  do_execsql_test e_expr-23.1.1 {
001305    CREATE TABLE t1(
001306      a TEXT     COLLATE NOCASE,
001307      b          COLLATE REVERSE,
001308      c INTEGER,
001309      d BLOB
001310    );
001311    INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
001312  } {}
001313  do_execsql_test e_expr-23.1.2 {
001314    SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
001315  } {B}
001316  do_execsql_test e_expr-23.1.3 {
001317    SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
001318  } {B}
001319  do_execsql_test e_expr-23.1.4 {
001320    SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
001321  } {B}
001322  do_execsql_test e_expr-23.1.5 {
001323    SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
001324  } {B}
001325  do_execsql_test e_expr-23.1.6 {
001326    SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
001327  } {B}
001328  do_execsql_test e_expr-23.1.7 {
001329    SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
001330  } {A}
001331  do_execsql_test e_expr-23.1.8 {
001332    SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
001333  } {B}
001334  do_execsql_test e_expr-23.1.9 {
001335    SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END
001336  } {B}
001337  
001338  # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the
001339  # result of the CASE is always the result of evaluating the ELSE
001340  # expression if it exists, or NULL if it does not.
001341  #
001342  do_execsql_test e_expr-24.1.1 {
001343    SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END;
001344  } {{}}
001345  do_execsql_test e_expr-24.1.2 {
001346    SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END;
001347  } {C}
001348  
001349  # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy,
001350  # or short-circuit, evaluation.
001351  #
001352  set varlist [list]
001353  foreach {a b c} {0 1 0} break
001354  do_execsql_test e_expr-25.1.1 {
001355    SELECT CASE WHEN var('a') THEN 'A' 
001356                WHEN var('b') THEN 'B' 
001357                WHEN var('c') THEN 'C' 
001358    END
001359  } {B}
001360  do_test e_expr-25.1.2 { set ::varlist } {a b}
001361  set varlist [list]
001362  do_execsql_test e_expr-25.1.3 {
001363    SELECT CASE '0' WHEN var('a') THEN 'A' 
001364                    WHEN var('b') THEN 'B' 
001365                    WHEN var('c') THEN 'C' 
001366    END
001367  } {A}
001368  do_test e_expr-25.1.4 { set ::varlist } {a}
001369  
001370  # EVIDENCE-OF: R-34773-62253 The only difference between the following
001371  # two CASE expressions is that the x expression is evaluated exactly
001372  # once in the first example but might be evaluated multiple times in the
001373  # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN
001374  # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
001375  #
001376  proc ceval {x} {
001377    incr ::evalcount
001378    return $x
001379  }
001380  db func ceval ceval
001381  set ::evalcount 0
001382  
001383  do_execsql_test e_expr-26.1.1 {
001384    CREATE TABLE t2(x, w1, r1, w2, r2, r3);
001385    INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3');
001386    INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3');
001387    INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3');
001388  } {}
001389  do_execsql_test e_expr-26.1.2 {
001390    SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
001391  } {R1 R2 R3}
001392  do_execsql_test e_expr-26.1.3 {
001393    SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2
001394  } {R1 R2 R3}
001395  
001396  do_execsql_test e_expr-26.1.4 {
001397    SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
001398  } {R1 R2 R3}
001399  do_test e_expr-26.1.5 { set ::evalcount } {3}
001400  set ::evalcount 0
001401  do_execsql_test e_expr-26.1.6 {
001402    SELECT CASE 
001403      WHEN ceval(x)=w1 THEN r1 
001404      WHEN ceval(x)=w2 THEN r2 
001405      ELSE r3 END 
001406    FROM t2
001407  } {R1 R2 R3}
001408  do_test e_expr-26.1.6 { set ::evalcount } {5}
001409  
001410  
001411  #-------------------------------------------------------------------------
001412  # Test statements related to CAST expressions.
001413  #
001414  # EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the
001415  # conversion that takes place when a column affinity is applied to a
001416  # value except that with the CAST operator the conversion always takes
001417  # place even if the conversion lossy and irreversible, whereas column
001418  # affinity only changes the data type of a value if the change is
001419  # lossless and reversible.
001420  #
001421  do_execsql_test e_expr-27.1.1 {
001422    CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
001423    INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
001424    SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
001425  } {blob UVU text 1.23abc real 4.5}
001426  do_execsql_test e_expr-27.1.2 {
001427    SELECT 
001428      typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
001429      typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
001430      typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
001431  } {text UVU real 1.23 integer 4}
001432  
001433  # EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the
001434  # result of the CAST expression is also NULL.
001435  #
001436  do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
001437  do_expr_test e_expr-27.2.2 { CAST(NULL AS text) }    null {}
001438  do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) }    null {}
001439  do_expr_test e_expr-27.2.4 { CAST(NULL AS number) }  null {}
001440  
001441  # EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result
001442  # is determined by applying the rules for determining column affinity to
001443  # the type-name.
001444  #
001445  # The R-29283-15561 requirement above is demonstrated by all of the 
001446  # subsequent e_expr-26 tests.
001447  #
001448  # EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no
001449  # affinity causes the value to be converted into a BLOB.
001450  #
001451  do_expr_test e_expr-27.3.1 { CAST('abc' AS blob)       } blob abc
001452  do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
001453  do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10)   } blob ghi
001454  
001455  # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
001456  # the value to TEXT in the encoding of the database connection, then
001457  # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
001458  #
001459  do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
001460  do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) }   X'343536'
001461  do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) }  X'312E3738'
001462  rename db db2
001463  sqlite3 db :memory:
001464  ifcapable {utf16} {
001465  db eval { PRAGMA encoding = 'utf-16le' }
001466  do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
001467  do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) }   X'340035003600'
001468  do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) }  X'31002E0037003800'
001469  }
001470  db close
001471  sqlite3 db :memory:
001472  db eval { PRAGMA encoding = 'utf-16be' }
001473  ifcapable {utf16} {
001474  do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
001475  do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) }   X'003400350036'
001476  do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) }  X'0031002E00370038'
001477  }
001478  db close
001479  rename db2 db
001480  
001481  # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
001482  # of bytes that make up the BLOB is interpreted as text encoded using
001483  # the database encoding.
001484  #
001485  do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
001486  do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
001487  rename db db2
001488  sqlite3 db :memory:
001489  db eval { PRAGMA encoding = 'utf-16le' }
001490  ifcapable {utf16} {
001491  do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
001492  do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
001493  }
001494  db close
001495  rename db2 db
001496  
001497  # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
001498  # renders the value as if via sqlite3_snprintf() except that the
001499  # resulting TEXT uses the encoding of the database connection.
001500  #
001501  do_expr_test e_expr-28.2.1 { CAST (1 AS text)   }     text 1
001502  do_expr_test e_expr-28.2.2 { CAST (45 AS text)  }     text 45
001503  do_expr_test e_expr-28.2.3 { CAST (-45 AS text) }     text -45
001504  do_expr_test e_expr-28.2.4 { CAST (8.8 AS text)    }  text 8.8
001505  do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) }  text 230000.0
001506  do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
001507  do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) }     text 0.0
001508  do_expr_test e_expr-28.2.7 { CAST (0 AS text) }       text 0
001509  
001510  # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
001511  # value is first converted to TEXT.
001512  #
001513  do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
001514  do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
001515  do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
001516  do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
001517  rename db db2
001518  sqlite3 db :memory:
001519  ifcapable {utf16} {
001520  db eval { PRAGMA encoding = 'utf-16le' }
001521  do_expr_test e_expr-29.1.5 { 
001522      CAST (X'31002E0032003300' AS REAL) } real 1.23
001523  do_expr_test e_expr-29.1.6 { 
001524      CAST (X'3200330030002E003000' AS REAL) } real 230.0
001525  do_expr_test e_expr-29.1.7 { 
001526      CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
001527  do_expr_test e_expr-29.1.8 { 
001528      CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
001529  }
001530  db close
001531  rename db2 db
001532  
001533  # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the
001534  # longest possible prefix of the value that can be interpreted as a real
001535  # number is extracted from the TEXT value and the remainder ignored.
001536  #
001537  do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23
001538  do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45
001539  do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212
001540  do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0
001541  
001542  # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are
001543  # ignored when converging from TEXT to REAL.
001544  #
001545  do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23
001546  do_expr_test e_expr-29.3.2 { CAST('    1.45.23abcd' AS REAL) } real 1.45
001547  do_expr_test e_expr-29.3.3 { CAST('   -2.12e-01ABC' AS REAL) } real -0.212
001548  do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0
001549  
001550  # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be
001551  # interpreted as a real number, the result of the conversion is 0.0.
001552  #
001553  do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0
001554  do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0
001555  do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0
001556  
001557  # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the
001558  # value is first converted to TEXT.
001559  #
001560  do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123
001561  do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678
001562  do_expr_test e_expr-30.1.3 { 
001563    CAST(X'31303030303030' AS INTEGER) 
001564  } integer 1000000
001565  do_expr_test e_expr-30.1.4 { 
001566    CAST(X'2D31313235383939393036383432363234' AS INTEGER) 
001567  } integer -1125899906842624
001568  
001569  rename db db2
001570  sqlite3 db :memory:
001571  ifcapable {utf16} {
001572  execsql { PRAGMA encoding = 'utf-16be' }
001573  do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123
001574  do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678
001575  do_expr_test e_expr-30.1.7 { 
001576    CAST(X'0031003000300030003000300030' AS INTEGER) 
001577  } integer 1000000
001578  do_expr_test e_expr-30.1.8 { 
001579    CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER) 
001580  } integer -1125899906842624
001581  }
001582  db close
001583  rename db2 db
001584  
001585  # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the
001586  # longest possible prefix of the value that can be interpreted as an
001587  # integer number is extracted from the TEXT value and the remainder
001588  # ignored.
001589  #
001590  do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123
001591  do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523
001592  do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2
001593  do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1
001594  
001595  # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when
001596  # converting from TEXT to INTEGER are ignored.
001597  #
001598  do_expr_test e_expr-30.3.1 { CAST('   123abcd' AS INT) } integer 123
001599  do_expr_test e_expr-30.3.2 { CAST('  14523abcd' AS INT) } integer 14523
001600  do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
001601  do_expr_test e_expr-30.3.4 { CAST('     1 2 3 4' AS INT) } integer 1
001602  
001603  # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
001604  # interpreted as an integer number, the result of the conversion is 0.
001605  #
001606  do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
001607  do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
001608  do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
001609  
001610  # EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal
001611  # integers only &mdash; conversion of hexadecimal integers stops at
001612  # the "x" in the "0x" prefix of the hexadecimal integer string and thus
001613  # result of the CAST is always zero.
001614  do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0
001615  do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0
001616  
001617  # EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER
001618  # results in the integer between the REAL value and zero that is closest
001619  # to the REAL value.
001620  #
001621  do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
001622  do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
001623  do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
001624  do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
001625  
001626  # EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest
001627  # possible signed integer (+9223372036854775807) then the result is the
001628  # greatest possible signed integer and if the REAL is less than the
001629  # least possible signed integer (-9223372036854775808) then the result
001630  # is the least possible signed integer.
001631  #
001632  do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807
001633  do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
001634  do_expr_test e_expr-31.2.3 { 
001635    CAST(-9223372036854775809.0 AS INT)
001636  } integer -9223372036854775808
001637  do_expr_test e_expr-31.2.4 { 
001638    CAST(9223372036854775809.0 AS INT)
001639  } integer 9223372036854775807
001640  
001641  
001642  # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
001643  # first does a forced conversion into REAL but then further converts the
001644  # result into INTEGER if and only if the conversion from REAL to INTEGER
001645  # is lossless and reversible.
001646  #
001647  do_expr_test e_expr-32.1.1 { CAST('45'   AS NUMERIC)  } integer 45
001648  do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC)  } integer 45
001649  do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC)  } real 45.2
001650  do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
001651  do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
001652  
001653  # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
001654  # is a no-op, even if a real value could be losslessly converted to an
001655  # integer.
001656  #
001657  do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0
001658  do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5
001659  
001660  do_expr_test e_expr-32.2.3 { 
001661    CAST(-9223372036854775808 AS NUMERIC)
001662  } integer -9223372036854775808
001663  do_expr_test e_expr-32.2.4 { 
001664    CAST(9223372036854775807 AS NUMERIC)
001665  } integer 9223372036854775807
001666  do_expr_test e_expr-32.2.5 { 
001667    CAST('9223372036854775807 ' AS NUMERIC)
001668  } integer 9223372036854775807
001669  do_expr_test e_expr-32.2.6 { 
001670    CAST('   9223372036854775807   ' AS NUMERIC)
001671  } integer 9223372036854775807
001672  do_expr_test e_expr-32.2.7 { 
001673    CAST('  ' AS NUMERIC)
001674  } integer 0
001675  do_execsql_test e_expr-32.2.8 {
001676    WITH t1(x) AS (VALUES
001677       ('9000000000000000001'),
001678       ('9000000000000000001x'),
001679       ('9000000000000000001 '),
001680       (' 9000000000000000001 '),
001681       (' 9000000000000000001'),
001682       (' 9000000000000000001.'),
001683       ('9223372036854775807'),
001684       ('9223372036854775807 '),
001685       ('   9223372036854775807   '),
001686       ('9223372036854775808'),
001687       ('   9223372036854775808   '),
001688       ('9223372036854775807.0'),
001689       ('9223372036854775807e+0'),
001690       ('-5.0'),
001691       ('-5e+0'))
001692    SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1;
001693  } [list \
001694   integer 9000000000000000001 \
001695   integer 9000000000000000001 \
001696   integer 9000000000000000001 \
001697   integer 9000000000000000001 \
001698   integer 9000000000000000001 \
001699   integer 9000000000000000001 \
001700   integer 9223372036854775807 \
001701   integer 9223372036854775807 \
001702   integer 9223372036854775807 \
001703   real 9.22337203685478e+18 \
001704   real 9.22337203685478e+18 \
001705   integer 9223372036854775807 \
001706   integer 9223372036854775807 \
001707   integer -5 \
001708   integer -5 \
001709  ]
001710  
001711  # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
001712  # non-BLOB value into a BLOB and the result from casting any BLOB value
001713  # into a non-BLOB value may be different depending on whether the
001714  # database encoding is UTF-8, UTF-16be, or UTF-16le.
001715  #
001716  ifcapable {utf16} {
001717  sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' }
001718  sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' }
001719  sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' }
001720  foreach {tn castexpr differs} {
001721    1 { CAST(123 AS BLOB)    } 1
001722    2 { CAST('' AS BLOB)     } 0
001723    3 { CAST('abcd' AS BLOB) } 1
001724  
001725    4 { CAST(X'abcd' AS TEXT) } 1
001726    5 { CAST(X'' AS TEXT)     } 0
001727  } {
001728    set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"]
001729    set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"]
001730    set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"]
001731  
001732    if {$differs} {
001733      set res [expr {$r1!=$r2 && $r2!=$r3}]
001734    } else {
001735      set res [expr {$r1==$r2 && $r2==$r3}]
001736    }
001737  
001738    do_test e_expr-33.1.$tn {set res} 1
001739  }
001740  db1 close
001741  db2 close
001742  db3 close
001743  }
001744  
001745  #-------------------------------------------------------------------------
001746  # Test statements related to the EXISTS and NOT EXISTS operators.
001747  #
001748  catch { db close }
001749  forcedelete test.db
001750  sqlite3 db test.db
001751  
001752  do_execsql_test e_expr-34.1 {
001753    CREATE TABLE t1(a, b);
001754    INSERT INTO t1 VALUES(1, 2);
001755    INSERT INTO t1 VALUES(NULL, 2);
001756    INSERT INTO t1 VALUES(1, NULL);
001757    INSERT INTO t1 VALUES(NULL, NULL);
001758  } {}
001759  
001760  # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one
001761  # of the integer values 0 and 1.
001762  #
001763  # This statement is not tested by itself. Instead, all e_expr-34.* tests 
001764  # following this point explicitly test that specific invocations of EXISTS
001765  # return either integer 0 or integer 1.
001766  #
001767  
001768  # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified
001769  # as the right-hand operand of the EXISTS operator would return one or
001770  # more rows, then the EXISTS operator evaluates to 1.
001771  #
001772  foreach {tn expr} {
001773      1 { EXISTS ( SELECT a FROM t1 ) }
001774      2 { EXISTS ( SELECT b FROM t1 ) }
001775      3 { EXISTS ( SELECT 24 ) }
001776      4 { EXISTS ( SELECT NULL ) }
001777      5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) }
001778  } {
001779    do_expr_test e_expr-34.2.$tn $expr integer 1
001780  }
001781  
001782  # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no
001783  # rows at all, then the EXISTS operator evaluates to 0.
001784  #
001785  foreach {tn expr} {
001786      1 { EXISTS ( SELECT a FROM t1 WHERE 0) }
001787      2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) }
001788      3 { EXISTS ( SELECT 24 WHERE 0) }
001789      4 { EXISTS ( SELECT NULL WHERE 1=2) }
001790  } {
001791    do_expr_test e_expr-34.3.$tn $expr integer 0
001792  }
001793  
001794  # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned
001795  # by the SELECT statement (if any) and the specific values returned have
001796  # no effect on the results of the EXISTS operator.
001797  #
001798  foreach {tn expr res} {
001799      1 { EXISTS ( SELECT * FROM t1 ) }                          1
001800      2 { EXISTS ( SELECT *, *, * FROM t1 ) }                    1
001801      3 { EXISTS ( SELECT 24, 25 ) }                             1
001802      4 { EXISTS ( SELECT NULL, NULL, NULL ) }                   1
001803      5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) }   1
001804  
001805      6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) }                0
001806      7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) }         0
001807      8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) }                  0
001808      9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) }                0
001809  } {
001810    do_expr_test e_expr-34.4.$tn $expr integer $res
001811  }
001812  
001813  # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values
001814  # are not handled any differently from rows without NULL values.
001815  #
001816  foreach {tn e1 e2} {
001817    1 { EXISTS (SELECT 'not null') }    { EXISTS (SELECT NULL) }
001818    2 { EXISTS (SELECT NULL FROM t1) }  { EXISTS (SELECT 'bread' FROM t1) }
001819  } {
001820    set res [db one "SELECT $e1"]
001821    do_expr_test e_expr-34.5.${tn}a $e1 integer $res
001822    do_expr_test e_expr-34.5.${tn}b $e2 integer $res
001823  }
001824  
001825  #-------------------------------------------------------------------------
001826  # Test statements related to scalar sub-queries.
001827  #
001828  
001829  catch { db close }
001830  forcedelete test.db
001831  sqlite3 db test.db
001832  do_test e_expr-35.0 {
001833    execsql {
001834      CREATE TABLE t2(a, b);
001835      INSERT INTO t2 VALUES('one', 'two');
001836      INSERT INTO t2 VALUES('three', NULL);
001837      INSERT INTO t2 VALUES(4, 5.0);
001838    }
001839  } {}
001840  
001841  # EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses
001842  # is a subquery.
001843  #
001844  # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
001845  # aggregate and compound SELECT queries (queries with keywords like
001846  # UNION or EXCEPT) are allowed as scalar subqueries.
001847  #
001848  do_expr_test e_expr-35.1.1 { (SELECT 35)   } integer 35
001849  do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
001850  
001851  do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
001852  do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
001853  
001854  do_expr_test e_expr-35.1.5 { 
001855    (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
001856  } null {}
001857  do_expr_test e_expr-35.1.6 { 
001858    (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
001859  } integer 4
001860  
001861  # EVIDENCE-OF: R-22239-33740 A subquery that returns two or more columns
001862  # is a row value subquery and can only be used as the operand of a
001863  # comparison operator.
001864  #
001865  # The following block tests that errors are returned in a bunch of cases
001866  # where a subquery returns more than one column.
001867  #
001868  set M {/1 {sub-select returns [23] columns - expected 1}/}
001869  foreach {tn sql} {
001870    1     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
001871    2     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
001872    3     { SELECT (SELECT 1, 2) }
001873    4     { SELECT (SELECT NULL, NULL, NULL) }
001874    5     { SELECT (SELECT * FROM t2) }
001875    6     { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
001876  } {
001877    do_catchsql_test e_expr-35.2.$tn $sql $M
001878  }
001879  
001880  # EVIDENCE-OF: R-18318-14995 The value of a subquery expression is the
001881  # first row of the result from the enclosed SELECT statement.
001882  #
001883  # EVIDENCE-OF: R-15900-52156 In other words, an implied "LIMIT 1" is
001884  # added to the subquery, overriding an explicitly coded LIMIT.
001885  #
001886  do_execsql_test e_expr-36.3.1 {
001887    CREATE TABLE t4(x, y);
001888    INSERT INTO t4 VALUES(1, 'one');
001889    INSERT INTO t4 VALUES(2, 'two');
001890    INSERT INTO t4 VALUES(3, 'three');
001891  } {}
001892  
001893  foreach {tn expr restype resval} {
001894      2  { ( SELECT x FROM t4 ORDER BY x )      }        integer 1
001895      3  { ( SELECT x FROM t4 ORDER BY y )      }        integer 1
001896      4  { ( SELECT x FROM t4 ORDER BY x DESC ) }        integer 3
001897      5  { ( SELECT x FROM t4 ORDER BY y DESC ) }        integer 2
001898      6  { ( SELECT y FROM t4 ORDER BY y DESC ) }        text    two
001899  
001900      7  { ( SELECT sum(x) FROM t4 )           }         integer 6
001901      8  { ( SELECT group_concat(y,'') FROM t4 ) }       text    onetwothree
001902      9  { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 
001903  
001904  } {
001905    do_expr_test e_expr-36.3.$tn $expr $restype $resval
001906  }
001907  
001908  # EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL
001909  # if the enclosed SELECT statement returns no rows.
001910  #
001911  foreach {tn expr} {
001912      1  { ( SELECT x FROM t4 WHERE x>3 ORDER BY x )      }
001913      2  { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y )  }
001914  } {
001915    do_expr_test e_expr-36.4.$tn $expr null {}
001916  }
001917  
001918  # EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0,
001919  # 'english' and '0' are all considered to be false.
001920  #
001921  do_execsql_test e_expr-37.1 {
001922     SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END;
001923  } {false}
001924  do_execsql_test e_expr-37.2 {
001925     SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END;
001926  } {false}
001927  do_execsql_test e_expr-37.3 {
001928     SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END;
001929  } {false}
001930  do_execsql_test e_expr-37.4 {
001931     SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END;
001932  } {false}
001933  do_execsql_test e_expr-37.5 {
001934     SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END;
001935  } {false}
001936  
001937  # EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are
001938  # considered to be true.
001939  #
001940  do_execsql_test e_expr-37.6 {
001941     SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END;
001942  } {true}
001943  do_execsql_test e_expr-37.7 {
001944     SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END;
001945  } {true}
001946  do_execsql_test e_expr-37.8 {
001947     SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END;
001948  } {true}
001949  do_execsql_test e_expr-37.9 {
001950     SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END;
001951  } {true}
001952  do_execsql_test e_expr-37.10 {
001953     SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END;
001954  } {true}
001955  
001956  
001957  finish_test