SQLite

do_execsql_test e_select-3.2.3 {
  SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k
} {3}
do_execsql_test e_select-3.2.4 {
  SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3
} {}

#-------------------------------------------------------------------------
# Tests below this point are focused on verifying the testable statements
# related to caculating the result rows of a simple SELECT statement.
#

drop_all_tables
do_execsql_test e_select-4.0 {
  CREATE TABLE z1(a, b, c);
  CREATE TABLE z2(d, e);
  CREATE TABLE z3(a, b);

  INSERT INTO z1 VALUES(51.65, -59.58, 'belfries');
  INSERT INTO z1 VALUES(-5, NULL, 75);
  INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters');
  INSERT INTO z1 VALUES(NULL, 67, 'quartets');
  INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen');
  INSERT INTO z1 VALUES(63, 'born', -26);

  INSERT INTO z2 VALUES(NULL, 21);
  INSERT INTO z2 VALUES(36, 6);

  INSERT INTO z3 VALUES('subsistence', 'gauze');
  INSERT INTO z3 VALUES(49.17, -67);
} {}

# EVIDENCE-OF: R-36327-17224 If a result expression is the special
# expression "*" then all columns in the input data are substituted for
# that one expression.
#
# EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table
# or subquery in the FROM clause followed by ".*" then all columns from
# the named table or subquery are substituted for the single expression.
#
foreach {tn select res} {
  1  "SELECT * FROM z1 LIMIT 1"             {51.65 -59.58 belfries}
  2  "SELECT * FROM z1,z2 LIMIT 1"          {51.65 -59.58 belfries {} 21}
  3  "SELECT z1.* FROM z1,z2 LIMIT 1"       {51.65 -59.58 belfries}
  4  "SELECT z2.* FROM z1,z2 LIMIT 1"       {{} 21}
  5  "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}

  6  "SELECT count(*), * FROM z1"           {6 63 born -26}
  7  "SELECT max(a), * FROM z1"             {63 63 born -26}
  8  "SELECT *, min(a) FROM z1"             {63 born -26 -5}

  9  "SELECT *,* FROM z1,z2 LIMIT 1" {        
     51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
  }
  10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
     51.65 -59.58 belfries 51.65 -59.58 belfries
  }
} {
  do_execsql_test e_select-4.1.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*"
# expression in any context other than than a result expression list.
#
# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
# "alias.*" expression in a simple SELECT query that does not have a
# FROM clause.
#
foreach {tn select err} {
  1.1  "SELECT a, b, c FROM z1 WHERE *"    {near "*": syntax error}
  1.2  "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error}
  1.3  "SELECT 1 + * FROM z1"              {near "*": syntax error}
  1.4  "SELECT * + 1 FROM z1"              {near "+": syntax error}

  2.1 "SELECT *" {no tables specified}
  2.2 "SELECT * WHERE 1" {no tables specified}
  2.3 "SELECT * WHERE 0" {no tables specified}
  2.4 "SELECT count(*), *" {no tables specified}
} {
  do_catchsql_test e_select-4.2.$tn $select [list 1 $err]
}

# EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned
# by a simple SELECT statement is equal to the number of expressions in
# the result expression list after substitution of * and alias.*
# expressions.
#
foreach {tn select nCol} {
  1   "SELECT * FROM z1"   3
  2   "SELECT * FROM z1 NATURAL JOIN z3"            3
  3   "SELECT z1.* FROM z1 NATURAL JOIN z3"         3
  4   "SELECT z3.* FROM z1 NATURAL JOIN z3"         2
  5   "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3"   5
  6   "SELECT 1, 2, z1.* FROM z1"                   5
  7   "SELECT a, *, b, c FROM z1"                   6
} {
  set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]
  do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol
  sqlite3_finalize $::stmt
}

# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate
# query, then each expression in the result expression list is evaluated
# for each row in the dataset filtered by the WHERE clause.
#
#   By other definitions in lang_select.html, a non-aggregate query is
#   any simple SELECT that has no GROUP BY clause and no aggregate expressions
#   in the result expression list.
#
do_execsql_test e_select-4.4.1 {
  SELECT a, b FROM z1
} {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}

do_execsql_test e_select-4.4.2 {
  SELECT a IS NULL, b+1, * FROM z1
} [list {*}{
        0 -58.58   51.65 -59.58 belfries
        0 {}       -5 {} 75            
        0 -22.18   -2.2 -23.18 suiters
        1 68       {} 67 quartets    
        0 -31.3    -1.04 -32.3 aspen
        0 1        63 born -26
}]

do_execsql_test e_select-4.4.3 {
  SELECT 32*32, d||e FROM z2
} {1024 {} 1024 366}

# EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate
# query without a GROUP BY clause, then each aggregate expression in the
# result-set is evaluated once across the entire dataset.
#
foreach {tn select res} {
  5.1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born}
  5.2 "SELECT count(*), max(1)"                           {1 1}

  5.3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3"           {-43.06}
  5.4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3"           {-38.06}
  5.5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}





finish_test