SQLite

# 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