/ Check-in [cbcf8abb]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add new test file e_expr.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: cbcf8abbb2cb3e603cc3da45075f6b076e5cad56
User & Date: dan 2010-07-17 18:44:49
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: fb6a59b0 user: drh tags: trunk
2010-07-17
18:44
Add new test file e_expr.test. check-in: cbcf8abb user: dan tags: trunk
09:27
Do not run journal3.test with the inmemory_journal permutation. check-in: e1d228e9 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/e_expr.test.

            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  +# Set up three global variables:
           21  +#
           22  +#   ::opname         An array mapping from SQL operator to an easy to parse
           23  +#                    name. The names are used as part of test case names.
           24  +#
           25  +#   ::opprec         An array mapping from SQL operator to a numeric
           26  +#                    precedence value. Operators that group more tightly
           27  +#                    have lower numeric precedences.
           28  +#
           29  +#   ::oplist         A list of all SQL operators supported by SQLite.
           30  +#
           31  +foreach {op opn} {
           32  +      ||   cat     *   mul       /  div       %     mod       +      add
           33  +      -    sub     <<  lshift    >> rshift    &     bitand    |      bitor
           34  +      <    less    <=  lesseq    >  more      >=    moreeq    =      eq1
           35  +      ==   eq2     <>  ne1       != ne2       IS    is        LIKE   like
           36  +      GLOB glob    AND and       OR or        MATCH match     REGEXP regexp
           37  +      {IS NOT} isnt
           38  +} {
           39  +  set ::opname($op) $opn
           40  +}
           41  +set oplist [list]
           42  +foreach {prec opl} {
           43  +  1   ||
           44  +  2   {* / %}
           45  +  3   {+ -}
           46  +  4   {<< >> & |}
           47  +  5   {< <= > >=}
           48  +  6   {= == != <> IS {IS NOT} LIKE GLOB MATCH REGEXP}
           49  +  7   AND
           50  +  8   OR
           51  +} {
           52  +  foreach op $opl { 
           53  +    set ::opprec($op) $prec 
           54  +    lappend oplist $op
           55  +  }
           56  +}
           57  +
           58  +
           59  +# Hook in definitions of MATCH and REGEX. The following implementations
           60  +# cause MATCH and REGEX to behave similarly to the == operator.
           61  +#
           62  +proc matchfunc {a b} { return [expr {$a==$b}] }
           63  +proc regexfunc {a b} { return [expr {$a==$b}] }
           64  +db func match  -argcount 2 matchfunc
           65  +db func regexp -argcount 2 regexfunc
           66  +
           67  +#-------------------------------------------------------------------------
           68  +# Test cases e_expr-1.* attempt to verify that all binary operators listed
           69  +# in the documentation exist and that the relative precedences of the
           70  +# operators are also as the documentation suggests.
           71  +#
           72  +# EVIDENCE-OF: R-15514-65163 SQLite understands the following binary
           73  +# operators, in order from highest to lowest precedence: || * / % + -
           74  +# << >> & | < <= > >= = == != <> IS IS
           75  +# NOT IN LIKE GLOB MATCH REGEXP AND OR
           76  +#
           77  +# EVIDENCE-OF: R-38759-38789 Operators IS and IS NOT have the same
           78  +# precedence as =.
           79  +#
           80  +
           81  +
           82  +# TODO: These tests are currently omitted because one or two cases
           83  +# related to LIKE/GLOB/MATCH/REGEXP fail. After this case is fixed,
           84  +# reinstate these tests.
           85  +#
           86  +if 0 {
           87  +
           88  +unset -nocomplain untested
           89  +foreach op1 $oplist {
           90  +  foreach op2 $oplist {
           91  +    set untested($op1,$op2) 1
           92  +    foreach {tn A B C} {
           93  +       1     22   45    66
           94  +       2      0    0     0
           95  +       3      0    0     1
           96  +       4      0    1     0
           97  +       5      0    1     1
           98  +       6      1    0     0
           99  +       7      1    0     1
          100  +       8      1    1     0
          101  +       9      1    1     1
          102  +      10      5    6     1
          103  +      11      1    5     6
          104  +      12      1    5     5
          105  +      13      5    5     1
          106  +
          107  +      14      5    2     1
          108  +      15      1    4     1
          109  +      16     -1    0     1
          110  +      17      0    1    -1
          111  +
          112  +    } {
          113  +      set testname "e_expr-1.$opname($op1).$opname($op2).$tn"
          114  +
          115  +      # If $op2 groups more tightly than $op1, then the result
          116  +      # of executing $sql1 whould be the same as executing $sql3.
          117  +      # If $op1 groups more tightly, or if $op1 and $op2 have 
          118  +      # the same precedence, then executing $sql1 should return
          119  +      # the same value as $sql2.
          120  +      #
          121  +      set sql1 "SELECT $A $op1 $B $op2 $C"
          122  +      set sql2 "SELECT ($A $op1 $B) $op2 $C"
          123  +      set sql3 "SELECT $A $op1 ($B $op2 $C)"
          124  +
          125  +      set a2 [db one $sql2]
          126  +      set a3 [db one $sql3]
          127  +
          128  +      do_execsql_test $testname $sql1 [list [
          129  +        expr {$opprec($op2) < $opprec($op1) ? $a3 : $a2}
          130  +      ]]
          131  +
          132  +      if {$a2 != $a3} { unset -nocomplain untested($op1,$op2) }
          133  +    }
          134  +  }
          135  +}
          136  +
          137  +foreach op {* AND OR + || & |} { unset untested($op,$op) }
          138  +unset untested(+,-)  ;#       Since    (a+b)-c == a+(b-c)
          139  +unset untested(*,<<) ;#       Since    (a*b)<<c == a*(b<<c)
          140  +
          141  +do_test e_expr-1.1 { array names untested } {}
          142  +
          143  +# At one point, test 1.2.2 was failing. Instead of the correct result, it
          144  +# was returning {1 1 0}. This would seem to indicate that LIKE has the
          145  +# same precedence as '<'. Which is incorrect. It has lower precedence.
          146  +#
          147  +do_execsql_test e_expr-1.2.1 { 
          148  +  SELECT 0 < 2 LIKE 1,   (0 < 2) LIKE 1,   0 < (2 LIKE 1)
          149  +} {1 1 0}
          150  +do_execsql_test e_expr-1.2.2 { 
          151  +  SELECT 0 LIKE 0 < 2,   (0 LIKE 0) < 2,   0 LIKE (0 < 2)
          152  +} {0 1 0}
          153  +
          154  +# Showing that LIKE and == have the same precedence
          155  +#
          156  +do_execsql_test e_expr-1.2.3 { 
          157  +  SELECT 2 LIKE 2 == 1,   (2 LIKE 2) == 1,    2 LIKE (2 == 1)
          158  +} {1 1 0}
          159  +do_execsql_test e_expr-1.2.4 { 
          160  +  SELECT 2 == 2 LIKE 1,   (2 == 2) LIKE 1,    2 == (2 LIKE 1)
          161  +} {1 1 0}
          162  +
          163  +# Showing that < groups more tightly than == (< has higher precedence). 
          164  +#
          165  +do_execsql_test e_expr-1.2.5 { 
          166  +  SELECT 0 < 2 == 1,   (0 < 2) == 1,   0 < (2 == 1)
          167  +} {1 1 0}
          168  +do_execsql_test e_expr-1.6 { 
          169  +  SELECT 0 == 0 < 2,   (0 == 0) < 2,   0 == (0 < 2)
          170  +} {0 1 0}
          171  +
          172  +}
          173  +
          174  +#-------------------------------------------------------------------------
          175  +# Check that the four unary prefix operators mentioned in the 
          176  +# documentation exist.
          177  +#
          178  +# EVIDENCE-OF: R-13958-53419 Supported unary prefix operators are these:
          179  +# - + ~ NOT
          180  +#
          181  +do_execsql_test e_expr-2.1 { SELECT -   10   } {-10}
          182  +do_execsql_test e_expr-2.2 { SELECT +   10   } {10}
          183  +do_execsql_test e_expr-2.3 { SELECT ~   10   } {-11}
          184  +do_execsql_test e_expr-2.4 { SELECT NOT 10   } {0}
          185  +
          186  +#-------------------------------------------------------------------------
          187  +# Tests for the two statements made regarding the unary + operator.
          188  +#
          189  +# EVIDENCE-OF: R-53670-03373 The unary operator + is a no-op.
          190  +#
          191  +# EVIDENCE-OF: R-19480-30968 It can be applied to strings, numbers,
          192  +# blobs or NULL and it always returns a result with the same value as
          193  +# the operand.
          194  +#
          195  +foreach {tn literal type} {
          196  +  1     'helloworld'   text
          197  +  2     45             integer
          198  +  3     45.2           real
          199  +  4     45.0           real
          200  +  5     X'ABCDEF'      blob
          201  +  6     NULL           null
          202  +} {
          203  +  set sql " SELECT quote( + $literal ), typeof( + $literal) "
          204  +  do_execsql_test e_expr-3.$tn $sql [list $literal $type]
          205  +}
          206  +
          207  +#-------------------------------------------------------------------------
          208  +# Check that both = and == are both acceptable as the "equals" operator.
          209  +# Similarly, either != or <> work as the not-equals operator.
          210  +#
          211  +# EVIDENCE-OF: R-03679-60639 Equals can be either = or ==.
          212  +#
          213  +# EVIDENCE-OF: R-30082-38996 The non-equals operator can be either != or
          214  +# <>.
          215  +#
          216  +foreach {tn literal different} {
          217  +  1   'helloworld'  '12345'
          218  +  2   22            23
          219  +  3   'xyz'         X'78797A'
          220  +  4   X'78797A00'   'xyz'
          221  +} {
          222  +  do_execsql_test e_expr-4.$tn "
          223  +    SELECT $literal  = $literal,   $literal == $literal,
          224  +           $literal  = $different, $literal == $different,
          225  +           $literal  = NULL,       $literal == NULL,
          226  +           $literal != $literal,   $literal <> $literal,
          227  +           $literal != $different, $literal <> $different,
          228  +           $literal != NULL,       $literal != NULL
          229  +
          230  +  " {1 1 0 0 {} {} 0 0 1 1 {} {}}
          231  +}
          232  +
          233  +#-------------------------------------------------------------------------
          234  +# Test the || operator.
          235  +#
          236  +# EVIDENCE-OF: R-44409-62641 The || operator is "concatenate" - it joins
          237  +# together the two strings of its operands.
          238  +#
          239  +foreach {tn a b} {
          240  +  1   'helloworld'  '12345'
          241  +  2   22            23
          242  +} {
          243  +  set as [db one "SELECT $a"]
          244  +  set bs [db one "SELECT $b"]
          245  +  
          246  +  do_execsql_test e_expr-5.$tn "SELECT $a || $b" [list "${as}${bs}"]
          247  +}
          248  +
          249  +#-------------------------------------------------------------------------
          250  +# Test the % operator.
          251  +#
          252  +# EVIDENCE-OF: R-08914-63790 The operator % outputs the value of its
          253  +# left operand modulo its right operand.
          254  +#
          255  +do_execsql_test e_expr-6.1 {SELECT  72%5}  {2}
          256  +do_execsql_test e_expr-6.2 {SELECT  72%-5} {2}
          257  +do_execsql_test e_expr-6.3 {SELECT -72%-5} {-2}
          258  +do_execsql_test e_expr-6.4 {SELECT -72%5}  {-2}
          259  +
          260  +#-------------------------------------------------------------------------
          261  +# Test that the results of all binary operators are either numeric or 
          262  +# NULL, except for the || operator, which may evaluate to either a text
          263  +# value or NULL.
          264  +#
          265  +# EVIDENCE-OF: R-20665-17792 The result of any binary operator is either
          266  +# a numeric value or NULL, except for the || concatenation operator
          267  +# which always evaluates to either NULL or a text value.
          268  +#
          269  +set literals {
          270  +  1 'abc'        2 'hexadecimal'       3 ''
          271  +  4 123          5 -123                6 0
          272  +  7 123.4        8 0.0                 9 -123.4
          273  + 10 X'ABCDEF'   11 X''                12 X'0000'
          274  + 13     NULL
          275  +}
          276  +foreach op $oplist {
          277  +  foreach {n1 rhs} $literals { 
          278  +  foreach {n2 lhs} $literals {
          279  +
          280  +    set t [db one " SELECT typeof($lhs $op $rhs) "]
          281  +    do_test e_expr-7.$opname($op).$n1.$n2 {
          282  +      expr {
          283  +           ($op=="||" && ($t == "text" || $t == "null"))
          284  +        || ($op!="||" && ($t == "integer" || $t == "real" || $t == "null"))
          285  +      }
          286  +    } 1
          287  +
          288  +  }}
          289  +}
          290  +
          291  +#-------------------------------------------------------------------------
          292  +# Test the IS and IS NOT operators.
          293  +#
          294  +# EVIDENCE-OF: R-24731-45773 The IS and IS NOT operators work like = and
          295  +# != except when one or both of the operands are NULL.
          296  +#
          297  +# EVIDENCE-OF: R-06325-15315 In this case, if both operands are NULL,
          298  +# then the IS operator evaluates to 1 (true) and the IS NOT operator
          299  +# evaluates to 0 (false).
          300  +#
          301  +# EVIDENCE-OF: R-19812-36779 If one operand is NULL and the other is
          302  +# not, then the IS operator evaluates to 0 (false) and the IS NOT
          303  +# operator is 1 (true).
          304  +#
          305  +# EVIDENCE-OF: R-61975-13410 It is not possible for an IS or IS NOT
          306  +# expression to evaluate to NULL.
          307  +#
          308  +do_execsql_test e_expr-8.1.1  { SELECT NULL IS     NULL } {1}
          309  +do_execsql_test e_expr-8.1.2  { SELECT 'ab' IS     NULL } {0}
          310  +do_execsql_test e_expr-8.1.3  { SELECT NULL IS     'ab' } {0}
          311  +do_execsql_test e_expr-8.1.4  { SELECT 'ab' IS     'ab' } {1}
          312  +do_execsql_test e_expr-8.1.5  { SELECT NULL ==     NULL } {{}}
          313  +do_execsql_test e_expr-8.1.6  { SELECT 'ab' ==     NULL } {{}}
          314  +do_execsql_test e_expr-8.1.7  { SELECT NULL ==     'ab' } {{}}
          315  +do_execsql_test e_expr-8.1.8  { SELECT 'ab' ==     'ab' } {1}
          316  +do_execsql_test e_expr-8.1.9  { SELECT NULL IS NOT NULL } {0}
          317  +do_execsql_test e_expr-8.1.10 { SELECT 'ab' IS NOT NULL } {1}
          318  +do_execsql_test e_expr-8.1.11 { SELECT NULL IS NOT 'ab' } {1}
          319  +do_execsql_test e_expr-8.1.12 { SELECT 'ab' IS NOT 'ab' } {0}
          320  +do_execsql_test e_expr-8.1.13 { SELECT NULL !=     NULL } {{}}
          321  +do_execsql_test e_expr-8.1.14 { SELECT 'ab' !=     NULL } {{}}
          322  +do_execsql_test e_expr-8.1.15 { SELECT NULL !=     'ab' } {{}}
          323  +do_execsql_test e_expr-8.1.16 { SELECT 'ab' !=     'ab' } {0}
          324  +
          325  +foreach {n1 rhs} $literals { 
          326  +  foreach {n2 lhs} $literals {
          327  +    if {$rhs!="NULL" && $lhs!="NULL"} {
          328  +      set eq [execsql "SELECT $lhs = $rhs, $lhs != $rhs"]
          329  +    } else {
          330  +      set eq [list [expr {$lhs=="NULL" && $rhs=="NULL"}] \
          331  +                   [expr {$lhs!="NULL" || $rhs!="NULL"}]
          332  +      ]
          333  +    }
          334  +    set test e_expr-8.2.$n1.$n2
          335  +    do_execsql_test $test.1 "SELECT $lhs IS $rhs, $lhs IS NOT $rhs" $eq
          336  +    do_execsql_test $test.2 "
          337  +      SELECT ($lhs IS $rhs) IS NULL, ($lhs IS NOT $rhs) IS NULL
          338  +    " {0 0}
          339  +  }
          340  +}
          341  +
          342  +finish_test