test/e_expr.test

# 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}

finish_test