SQLite

  }
} {}
do_delete_tests e_delete-1.1 {
  1  "DELETE FROM t1       ; SELECT * FROM t1"       {}
  2  "DELETE FROM main.t2  ; SELECT * FROM t2"       {}
}

# EVIDENCE-OF: R-25092-63878 If a WHERE clause is supplied, then only
# those rows for which evaluating the WHERE clause and casting the
# result to a NUMERIC value produces a result other than NULL or zero
# (integer value 0 or real value 0.0).
#
do_delete_tests e_delete-1.2 {
  1  "DELETE FROM t3 WHERE 1       ; SELECT x FROM t3"       {}
  2  "DELETE FROM main.t4 WHERE 0  ; SELECT x FROM t4"       {1 2 3 4 5}
  3  "DELETE FROM t4 WHERE 0.0     ; SELECT x FROM t4"       {1 2 3 4 5}
  4  "DELETE FROM t4 WHERE NULL    ; SELECT x FROM t4"       {1 2 3 4 5}
  5  "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4"       {2}

    1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
    2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
    3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
    4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
]


# EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then
# the ON expression is evaluated for each row of the cartesian product
# and the result cast to a numeric value as if by a CAST expression. All
# rows for which the expression evaluates to NULL or zero (integer value
# 0 or real value 0.0) are excluded from the dataset.
#
foreach {tn select res} [list                                              \
    1 { SELECT * FROM t1 %JOIN% t2 ON (1) }       $t1_cross_t2             \
    2 { SELECT * FROM t1 %JOIN% t2 ON (0) }       [list]                   \
    3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) }    [list]                   \
    4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') }   [list]                   \
    5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') }   $t1_cross_t2             \

  cannot join using column %s - column not present in both tables
} {
  1 { SELECT * FROM t1, t3 USING (b) }   "b"
  2 { SELECT * FROM t3, t1 USING (c) }   "c"
  3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"
} 

# EVIDENCE-OF: R-42568-37000 For each pair of namesake columns, the
# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
# product and the result cast to a numeric value. All rows for which one
# or more of the expressions evaluates to NULL or zero are excluded from
# the result set.
#
do_select_tests e_select-1.5 {
  1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
  2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}
} 

# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a

  test_join $tn.1.7  "t2 CROSS JOIN t3"      {t2 t3}
  test_join $tn.1.8  "t2 JOIN t3"            {t2 t3}
  test_join $tn.1.9  "t2, t2 AS x"           {t2 t2}
  test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
  test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
  test_join $tn.1.12 "t2 JOIN t2 AS x"       {t2 t2}

  # EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then
  # the ON expression is evaluated for each row of the cartesian product
  # and the result cast to a numeric value as if by a CAST expression. All
  # rows for which the expression evaluates to NULL or zero (integer value
  # 0 or real value 0.0) are excluded from the dataset.
  #
  test_join $tn.2.1  "t1, t2 ON (t1.a=t2.a)"  {t1 t2 -on {te_equals a a}}
  test_join $tn.2.2  "t2, t1 ON (t1.a=t2.a)"  {t2 t1 -on {te_equals a a}}
  test_join $tn.2.3  "t2, t1 ON (1)"          {t2 t1 -on te_true}
  test_join $tn.2.4  "t2, t1 ON (NULL)"       {t2 t1 -on te_false}
  test_join $tn.2.5  "t2, t1 ON (1.1-1.1)"    {t2 t1 -on te_false}
  test_join $tn.2.6  "t1, t2 ON (1.1-1.0)"    {t1 t2 -on te_true}

  CREATE TABLE x2(k, x, y2);
  INSERT INTO x2 VALUES(1, 50, X'B82838');
  INSERT INTO x2 VALUES(5, 84.79, 65.88);
  INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
  INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
} {}

# EVIDENCE-OF: R-22873-49686 If a WHERE clause is specified, the WHERE
# expression is evaluated for each row in the input data and the result
# cast to a numeric value. All rows for which the WHERE clause
# expression evaluates to a NULL value or to zero (integer value 0 or
# real value 0.0) are excluded from the dataset before continuing.
#
do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x }         {3}
do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y }         {3 5 6}
do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z }         {1 2 6}
do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z    } {1 2 4 6}
do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}

  12.2  "SELECT max(a) FROM b3 GROUP BY max(b)"
  12.3  "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)"
} {
  set res {1 {aggregate functions are not allowed in the GROUP BY clause}}
  do_catchsql_test e_select-4.$tn $select $res
}

# EVIDENCE-OF: R-40359-04817 If a HAVING clause is specified, it is
# evaluated once for each group of rows and cast to an integer value. If
# the result of evaluating the HAVING clause is NULL or zero (integer
# value 0), the group is discarded.
#
#   This requirement is tested by all e_select-4.13.* tests.
#
# EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate
# expression, it is evaluated across all rows in the group.
#
#   Tested by e_select-4.13.1.*

  1  "UPDATE t1 SET b = 'roman' ; SELECT * FROM t1"
     {1 roman  2 roman  3 roman}

  2  "UPDATE t1 SET a = 'greek' ; SELECT * FROM t1"
     {greek roman  greek roman  greek roman}
}

# EVIDENCE-OF: R-41754-00978 Otherwise, the UPDATE affects only those
# rows for which evaluating the WHERE clause expression and casting the
# result to a NUMERIC value produces a value other than NULL or zero
# (integer value 0 or real value 0.0).
#
do_execsql_test e_update-1.3.0 {
  DELETE FROM main.t1;
  INSERT INTO main.t1 VALUES(NULL, '');
  INSERT INTO main.t1 VALUES(1, 'i');
  INSERT INTO main.t1 VALUES(2, 'ii');
  INSERT INTO main.t1 VALUES(3, 'iii');

  3  "UPDATE t1 SET b = 'macedonian' WHERE a; SELECT * FROM t1"
     {{} {}  1 macedonian  2 macedonian  3 macedonian}

  4  "UPDATE t1 SET b = 'lithuanian' WHERE a IS NULL; SELECT * FROM t1"
     {{} lithuanian  1 macedonian  2 macedonian  3 macedonian}
}

# EVIDENCE-OF: R-61178-36001 It is not an error if the WHERE clause does
# not evaluate to a non-NULL, non-zero value for any row in the table -
# this just means that the UPDATE statement affects zero rows.
#
do_execsql_test e_update-1.4.0 {
  DELETE FROM main.t1;
  INSERT INTO main.t1 VALUES(NULL, '');
  INSERT INTO main.t1 VALUES(1, 'i');
  INSERT INTO main.t1 VALUES(2, 'ii');
  INSERT INTO main.t1 VALUES(3, 'iii');