Annotation of embedaddon/sqlite3/test/e_expr.test, revision 1.1
1.1 ! misho 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:
! 21: proc do_expr_test {tn expr type value} {
! 22: uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [
! 23: list [list $type $value]
! 24: ]
! 25: }
! 26:
! 27: proc do_qexpr_test {tn expr value} {
! 28: uplevel do_execsql_test $tn [list "SELECT quote($expr)"] [list $value]
! 29: }
! 30:
! 31: # Set up three global variables:
! 32: #
! 33: # ::opname An array mapping from SQL operator to an easy to parse
! 34: # name. The names are used as part of test case names.
! 35: #
! 36: # ::opprec An array mapping from SQL operator to a numeric
! 37: # precedence value. Operators that group more tightly
! 38: # have lower numeric precedences.
! 39: #
! 40: # ::oplist A list of all SQL operators supported by SQLite.
! 41: #
! 42: foreach {op opn} {
! 43: || cat * mul / div % mod + add
! 44: - sub << lshift >> rshift & bitand | bitor
! 45: < less <= lesseq > more >= moreeq = eq1
! 46: == eq2 <> ne1 != ne2 IS is LIKE like
! 47: GLOB glob AND and OR or MATCH match REGEXP regexp
! 48: {IS NOT} isnt
! 49: } {
! 50: set ::opname($op) $opn
! 51: }
! 52: set oplist [list]
! 53: foreach {prec opl} {
! 54: 1 ||
! 55: 2 {* / %}
! 56: 3 {+ -}
! 57: 4 {<< >> & |}
! 58: 5 {< <= > >=}
! 59: 6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
! 60: 7 AND
! 61: 8 OR
! 62: } {
! 63: foreach op $opl {
! 64: set ::opprec($op) $prec
! 65: lappend oplist $op
! 66: }
! 67: }
! 68:
! 69:
! 70: # Hook in definitions of MATCH and REGEX. The following implementations
! 71: # cause MATCH and REGEX to behave similarly to the == operator.
! 72: #
! 73: proc matchfunc {a b} { return [expr {$a==$b}] }
! 74: proc regexfunc {a b} { return [expr {$a==$b}] }
! 75: db func match -argcount 2 matchfunc
! 76: db func regexp -argcount 2 regexfunc
! 77:
! 78: #-------------------------------------------------------------------------
! 79: # Test cases e_expr-1.* attempt to verify that all binary operators listed
! 80: # in the documentation exist and that the relative precedences of the
! 81: # operators are also as the documentation suggests.
! 82: #
! 83: # EVIDENCE-OF: R-15514-65163 SQLite understands the following binary
! 84: # operators, in order from highest to lowest precedence: || * / % + -
! 85: # << >> & | < <= > >= = == != <> IS IS
! 86: # NOT IN LIKE GLOB MATCH REGEXP AND OR
! 87: #
! 88: # EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same
! 89: # precedence as =.
! 90: #
! 91:
! 92: unset -nocomplain untested
! 93: foreach op1 $oplist {
! 94: foreach op2 $oplist {
! 95: set untested($op1,$op2) 1
! 96: foreach {tn A B C} {
! 97: 1 22 45 66
! 98: 2 0 0 0
! 99: 3 0 0 1
! 100: 4 0 1 0
! 101: 5 0 1 1
! 102: 6 1 0 0
! 103: 7 1 0 1
! 104: 8 1 1 0
! 105: 9 1 1 1
! 106: 10 5 6 1
! 107: 11 1 5 6
! 108: 12 1 5 5
! 109: 13 5 5 1
! 110:
! 111: 14 5 2 1
! 112: 15 1 4 1
! 113: 16 -1 0 1
! 114: 17 0 1 -1
! 115:
! 116: } {
! 117: set testname "e_expr-1.$opname($op1).$opname($op2).$tn"
! 118:
! 119: # If $op2 groups more tightly than $op1, then the result
! 120: # of executing $sql1 whould be the same as executing $sql3.
! 121: # If $op1 groups more tightly, or if $op1 and $op2 have
! 122: # the same precedence, then executing $sql1 should return
! 123: # the same value as $sql2.
! 124: #
! 125: set sql1 "SELECT $A $op1 $B $op2 $C"
! 126: set sql2 "SELECT ($A $op1 $B) $op2 $C"
! 127: set sql3 "SELECT $A $op1 ($B $op2 $C)"
! 128:
! 129: set a2 [db one $sql2]
! 130: set a3 [db one $sql3]
! 131:
! 132: do_execsql_test $testname $sql1 [list [
! 133: if {$opprec($op2) < $opprec($op1)} {set a3} {set a2}
! 134: ]]
! 135: if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) }
! 136: }
! 137: }
! 138: }
! 139:
! 140: foreach op {* AND OR + || & |} { unset untested($op,$op) }
! 141: unset untested(+,-) ;# Since (a+b)-c == a+(b-c)
! 142: unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c)
! 143:
! 144: do_test e_expr-1.1 { array names untested } {}
! 145:
! 146: # At one point, test 1.2.2 was failing. Instead of the correct result, it
! 147: # was returning {1 1 0}. This would seem to indicate that LIKE has the
! 148: # same precedence as '<'. Which is incorrect. It has lower precedence.
! 149: #
! 150: do_execsql_test e_expr-1.2.1 {
! 151: SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1)
! 152: } {1 1 0}
! 153: do_execsql_test e_expr-1.2.2 {
! 154: SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2)
! 155: } {0 1 0}
! 156:
! 157: # Showing that LIKE and == have the same precedence
! 158: #
! 159: do_execsql_test e_expr-1.2.3 {
! 160: SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1)
! 161: } {1 1 0}
! 162: do_execsql_test e_expr-1.2.4 {
! 163: SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1)
! 164: } {1 1 0}
! 165:
! 166: # Showing that < groups more tightly than == (< has higher precedence).
! 167: #
! 168: do_execsql_test e_expr-1.2.5 {
! 169: SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1)
! 170: } {1 1 0}
! 171: do_execsql_test e_expr-1.6 {
! 172: SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2)
! 173: } {0 1 0}
! 174:
! 175: #-------------------------------------------------------------------------
! 176: # Check that the four unary prefix operators mentioned in the
! 177: # documentation exist.
! 178: #
! 179: # EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these:
! 180: # - + ~ NOT
! 181: #
! 182: do_execsql_test e_expr-2.1 { SELECT - 10 } {-10}
! 183: do_execsql_test e_expr-2.2 { SELECT + 10 } {10}
! 184: do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11}
! 185: do_execsql_test e_expr-2.4 { SELECT NOT 10 } {0}
! 186:
! 187: #-------------------------------------------------------------------------
! 188: # Tests for the two statements made regarding the unary + operator.
! 189: #
! 190: # EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op.
! 191: #
! 192: # EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers,
! 193: # blobs or NULL and it always returns a result with the same value as
! 194: # the operand.
! 195: #
! 196: foreach {tn literal type} {
! 197: 1 'helloworld' text
! 198: 2 45 integer
! 199: 3 45.2 real
! 200: 4 45.0 real
! 201: 5 X'ABCDEF' blob
! 202: 6 NULL null
! 203: } {
! 204: set sql " SELECT quote( + $literal ), typeof( + $literal) "
! 205: do_execsql_test e_expr-3.$tn $sql [list $literal $type]
! 206: }
! 207:
! 208: #-------------------------------------------------------------------------
! 209: # Check that both = and == are both acceptable as the "equals" operator.
! 210: # Similarly, either != or <> work as the not-equals operator.
! 211: #
! 212: # EVIDENCE-OF: R-03679-60639 Equals can be either = or ==.
! 213: #
! 214: # EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or
! 215: # <>.
! 216: #
! 217: foreach {tn literal different} {
! 218: 1 'helloworld' '12345'
! 219: 2 22 23
! 220: 3 'xyz' X'78797A'
! 221: 4 X'78797A00' 'xyz'
! 222: } {
! 223: do_execsql_test e_expr-4.$tn "
! 224: SELECT $literal = $literal, $literal == $literal,
! 225: $literal = $different, $literal == $different,
! 226: $literal = NULL, $literal == NULL,
! 227: $literal != $literal, $literal <> $literal,
! 228: $literal != $different, $literal <> $different,
! 229: $literal != NULL, $literal != NULL
! 230:
! 231: " {1 1 0 0 {} {} 0 0 1 1 {} {}}
! 232: }
! 233:
! 234: #-------------------------------------------------------------------------
! 235: # Test the || operator.
! 236: #
! 237: # EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins
! 238: # together the two strings of its operands.
! 239: #
! 240: foreach {tn a b} {
! 241: 1 'helloworld' '12345'
! 242: 2 22 23
! 243: } {
! 244: set as [db one "SELECT $a"]
! 245: set bs [db one "SELECT $b"]
! 246:
! 247: do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"]
! 248: }
! 249:
! 250: #-------------------------------------------------------------------------
! 251: # Test the % operator.
! 252: #
! 253: # EVIDENCE-OF: R-08914-63790 The operator % outputs the value of its
! 254: # left operand modulo its right operand.
! 255: #
! 256: do_execsql_test e_expr-6.1 {SELECT 72%5} {2}
! 257: do_execsql_test e_expr-6.2 {SELECT 72%-5} {2}
! 258: do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2}
! 259: do_execsql_test e_expr-6.4 {SELECT -72%5} {-2}
! 260:
! 261: #-------------------------------------------------------------------------
! 262: # Test that the results of all binary operators are either numeric or
! 263: # NULL, except for the || operator, which may evaluate to either a text
! 264: # value or NULL.
! 265: #
! 266: # EVIDENCE-OF: R-20665-17792 The result of any binary operator is either
! 267: # a numeric value or NULL, except for the || concatenation operator
! 268: # which always evaluates to either NULL or a text value.
! 269: #
! 270: set literals {
! 271: 1 'abc' 2 'hexadecimal' 3 ''
! 272: 4 123 5 -123 6 0
! 273: 7 123.4 8 0.0 9 -123.4
! 274: 10 X'ABCDEF' 11 X'' 12 X'0000'
! 275: 13 NULL
! 276: }
! 277: foreach op $oplist {
! 278: foreach {n1 rhs} $literals {
! 279: foreach {n2 lhs} $literals {
! 280:
! 281: set t [db one " SELECT typeof($lhs $op $rhs) "]
! 282: do_test e_expr-7.$opname($op).$n1.$n2 {
! 283: expr {
! 284: ($op=="||" && ($t == "text" || $t == "null"))
! 285: || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null"))
! 286: }
! 287: } 1
! 288:
! 289: }}
! 290: }
! 291:
! 292: #-------------------------------------------------------------------------
! 293: # Test the IS and IS NOT operators.
! 294: #
! 295: # EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and
! 296: # != except when one or both of the operands are NULL.
! 297: #
! 298: # EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL,
! 299: # then the IS operator evaluates to 1 (true) and the IS NOT operator
! 300: # evaluates to 0 (false).
! 301: #
! 302: # EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is
! 303: # not, then the IS operator evaluates to 0 (false) and the IS NOT
! 304: # operator is 1 (true).
! 305: #
! 306: # EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT
! 307: # expression to evaluate to NULL.
! 308: #
! 309: do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1}
! 310: do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0}
! 311: do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0}
! 312: do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1}
! 313: do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}}
! 314: do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}}
! 315: do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}}
! 316: do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1}
! 317: do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0}
! 318: do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1}
! 319: do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1}
! 320: do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0}
! 321: do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}}
! 322: do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}}
! 323: do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}}
! 324: do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0}
! 325:
! 326: foreach {n1 rhs} $literals {
! 327: foreach {n2 lhs} $literals {
! 328: if {$rhs!="NULL" && $lhs!="NULL"} {
! 329: set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"]
! 330: } else {
! 331: set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \
! 332: [expr {$lhs!="NULL" || $rhs!="NULL"}]
! 333: ]
! 334: }
! 335: set test e_expr-8.2.$n1.$n2
! 336: do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq
! 337: do_execsql_test $test.2 "
! 338: SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL
! 339: " {0 0}
! 340: }
! 341: }
! 342:
! 343: #-------------------------------------------------------------------------
! 344: # Run some tests on the COLLATE "unary postfix operator".
! 345: #
! 346: # This collation sequence reverses both arguments before using
! 347: # [string compare] to compare them. For example, when comparing the
! 348: # strings 'one' and 'four', return the result of:
! 349: #
! 350: # string compare eno ruof
! 351: #
! 352: proc reverse_str {zStr} {
! 353: set out ""
! 354: foreach c [split $zStr {}] { set out "${c}${out}" }
! 355: set out
! 356: }
! 357: proc reverse_collate {zLeft zRight} {
! 358: string compare [reverse_str $zLeft] [reverse_str $zRight]
! 359: }
! 360: db collate reverse reverse_collate
! 361:
! 362: # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
! 363: # operator that assigns a collating sequence to an expression.
! 364: #
! 365: # EVIDENCE-OF: R-23441-22541 The COLLATE operator has a higher
! 366: # precedence (binds more tightly) than any prefix unary operator or any
! 367: # binary operator.
! 368: #
! 369: do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0
! 370: do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1
! 371: do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0
! 372: do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1
! 373:
! 374: do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1
! 375: do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb') COLLATE reverse } 0
! 376: do_execsql_test e_expr-9.7 { SELECT 'abcd' >= 'bbbb' COLLATE reverse } 1
! 377: do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb') COLLATE reverse } 0
! 378:
! 379: do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1
! 380: do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0
! 381: do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1
! 382: do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0
! 383: do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1
! 384: do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0
! 385:
! 386: do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0
! 387: do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1
! 388: do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0
! 389: do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1
! 390: do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0
! 391: do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1
! 392:
! 393: do_execsql_test e_expr-9.22 {
! 394: SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase
! 395: } 1
! 396: do_execsql_test e_expr-9.23 {
! 397: SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase
! 398: } 0
! 399:
! 400: # EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE
! 401: # operator overrides the collating sequence determined by the COLLATE
! 402: # clause in a table column definition.
! 403: #
! 404: do_execsql_test e_expr-9.24 {
! 405: CREATE TABLE t24(a COLLATE NOCASE, b);
! 406: INSERT INTO t24 VALUES('aaa', 1);
! 407: INSERT INTO t24 VALUES('bbb', 2);
! 408: INSERT INTO t24 VALUES('ccc', 3);
! 409: } {}
! 410: do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0}
! 411: do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0}
! 412: do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0}
! 413: do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0}
! 414:
! 415: #-------------------------------------------------------------------------
! 416: # Test statements related to literal values.
! 417: #
! 418: # EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating
! 419: # point numbers, strings, BLOBs, or NULLs.
! 420: #
! 421: do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer}
! 422: do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real}
! 423: do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text}
! 424: do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob}
! 425: do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null}
! 426:
! 427: # "Scientific notation is supported for point literal values."
! 428: #
! 429: do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real}
! 430: do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real}
! 431: do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034}
! 432: do_execsql_test e_expr-10.2.4 { SELECT 3e+4 } {30000.0}
! 433:
! 434: # EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing
! 435: # the string in single quotes (').
! 436: #
! 437: # EVIDENCE-OF: R-07100-06606 A single quote within the string can be
! 438: # encoded by putting two single quotes in a row - as in Pascal.
! 439: #
! 440: do_execsql_test e_expr-10.3.1 { SELECT 'is not' } {{is not}}
! 441: do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text}
! 442: do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' } {isn't}
! 443: do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text}
! 444:
! 445: # EVIDENCE-OF: R-09593-03321 BLOB literals are string literals
! 446: # containing hexadecimal data and preceded by a single "x" or "X"
! 447: # character.
! 448: #
! 449: # EVIDENCE-OF: R-39344-59787 For example: X'53514C697465'
! 450: #
! 451: do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob
! 452: do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob
! 453: do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob
! 454: do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob
! 455: do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465') } blob
! 456:
! 457: # EVIDENCE-OF: R-23914-51476 A literal value can also be the token
! 458: # "NULL".
! 459: #
! 460: do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}}
! 461: do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null}
! 462:
! 463: #-------------------------------------------------------------------------
! 464: # Test statements related to bound parameters
! 465: #
! 466:
! 467: proc parameter_test {tn sql params result} {
! 468: set stmt [sqlite3_prepare_v2 db $sql -1]
! 469:
! 470: foreach {number name} $params {
! 471: set nm [sqlite3_bind_parameter_name $stmt $number]
! 472: do_test $tn.name.$number [list set {} $nm] $name
! 473: sqlite3_bind_int $stmt $number [expr -1 * $number]
! 474: }
! 475:
! 476: sqlite3_step $stmt
! 477:
! 478: set res [list]
! 479: for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {
! 480: lappend res [sqlite3_column_text $stmt $i]
! 481: }
! 482:
! 483: set rc [sqlite3_finalize $stmt]
! 484: do_test $tn.rc [list set {} $rc] SQLITE_OK
! 485: do_test $tn.res [list set {} $res] $result
! 486: }
! 487:
! 488: # EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN
! 489: # holds a spot for the NNN-th parameter. NNN must be between 1 and
! 490: # SQLITE_MAX_VARIABLE_NUMBER.
! 491: #
! 492: set mvn $SQLITE_MAX_VARIABLE_NUMBER
! 493: parameter_test e_expr-11.1 "
! 494: SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4
! 495: " "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4"
! 496:
! 497: set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER"
! 498: foreach {tn param_number} [list \
! 499: 2 0 \
! 500: 3 [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \
! 501: 4 [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \
! 502: 5 12345678903456789034567890234567890 \
! 503: 6 2147483648 \
! 504: 7 2147483649 \
! 505: 8 4294967296 \
! 506: 9 4294967297 \
! 507: 10 9223372036854775808 \
! 508: 11 9223372036854775809 \
! 509: 12 18446744073709551616 \
! 510: 13 18446744073709551617 \
! 511: ] {
! 512: do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg]
! 513: }
! 514:
! 515: # EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a
! 516: # number creates a parameter with a number one greater than the largest
! 517: # parameter number already assigned.
! 518: #
! 519: # EVIDENCE-OF: R-42938-07030 If this means the parameter number is
! 520: # greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error.
! 521: #
! 522: parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1
! 523: parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2}
! 524: parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6}
! 525: parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5}
! 526: parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" {
! 527: 1 {} 456 ?456 457 {}
! 528: } {-1 -456 -457}
! 529: parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" {
! 530: 1 {} 456 ?456 4 ?4 457 {}
! 531: } {-1 -456 -4 -457}
! 532: foreach {tn sql} [list \
! 533: 1 "SELECT ?$mvn, ?" \
! 534: 2 "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?" \
! 535: 3 "SELECT ?[expr $mvn], ?5, ?6, ?" \
! 536: ] {
! 537: do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}]
! 538: }
! 539:
! 540: # EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name
! 541: # holds a spot for a named parameter with the name :AAAA.
! 542: #
! 543: # Identifiers in SQLite consist of alphanumeric, '_' and '$' characters,
! 544: # and any UTF characters with codepoints larger than 127 (non-ASCII
! 545: # characters).
! 546: #
! 547: parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1
! 548: parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1
! 549: parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1
! 550: parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1
! 551: parameter_test e_expr-11.2.5 "
! 552: SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
! 553: " "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
! 554: parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1
! 555:
! 556: # EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon,
! 557: # except that the name of the parameter created is @AAAA.
! 558: #
! 559: parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1
! 560: parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1
! 561: parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1
! 562: parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1
! 563: parameter_test e_expr-11.3.5 "
! 564: SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
! 565: " "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
! 566: parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1
! 567:
! 568: # EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier
! 569: # name also holds a spot for a named parameter with the name $AAAA.
! 570: #
! 571: # EVIDENCE-OF: R-55025-21042 The identifier name in this case can
! 572: # include one or more occurrences of "::" and a suffix enclosed in
! 573: # "(...)" containing any text at all.
! 574: #
! 575: # Note: Looks like an identifier cannot consist entirely of "::"
! 576: # characters or just a suffix. Also, the other named variable characters
! 577: # (: and @) work the same way internally. Why not just document it that way?
! 578: #
! 579: parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1
! 580: parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1
! 581: parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1
! 582: parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1
! 583: parameter_test e_expr-11.4.5 "
! 584: SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
! 585: " "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
! 586: parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1
! 587:
! 588: parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1
! 589: parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1
! 590: parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1
! 591:
! 592: # EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The
! 593: # number assigned is one greater than the largest parameter number
! 594: # already assigned.
! 595: #
! 596: # EVIDENCE-OF: R-42620-22184 If this means the parameter would be
! 597: # assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an
! 598: # error.
! 599: #
! 600: parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2}
! 601: parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124}
! 602: parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} {
! 603: 1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c
! 604: } {-1 -8 -9 -10 -2 -11}
! 605: foreach {tn sql} [list \
! 606: 1 "SELECT ?$mvn, \$::a" \
! 607: 2 "SELECT ?$mvn, ?4, @a1" \
! 608: 3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \
! 609: ] {
! 610: do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}]
! 611: }
! 612:
! 613: # EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values
! 614: # using sqlite3_bind() are treated as NULL.
! 615: #
! 616: do_test e_expr-11.7.1 {
! 617: set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1]
! 618: sqlite3_step $stmt
! 619:
! 620: list [sqlite3_column_type $stmt 0] \
! 621: [sqlite3_column_type $stmt 1] \
! 622: [sqlite3_column_type $stmt 2] \
! 623: [sqlite3_column_type $stmt 3]
! 624: } {NULL NULL NULL NULL}
! 625: do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK
! 626:
! 627: #-------------------------------------------------------------------------
! 628: # "Test" the syntax diagrams in lang_expr.html.
! 629: #
! 630: # EVIDENCE-OF: R-02989-21050 -- syntax diagram signed-number
! 631: #
! 632: do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
! 633: do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
! 634: do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
! 635: do_execsql_test e_expr-12.1.4 {
! 636: SELECT 1.4, +1.4, -1.4
! 637: } {1.4 1.4 -1.4}
! 638: do_execsql_test e_expr-12.1.5 {
! 639: SELECT 1.5e+5, +1.5e+5, -1.5e+5
! 640: } {150000.0 150000.0 -150000.0}
! 641: do_execsql_test e_expr-12.1.6 {
! 642: SELECT 0.0001, +0.0001, -0.0001
! 643: } {0.0001 0.0001 -0.0001}
! 644:
! 645: # EVIDENCE-OF: R-43188-60852 -- syntax diagram literal-value
! 646: #
! 647: set sqlite_current_time 1
! 648: do_execsql_test e_expr-12.2.1 {SELECT 123} {123}
! 649: do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0}
! 650: do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde}
! 651: do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC}
! 652: do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}}
! 653: do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01}
! 654: do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01}
! 655: do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
! 656: set sqlite_current_time 0
! 657:
! 658: # EVIDENCE-OF: R-50544-32159 -- syntax diagram expr
! 659: #
! 660: forcedelete test.db2
! 661: execsql {
! 662: ATTACH 'test.db2' AS dbname;
! 663: CREATE TABLE dbname.tblname(cname);
! 664: }
! 665:
! 666: proc glob {args} {return 1}
! 667: db function glob glob
! 668: db function match glob
! 669: db function regexp glob
! 670:
! 671: foreach {tn expr} {
! 672: 1 123
! 673: 2 123.4e05
! 674: 3 'abcde'
! 675: 4 X'414243'
! 676: 5 NULL
! 677: 6 CURRENT_TIME
! 678: 7 CURRENT_DATE
! 679: 8 CURRENT_TIMESTAMP
! 680:
! 681: 9 ?
! 682: 10 ?123
! 683: 11 @hello
! 684: 12 :world
! 685: 13 $tcl
! 686: 14 $tcl(array)
! 687:
! 688: 15 cname
! 689: 16 tblname.cname
! 690: 17 dbname.tblname.cname
! 691:
! 692: 18 "+ EXPR"
! 693: 19 "- EXPR"
! 694: 20 "NOT EXPR"
! 695: 21 "~ EXPR"
! 696:
! 697: 22 "EXPR1 || EXPR2"
! 698: 23 "EXPR1 * EXPR2"
! 699: 24 "EXPR1 / EXPR2"
! 700: 25 "EXPR1 % EXPR2"
! 701: 26 "EXPR1 + EXPR2"
! 702: 27 "EXPR1 - EXPR2"
! 703: 28 "EXPR1 << EXPR2"
! 704: 29 "EXPR1 >> EXPR2"
! 705: 30 "EXPR1 & EXPR2"
! 706: 31 "EXPR1 | EXPR2"
! 707: 32 "EXPR1 < EXPR2"
! 708: 33 "EXPR1 <= EXPR2"
! 709: 34 "EXPR1 > EXPR2"
! 710: 35 "EXPR1 >= EXPR2"
! 711: 36 "EXPR1 = EXPR2"
! 712: 37 "EXPR1 == EXPR2"
! 713: 38 "EXPR1 != EXPR2"
! 714: 39 "EXPR1 <> EXPR2"
! 715: 40 "EXPR1 IS EXPR2"
! 716: 41 "EXPR1 IS NOT EXPR2"
! 717: 42 "EXPR1 AND EXPR2"
! 718: 43 "EXPR1 OR EXPR2"
! 719:
! 720: 44 "count(*)"
! 721: 45 "count(DISTINCT EXPR)"
! 722: 46 "substr(EXPR, 10, 20)"
! 723: 47 "changes()"
! 724:
! 725: 48 "( EXPR )"
! 726:
! 727: 49 "CAST ( EXPR AS integer )"
! 728: 50 "CAST ( EXPR AS 'abcd' )"
! 729: 51 "CAST ( EXPR AS 'ab$ $cd' )"
! 730:
! 731: 52 "EXPR COLLATE nocase"
! 732: 53 "EXPR COLLATE binary"
! 733:
! 734: 54 "EXPR1 LIKE EXPR2"
! 735: 55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
! 736: 56 "EXPR1 GLOB EXPR2"
! 737: 57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
! 738: 58 "EXPR1 REGEXP EXPR2"
! 739: 59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
! 740: 60 "EXPR1 MATCH EXPR2"
! 741: 61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
! 742: 62 "EXPR1 NOT LIKE EXPR2"
! 743: 63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
! 744: 64 "EXPR1 NOT GLOB EXPR2"
! 745: 65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
! 746: 66 "EXPR1 NOT REGEXP EXPR2"
! 747: 67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
! 748: 68 "EXPR1 NOT MATCH EXPR2"
! 749: 69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"
! 750:
! 751: 70 "EXPR ISNULL"
! 752: 71 "EXPR NOTNULL"
! 753: 72 "EXPR NOT NULL"
! 754:
! 755: 73 "EXPR1 IS EXPR2"
! 756: 74 "EXPR1 IS NOT EXPR2"
! 757:
! 758: 75 "EXPR NOT BETWEEN EXPR1 AND EXPR2"
! 759: 76 "EXPR BETWEEN EXPR1 AND EXPR2"
! 760:
! 761: 77 "EXPR NOT IN (SELECT cname FROM tblname)"
! 762: 78 "EXPR NOT IN (1)"
! 763: 79 "EXPR NOT IN (1, 2, 3)"
! 764: 80 "EXPR NOT IN tblname"
! 765: 81 "EXPR NOT IN dbname.tblname"
! 766: 82 "EXPR IN (SELECT cname FROM tblname)"
! 767: 83 "EXPR IN (1)"
! 768: 84 "EXPR IN (1, 2, 3)"
! 769: 85 "EXPR IN tblname"
! 770: 86 "EXPR IN dbname.tblname"
! 771:
! 772: 87 "EXISTS (SELECT cname FROM tblname)"
! 773: 88 "NOT EXISTS (SELECT cname FROM tblname)"
! 774:
! 775: 89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
! 776: 90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END"
! 777: 91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
! 778: 92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
! 779: 93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
! 780: 94 "CASE WHEN EXPR1 THEN EXPR2 END"
! 781: 95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
! 782: 96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
! 783: } {
! 784:
! 785: # If the expression string being parsed contains "EXPR2", then replace
! 786: # string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it
! 787: # contains "EXPR", then replace EXPR with an arbitrary SQL expression.
! 788: #
! 789: set elist [list $expr]
! 790: if {[string match *EXPR2* $expr]} {
! 791: set elist [list]
! 792: foreach {e1 e2} { cname "34+22" } {
! 793: lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr]
! 794: }
! 795: }
! 796: if {[string match *EXPR* $expr]} {
! 797: set elist2 [list]
! 798: foreach el $elist {
! 799: foreach e { cname "34+22" } {
! 800: lappend elist2 [string map [list EXPR $e] $el]
! 801: }
! 802: }
! 803: set elist $elist2
! 804: }
! 805:
! 806: set x 0
! 807: foreach e $elist {
! 808: incr x
! 809: do_test e_expr-12.3.$tn.$x {
! 810: set rc [catch { execsql "SELECT $e FROM tblname" } msg]
! 811: } {0}
! 812: }
! 813: }
! 814:
! 815: # EVIDENCE-OF: R-39820-63916 -- syntax diagram raise-function
! 816: #
! 817: foreach {tn raiseexpr} {
! 818: 1 "RAISE(IGNORE)"
! 819: 2 "RAISE(ROLLBACK, 'error message')"
! 820: 3 "RAISE(ABORT, 'error message')"
! 821: 4 "RAISE(FAIL, 'error message')"
! 822: } {
! 823: do_execsql_test e_expr-12.4.$tn "
! 824: CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN
! 825: SELECT $raiseexpr ;
! 826: END;
! 827: " {}
! 828: }
! 829:
! 830: #-------------------------------------------------------------------------
! 831: # Test the statements related to the BETWEEN operator.
! 832: #
! 833: # EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically
! 834: # equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent
! 835: # to "x>=y AND x<=z" except that with BETWEEN, the x expression is
! 836: # only evaluated once.
! 837: #
! 838: db func x x
! 839: proc x {} { incr ::xcount ; return [expr $::x] }
! 840: foreach {tn x expr res nEval} {
! 841: 1 10 "x() >= 5 AND x() <= 15" 1 2
! 842: 2 10 "x() BETWEEN 5 AND 15" 1 1
! 843:
! 844: 3 5 "x() >= 5 AND x() <= 5" 1 2
! 845: 4 5 "x() BETWEEN 5 AND 5" 1 1
! 846: } {
! 847: do_test e_expr-13.1.$tn {
! 848: set ::xcount 0
! 849: set a [execsql "SELECT $expr"]
! 850: list $::xcount $a
! 851: } [list $nEval $res]
! 852: }
! 853:
! 854: # EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is
! 855: # the same as the precedence as operators == and != and LIKE and groups
! 856: # left to right.
! 857: #
! 858: # Therefore, BETWEEN groups more tightly than operator "AND", but less
! 859: # so than "<".
! 860: #
! 861: do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1
! 862: do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1
! 863: do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0
! 864: do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1
! 865: do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1
! 866: do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0
! 867:
! 868: do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1
! 869: do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1
! 870: do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0
! 871: do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1
! 872: do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1
! 873: do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0
! 874:
! 875: do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1
! 876: do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1
! 877: do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0
! 878: do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1
! 879: do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1
! 880: do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0
! 881:
! 882: do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0
! 883: do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0
! 884: do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1
! 885: do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0
! 886: do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0
! 887: do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1
! 888:
! 889: do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1
! 890: do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1
! 891: do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0
! 892: do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0
! 893: do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0
! 894: do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1
! 895:
! 896: #-------------------------------------------------------------------------
! 897: # Test the statements related to the LIKE and GLOB operators.
! 898: #
! 899: # EVIDENCE-OF: R-16584-60189 The LIKE operator does a pattern matching
! 900: # comparison.
! 901: #
! 902: # EVIDENCE-OF: R-11295-04657 The operand to the right of the LIKE
! 903: # operator contains the pattern and the left hand operand contains the
! 904: # string to match against the pattern.
! 905: #
! 906: do_execsql_test e_expr-14.1.1 { SELECT 'abc%' LIKE 'abcde' } 0
! 907: do_execsql_test e_expr-14.1.2 { SELECT 'abcde' LIKE 'abc%' } 1
! 908:
! 909: # EVIDENCE-OF: R-55406-38524 A percent symbol ("%") in the LIKE pattern
! 910: # matches any sequence of zero or more characters in the string.
! 911: #
! 912: do_execsql_test e_expr-14.2.1 { SELECT 'abde' LIKE 'ab%de' } 1
! 913: do_execsql_test e_expr-14.2.2 { SELECT 'abXde' LIKE 'ab%de' } 1
! 914: do_execsql_test e_expr-14.2.3 { SELECT 'abABCde' LIKE 'ab%de' } 1
! 915:
! 916: # EVIDENCE-OF: R-30433-25443 An underscore ("_") in the LIKE pattern
! 917: # matches any single character in the string.
! 918: #
! 919: do_execsql_test e_expr-14.3.1 { SELECT 'abde' LIKE 'ab_de' } 0
! 920: do_execsql_test e_expr-14.3.2 { SELECT 'abXde' LIKE 'ab_de' } 1
! 921: do_execsql_test e_expr-14.3.3 { SELECT 'abABCde' LIKE 'ab_de' } 0
! 922:
! 923: # EVIDENCE-OF: R-59007-20454 Any other character matches itself or its
! 924: # lower/upper case equivalent (i.e. case-insensitive matching).
! 925: #
! 926: do_execsql_test e_expr-14.4.1 { SELECT 'abc' LIKE 'aBc' } 1
! 927: do_execsql_test e_expr-14.4.2 { SELECT 'aBc' LIKE 'aBc' } 1
! 928: do_execsql_test e_expr-14.4.3 { SELECT 'ac' LIKE 'aBc' } 0
! 929:
! 930: # EVIDENCE-OF: R-23648-58527 SQLite only understands upper/lower case
! 931: # for ASCII characters by default.
! 932: #
! 933: # EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
! 934: # default for unicode characters that are beyond the ASCII range.
! 935: #
! 936: # EVIDENCE-OF: R-44381-11669 the expression
! 937: # 'a' LIKE 'A' is TRUE but
! 938: # 'æ' LIKE 'Æ' is FALSE.
! 939: #
! 940: # The restriction to ASCII characters does not apply if the ICU
! 941: # library is compiled in. When ICU is enabled SQLite does not act
! 942: # as it does "by default".
! 943: #
! 944: do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a' } 1
! 945: ifcapable !icu {
! 946: do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0
! 947: }
! 948:
! 949: # EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
! 950: # then the expression following the ESCAPE keyword must evaluate to a
! 951: # string consisting of a single character.
! 952: #
! 953: do_catchsql_test e_expr-14.6.1 {
! 954: SELECT 'A' LIKE 'a' ESCAPE '12'
! 955: } {1 {ESCAPE expression must be a single character}}
! 956: do_catchsql_test e_expr-14.6.2 {
! 957: SELECT 'A' LIKE 'a' ESCAPE ''
! 958: } {1 {ESCAPE expression must be a single character}}
! 959: do_catchsql_test e_expr-14.6.3 { SELECT 'A' LIKE 'a' ESCAPE 'x' } {0 1}
! 960: do_catchsql_test e_expr-14.6.4 "SELECT 'A' LIKE 'a' ESCAPE '\u00e6'" {0 1}
! 961:
! 962: # EVIDENCE-OF: R-02045-23762 This character may be used in the LIKE
! 963: # pattern to include literal percent or underscore characters.
! 964: #
! 965: # EVIDENCE-OF: R-13345-31830 The escape character followed by a percent
! 966: # symbol (%), underscore (_), or a second instance of the escape
! 967: # character itself matches a literal percent symbol, underscore, or a
! 968: # single escape character, respectively.
! 969: #
! 970: do_execsql_test e_expr-14.7.1 { SELECT 'abc%' LIKE 'abcX%' ESCAPE 'X' } 1
! 971: do_execsql_test e_expr-14.7.2 { SELECT 'abc5' LIKE 'abcX%' ESCAPE 'X' } 0
! 972: do_execsql_test e_expr-14.7.3 { SELECT 'abc' LIKE 'abcX%' ESCAPE 'X' } 0
! 973: do_execsql_test e_expr-14.7.4 { SELECT 'abcX%' LIKE 'abcX%' ESCAPE 'X' } 0
! 974: do_execsql_test e_expr-14.7.5 { SELECT 'abc%%' LIKE 'abcX%' ESCAPE 'X' } 0
! 975:
! 976: do_execsql_test e_expr-14.7.6 { SELECT 'abc_' LIKE 'abcX_' ESCAPE 'X' } 1
! 977: do_execsql_test e_expr-14.7.7 { SELECT 'abc5' LIKE 'abcX_' ESCAPE 'X' } 0
! 978: do_execsql_test e_expr-14.7.8 { SELECT 'abc' LIKE 'abcX_' ESCAPE 'X' } 0
! 979: do_execsql_test e_expr-14.7.9 { SELECT 'abcX_' LIKE 'abcX_' ESCAPE 'X' } 0
! 980: do_execsql_test e_expr-14.7.10 { SELECT 'abc__' LIKE 'abcX_' ESCAPE 'X' } 0
! 981:
! 982: do_execsql_test e_expr-14.7.11 { SELECT 'abcX' LIKE 'abcXX' ESCAPE 'X' } 1
! 983: do_execsql_test e_expr-14.7.12 { SELECT 'abc5' LIKE 'abcXX' ESCAPE 'X' } 0
! 984: do_execsql_test e_expr-14.7.13 { SELECT 'abc' LIKE 'abcXX' ESCAPE 'X' } 0
! 985: do_execsql_test e_expr-14.7.14 { SELECT 'abcXX' LIKE 'abcXX' ESCAPE 'X' } 0
! 986:
! 987: # EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
! 988: # calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
! 989: #
! 990: proc likefunc {args} {
! 991: eval lappend ::likeargs $args
! 992: return 1
! 993: }
! 994: db func like -argcount 2 likefunc
! 995: db func like -argcount 3 likefunc
! 996: set ::likeargs [list]
! 997: do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
! 998: do_test e_expr-15.1.2 { set likeargs } {def abc}
! 999: set ::likeargs [list]
! 1000: do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
! 1001: do_test e_expr-15.1.4 { set likeargs } {def abc X}
! 1002: db close
! 1003: sqlite3 db test.db
! 1004:
! 1005: # EVIDENCE-OF: R-22868-25880 The LIKE operator can be made case
! 1006: # sensitive using the case_sensitive_like pragma.
! 1007: #
! 1008: do_execsql_test e_expr-16.1.1 { SELECT 'abcxyz' LIKE 'ABC%' } 1
! 1009: do_execsql_test e_expr-16.1.2 { PRAGMA case_sensitive_like = 1 } {}
! 1010: do_execsql_test e_expr-16.1.3 { SELECT 'abcxyz' LIKE 'ABC%' } 0
! 1011: do_execsql_test e_expr-16.1.4 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
! 1012: do_execsql_test e_expr-16.1.5 { PRAGMA case_sensitive_like = 0 } {}
! 1013: do_execsql_test e_expr-16.1.6 { SELECT 'abcxyz' LIKE 'ABC%' } 1
! 1014: do_execsql_test e_expr-16.1.7 { SELECT 'ABCxyz' LIKE 'ABC%' } 1
! 1015:
! 1016: # EVIDENCE-OF: R-52087-12043 The GLOB operator is similar to LIKE but
! 1017: # uses the Unix file globbing syntax for its wildcards.
! 1018: #
! 1019: # EVIDENCE-OF: R-09813-17279 Also, GLOB is case sensitive, unlike LIKE.
! 1020: #
! 1021: do_execsql_test e_expr-17.1.1 { SELECT 'abcxyz' GLOB 'abc%' } 0
! 1022: do_execsql_test e_expr-17.1.2 { SELECT 'abcxyz' GLOB 'abc*' } 1
! 1023: do_execsql_test e_expr-17.1.3 { SELECT 'abcxyz' GLOB 'abc___' } 0
! 1024: do_execsql_test e_expr-17.1.4 { SELECT 'abcxyz' GLOB 'abc???' } 1
! 1025:
! 1026: do_execsql_test e_expr-17.1.5 { SELECT 'abcxyz' GLOB 'abc*' } 1
! 1027: do_execsql_test e_expr-17.1.6 { SELECT 'ABCxyz' GLOB 'abc*' } 0
! 1028: do_execsql_test e_expr-17.1.7 { SELECT 'abcxyz' GLOB 'ABC*' } 0
! 1029:
! 1030: # EVIDENCE-OF: R-39616-20555 Both GLOB and LIKE may be preceded by the
! 1031: # NOT keyword to invert the sense of the test.
! 1032: #
! 1033: do_execsql_test e_expr-17.2.1 { SELECT 'abcxyz' NOT GLOB 'ABC*' } 1
! 1034: do_execsql_test e_expr-17.2.2 { SELECT 'abcxyz' NOT GLOB 'abc*' } 0
! 1035: do_execsql_test e_expr-17.2.3 { SELECT 'abcxyz' NOT LIKE 'ABC%' } 0
! 1036: do_execsql_test e_expr-17.2.4 { SELECT 'abcxyz' NOT LIKE 'abc%' } 0
! 1037: do_execsql_test e_expr-17.2.5 { SELECT 'abdxyz' NOT LIKE 'abc%' } 1
! 1038:
! 1039: db nullvalue null
! 1040: do_execsql_test e_expr-17.2.6 { SELECT 'abcxyz' NOT GLOB NULL } null
! 1041: do_execsql_test e_expr-17.2.7 { SELECT 'abcxyz' NOT LIKE NULL } null
! 1042: do_execsql_test e_expr-17.2.8 { SELECT NULL NOT GLOB 'abc*' } null
! 1043: do_execsql_test e_expr-17.2.9 { SELECT NULL NOT LIKE 'ABC%' } null
! 1044: db nullvalue {}
! 1045:
! 1046: # EVIDENCE-OF: R-39414-35489 The infix GLOB operator is implemented by
! 1047: # calling the function glob(Y,X) and can be modified by overriding that
! 1048: # function.
! 1049: proc globfunc {args} {
! 1050: eval lappend ::globargs $args
! 1051: return 1
! 1052: }
! 1053: db func glob -argcount 2 globfunc
! 1054: set ::globargs [list]
! 1055: do_execsql_test e_expr-17.3.1 { SELECT 'abc' GLOB 'def' } 1
! 1056: do_test e_expr-17.3.2 { set globargs } {def abc}
! 1057: set ::globargs [list]
! 1058: do_execsql_test e_expr-17.3.3 { SELECT 'X' NOT GLOB 'Y' } 0
! 1059: do_test e_expr-17.3.4 { set globargs } {Y X}
! 1060: sqlite3 db test.db
! 1061:
! 1062: # EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
! 1063: # default and so use of the REGEXP operator will normally result in an
! 1064: # error message.
! 1065: #
! 1066: # There is a regexp function if ICU is enabled though.
! 1067: #
! 1068: ifcapable !icu {
! 1069: do_catchsql_test e_expr-18.1.1 {
! 1070: SELECT regexp('abc', 'def')
! 1071: } {1 {no such function: regexp}}
! 1072: do_catchsql_test e_expr-18.1.2 {
! 1073: SELECT 'abc' REGEXP 'def'
! 1074: } {1 {no such function: REGEXP}}
! 1075: }
! 1076:
! 1077: # EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
! 1078: # the regexp() user function.
! 1079: #
! 1080: # EVIDENCE-OF: R-57289-13578 If a application-defined SQL function named
! 1081: # "regexp" is added at run-time, that function will be called in order
! 1082: # to implement the REGEXP operator.
! 1083: #
! 1084: proc regexpfunc {args} {
! 1085: eval lappend ::regexpargs $args
! 1086: return 1
! 1087: }
! 1088: db func regexp -argcount 2 regexpfunc
! 1089: set ::regexpargs [list]
! 1090: do_execsql_test e_expr-18.2.1 { SELECT 'abc' REGEXP 'def' } 1
! 1091: do_test e_expr-18.2.2 { set regexpargs } {def abc}
! 1092: set ::regexpargs [list]
! 1093: do_execsql_test e_expr-18.2.3 { SELECT 'X' NOT REGEXP 'Y' } 0
! 1094: do_test e_expr-18.2.4 { set regexpargs } {Y X}
! 1095: sqlite3 db test.db
! 1096:
! 1097: # EVIDENCE-OF: R-42037-37826 The default match() function implementation
! 1098: # raises an exception and is not really useful for anything.
! 1099: #
! 1100: do_catchsql_test e_expr-19.1.1 {
! 1101: SELECT 'abc' MATCH 'def'
! 1102: } {1 {unable to use function MATCH in the requested context}}
! 1103: do_catchsql_test e_expr-19.1.2 {
! 1104: SELECT match('abc', 'def')
! 1105: } {1 {unable to use function MATCH in the requested context}}
! 1106:
! 1107: # EVIDENCE-OF: R-37916-47407 The MATCH operator is a special syntax for
! 1108: # the match() application-defined function.
! 1109: #
! 1110: # EVIDENCE-OF: R-06021-09373 But extensions can override the match()
! 1111: # function with more helpful logic.
! 1112: #
! 1113: proc matchfunc {args} {
! 1114: eval lappend ::matchargs $args
! 1115: return 1
! 1116: }
! 1117: db func match -argcount 2 matchfunc
! 1118: set ::matchargs [list]
! 1119: do_execsql_test e_expr-19.2.1 { SELECT 'abc' MATCH 'def' } 1
! 1120: do_test e_expr-19.2.2 { set matchargs } {def abc}
! 1121: set ::matchargs [list]
! 1122: do_execsql_test e_expr-19.2.3 { SELECT 'X' NOT MATCH 'Y' } 0
! 1123: do_test e_expr-19.2.4 { set matchargs } {Y X}
! 1124: sqlite3 db test.db
! 1125:
! 1126: #-------------------------------------------------------------------------
! 1127: # Test cases for the testable statements related to the CASE expression.
! 1128: #
! 1129: # EVIDENCE-OF: R-15199-61389 There are two basic forms of the CASE
! 1130: # expression: those with a base expression and those without.
! 1131: #
! 1132: do_execsql_test e_expr-20.1 {
! 1133: SELECT CASE WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
! 1134: } {true}
! 1135: do_execsql_test e_expr-20.2 {
! 1136: SELECT CASE 0 WHEN 1 THEN 'true' WHEN 0 THEN 'false' ELSE 'else' END;
! 1137: } {false}
! 1138:
! 1139: proc var {nm} {
! 1140: lappend ::varlist $nm
! 1141: return [set "::$nm"]
! 1142: }
! 1143: db func var var
! 1144:
! 1145: # EVIDENCE-OF: R-30638-59954 In a CASE without a base expression, each
! 1146: # WHEN expression is evaluated and the result treated as a boolean,
! 1147: # starting with the leftmost and continuing to the right.
! 1148: #
! 1149: foreach {a b c} {0 0 0} break
! 1150: set varlist [list]
! 1151: do_execsql_test e_expr-21.1.1 {
! 1152: SELECT CASE WHEN var('a') THEN 'A'
! 1153: WHEN var('b') THEN 'B'
! 1154: WHEN var('c') THEN 'C' END
! 1155: } {{}}
! 1156: do_test e_expr-21.1.2 { set varlist } {a b c}
! 1157: set varlist [list]
! 1158: do_execsql_test e_expr-21.1.3 {
! 1159: SELECT CASE WHEN var('c') THEN 'C'
! 1160: WHEN var('b') THEN 'B'
! 1161: WHEN var('a') THEN 'A'
! 1162: ELSE 'no result'
! 1163: END
! 1164: } {{no result}}
! 1165: do_test e_expr-21.1.4 { set varlist } {c b a}
! 1166:
! 1167: # EVIDENCE-OF: R-39009-25596 The result of the CASE expression is the
! 1168: # evaluation of the THEN expression that corresponds to the first WHEN
! 1169: # expression that evaluates to true.
! 1170: #
! 1171: foreach {a b c} {0 1 0} break
! 1172: do_execsql_test e_expr-21.2.1 {
! 1173: SELECT CASE WHEN var('a') THEN 'A'
! 1174: WHEN var('b') THEN 'B'
! 1175: WHEN var('c') THEN 'C'
! 1176: ELSE 'no result'
! 1177: END
! 1178: } {B}
! 1179: foreach {a b c} {0 1 1} break
! 1180: do_execsql_test e_expr-21.2.2 {
! 1181: SELECT CASE WHEN var('a') THEN 'A'
! 1182: WHEN var('b') THEN 'B'
! 1183: WHEN var('c') THEN 'C'
! 1184: ELSE 'no result'
! 1185: END
! 1186: } {B}
! 1187: foreach {a b c} {0 0 1} break
! 1188: do_execsql_test e_expr-21.2.3 {
! 1189: SELECT CASE WHEN var('a') THEN 'A'
! 1190: WHEN var('b') THEN 'B'
! 1191: WHEN var('c') THEN 'C'
! 1192: ELSE 'no result'
! 1193: END
! 1194: } {C}
! 1195:
! 1196: # EVIDENCE-OF: R-24227-04807 Or, if none of the WHEN expressions
! 1197: # evaluate to true, the result of evaluating the ELSE expression, if
! 1198: # any.
! 1199: #
! 1200: foreach {a b c} {0 0 0} break
! 1201: do_execsql_test e_expr-21.3.1 {
! 1202: SELECT CASE WHEN var('a') THEN 'A'
! 1203: WHEN var('b') THEN 'B'
! 1204: WHEN var('c') THEN 'C'
! 1205: ELSE 'no result'
! 1206: END
! 1207: } {{no result}}
! 1208:
! 1209: # EVIDENCE-OF: R-14168-07579 If there is no ELSE expression and none of
! 1210: # the WHEN expressions are true, then the overall result is NULL.
! 1211: #
! 1212: db nullvalue null
! 1213: do_execsql_test e_expr-21.3.2 {
! 1214: SELECT CASE WHEN var('a') THEN 'A'
! 1215: WHEN var('b') THEN 'B'
! 1216: WHEN var('c') THEN 'C'
! 1217: END
! 1218: } {null}
! 1219: db nullvalue {}
! 1220:
! 1221: # EVIDENCE-OF: R-13943-13592 A NULL result is considered untrue when
! 1222: # evaluating WHEN terms.
! 1223: #
! 1224: do_execsql_test e_expr-21.4.1 {
! 1225: SELECT CASE WHEN NULL THEN 'A' WHEN 1 THEN 'B' END
! 1226: } {B}
! 1227: do_execsql_test e_expr-21.4.2 {
! 1228: SELECT CASE WHEN 0 THEN 'A' WHEN NULL THEN 'B' ELSE 'C' END
! 1229: } {C}
! 1230:
! 1231: # EVIDENCE-OF: R-38620-19499 In a CASE with a base expression, the base
! 1232: # expression is evaluated just once and the result is compared against
! 1233: # the evaluation of each WHEN expression from left to right.
! 1234: #
! 1235: # Note: This test case tests the "evaluated just once" part of the above
! 1236: # statement. Tests associated with the next two statements test that the
! 1237: # comparisons take place.
! 1238: #
! 1239: foreach {a b c} [list [expr 3] [expr 4] [expr 5]] break
! 1240: set ::varlist [list]
! 1241: do_execsql_test e_expr-22.1.1 {
! 1242: SELECT CASE var('a') WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END
! 1243: } {C}
! 1244: do_test e_expr-22.1.2 { set ::varlist } {a}
! 1245:
! 1246: # EVIDENCE-OF: R-07667-49537 The result of the CASE expression is the
! 1247: # evaluation of the THEN expression that corresponds to the first WHEN
! 1248: # expression for which the comparison is true.
! 1249: #
! 1250: do_execsql_test e_expr-22.2.1 {
! 1251: SELECT CASE 23 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
! 1252: } {B}
! 1253: do_execsql_test e_expr-22.2.2 {
! 1254: SELECT CASE 1 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
! 1255: } {A}
! 1256:
! 1257: # EVIDENCE-OF: R-47543-32145 Or, if none of the WHEN expressions
! 1258: # evaluate to a value equal to the base expression, the result of
! 1259: # evaluating the ELSE expression, if any.
! 1260: #
! 1261: do_execsql_test e_expr-22.3.1 {
! 1262: SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' ELSE 'D' END
! 1263: } {D}
! 1264:
! 1265: # EVIDENCE-OF: R-54721-48557 If there is no ELSE expression and none of
! 1266: # the WHEN expressions produce a result equal to the base expression,
! 1267: # the overall result is NULL.
! 1268: #
! 1269: do_execsql_test e_expr-22.4.1 {
! 1270: SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
! 1271: } {{}}
! 1272: db nullvalue null
! 1273: do_execsql_test e_expr-22.4.2 {
! 1274: SELECT CASE 24 WHEN 1 THEN 'A' WHEN 23 THEN 'B' WHEN 23 THEN 'C' END
! 1275: } {null}
! 1276: db nullvalue {}
! 1277:
! 1278: # EVIDENCE-OF: R-11479-62774 When comparing a base expression against a
! 1279: # WHEN expression, the same collating sequence, affinity, and
! 1280: # NULL-handling rules apply as if the base expression and WHEN
! 1281: # expression are respectively the left- and right-hand operands of an =
! 1282: # operator.
! 1283: #
! 1284: proc rev {str} {
! 1285: set ret ""
! 1286: set chars [split $str]
! 1287: for {set i [expr [llength $chars]-1]} {$i>=0} {incr i -1} {
! 1288: append ret [lindex $chars $i]
! 1289: }
! 1290: set ret
! 1291: }
! 1292: proc reverse {lhs rhs} {
! 1293: string compare [rev $lhs] [ref $rhs]
! 1294: }
! 1295: db collate reverse reverse
! 1296: do_execsql_test e_expr-23.1.1 {
! 1297: CREATE TABLE t1(
! 1298: a TEXT COLLATE NOCASE,
! 1299: b COLLATE REVERSE,
! 1300: c INTEGER,
! 1301: d BLOB
! 1302: );
! 1303: INSERT INTO t1 VALUES('abc', 'cba', 55, 34.5);
! 1304: } {}
! 1305: do_execsql_test e_expr-23.1.2 {
! 1306: SELECT CASE a WHEN 'xyz' THEN 'A' WHEN 'AbC' THEN 'B' END FROM t1
! 1307: } {B}
! 1308: do_execsql_test e_expr-23.1.3 {
! 1309: SELECT CASE 'AbC' WHEN 'abc' THEN 'A' WHEN a THEN 'B' END FROM t1
! 1310: } {B}
! 1311: do_execsql_test e_expr-23.1.4 {
! 1312: SELECT CASE a WHEN b THEN 'A' ELSE 'B' END FROM t1
! 1313: } {B}
! 1314: do_execsql_test e_expr-23.1.5 {
! 1315: SELECT CASE b WHEN a THEN 'A' ELSE 'B' END FROM t1
! 1316: } {A}
! 1317: do_execsql_test e_expr-23.1.6 {
! 1318: SELECT CASE 55 WHEN '55' THEN 'A' ELSE 'B' END
! 1319: } {B}
! 1320: do_execsql_test e_expr-23.1.7 {
! 1321: SELECT CASE c WHEN '55' THEN 'A' ELSE 'B' END FROM t1
! 1322: } {A}
! 1323: do_execsql_test e_expr-23.1.8 {
! 1324: SELECT CASE '34.5' WHEN d THEN 'A' ELSE 'B' END FROM t1
! 1325: } {B}
! 1326: do_execsql_test e_expr-23.1.9 {
! 1327: SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END
! 1328: } {B}
! 1329:
! 1330: # EVIDENCE-OF: R-37304-39405 If the base expression is NULL then the
! 1331: # result of the CASE is always the result of evaluating the ELSE
! 1332: # expression if it exists, or NULL if it does not.
! 1333: #
! 1334: do_execsql_test e_expr-24.1.1 {
! 1335: SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' END;
! 1336: } {{}}
! 1337: do_execsql_test e_expr-24.1.2 {
! 1338: SELECT CASE NULL WHEN 'abc' THEN 'A' WHEN 'def' THEN 'B' ELSE 'C' END;
! 1339: } {C}
! 1340:
! 1341: # EVIDENCE-OF: R-56280-17369 Both forms of the CASE expression use lazy,
! 1342: # or short-circuit, evaluation.
! 1343: #
! 1344: set varlist [list]
! 1345: foreach {a b c} {0 1 0} break
! 1346: do_execsql_test e_expr-25.1.1 {
! 1347: SELECT CASE WHEN var('a') THEN 'A'
! 1348: WHEN var('b') THEN 'B'
! 1349: WHEN var('c') THEN 'C'
! 1350: END
! 1351: } {B}
! 1352: do_test e_expr-25.1.2 { set ::varlist } {a b}
! 1353: set varlist [list]
! 1354: do_execsql_test e_expr-25.1.3 {
! 1355: SELECT CASE '0' WHEN var('a') THEN 'A'
! 1356: WHEN var('b') THEN 'B'
! 1357: WHEN var('c') THEN 'C'
! 1358: END
! 1359: } {A}
! 1360: do_test e_expr-25.1.4 { set ::varlist } {a}
! 1361:
! 1362: # EVIDENCE-OF: R-34773-62253 The only difference between the following
! 1363: # two CASE expressions is that the x expression is evaluated exactly
! 1364: # once in the first example but might be evaluated multiple times in the
! 1365: # second: CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END CASE WHEN
! 1366: # x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
! 1367: #
! 1368: proc ceval {x} {
! 1369: incr ::evalcount
! 1370: return $x
! 1371: }
! 1372: db func ceval ceval
! 1373: set ::evalcount 0
! 1374:
! 1375: do_execsql_test e_expr-26.1.1 {
! 1376: CREATE TABLE t2(x, w1, r1, w2, r2, r3);
! 1377: INSERT INTO t2 VALUES(1, 1, 'R1', 2, 'R2', 'R3');
! 1378: INSERT INTO t2 VALUES(2, 1, 'R1', 2, 'R2', 'R3');
! 1379: INSERT INTO t2 VALUES(3, 1, 'R1', 2, 'R2', 'R3');
! 1380: } {}
! 1381: do_execsql_test e_expr-26.1.2 {
! 1382: SELECT CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
! 1383: } {R1 R2 R3}
! 1384: do_execsql_test e_expr-26.1.3 {
! 1385: SELECT CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END FROM t2
! 1386: } {R1 R2 R3}
! 1387:
! 1388: do_execsql_test e_expr-26.1.4 {
! 1389: SELECT CASE ceval(x) WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END FROM t2
! 1390: } {R1 R2 R3}
! 1391: do_test e_expr-26.1.5 { set ::evalcount } {3}
! 1392: set ::evalcount 0
! 1393: do_execsql_test e_expr-26.1.6 {
! 1394: SELECT CASE
! 1395: WHEN ceval(x)=w1 THEN r1
! 1396: WHEN ceval(x)=w2 THEN r2
! 1397: ELSE r3 END
! 1398: FROM t2
! 1399: } {R1 R2 R3}
! 1400: do_test e_expr-26.1.6 { set ::evalcount } {5}
! 1401:
! 1402:
! 1403: #-------------------------------------------------------------------------
! 1404: # Test statements related to CAST expressions.
! 1405: #
! 1406: # EVIDENCE-OF: R-65079-31758 Application of a CAST expression is
! 1407: # different to application of a column affinity, as with a CAST
! 1408: # expression the storage class conversion is forced even if it is lossy
! 1409: # and irrreversible.
! 1410: #
! 1411: do_execsql_test e_expr-27.1.1 {
! 1412: CREATE TABLE t3(a TEXT, b REAL, c INTEGER);
! 1413: INSERT INTO t3 VALUES(X'555655', '1.23abc', 4.5);
! 1414: SELECT typeof(a), a, typeof(b), b, typeof(c), c FROM t3;
! 1415: } {blob UVU text 1.23abc real 4.5}
! 1416: do_execsql_test e_expr-27.1.2 {
! 1417: SELECT
! 1418: typeof(CAST(X'555655' as TEXT)), CAST(X'555655' as TEXT),
! 1419: typeof(CAST('1.23abc' as REAL)), CAST('1.23abc' as REAL),
! 1420: typeof(CAST(4.5 as INTEGER)), CAST(4.5 as INTEGER)
! 1421: } {text UVU real 1.23 integer 4}
! 1422:
! 1423: # EVIDENCE-OF: R-27225-65050 If the value of <expr> is NULL, then
! 1424: # the result of the CAST expression is also NULL.
! 1425: #
! 1426: do_expr_test e_expr-27.2.1 { CAST(NULL AS integer) } null {}
! 1427: do_expr_test e_expr-27.2.2 { CAST(NULL AS text) } null {}
! 1428: do_expr_test e_expr-27.2.3 { CAST(NULL AS blob) } null {}
! 1429: do_expr_test e_expr-27.2.4 { CAST(NULL AS number) } null {}
! 1430:
! 1431: # EVIDENCE-OF: R-31076-23575 Casting a value to a <type-name> with
! 1432: # no affinity causes the value to be converted into a BLOB.
! 1433: #
! 1434: do_expr_test e_expr-27.3.1 { CAST('abc' AS blob) } blob abc
! 1435: do_expr_test e_expr-27.3.2 { CAST('def' AS shobblob_x) } blob def
! 1436: do_expr_test e_expr-27.3.3 { CAST('ghi' AS abbLOb10) } blob ghi
! 1437:
! 1438: # EVIDENCE-OF: R-22956-37754 Casting to a BLOB consists of first casting
! 1439: # the value to TEXT in the encoding of the database connection, then
! 1440: # interpreting the resulting byte sequence as a BLOB instead of as TEXT.
! 1441: #
! 1442: do_qexpr_test e_expr-27.4.1 { CAST('ghi' AS blob) } X'676869'
! 1443: do_qexpr_test e_expr-27.4.2 { CAST(456 AS blob) } X'343536'
! 1444: do_qexpr_test e_expr-27.4.3 { CAST(1.78 AS blob) } X'312E3738'
! 1445: rename db db2
! 1446: sqlite3 db :memory:
! 1447: ifcapable {utf16} {
! 1448: db eval { PRAGMA encoding = 'utf-16le' }
! 1449: do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
! 1450: do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) } X'340035003600'
! 1451: do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) } X'31002E0037003800'
! 1452: }
! 1453: db close
! 1454: sqlite3 db :memory:
! 1455: db eval { PRAGMA encoding = 'utf-16be' }
! 1456: ifcapable {utf16} {
! 1457: do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
! 1458: do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) } X'003400350036'
! 1459: do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) } X'0031002E00370038'
! 1460: }
! 1461: db close
! 1462: rename db2 db
! 1463:
! 1464: # EVIDENCE-OF: R-04207-37981 To cast a BLOB value to TEXT, the sequence
! 1465: # of bytes that make up the BLOB is interpreted as text encoded using
! 1466: # the database encoding.
! 1467: #
! 1468: do_expr_test e_expr-28.1.1 { CAST (X'676869' AS text) } text ghi
! 1469: do_expr_test e_expr-28.1.2 { CAST (X'670068006900' AS text) } text g
! 1470: rename db db2
! 1471: sqlite3 db :memory:
! 1472: db eval { PRAGMA encoding = 'utf-16le' }
! 1473: ifcapable {utf16} {
! 1474: do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
! 1475: do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
! 1476: }
! 1477: db close
! 1478: rename db2 db
! 1479:
! 1480: # EVIDENCE-OF: R-22235-47006 Casting an INTEGER or REAL value into TEXT
! 1481: # renders the value as if via sqlite3_snprintf() except that the
! 1482: # resulting TEXT uses the encoding of the database connection.
! 1483: #
! 1484: do_expr_test e_expr-28.2.1 { CAST (1 AS text) } text 1
! 1485: do_expr_test e_expr-28.2.2 { CAST (45 AS text) } text 45
! 1486: do_expr_test e_expr-28.2.3 { CAST (-45 AS text) } text -45
! 1487: do_expr_test e_expr-28.2.4 { CAST (8.8 AS text) } text 8.8
! 1488: do_expr_test e_expr-28.2.5 { CAST (2.3e+5 AS text) } text 230000.0
! 1489: do_expr_test e_expr-28.2.6 { CAST (-2.3e-5 AS text) } text -2.3e-05
! 1490: do_expr_test e_expr-28.2.7 { CAST (0.0 AS text) } text 0.0
! 1491: do_expr_test e_expr-28.2.7 { CAST (0 AS text) } text 0
! 1492:
! 1493: # EVIDENCE-OF: R-26346-36443 When casting a BLOB value to a REAL, the
! 1494: # value is first converted to TEXT.
! 1495: #
! 1496: do_expr_test e_expr-29.1.1 { CAST (X'312E3233' AS REAL) } real 1.23
! 1497: do_expr_test e_expr-29.1.2 { CAST (X'3233302E30' AS REAL) } real 230.0
! 1498: do_expr_test e_expr-29.1.3 { CAST (X'2D392E3837' AS REAL) } real -9.87
! 1499: do_expr_test e_expr-29.1.4 { CAST (X'302E30303031' AS REAL) } real 0.0001
! 1500: rename db db2
! 1501: sqlite3 db :memory:
! 1502: ifcapable {utf16} {
! 1503: db eval { PRAGMA encoding = 'utf-16le' }
! 1504: do_expr_test e_expr-29.1.5 {
! 1505: CAST (X'31002E0032003300' AS REAL) } real 1.23
! 1506: do_expr_test e_expr-29.1.6 {
! 1507: CAST (X'3200330030002E003000' AS REAL) } real 230.0
! 1508: do_expr_test e_expr-29.1.7 {
! 1509: CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
! 1510: do_expr_test e_expr-29.1.8 {
! 1511: CAST (X'30002E003000300030003100' AS REAL) } real 0.0001
! 1512: }
! 1513: db close
! 1514: rename db2 db
! 1515:
! 1516: # EVIDENCE-OF: R-54898-34554 When casting a TEXT value to REAL, the
! 1517: # longest possible prefix of the value that can be interpreted as a real
! 1518: # number is extracted from the TEXT value and the remainder ignored.
! 1519: #
! 1520: do_expr_test e_expr-29.2.1 { CAST('1.23abcd' AS REAL) } real 1.23
! 1521: do_expr_test e_expr-29.2.2 { CAST('1.45.23abcd' AS REAL) } real 1.45
! 1522: do_expr_test e_expr-29.2.3 { CAST('-2.12e-01ABC' AS REAL) } real -0.212
! 1523: do_expr_test e_expr-29.2.4 { CAST('1 2 3 4' AS REAL) } real 1.0
! 1524:
! 1525: # EVIDENCE-OF: R-11321-47427 Any leading spaces in the TEXT value are
! 1526: # ignored when converging from TEXT to REAL.
! 1527: #
! 1528: do_expr_test e_expr-29.3.1 { CAST(' 1.23abcd' AS REAL) } real 1.23
! 1529: do_expr_test e_expr-29.3.2 { CAST(' 1.45.23abcd' AS REAL) } real 1.45
! 1530: do_expr_test e_expr-29.3.3 { CAST(' -2.12e-01ABC' AS REAL) } real -0.212
! 1531: do_expr_test e_expr-29.3.4 { CAST(' 1 2 3 4' AS REAL) } real 1.0
! 1532:
! 1533: # EVIDENCE-OF: R-22662-28218 If there is no prefix that can be
! 1534: # interpreted as a real number, the result of the conversion is 0.0.
! 1535: #
! 1536: do_expr_test e_expr-29.4.1 { CAST('' AS REAL) } real 0.0
! 1537: do_expr_test e_expr-29.4.2 { CAST('not a number' AS REAL) } real 0.0
! 1538: do_expr_test e_expr-29.4.3 { CAST('XXI' AS REAL) } real 0.0
! 1539:
! 1540: # EVIDENCE-OF: R-21829-14563 When casting a BLOB value to INTEGER, the
! 1541: # value is first converted to TEXT.
! 1542: #
! 1543: do_expr_test e_expr-30.1.1 { CAST(X'313233' AS INTEGER) } integer 123
! 1544: do_expr_test e_expr-30.1.2 { CAST(X'2D363738' AS INTEGER) } integer -678
! 1545: do_expr_test e_expr-30.1.3 {
! 1546: CAST(X'31303030303030' AS INTEGER)
! 1547: } integer 1000000
! 1548: do_expr_test e_expr-30.1.4 {
! 1549: CAST(X'2D31313235383939393036383432363234' AS INTEGER)
! 1550: } integer -1125899906842624
! 1551:
! 1552: rename db db2
! 1553: sqlite3 db :memory:
! 1554: ifcapable {utf16} {
! 1555: execsql { PRAGMA encoding = 'utf-16be' }
! 1556: do_expr_test e_expr-30.1.5 { CAST(X'003100320033' AS INTEGER) } integer 123
! 1557: do_expr_test e_expr-30.1.6 { CAST(X'002D003600370038' AS INTEGER) } integer -678
! 1558: do_expr_test e_expr-30.1.7 {
! 1559: CAST(X'0031003000300030003000300030' AS INTEGER)
! 1560: } integer 1000000
! 1561: do_expr_test e_expr-30.1.8 {
! 1562: CAST(X'002D0031003100320035003800390039003900300036003800340032003600320034' AS INTEGER)
! 1563: } integer -1125899906842624
! 1564: }
! 1565: db close
! 1566: rename db2 db
! 1567:
! 1568: # EVIDENCE-OF: R-47612-45842 When casting a TEXT value to INTEGER, the
! 1569: # longest possible prefix of the value that can be interpreted as an
! 1570: # integer number is extracted from the TEXT value and the remainder
! 1571: # ignored.
! 1572: #
! 1573: do_expr_test e_expr-30.2.1 { CAST('123abcd' AS INT) } integer 123
! 1574: do_expr_test e_expr-30.2.2 { CAST('14523abcd' AS INT) } integer 14523
! 1575: do_expr_test e_expr-30.2.3 { CAST('-2.12e-01ABC' AS INT) } integer -2
! 1576: do_expr_test e_expr-30.2.4 { CAST('1 2 3 4' AS INT) } integer 1
! 1577:
! 1578: # EVIDENCE-OF: R-34400-33772 Any leading spaces in the TEXT value when
! 1579: # converting from TEXT to INTEGER are ignored.
! 1580: #
! 1581: do_expr_test e_expr-30.3.1 { CAST(' 123abcd' AS INT) } integer 123
! 1582: do_expr_test e_expr-30.3.2 { CAST(' 14523abcd' AS INT) } integer 14523
! 1583: do_expr_test e_expr-30.3.3 { CAST(' -2.12e-01ABC' AS INT) } integer -2
! 1584: do_expr_test e_expr-30.3.4 { CAST(' 1 2 3 4' AS INT) } integer 1
! 1585:
! 1586: # EVIDENCE-OF: R-43164-44276 If there is no prefix that can be
! 1587: # interpreted as an integer number, the result of the conversion is 0.
! 1588: #
! 1589: do_expr_test e_expr-30.4.1 { CAST('' AS INTEGER) } integer 0
! 1590: do_expr_test e_expr-30.4.2 { CAST('not a number' AS INTEGER) } integer 0
! 1591: do_expr_test e_expr-30.4.3 { CAST('XXI' AS INTEGER) } integer 0
! 1592:
! 1593: # EVIDENCE-OF: R-00741-38776 A cast of a REAL value into an INTEGER will
! 1594: # truncate the fractional part of the REAL.
! 1595: #
! 1596: do_expr_test e_expr-31.1.1 { CAST(3.14159 AS INTEGER) } integer 3
! 1597: do_expr_test e_expr-31.1.2 { CAST(1.99999 AS INTEGER) } integer 1
! 1598: do_expr_test e_expr-31.1.3 { CAST(-1.99999 AS INTEGER) } integer -1
! 1599: do_expr_test e_expr-31.1.4 { CAST(-0.99999 AS INTEGER) } integer 0
! 1600:
! 1601: # EVIDENCE-OF: R-49503-28105 If a REAL is too large to be represented as
! 1602: # an INTEGER then the result of the cast is the largest negative
! 1603: # integer: -9223372036854775808.
! 1604: #
! 1605: do_expr_test e_expr-31.2.1 { CAST(2e+50 AS INT) } integer -9223372036854775808
! 1606: do_expr_test e_expr-31.2.2 { CAST(-2e+50 AS INT) } integer -9223372036854775808
! 1607: do_expr_test e_expr-31.2.3 {
! 1608: CAST(-9223372036854775809.0 AS INT)
! 1609: } integer -9223372036854775808
! 1610: do_expr_test e_expr-31.2.4 {
! 1611: CAST(9223372036854775809.0 AS INT)
! 1612: } integer -9223372036854775808
! 1613:
! 1614:
! 1615: # EVIDENCE-OF: R-09295-61337 Casting a TEXT or BLOB value into NUMERIC
! 1616: # first does a forced conversion into REAL but then further converts the
! 1617: # result into INTEGER if and only if the conversion from REAL to INTEGER
! 1618: # is lossless and reversible.
! 1619: #
! 1620: do_expr_test e_expr-32.1.1 { CAST('45' AS NUMERIC) } integer 45
! 1621: do_expr_test e_expr-32.1.2 { CAST('45.0' AS NUMERIC) } integer 45
! 1622: do_expr_test e_expr-32.1.3 { CAST('45.2' AS NUMERIC) } real 45.2
! 1623: do_expr_test e_expr-32.1.4 { CAST('11abc' AS NUMERIC) } integer 11
! 1624: do_expr_test e_expr-32.1.5 { CAST('11.1abc' AS NUMERIC) } real 11.1
! 1625:
! 1626: # EVIDENCE-OF: R-30347-18702 Casting a REAL or INTEGER value to NUMERIC
! 1627: # is a no-op, even if a real value could be losslessly converted to an
! 1628: # integer.
! 1629: #
! 1630: do_expr_test e_expr-32.2.1 { CAST(13.0 AS NUMERIC) } real 13.0
! 1631: do_expr_test e_expr-32.2.2 { CAST(13.5 AS NUMERIC) } real 13.5
! 1632:
! 1633: do_expr_test e_expr-32.2.3 {
! 1634: CAST(-9223372036854775808 AS NUMERIC)
! 1635: } integer -9223372036854775808
! 1636: do_expr_test e_expr-32.2.4 {
! 1637: CAST(9223372036854775807 AS NUMERIC)
! 1638: } integer 9223372036854775807
! 1639:
! 1640: # EVIDENCE-OF: R-64550-29191 Note that the result from casting any
! 1641: # non-BLOB value into a BLOB and the result from casting any BLOB value
! 1642: # into a non-BLOB value may be different depending on whether the
! 1643: # database encoding is UTF-8, UTF-16be, or UTF-16le.
! 1644: #
! 1645: ifcapable {utf16} {
! 1646: sqlite3 db1 :memory: ; db1 eval { PRAGMA encoding = 'utf-8' }
! 1647: sqlite3 db2 :memory: ; db2 eval { PRAGMA encoding = 'utf-16le' }
! 1648: sqlite3 db3 :memory: ; db3 eval { PRAGMA encoding = 'utf-16be' }
! 1649: foreach {tn castexpr differs} {
! 1650: 1 { CAST(123 AS BLOB) } 1
! 1651: 2 { CAST('' AS BLOB) } 0
! 1652: 3 { CAST('abcd' AS BLOB) } 1
! 1653:
! 1654: 4 { CAST(X'abcd' AS TEXT) } 1
! 1655: 5 { CAST(X'' AS TEXT) } 0
! 1656: } {
! 1657: set r1 [db1 eval "SELECT typeof($castexpr), quote($castexpr)"]
! 1658: set r2 [db2 eval "SELECT typeof($castexpr), quote($castexpr)"]
! 1659: set r3 [db3 eval "SELECT typeof($castexpr), quote($castexpr)"]
! 1660:
! 1661: if {$differs} {
! 1662: set res [expr {$r1!=$r2 && $r2!=$r3}]
! 1663: } else {
! 1664: set res [expr {$r1==$r2 && $r2==$r3}]
! 1665: }
! 1666:
! 1667: do_test e_expr-33.1.$tn {set res} 1
! 1668: }
! 1669: db1 close
! 1670: db2 close
! 1671: db3 close
! 1672: }
! 1673:
! 1674: #-------------------------------------------------------------------------
! 1675: # Test statements related to the EXISTS and NOT EXISTS operators.
! 1676: #
! 1677: catch { db close }
! 1678: forcedelete test.db
! 1679: sqlite3 db test.db
! 1680:
! 1681: do_execsql_test e_expr-34.1 {
! 1682: CREATE TABLE t1(a, b);
! 1683: INSERT INTO t1 VALUES(1, 2);
! 1684: INSERT INTO t1 VALUES(NULL, 2);
! 1685: INSERT INTO t1 VALUES(1, NULL);
! 1686: INSERT INTO t1 VALUES(NULL, NULL);
! 1687: } {}
! 1688:
! 1689: # EVIDENCE-OF: R-25588-27181 The EXISTS operator always evaluates to one
! 1690: # of the integer values 0 and 1.
! 1691: #
! 1692: # This statement is not tested by itself. Instead, all e_expr-34.* tests
! 1693: # following this point explicitly test that specific invocations of EXISTS
! 1694: # return either integer 0 or integer 1.
! 1695: #
! 1696:
! 1697: # EVIDENCE-OF: R-58553-63740 If executing the SELECT statement specified
! 1698: # as the right-hand operand of the EXISTS operator would return one or
! 1699: # more rows, then the EXISTS operator evaluates to 1.
! 1700: #
! 1701: foreach {tn expr} {
! 1702: 1 { EXISTS ( SELECT a FROM t1 ) }
! 1703: 2 { EXISTS ( SELECT b FROM t1 ) }
! 1704: 3 { EXISTS ( SELECT 24 ) }
! 1705: 4 { EXISTS ( SELECT NULL ) }
! 1706: 5 { EXISTS ( SELECT a FROM t1 WHERE a IS NULL ) }
! 1707: } {
! 1708: do_expr_test e_expr-34.2.$tn $expr integer 1
! 1709: }
! 1710:
! 1711: # EVIDENCE-OF: R-19673-40972 If executing the SELECT would return no
! 1712: # rows at all, then the EXISTS operator evaluates to 0.
! 1713: #
! 1714: foreach {tn expr} {
! 1715: 1 { EXISTS ( SELECT a FROM t1 WHERE 0) }
! 1716: 2 { EXISTS ( SELECT b FROM t1 WHERE a = 5) }
! 1717: 3 { EXISTS ( SELECT 24 WHERE 0) }
! 1718: 4 { EXISTS ( SELECT NULL WHERE 1=2) }
! 1719: } {
! 1720: do_expr_test e_expr-34.3.$tn $expr integer 0
! 1721: }
! 1722:
! 1723: # EVIDENCE-OF: R-35109-49139 The number of columns in each row returned
! 1724: # by the SELECT statement (if any) and the specific values returned have
! 1725: # no effect on the results of the EXISTS operator.
! 1726: #
! 1727: foreach {tn expr res} {
! 1728: 1 { EXISTS ( SELECT * FROM t1 ) } 1
! 1729: 2 { EXISTS ( SELECT *, *, * FROM t1 ) } 1
! 1730: 3 { EXISTS ( SELECT 24, 25 ) } 1
! 1731: 4 { EXISTS ( SELECT NULL, NULL, NULL ) } 1
! 1732: 5 { EXISTS ( SELECT a,b,a||b FROM t1 WHERE a IS NULL ) } 1
! 1733:
! 1734: 6 { EXISTS ( SELECT a, a FROM t1 WHERE 0) } 0
! 1735: 7 { EXISTS ( SELECT b, b, a FROM t1 WHERE a = 5) } 0
! 1736: 8 { EXISTS ( SELECT 24, 46, 89 WHERE 0) } 0
! 1737: 9 { EXISTS ( SELECT NULL, NULL WHERE 1=2) } 0
! 1738: } {
! 1739: do_expr_test e_expr-34.4.$tn $expr integer $res
! 1740: }
! 1741:
! 1742: # EVIDENCE-OF: R-10645-12439 In particular, rows containing NULL values
! 1743: # are not handled any differently from rows without NULL values.
! 1744: #
! 1745: foreach {tn e1 e2} {
! 1746: 1 { EXISTS (SELECT 'not null') } { EXISTS (SELECT NULL) }
! 1747: 2 { EXISTS (SELECT NULL FROM t1) } { EXISTS (SELECT 'bread' FROM t1) }
! 1748: } {
! 1749: set res [db one "SELECT $e1"]
! 1750: do_expr_test e_expr-34.5.${tn}a $e1 integer $res
! 1751: do_expr_test e_expr-34.5.${tn}b $e2 integer $res
! 1752: }
! 1753:
! 1754: #-------------------------------------------------------------------------
! 1755: # Test statements related to scalar sub-queries.
! 1756: #
! 1757:
! 1758: catch { db close }
! 1759: forcedelete test.db
! 1760: sqlite3 db test.db
! 1761: do_test e_expr-35.0 {
! 1762: execsql {
! 1763: CREATE TABLE t2(a, b);
! 1764: INSERT INTO t2 VALUES('one', 'two');
! 1765: INSERT INTO t2 VALUES('three', NULL);
! 1766: INSERT INTO t2 VALUES(4, 5.0);
! 1767: }
! 1768: } {}
! 1769:
! 1770: # EVIDENCE-OF: R-00980-39256 A SELECT statement enclosed in parentheses
! 1771: # may appear as a scalar quantity.
! 1772: #
! 1773: # EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
! 1774: # aggregate and compound SELECT queries (queries with keywords like
! 1775: # UNION or EXCEPT) are allowed as scalar subqueries.
! 1776: #
! 1777: do_expr_test e_expr-35.1.1 { (SELECT 35) } integer 35
! 1778: do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
! 1779:
! 1780: do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
! 1781: do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
! 1782:
! 1783: do_expr_test e_expr-35.1.5 {
! 1784: (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
! 1785: } null {}
! 1786: do_expr_test e_expr-35.1.6 {
! 1787: (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
! 1788: } integer 4
! 1789:
! 1790: # EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must
! 1791: # return a result set with a single column.
! 1792: #
! 1793: # The following block tests that errors are returned in a bunch of cases
! 1794: # where a subquery returns more than one column.
! 1795: #
! 1796: set M {only a single result allowed for a SELECT that is part of an expression}
! 1797: foreach {tn sql} {
! 1798: 1 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
! 1799: 2 { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
! 1800: 3 { SELECT (SELECT 1, 2) }
! 1801: 4 { SELECT (SELECT NULL, NULL, NULL) }
! 1802: 5 { SELECT (SELECT * FROM t2) }
! 1803: 6 { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
! 1804: } {
! 1805: do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M]
! 1806: }
! 1807:
! 1808: # EVIDENCE-OF: R-35764-28041 The result of the expression is the value
! 1809: # of the only column in the first row returned by the SELECT statement.
! 1810: #
! 1811: # EVIDENCE-OF: R-41898-06686 If the SELECT yields more than one result
! 1812: # row, all rows after the first are ignored.
! 1813: #
! 1814: do_execsql_test e_expr-36.3.1 {
! 1815: CREATE TABLE t4(x, y);
! 1816: INSERT INTO t4 VALUES(1, 'one');
! 1817: INSERT INTO t4 VALUES(2, 'two');
! 1818: INSERT INTO t4 VALUES(3, 'three');
! 1819: } {}
! 1820:
! 1821: foreach {tn expr restype resval} {
! 1822: 2 { ( SELECT x FROM t4 ORDER BY x ) } integer 1
! 1823: 3 { ( SELECT x FROM t4 ORDER BY y ) } integer 1
! 1824: 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3
! 1825: 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2
! 1826: 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two
! 1827:
! 1828: 7 { ( SELECT sum(x) FROM t4 ) } integer 6
! 1829: 8 { ( SELECT group_concat(y,'') FROM t4 ) } text onetwothree
! 1830: 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2
! 1831:
! 1832: } {
! 1833: do_expr_test e_expr-36.3.$tn $expr $restype $resval
! 1834: }
! 1835:
! 1836: # EVIDENCE-OF: R-25492-41572 If the SELECT yields no rows, then the
! 1837: # value of the expression is NULL.
! 1838: #
! 1839: foreach {tn expr} {
! 1840: 1 { ( SELECT x FROM t4 WHERE x>3 ORDER BY x ) }
! 1841: 2 { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y ) }
! 1842: } {
! 1843: do_expr_test e_expr-36.4.$tn $expr null {}
! 1844: }
! 1845:
! 1846:
! 1847: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_expr.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test