SQLite

#
proc do_join_test {tn select res} {
  foreach {tn2 joinop} [list    1 ,    2 "CROSS JOIN"    3 "INNER JOIN"] {
    set S [string map [list %JOIN% $joinop] $select]
    uplevel do_execsql_test $tn.$tn2 [list $S] [list $res]
  }
}

#
#   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
#
# Where switches are:
#
#   -errorformat FMTSTRING
#
proc do_select_tests {prefix args} {

  set testlist [lindex $args end]
  set switches [lrange $args 0 end-1]

  set errfmt ""
  set countonly 0

  for {set i 0} {$i < [llength $switches]} {incr i} {
    set s [lindex $switches $i]
    set n [string length $s]
    if {$n>=2 && [string equal -length $n $s "-errorformat"]} {
      set errfmt [lindex $switches [incr i]]
    } elseif {$n>=2 && [string equal -length $n $s "-count"]} {
      set countonly 1
    } else {
      error "unknown switch: $s"
    }
  }

  if {$countonly && $errfmt!=""} {
    error "Cannot use -count and -errorformat together"
  }
  set nTestlist [llength $testlist]
  if {$nTestlist%3 || $nTestlist==0 } {
    error "SELECT test list contains [llength $testlist] elements"
  }

  foreach {tn sql res} $testlist {
    if {$countonly} {
      set nRow 0
      db eval $sql {incr nRow}
      uplevel do_test ${prefix}.$tn [list [list set {} $nRow]] [list $res]
    } elseif {$errfmt==""} {
      uplevel do_execsql_test ${prefix}.${tn} [list $sql] [list [list {*}$res]]
    } else {
      set res [list 1 [string trim [format $errfmt $res]]]
      uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res]
    }
  }
}

#-------------------------------------------------------------------------
# The following tests check that all paths on the syntax diagrams on
# the lang_select.html page may be taken.
#
# EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint
#

#   0: No WHERE clause
#   1: Has WHERE clause
#
#   0: No GROUP BY clause
#   1: Has GROUP BY clause
#   2: Has GROUP BY and HAVING clauses
#
do_select_tests e_select-0.2 {
  0000.1  "SELECT 1, 2, 3 " {1 2 3}
  1000.1  "SELECT DISTINCT 1, 2, 3 " {1 2 3}
  2000.1  "SELECT ALL 1, 2, 3 " {1 2 3}
  
  0100.1  "SELECT a, b, a||b FROM t1 " {
    a one aone b two btwo c three cthree
  }
  1100.1  "SELECT DISTINCT a, b, a||b FROM t1 " {
    a one aone b two btwo c three cthree
  }
  1200.1  "SELECT ALL a, b, a||b FROM t1 " {
    a one aone b two btwo c three cthree
  }

  0010.1  "SELECT 1, 2, 3 WHERE 1 " {1 2 3}
  0010.2  "SELECT 1, 2, 3 WHERE 0 " {}
  0010.3  "SELECT 1, 2, 3 WHERE NULL " {}

  1010.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3}

  2010.1  "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3}

  0110.1  "SELECT a, b, a||b FROM t1 WHERE a!='x' " {
    a one aone b two btwo c three cthree
  }
  0110.2  "SELECT a, b, a||b FROM t1 WHERE a=='x'" {}

  1110.1  "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " {
    a one aone b two btwo c three cthree
  }

  2110.0  "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {}

  0001.1  "SELECT 1, 2, 3 GROUP BY 2" {1 2 3}
  0002.1  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
  0002.2  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

  1001.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3}
  1002.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
  1002.2  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

  2001.1  "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3}
  2002.1  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
  2002.2  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

  0101.1  "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
  0102.1  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" {
    1 a 1 c 1 b
  }
  0102.2  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { }

  1101.1  "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
  1102.1  "SELECT DISTINCT count(*), max(a) FROM t1 
           GROUP BY b HAVING count(*)=1" {
    1 a 1 c 1 b
  }
  1102.2  "SELECT DISTINCT count(*), max(a) FROM t1 
           GROUP BY b HAVING count(*)=2" { 
  }

  2101.1  "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
  2102.1  "SELECT ALL count(*), max(a) FROM t1 
           GROUP BY b HAVING count(*)=1" {
    1 a 1 c 1 b
  }
  2102.2  "SELECT ALL count(*), max(a) FROM t1 
           GROUP BY b HAVING count(*)=2" { 
  }

  0011.1  "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
  0012.1  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
  0012.2  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {}

  1011.1  "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {}
  1012.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" 
          {1 2 3}
  1012.2  "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {}

  2011.1  "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
  2012.1  "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
  2012.2  "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {}

  0111.1  "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a}
  0112.1  "SELECT count(*), max(a) FROM t1 
           WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c}
  0112.2  "SELECT count(*), max(a) FROM t1 
           WHERE 0 GROUP BY b HAVING count(*)=2" { }
  1111.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" 
          {1 a 1 b}
  1112.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a'
           GROUP BY b HAVING count(*)=1" {
    1 c 1 b
  }
  1112.2  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0
           GROUP BY b HAVING count(*)=2" { 
  }

  2111.1  "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" 
          {1 c 1 b}
  2112.1  "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'
           GROUP BY b HAVING count(*)=1" {
    1 a 1 c
  }
  2112.2  "SELECT ALL count(*), max(a) FROM t1 
           WHERE 0 GROUP BY b HAVING count(*)=2" { }
}


# EVIDENCE-OF: R-23316-20169 -- syntax diagram result-column
#
do_select_tests e_select-0.3 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT t1.* FROM t1" {a one b two c three}
  3  "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}
  4  "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}
  5  "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}
}

# EVIDENCE-OF: R-41233-21397 -- syntax diagram join-source
#
# EVIDENCE-OF: R-62821-57533 -- syntax diagram join-op
#
do_select_tests e_select-0.4 {
  1  "SELECT t1.rowid FROM t1" {1 2 3}
  2  "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}
  3  "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}

  4  "SELECT t1.rowid FROM t1" {1 2 3}
  5  "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3}
  6  "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" 
     {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}

  7  "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2}
  8  "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3}
  9  "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2}
  10 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2}

  11 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}
  12 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}
  13 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}
  14 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}
}

# EVIDENCE-OF: R-56911-63533 -- syntax diagram compound-operator
#
do_select_tests e_select-0.5 {
  1  "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}
  2  "SELECT rowid FROM t1 UNION     SELECT rowid+2 FROM t4" {1 2 3 4}
  3  "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}
  4  "SELECT rowid FROM t1 EXCEPT    SELECT rowid+2 FROM t4" {1 2}
}

# EVIDENCE-OF: R-60388-27458 -- syntax diagram ordering-term
#
do_select_tests e_select-0.6 {
  1  "SELECT b||a FROM t1 ORDER BY b||a"                  {onea threec twob}
  2  "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}
  3  "SELECT b||a FROM t1 ORDER BY (b||a) ASC"            {onea threec twob}
  4  "SELECT b||a FROM t1 ORDER BY (b||a) DESC"           {twob threec onea}
}

# EVIDENCE-OF: R-35843-38195 -- syntax diagram select-stmt
#
do_select_tests e_select-0.7 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT * FROM t1 ORDER BY b" {a one c three b two}
  3  "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}

  4  "SELECT * FROM t1 LIMIT 10" {a one b two c three}
  5  "SELECT * FROM t1 LIMIT 10 OFFSET 5" {}
  6  "SELECT * FROM t1 LIMIT 10, 5" {}

  7  "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three}
  8  "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {}
  9  "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {}

  10  "SELECT * FROM t1 UNION SELECT b, a FROM t1" 
     {a one b two c three one a three c two b}
  11  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" 
     {one a two b three c a one c three b two}
  12  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" 
     {one a two b three c a one c three b two}
  13  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" 
     {a one b two c three one a three c two b}
  14  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" 
     {two b}
  15  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" 
     {}
  16  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" 
     {a one b two c three one a three c two b}
  17  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" 
     {b two}
  18  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" 
     {}
}

#-------------------------------------------------------------------------
# The following tests focus on FROM clause (join) processing.
#

# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple
# SELECT statement, then the input data is implicitly a single row zero
# columns wide
#
do_select_tests e_select-1.1 {
  1 "SELECT 'abc'"            {abc}
  2 "SELECT 'abc' WHERE NULL" {}
  3 "SELECT NULL"             {{}}
  4 "SELECT count(*)"         {1}
  5 "SELECT count(*) WHERE 0" {0}
  6 "SELECT count(*) WHERE 1" {1}
}

# EVIDENCE-OF: R-48114-33255 If there is only a single table in the
# join-source following the FROM clause, then the input data used by the
# SELECT statement is the contents of the named table.
#
#   The results of the SELECT queries suggest that they are operating on the
#   contents of the table 'xx'.
#
do_execsql_test e_select-1.2.0 {
  CREATE TABLE xx(x, y);
  INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
  INSERT INTO xx VALUES(NULL, -16.87);
  INSERT INTO xx VALUES(-17.89, 'linguistically');
} {}
do_select_tests e_select-1.2 {
  1  "SELECT quote(x), quote(y) FROM xx" {

     'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' 
     NULL             -16.87                          
     -17.89           'linguistically'                
  }


  2  "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}


  3  "SELECT sum(x), sum(y) FROM xx"               {-17.89 -16.87}

}

# EVIDENCE-OF: R-23593-12456 If there is more than one table specified
# as part of the join-source following the FROM keyword, then the
# contents of each named table are joined into a single dataset for the
# simple SELECT statement to operate on.
#
#   There are more detailed tests for subsequent requirements that add 
#   more detail to this idea. We just add a single test that shows that
#   data is coming from each of the three tables following the FROM clause
#   here to show that the statement, vague as it is, is not incorrect.
#
do_select_tests e_select-1.3 {
  1 "SELECT * FROM t1, t2, t3" {
      a one a I a 1 a one a I b 2 a one b II a 1 
      a one b II b 2 a one c III a 1 a one c III b 2 
      b two a I a 1 b two a I b 2 b two b II a 1 
      b two b II b 2 b two c III a 1 b two c III b 2 
      c three a I a 1 c three a I b 2 c three b II a 1 
      c three b II b 2 c three c III a 1 c three c III b 2
  }
}

#
# The following block of tests - e_select-1.4.* - test that the description
# of cartesian joins in the SELECT documentation is consistent with SQLite.
# In doing so, we test the following three requirements as a side-effect:
#
# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER

  expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25}
} [expr 4+4]

# Some extra cartesian product tests using tables t1 and t2.
#
do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2
do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1

do_select_tests e_select-1.4.5 [list                                   \
    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.

  do_join_test e_select-1.3.$tn $select $res
}

# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
# part of the join-constraint, then each of the column names specified
# must exist in the datasets to both the left and right of the join-op.
#
do_select_tests e_select-1.4 -error {
  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
# USING clause, the normal rules for handling affinities, collation
# sequences and NULL values in comparisons apply.
#
# EVIDENCE-OF: R-35466-18578 The column from the dataset on the
# left-hand side of the join operator is considered to be on the

  INSERT INTO t7 VALUES('x', 'ex',  24);
  INSERT INTO t7 VALUES('y', 'why', 25);

  INSERT INTO t8 VALUES('x', 'abc', 24);
  INSERT INTO t8 VALUES('z', 'ghi', 26);
} {}

do_select_tests e_select-1.8 {
  1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1}


  1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2}



  2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1}


  2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2}
}


# EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the
# columns that would normally contain values copied from the right-hand
# input dataset.
#
do_select_tests e_select-1.9 {
  1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}


  1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 
     {x ex 24 x abc 24 y why 25 {} {} {}}


  2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}


  2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}

}

# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
# the join-ops, then an implicit USING clause is added to the
# join-constraints. The implicit USING clause contains each of the
# column names that appear in both the left and right-hand input
# datasets.
#
do_select_tests e_select-1-10 {
  1a "SELECT * FROM t7 JOIN t8 USING (a)"        {x ex 24 abc 24}
  1b "SELECT * FROM t7 NATURAL JOIN t8"          {x ex 24 abc 24}


  2a "SELECT * FROM t8 JOIN t7 USING (a)"        {x abc 24 ex 24}
  2b "SELECT * FROM t8 NATURAL JOIN t7"          {x abc 24 ex 24}


  3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)"   {x ex 24 abc 24 y why 25 {} {}}
  3b "SELECT * FROM t7 NATURAL LEFT JOIN t8"     {x ex 24 abc 24 y why 25 {} {}}


  4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)"   {x abc 24 ex 24 z ghi 26 {} {}}
  4b "SELECT * FROM t8 NATURAL LEFT JOIN t7"     {x abc 24 ex 24 z ghi 26 {} {}}


  5a "SELECT * FROM t3 JOIN t4 USING (a,c)"      {b 2}
  5b "SELECT * FROM t3 NATURAL JOIN t4"          {b 2}


  6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2}
  6b "SELECT * FROM t3 NATURAL LEFT JOIN t4"     {a 1 b 2}




} 

# EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets
# feature no common column names, then the NATURAL keyword has no effect
# on the results of the join.
#
do_execsql_test e_select-1.11.0 {
  CREATE TABLE t10(x, y);
  INSERT INTO t10 VALUES(1, 'true');
  INSERT INTO t10 VALUES(0, 'false');
} {}
do_select_tests e_select-1-11 {
  1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}
  1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}




}

# EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a
# join that specifies the NATURAL keyword.
#
foreach {tn sql} {
  1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)}

# 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.
#
do_select_tests e_select-4.1 {
  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
  }


}

# 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

  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
}





# In lang_select.html, a non-aggregate query is defined as any simple SELECT
# that has no GROUP BY clause and no aggregate expressions in the result
# expression list. Other queries are aggregate queries. Test cases
# e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of
# simple SELECT that is different for aggregate and non-aggregate queries
# verify (in a way) that these definitions are consistent:
#
# EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate
# query if it contains either a GROUP BY clause or one or more aggregate
# functions in the result-set.
#
# EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no
# aggregate functions or a GROUP BY clause, it is a non-aggregate query.
#

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


  2 "SELECT a IS NULL, b+1, * FROM z1" {

        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

  }

  3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366}
}


# Test cases e_select-4.5.* and e_select-4.6.* together show that:
#
# EVIDENCE-OF: R-51988-01124 The single row of result-set data created
# by evaluating the aggregate and non-aggregate expressions in the
# result-set forms the result of an aggregate query without a GROUP BY
# clause.
#

# 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.
#
do_select_tests e_select-4.5 {
  1 "SELECT count(a), max(a), count(b), max(b) FROM z1"      {5 63 5 born}
  2 "SELECT count(*), max(1)"                                {1 1}

  3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3"           {-43.06}
  4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3"           {-38.06}
  5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}


}

# EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the
# result-set is evaluated once for an arbitrarily selected row of the
# dataset.
#
# EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used

  CREATE TABLE a2(one PRIMARY KEY, three);
  INSERT INTO a2 VALUES(1, 1);
  INSERT INTO a2 VALUES(3, 2);
  INSERT INTO a2 VALUES(6, 3);
  INSERT INTO a2 VALUES(10, 4);
} {}
do_select_tests e_select-4.6 {
  1 "SELECT one, two, count(*) FROM a1"                        {4 10 4} 
  2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {2 3 2} 
  3 "SELECT one, two, count(*) FROM a1 WHERE one>3"            {4 10 1} 
  4 "SELECT *, count(*) FROM a1 JOIN a2"                       {4 10 10 4 16} 
  5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6}


}

# EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then
# each non-aggregate expression is evaluated against a row consisting
# entirely of NULL values.
#
do_select_tests e_select-4.7 {
  1  "SELECT one, two, count(*) FROM a1 WHERE 0"           {{} {} 0}
  2  "SELECT sum(two), * FROM a1, a2 WHERE three>5"        {{} {} {} {} {}}
  3  "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" {
    1 1 1
  }


} 

# EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY
# clause always returns exactly one row of data, even if there are zero
# rows of input data.
#
foreach {tn select} {
  8.1  "SELECT count(*) FROM a1"

# EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate
# query with a GROUP BY clause, then each of the expressions specified
# as part of the GROUP BY clause is evaluated for each row of the
# dataset. Each row is then assigned to a "group" based on the results;
# rows for which the results of evaluating the GROUP BY expressions are
# the same are assigned to the same group.
#
#   These tests also show that the following is not untrue:
#
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
  1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
    4,5 f   1 o   7,6   s 3,2 t
  }
  2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,4,3,2 10    5,7,6 18
  }
  3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
    4  1,5    2,6   3,7
  }
  4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
    4,3,5,7,6    1,2
  }


}

# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
do_select_tests e_select-4.10 {
  1  "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1   3   2,4}
  2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}

} 




# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
# sequence with which to compare text values apply when evaluating
# expressions in a GROUP BY clause.
#
do_select_tests e_select-4.11 {
  1  "SELECT count(*) FROM b3 GROUP BY b"      {1 1 1 1}
  2  "SELECT count(*) FROM b3 GROUP BY a"      {2 2}
  3  "SELECT count(*) FROM b3 GROUP BY +b"     {1 1 1 1}
  4  "SELECT count(*) FROM b3 GROUP BY +a"     {2 2}
  5  "SELECT count(*) FROM b3 GROUP BY b||''"  {1 1 1 1}
  6  "SELECT count(*) FROM b3 GROUP BY a||''"  {1 1 1 1}


}

# EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may
# not be aggregate expressions.
#
foreach {tn select} {
  12.1  "SELECT * FROM b3 GROUP BY count(*)"

#
# EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate
# expression, it is evaluated with respect to an arbitrarily selected
# row from the group.
#
#   Tested by e_select-4.13.2.*
#
#   Tests in this block also show that this is not untrue:
#
# EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values,
# even aggregate functions, that are not in the result.
#
do_execsql_test e_select-4.13.0 {
  CREATE TABLE c1(up, down);
  INSERT INTO c1 VALUES('x', 1);
  INSERT INTO c1 VALUES('x', 2);
  INSERT INTO c1 VALUES('x', 4);
  INSERT INTO c1 VALUES('x', 8);
  INSERT INTO c1 VALUES('y', 16);

  INSERT INTO c3 VALUES(2,  'helium');
  INSERT INTO c3 VALUES(3,  'lithium');
  INSERT INTO c3 VALUES(4,  'beryllium');
  INSERT INTO c3 VALUES(5,  'boron');
  INSERT INTO c3 VALUES(94, 'plutonium');
} {}

do_select_tests e_select-4.13 {
  1.1  "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}
  1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
  1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
  1.4  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}

  2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
  2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}

  2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6"  {9 36}


}

# EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then
# evaluated once for each group of rows.
#
# EVIDENCE-OF: R-53735-47017 If the expression is an aggregate
# expression, it is evaluated across all rows in the group.
#
do_select_tests e_select-4.15 {
  1  "SELECT sum(down) FROM c1 GROUP BY up" {15 48}
  2  "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)"     {54 36 27 21 39 28}
  3  "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)"     {80 36 40 21}
  4  "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22}
  5  "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2"
        {3 4.33 1 2.0}


} 

# EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single
# arbitrarily chosen row from within the group.
#
# EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate
# expression in the result-set, then all such expressions are evaluated
# for the same row.
#
do_select_tests e_select-4.15 {
  1  "SELECT i, j FROM c2 GROUP BY i%2"             {8 28   9 36}
  2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28}
  3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  5  "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"
        {2 5 boron   2 2 helium   1 3 lithium}


} 

# EVIDENCE-OF: R-19334-12811 Each group of input dataset rows
# contributes a single row to the set of result rows.
#
# EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the
# DISTINCT keyword, the number of rows returned by an aggregate query
# with a GROUP BY clause is the same as the number of groups of rows
# produced by applying the GROUP BY and HAVING clauses to the filtered
# input dataset.
#
do_select_tests e_select.4.16 -count {
  1  "SELECT i, j FROM c2 GROUP BY i%2"          2
  2  "SELECT i, j FROM c2 GROUP BY i"            9
  3  "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4




} 

#-------------------------------------------------------------------------
# The following tests attempt to verify statements made regarding the ALL
# and DISTINCT keywords.
#
drop_all_tables
do_execsql_test e_select-5.1.0 {

  INSERT INTO h3 VALUES(8, '2,4');
  INSERT INTO h3 VALUES(9, '3');
} {}

# EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may
# follow the SELECT keyword in a simple SELECT statement.
#
do_select_tests e_select-5.1 {
  1   "SELECT ALL a FROM h1"      {1 1 1 4 4 4}
  2   "SELECT DISTINCT a FROM h1" {1 4}
}

# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
# the entire set of result rows are returned by the SELECT.
#
# EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present,
# then the behaviour is as if ALL were specified.
#
# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
# then duplicate rows are removed from the set of result rows before it
# is returned.
#
#   The three testable statements above are tested by e_select-5.2.*,
#   5.3.* and 5.4.* respectively.
#
do_select_tests e_select-5 {
  3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four}
  3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four}

  3.1 "SELECT x FROM h2" {One Two Three Four one two three four}
  3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four}

  4.1 "SELECT DISTINCT x FROM h2" {four one three two}
  4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one}


} 

# EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate
# rows, two NULL values are considered to be equal.
#
do_select_tests e_select-5.5 {
  1  "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3}
}

# EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation
# sequence to compare text values with apply.
#
do_select_tests e_select-5.6 {
  1  "SELECT DISTINCT b FROM h1"                  {I IV four i iv one}
  2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {four i iv one}
  3  "SELECT DISTINCT x FROM h2"                  {four one three two}
  4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
    Four One Three Two four one three two
  }


}

#-------------------------------------------------------------------------
# The following tests - e_select-7.* - test that statements made to do
# with compound SELECT statements are correct.
#

# EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent
# SELECTs must return the same number of result columns.
#
#   All the other tests in this section use compound SELECTs created
#   using component SELECTs that do return the same number of columns.
#   So the tests here just show that it is an error to attempt otherwise.
#
drop_all_tables
do_execsql_test e_select-7.1.0 {
  CREATE TABLE j1(a, b, c);
  CREATE TABLE j2(e, f);
  CREATE TABLE j3(g);
} {}
do_select_tests e_select-7.1 -error {
  SELECTs to the left and right of %s do not have the same number of result columns
} {
  1   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {UNION ALL}
  2   "SELECT *    FROM j1    UNION ALL SELECT * FROM j3"    {UNION ALL}
  3   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {UNION ALL}
  4   "SELECT a, b FROM j1    UNION ALL SELECT * FROM j3,j2" {UNION ALL}
  5   "SELECT *    FROM j3,j2 UNION ALL SELECT a, b FROM j1" {UNION ALL}

  6   "SELECT a, b FROM j1    UNION SELECT g FROM j3"        {UNION}

  15  "SELECT *    FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT}

  16  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
  17  "SELECT *    FROM j1    EXCEPT SELECT * FROM j3"       {EXCEPT}
  18  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
  19  "SELECT a, b FROM j1    EXCEPT SELECT * FROM j3,j2"    {EXCEPT}
  20  "SELECT *    FROM j3,j2 EXCEPT SELECT a, b FROM j1"    {EXCEPT}





} 

# EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must
# be simple SELECT statements, they may not contain ORDER BY or LIMIT
# clauses.
# 
foreach {tn select op1 op2} {
  1   "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" 

  INSERT INTO q2 VALUES('beauty', 2);
  INSERT INTO q2 VALUES(-65.91, 4);
  INSERT INTO q2 VALUES('emanating', -16.56);

  INSERT INTO q3 VALUES('beauty', 2);
  INSERT INTO q3 VALUES('beauty', 2);
} {}
do_select_tests e_select-7.4 {
  1   {SELECT a FROM q1 UNION ALL SELECT d FROM q2}
      {16 legible beauty legible beauty -65.91 emanating}

  2   {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1}
      {16 -87.66 {} x legible 1}

  3   {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 
      {3 -16.56}

  4   {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 
      {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2}


} 

# EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as
# UNION ALL, except that duplicate rows are removed from the final
# result set.
#
do_select_tests e_select-7.5 {
  1   {SELECT a FROM q1 UNION SELECT d FROM q2}
      {-65.91 16 beauty emanating legible}

  2   {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1}
      {16 -87.66 {} x legible 1}

  3   {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 
      {-16.56 3}

  4   {SELECT * FROM q2 UNION SELECT * FROM q3} 
      {-65.91 4 beauty 2 emanating -16.56 legible 1}


} 

# EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the
# intersection of the results of the left and right SELECTs.
#
do_select_tests e_select-7.6 {
  1   {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible}
  2   {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2}


}

# EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of
# rows returned by the left SELECT that are not also returned by the
# right-hand SELECT.
#
do_select_tests e_select-7.7 {
  1   {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16}

  2   {SELECT * FROM q2 EXCEPT SELECT * FROM q3} 
      {-65.91 4 emanating -16.56 legible 1}


}

# EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results
# of INTERSECT and EXCEPT operators before the result set is returned.
#
do_select_tests e_select-7.8 {
  0   {SELECT * FROM q3} {beauty 2 beauty 2}

  1   {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2}
  2   {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1}  {beauty 2}


}

# EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate
# rows for the results of compound SELECT operators, NULL values are
# considered equal to other NULL values and distinct from all non-NULL
# values.
#
db nullvalue null
do_select_tests e_select-7.9 {
  1   {SELECT NULL UNION ALL SELECT NULL} {null null}
  2   {SELECT NULL UNION     SELECT NULL} {null}
  3   {SELECT NULL INTERSECT SELECT NULL} {null}
  4   {SELECT NULL EXCEPT    SELECT NULL} {}

  5   {SELECT NULL UNION ALL SELECT 'ab'} {null ab}
  6   {SELECT NULL UNION     SELECT 'ab'} {null ab}
  7   {SELECT NULL INTERSECT SELECT 'ab'} {}
  8   {SELECT NULL EXCEPT    SELECT 'ab'} {null}

  9   {SELECT NULL UNION ALL SELECT 0} {null 0}
  10  {SELECT NULL UNION     SELECT 0} {null 0}
  11  {SELECT NULL INTERSECT SELECT 0} {}
  12  {SELECT NULL EXCEPT    SELECT 0} {null}

  13  {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2}
  14  {SELECT c FROM q1 UNION     SELECT g FROM q3} {null -42.47 2}
  15  {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {}
  16  {SELECT c FROM q1 EXCEPT    SELECT g FROM q3} {null -42.47}


}
db nullvalue {} 

# EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two
# text values is determined as if the columns of the left and right-hand
# SELECT statements were the left and right-hand operands of the equals
# (=) operator, except that greater precedence is not assigned to a
# collation sequence specified with the postfix COLLATE operator.
#
drop_all_tables
do_execsql_test e_select-7.10.0 {
  CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c);
  INSERT INTO y1 VALUES('Abc', 'abc', 'aBC');
} {}
do_select_tests e_select-7.10 {
  1   {SELECT 'abc'                UNION SELECT 'ABC'} {ABC abc}
  2   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC}
  3   {SELECT 'abc'                UNION SELECT 'ABC' COLLATE nocase} {ABC}
  4   {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc}
  5   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC}

  6   {SELECT a FROM y1 UNION SELECT b FROM y1}                {abc}
  7   {SELECT b FROM y1 UNION SELECT a FROM y1}                {Abc abc}
  8   {SELECT a FROM y1 UNION SELECT c FROM y1}                {aBC}

  9   {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC}



}

# EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to
# any values when comparing rows as part of a compound SELECT.
#
drop_all_tables
do_execsql_test e_select-7.10.0 {
  CREATE TABLE w1(a TEXT, b NUMBER);
  CREATE TABLE w2(a, b TEXT);

  INSERT INTO w1 VALUES('1', 4.1);
  INSERT INTO w2 VALUES(1, 4.1);
} {}

do_select_tests e_select-7.11 {
  1  { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1}
  2  { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1}
  3  { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1}
  4  { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1}

  5  { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {}
  6  { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {}
  7  { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {}
  8  { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {}

  9  { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1}
  10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1}
  11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1}
  12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1}


}


# EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are
# connected into a compound SELECT, they group from left to right. In
# other words, if "A", "B" and "C" are all simple SELECT statements, (A
# op B op C) is processed as ((A op B) op C).

  INSERT INTO d2 VALUES('lad', 'relenting');
} {}

# EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results
# of evaluating the left-most expression in the ORDER BY list, then ties
# are broken by evaluating the second left-most expression and so on.
#
do_select_tests e_select-8.1 {
  1  "SELECT * FROM d1 ORDER BY x, y, z" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }


}

# EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally
# followed by one of the keywords ASC (smaller values are returned
# first) or DESC (larger values are returned first).
#
#   Test cases e_select-8.2.* test the above.
#
# EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows
# are sorted in ascending (smaller values first) order by default.
#
#   Test cases e_select-8.3.* test the above. All 8.3 test cases are
#   copies of 8.2 test cases with the explicit "ASC" removed.
#
do_select_tests e_select-8 {
  2.1  "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }
  2.2  "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" {
     2 5 -1     2 4 93   1 5 -1   1 4  93    
     1 2 8      1 2 7    1 2 3    1 2 -20    

     2 4 93   2 5 -1     1 2 8      1 2 7    
     1 2 3    1 2 -20    1 4  93    1 5 -1   
  }
  3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" {
     2 4 93   2 5 -1     1 2 -20    1 2 3    
     1 2 7    1 2 8      1 4  93    1 5 -1   
  }


}

# EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant
# integer K then the expression is considered an alias for the K-th
# column of the result set (columns are numbered from left to right
# starting with 1).
#
do_select_tests e_select-8.4 {
  1  "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }
  2  "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" {
     2 5 -1     2 4 93   1 5 -1   1 4  93    
     1 2 8      1 2 7    1 2 3    1 2 -20    

     3 1     8 1    7 1   -20 1 
     93 1   -1 1   -1 2   93 2
  }
  9  "SELECT z, x FROM d1 ORDER BY 1" {
     -20 1  -1 2   -1 1   3 1     
     7 1     8 1   93 2   93 1   
  }


}

# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
# that corresponds to the alias of one of the output columns, then the
# expression is considered an alias for that column.
#
do_select_tests e_select-8.5 {
  1   "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
    -19 0 0 4 8 9 94 94
  }
  2   "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
    94 94 9 8 4 0 0 -19
  }
  3  "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
    3 1    8 1    7 1    -20 1    93 1    -1 1    -1 2    93 2
  }
  4  "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
    -20 1    -1 2    -1 1    3 1    7 1    8 1    93 2    93 1
  }


}

# EVIDENCE-OF: R-27923-38747 Otherwise, if the ORDER BY expression is
# any other expression, it is evaluated and the the returned value used
# to order the output rows.
#
# EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT,
# then an ORDER BY may contain any arbitrary expressions.
#
do_select_tests e_select-8.6 {
  1   "SELECT * FROM d1 ORDER BY x+y+z" {
    1 2 -20    1 5 -1    1 2 3    2 5 -1 
    1 2 7      1 2 8     1 4 93   2 4 93
  }
  2   "SELECT * FROM d1 ORDER BY x*z" {
    1 2 -20    2 5 -1    1 5 -1    1 2 3 
    1 2 7      1 2 8     1 4 93    2 4 93
  }
  3   "SELECT * FROM d1 ORDER BY y*z" {
    1 2 -20    2 5 -1    1 5 -1    1 2 3 
    1 2 7      1 2 8     2 4 93    1 4 93
  }


}

# EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound
# SELECT, then ORDER BY expressions that are not aliases to output
# columns must be exactly the same as an expression used as an output
# column.
#
do_select_tests e_select-8.7.1 -error {
  %s ORDER BY term does not match any column in the result set
} {
  1   "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z"        1st
  2   "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd



} 

do_select_tests e_select-8.7.2 {
  1   "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" {
    -20 -2 -1 3 7 8 93 186 babied barked commercials gently 
    iterate lad pragmatist reemphasizes rejoicing solemnness
  }
  2   "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" {
    1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 
    babied charitableness barked interrupted commercials bathrobe gently
    failings iterate sexton lad relenting pragmatist guarded reemphasizes reply
    rejoicing liabilities solemnness annexed
  }


} 

do_execsql_test e_select-8.8.0 {
  CREATE TABLE d3(a);
  INSERT INTO d3 VALUES('text');
  INSERT INTO d3 VALUES(14.1);
  INSERT INTO d3 VALUES(13);
  INSERT INTO d3 VALUES(X'78787878');

  CREATE TABLE d9(y COLLATE nocase);

  INSERT INTO d8 VALUES('a');
  INSERT INTO d9 VALUES('B');
  INSERT INTO d8 VALUES('c');
  INSERT INTO d9 VALUES('D');
} {}
do_select_tests e_select-8.13 {
  1   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
         ORDER BY a
      } {1 2 3 4 5 6}
  2   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
         ORDER BY c
      } {1 2 3 4 5 6}
  3   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7

  10  { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } 
      {f 2   c 5   4 c   1 f}

  11  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 
      {2 f   5 c   c 5   f 2}
  12  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } 
      {2 f   5 c   c 5   f 2}
} 




# EVIDENCE-OF: R-39265-04070 If no matching expression can be found in
# the result columns of any constituent SELECT, it is an error.
#
do_select_tests e_select-8.14 -error {
  %s ORDER BY term does not match any column in the result set
} {
  1   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 }          1st
  2   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 }       2nd
  3   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' }  1st
  4   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah    }  1st
  5   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d }  3rd
  6   { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b  }  4th



} 

# EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is
# processed separately and may be matched against result columns from
# different SELECT statements in the compound.
# 
do_select_tests e_select-8.15 {
  1  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d }
     {1 e   1 f   4 b   4 c}
  2  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b }
     {1 e   1 f   4 b   4 c}
  3  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 }
     {1 e   1 f   4 b   4 c}


} 


#-------------------------------------------------------------------------
# Tests related to statements made about the LIMIT/OFFSET clause.
#
do_execsql_test e_select-9.0 {
  CREATE TABLE f1(a, b);

  INSERT INTO f1 VALUES(1, 'a');
} {}

# EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the
# LIMIT clause, so long as it evaluates to an integer or a value that
# can be losslessly converted to an integer.
#
do_select_tests e_select-9.1 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e}
  3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } 
     {a b c d e}
  4  { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e}
  5  { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e}


}

# EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value
# or any other value that cannot be losslessly converted to an integer,
# an error is returned.
#

do_select_tests e_select-9.2 -error "datatype mismatch" {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } {}
  2  { SELECT b FROM f1 ORDER BY a LIMIT NULL } {}
  3  { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } {}
  4  { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } {}
  5  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } {}


} 

# EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a
# negative value, then there is no upper bound on the number of rows
# returned.
#
do_select_tests e_select-9.4 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT -1 } 
     {a b c d e f g h i j k l m n o p q r s t u v w x y z}
  2  { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } 
     {a b c d e f g h i j k l m n o p q r s t u v w x y z}
  3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 }
     {a b c d e f g h i j k l m n o p q r s t u v w x y z}


}

# EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N
# rows of its result set only, where N is the value that the LIMIT
# expression evaluates to.
#
do_select_tests e_select-9.5 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 0 } {}
  2  { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w}
  3  { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s}
  4  { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o}


}

# EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return
# less than N rows without a LIMIT clause, then the entire result set is
# returned.
#
do_select_tests e_select-9.6 {
  1  { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z}
  2  { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5}


} 


# EVIDENCE-OF: R-24188-24349 The expression attached to the optional
# OFFSET clause that may follow a LIMIT clause must also evaluate to an
# integer, or a value that can be losslessly converted to an integer.
#
foreach {tn select} {

# EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then
# the first M rows are omitted from the result set returned by the
# SELECT statement and the next N rows are returned, where M and N are
# the values that the OFFSET and LIMIT clauses evaluate to,
# respectively.
#
do_select_tests e_select-9.8 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o}
  3  { SELECT b FROM f1 ORDER BY a 
       LIMIT  (SELECT a FROM f1 WHERE b='j') 
       OFFSET (SELECT a FROM f1 WHERE b='b') 
     } {c d e f g h i j k l}
  4  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h}
  5  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e}
  6  { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {}
  7  { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r}


}

# EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than
# M+N rows if it did not have a LIMIT clause, then the first M rows are
# skipped and the remaining rows (if any) are returned.
#
do_select_tests e_select-9.9 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z}
  2  { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1}


}


# EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a
# negative value, the results are the same as if it had evaluated to
# zero.
#
do_select_tests e_select-9.10 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e}
  3  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0  } {a b c d e}


} 

# EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the
# LIMIT clause may specify two scalar expressions separated by a comma.
#
# EVIDENCE-OF: R-33788-46243 In this case, the first expression is used
# as the OFFSET expression and the second as the LIMIT expression.
#
do_select_tests e_select-9.11 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o}
  3  { SELECT b FROM f1 ORDER BY a 
       LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') 
     } {c d e f g h i j k l}
  4  { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h}
  5  { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e}
  6  { SELECT b FROM f1 ORDER BY a LIMIT 10, 0 } {}
  7  { SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 } {p q r}

  8  { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z}
  9  { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1}

  10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e}
  11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e}
  12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e}


}


finish_test