SQLite

Check-in [cbcf8abbb2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add new test file e_expr.test.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: cbcf8abbb2cb3e603cc3da45075f6b076e5cad56
User & Date: dan 2010-07-17 18:44:49.000
Context
2010-07-18
11:35
Add the -p option to lemon to cause conflicts resolved by precedence rules to appear in the parse.out file. (check-in: fb6a59b0a9 user: drh tags: trunk)
2010-07-17
18:44
Add new test file e_expr.test. (check-in: cbcf8abbb2 user: dan tags: trunk)
09:27
Do not run journal3.test with the inmemory_journal permutation. (check-in: e1d228e992 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Added test/e_expr.test.












































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
# 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 =.
#


# TODO: These tests are currently omitted because one or two cases
# related to LIKE/GLOB/MATCH/REGEXP fail. After this case is fixed,
# reinstate these tests.
#
if 0 {

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 [
        expr {$opprec($op2) < $opprec($op1) ? $a3 : $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}
  }
}

finish_test