AryaWu/sqlite
0
1# 2010 July 162#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11#12# This file implements tests to verify that the "testable statements" in 13# the lang_expr.html document are correct.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18source $testdir/malloc_common.tcl19 20ifcapable !compound {21 finish_test22 return23}24 25proc do_expr_test {tn expr type value} {26 uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [27 list [list $type $value]28 ]29}30 31proc do_qexpr_test {tn expr value} {32 uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]33}34 35# Set up three global variables:36#37# ::opname An array mapping from SQL operator to an easy to parse38# name. The names are used as part of test case names.39#40# ::opprec An array mapping from SQL operator to a numeric41# precedence value. Operators that group more tightly42# have lower numeric precedences.43#44# ::oplist A list of all SQL operators supported by SQLite.45#46foreach {op opn} {47 || cat * mul / div % mod + add48 - sub << lshift >> rshift & bitand | bitor49 < less <= lesseq > more >= moreeq = eq150 == eq2 <> ne1 != ne2 IS is LIKE like51 GLOB glob AND and OR or MATCH match REGEXP regexp52 {IS NOT} isnt53} {54 set ::opname($op) $opn55}56set oplist [list]57foreach {prec opl} {58 1 ||59 2 {* / %}60 3 {+ -}61 4 {<< >> & |}62 5 {< <= > >=}63 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}64 7 AND65 8 OR66} {67 foreach op $opl { 68 set ::opprec($op) $prec 69 lappend oplist $op70 }71}72 73 74# Hook in definitions of MATCH and REGEX. The following implementations75# cause MATCH and REGEX to behave similarly to the == operator.76#77proc matchfunc {a b} { return [expr {$a==$b}] }78proc regexfunc {a b} { return [expr {$a==$b}] }79db func match -argcount 2 matchfunc80db func regexp -argcount 2 regexfunc81 82#-------------------------------------------------------------------------83# Test cases e_expr-1.* attempt to verify that all binary operators listed84# in the documentation exist and that the relative precedences of the85# operators are also as the documentation suggests.86#87# X-EVIDENCE-OF: R-15514-65163 SQLite understands the following binary88# operators, in order from highest to lowest precedence: || * / % + -89# << >> & | < <= > >= = == != <> IS IS90# NOT IN LIKE GLOB MATCH REGEXP AND OR91#92# X-EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same93# precedence as =.94#95 96unset -nocomplain untested97foreach op1 $oplist {98 foreach op2 $oplist {99 set untested($op1,$op2) 1100 foreach {tn A B C} {101 1 22 45 66102 2 0 0 0103 3 0 0 1104 4 0 1 0105 5 0 1 1106 6 1 0 0107 7 1 0 1108 8 1 1 0109 9 1 1 1110 10 5 6 1111 11 1 5 6112 12 1 5 5113 13 5 5 1114 115 14 5 2 1116 15 1 4 1117 16 -1 0 1118 17 0 1 -1119 120 } {121 set testname "e_expr-1.$opname($op1).$opname($op2).$tn"122 123 # If $op2 groups more tightly than $op1, then the result124 # of executing $sql1 whould be the same as executing $sql3.125 # If $op1 groups more tightly, or if $op1 and $op2 have 126 # the same precedence, then executing $sql1 should return127 # the same value as $sql2.128 #129 set sql1 "SELECT $A $op1 $B $op2 $C"130 set sql2 "SELECT ($A $op1 $B) $op2 $C"131 set sql3 "SELECT $A $op1 ($B $op2 $C)"132 133 set a2 [db one $sql2]134 set a3 [db one $sql3]135 136 do_execsql_test $testname $sql1 [list [137 if {$opprec($op2) < $opprec($op1)} {set a3} {set a2}138 ]]139 if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) }140 }141 }142}143 144foreach op {* AND OR + || & |} { unset untested($op,$op) }145unset untested(+,-) ;# Since (a+b)-c == a+(b-c)146unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c)147 148do_test e_expr-1.1 { array names untested } {}149 150# At one point, test 1.2.2 was failing. Instead of the correct result, it151# was returning {1 1 0}. This would seem to indicate that LIKE has the152# same precedence as '<'. Which is incorrect. It has lower precedence.153#154do_execsql_test e_expr-1.2.1 { 155 SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1)156} {1 1 0}157do_execsql_test e_expr-1.2.2 { 158 SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2)159} {0 1 0}160 161# Showing that LIKE and == have the same precedence162#163do_execsql_test e_expr-1.2.3 { 164 SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1)165} {1 1 0}166do_execsql_test e_expr-1.2.4 { 167 SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1)168} {1 1 0}169 170# Showing that < groups more tightly than == (< has higher precedence). 171#172do_execsql_test e_expr-1.2.5 { 173 SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1)174} {1 1 0}175do_execsql_test e_expr-1.6 { 176 SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2)177} {0 1 0}178 179#-------------------------------------------------------------------------180# Check that the four unary prefix operators mentioned in the 181# documentation exist.182#183# X-EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these:184# - + ~ NOT185#186do_execsql_test e_expr-2.1 { SELECT - 10 } {-10}187do_execsql_test e_expr-2.2 { SELECT + 10 } {10}188do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11}189do_execsql_test e_expr-2.4 { SELECT NOT 10 } {0}190 191#-------------------------------------------------------------------------192# Tests for the two statements made regarding the unary + operator.193#194# EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op.195#196# EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers,197# blobs or NULL and it always returns a result with the same value as198# the operand.199#200foreach {tn literal type} {201 1 'helloworld' text202 2 45 integer203 3 45.2 real204 4 45.0 real205 5 X'ABCDEF' blob206 6 NULL null207} {208 set sql " SELECT quote( + $literal ), typeof( + $literal) "209 do_execsql_test e_expr-3.$tn $sql [list $literal $type]210}211 212#-------------------------------------------------------------------------213# Check that both = and == are both acceptable as the "equals" operator.214# Similarly, either != or <> work as the not-equals operator.215#216# EVIDENCE-OF: R-03679-60639 Equals can be either = or ==.217#218# EVIDENCE-OF: R-49372-18364 The not-equal operator can be either != or219# <>.220#221foreach {tn literal different} {222 1 'helloworld' '12345'223 2 22 23224 3 'xyz' X'78797A'225 4 X'78797A00' 'xyz'226} {227 do_execsql_test e_expr-4.$tn "228 SELECT $literal = $literal, $literal == $literal,229 $literal = $different, $literal == $different,230 $literal = NULL, $literal == NULL,231 $literal != $literal, $literal <> $literal,232 $literal != $different, $literal <> $different,233 $literal != NULL, $literal != NULL234 235 " {1 1 0 0 {} {} 0 0 1 1 {} {}}236}237 238#-------------------------------------------------------------------------239# Test the || operator.240#241# EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins242# together the two strings of its operands.243#244foreach {tn a b} {245 1 'helloworld' '12345'246 2 22 23247} {248 set as [db one "SELECT $a"]249 set bs [db one "SELECT $b"]250 251 do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"]252}253 254#-------------------------------------------------------------------------255# Test the % operator.256#257# EVIDENCE-OF: R-53431-59159 The % operator casts both of its operands258# to type INTEGER and then computes the remainder after dividing the259# left integer by the right integer.260#261do_execsql_test e_expr-6.1 {SELECT 72%5} {2}262do_execsql_test e_expr-6.2 {SELECT 72%-5} {2}263do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2}264do_execsql_test e_expr-6.4 {SELECT -72%5} {-2}265do_execsql_test e_expr-6.5 {SELECT 72.35%5} {2.0}266 267#-------------------------------------------------------------------------268# EVIDENCE-OF: R-15904-00746 The result of any binary operator is either269# a numeric value or NULL, except for the || concatenation operator, and270# the -> and ->> extract operators which evaluate to either271# NULL or a text value.272#273set literals {274 1 'abc' 2 'hexadecimal' 3 ''275 4 123 5 -123 6 0276 7 123.4 8 0.0 9 -123.4277 10 X'ABCDEF' 11 X'' 12 X'0000'278 13 NULL279}280foreach op $oplist {281 if {$op eq "AND" || $op eq "OR"} {282 # These tests do not work with AND and OR due to short-circuit evaluation283 continue284 }285 foreach {n1 rhs} $literals { 286 foreach {n2 lhs} $literals {287 288 set t [db one " SELECT typeof($lhs $op $rhs) "]289 do_test e_expr-7.$opname($op).$n1.$n2 {290 expr {291 ($op=="||" && ($t == "text" || $t == "null"))292 || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null"))293 }294 } 1295 296 }}297}298 299#-------------------------------------------------------------------------300# Test the IS and IS NOT operators.301#302# EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and303# != except when one or both of the operands are NULL.304#305# EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL,306# then the IS operator evaluates to 1 (true) and the IS NOT operator307# evaluates to 0 (false).308#309# EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is310# not, then the IS operator evaluates to 0 (false) and the IS NOT311# operator is 1 (true).312#313# EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT314# expression to evaluate to NULL.315#316do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1}317do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0}318do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0}319do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1}320do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}}321do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}}322do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}}323do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1}324do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0}325do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1}326do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1}327do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0}328do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}}329do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}}330do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}}331do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0}332 333foreach {n1 rhs} $literals { 334 foreach {n2 lhs} $literals {335 if {$rhs!="NULL" && $lhs!="NULL"} {336 set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"]337 } else {338 set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \339 [expr {$lhs!="NULL" || $rhs!="NULL"}]340 ]341 }342 set test e_expr-8.2.$n1.$n2343 do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq344 do_execsql_test $test.2 "345 SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL346 " {0 0}347 }348}349 350#-------------------------------------------------------------------------351# Run some tests on the COLLATE "unary postfix operator".352#353# This collation sequence reverses both arguments before using 354# [string compare] to compare them. For example, when comparing the355# strings 'one' and 'four', return the result of:356# 357# string compare eno ruof358#359proc reverse_str {zStr} {360 set out ""361 foreach c [split $zStr {}] { set out "${c}${out}" }362 set out363}364proc reverse_collate {zLeft zRight} {365 string compare [reverse_str $zLeft] [reverse_str $zRight]366}367db collate reverse reverse_collate368 369# EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix370# operator that assigns a collating sequence to an expression.371#372# X-EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher373# precedence (binds more tightly) than any binary operator and any unary374# prefix operator except "~".375#376do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0377do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1378do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0379do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1380 381do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1382do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb') COLLATE reverse } 0383do_execsql_test e_expr-9.7 { SELECT 'abcd' >= 'bbbb' COLLATE reverse } 1384do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb') COLLATE reverse } 0385 386do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1387do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0388do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1389do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0390do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1391do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0392 393do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0394do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1395do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0396do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1397do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0398do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1399 400do_execsql_test e_expr-9.22 { 401 SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase 402} 1403do_execsql_test e_expr-9.23 { 404 SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase 405} 0406 407# EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE408# operator overrides the collating sequence determined by the COLLATE409# clause in a table column definition.410#411do_execsql_test e_expr-9.24 { 412 CREATE TABLE t24(a COLLATE NOCASE, b);413 INSERT INTO t24 VALUES('aaa', 1);414 INSERT INTO t24 VALUES('bbb', 2);415 INSERT INTO t24 VALUES('ccc', 3);416} {}417do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0}418do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0}419do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0}420do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0}421 422#-------------------------------------------------------------------------423# Test statements related to literal values.424#425# EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating426# point numbers, strings, BLOBs, or NULLs.427#428do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer}429do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real}430do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text}431do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob}432do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null}433 434# "Scientific notation is supported for point literal values."435#436do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real}437do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real}438do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034}439do_execsql_test e_expr-10.2.4 { SELECT 3e+4 } {30000.0}440 441# EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing442# the string in single quotes (').443#444# EVIDENCE-OF: R-07100-06606 A single quote within the string can be445# encoded by putting two single quotes in a row - as in Pascal.446#447do_execsql_test e_expr-10.3.1 { SELECT 'is not' } {{is not}}448do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text}449do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' } {isn't}450do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text}451 452# EVIDENCE-OF: R-09593-03321 BLOB literals are string literals453# containing hexadecimal data and preceded by a single "x" or "X"454# character.455#456# EVIDENCE-OF: R-19836-11244 Example: X'53514C697465'457#458do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob459do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob460do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob461do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob462do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465') } blob463 464# EVIDENCE-OF: R-23914-51476 A literal value can also be the token465# "NULL".466#467do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}}468do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null}469 470#-------------------------------------------------------------------------471# Test statements related to bound parameters472#473 474proc parameter_test {tn sql params result} {475 set stmt [sqlite3_prepare_v2 db $sql -1]476 477 foreach {number name} $params {478 set nm [sqlite3_bind_parameter_name $stmt $number]479 do_test $tn.name.$number [list set {} $nm] $name480 sqlite3_bind_int $stmt $number [expr -1 * $number]481 }482 483 sqlite3_step $stmt484 485 set res [list]486 for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {487 lappend res [sqlite3_column_text $stmt $i]488 }489 490 set rc [sqlite3_finalize $stmt]491 do_test $tn.rc [list set {} $rc] SQLITE_OK492 do_test $tn.res [list set {} $res] $result493}494 495# EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN496# holds a spot for the NNN-th parameter. NNN must be between 1 and497# SQLITE_MAX_VARIABLE_NUMBER.498#499set mvn $SQLITE_MAX_VARIABLE_NUMBER500parameter_test e_expr-11.1 "501 SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4502" "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4"503 504set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER"505foreach {tn param_number} [list \506 2 0 \507 3 [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \508 4 [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \509 5 12345678903456789034567890234567890 \510 6 2147483648 \511 7 2147483649 \512 8 4294967296 \513 9 4294967297 \514 10 9223372036854775808 \515 11 9223372036854775809 \516 12 18446744073709551616 \517 13 18446744073709551617 \518] {519 do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg]520}521 522# EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a523# number creates a parameter with a number one greater than the largest524# parameter number already assigned.525#526# EVIDENCE-OF: R-42938-07030 If this means the parameter number is527# greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error.528#529parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1530parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2}531parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6}532parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5}533parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" {534 1 {} 456 ?456 457 {}535} {-1 -456 -457}536parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" {537 1 {} 456 ?456 4 ?4 457 {}538} {-1 -456 -4 -457}539foreach {tn sql} [list \540 1 "SELECT ?$mvn, ?" \541 2 "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?" \542 3 "SELECT ?[expr $mvn], ?5, ?6, ?" \543] {544 do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}]545}546 547# EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name548# holds a spot for a named parameter with the name :AAAA.549#550# Identifiers in SQLite consist of alphanumeric, '_' and '$' characters,551# and any UTF characters with codepoints larger than 127 (non-ASCII 552# characters).553#554parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1555parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1556parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1557parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1558parameter_test e_expr-11.2.5 "559 SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25560" "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1561parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1562 563# EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon,564# except that the name of the parameter created is @AAAA.565#566parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1567parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1568parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1569parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1570parameter_test e_expr-11.3.5 "571 SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25572" "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1573parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1574 575# EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier576# name also holds a spot for a named parameter with the name $AAAA.577#578# EVIDENCE-OF: R-55025-21042 The identifier name in this case can579# include one or more occurrences of "::" and a suffix enclosed in580# "(...)" containing any text at all.581#582# Note: Looks like an identifier cannot consist entirely of "::" 583# characters or just a suffix. Also, the other named variable characters584# (: and @) work the same way internally. Why not just document it that way?585#586parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1587parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1588parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1589parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1590parameter_test e_expr-11.4.5 "591 SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25592" "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1593parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1594 595parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1596parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1597parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1598 599# EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The600# number assigned is one greater than the largest parameter number601# already assigned.602#603# EVIDENCE-OF: R-42620-22184 If this means the parameter would be604# assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an605# error.606#607parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2}608parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124}609parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} {610 1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c611} {-1 -8 -9 -10 -2 -11}612foreach {tn sql} [list \613 1 "SELECT ?$mvn, \$::a" \614 2 "SELECT ?$mvn, ?4, @a1" \615 3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \616] {617 do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}]618}619 620# EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values621# using sqlite3_bind() are treated as NULL.622#623do_test e_expr-11.7.1 {624 set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1]625 sqlite3_step $stmt626 627 list [sqlite3_column_type $stmt 0] \628 [sqlite3_column_type $stmt 1] \629 [sqlite3_column_type $stmt 2] \630 [sqlite3_column_type $stmt 3] 631} {NULL NULL NULL NULL}632do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK633 634#-------------------------------------------------------------------------635# "Test" the syntax diagrams in lang_expr.html.636#637# -- syntax diagram signed-number638#639do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}640do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}641do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}642do_execsql_test e_expr-12.1.4 { 643 SELECT 1.4, +1.4, -1.4 644} {1.4 1.4 -1.4}645do_execsql_test e_expr-12.1.5 { 646 SELECT 1.5e+5, +1.5e+5, -1.5e+5 647} {150000.0 150000.0 -150000.0}648do_execsql_test e_expr-12.1.6 { 649 SELECT 0.0001, +0.0001, -0.0001 650} {0.0001 0.0001 -0.0001}651 652# -- syntax diagram literal-value653#654set sqlite_current_time 1655do_execsql_test e_expr-12.2.1 {SELECT 123} {123}656do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0}657do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde}658do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC}659do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}}660do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01}661do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01}662do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}663set sqlite_current_time 0664 665# -- syntax diagram expr666#667forcedelete test.db2668execsql {669 ATTACH 'test.db2' AS dbname;670 CREATE TABLE dbname.tblname(cname);671}672 673proc glob {args} {return 1}674db function glob glob675db function match glob676db function regexp glob677 678foreach {tn expr} {679 1 123680 2 123.4e05681 3 'abcde'682 4 X'414243'683 5 NULL684 6 CURRENT_TIME685 7 CURRENT_DATE686 8 CURRENT_TIMESTAMP687 688 9 ?689 10 ?123690 11 @hello691 12 :world692 13 $tcl693 14 $tcl(array)694 695 15 cname696 16 tblname.cname697 17 dbname.tblname.cname698 699 18 "+ EXPR"700 19 "- EXPR"701 20 "NOT EXPR"702 21 "~ EXPR"703 704 22 "EXPR1 || EXPR2"705 23 "EXPR1 * EXPR2"706 24 "EXPR1 / EXPR2"707 25 "EXPR1 % EXPR2"708 26 "EXPR1 + EXPR2"709 27 "EXPR1 - EXPR2"710 28 "EXPR1 << EXPR2"711 29 "EXPR1 >> EXPR2"712 30 "EXPR1 & EXPR2"713 31 "EXPR1 | EXPR2"714 32 "EXPR1 < EXPR2"715 33 "EXPR1 <= EXPR2"716 34 "EXPR1 > EXPR2"717 35 "EXPR1 >= EXPR2"718 36 "EXPR1 = EXPR2"719 37 "EXPR1 == EXPR2"720 38 "EXPR1 != EXPR2"721 39 "EXPR1 <> EXPR2"722 40 "EXPR1 IS EXPR2"723 41 "EXPR1 IS NOT EXPR2"724 42 "EXPR1 AND EXPR2"725 43 "EXPR1 OR EXPR2"726 727 44 "count(*)"728 45 "count(DISTINCT EXPR)"729 46 "substr(EXPR, 10, 20)"730 47 "changes()"731 732 48 "( EXPR )"733 734 49 "CAST ( EXPR AS integer )"735 50 "CAST ( EXPR AS 'abcd' )"736 51 "CAST ( EXPR AS 'ab$ $cd' )"737 738 52 "EXPR COLLATE nocase"739 53 "EXPR COLLATE binary"740 741 54 "EXPR1 LIKE EXPR2"742 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"743 56 "EXPR1 GLOB EXPR2"744 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"745 58 "EXPR1 REGEXP EXPR2"746 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"747 60 "EXPR1 MATCH EXPR2"748 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"749 62 "EXPR1 NOT LIKE EXPR2"750 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"751 64 "EXPR1 NOT GLOB EXPR2"752 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"753 66 "EXPR1 NOT REGEXP EXPR2"754 67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"755 68 "EXPR1 NOT MATCH EXPR2"756 69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"757 758 70 "EXPR ISNULL"759 71 "EXPR NOTNULL"760 72 "EXPR NOT NULL"761 762 73 "EXPR1 IS EXPR2"763 74 "EXPR1 IS NOT EXPR2"764 765 75 "EXPR NOT BETWEEN EXPR1 AND EXPR2"766 76 "EXPR BETWEEN EXPR1 AND EXPR2"767 768 77 "EXPR NOT IN (SELECT cname FROM tblname)"769 78 "EXPR NOT IN (1)"770 79 "EXPR NOT IN (1, 2, 3)"771 80 "EXPR NOT IN tblname"772 81 "EXPR NOT IN dbname.tblname"773 82 "EXPR IN (SELECT cname FROM tblname)"774 83 "EXPR IN (1)"775 84 "EXPR IN (1, 2, 3)"776 85 "EXPR IN tblname"777 86 "EXPR IN dbname.tblname"778 779 87 "EXISTS (SELECT cname FROM tblname)"780 88 "NOT EXISTS (SELECT cname FROM tblname)"781 782 89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"783 90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END"784 91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"785 92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"786 93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"787 94 "CASE WHEN EXPR1 THEN EXPR2 END"788 95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"789 96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"790} {791 792 # If the expression string being parsed contains "EXPR2", then replace793 # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it 794 # contains "EXPR", then replace EXPR with an arbitrary SQL expression.795 # 796 set elist [list $expr]797 if {[string match *EXPR2* $expr]} {798 set elist [list]799 foreach {e1 e2} { cname "34+22" } {800 lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr]801 }802 } 803 if {[string match *EXPR* $expr]} {804 set elist2 [list]805 foreach el $elist {806 foreach e { cname "34+22" } {807 lappend elist2 [string map [list EXPR $e] $el]808 }809 }810 set elist $elist2811 }812 813 set x 0814 foreach e $elist {815 incr x816 do_test e_expr-12.3.$tn.$x { 817 set rc [catch { execsql "SELECT $e FROM tblname" } msg]818 } {0}819 }820}821 822# -- syntax diagram raise-function823#824foreach {tn raiseexpr} {825 1 "RAISE(IGNORE)"826 2 "RAISE(ROLLBACK, 'error message')"827 3 "RAISE(ABORT, 'error message')"828 4 "RAISE(FAIL, 'error message')"829} {830 do_execsql_test e_expr-12.4.$tn "831 CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN832 SELECT $raiseexpr ;833 END;834 " {}835}836 837#-------------------------------------------------------------------------838# Test the statements related to the BETWEEN operator.839#840# EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically841# equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent842# to "x>=y AND x<=z" except that with BETWEEN, the x expression is843# only evaluated once.844#845db func x x846proc x {} { incr ::xcount ; return [expr $::x] }847foreach {tn x expr res nEval} {848 1 10 "x() >= 5 AND x() <= 15" 1 2849 2 10 "x() BETWEEN 5 AND 15" 1 1850 851 3 5 "x() >= 5 AND x() <= 5" 1 2852 4 5 "x() BETWEEN 5 AND 5" 1 1853 854 5 9 "(x(),8) >= (9,7) AND (x(),8)<=(9,10)" 1 2855 6 9 "(x(),8) BETWEEN (9,7) AND (9,10)" 1 1856} {857 do_test e_expr-13.1.$tn {858 set ::xcount 0859 set a [execsql "SELECT $expr"]860 list $::xcount $a861 } [list $nEval $res]862}863 864# X-EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is865# the same as the precedence as operators == and != and LIKE and groups866# left to right.867# 868# Therefore, BETWEEN groups more tightly than operator "AND", but less869# so than "<".870#871do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1872do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1873do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0874do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1875do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1876do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0877 878do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1879do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1880do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0881do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1882do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1883do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0884 885do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1886do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1887do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0888do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1889do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1890do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0891 892do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0893do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0894do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1895do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0896do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0897do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1898 899do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1900do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1901do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0902do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0903do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0904do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1905 906#-------------------------------------------------------------------------907# Test the statements related to the LIKE and GLOB operators.908#909# EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching910# comparison.911#912# EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE913# operator contains the pattern and the left hand operand contains the914# string to match against the pattern.915#916do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0917do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1918 919# EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern920# matches any sequence of zero or more characters in the string.921#922do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1923do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1924do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1925 926# EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern927# matches any single character in the string.928#929do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0930do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1931do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0932 933# EVIDENCE-OF: R-59007-20454 Any other character matches itself or its934# lower/upper case equivalent (i.e. case-insensitive matching).935#936do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1937do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1938do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0939 940# EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case941# for ASCII characters by default.942#943# EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by944# default for unicode characters that are beyond the ASCII range.945#946# EVIDENCE-OF: R-44381-11669 the expression947# 'a' LIKE 'A' is TRUE but948# 'æ' LIKE 'Æ' is FALSE.949#950# The restriction to ASCII characters does not apply if the ICU951# library is compiled in. When ICU is enabled SQLite does not act952# as it does "by default".953#954do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1955ifcapable !icu {956 do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0957}958 959# EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,960# then the expression following the ESCAPE keyword must evaluate to a961# string consisting of a single character.962#963do_catchsql_test e_expr-14.6.1 { 964 SELECT 'A' LIKE 'a' ESCAPE '12' 965} {1 {ESCAPE expression must be a single character}}966do_catchsql_test e_expr-14.6.2 { 967 SELECT 'A' LIKE 'a' ESCAPE '' 968} {1 {ESCAPE expression must be a single character}}969do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1}970do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1}971 972# EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE973# pattern to include literal percent or underscore characters.974#975# EVIDENCE-OF: R-13345-31830 The escape character followed by a percent976# symbol (%), underscore (_), or a second instance of the escape977# character itself matches a literal percent symbol, underscore, or a978# single escape character, respectively.979#980do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1981do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0982do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0983do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0984do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0985 986do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1987do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0988do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0989do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0990do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0991 992do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1993do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0994do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0995do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0996 997# EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by998# calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).999#1000proc likefunc {args} {1001 eval lappend ::likeargs $args1002 return 11003}1004db func like -argcount 2 likefunc1005db func like -argcount 3 likefunc1006set ::likeargs [list]1007do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 11008do_test e_expr-15.1.2 { set likeargs } {def abc}1009set ::likeargs [list]1010do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 11011do_test e_expr-15.1.4 { set likeargs } {def abc X}1012db close1013sqlite3 db test.db1014 1015# EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case1016# sensitive using the case_sensitive_like pragma.1017#1018do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 11019do_execsql_test e_expr-16.1.1b { SELECT 'abc%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 11020do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {}1021do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 01022do_execsql_test e_expr-16.1.3b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 01023do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 11024do_execsql_test e_expr-16.1.4b { SELECT 'ABC%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 11025do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {}1026do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 11027do_execsql_test e_expr-16.1.6b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 11028do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 11029do_execsql_test e_expr-16.1.7b { SELECT 'ABC%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 11030 1031# EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but1032# uses the Unix file globbing syntax for its wildcards.1033#1034# EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE.1035#1036do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 01037do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 11038do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 01039do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 11040 1041do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 11042do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 01043do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 01044 1045# EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the1046# NOT keyword to invert the sense of the test.1047#1048do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 11049do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 01050do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 01051do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 01052do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 11053 1054db nullvalue null1055do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null1056do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null1057do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null1058do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null1059db nullvalue {}1060 1061# EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by1062# calling the function glob(Y,X) and can be modified by overriding that1063# function.1064proc globfunc {args} {1065 eval lappend ::globargs $args1066 return 11067}1068db func glob -argcount 2 globfunc1069set ::globargs [list]1070do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 11071do_test e_expr-17.3.2 { set globargs } {def abc}1072set ::globargs [list]1073do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 01074do_test e_expr-17.3.4 { set globargs } {Y X}1075sqlite3 db test.db1076 1077# EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by1078# default and so use of the REGEXP operator will normally result in an1079# error message.1080#1081# There is a regexp function if ICU is enabled though.1082#1083ifcapable !icu {1084 do_catchsql_test e_expr-18.1.1 { 1085 SELECT regexp('abc', 'def') 1086 } {1 {no such function: regexp}}1087 do_catchsql_test e_expr-18.1.2 { 1088 SELECT 'abc' REGEXP 'def'1089 } {1 {no such function: REGEXP}}1090}1091 1092# EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for1093# the regexp() user function.1094#1095# EVIDENCE-OF: R-65524-61849 If an application-defined SQL function1096# named "regexp" is added at run-time, then the "X REGEXP Y" operator1097# will be implemented as a call to "regexp(Y,X)".1098#1099proc regexpfunc {args} {1100 eval lappend ::regexpargs $args1101 return 11102}1103db func regexp -argcount 2 regexpfunc1104set ::regexpargs [list]1105do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 11106do_test e_expr-18.2.2 { set regexpargs } {def abc}1107set ::regexpargs [list]1108do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 01109do_test e_expr-18.2.4 { set regexpargs } {Y X}1110sqlite3 db test.db1111 1112# EVIDENCE-OF: R-42037-37826 The default match() function implementation1113# raises an exception and is not really useful for anything.1114#1115do_catchsql_test e_expr-19.1.1 { 1116 SELECT 'abc' MATCH 'def' 1117} {1 {unable to use function MATCH in the requested context}}1118do_catchsql_test e_expr-19.1.2 { 1119 SELECT match('abc', 'def')1120} {1 {unable to use function MATCH in the requested context}}1121 1122# EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for1123# the match() application-defined function.1124#1125# EVIDENCE-OF: R-06021-09373 But extensions can override the match()1126# function with more helpful logic.1127#1128proc matchfunc {args} {1129 eval lappend ::matchargs $args1130 return 11131}1132db func match -argcount 2 matchfunc1133set ::matchargs [list]1134do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 11135do_test e_expr-19.2.2 { set matchargs } {def abc}1136set ::matchargs [list]1137do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 01138do_test e_expr-19.2.4 { set matchargs } {Y X}1139sqlite3 db test.db1140 1141#-------------------------------------------------------------------------1142# Test cases for the testable statements related to the CASE expression.1143#1144# EVIDENCE-OF: R-57495-24088 There are two fundamental forms of the CASE1145# expression: those with a base expression and those without.1146#1147do_execsql_test e_expr-20.1 {1148 SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;1149} {true}1150do_execsql_test e_expr-20.2 {1151 SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;1152} {false}1153 1154proc var {nm} {1155 lappend ::varlist $nm1156 return [set "::$nm"]1157}1158db func var var1159 1160# EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each1161# WHEN expression is evaluated and the result treated as a boolean,1162# starting with the leftmost and continuing to the right.1163#1164foreach {a b c} {0 0 0} break1165set varlist [list]1166do_execsql_test e_expr-21.1.1 {1167 SELECT CASE WHEN var('a') THEN 'A' 1168 WHEN var('b') THEN 'B' 1169 WHEN var('c') THEN 'C' END1170} {{}}1171do_test e_expr-21.1.2 { set varlist } {a b c}1172set varlist [list]1173do_execsql_test e_expr-21.1.3 {1174 SELECT CASE WHEN var('c') THEN 'C' 1175 WHEN var('b') THEN 'B' 1176 WHEN var('a') THEN 'A' 1177 ELSE 'no result'1178 END1179} {{no result}}1180do_test e_expr-21.1.4 { set varlist } {c b a}1181 1182# EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the1183# evaluation of the THEN expression that corresponds to the first WHEN1184# expression that evaluates to true.1185#1186foreach {a b c} {0 1 0} break1187do_execsql_test e_expr-21.2.1 {1188 SELECT CASE WHEN var('a') THEN 'A' 1189 WHEN var('b') THEN 'B' 1190 WHEN var('c') THEN 'C' 1191 ELSE 'no result'1192 END1193} {B}1194foreach {a b c} {0 1 1} break1195do_execsql_test e_expr-21.2.2 {1196 SELECT CASE WHEN var('a') THEN 'A' 1197 WHEN var('b') THEN 'B' 1198 WHEN var('c') THEN 'C'1199 ELSE 'no result'1200 END