# 2010 July 16
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements tests to verify that the "testable statements" in
# the lang_expr.html document are correct.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/malloc_common.tcl
# Set up three global variables:
#
# ::opname An array mapping from SQL operator to an easy to parse
# name. The names are used as part of test case names.
#
# ::opprec An array mapping from SQL operator to a numeric
# precedence value. Operators that group more tightly
# have lower numeric precedences.
#
# ::oplist A list of all SQL operators supported by SQLite.
#
foreach {op opn} {
|| cat * mul / div % mod + add
- sub << lshift >> rshift & bitand | bitor
< less <= lesseq > more >= moreeq = eq1
== eq2 <> ne1 != ne2 IS is LIKE like
GLOB glob AND and OR or MATCH match REGEXP regexp
{IS NOT} isnt
} {
set ::opname($op) $opn
}
set oplist [list]
foreach {prec opl} {
1 ||
2 {* / %}
3 {+ -}
4 {<< >> & |}
5 {< <= > >=}
6 {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
7 AND
8 OR
} {
foreach op $opl {
set ::opprec($op) $prec
lappend oplist $op
}
}
# Hook in definitions of MATCH and REGEX. The following implementations
# cause MATCH and REGEX to behave similarly to the == operator.
#
proc matchfunc {a b} { return [expr {$a==$b}] }
proc regexfunc {a b} { return [expr {$a==$b}] }
db func match -argcount 2 matchfunc
db func regexp -argcount 2 regexfunc
#-------------------------------------------------------------------------
# Test cases e_expr-1.* attempt to verify that all binary operators listed
# in the documentation exist and that the relative precedences of the
# operators are also as the documentation suggests.
#
# EVIDENCE-OF: R-15514-65163 SQLite understands the following binary
# operators, in order from highest to lowest precedence: || * / % + -
# << >> & | < <= > >= = == != <> IS IS
# NOT IN LIKE GLOB MATCH REGEXP AND OR
#
# EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same
# precedence as =.
#
unset -nocomplain untested
foreach op1 $oplist {
foreach op2 $oplist {
set untested($op1,$op2) 1
foreach {tn A B C} {
1 22 45 66
2 0 0 0
3 0 0 1
4 0 1 0
5 0 1 1
6 1 0 0
7 1 0 1
8 1 1 0
9 1 1 1
10 5 6 1
11 1 5 6
12 1 5 5
13 5 5 1
14 5 2 1
15 1 4 1
16 -1 0 1
17 0 1 -1
} {
set testname "e_expr-1.$opname($op1).$opname($op2).$tn"
# If $op2 groups more tightly than $op1, then the result
# of executing $sql1 whould be the same as executing $sql3.
# If $op1 groups more tightly, or if $op1 and $op2 have
# the same precedence, then executing $sql1 should return
# the same value as $sql2.
#
set sql1 "SELECT $A $op1 $B $op2 $C"
set sql2 "SELECT ($A $op1 $B) $op2 $C"
set sql3 "SELECT $A $op1 ($B $op2 $C)"
set a2 [db one $sql2]
set a3 [db one $sql3]
do_execsql_test $testname $sql1 [list [
if {$opprec($op2) < $opprec($op1)} {set a3} {set a2}
]]
if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) }
}
}
}
foreach op {* AND OR + || & |} { unset untested($op,$op) }
unset untested(+,-) ;# Since (a+b)-c == a+(b-c)
unset untested(*,<<) ;# Since (a*b)<<c == a*(b<<c)
do_test e_expr-1.1 { array names untested } {}
# At one point, test 1.2.2 was failing. Instead of the correct result, it
# was returning {1 1 0}. This would seem to indicate that LIKE has the
# same precedence as '<'. Which is incorrect. It has lower precedence.
#
do_execsql_test e_expr-1.2.1 {
SELECT 0 < 2 LIKE 1, (0 < 2) LIKE 1, 0 < (2 LIKE 1)
} {1 1 0}
do_execsql_test e_expr-1.2.2 {
SELECT 0 LIKE 0 < 2, (0 LIKE 0) < 2, 0 LIKE (0 < 2)
} {0 1 0}
# Showing that LIKE and == have the same precedence
#
do_execsql_test e_expr-1.2.3 {
SELECT 2 LIKE 2 == 1, (2 LIKE 2) == 1, 2 LIKE (2 == 1)
} {1 1 0}
do_execsql_test e_expr-1.2.4 {
SELECT 2 == 2 LIKE 1, (2 == 2) LIKE 1, 2 == (2 LIKE 1)
} {1 1 0}
# Showing that < groups more tightly than == (< has higher precedence).
#
do_execsql_test e_expr-1.2.5 {
SELECT 0 < 2 == 1, (0 < 2) == 1, 0 < (2 == 1)
} {1 1 0}
do_execsql_test e_expr-1.6 {
SELECT 0 == 0 < 2, (0 == 0) < 2, 0 == (0 < 2)
} {0 1 0}
#-------------------------------------------------------------------------
# Check that the four unary prefix operators mentioned in the
# documentation exist.
#
# EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these:
# - + ~ NOT
#
do_execsql_test e_expr-2.1 { SELECT - 10 } {-10}
do_execsql_test e_expr-2.2 { SELECT + 10 } {10}
do_execsql_test e_expr-2.3 { SELECT ~ 10 } {-11}
do_execsql_test e_expr-2.4 { SELECT NOT 10 } {0}
#-------------------------------------------------------------------------
# Tests for the two statements made regarding the unary + operator.
#
# EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op.
#
# EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers,
# blobs or NULL and it always returns a result with the same value as
# the operand.
#
foreach {tn literal type} {
1 'helloworld' text
2 45 integer
3 45.2 real
4 45.0 real
5 X'ABCDEF' blob
6 NULL null
} {
set sql " SELECT quote( + $literal ), typeof( + $literal) "
do_execsql_test e_expr-3.$tn $sql [list $literal $type]
}
#-------------------------------------------------------------------------
# Check that both = and == are both acceptable as the "equals" operator.
# Similarly, either != or <> work as the not-equals operator.
#
# EVIDENCE-OF: R-03679-60639 Equals can be either = or ==.
#
# EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or
# <>.
#
foreach {tn literal different} {
1 'helloworld' '12345'
2 22 23
3 'xyz' X'78797A'
4 X'78797A00' 'xyz'
} {
do_execsql_test e_expr-4.$tn "
SELECT $literal = $literal, $literal == $literal,
$literal = $different, $literal == $different,
$literal = NULL, $literal == NULL,
$literal != $literal, $literal <> $literal,
$literal != $different, $literal <> $different,
$literal != NULL, $literal != NULL
" {1 1 0 0 {} {} 0 0 1 1 {} {}}
}
#-------------------------------------------------------------------------
# Test the || operator.
#
# EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins
# together the two strings of its operands.
#
foreach {tn a b} {
1 'helloworld' '12345'
2 22 23
} {
set as [db one "SELECT $a"]
set bs [db one "SELECT $b"]
do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"]
}
#-------------------------------------------------------------------------
# Test the % operator.
#
# EVIDENCE-OF: R-08914-63790 The operator % outputs the value of its
# left operand modulo its right operand.
#
do_execsql_test e_expr-6.1 {SELECT 72%5} {2}
do_execsql_test e_expr-6.2 {SELECT 72%-5} {2}
do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2}
do_execsql_test e_expr-6.4 {SELECT -72%5} {-2}
#-------------------------------------------------------------------------
# Test that the results of all binary operators are either numeric or
# NULL, except for the || operator, which may evaluate to either a text
# value or NULL.
#
# EVIDENCE-OF: R-20665-17792 The result of any binary operator is either
# a numeric value or NULL, except for the || concatenation operator
# which always evaluates to either NULL or a text value.
#
set literals {
1 'abc' 2 'hexadecimal' 3 ''
4 123 5 -123 6 0
7 123.4 8 0.0 9 -123.4
10 X'ABCDEF' 11 X'' 12 X'0000'
13 NULL
}
foreach op $oplist {
foreach {n1 rhs} $literals {
foreach {n2 lhs} $literals {
set t [db one " SELECT typeof($lhs $op $rhs) "]
do_test e_expr-7.$opname($op).$n1.$n2 {
expr {
($op=="||" && ($t == "text" || $t == "null"))
|| ($op!="||" && ($t == "integer" || $t == "real" || $t == "null"))
}
} 1
}}
}
#-------------------------------------------------------------------------
# Test the IS and IS NOT operators.
#
# EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and
# != except when one or both of the operands are NULL.
#
# EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL,
# then the IS operator evaluates to 1 (true) and the IS NOT operator
# evaluates to 0 (false).
#
# EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is
# not, then the IS operator evaluates to 0 (false) and the IS NOT
# operator is 1 (true).
#
# EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT
# expression to evaluate to NULL.
#
do_execsql_test e_expr-8.1.1 { SELECT NULL IS NULL } {1}
do_execsql_test e_expr-8.1.2 { SELECT 'ab' IS NULL } {0}
do_execsql_test e_expr-8.1.3 { SELECT NULL IS 'ab' } {0}
do_execsql_test e_expr-8.1.4 { SELECT 'ab' IS 'ab' } {1}
do_execsql_test e_expr-8.1.5 { SELECT NULL == NULL } {{}}
do_execsql_test e_expr-8.1.6 { SELECT 'ab' == NULL } {{}}
do_execsql_test e_expr-8.1.7 { SELECT NULL == 'ab' } {{}}
do_execsql_test e_expr-8.1.8 { SELECT 'ab' == 'ab' } {1}
do_execsql_test e_expr-8.1.9 { SELECT NULL IS NOT NULL } {0}
do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1}
do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1}
do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0}
do_execsql_test e_expr-8.1.13 { SELECT NULL != NULL } {{}}
do_execsql_test e_expr-8.1.14 { SELECT 'ab' != NULL } {{}}
do_execsql_test e_expr-8.1.15 { SELECT NULL != 'ab' } {{}}
do_execsql_test e_expr-8.1.16 { SELECT 'ab' != 'ab' } {0}
foreach {n1 rhs} $literals {
foreach {n2 lhs} $literals {
if {$rhs!="NULL" && $lhs!="NULL"} {
set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"]
} else {
set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \
[expr {$lhs!="NULL" || $rhs!="NULL"}]
]
}
set test e_expr-8.2.$n1.$n2
do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq
do_execsql_test $test.2 "
SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL
" {0 0}
}
}
#-------------------------------------------------------------------------
# Run some tests on the COLLATE "unary postfix operator".
#
# This collation sequence reverses both arguments before using
# [string compare] to compare them. For example, when comparing the
# strings 'one' and 'four', return the result of:
#
# string compare eno ruof
#
proc reverse_str {zStr} {
set out ""
foreach c [split $zStr {}] { set out "${c}${out}" }
set out
}
proc reverse_collate {zLeft zRight} {
string compare [reverse_str $zLeft] [reverse_str $zRight]
}
db collate reverse reverse_collate
# EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix
# operator that assigns a collating sequence to an expression.
#
# EVIDENCE-OF: R-23441-22541 The COLLATE operator has a higher
# precedence (binds more tightly) than any prefix unary operator or any
# binary operator.
#
do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0
do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1
do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0
do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1
do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1
do_execsql_test e_expr-9.6 { SELECT ('abcd' > 'bbbb') COLLATE reverse } 0
do_execsql_test e_expr-9.7 { SELECT 'abcd' >= 'bbbb' COLLATE reverse } 1
do_execsql_test e_expr-9.8 { SELECT ('abcd' >= 'bbbb') COLLATE reverse } 0
do_execsql_test e_expr-9.10 { SELECT 'abcd' = 'ABCD' COLLATE nocase } 1
do_execsql_test e_expr-9.11 { SELECT ('abcd' = 'ABCD') COLLATE nocase } 0
do_execsql_test e_expr-9.12 { SELECT 'abcd' == 'ABCD' COLLATE nocase } 1
do_execsql_test e_expr-9.13 { SELECT ('abcd' == 'ABCD') COLLATE nocase } 0
do_execsql_test e_expr-9.14 { SELECT 'abcd' IS 'ABCD' COLLATE nocase } 1
do_execsql_test e_expr-9.15 { SELECT ('abcd' IS 'ABCD') COLLATE nocase } 0
do_execsql_test e_expr-9.16 { SELECT 'abcd' != 'ABCD' COLLATE nocase } 0
do_execsql_test e_expr-9.17 { SELECT ('abcd' != 'ABCD') COLLATE nocase } 1
do_execsql_test e_expr-9.18 { SELECT 'abcd' <> 'ABCD' COLLATE nocase } 0
do_execsql_test e_expr-9.19 { SELECT ('abcd' <> 'ABCD') COLLATE nocase } 1
do_execsql_test e_expr-9.20 { SELECT 'abcd' IS NOT 'ABCD' COLLATE nocase } 0
do_execsql_test e_expr-9.21 { SELECT ('abcd' IS NOT 'ABCD') COLLATE nocase } 1
do_execsql_test e_expr-9.22 {
SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE nocase
} 1
do_execsql_test e_expr-9.23 {
SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE nocase
} 0
# EVIDENCE-OF: R-58731-25439 The collating sequence set by the COLLATE
# operator overrides the collating sequence determined by the COLLATE
# clause in a table column definition.
#
do_execsql_test e_expr-9.24 {
CREATE TABLE t24(a COLLATE NOCASE, b);
INSERT INTO t24 VALUES('aaa', 1);
INSERT INTO t24 VALUES('bbb', 2);
INSERT INTO t24 VALUES('ccc', 3);
} {}
do_execsql_test e_expr-9.25 { SELECT 'BBB' = a FROM t24 } {0 1 0}
do_execsql_test e_expr-9.25 { SELECT a = 'BBB' FROM t24 } {0 1 0}
do_execsql_test e_expr-9.25 { SELECT 'BBB' = a COLLATE binary FROM t24 } {0 0 0}
do_execsql_test e_expr-9.25 { SELECT a COLLATE binary = 'BBB' FROM t24 } {0 0 0}
#-------------------------------------------------------------------------
# Test statements related to literal values.
#
# EVIDENCE-OF: R-31536-32008 Literal values may be integers, floating
# point numbers, strings, BLOBs, or NULLs.
#
do_execsql_test e_expr-10.1.1 { SELECT typeof(5) } {integer}
do_execsql_test e_expr-10.1.2 { SELECT typeof(5.1) } {real}
do_execsql_test e_expr-10.1.3 { SELECT typeof('5.1') } {text}
do_execsql_test e_expr-10.1.4 { SELECT typeof(X'ABCD') } {blob}
do_execsql_test e_expr-10.1.5 { SELECT typeof(NULL) } {null}
# EVIDENCE-OF: R-26921-59298 Scientific notation is supported for
# floating point literal values.
#
do_execsql_test e_expr-10.2.1 { SELECT typeof(3.4e-02) } {real}
do_execsql_test e_expr-10.2.2 { SELECT typeof(3e+5) } {real}
do_execsql_test e_expr-10.2.3 { SELECT 3.4e-02 } {0.034}
do_execsql_test e_expr-10.2.4 { SELECT 3e+4 } {30000.0}
# EVIDENCE-OF: R-35229-17830 A string constant is formed by enclosing
# the string in single quotes (').
#
# EVIDENCE-OF: R-07100-06606 A single quote within the string can be
# encoded by putting two single quotes in a row - as in Pascal.
#
do_execsql_test e_expr-10.3.1 { SELECT 'is not' } {{is not}}
do_execsql_test e_expr-10.3.2 { SELECT typeof('is not') } {text}
do_execsql_test e_expr-10.3.3 { SELECT 'isn''t' } {isn't}
do_execsql_test e_expr-10.3.4 { SELECT typeof('isn''t') } {text}
# EVIDENCE-OF: R-09593-03321 BLOB literals are string literals
# containing hexadecimal data and preceded by a single "x" or "X"
# character.
#
# EVIDENCE-OF: R-39344-59787 For example: X'53514C697465'
#
do_execsql_test e_expr-10.4.1 { SELECT typeof(X'0123456789ABCDEF') } blob
do_execsql_test e_expr-10.4.2 { SELECT typeof(x'0123456789ABCDEF') } blob
do_execsql_test e_expr-10.4.3 { SELECT typeof(X'0123456789abcdef') } blob
do_execsql_test e_expr-10.4.4 { SELECT typeof(x'0123456789abcdef') } blob
do_execsql_test e_expr-10.4.5 { SELECT typeof(X'53514C697465') } blob
# EVIDENCE-OF: R-23914-51476 A literal value can also be the token
# "NULL".
#
do_execsql_test e_expr-10.5.1 { SELECT NULL } {{}}
do_execsql_test e_expr-10.5.2 { SELECT typeof(NULL) } {null}
#-------------------------------------------------------------------------
# Test statements related to bound parameters
#
proc parameter_test {tn sql params result} {
set stmt [sqlite3_prepare_v2 db $sql -1]
foreach {number name} $params {
set nm [sqlite3_bind_parameter_name $stmt $number]
do_test $tn.name.$number [list set {} $nm] $name
sqlite3_bind_int $stmt $number [expr -1 * $number]
}
sqlite3_step $stmt
set res [list]
for {set i 0} {$i < [sqlite3_column_count $stmt]} {incr i} {
lappend res [sqlite3_column_text $stmt $i]
}
set rc [sqlite3_finalize $stmt]
do_test $tn.rc [list set {} $rc] SQLITE_OK
do_test $tn.res [list set {} $res] $result
}
# EVIDENCE-OF: R-33509-39458 A question mark followed by a number NNN
# holds a spot for the NNN-th parameter. NNN must be between 1 and
# SQLITE_MAX_VARIABLE_NUMBER.
#
set mvn $SQLITE_MAX_VARIABLE_NUMBER
parameter_test e_expr-11.1 "
SELECT ?1, ?123, ?$SQLITE_MAX_VARIABLE_NUMBER, ?123, ?4
" "1 ?1 123 ?123 $mvn ?$mvn 4 ?4" "-1 -123 -$mvn -123 -4"
set errmsg "variable number must be between ?1 and ?$SQLITE_MAX_VARIABLE_NUMBER"
foreach {tn param_number} [list \
2 0 \
3 [expr $SQLITE_MAX_VARIABLE_NUMBER+1] \
4 [expr $SQLITE_MAX_VARIABLE_NUMBER+2] \
5 12345678903456789034567890234567890 \
6 2147483648 \
7 2147483649 \
8 4294967296 \
9 4294967297 \
10 9223372036854775808 \
11 9223372036854775809 \
12 18446744073709551616 \
13 18446744073709551617 \
] {
do_catchsql_test e_expr-11.1.$tn "SELECT ?$param_number" [list 1 $errmsg]
}
# EVIDENCE-OF: R-33670-36097 A question mark that is not followed by a
# number creates a parameter with a number one greater than the largest
# parameter number already assigned.
#
# EVIDENCE-OF: R-42938-07030 If this means the parameter number is
# greater than SQLITE_MAX_VARIABLE_NUMBER, it is an error.
#
parameter_test e_expr-11.2.1 "SELECT ?" {1 {}} -1
parameter_test e_expr-11.2.2 "SELECT ?, ?" {1 {} 2 {}} {-1 -2}
parameter_test e_expr-11.2.3 "SELECT ?5, ?" {5 ?5 6 {}} {-5 -6}
parameter_test e_expr-11.2.4 "SELECT ?, ?5" {1 {} 5 ?5} {-1 -5}
parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?" {
1 {} 456 ?456 457 {}
} {-1 -456 -457}
parameter_test e_expr-11.2.5 "SELECT ?, ?456, ?4, ?" {
1 {} 456 ?456 4 ?4 457 {}
} {-1 -456 -4 -457}
foreach {tn sql} [list \
1 "SELECT ?$mvn, ?" \
2 "SELECT ?[expr $mvn-5], ?, ?, ?, ?, ?, ?" \
3 "SELECT ?[expr $mvn], ?5, ?6, ?" \
] {
do_catchsql_test e_expr-11.3.$tn $sql [list 1 {too many SQL variables}]
}
# EVIDENCE-OF: R-11620-22743 A colon followed by an identifier name
# holds a spot for a named parameter with the name :AAAA.
#
# Identifiers in SQLite consist of alphanumeric, '_' and '$' characters,
# and any UTF characters with codepoints larger than 127 (non-ASCII
# characters).
#
parameter_test e_expr-11.2.1 {SELECT :AAAA} {1 :AAAA} -1
parameter_test e_expr-11.2.2 {SELECT :123} {1 :123} -1
parameter_test e_expr-11.2.3 {SELECT :__} {1 :__} -1
parameter_test e_expr-11.2.4 {SELECT :_$_} {1 :_$_} -1
parameter_test e_expr-11.2.5 "
SELECT :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
" "1 :\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
parameter_test e_expr-11.2.6 "SELECT :\u0080" "1 :\u0080" -1
# EVIDENCE-OF: R-49783-61279 An "at" sign works exactly like a colon,
# except that the name of the parameter created is @AAAA.
#
parameter_test e_expr-11.3.1 {SELECT @AAAA} {1 @AAAA} -1
parameter_test e_expr-11.3.2 {SELECT @123} {1 @123} -1
parameter_test e_expr-11.3.3 {SELECT @__} {1 @__} -1
parameter_test e_expr-11.3.4 {SELECT @_$_} {1 @_$_} -1
parameter_test e_expr-11.3.5 "
SELECT @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
" "1 @\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
parameter_test e_expr-11.3.6 "SELECT @\u0080" "1 @\u0080" -1
# EVIDENCE-OF: R-62610-51329 A dollar-sign followed by an identifier
# name also holds a spot for a named parameter with the name $AAAA.
#
# EVIDENCE-OF: R-55025-21042 The identifier name in this case can
# include one or more occurrences of "::" and a suffix enclosed in
# "(...)" containing any text at all.
#
# Note: Looks like an identifier cannot consist entirely of "::"
# characters or just a suffix. Also, the other named variable characters
# (: and @) work the same way internally. Why not just document it that way?
#
parameter_test e_expr-11.4.1 {SELECT $AAAA} {1 $AAAA} -1
parameter_test e_expr-11.4.2 {SELECT $123} {1 $123} -1
parameter_test e_expr-11.4.3 {SELECT $__} {1 $__} -1
parameter_test e_expr-11.4.4 {SELECT $_$_} {1 $_$_} -1
parameter_test e_expr-11.4.5 "
SELECT \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25
" "1 \$\u0e40\u0e2d\u0e28\u0e02\u0e39\u0e40\u0e2d\u0e25" -1
parameter_test e_expr-11.4.6 "SELECT \$\u0080" "1 \$\u0080" -1
parameter_test e_expr-11.5.1 {SELECT $::::a(++--++)} {1 $::::a(++--++)} -1
parameter_test e_expr-11.5.2 {SELECT $::a()} {1 $::a()} -1
parameter_test e_expr-11.5.3 {SELECT $::1(::#$)} {1 $::1(::#$)} -1
# EVIDENCE-OF: R-11370-04520 Named parameters are also numbered. The
# number assigned is one greater than the largest parameter number
# already assigned.
#
# EVIDENCE-OF: R-42620-22184 If this means the parameter would be
# assigned a number greater than SQLITE_MAX_VARIABLE_NUMBER, it is an
# error.
#
parameter_test e_expr-11.6.1 "SELECT ?, @abc" {1 {} 2 @abc} {-1 -2}
parameter_test e_expr-11.6.2 "SELECT ?123, :a1" {123 ?123 124 :a1} {-123 -124}
parameter_test e_expr-11.6.3 {SELECT $a, ?8, ?, $b, ?2, $c} {
1 $a 8 ?8 9 {} 10 $b 2 ?2 11 $c
} {-1 -8 -9 -10 -2 -11}
foreach {tn sql} [list \
1 "SELECT ?$mvn, \$::a" \
2 "SELECT ?$mvn, ?4, @a1" \
3 "SELECT ?[expr $mvn-2], :bag, @123, \$x" \
] {
do_catchsql_test e_expr-11.7.$tn $sql [list 1 {too many SQL variables}]
}
# EVIDENCE-OF: R-14068-49671 Parameters that are not assigned values
# using sqlite3_bind() are treated as NULL.
#
do_test e_expr-11.7.1 {
set stmt [sqlite3_prepare_v2 db { SELECT ?, :a, @b, $d } -1]
sqlite3_step $stmt
list [sqlite3_column_type $stmt 0] \
[sqlite3_column_type $stmt 1] \
[sqlite3_column_type $stmt 2] \
[sqlite3_column_type $stmt 3]
} {NULL NULL NULL NULL}
do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK
#-------------------------------------------------------------------------
# "Test" the syntax diagrams in lang_expr.html.
#
# EVIDENCE-OF: R-04177-20688 -- syntax diagram signed-number
#
do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0}
do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1}
do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2}
do_execsql_test e_expr-12.1.4 {
SELECT 1.4, +1.4, -1.4
} {1.4 1.4 -1.4}
do_execsql_test e_expr-12.1.5 {
SELECT 1.5e+5, +1.5e+5, -1.5e+5
} {150000.0 150000.0 -150000.0}
do_execsql_test e_expr-12.1.6 {
SELECT 0.0001, +0.0001, -0.0001
} {0.0001 0.0001 -0.0001}
# EVIDENCE-OF: R-30740-26723 -- syntax diagram literal-value
#
set sqlite_current_time 1
do_execsql_test e_expr-12.2.1 {SELECT 123} {123}
do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0}
do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde}
do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC}
do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}}
do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01}
do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01}
do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}}
set sqlite_current_time 0
# EVIDENCE-OF: R-57598-59332 -- syntax diagram expr
#
file delete -force test.db2
execsql {
ATTACH 'test.db2' AS dbname;
CREATE TABLE dbname.tblname(cname);
}
proc glob {args} {return 1}
db function glob glob
db function match glob
db function regexp glob
foreach {tn expr} {
1 123
2 123.4e05
3 'abcde'
4 X'414243'
5 NULL
6 CURRENT_TIME
7 CURRENT_DATE
8 CURRENT_TIMESTAMP
9 ?
10 ?123
11 @hello
12 :world
13 $tcl
14 $tcl(array)
15 cname
16 tblname.cname
17 dbname.tblname.cname
18 "+ EXPR"
19 "- EXPR"
20 "NOT EXPR"
21 "~ EXPR"
22 "EXPR1 || EXPR2"
23 "EXPR1 * EXPR2"
24 "EXPR1 / EXPR2"
25 "EXPR1 % EXPR2"
26 "EXPR1 + EXPR2"
27 "EXPR1 - EXPR2"
28 "EXPR1 << EXPR2"
29 "EXPR1 >> EXPR2"
30 "EXPR1 & EXPR2"
31 "EXPR1 | EXPR2"
32 "EXPR1 < EXPR2"
33 "EXPR1 <= EXPR2"
34 "EXPR1 > EXPR2"
35 "EXPR1 >= EXPR2"
36 "EXPR1 = EXPR2"
37 "EXPR1 == EXPR2"
38 "EXPR1 != EXPR2"
39 "EXPR1 <> EXPR2"
40 "EXPR1 IS EXPR2"
41 "EXPR1 IS NOT EXPR2"
42 "EXPR1 AND EXPR2"
43 "EXPR1 OR EXPR2"
44 "count(*)"
45 "count(DISTINCT EXPR)"
46 "substr(EXPR, 10, 20)"
47 "changes()"
48 "( EXPR )"
49 "CAST ( EXPR AS integer )"
50 "CAST ( EXPR AS 'abcd' )"
51 "CAST ( EXPR AS 'ab$ $cd' )"
52 "EXPR COLLATE nocase"
53 "EXPR COLLATE binary"
54 "EXPR1 LIKE EXPR2"
55 "EXPR1 LIKE EXPR2 ESCAPE EXPR"
56 "EXPR1 GLOB EXPR2"
57 "EXPR1 GLOB EXPR2 ESCAPE EXPR"
58 "EXPR1 REGEXP EXPR2"
59 "EXPR1 REGEXP EXPR2 ESCAPE EXPR"
60 "EXPR1 MATCH EXPR2"
61 "EXPR1 MATCH EXPR2 ESCAPE EXPR"
62 "EXPR1 NOT LIKE EXPR2"
63 "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"
64 "EXPR1 NOT GLOB EXPR2"
65 "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"
66 "EXPR1 NOT REGEXP EXPR2"
67 "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"
68 "EXPR1 NOT MATCH EXPR2"
69 "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"
70 "EXPR ISNULL"
71 "EXPR NOTNULL"
72 "EXPR NOT NULL"
73 "EXPR1 IS EXPR2"
74 "EXPR1 IS NOT EXPR2"
75 "EXPR NOT BETWEEN EXPR1 AND EXPR2"
76 "EXPR BETWEEN EXPR1 AND EXPR2"
77 "EXPR NOT IN (SELECT cname FROM tblname)"
78 "EXPR NOT IN (1)"
79 "EXPR NOT IN (1, 2, 3)"
80 "EXPR NOT IN tblname"
81 "EXPR NOT IN dbname.tblname"
82 "EXPR IN (SELECT cname FROM tblname)"
83 "EXPR IN (1)"
84 "EXPR IN (1, 2, 3)"
85 "EXPR IN tblname"
86 "EXPR IN dbname.tblname"
87 "EXISTS (SELECT cname FROM tblname)"
88 "NOT EXISTS (SELECT cname FROM tblname)"
89 "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
90 "CASE EXPR WHEN EXPR1 THEN EXPR2 END"
91 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
92 "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
93 "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"
94 "CASE WHEN EXPR1 THEN EXPR2 END"
95 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"
96 "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"
} {
# If the expression string being parsed contains "EXPR2", then replace
# string "EXPR1" and "EXPR2" with arbitrary SQL expressions. If it
# contains "EXPR", then replace EXPR with an arbitrary SQL expression.
#
set elist [list $expr]
if {[string match *EXPR2* $expr]} {
set elist [list]
foreach {e1 e2} { cname "34+22" } {
lappend elist [string map [list EXPR1 $e1 EXPR2 $e2] $expr]
}
}
if {[string match *EXPR* $expr]} {
set elist2 [list]
foreach el $elist {
foreach e { cname "34+22" } {
lappend elist2 [string map [list EXPR $e] $el]
}
}
set elist $elist2
}
set x 0
foreach e $elist {
incr x
do_test e_expr-12.3.$tn.$x {
set rc [catch { execsql "SELECT $e FROM tblname" } msg]
} {0}
}
}
# EVIDENCE-OF: R-49462-56079 -- syntax diagram raise-function
#
foreach {tn raiseexpr} {
1 "RAISE(IGNORE)"
2 "RAISE(ROLLBACK, 'error message')"
3 "RAISE(ABORT, 'error message')"
4 "RAISE(FAIL, 'error message')"
} {
do_execsql_test e_expr-12.4.$tn "
CREATE TRIGGER dbname.tr$tn BEFORE DELETE ON tblname BEGIN
SELECT $raiseexpr ;
END;
" {}
}
#-------------------------------------------------------------------------
# Test the statements related to the BETWEEN operator.
#
# EVIDENCE-OF: R-40079-54503 The BETWEEN operator is logically
# equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent
# to "x>=y AND x<=z" except that with BETWEEN, the x expression is
# only evaluated once.
#
db func x x
proc x {} { incr ::xcount ; return [expr $::x] }
foreach {tn x expr res nEval} {
1 10 "x() >= 5 AND x() <= 15" 1 2
2 10 "x() BETWEEN 5 AND 15" 1 1
3 5 "x() >= 5 AND x() <= 5" 1 2
4 5 "x() BETWEEN 5 AND 5" 1 1
} {
do_test e_expr-13.1.$tn {
set ::xcount 0
set a [execsql "SELECT $expr"]
list $::xcount $a
} [list $nEval $res]
}
# EVIDENCE-OF: R-05155-34454 The precedence of the BETWEEN operator is
# the same as the precedence as operators == and != and LIKE and groups
# left to right.
#
# Therefore, BETWEEN groups more tightly than operator "AND", but less
# so than "<".
#
do_execsql_test e_expr-13.2.1 { SELECT 1 == 10 BETWEEN 0 AND 2 } 1
do_execsql_test e_expr-13.2.2 { SELECT (1 == 10) BETWEEN 0 AND 2 } 1
do_execsql_test e_expr-13.2.3 { SELECT 1 == (10 BETWEEN 0 AND 2) } 0
do_execsql_test e_expr-13.2.4 { SELECT 6 BETWEEN 4 AND 8 == 1 } 1
do_execsql_test e_expr-13.2.5 { SELECT (6 BETWEEN 4 AND 8) == 1 } 1
do_execsql_test e_expr-13.2.6 { SELECT 6 BETWEEN 4 AND (8 == 1) } 0
do_execsql_test e_expr-13.2.7 { SELECT 5 BETWEEN 0 AND 0 != 1 } 1
do_execsql_test e_expr-13.2.8 { SELECT (5 BETWEEN 0 AND 0) != 1 } 1
do_execsql_test e_expr-13.2.9 { SELECT 5 BETWEEN 0 AND (0 != 1) } 0
do_execsql_test e_expr-13.2.10 { SELECT 1 != 0 BETWEEN 0 AND 2 } 1
do_execsql_test e_expr-13.2.11 { SELECT (1 != 0) BETWEEN 0 AND 2 } 1
do_execsql_test e_expr-13.2.12 { SELECT 1 != (0 BETWEEN 0 AND 2) } 0
do_execsql_test e_expr-13.2.13 { SELECT 1 LIKE 10 BETWEEN 0 AND 2 } 1
do_execsql_test e_expr-13.2.14 { SELECT (1 LIKE 10) BETWEEN 0 AND 2 } 1
do_execsql_test e_expr-13.2.15 { SELECT 1 LIKE (10 BETWEEN 0 AND 2) } 0
do_execsql_test e_expr-13.2.16 { SELECT 6 BETWEEN 4 AND 8 LIKE 1 } 1
do_execsql_test e_expr-13.2.17 { SELECT (6 BETWEEN 4 AND 8) LIKE 1 } 1
do_execsql_test e_expr-13.2.18 { SELECT 6 BETWEEN 4 AND (8 LIKE 1) } 0
do_execsql_test e_expr-13.2.19 { SELECT 0 AND 0 BETWEEN 0 AND 1 } 0
do_execsql_test e_expr-13.2.20 { SELECT 0 AND (0 BETWEEN 0 AND 1) } 0
do_execsql_test e_expr-13.2.21 { SELECT (0 AND 0) BETWEEN 0 AND 1 } 1
do_execsql_test e_expr-13.2.22 { SELECT 0 BETWEEN -1 AND 1 AND 0 } 0
do_execsql_test e_expr-13.2.23 { SELECT (0 BETWEEN -1 AND 1) AND 0 } 0
do_execsql_test e_expr-13.2.24 { SELECT 0 BETWEEN -1 AND (1 AND 0) } 1
do_execsql_test e_expr-13.2.25 { SELECT 2 < 3 BETWEEN 0 AND 1 } 1
do_execsql_test e_expr-13.2.26 { SELECT (2 < 3) BETWEEN 0 AND 1 } 1
do_execsql_test e_expr-13.2.27 { SELECT 2 < (3 BETWEEN 0 AND 1) } 0
do_execsql_test e_expr-13.2.28 { SELECT 2 BETWEEN 1 AND 2 < 3 } 0
do_execsql_test e_expr-13.2.29 { SELECT 2 BETWEEN 1 AND (2 < 3) } 0
do_execsql_test e_expr-13.2.30 { SELECT (2 BETWEEN 1 AND 2) < 3 } 1
finish_test