| 1 |
# 2010 July 16 |
| 2 |
# |
| 3 |
# The author disclaims copyright to this source code. In place of |
| 4 |
# a legal notice, here is a blessing: |
| 5 |
# |
| 6 |
# May you do good and not evil. |
| 7 |
# May you find forgiveness for yourself and forgive others. |
| 8 |
# May you share freely, never taking more than you give. |
| 9 |
# |
| 10 |
#*********************************************************************** |
| 11 |
# |
| 12 |
# This file implements tests to verify that the "testable statements" in |
| 13 |
# the lang_expr.html document are correct. |
| 14 |
# |
| 15 |
|
| 16 |
set testdir [file dirname $argv0] |
| 17 |
source $testdir/tester.tcl |
| 18 |
source $testdir/malloc_common.tcl |
| 19 |
|
| 20 |
ifcapable !compound { |
| 21 |
finish_test |
| 22 |
return |
| 23 |
} |
| 24 |
|
| 25 |
proc 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 |
|
| 31 |
proc 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 parse |
| 38 |
# name. The names are used as part of test case names. |
| 39 |
# |
| 40 |
# ::opprec An array mapping from SQL operator to a numeric |
| 41 |
# precedence value. Operators that group more tightly |
| 42 |
# have lower numeric precedences. |
| 43 |
# |
| 44 |
# ::oplist A list of all SQL operators supported by SQLite. |
| 45 |
# |
| 46 |
foreach {op opn} { |
| 47 |
|| cat * mul / div % mod + add |
| 48 |
- sub << lshift >> rshift & bitand | bitor |
| 49 |
< less <= lesseq > more >= moreeq = eq1 |
| 50 |
== eq2 <> ne1 != ne2 IS is LIKE like |
| 51 |
GLOB glob AND and OR or MATCH match REGEXP regexp |
| 52 |
{IS NOT} isnt |
| 53 |
} { |
| 54 |
set ::opname($op) $opn |
| 55 |
} |
| 56 |
set oplist [list] |
| 57 |
foreach {prec opl} { |
| 58 |
1 || |
| 59 |
2 {* / %} |
| 60 |
3 {+ -} |
| 61 |
4 {<< >> & |} |
| 62 |
5 {< <= > >=} |
| 63 |
6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP} |
| 64 |
7 AND |
| 65 |
8 OR |
| 66 |
} { |
| 67 |
foreach op $opl { |
| 68 |
set ::opprec($op) $prec |
| 69 |
lappend oplist $op |
| 70 |
} |
| 71 |
} |
| 72 |
|
| 73 |
|
| 74 |
# Hook in definitions of MATCH and REGEX. The following implementations |
| 75 |
# cause MATCH and REGEX to behave similarly to the == operator. |
| 76 |
# |
| 77 |
proc matchfunc {a b} { return [expr {$a==$b}] } |
| 78 |
proc regexfunc {a b} { return [expr {$a==$b}] } |
| 79 |
db func match -argcount 2 matchfunc |
| 80 |
db func regexp -argcount 2 regexfunc |
| 81 |
|
| 82 |
#------------------------------------------------------------------------- |
| 83 |
# Test cases e_expr-1.* attempt to verify that all binary operators listed |
| 84 |
# in the documentation exist and that the relative precedences of the |
| 85 |
# operators are also as the documentation suggests. |
| 86 |
# |
| 87 |
# X-EVIDENCE-OF: R-15514-65163 SQLite understands the following binary |
| 88 |
# operators, in order from highest to lowest precedence: || * / % + - |
| 89 |
# << >> & | < <= > >= = == != <> IS IS |
| 90 |
# NOT IN LIKE GLOB MATCH REGEXP AND OR |
| 91 |
# |
| 92 |
# X-EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same |
| 93 |
# precedence as =. |
| 94 |
# |
| 95 |
|
| 96 |
unset -nocomplain untested |
| 97 |
foreach op1 $oplist { |
| 98 |
foreach op2 $oplist { |
| 99 |
set untested($op1,$op2) 1 |
| 100 |
foreach {tn A B C} { |
| 101 |
1 22 45 66 |
| 102 |
2 0 0 0 |
| 103 |
3 0 0 1 |
| 104 |
4 0 1 0 |
| 105 |
5 0 1 1 |
| 106 |
6 1 0 0 |
| 107 |
7 1 0 1 |
| 108 |
8 1 1 0 |
| 109 |
9 1 1 1 |
| 110 |
10 5 6 1 |
| 111 |
11 1 5 6 |
| 112 |
12 1 5 5 |
| 113 |
13 5 5 1 |
| 114 |
|
| 115 |
14 5 2 1 |
| 116 |
15 1 4 1 |
| 117 |
16 -1 0 1 |
| 118 |
17 0 1 -1 |
| 119 |
|
| 120 |
} { |
| 121 |
set testname "e_expr-1.$opname($op1).$opname($op2).$tn" |
| 122 |
|
| 123 |
# If $op2 groups more tightly than $op1, then the result |
| 124 |
# 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 return |
| 127 |
# 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 |
|
| 144 |
foreach op {* AND OR + || & |} { unset untested($op,$op) } |
| 145 |
unset untested(+,-) ;# Since (a+b)-c == a+(b-c) |
| 146 |
unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c) |
| 147 |
|
| 148 |
do_test e_expr-1.1 { array names untested } {} |
| 149 |
|
| 150 |
# At one point, test 1.2.2 was failing. Instead of the correct result, it |
| 151 |
# was returning {1 1 0}. This would seem to indicate that LIKE has the |
| 152 |
# same precedence as '<'. Which is incorrect. It has lower precedence. |
| 153 |
# |
| 154 |
do_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} |
| 157 |
do_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 precedence |
| 162 |
# |
| 163 |
do_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} |
| 166 |
do_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 |
# |
| 172 |
do_execsql_test e_expr-1.2.5 { |
| 173 |
SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1) |
| 174 |
} {1 1 0} |
| 175 |
do_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 |
# - + ~ NOT |
| 185 |
# |
| 186 |
do_execsql_test e_expr-2.1 { SELECT - 10 } {-10} |
| 187 |
do_execsql_test e_expr-2.2 { SELECT + 10 } {10} |
| 188 |
do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11} |
| 189 |
do_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 as |
| 198 |
# the operand. |
| 199 |
# |
| 200 |
foreach {tn literal type} { |
| 201 |
1 'helloworld' text |
| 202 |
2 45 integer |
| 203 |
3 45.2 real |
| 204 |
4 45.0 real |
| 205 |
5 X'ABCDEF' blob |
| 206 |
6 NULL null |
| 207 |
} { |
| 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 != or |
| 219 |
# <>. |
| 220 |
# |
| 221 |
foreach {tn literal different} { |
| 222 |
1 'helloworld' '12345' |
| 223 |
2 22 23 |
| 224 |
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 != NULL |
| 234 |
|
| 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 joins |
| 242 |
# together the two strings of its operands. |
| 243 |
# |
| 244 |
foreach {tn a b} { |
| 245 |
1 'helloworld' '12345' |
| 246 |
2 22 23 |
| 247 |
} { |
| 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 operands |
| 258 |
# to type INTEGER and then computes the remainder after dividing the |
| 259 |
# left integer by the right integer. |
| 260 |
# |
| 261 |
do_execsql_test e_expr-6.1 {SELECT 72%5} {2} |
| 262 |
do_execsql_test e_expr-6.2 {SELECT 72%-5} {2} |
| 263 |
do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2} |
| 264 |
do_execsql_test e_expr-6.4 {SELECT -72%5} {-2} |
| 265 |
do_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 either |
| 269 |
# a numeric value or NULL, except for the || concatenation operator, and |
| 270 |
# the -> and ->> extract operators which evaluate to either |
| 271 |
# NULL or a text value. |
| 272 |
# |
| 273 |
set literals { |
| 274 |
1 'abc' 2 'hexadecimal' 3 '' |
| 275 |
4 123 5 -123 6 0 |
| 276 |
7 123.4 8 0.0 9 -123.4 |
| 277 |
10 X'ABCDEF' 11 X'' 12 X'0000' |
| 278 |
13 NULL |
| 279 |
} |
| 280 |
foreach op $oplist { |
| 281 |
if {$op eq "AND" || $op eq "OR"} { |
| 282 |
# These tests do not work with AND and OR due to short-circuit evaluation |
| 283 |
continue |
| 284 |
} |
| 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 |
} 1 |
| 295 |
|
| 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 = and |
| 303 |
# != 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 operator |
| 307 |
# evaluates to 0 (false). |
| 308 |
# |
| 309 |
# EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is |
| 310 |
# not, then the IS operator evaluates to 0 (false) and the IS NOT |
| 311 |
# operator is 1 (true). |
| 312 |
# |
| 313 |
# EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT |
| 314 |
# expression to evaluate to NULL. |
| 315 |
# |
| 316 |
do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1} |
| 317 |
do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0} |
| 318 |
do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0} |
| 319 |
do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1} |
| 320 |
do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}} |
| 321 |
do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}} |
| 322 |
do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}} |
| 323 |
do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1} |
| 324 |
do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0} |
| 325 |
do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1} |
| 326 |
do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1} |
| 327 |
do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0} |
| 328 |
do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}} |
| 329 |
do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}} |
| 330 |
do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}} |
| 331 |
do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0} |
| 332 |
|
| 333 |
foreach {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.$n2 |
| 343 |
do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq |
| 344 |
do_execsql_test $test.2 " |
| 345 |
SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL |
| 346 |
" {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 the |
| 355 |
# strings 'one' and 'four', return the result of: |
| 356 |
# |
| 357 |
# string compare eno ruof |
| 358 |
# |
| 359 |
proc reverse_str {zStr} { |
| 360 |
set out "" |
| 361 |
foreach c [split $zStr {}] { set out "${c}${out}" } |
| 362 |
set out |
| 363 |
} |
| 364 |
proc reverse_collate {zLeft zRight} { |
| 365 |
string compare [reverse_str $zLeft] [reverse_str $zRight] |
| 366 |
} |
| 367 |
db collate reverse reverse_collate |
| 368 |
|
| 369 |
# EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix |
| 370 |
# operator that assigns a collating sequence to an expression. |
| 371 |
# |
| 372 |
# X-EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher |
| 373 |
# precedence (binds more tightly) than any binary operator and any unary |
| 374 |
# prefix operator except "~". |
| 375 |
# |
| 376 |
do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0 |
| 377 |
do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1 |
| 378 |
do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0 |
| 379 |
do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1 |
| 380 |
|
| 381 |
do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1 |
| 382 |
do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb') COLLATE reverse } 0 |
| 383 |
do_execsql_test e_expr-9.7 { SELECT 'abcd' >= 'bbbb' COLLATE reverse } 1 |
| 384 |
do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb') COLLATE reverse } 0 |
| 385 |
|
| 386 |
do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1 |
| 387 |
do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0 |
| 388 |
do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1 |
| 389 |
do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0 |
| 390 |
do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1 |
| 391 |
do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0 |
| 392 |
|
| 393 |
do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0 |
| 394 |
do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1 |
| 395 |
do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0 |
| 396 |
do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1 |
| 397 |
do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0 |
| 398 |
do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1 |
| 399 |
|
| 400 |
do_execsql_test e_expr-9.22 { |
| 401 |
SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase |
| 402 |
} 1 |
| 403 |
do_execsql_test e_expr-9.23 { |
| 404 |
SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase |
| 405 |
} 0 |
| 406 |
|
| 407 |
# EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE |
| 408 |
# operator overrides the collating sequence determined by the COLLATE |
| 409 |
# clause in a table column definition. |
| 410 |
# |
| 411 |
do_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 |
} {} |
| 417 |
do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0} |
| 418 |
do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0} |
| 419 |
do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0} |
| 420 |
do_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, floating |
| 426 |
# point numbers, strings, BLOBs, or NULLs. |
| 427 |
# |
| 428 |
do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer} |
| 429 |
do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real} |
| 430 |
do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text} |
| 431 |
do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob} |
| 432 |
do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null} |
| 433 |
|
| 434 |
# "Scientific notation is supported for point literal values." |
| 435 |
# |
| 436 |
do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real} |
| 437 |
do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real} |
| 438 |
do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034} |
| 439 |
do_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 enclosing |
| 442 |
# the string in single quotes ('). |
| 443 |
# |
| 444 |
# EVIDENCE-OF: R-07100-06606 A single quote within the string can be |
| 445 |
# encoded by putting two single quotes in a row - as in Pascal. |
| 446 |
# |
| 447 |
do_execsql_test e_expr-10.3.1 { SELECT 'is not' } {{is not}} |
| 448 |
do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text} |
| 449 |
do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' } {isn't} |
| 450 |
do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text} |
| 451 |
|
| 452 |
# EVIDENCE-OF: R-09593-03321 BLOB literals are string literals |
| 453 |
# containing hexadecimal data and preceded by a single "x" or "X" |
| 454 |
# character. |
| 455 |
# |
| 456 |
# EVIDENCE-OF: R-19836-11244 Example: X'53514C697465' |
| 457 |
# |
| 458 |
do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob |
| 459 |
do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob |
| 460 |
do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob |
| 461 |
do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob |
| 462 |
do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465') } blob |
| 463 |
|
| 464 |
# EVIDENCE-OF: R-23914-51476 A literal value can also be the token |
| 465 |
# "NULL". |
| 466 |
# |
| 467 |
do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}} |
| 468 |
do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null} |
| 469 |
|
| 470 |
#------------------------------------------------------------------------- |
| 471 |
# Test statements related to bound parameters |
| 472 |
# |
| 473 |
|
| 474 |
proc 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] $name |
| 480 |
sqlite3_bind_int $stmt $number [expr -1 * $number] |
| 481 |
} |
| 482 |
|
| 483 |
sqlite3_step $stmt |
| 484 |
|
| 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_OK |
| 492 |
do_test $tn.res [list set {} $res] $result |
| 493 |
} |
| 494 |
|
| 495 |
# EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN |
| 496 |
# holds a spot for the NNN-th parameter. NNN must be between 1 and |
| 497 |
# SQLITE_MAX_VARIABLE_NUMBER. |
| 498 |
# |
| 499 |
set mvn $SQLITE_MAX_VARIABLE_NUMBER |
| 500 |
parameter_test e_expr-11.1 " |
| 501 |
SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4 |
| 502 |
" "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4" |
| 503 |
|
| 504 |
set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER" |
| 505 |
foreach {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 a |
| 523 |
# number creates a parameter with a number one greater than the largest |
| 524 |
# parameter number already assigned. |
| 525 |
# |
| 526 |
# EVIDENCE-OF: R-42938-07030 If this means the parameter number is |
| 527 |
# greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error. |
| 528 |
# |
| 529 |
parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1 |
| 530 |
parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2} |
| 531 |
parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6} |
| 532 |
parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5} |
| 533 |
parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" { |
| 534 |
1 {} 456 ?456 457 {} |
| 535 |
} {-1 -456 -457} |
| 536 |
parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" { |
| 537 |
1 {} 456 ?456 4 ?4 457 {} |
| 538 |
} {-1 -456 -4 -457} |
| 539 |
foreach {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 name |
| 548 |
# 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 |
# |
| 554 |
parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1 |
| 555 |
parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1 |
| 556 |
parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1 |
| 557 |
parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1 |
| 558 |
parameter_test e_expr-11.2.5 " |
| 559 |
SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 |
| 560 |
" "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 |
| 561 |
parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1 |
| 562 |
|
| 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 |
# |
| 566 |
parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1 |
| 567 |
parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1 |
| 568 |
parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1 |
| 569 |
parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1 |
| 570 |
parameter_test e_expr-11.3.5 " |
| 571 |
SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 |
| 572 |
" "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 |
| 573 |
parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1 |
| 574 |
|
| 575 |
# EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier |
| 576 |
# 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 can |
| 579 |
# include one or more occurrences of "::" and a suffix enclosed in |
| 580 |
# "(...)" 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 characters |
| 584 |
# (: and @) work the same way internally. Why not just document it that way? |
| 585 |
# |
| 586 |
parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1 |
| 587 |
parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1 |
| 588 |
parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1 |
| 589 |
parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1 |
| 590 |
parameter_test e_expr-11.4.5 " |
| 591 |
SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25 |
| 592 |
" "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1 |
| 593 |
parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1 |
| 594 |
|
| 595 |
parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1 |
| 596 |
parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1 |
| 597 |
parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1 |
| 598 |
|
| 599 |
# EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The |
| 600 |
# number assigned is one greater than the largest parameter number |
| 601 |
# already assigned. |
| 602 |
# |
| 603 |
# EVIDENCE-OF: R-42620-22184 If this means the parameter would be |
| 604 |
# assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an |
| 605 |
# error. |
| 606 |
# |
| 607 |
parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2} |
| 608 |
parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124} |
| 609 |
parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} { |
| 610 |
1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c |
| 611 |
} {-1 -8 -9 -10 -2 -11} |
| 612 |
foreach {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 values |
| 621 |
# using sqlite3_bind() are treated as NULL. |
| 622 |
# |
| 623 |
do_test e_expr-11.7.1 { |
| 624 |
set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1] |
| 625 |
sqlite3_step $stmt |
| 626 |
|
| 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} |
| 632 |
do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK |
| 633 |
|
| 634 |
#------------------------------------------------------------------------- |
| 635 |
# "Test" the syntax diagrams in lang_expr.html. |
| 636 |
# |
| 637 |
# -- syntax diagram signed-number |
| 638 |
# |
| 639 |
do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0} |
| 640 |
do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1} |
| 641 |
do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2} |
| 642 |
do_execsql_test e_expr-12.1.4 { |
| 643 |
SELECT 1.4, +1.4, -1.4 |
| 644 |
} {1.4 1.4 -1.4} |
| 645 |
do_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} |
| 648 |
do_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-value |
| 653 |
# |
| 654 |
set sqlite_current_time 1 |
| 655 |
do_execsql_test e_expr-12.2.1 {SELECT 123} {123} |
| 656 |
do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0} |
| 657 |
do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde} |
| 658 |
do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC} |
| 659 |
do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}} |
| 660 |
do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01} |
| 661 |
do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01} |
| 662 |
do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}} |
| 663 |
set sqlite_current_time 0 |
| 664 |
|
| 665 |
# -- syntax diagram expr |
| 666 |
# |
| 667 |
forcedelete test.db2 |
| 668 |
execsql { |
| 669 |
ATTACH 'test.db2' AS dbname; |
| 670 |
CREATE TABLE dbname.tblname(cname); |
| 671 |
} |
| 672 |
|
| 673 |
proc glob {args} {return 1} |
| 674 |
db function glob glob |
| 675 |
db function match glob |
| 676 |
db function regexp glob |
| 677 |
|
| 678 |
foreach {tn expr} { |
| 679 |
1 123 |
| 680 |
2 123.4e05 |
| 681 |
3 'abcde' |
| 682 |
4 X'414243' |
| 683 |
5 NULL |
| 684 |
6 CURRENT_TIME |
| 685 |
7 CURRENT_DATE |
| 686 |
8 CURRENT_TIMESTAMP |
| 687 |
|
| 688 |
9 ? |
| 689 |
10 ?123 |
| 690 |
11 @hello |
| 691 |
12 :world |
| 692 |
13 $tcl |
| 693 |
14 $tcl(array) |
| 694 |
|
| 695 |
15 cname |
| 696 |
16 tblname.cname |
| 697 |
17 dbname.tblname.cname |
| 698 |
|
| 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 replace |
| 793 |
# 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 $elist2 |
| 811 |
} |
| 812 |
|
| 813 |
set x 0 |
| 814 |
foreach e $elist { |
| 815 |
incr x |
| 816 |
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-function |
| 823 |
# |
| 824 |
foreach {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 BEGIN |
| 832 |
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 logically |
| 841 |
# equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent |
| 842 |
# to "x>=y AND x<=z" except that with BETWEEN, the x expression is |
| 843 |
# only evaluated once. |
| 844 |
# |
| 845 |
db func x x |
| 846 |
proc x {} { incr ::xcount ; return [expr $::x] } |
| 847 |
foreach {tn x expr res nEval} { |
| 848 |
1 10 "x() >= 5 AND x() <= 15" 1 2 |
| 849 |
2 10 "x() BETWEEN 5 AND 15" 1 1 |
| 850 |
|
| 851 |
3 5 "x() >= 5 AND x() <= 5" 1 2 |
| 852 |
4 5 "x() BETWEEN 5 AND 5" 1 1 |
| 853 |
|
| 854 |
5 9 "(x(),8) >= (9,7) AND (x(),8)<=(9,10)" 1 2 |
| 855 |
6 9 "(x(),8) BETWEEN (9,7) AND (9,10)" 1 1 |
| 856 |
} { |
| 857 |
do_test e_expr-13.1.$tn { |
| 858 |
set ::xcount 0 |
| 859 |
set a [execsql "SELECT $expr"] |
| 860 |
list $::xcount $a |
| 861 |
} [list $nEval $res] |
| 862 |
} |
| 863 |
|
| 864 |
# X-EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is |
| 865 |
# the same as the precedence as operators == and != and LIKE and groups |
| 866 |
# left to right. |
| 867 |
# |
| 868 |
# Therefore, BETWEEN groups more tightly than operator "AND", but less |
| 869 |
# so than "<". |
| 870 |
# |
| 871 |
do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1 |
| 872 |
do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1 |
| 873 |
do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0 |
| 874 |
do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1 |
| 875 |
do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1 |
| 876 |
do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0 |
| 877 |
|
| 878 |
do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1 |
| 879 |
do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1 |
| 880 |
do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0 |
| 881 |
do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1 |
| 882 |
do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1 |
| 883 |
do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0 |
| 884 |
|
| 885 |
do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1 |
| 886 |
do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1 |
| 887 |
do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0 |
| 888 |
do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1 |
| 889 |
do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1 |
| 890 |
do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0 |
| 891 |
|
| 892 |
do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0 |
| 893 |
do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0 |
| 894 |
do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1 |
| 895 |
do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0 |
| 896 |
do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0 |
| 897 |
do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1 |
| 898 |
|
| 899 |
do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1 |
| 900 |
do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1 |
| 901 |
do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0 |
| 902 |
do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0 |
| 903 |
do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0 |
| 904 |
do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1 |
| 905 |
|
| 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 matching |
| 910 |
# comparison. |
| 911 |
# |
| 912 |
# EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE |
| 913 |
# operator contains the pattern and the left hand operand contains the |
| 914 |
# string to match against the pattern. |
| 915 |
# |
| 916 |
do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0 |
| 917 |
do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1 |
| 918 |
|
| 919 |
# EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern |
| 920 |
# matches any sequence of zero or more characters in the string. |
| 921 |
# |
| 922 |
do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1 |
| 923 |
do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1 |
| 924 |
do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1 |
| 925 |
|
| 926 |
# EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern |
| 927 |
# matches any single character in the string. |
| 928 |
# |
| 929 |
do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0 |
| 930 |
do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1 |
| 931 |
do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0 |
| 932 |
|
| 933 |
# EVIDENCE-OF: R-59007-20454 Any other character matches itself or its |
| 934 |
# lower/upper case equivalent (i.e. case-insensitive matching). |
| 935 |
# |
| 936 |
do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1 |
| 937 |
do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1 |
| 938 |
do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0 |
| 939 |
|
| 940 |
# EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case |
| 941 |
# for ASCII characters by default. |
| 942 |
# |
| 943 |
# EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by |
| 944 |
# default for unicode characters that are beyond the ASCII range. |
| 945 |
# |
| 946 |
# EVIDENCE-OF: R-44381-11669 the expression |
| 947 |
# 'a' LIKE 'A' is TRUE but |
| 948 |
# 'æ' LIKE 'Æ' is FALSE. |
| 949 |
# |
| 950 |
# The restriction to ASCII characters does not apply if the ICU |
| 951 |
# library is compiled in. When ICU is enabled SQLite does not act |
| 952 |
# as it does "by default". |
| 953 |
# |
| 954 |
do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1 |
| 955 |
ifcapable !icu { |
| 956 |
do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0 |
| 957 |
} |
| 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 a |
| 961 |
# string consisting of a single character. |
| 962 |
# |
| 963 |
do_catchsql_test e_expr-14.6.1 { |
| 964 |
SELECT 'A' LIKE 'a' ESCAPE '12' |
| 965 |
} {1 {ESCAPE expression must be a single character}} |
| 966 |
do_catchsql_test e_expr-14.6.2 { |
| 967 |
SELECT 'A' LIKE 'a' ESCAPE '' |
| 968 |
} {1 {ESCAPE expression must be a single character}} |
| 969 |
do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1} |
| 970 |
do_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 LIKE |
| 973 |
# pattern to include literal percent or underscore characters. |
| 974 |
# |
| 975 |
# EVIDENCE-OF: R-13345-31830 The escape character followed by a percent |
| 976 |
# symbol (%), underscore (_), or a second instance of the escape |
| 977 |
# character itself matches a literal percent symbol, underscore, or a |
| 978 |
# single escape character, respectively. |
| 979 |
# |
| 980 |
do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1 |
| 981 |
do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0 |
| 982 |
do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0 |
| 983 |
do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0 |
| 984 |
do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0 |
| 985 |
|
| 986 |
do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1 |
| 987 |
do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0 |
| 988 |
do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0 |
| 989 |
do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0 |
| 990 |
do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0 |
| 991 |
|
| 992 |
do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1 |
| 993 |
do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0 |
| 994 |
do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0 |
| 995 |
do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0 |
| 996 |
|
| 997 |
# EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by |
| 998 |
# calling the application-defined SQL functions like(Y,X) or like(Y,X,Z). |
| 999 |
# |
| 1000 |
proc likefunc {args} { |
| 1001 |
eval lappend ::likeargs $args |
| 1002 |
return 1 |
| 1003 |
} |
| 1004 |
db func like -argcount 2 likefunc |
| 1005 |
db func like -argcount 3 likefunc |
| 1006 |
set ::likeargs [list] |
| 1007 |
do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1 |
| 1008 |
do_test e_expr-15.1.2 { set likeargs } {def abc} |
| 1009 |
set ::likeargs [list] |
| 1010 |
do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1 |
| 1011 |
do_test e_expr-15.1.4 { set likeargs } {def abc X} |
| 1012 |
db close |
| 1013 |
sqlite3 db test.db |
| 1014 |
|
| 1015 |
# EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case |
| 1016 |
# sensitive using the case_sensitive_like pragma. |
| 1017 |
# |
| 1018 |
do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1 |
| 1019 |
do_execsql_test e_expr-16.1.1b { SELECT 'abc%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1 |
| 1020 |
do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {} |
| 1021 |
do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0 |
| 1022 |
do_execsql_test e_expr-16.1.3b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 0 |
| 1023 |
do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 |
| 1024 |
do_execsql_test e_expr-16.1.4b { SELECT 'ABC%xyz' LIKE 'ABC\%x%' ESCAPE '\' } 1 |
| 1025 |
do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {} |
| 1026 |
do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1 |
| 1027 |
do_execsql_test e_expr-16.1.6b { SELECT 'abc%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1 |
| 1028 |
do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1 |
| 1029 |
do_execsql_test e_expr-16.1.7b { SELECT 'ABC%xyz' LIKE 'ABC\%X%' ESCAPE '\' } 1 |
| 1030 |
|
| 1031 |
# EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but |
| 1032 |
# uses the Unix file globbing syntax for its wildcards. |
| 1033 |
# |
| 1034 |
# EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE. |
| 1035 |
# |
| 1036 |
do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0 |
| 1037 |
do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1 |
| 1038 |
do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0 |
| 1039 |
do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1 |
| 1040 |
|
| 1041 |
do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1 |
| 1042 |
do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0 |
| 1043 |
do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0 |
| 1044 |
|
| 1045 |
# EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the |
| 1046 |
# NOT keyword to invert the sense of the test. |
| 1047 |
# |
| 1048 |
do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1 |
| 1049 |
do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0 |
| 1050 |
do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0 |
| 1051 |
do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0 |
| 1052 |
do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1 |
| 1053 |
|
| 1054 |
db nullvalue null |
| 1055 |
do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null |
| 1056 |
do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null |
| 1057 |
do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null |
| 1058 |
do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null |
| 1059 |
db nullvalue {} |
| 1060 |
|
| 1061 |
# EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by |
| 1062 |
# calling the function glob(Y,X) and can be modified by overriding that |
| 1063 |
# function. |
| 1064 |
proc globfunc {args} { |
| 1065 |
eval lappend ::globargs $args |
| 1066 |
return 1 |
| 1067 |
} |
| 1068 |
db func glob -argcount 2 globfunc |
| 1069 |
set ::globargs [list] |
| 1070 |
do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1 |
| 1071 |
do_test e_expr-17.3.2 { set globargs } {def abc} |
| 1072 |
set ::globargs [list] |
| 1073 |
do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0 |
| 1074 |
do_test e_expr-17.3.4 { set globargs } {Y X} |
| 1075 |
sqlite3 db test.db |
| 1076 |
|
| 1077 |
# EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by |
| 1078 |
# default and so use of the REGEXP operator will normally result in an |
| 1079 |
# error message. |
| 1080 |
# |
| 1081 |
# There is a regexp function if ICU is enabled though. |
| 1082 |
# |
| 1083 |
ifcapable !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 for |
| 1093 |
# the regexp() user function. |
| 1094 |
# |
| 1095 |
# EVIDENCE-OF: R-65524-61849 If an application-defined SQL function |
| 1096 |
# named "regexp" is added at run-time, then the "X REGEXP Y" operator |
| 1097 |
# will be implemented as a call to "regexp(Y,X)". |
| 1098 |
# |
| 1099 |
proc regexpfunc {args} { |
| 1100 |
eval lappend ::regexpargs $args |
| 1101 |
return 1 |
| 1102 |
} |
| 1103 |
db func regexp -argcount 2 regexpfunc |
| 1104 |
set ::regexpargs [list] |
| 1105 |
do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1 |
| 1106 |
do_test e_expr-18.2.2 { set regexpargs } {def abc} |
| 1107 |
set ::regexpargs [list] |
| 1108 |
do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0 |
| 1109 |
do_test e_expr-18.2.4 { set regexpargs } {Y X} |
| 1110 |
sqlite3 db test.db |
| 1111 |
|
| 1112 |
# EVIDENCE-OF: R-42037-37826 The default match() function implementation |
| 1113 |
# raises an exception and is not really useful for anything. |
| 1114 |
# |
| 1115 |
do_catchsql_test e_expr-19.1.1 { |
| 1116 |
SELECT 'abc' MATCH 'def' |
| 1117 |
} {1 {unable to use function MATCH in the requested context}} |
| 1118 |
do_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 for |
| 1123 |
# 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 |
# |
| 1128 |
proc matchfunc {args} { |
| 1129 |
eval lappend ::matchargs $args |
| 1130 |
return 1 |
| 1131 |
} |
| 1132 |
db func match -argcount 2 matchfunc |
| 1133 |
set ::matchargs [list] |
| 1134 |
do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1 |
| 1135 |
do_test e_expr-19.2.2 { set matchargs } {def abc} |
| 1136 |
set ::matchargs [list] |
| 1137 |
do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0 |
| 1138 |
do_test e_expr-19.2.4 { set matchargs } {Y X} |
| 1139 |
sqlite3 db test.db |
| 1140 |
|
| 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 CASE |
| 1145 |
# expression: those with a base expression and those without. |
| 1146 |
# |
| 1147 |
do_execsql_test e_expr-20.1 { |
| 1148 |
SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; |
| 1149 |
} {true} |
| 1150 |
do_execsql_test e_expr-20.2 { |
| 1151 |
SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END; |
| 1152 |
} {false} |
| 1153 |
|
| 1154 |
proc var {nm} { |
| 1155 |
lappend ::varlist $nm |
| 1156 |
return [set "::$nm"] |
| 1157 |
} |
| 1158 |
db func var var |
| 1159 |
|
| 1160 |
# EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each |
| 1161 |
# WHEN expression is evaluated and the result treated as a boolean, |
| 1162 |
# starting with the leftmost and continuing to the right. |
| 1163 |
# |
| 1164 |
foreach {a b c} {0 0 0} break |
| 1165 |
set varlist [list] |
| 1166 |
do_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' END |
| 1170 |
} {{}} |
| 1171 |
do_test e_expr-21.1.2 { set varlist } {a b c} |
| 1172 |
set varlist [list] |
| 1173 |
do_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 |
END |
| 1179 |
} {{no result}} |
| 1180 |
do_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 the |
| 1183 |
# evaluation of the THEN expression that corresponds to the first WHEN |
| 1184 |
# expression that evaluates to true. |
| 1185 |
# |
| 1186 |
foreach {a b c} {0 1 0} break |
| 1187 |
do_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 |
END |
| 1193 |
} {B} |
| 1194 |
foreach {a b c} {0 1 1} break |
| 1195 |
do_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 |
| 1201 |
} {B} |
| 1202 |
foreach {a b c} {0 0 1} break |
| 1203 |
do_execsql_test e_expr-21.2.3 { |
| 1204 |
SELECT CASE WHEN var('a') THEN 'A' |
| 1205 |
WHEN var('b') THEN 'B' |
| 1206 |
WHEN var('c') THEN 'C' |
| 1207 |
ELSE 'no result' |
| 1208 |
END |
| 1209 |
} {C} |
| 1210 |
|
| 1211 |
# EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions |
| 1212 |
# evaluate to true, the result of evaluating the ELSE expression, if |
| 1213 |
# any. |
| 1214 |
# |
| 1215 |
foreach {a b c} {0 0 0} break |
| 1216 |
do_execsql_test e_expr-21.3.1 { |
| 1217 |
SELECT CASE WHEN var('a') THEN 'A' |
| 1218 |
WHEN var('b') THEN 'B' |
| 1219 |
WHEN var('c') THEN 'C' |
| 1220 |
ELSE 'no result' |
| 1221 |
END |
| 1222 |
} {{no result}} |
| 1223 |
|
| 1224 |
# EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of |
| 1225 |
# the WHEN expressions are true, then the overall result is NULL. |
| 1226 |
# |
| 1227 |
db nullvalue null |
| 1228 |
do_execsql_test e_expr-21.3.2 { |
| 1229 |
SELECT CASE WHEN var('a') THEN 'A' |
| 1230 |
WHEN var('b') THEN 'B' |
| 1231 |
WHEN var('c') THEN 'C' |
| 1232 |
END |
| 1233 |
} {null} |
| 1234 |
db nullvalue {} |
| 1235 |
|
| 1236 |
# EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when |
| 1237 |
# evaluating WHEN terms. |
| 1238 |
# |
| 1239 |
do_execsql_test e_expr-21.4.1a { |
| 1240 |
SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END, iif(NULL,8,99); |
| 1241 |
} {B 99} |
| 1242 |
do_execsql_test e_expr-21.4.1b { |
| 1243 |
SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END, if(NULL,8,99); |
| 1244 |
} {B 99} |
| 1245 |
do_execsql_test e_expr-21.4.2a { |
| 1246 |
SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END, iif(0,8,99); |
| 1247 |
} {C 99} |
| 1248 |
do_execsql_test e_expr-21.4.2b { |
| 1249 |
SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END, if(0,8,99); |
| 1250 |
} {C 99} |
| 1251 |
|
| 1252 |
# EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base |
| 1253 |
# expression is evaluated just once and the result is compared against |
| 1254 |
# the evaluation of each WHEN expression from left to right. |
| 1255 |
# |
| 1256 |
# Note: This test case tests the "evaluated just once" part of the above |
| 1257 |
# statement. Tests associated with the next two statements test that the |
| 1258 |
# comparisons take place. |
| 1259 |
# |
| 1260 |
foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break |
| 1261 |
set ::varlist [list] |
| 1262 |
do_execsql_test e_expr-22.1.1 { |
| 1263 |
SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END |
| 1264 |
} {C} |
| 1265 |
do_test e_expr-22.1.2 { set ::varlist } {a} |
| 1266 |
|
| 1267 |
# EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the |
| 1268 |
# evaluation of the THEN expression that corresponds to the first WHEN |
| 1269 |
# expression for which the comparison is true. |
| 1270 |
# |
| 1271 |
do_execsql_test e_expr-22.2.1 { |
| 1272 |
SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END |
| 1273 |
} {B} |
| 1274 |
do_execsql_test e_expr-22.2.2 { |
| 1275 |
SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END |
| 1276 |
} {A} |
| 1277 |
|
| 1278 |
# EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions |
| 1279 |
# evaluate to a value equal to the base expression, the result of |
| 1280 |
# evaluating the ELSE expression, if any. |
| 1281 |
# |
| 1282 |
do_execsql_test e_expr-22.3.1 { |
| 1283 |
SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END |
| 1284 |
} {D} |
| 1285 |
|
| 1286 |
# EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of |
| 1287 |
# the WHEN expressions produce a result equal to the base expression, |
| 1288 |
# the overall result is NULL. |
| 1289 |
# |
| 1290 |
do_execsql_test e_expr-22.4.1 { |
| 1291 |
SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END |
| 1292 |
} {{}} |
| 1293 |
db nullvalue null |
| 1294 |
do_execsql_test e_expr-22.4.2 { |
| 1295 |
SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END |
| 1296 |
} {null} |
| 1297 |
db nullvalue {} |
| 1298 |
|
| 1299 |
# EVIDENCE-OF: R-11479-62774 When comparing a base expression against a |
| 1300 |
# WHEN expression, the same collating sequence, affinity, and |
| 1301 |
# NULL-handling rules apply as if the base expression and WHEN |
| 1302 |
# expression are respectively the left- and right-hand operands of an = |
| 1303 |
# operator. |
| 1304 |
# |
| 1305 |
proc rev {str} { |
| 1306 |
set ret "" |
| 1307 |
set chars [split $str] |
| 1308 |
for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} { |
| 1309 |
append ret [lindex $chars $i] |
| 1310 |
} |
| 1311 |
set ret |
| 1312 |
} |
| 1313 |
proc reverse {lhs rhs} { |
| 1314 |
string compare [rev $lhs] [rev $rhs] |
| 1315 |
} |
| 1316 |
db collate reverse reverse |
| 1317 |
do_execsql_test e_expr-23.1.1 { |
| 1318 |
CREATE TABLE t1( |
| 1319 |
a TEXT COLLATE NOCASE, |
| 1320 |
b COLLATE REVERSE, |
| 1321 |
c INTEGER, |
| 1322 |
d BLOB |
| 1323 |
); |
| 1324 |
INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5); |
| 1325 |
} {} |
| 1326 |
do_execsql_test e_expr-23.1.2 { |
| 1327 |
SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1 |
| 1328 |
} {B} |
| 1329 |
do_execsql_test e_expr-23.1.3 { |
| 1330 |
SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1 |
| 1331 |
} {B} |
| 1332 |
do_execsql_test e_expr-23.1.4 { |
| 1333 |
SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1 |
| 1334 |
} {B} |
| 1335 |
do_execsql_test e_expr-23.1.5 { |
| 1336 |
SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1 |
| 1337 |
} {B} |
| 1338 |
do_execsql_test e_expr-23.1.6 { |
| 1339 |
SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END |
| 1340 |
} {B} |
| 1341 |
do_execsql_test e_expr-23.1.7 { |
| 1342 |
SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1 |
| 1343 |
} {A} |
| 1344 |
do_execsql_test e_expr-23.1.8 { |
| 1345 |
SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1 |
| 1346 |
} {B} |
| 1347 |
do_execsql_test e_expr-23.1.9 { |
| 1348 |
SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END |
| 1349 |
} {B} |
| 1350 |
|
| 1351 |
# EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the |
| 1352 |
# result of the CASE is always the result of evaluating the ELSE |
| 1353 |
# expression if it exists, or NULL if it does not. |
| 1354 |
# |
| 1355 |
do_execsql_test e_expr-24.1.1 { |
| 1356 |
SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END; |
| 1357 |
} {{}} |
| 1358 |
do_execsql_test e_expr-24.1.2 { |
| 1359 |
SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END; |
| 1360 |
} {C} |
| 1361 |
|
| 1362 |
# EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy, |
| 1363 |
# or short-circuit, evaluation. |
| 1364 |
# |
| 1365 |
set varlist [list] |
| 1366 |
foreach {a b c} {0 1 0} break |
| 1367 |
do_execsql_test e_expr-25.1.1 { |
| 1368 |
SELECT CASE WHEN var('a') THEN 'A' |
| 1369 |
WHEN var('b') THEN 'B' |
| 1370 |
WHEN var('c') THEN 'C' |
| 1371 |
END |
| 1372 |
} {B} |
| 1373 |
do_test e_expr-25.1.2 { set ::varlist } {a b} |
| 1374 |
set varlist [list] |
| 1375 |
do_execsql_test e_expr-25.1.3 { |
| 1376 |
SELECT CASE '0' WHEN var('a') THEN 'A' |
| 1377 |
WHEN var('b') THEN 'B' |
| 1378 |
WHEN var('c') THEN 'C' |
| 1379 |
END |
| 1380 |
} {A} |
| 1381 |
do_test e_expr-25.1.4 { set ::varlist } {a} |
| 1382 |
|
| 1383 |
# EVIDENCE-OF: R-34773-62253 The only difference between the following |
| 1384 |
# two CASE expressions is that the x expression is evaluated exactly |
| 1385 |
# once in the first example but might be evaluated multiple times in the |
| 1386 |
# second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN |
| 1387 |
# x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END |
| 1388 |
# |
| 1389 |
proc ceval {x} { |
| 1390 |
incr ::evalcount |
| 1391 |
return $x |
| 1392 |
} |
| 1393 |
db func ceval ceval |
| 1394 |
set ::evalcount 0 |
| 1395 |
|
| 1396 |
do_execsql_test e_expr-26.1.1 { |
| 1397 |
CREATE TABLE t2(x, w1, r1, w2, r2, r3); |
| 1398 |
INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3'); |
| 1399 |
INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3'); |
| 1400 |
INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3'); |
| 1401 |
} {} |
| 1402 |
do_execsql_test e_expr-26.1.2 { |
| 1403 |
SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 |
| 1404 |
} {R1 R2 R3} |
| 1405 |
do_execsql_test e_expr-26.1.3 { |
| 1406 |
SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2 |
| 1407 |
} {R1 R2 R3} |
| 1408 |
|
| 1409 |
do_execsql_test e_expr-26.1.4 { |
| 1410 |
SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2 |
| 1411 |
} {R1 R2 R3} |
| 1412 |
do_test e_expr-26.1.5 { set ::evalcount } {3} |
| 1413 |
set ::evalcount 0 |
| 1414 |
do_execsql_test e_expr-26.1.6 { |
| 1415 |
SELECT CASE |
| 1416 |
WHEN ceval(x)=w1 THEN r1 |
| 1417 |
WHEN ceval(x)=w2 THEN r2 |
| 1418 |
ELSE r3 END |
| 1419 |
FROM t2 |
| 1420 |
} {R1 R2 R3} |
| 1421 |
do_test e_expr-26.1.6 { set ::evalcount } {5} |
| 1422 |
|
| 1423 |
|
| 1424 |
#------------------------------------------------------------------------- |
| 1425 |
# Test statements related to CAST expressions. |
| 1426 |
# |
| 1427 |
# EVIDENCE-OF: R-20854-17109 A CAST conversion is similar to the |
| 1428 |
# conversion that takes place when a column affinity is applied to a |
| 1429 |
# value except that with the CAST operator the conversion always takes |
| 1430 |
# place even if the conversion lossy and irreversible, whereas column |
| 1431 |
# affinity only changes the data type of a value if the change is |
| 1432 |
# lossless and reversible. |
| 1433 |
# |
| 1434 |
do_execsql_test e_expr-27.1.1 { |
| 1435 |
CREATE TABLE t3(a TEXT, b REAL, c INTEGER); |
| 1436 |
INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5); |
| 1437 |
SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3; |
| 1438 |
} {blob UVU text 1.23abc real 4.5} |
| 1439 |
do_execsql_test e_expr-27.1.2 { |
| 1440 |
SELECT |
| 1441 |
typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT), |
| 1442 |
typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL), |
| 1443 |
typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER) |
| 1444 |
} {text UVU real 1.23 integer 4} |
| 1445 |
|
| 1446 |
# EVIDENCE-OF: R-32434-09092 If the value of expr is NULL, then the |
| 1447 |
# result of the CAST expression is also NULL. |
| 1448 |
# |
| 1449 |
do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {} |
| 1450 |
do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {} |
| 1451 |
do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {} |
| 1452 |
do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {} |
| 1453 |
|
| 1454 |
# EVIDENCE-OF: R-29283-15561 Otherwise, the storage class of the result |
| 1455 |
# is determined by applying the rules for determining column affinity to |
| 1456 |
# the type-name. |
| 1457 |
# |
| 1458 |
# The R-29283-15561 requirement above is demonstrated by all of the |
| 1459 |
# subsequent e_expr-26 tests. |
| 1460 |
# |
| 1461 |
# EVIDENCE-OF: R-43522-35548 Casting a value to a type-name with no |
| 1462 |
# affinity causes the value to be converted into a BLOB. |
| 1463 |
# |
| 1464 |
do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc |
| 1465 |
do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def |
| 1466 |
do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi |
| 1467 |
|
| 1468 |
# EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting |
| 1469 |
# the value to TEXT in the encoding of the database connection, then |
| 1470 |
# interpreting the resulting byte sequence as a BLOB instead of as TEXT. |
| 1471 |
# |
| 1472 |
do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869' |
| 1473 |
do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536' |
| 1474 |
do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738' |
| 1475 |
rename db db2 |
| 1476 |
sqlite3 db :memory: |
| 1477 |
ifcapable {utf16} { |
| 1478 |
db eval { PRAGMA encoding = 'utf-16le' } |
| 1479 |
do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900' |
| 1480 |
do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600' |
| 1481 |
do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800' |
| 1482 |
} |
| 1483 |
db close |
| 1484 |
sqlite3 db :memory: |
| 1485 |
db eval { PRAGMA encoding = 'utf-16be' } |
| 1486 |
ifcapable {utf16} { |
| 1487 |
do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069' |
| 1488 |
do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036' |
| 1489 |
do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038' |
| 1490 |
} |
| 1491 |
db close |
| 1492 |
rename db2 db |
| 1493 |
|
| 1494 |
# EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence |
| 1495 |
# of bytes that make up the BLOB is interpreted as text encoded using |
| 1496 |
# the database encoding. |
| 1497 |
# |
| 1498 |
do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi |
| 1499 |
do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g |
| 1500 |
rename db db2 |
| 1501 |
sqlite3 db :memory: |
| 1502 |
db eval { PRAGMA encoding = 'utf-16le' } |
| 1503 |
ifcapable {utf16} { |
| 1504 |
do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0 |
| 1505 |
do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi |
| 1506 |
} |
| 1507 |
db close |
| 1508 |
rename db2 db |
| 1509 |
|
| 1510 |
# EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT |
| 1511 |
# renders the value as if via sqlite3_snprintf() except that the |
| 1512 |
# resulting TEXT uses the encoding of the database connection. |
| 1513 |
# |
| 1514 |
do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1 |
| 1515 |
do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45 |
| 1516 |
do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45 |
| 1517 |
do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8 |
| 1518 |
do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0 |
| 1519 |
do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05 |
| 1520 |
do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0 |
| 1521 |
do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0 |
| 1522 |
|
| 1523 |
# EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the |
| 1524 |
# value is first converted to TEXT. |
| 1525 |
# |
| 1526 |
do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23 |
| 1527 |
do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0 |
| 1528 |
do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87 |
| 1529 |
do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001 |
| 1530 |
rename db db2 |
| 1531 |
sqlite3 db :memory: |
| 1532 |
ifcapable {utf16} { |
| 1533 |
db eval { PRAGMA encoding = 'utf-16le' } |
| 1534 |
do_expr_test e_expr-29.1.5 { |
| 1535 |
CAST (X'31002E0032003300' AS REAL) } real 1.23 |
| 1536 |
do_expr_test e_expr-29.1.6 { |
| 1537 |
CAST (X'3200330030002E003000' AS REAL) } real 230.0 |
| 1538 |
do_expr_test e_expr-29.1.7 { |
| 1539 |
CAST (X'2D0039002E0038003700' AS REAL) } real -9.87 |
| 1540 |
do_expr_test e_expr-29.1.8 { |
| 1541 |
CAST (X'30002E003000300030003100' AS REAL) } real 0.0001 |
| 1542 |
} |
| 1543 |
db close |
| 1544 |
rename db2 db |
| 1545 |
|
| 1546 |
# EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the |
| 1547 |
# longest possible prefix of the value that can be interpreted as a real |
| 1548 |
# number is extracted from the TEXT value and the remainder ignored. |
| 1549 |
# |
| 1550 |
do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23 |
| 1551 |
do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45 |
| 1552 |
do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212 |
| 1553 |
do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0 |
| 1554 |
|
| 1555 |
# EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are |
| 1556 |
# ignored when converging from TEXT to REAL. |
| 1557 |
# |
| 1558 |
do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23 |
| 1559 |
do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45 |
| 1560 |
do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212 |
| 1561 |
do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0 |
| 1562 |
|
| 1563 |
# EVIDENCE-OF: R-22662-28218 If there is no prefix that can be |
| 1564 |
# interpreted as a real number, the result of the conversion is 0.0. |
| 1565 |
# |
| 1566 |
do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0 |
| 1567 |
do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0 |
| 1568 |
do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0 |
| 1569 |
|
| 1570 |
# EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the |
| 1571 |
# value is first converted to TEXT. |
| 1572 |
# |
| 1573 |
do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123 |
| 1574 |
do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678 |
| 1575 |
do_expr_test e_expr-30.1.3 { |
| 1576 |
CAST(X'31303030303030' AS INTEGER) |
| 1577 |
} integer 1000000 |
| 1578 |
do_expr_test e_expr-30.1.4 { |
| 1579 |
CAST(X'2D31313235383939393036383432363234' AS INTEGER) |
| 1580 |
} integer -1125899906842624 |
| 1581 |
|
| 1582 |
rename db db2 |
| 1583 |
sqlite3 db :memory: |
| 1584 |
ifcapable {utf16} { |
| 1585 |
execsql { PRAGMA encoding = 'utf-16be' } |
| 1586 |
do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123 |
| 1587 |
do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678 |
| 1588 |
do_expr_test e_expr-30.1.7 { |
| 1589 |
CAST(X'0031003000300030003000300030' AS INTEGER) |
| 1590 |
} integer 1000000 |
| 1591 |
do_expr_test e_expr-30.1.8 { |
| 1592 |
CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER) |
| 1593 |
} integer -1125899906842624 |
| 1594 |
} |
| 1595 |
db close |
| 1596 |
rename db2 db |
| 1597 |
|
| 1598 |
# EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the |
| 1599 |
# longest possible prefix of the value that can be interpreted as an |
| 1600 |
# integer number is extracted from the TEXT value and the remainder |
| 1601 |
# ignored. |
| 1602 |
# |
| 1603 |
do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123 |
| 1604 |
do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523 |
| 1605 |
do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2 |
| 1606 |
do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1 |
| 1607 |
|
| 1608 |
# EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when |
| 1609 |
# converting from TEXT to INTEGER are ignored. |
| 1610 |
# |
| 1611 |
do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123 |
| 1612 |
do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523 |
| 1613 |
do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2 |
| 1614 |
do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1 |
| 1615 |
|
| 1616 |
# EVIDENCE-OF: R-43164-44276 If there is no prefix that can be |
| 1617 |
# interpreted as an integer number, the result of the conversion is 0. |
| 1618 |
# |
| 1619 |
do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0 |
| 1620 |
do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0 |
| 1621 |
do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0 |
| 1622 |
|
| 1623 |
# EVIDENCE-OF: R-08980-53124 The CAST operator understands decimal |
| 1624 |
# integers only — conversion of hexadecimal integers stops at |
| 1625 |
# the "x" in the "0x" prefix of the hexadecimal integer string and thus |
| 1626 |
# result of the CAST is always zero. |
| 1627 |
do_expr_test e_expr-30.5.1 { CAST('0x1234' AS INTEGER) } integer 0 |
| 1628 |
do_expr_test e_expr-30.5.2 { CAST('0X1234' AS INTEGER) } integer 0 |
| 1629 |
|
| 1630 |
# EVIDENCE-OF: R-02752-50091 A cast of a REAL value into an INTEGER |
| 1631 |
# results in the integer between the REAL value and zero that is closest |
| 1632 |
# to the REAL value. |
| 1633 |
# |
| 1634 |
do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3 |
| 1635 |
do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1 |
| 1636 |
do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1 |
| 1637 |
do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0 |
| 1638 |
|
| 1639 |
# EVIDENCE-OF: R-51517-40824 If a REAL is greater than the greatest |
| 1640 |
# possible signed integer (+9223372036854775807) then the result is the |
| 1641 |
# greatest possible signed integer and if the REAL is less than the |
| 1642 |
# least possible signed integer (-9223372036854775808) then the result |
| 1643 |
# is the least possible signed integer. |
| 1644 |
# |
| 1645 |
do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer 9223372036854775807 |
| 1646 |
do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808 |
| 1647 |
do_expr_test e_expr-31.2.3 { |
| 1648 |
CAST(-9223372036854775809.0 AS INT) |
| 1649 |
} integer -9223372036854775808 |
| 1650 |
do_expr_test e_expr-31.2.4 { |
| 1651 |
CAST(9223372036854775809.0 AS INT) |
| 1652 |
} integer 9223372036854775807 |
| 1653 |
|
| 1654 |
|
| 1655 |
# EVIDENCE-OF: R-55084-10555 Casting a TEXT or BLOB value into NUMERIC |
| 1656 |
# yields either an INTEGER or a REAL result. |
| 1657 |
# |
| 1658 |
# EVIDENCE-OF: R-48945-04866 If the input text looks like an integer |
| 1659 |
# (there is no decimal point nor exponent) and the value is small enough |
| 1660 |
# to fit in a 64-bit signed integer, then the result will be INTEGER. |
| 1661 |
# |
| 1662 |
# EVIDENCE-OF: R-47045-23194 Input text that looks like floating point |
| 1663 |
# (there is a decimal point and/or an exponent) and the text describes a |
| 1664 |
# value that can be losslessly converted back and forth between IEEE 754 |
| 1665 |
# 64-bit float and a 51-bit signed integer, then the result is INTEGER. |
| 1666 |
# |
| 1667 |
do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45 |
| 1668 |
do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45 |
| 1669 |
do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2 |
| 1670 |
do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11 |
| 1671 |
do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1 |
| 1672 |
do_expr_test e_expr-32.1.6 {CAST( '9.223372036e14' AS NUMERIC)} integer 922337203600000 |
| 1673 |
do_expr_test e_expr-32.1.7 {CAST('-9.223372036e14' AS NUMERIC)} integer -922337203600000 |
| 1674 |
do_test e_expr-32.1.8 { |
| 1675 |
set expr {CAST( '9.223372036e15' AS NUMERIC)} |
| 1676 |
db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break; |
| 1677 |
list $type $value |
| 1678 |
} {real 9.22337e+15} |
| 1679 |
do_test e_expr-32.1.9 { |
| 1680 |
set expr {CAST('-9.223372036e15' AS NUMERIC)} |
| 1681 |
db eval "SELECT typeof($expr) AS type, printf('%.5e',$expr) AS value" break; |
| 1682 |
list $type $value |
| 1683 |
} {real -9.22337e+15} |
| 1684 |
|
| 1685 |
# EVIDENCE-OF: R-50300-26941 Any text input that describes a value |
| 1686 |
# outside the range of a 64-bit signed integer yields a REAL result. |
| 1687 |
# |
| 1688 |
do_expr_test e_expr-32.1.20 { CAST('9223372036854775807' AS numeric) } \ |
| 1689 |
integer 9223372036854775807 |
| 1690 |
do_expr_test e_expr-32.1.21 { CAST('9223372036854775808' AS numeric) } \ |
| 1691 |
real 9.22337203685478e+18 |
| 1692 |
do_expr_test e_expr-32.1.22 { CAST('-9223372036854775808' AS numeric) } \ |
| 1693 |
integer -9223372036854775808 |
| 1694 |
do_expr_test e_expr-32.1.23 { CAST('-9223372036854775809' AS numeric) } \ |
| 1695 |
real -9.22337203685478e+18 |
| 1696 |
|
| 1697 |
# EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC |
| 1698 |
# is a no-op, even if a real value could be losslessly converted to an |
| 1699 |
# integer. |
| 1700 |
# |
| 1701 |
do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0 |
| 1702 |
do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5 |
| 1703 |
|
| 1704 |
do_expr_test e_expr-32.2.3 { |
| 1705 |
CAST(-9223372036854775808 AS NUMERIC) |
| 1706 |
} integer -9223372036854775808 |
| 1707 |
do_expr_test e_expr-32.2.4 { |
| 1708 |
CAST(9223372036854775807 AS NUMERIC) |
| 1709 |
} integer 9223372036854775807 |
| 1710 |
do_expr_test e_expr-32.2.5 { |
| 1711 |
CAST('9223372036854775807 ' AS NUMERIC) |
| 1712 |
} integer 9223372036854775807 |
| 1713 |
do_expr_test e_expr-32.2.6 { |
| 1714 |
CAST(' 9223372036854775807 ' AS NUMERIC) |
| 1715 |
} integer 9223372036854775807 |
| 1716 |
do_expr_test e_expr-32.2.7 { |
| 1717 |
CAST(' ' AS NUMERIC) |
| 1718 |
} integer 0 |
| 1719 |
do_execsql_test e_expr-32.2.8 { |
| 1720 |
WITH t1(x) AS (VALUES |
| 1721 |
('9000000000000000001'), |
| 1722 |
('9000000000000000001x'), |
| 1723 |
('9000000000000000001 '), |
| 1724 |
(' 9000000000000000001 '), |
| 1725 |
(' 9000000000000000001'), |
| 1726 |
(' 9000000000000000001.'), |
| 1727 |
('9223372036854775807'), |
| 1728 |
('9223372036854775807 '), |
| 1729 |
(' 9223372036854775807 '), |
| 1730 |
('9223372036854775808'), |
| 1731 |
(' 9223372036854775808 '), |
| 1732 |
('9223372036854775807.0'), |
| 1733 |
('9223372036854775807e+0'), |
| 1734 |
('-5.0'), |
| 1735 |
('-5e+0')) |
| 1736 |
SELECT typeof(CAST(x AS NUMERIC)), CAST(x AS NUMERIC)||'' FROM t1; |
| 1737 |
} [list \ |
| 1738 |
integer 9000000000000000001 \ |
| 1739 |
integer 9000000000000000001 \ |
| 1740 |
integer 9000000000000000001 \ |
| 1741 |
integer 9000000000000000001 \ |
| 1742 |
integer 9000000000000000001 \ |
| 1743 |
real 9.0e+18 \ |
| 1744 |
integer 9223372036854775807 \ |
| 1745 |
integer 9223372036854775807 \ |
| 1746 |
integer 9223372036854775807 \ |
| 1747 |
real 9.2233720368547758e+18 \ |
| 1748 |
real 9.2233720368547758e+18 \ |
| 1749 |
real 9.2233720368547758e+18 \ |
| 1750 |
real 9.2233720368547758e+18 \ |
| 1751 |
integer -5 \ |
| 1752 |
integer -5 \ |
| 1753 |
] |
| 1754 |
|
| 1755 |
# EVIDENCE-OF: R-64550-29191 Note that the result from casting any |
| 1756 |
# non-BLOB value into a BLOB and the result from casting any BLOB value |
| 1757 |
# into a non-BLOB value may be different depending on whether the |
| 1758 |
# database encoding is UTF-8, UTF-16be, or UTF-16le. |
| 1759 |
# |
| 1760 |
ifcapable {utf16} { |
| 1761 |
sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' } |
| 1762 |
sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' } |
| 1763 |
sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' } |
| 1764 |
foreach {tn castexpr differs} { |
| 1765 |
1 { CAST(123 AS BLOB) } 1 |
| 1766 |
2 { CAST('' AS BLOB) } 0 |
| 1767 |
3 { CAST('abcd' AS BLOB) } 1 |
| 1768 |
|
| 1769 |
4 { CAST(X'abcd' AS TEXT) } 1 |
| 1770 |
5 { CAST(X'' AS TEXT) } 0 |
| 1771 |
} { |
| 1772 |
set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"] |
| 1773 |
set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"] |
| 1774 |
set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"] |
| 1775 |
|
| 1776 |
if {$differs} { |
| 1777 |
set res [expr {$r1!=$r2 && $r2!=$r3}] |
| 1778 |
} else { |
| 1779 |
set res [expr {$r1==$r2 && $r2==$r3}] |
| 1780 |
} |
| 1781 |
|
| 1782 |
do_test e_expr-33.1.$tn {set res} 1 |
| 1783 |
} |
| 1784 |
db1 close |
| 1785 |
db2 close |
| 1786 |
db3 close |
| 1787 |
} |
| 1788 |
|
| 1789 |
#------------------------------------------------------------------------- |
| 1790 |
# Test statements related to the EXISTS and NOT EXISTS operators. |
| 1791 |
# |
| 1792 |
catch { db close } |
| 1793 |
forcedelete test.db |
| 1794 |
sqlite3 db test.db |
| 1795 |
|
| 1796 |
do_execsql_test e_expr-34.1 { |
| 1797 |
CREATE TABLE t1(a, b); |
| 1798 |
INSERT INTO t1 VALUES(1, 2); |
| 1799 |
INSERT INTO t1 VALUES(NULL, 2); |
| 1800 |
INSERT INTO t1 VALUES(1, NULL); |
| 1801 |
INSERT INTO t1 VALUES(NULL, NULL); |
| 1802 |
} {} |
| 1803 |
|
| 1804 |
# EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one |
| 1805 |
# of the integer values 0 and 1. |
| 1806 |
# |
| 1807 |
# This statement is not tested by itself. Instead, all e_expr-34.* tests |
| 1808 |
# following this point explicitly test that specific invocations of EXISTS |
| 1809 |
# return either integer 0 or integer 1. |
| 1810 |
# |
| 1811 |
|
| 1812 |
# EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified |
| 1813 |
# as the right-hand operand of the EXISTS operator would return one or |
| 1814 |
# more rows, then the EXISTS operator evaluates to 1. |
| 1815 |
# |
| 1816 |
foreach {tn expr} { |
| 1817 |
1 { EXISTS ( SELECT a FROM t1 ) } |
| 1818 |
2 { EXISTS ( SELECT b FROM t1 ) } |
| 1819 |
3 { EXISTS ( SELECT 24 ) } |
| 1820 |
4 { EXISTS ( SELECT NULL ) } |
| 1821 |
5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) } |
| 1822 |
} { |
| 1823 |
do_expr_test e_expr-34.2.$tn $expr integer 1 |
| 1824 |
} |
| 1825 |
|
| 1826 |
# EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no |
| 1827 |
# rows at all, then the EXISTS operator evaluates to 0. |
| 1828 |
# |
| 1829 |
foreach {tn expr} { |
| 1830 |
1 { EXISTS ( SELECT a FROM t1 WHERE 0) } |
| 1831 |
2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) } |
| 1832 |
3 { EXISTS ( SELECT 24 WHERE 0) } |
| 1833 |
4 { EXISTS ( SELECT NULL WHERE 1=2) } |
| 1834 |
} { |
| 1835 |
do_expr_test e_expr-34.3.$tn $expr integer 0 |
| 1836 |
} |
| 1837 |
|
| 1838 |
# EVIDENCE-OF: R-35109-49139 The number of columns in each row returned |
| 1839 |
# by the SELECT statement (if any) and the specific values returned have |
| 1840 |
# no effect on the results of the EXISTS operator. |
| 1841 |
# |
| 1842 |
foreach {tn expr res} { |
| 1843 |
1 { EXISTS ( SELECT * FROM t1 ) } 1 |
| 1844 |
2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1 |
| 1845 |
3 { EXISTS ( SELECT 24, 25 ) } 1 |
| 1846 |
4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1 |
| 1847 |
5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1 |
| 1848 |
|
| 1849 |
6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0 |
| 1850 |
7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0 |
| 1851 |
8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0 |
| 1852 |
9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0 |
| 1853 |
} { |
| 1854 |
do_expr_test e_expr-34.4.$tn $expr integer $res |
| 1855 |
} |
| 1856 |
|
| 1857 |
# EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values |
| 1858 |
# are not handled any differently from rows without NULL values. |
| 1859 |
# |
| 1860 |
foreach {tn e1 e2} { |
| 1861 |
1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) } |
| 1862 |
2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) } |
| 1863 |
} { |
| 1864 |
set res [db one "SELECT $e1"] |
| 1865 |
do_expr_test e_expr-34.5.${tn}a $e1 integer $res |
| 1866 |
do_expr_test e_expr-34.5.${tn}b $e2 integer $res |
| 1867 |
} |
| 1868 |
|
| 1869 |
#------------------------------------------------------------------------- |
| 1870 |
# Test statements related to scalar sub-queries. |
| 1871 |
# |
| 1872 |
|
| 1873 |
catch { db close } |
| 1874 |
forcedelete test.db |
| 1875 |
sqlite3 db test.db |
| 1876 |
do_test e_expr-35.0 { |
| 1877 |
execsql { |
| 1878 |
CREATE TABLE t2(a, b); |
| 1879 |
INSERT INTO t2 VALUES('one', 'two'); |
| 1880 |
INSERT INTO t2 VALUES('three', NULL); |
| 1881 |
INSERT INTO t2 VALUES(4, 5.0); |
| 1882 |
} |
| 1883 |
} {} |
| 1884 |
|
| 1885 |
# EVIDENCE-OF: R-43573-23448 A SELECT statement enclosed in parentheses |
| 1886 |
# is a subquery. |
| 1887 |
# |
| 1888 |
# EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including |
| 1889 |
# aggregate and compound SELECT queries (queries with keywords like |
| 1890 |
# UNION or EXCEPT) are allowed as scalar subqueries. |
| 1891 |
# |
| 1892 |
do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35 |
| 1893 |
do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {} |
| 1894 |
|
| 1895 |
do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3 |
| 1896 |
do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4 |
| 1897 |
|
| 1898 |
do_expr_test e_expr-35.1.5 { |
| 1899 |
(SELECT b FROM t2 UNION SELECT a+1 FROM t2) |
| 1900 |
} null {} |
| 1901 |
do_expr_test e_expr-35.1.6 { |
| 1902 |
(SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1) |
| 1903 |
} integer 4 |
| 1904 |
|
| 1905 |
# EVIDENCE-OF: R-43101-20178 A subquery that returns two or more columns |
| 1906 |
# is a row value subquery and can only be used as an operand of a |
| 1907 |
# comparison operator or as the value in an UPDATE SET clause whose |
| 1908 |
# column name list has the same size. |
| 1909 |
# |
| 1910 |
# The following block tests that errors are returned in a bunch of cases |
| 1911 |
# where a subquery returns more than one column. |
| 1912 |
# |
| 1913 |
set M {/1 {sub-select returns [23] columns - expected 1}/} |
| 1914 |
foreach {tn sql} { |
| 1915 |
1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) } |
| 1916 |
2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) } |
| 1917 |
3 { SELECT (SELECT 1, 2) } |
| 1918 |
4 { SELECT (SELECT NULL, NULL, NULL) } |
| 1919 |
5 { SELECT (SELECT * FROM t2) } |
| 1920 |
6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) } |
| 1921 |
} { |
| 1922 |
do_catchsql_test e_expr-35.2.$tn $sql $M |
| 1923 |
} |
| 1924 |
|
| 1925 |
# EVIDENCE-OF: R-18318-14995 The value of a subquery expression is the |
| 1926 |
# first row of the result from the enclosed SELECT statement. |
| 1927 |
# |
| 1928 |
do_execsql_test e_expr-36.3.1 { |
| 1929 |
CREATE TABLE t4(x, y); |
| 1930 |
INSERT INTO t4 VALUES(1, 'one'); |
| 1931 |
INSERT INTO t4 VALUES(2, 'two'); |
| 1932 |
INSERT INTO t4 VALUES(3, 'three'); |
| 1933 |
} {} |
| 1934 |
|
| 1935 |
foreach {tn expr restype resval} { |
| 1936 |
2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1 |
| 1937 |
3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1 |
| 1938 |
4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3 |
| 1939 |
5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2 |
| 1940 |
6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two |
| 1941 |
|
| 1942 |
7 { ( SELECT sum(x) FROM t4 ) } integer 6 |
| 1943 |
8 { ( SELECT string_agg(y,'') FROM t4 ) } text onetwothree |
| 1944 |
9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 |
| 1945 |
|
| 1946 |
} { |
| 1947 |
do_expr_test e_expr-36.3.$tn $expr $restype $resval |
| 1948 |
} |
| 1949 |
|
| 1950 |
# EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL |
| 1951 |
# if the enclosed SELECT statement returns no rows. |
| 1952 |
# |
| 1953 |
foreach {tn expr} { |
| 1954 |
1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) } |
| 1955 |
2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) } |
| 1956 |
} { |
| 1957 |
do_expr_test e_expr-36.4.$tn $expr null {} |
| 1958 |
} |
| 1959 |
|
| 1960 |
# EVIDENCE-OF: R-62477-06476 For example, the values NULL, 0.0, 0, |
| 1961 |
# 'english' and '0' are all considered to be false. |
| 1962 |
# |
| 1963 |
do_execsql_test e_expr-37.1 { |
| 1964 |
SELECT CASE WHEN NULL THEN 'true' ELSE 'false' END, iif(NULL,'true','false'); |
| 1965 |
} {false false} |
| 1966 |
do_execsql_test e_expr-37.2 { |
| 1967 |
SELECT CASE WHEN 0.0 THEN 'true' ELSE 'false' END, iif(0.0,'true','false'); |
| 1968 |
} {false false} |
| 1969 |
do_execsql_test e_expr-37.3 { |
| 1970 |
SELECT CASE WHEN 0 THEN 'true' ELSE 'false' END, iif(0,'true','false'); |
| 1971 |
} {false false} |
| 1972 |
do_execsql_test e_expr-37.4 { |
| 1973 |
SELECT CASE WHEN 'engligh' THEN 'true' ELSE 'false' END, iif('engligh','true','false'); |
| 1974 |
} {false false} |
| 1975 |
do_execsql_test e_expr-37.5 { |
| 1976 |
SELECT CASE WHEN '0' THEN 'true' ELSE 'false' END, iif('0','true','false'); |
| 1977 |
} {false false} |
| 1978 |
|
| 1979 |
# EVIDENCE-OF: R-55532-10108 Values 1, 1.0, 0.1, -0.1 and '1english' are |
| 1980 |
# considered to be true. |
| 1981 |
# |
| 1982 |
do_execsql_test e_expr-37.6a { |
| 1983 |
SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END, iif(1,'true','false'); |
| 1984 |
} {true true} |
| 1985 |
do_execsql_test e_expr-37.6b { |
| 1986 |
SELECT CASE WHEN 1 THEN 'true' ELSE 'false' END, if(1,'true'); |
| 1987 |
} {true true} |
| 1988 |
do_execsql_test e_expr-37.7 { |
| 1989 |
SELECT CASE WHEN 1.0 THEN 'true' ELSE 'false' END, iif(1.0,'true','false'); |
| 1990 |
} {true true} |
| 1991 |
do_execsql_test e_expr-37.8 { |
| 1992 |
SELECT CASE WHEN 0.1 THEN 'true' ELSE 'false' END, iif(0.1,'true','false'); |
| 1993 |
} {true true} |
| 1994 |
do_execsql_test e_expr-37.9 { |
| 1995 |
SELECT CASE WHEN -0.1 THEN 'true' ELSE 'false' END, iif(-0.1,'true','false'); |
| 1996 |
} {true true} |
| 1997 |
do_execsql_test e_expr-37.10 { |
| 1998 |
SELECT CASE WHEN '1english' THEN 'true' ELSE 'false' END, iif('1engl','true','false'); |
| 1999 |
} {true true} |
| 2000 |
|
| 2001 |
|
| 2002 |
finish_test |