/ Check-in [0ee9e755]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests to e_select.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0ee9e755719c45e6047f9f004030716029b886ca
User & Date: dan 2010-09-18 15:03:35
Context
2010-09-18
15:15
Fix a couple of stale evidence marks in e_select.test. check-in: 14227724 user: dan tags: trunk
15:03
Add tests to e_select.test. check-in: 0ee9e755 user: dan tags: trunk
2010-09-17
22:39
Clarifications to the sqlite3_auto_extension() documentation. check-in: ca96e0df user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

    69     69   #
    70     70   proc do_join_test {tn select res} {
    71     71     foreach {tn2 joinop} [list    1 ,    2 "CROSS JOIN"    3 "INNER JOIN"] {
    72     72       set S [string map [list %JOIN% $joinop] $select]
    73     73       uplevel do_execsql_test $tn.$tn2 [list $S] [list $res]
    74     74     }
    75     75   }
           76  +
           77  +#
           78  +#   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
           79  +#
           80  +# Where switches are:
           81  +#
           82  +#   -errorformat FMTSTRING
           83  +#
           84  +proc do_select_tests {prefix args} {
           85  +
           86  +  set testlist [lindex $args end]
           87  +  set switches [lrange $args 0 end-1]
           88  +
           89  +  set errfmt ""
           90  +  set countonly 0
           91  +
           92  +  for {set i 0} {$i < [llength $switches]} {incr i} {
           93  +    set s [lindex $switches $i]
           94  +    set n [string length $s]
           95  +    if {$n>=2 && [string equal -length $n $s "-errorformat"]} {
           96  +      set errfmt [lindex $switches [incr i]]
           97  +    } elseif {$n>=2 && [string equal -length $n $s "-count"]} {
           98  +      set countonly 1
           99  +    } else {
          100  +      error "unknown switch: $s"
          101  +    }
          102  +  }
          103  +
          104  +  if {$countonly && $errfmt!=""} {
          105  +    error "Cannot use -count and -errorformat together"
          106  +  }
          107  +  set nTestlist [llength $testlist]
          108  +  if {$nTestlist%3 || $nTestlist==0 } {
          109  +    error "SELECT test list contains [llength $testlist] elements"
          110  +  }
          111  +
          112  +  foreach {tn sql res} $testlist {
          113  +    if {$countonly} {
          114  +      set nRow 0
          115  +      db eval $sql {incr nRow}
          116  +      uplevel do_test ${prefix}.$tn [list [list set {} $nRow]] [list $res]
          117  +    } elseif {$errfmt==""} {
          118  +      uplevel do_execsql_test ${prefix}.${tn} [list $sql] [list [list {*}$res]]
          119  +    } else {
          120  +      set res [list 1 [string trim [format $errfmt $res]]]
          121  +      uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res]
          122  +    }
          123  +  }
          124  +}
    76    125   
    77    126   #-------------------------------------------------------------------------
    78    127   # The following tests check that all paths on the syntax diagrams on
    79    128   # the lang_select.html page may be taken.
    80    129   #
    81    130   # EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint
    82    131   #
................................................................................
   108    157   #   0: No WHERE clause
   109    158   #   1: Has WHERE clause
   110    159   #
   111    160   #   0: No GROUP BY clause
   112    161   #   1: Has GROUP BY clause
   113    162   #   2: Has GROUP BY and HAVING clauses
   114    163   #
   115         -foreach {tn select res} {
   116         -    0000.1  "SELECT 1, 2, 3 " {1 2 3}
   117         -    1000.1  "SELECT DISTINCT 1, 2, 3 " {1 2 3}
   118         -    2000.1  "SELECT ALL 1, 2, 3 " {1 2 3}
   119         -    
   120         -    0100.1  "SELECT a, b, a||b FROM t1 " {
   121         -      a one aone b two btwo c three cthree
   122         -    }
   123         -    1100.1  "SELECT DISTINCT a, b, a||b FROM t1 " {
   124         -      a one aone b two btwo c three cthree
   125         -    }
   126         -    1200.1  "SELECT ALL a, b, a||b FROM t1 " {
   127         -      a one aone b two btwo c three cthree
   128         -    }
   129         -
   130         -    0010.1  "SELECT 1, 2, 3 WHERE 1 " {1 2 3}
   131         -    0010.2  "SELECT 1, 2, 3 WHERE 0 " {}
   132         -    0010.3  "SELECT 1, 2, 3 WHERE NULL " {}
   133         -
   134         -    1010.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3}
   135         -
   136         -    2010.1  "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3}
   137         -
   138         -    0110.1  "SELECT a, b, a||b FROM t1 WHERE a!='x' " {
   139         -      a one aone b two btwo c three cthree
   140         -    }
   141         -    0110.2  "SELECT a, b, a||b FROM t1 WHERE a=='x'" {}
   142         -
   143         -    1110.1  "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " {
   144         -      a one aone b two btwo c three cthree
   145         -    }
   146         -
   147         -    2110.0  "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {}
   148         -
   149         -    0001.1  "SELECT 1, 2, 3 GROUP BY 2" {1 2 3}
   150         -    0002.1  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
   151         -    0002.2  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
   152         -
   153         -    1001.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3}
   154         -    1002.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
   155         -    1002.2  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
   156         -
   157         -    2001.1  "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3}
   158         -    2002.1  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
   159         -    2002.2  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
   160         -
   161         -    0101.1  "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
   162         -    0102.1  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" {
   163         -      1 a 1 c 1 b
   164         -    }
   165         -    0102.2  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { }
   166         -
   167         -    1101.1  "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
   168         -    1102.1  "SELECT DISTINCT count(*), max(a) FROM t1 
   169         -             GROUP BY b HAVING count(*)=1" {
   170         -      1 a 1 c 1 b
   171         -    }
   172         -    1102.2  "SELECT DISTINCT count(*), max(a) FROM t1 
   173         -             GROUP BY b HAVING count(*)=2" { 
   174         -    }
   175         -
   176         -    2101.1  "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
   177         -    2102.1  "SELECT ALL count(*), max(a) FROM t1 
   178         -             GROUP BY b HAVING count(*)=1" {
   179         -      1 a 1 c 1 b
   180         -    }
   181         -    2102.2  "SELECT ALL count(*), max(a) FROM t1 
   182         -             GROUP BY b HAVING count(*)=2" { 
   183         -    }
   184         -
   185         -    0011.1  "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
   186         -    0012.1  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
   187         -    0012.2  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {}
   188         -
   189         -    1011.1  "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {}
   190         -    1012.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" 
   191         -            {1 2 3}
   192         -    1012.2  "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {}
   193         -
   194         -    2011.1  "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
   195         -    2012.1  "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
   196         -    2012.2  "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {}
   197         -
   198         -    0111.1  "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a}
   199         -    0112.1  "SELECT count(*), max(a) FROM t1 
   200         -             WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c}
   201         -    0112.2  "SELECT count(*), max(a) FROM t1 
   202         -             WHERE 0 GROUP BY b HAVING count(*)=2" { }
   203         -    1111.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" 
   204         -            {1 a 1 b}
   205         -    1112.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a'
   206         -             GROUP BY b HAVING count(*)=1" {
   207         -      1 c 1 b
   208         -    }
   209         -    1112.2  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0
   210         -             GROUP BY b HAVING count(*)=2" { 
   211         -    }
   212         -
   213         -    2111.1  "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" 
   214         -            {1 c 1 b}
   215         -    2112.1  "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'
   216         -             GROUP BY b HAVING count(*)=1" {
   217         -      1 a 1 c
   218         -    }
   219         -    2112.2  "SELECT ALL count(*), max(a) FROM t1 
   220         -             WHERE 0 GROUP BY b HAVING count(*)=2" { }
   221         -} {
   222         -  do_execsql_test e_select-0.2.$tn $select [list {*}$res]
   223         -}
   224         -
   225         -
          164  +do_select_tests e_select-0.2 {
          165  +  0000.1  "SELECT 1, 2, 3 " {1 2 3}
          166  +  1000.1  "SELECT DISTINCT 1, 2, 3 " {1 2 3}
          167  +  2000.1  "SELECT ALL 1, 2, 3 " {1 2 3}
          168  +  
          169  +  0100.1  "SELECT a, b, a||b FROM t1 " {
          170  +    a one aone b two btwo c three cthree
          171  +  }
          172  +  1100.1  "SELECT DISTINCT a, b, a||b FROM t1 " {
          173  +    a one aone b two btwo c three cthree
          174  +  }
          175  +  1200.1  "SELECT ALL a, b, a||b FROM t1 " {
          176  +    a one aone b two btwo c three cthree
          177  +  }
          178  +
          179  +  0010.1  "SELECT 1, 2, 3 WHERE 1 " {1 2 3}
          180  +  0010.2  "SELECT 1, 2, 3 WHERE 0 " {}
          181  +  0010.3  "SELECT 1, 2, 3 WHERE NULL " {}
          182  +
          183  +  1010.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3}
          184  +
          185  +  2010.1  "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3}
          186  +
          187  +  0110.1  "SELECT a, b, a||b FROM t1 WHERE a!='x' " {
          188  +    a one aone b two btwo c three cthree
          189  +  }
          190  +  0110.2  "SELECT a, b, a||b FROM t1 WHERE a=='x'" {}
          191  +
          192  +  1110.1  "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " {
          193  +    a one aone b two btwo c three cthree
          194  +  }
          195  +
          196  +  2110.0  "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {}
          197  +
          198  +  0001.1  "SELECT 1, 2, 3 GROUP BY 2" {1 2 3}
          199  +  0002.1  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
          200  +  0002.2  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
          201  +
          202  +  1001.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3}
          203  +  1002.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
          204  +  1002.2  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
          205  +
          206  +  2001.1  "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3}
          207  +  2002.1  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
          208  +  2002.2  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
          209  +
          210  +  0101.1  "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
          211  +  0102.1  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" {
          212  +    1 a 1 c 1 b
          213  +  }
          214  +  0102.2  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { }
          215  +
          216  +  1101.1  "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
          217  +  1102.1  "SELECT DISTINCT count(*), max(a) FROM t1 
          218  +           GROUP BY b HAVING count(*)=1" {
          219  +    1 a 1 c 1 b
          220  +  }
          221  +  1102.2  "SELECT DISTINCT count(*), max(a) FROM t1 
          222  +           GROUP BY b HAVING count(*)=2" { 
          223  +  }
          224  +
          225  +  2101.1  "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
          226  +  2102.1  "SELECT ALL count(*), max(a) FROM t1 
          227  +           GROUP BY b HAVING count(*)=1" {
          228  +    1 a 1 c 1 b
          229  +  }
          230  +  2102.2  "SELECT ALL count(*), max(a) FROM t1 
          231  +           GROUP BY b HAVING count(*)=2" { 
          232  +  }
          233  +
          234  +  0011.1  "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
          235  +  0012.1  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
          236  +  0012.2  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {}
          237  +
          238  +  1011.1  "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {}
          239  +  1012.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" 
          240  +          {1 2 3}
          241  +  1012.2  "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {}
          242  +
          243  +  2011.1  "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
          244  +  2012.1  "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
          245  +  2012.2  "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {}
          246  +
          247  +  0111.1  "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a}
          248  +  0112.1  "SELECT count(*), max(a) FROM t1 
          249  +           WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c}
          250  +  0112.2  "SELECT count(*), max(a) FROM t1 
          251  +           WHERE 0 GROUP BY b HAVING count(*)=2" { }
          252  +  1111.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" 
          253  +          {1 a 1 b}
          254  +  1112.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a'
          255  +           GROUP BY b HAVING count(*)=1" {
          256  +    1 c 1 b
          257  +  }
          258  +  1112.2  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0
          259  +           GROUP BY b HAVING count(*)=2" { 
          260  +  }
          261  +
          262  +  2111.1  "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" 
          263  +          {1 c 1 b}
          264  +  2112.1  "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'
          265  +           GROUP BY b HAVING count(*)=1" {
          266  +    1 a 1 c
          267  +  }
          268  +  2112.2  "SELECT ALL count(*), max(a) FROM t1 
          269  +           WHERE 0 GROUP BY b HAVING count(*)=2" { }
          270  +}
          271  +
          272  +
          273  +# EVIDENCE-OF: R-23316-20169 -- syntax diagram result-column
          274  +#
          275  +do_select_tests e_select-0.3 {
          276  +  1  "SELECT * FROM t1" {a one b two c three}
          277  +  2  "SELECT t1.* FROM t1" {a one b two c three}
          278  +  3  "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}
          279  +  4  "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}
          280  +  5  "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}
          281  +}
          282  +
          283  +# EVIDENCE-OF: R-41233-21397 -- syntax diagram join-source
          284  +#
          285  +# EVIDENCE-OF: R-62821-57533 -- syntax diagram join-op
          286  +#
          287  +do_select_tests e_select-0.4 {
          288  +  1  "SELECT t1.rowid FROM t1" {1 2 3}
          289  +  2  "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}
          290  +  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}
          291  +
          292  +  4  "SELECT t1.rowid FROM t1" {1 2 3}
          293  +  5  "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3}
          294  +  6  "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" 
          295  +     {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}
          296  +
          297  +  7  "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2}
          298  +  8  "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3}
          299  +  9  "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2}
          300  +  10 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2}
          301  +
          302  +  11 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}
          303  +  12 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}
          304  +  13 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}
          305  +  14 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}
          306  +}
          307  +
          308  +# EVIDENCE-OF: R-56911-63533 -- syntax diagram compound-operator
          309  +#
          310  +do_select_tests e_select-0.5 {
          311  +  1  "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}
          312  +  2  "SELECT rowid FROM t1 UNION     SELECT rowid+2 FROM t4" {1 2 3 4}
          313  +  3  "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}
          314  +  4  "SELECT rowid FROM t1 EXCEPT    SELECT rowid+2 FROM t4" {1 2}
          315  +}
          316  +
          317  +# EVIDENCE-OF: R-60388-27458 -- syntax diagram ordering-term
          318  +#
          319  +do_select_tests e_select-0.6 {
          320  +  1  "SELECT b||a FROM t1 ORDER BY b||a"                  {onea threec twob}
          321  +  2  "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}
          322  +  3  "SELECT b||a FROM t1 ORDER BY (b||a) ASC"            {onea threec twob}
          323  +  4  "SELECT b||a FROM t1 ORDER BY (b||a) DESC"           {twob threec onea}
          324  +}
          325  +
          326  +# EVIDENCE-OF: R-35843-38195 -- syntax diagram select-stmt
          327  +#
          328  +do_select_tests e_select-0.7 {
          329  +  1  "SELECT * FROM t1" {a one b two c three}
          330  +  2  "SELECT * FROM t1 ORDER BY b" {a one c three b two}
          331  +  3  "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}
          332  +
          333  +  4  "SELECT * FROM t1 LIMIT 10" {a one b two c three}
          334  +  5  "SELECT * FROM t1 LIMIT 10 OFFSET 5" {}
          335  +  6  "SELECT * FROM t1 LIMIT 10, 5" {}
          336  +
          337  +  7  "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three}
          338  +  8  "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {}
          339  +  9  "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {}
          340  +
          341  +  10  "SELECT * FROM t1 UNION SELECT b, a FROM t1" 
          342  +     {a one b two c three one a three c two b}
          343  +  11  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" 
          344  +     {one a two b three c a one c three b two}
          345  +  12  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" 
          346  +     {one a two b three c a one c three b two}
          347  +  13  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" 
          348  +     {a one b two c three one a three c two b}
          349  +  14  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" 
          350  +     {two b}
          351  +  15  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" 
          352  +     {}
          353  +  16  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" 
          354  +     {a one b two c three one a three c two b}
          355  +  17  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" 
          356  +     {b two}
          357  +  18  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" 
          358  +     {}
          359  +}
   226    360   
   227    361   #-------------------------------------------------------------------------
   228    362   # The following tests focus on FROM clause (join) processing.
   229    363   #
   230    364   
   231    365   # EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple
   232    366   # SELECT statement, then the input data is implicitly a single row zero
   233    367   # columns wide
   234    368   #
   235         -do_execsql_test e_select-1.1.1 { SELECT 'abc' }            {abc}
   236         -do_execsql_test e_select-1.1.2 { SELECT 'abc' WHERE NULL } {}
   237         -do_execsql_test e_select-1.1.3 { SELECT NULL }             {{}}
   238         -do_execsql_test e_select-1.1.4 { SELECT count(*) }         {1}
   239         -do_execsql_test e_select-1.1.5 { SELECT count(*) WHERE 0 } {0}
   240         -do_execsql_test e_select-1.1.6 { SELECT count(*) WHERE 1 } {1}
          369  +do_select_tests e_select-1.1 {
          370  +  1 "SELECT 'abc'"            {abc}
          371  +  2 "SELECT 'abc' WHERE NULL" {}
          372  +  3 "SELECT NULL"             {{}}
          373  +  4 "SELECT count(*)"         {1}
          374  +  5 "SELECT count(*) WHERE 0" {0}
          375  +  6 "SELECT count(*) WHERE 1" {1}
          376  +}
   241    377   
   242    378   # EVIDENCE-OF: R-48114-33255 If there is only a single table in the
   243    379   # join-source following the FROM clause, then the input data used by the
   244    380   # SELECT statement is the contents of the named table.
   245    381   #
   246    382   #   The results of the SELECT queries suggest that they are operating on the
   247    383   #   contents of the table 'xx'.
   248    384   #
   249         -do_execsql_test e_select-1.2.1 {
          385  +do_execsql_test e_select-1.2.0 {
   250    386     CREATE TABLE xx(x, y);
   251    387     INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
   252    388     INSERT INTO xx VALUES(NULL, -16.87);
   253    389     INSERT INTO xx VALUES(-17.89, 'linguistically');
   254    390   } {}
   255         -do_execsql_test e_select-1.2.2 { 
   256         -  SELECT quote(x), quote(y) FROM xx
   257         -} [list \
   258         -  'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' \
   259         -  NULL             -16.87                          \
   260         -  -17.89           'linguistically'                \
   261         -]
   262         -do_execsql_test e_select-1.2.3 { 
   263         -  SELECT count(*), count(x), count(y) FROM xx
   264         -} {3 2 3}
   265         -do_execsql_test e_select-1.2.4 { 
   266         -  SELECT sum(x), sum(y) FROM xx
   267         -} {-17.89 -16.87}
          391  +do_select_tests e_select-1.2 {
          392  +  1  "SELECT quote(x), quote(y) FROM xx" {
          393  +     'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' 
          394  +     NULL             -16.87                          
          395  +     -17.89           'linguistically'                
          396  +  }
          397  +
          398  +  2  "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}
          399  +  3  "SELECT sum(x), sum(y) FROM xx"               {-17.89 -16.87}
          400  +}
   268    401   
   269    402   # EVIDENCE-OF: R-23593-12456 If there is more than one table specified
   270    403   # as part of the join-source following the FROM keyword, then the
   271    404   # contents of each named table are joined into a single dataset for the
   272    405   # simple SELECT statement to operate on.
   273    406   #
   274    407   #   There are more detailed tests for subsequent requirements that add 
   275    408   #   more detail to this idea. We just add a single test that shows that
   276    409   #   data is coming from each of the three tables following the FROM clause
   277    410   #   here to show that the statement, vague as it is, is not incorrect.
   278    411   #
   279         -do_execsql_test e_select-1.3.1 {
   280         -  SELECT * FROM t1, t2, t3
   281         -} [list 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]
          412  +do_select_tests e_select-1.3 {
          413  +  1 "SELECT * FROM t1, t2, t3" {
          414  +      a one a I a 1 a one a I b 2 a one b II a 1 
          415  +      a one b II b 2 a one c III a 1 a one c III b 2 
          416  +      b two a I a 1 b two a I b 2 b two b II a 1 
          417  +      b two b II b 2 b two c III a 1 b two c III b 2 
          418  +      c three a I a 1 c three a I b 2 c three b II a 1 
          419  +      c three b II b 2 c three c III a 1 c three c III b 2
          420  +  }
          421  +}
   282    422   
   283    423   #
   284    424   # The following block of tests - e_select-1.4.* - test that the description
   285    425   # of cartesian joins in the SELECT documentation is consistent with SQLite.
   286    426   # In doing so, we test the following three requirements as a side-effect:
   287    427   #
   288    428   # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
................................................................................
   403    543     expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25}
   404    544   } [expr 4+4]
   405    545   
   406    546   # Some extra cartesian product tests using tables t1 and t2.
   407    547   #
   408    548   do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2
   409    549   do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1
   410         -foreach {tn select res} [list \
          550  +
          551  +do_select_tests e_select-1.4.5 [list                                   \
   411    552       1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
   412    553       2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
   413    554       3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
   414    555       4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
   415         -] {
   416         -  do_execsql_test e_select-1.4.5.$tn $select $res
   417         -}
          556  +]
   418    557   
   419    558   
   420    559   # EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then
   421    560   # the ON expression is evaluated for each row of the cartesian product
   422    561   # and the result cast to a numeric value as if by a CAST expression. All
   423    562   # rows for which the expression evaluates to NULL or zero (integer value
   424    563   # 0 or real value 0.0) are excluded from the dataset.
................................................................................
   444    583     do_join_test e_select-1.3.$tn $select $res
   445    584   }
   446    585   
   447    586   # EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
   448    587   # part of the join-constraint, then each of the column names specified
   449    588   # must exist in the datasets to both the left and right of the join-op.
   450    589   #
   451         -foreach {tn select col} {
          590  +do_select_tests e_select-1.4 -error {
          591  +  cannot join using column %s - column not present in both tables
          592  +} {
   452    593     1 { SELECT * FROM t1, t3 USING (b) }   "b"
   453    594     2 { SELECT * FROM t3, t1 USING (c) }   "c"
   454    595     3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"
   455         -} {
   456         -  set err "cannot join using column $col - column not present in both tables"
   457         -  do_catchsql_test e_select-1.4.$tn $select [list 1 $err]
   458         -}
          596  +} 
   459    597   
   460    598   # EVIDENCE-OF: R-42568-37000 For each pair of namesake columns, the
   461    599   # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
   462    600   # product and the result cast to a numeric value. All rows for which one
   463    601   # or more of the expressions evaluates to NULL or zero are excluded from
   464    602   # the result set.
   465    603   #
   466         -foreach {tn select res} {
          604  +do_select_tests e_select-1.5 {
   467    605     1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
   468    606     2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}
   469         -} {
   470         -  do_execsql_test e_select-1.5.$tn $select $res
   471         -}
          607  +} 
   472    608   
   473    609   # EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
   474    610   # USING clause, the normal rules for handling affinities, collation
   475    611   # sequences and NULL values in comparisons apply.
   476    612   #
   477    613   # EVIDENCE-OF: R-35466-18578 The column from the dataset on the
   478    614   # left-hand side of the join operator is considered to be on the
................................................................................
   544    680     INSERT INTO t7 VALUES('x', 'ex',  24);
   545    681     INSERT INTO t7 VALUES('y', 'why', 25);
   546    682   
   547    683     INSERT INTO t8 VALUES('x', 'abc', 24);
   548    684     INSERT INTO t8 VALUES('z', 'ghi', 26);
   549    685   } {}
   550    686   
   551         -do_execsql_test e_select-1.8.1a {
   552         -  SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)
   553         -} {1}
   554         -do_execsql_test e_select-1.8.1b {
   555         -  SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)
   556         -} {2}
          687  +do_select_tests e_select-1.8 {
          688  +  1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1}
          689  +  1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2}
          690  +  2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1}
          691  +  2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2}
          692  +}
   557    693   
   558         -do_execsql_test e_select-1.8.2a {
   559         -  SELECT count(*) FROM t7 JOIN t8 USING (a)
   560         -} {1}
   561         -do_execsql_test e_select-1.8.2b {
   562         -  SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)
   563         -} {2}
   564    694   
   565    695   # EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the
   566    696   # columns that would normally contain values copied from the right-hand
   567    697   # input dataset.
   568    698   #
   569         -do_execsql_test e_select-1.9.1a {
   570         -  SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)
   571         -} {x ex 24 x abc 24}
   572         -do_execsql_test e_select-1.9.1b {
   573         -  SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)
   574         -} {x ex 24 x abc 24 y why 25 {} {} {}}
   575         -
   576         -do_execsql_test e_select-1.9.2a {
   577         -  SELECT * FROM t7 JOIN t8 USING (a)
   578         -} {x ex 24 abc 24}
   579         -do_execsql_test e_select-1.9.2b {
   580         -  SELECT * FROM t7 LEFT JOIN t8 USING (a)
   581         -} {x ex 24 abc 24 y why 25 {} {}}
          699  +do_select_tests e_select-1.9 {
          700  +  1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}
          701  +  1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 
          702  +     {x ex 24 x abc 24 y why 25 {} {} {}}
          703  +  2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
          704  +  2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}
          705  +}
   582    706   
   583    707   # EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
   584    708   # the join-ops, then an implicit USING clause is added to the
   585    709   # join-constraints. The implicit USING clause contains each of the
   586    710   # column names that appear in both the left and right-hand input
   587    711   # datasets.
   588    712   #
   589         -foreach {tn s1 s2 res} {
   590         -  1 { SELECT * FROM t7 JOIN t8 USING (a) }
   591         -    { SELECT * FROM t7 NATURAL JOIN t8 }
   592         -    {x ex 24 abc 24}
   593         -
   594         -  2 { SELECT * FROM t8 JOIN t7 USING (a) }
   595         -    { SELECT * FROM t8 NATURAL JOIN t7 }
   596         -    {x abc 24 ex 24}
   597         -
   598         -  3 { SELECT * FROM t7 LEFT JOIN t8 USING (a) }
   599         -    { SELECT * FROM t7 NATURAL LEFT JOIN t8 }
   600         -    {x ex 24 abc 24 y why 25 {} {}}
   601         -
   602         -  4 { SELECT * FROM t8 LEFT JOIN t7 USING (a) }
   603         -    { SELECT * FROM t8 NATURAL LEFT JOIN t7 }
   604         -    {x abc 24 ex 24 z ghi 26 {} {}}
   605         -
   606         -  5 { SELECT * FROM t3 JOIN t4 USING (a,c) }
   607         -    { SELECT * FROM t3 NATURAL JOIN t4 }
   608         -    {b 2}
   609         -
   610         -  6 { SELECT * FROM t3 LEFT JOIN t4 USING (a,c) }
   611         -    { SELECT * FROM t3 NATURAL LEFT JOIN t4 }
   612         -    {a 1 b 2}
   613         -} {
   614         -  do_execsql_test e_select-1.10.${tn}a $s1 $res
   615         -  do_execsql_test e_select-1.10.${tn}b $s2 $res
   616         -}
          713  +do_select_tests e_select-1-10 {
          714  +  1a "SELECT * FROM t7 JOIN t8 USING (a)"        {x ex 24 abc 24}
          715  +  1b "SELECT * FROM t7 NATURAL JOIN t8"          {x ex 24 abc 24}
          716  +
          717  +  2a "SELECT * FROM t8 JOIN t7 USING (a)"        {x abc 24 ex 24}
          718  +  2b "SELECT * FROM t8 NATURAL JOIN t7"          {x abc 24 ex 24}
          719  +
          720  +  3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)"   {x ex 24 abc 24 y why 25 {} {}}
          721  +  3b "SELECT * FROM t7 NATURAL LEFT JOIN t8"     {x ex 24 abc 24 y why 25 {} {}}
          722  +
          723  +  4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)"   {x abc 24 ex 24 z ghi 26 {} {}}
          724  +  4b "SELECT * FROM t8 NATURAL LEFT JOIN t7"     {x abc 24 ex 24 z ghi 26 {} {}}
          725  +
          726  +  5a "SELECT * FROM t3 JOIN t4 USING (a,c)"      {b 2}
          727  +  5b "SELECT * FROM t3 NATURAL JOIN t4"          {b 2}
          728  +
          729  +  6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2}
          730  +  6b "SELECT * FROM t3 NATURAL LEFT JOIN t4"     {a 1 b 2}
          731  +} 
   617    732   
   618    733   # EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets
   619    734   # feature no common column names, then the NATURAL keyword has no effect
   620    735   # on the results of the join.
   621    736   #
   622    737   do_execsql_test e_select-1.11.0 {
   623    738     CREATE TABLE t10(x, y);
   624    739     INSERT INTO t10 VALUES(1, 'true');
   625    740     INSERT INTO t10 VALUES(0, 'false');
   626    741   } {}
   627         -foreach {tn s1 s2 res} {
   628         -  1 { SELECT a, x FROM t1 CROSS JOIN t10 }
   629         -    { SELECT a, x FROM t1 NATURAL CROSS JOIN t10 }
   630         -    {a 1 a 0 b 1 b 0 c 1 c 0}
   631         -} {
   632         -  do_execsql_test e_select-1.11.${tn}a $s1 $res
   633         -  do_execsql_test e_select-1.11.${tn}b $s2 $res
          742  +do_select_tests e_select-1-11 {
          743  +  1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}
          744  +  1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}
   634    745   }
   635    746   
   636    747   # EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a
   637    748   # join that specifies the NATURAL keyword.
   638    749   #
   639    750   foreach {tn sql} {
   640    751     1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)}
................................................................................
  1288   1399   # expression "*" then all columns in the input data are substituted for
  1289   1400   # that one expression.
  1290   1401   #
  1291   1402   # EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table
  1292   1403   # or subquery in the FROM clause followed by ".*" then all columns from
  1293   1404   # the named table or subquery are substituted for the single expression.
  1294   1405   #
  1295         -foreach {tn select res} {
         1406  +do_select_tests e_select-4.1 {
  1296   1407     1  "SELECT * FROM z1 LIMIT 1"             {51.65 -59.58 belfries}
  1297   1408     2  "SELECT * FROM z1,z2 LIMIT 1"          {51.65 -59.58 belfries {} 21}
  1298   1409     3  "SELECT z1.* FROM z1,z2 LIMIT 1"       {51.65 -59.58 belfries}
  1299   1410     4  "SELECT z2.* FROM z1,z2 LIMIT 1"       {{} 21}
  1300   1411     5  "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}
  1301   1412   
  1302   1413     6  "SELECT count(*), * FROM z1"           {6 63 born -26}
................................................................................
  1305   1416   
  1306   1417     9  "SELECT *,* FROM z1,z2 LIMIT 1" {        
  1307   1418        51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
  1308   1419     }
  1309   1420     10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
  1310   1421        51.65 -59.58 belfries 51.65 -59.58 belfries
  1311   1422     }
  1312         -} {
  1313         -  do_execsql_test e_select-4.1.$tn $select [list {*}$res]
  1314   1423   }
  1315   1424   
  1316   1425   # EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*"
  1317   1426   # expression in any context other than than a result expression list.
  1318   1427   #
  1319   1428   # EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
  1320   1429   # "alias.*" expression in a simple SELECT query that does not have a
................................................................................
  1349   1458     7   "SELECT a, *, b, c FROM z1"                   6
  1350   1459   } {
  1351   1460     set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]
  1352   1461     do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol
  1353   1462     sqlite3_finalize $::stmt
  1354   1463   }
  1355   1464   
         1465  +
         1466  +
         1467  +# In lang_select.html, a non-aggregate query is defined as any simple SELECT
         1468  +# that has no GROUP BY clause and no aggregate expressions in the result
         1469  +# expression list. Other queries are aggregate queries. Test cases
         1470  +# e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of
         1471  +# simple SELECT that is different for aggregate and non-aggregate queries
         1472  +# verify (in a way) that these definitions are consistent:
         1473  +#
         1474  +# EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate
         1475  +# query if it contains either a GROUP BY clause or one or more aggregate
         1476  +# functions in the result-set.
         1477  +#
         1478  +# EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no
         1479  +# aggregate functions or a GROUP BY clause, it is a non-aggregate query.
         1480  +#
         1481  +
  1356   1482   # EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate
  1357   1483   # query, then each expression in the result expression list is evaluated
  1358   1484   # for each row in the dataset filtered by the WHERE clause.
  1359   1485   #
  1360         -#   By other definitions in lang_select.html, a non-aggregate query is
  1361         -#   any simple SELECT that has no GROUP BY clause and no aggregate expressions
  1362         -#   in the result expression list. These tests also verify (in a way) that
  1363         -#   that definition is consistent:
  1364         -#
  1365         -do_execsql_test e_select-4.4.1 {
  1366         -  SELECT a, b FROM z1
  1367         -} {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}
         1486  +do_select_tests e_select-4.4 {
         1487  +  1 "SELECT a, b FROM z1"
         1488  +    {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}
  1368   1489   
  1369         -do_execsql_test e_select-4.4.2 {
  1370         -  SELECT a IS NULL, b+1, * FROM z1
  1371         -} [list {*}{
         1490  +  2 "SELECT a IS NULL, b+1, * FROM z1" {
  1372   1491           0 -58.58   51.65 -59.58 belfries
  1373   1492           0 {}       -5 {} 75            
  1374   1493           0 -22.18   -2.2 -23.18 suiters
  1375   1494           1 68       {} 67 quartets    
  1376   1495           0 -31.3    -1.04 -32.3 aspen
  1377   1496           0 1        63 born -26
  1378         -}]
         1497  +  }
  1379   1498   
  1380         -do_execsql_test e_select-4.4.3 {
  1381         -  SELECT 32*32, d||e FROM z2
  1382         -} {1024 {} 1024 366}
         1499  +  3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366}
         1500  +}
         1501  +
         1502  +
         1503  +# Test cases e_select-4.5.* and e_select-4.6.* together show that:
         1504  +#
         1505  +# EVIDENCE-OF: R-51988-01124 The single row of result-set data created
         1506  +# by evaluating the aggregate and non-aggregate expressions in the
         1507  +# result-set forms the result of an aggregate query without a GROUP BY
         1508  +# clause.
         1509  +#
  1383   1510   
  1384   1511   # EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate
  1385   1512   # query without a GROUP BY clause, then each aggregate expression in the
  1386   1513   # result-set is evaluated once across the entire dataset.
  1387   1514   #
  1388         -foreach {tn select res} {
  1389         -  5.1 "SELECT count(a), max(a), count(b), max(b) FROM z1"      {5 63 5 born}
  1390         -  5.2 "SELECT count(*), max(1)"                                {1 1}
         1515  +do_select_tests e_select-4.5 {
         1516  +  1 "SELECT count(a), max(a), count(b), max(b) FROM z1"      {5 63 5 born}
         1517  +  2 "SELECT count(*), max(1)"                                {1 1}
  1391   1518   
  1392         -  5.3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3"           {-43.06}
  1393         -  5.4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3"           {-38.06}
  1394         -  5.5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}
  1395         -} {
  1396         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
         1519  +  3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3"           {-43.06}
         1520  +  4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3"           {-38.06}
         1521  +  5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}
  1397   1522   }
  1398   1523   
  1399   1524   # EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the
  1400   1525   # result-set is evaluated once for an arbitrarily selected row of the
  1401   1526   # dataset.
  1402   1527   #
  1403   1528   # EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used
................................................................................
  1418   1543   
  1419   1544     CREATE TABLE a2(one PRIMARY KEY, three);
  1420   1545     INSERT INTO a2 VALUES(1, 1);
  1421   1546     INSERT INTO a2 VALUES(3, 2);
  1422   1547     INSERT INTO a2 VALUES(6, 3);
  1423   1548     INSERT INTO a2 VALUES(10, 4);
  1424   1549   } {}
  1425         -foreach {tn select res} {
  1426         -  6.1 "SELECT one, two, count(*) FROM a1"                        {4 10 4} 
  1427         -  6.2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {2 3 2} 
  1428         -  6.3 "SELECT one, two, count(*) FROM a1 WHERE one>3"            {4 10 1} 
  1429         -  6.4 "SELECT *, count(*) FROM a1 JOIN a2"                       {4 10 10 4 16} 
  1430         -  6.5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  1431         -  6.6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  1432         -  6.7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6}
  1433         -} {
  1434         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
         1550  +do_select_tests e_select-4.6 {
         1551  +  1 "SELECT one, two, count(*) FROM a1"                        {4 10 4} 
         1552  +  2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {2 3 2} 
         1553  +  3 "SELECT one, two, count(*) FROM a1 WHERE one>3"            {4 10 1} 
         1554  +  4 "SELECT *, count(*) FROM a1 JOIN a2"                       {4 10 10 4 16} 
         1555  +  5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
         1556  +  6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
         1557  +  7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6}
  1435   1558   }
  1436   1559   
  1437   1560   # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then
  1438   1561   # each non-aggregate expression is evaluated against a row consisting
  1439   1562   # entirely of NULL values.
  1440   1563   #
  1441         -foreach {tn select res} {
  1442         -  7.1  "SELECT one, two, count(*) FROM a1 WHERE 0"           {{} {} 0}
  1443         -  7.2  "SELECT sum(two), * FROM a1, a2 WHERE three>5"        {{} {} {} {} {}}
  1444         -  7.3  "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" {
         1564  +do_select_tests e_select-4.7 {
         1565  +  1  "SELECT one, two, count(*) FROM a1 WHERE 0"           {{} {} 0}
         1566  +  2  "SELECT sum(two), * FROM a1, a2 WHERE three>5"        {{} {} {} {} {}}
         1567  +  3  "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" {
  1445   1568       1 1 1
  1446   1569     }
  1447         -} {
  1448         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
  1449         -}  
         1570  +} 
  1450   1571   
  1451   1572   # EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY
  1452   1573   # clause always returns exactly one row of data, even if there are zero
  1453   1574   # rows of input data.
  1454   1575   #
  1455   1576   foreach {tn select} {
  1456   1577     8.1  "SELECT count(*) FROM a1"
................................................................................
  1498   1619   # EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate
  1499   1620   # query with a GROUP BY clause, then each of the expressions specified
  1500   1621   # as part of the GROUP BY clause is evaluated for each row of the
  1501   1622   # dataset. Each row is then assigned to a "group" based on the results;
  1502   1623   # rows for which the results of evaluating the GROUP BY expressions are
  1503   1624   # the same are assigned to the same group.
  1504   1625   #
  1505         -foreach {tn select res} {
  1506         -  9.1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
         1626  +#   These tests also show that the following is not untrue:
         1627  +#
         1628  +# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
         1629  +# not have to be expressions that appear in the result.
         1630  +#
         1631  +do_select_tests e_select-4.9 {
         1632  +  1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
  1507   1633       4,5 f   1 o   7,6   s 3,2 t
  1508   1634     }
  1509         -  9.2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
         1635  +  2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
  1510   1636       1,4,3,2 10    5,7,6 18
  1511   1637     }
  1512         -  9.3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
         1638  +  3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
  1513   1639       4  1,5    2,6   3,7
  1514   1640     }
  1515         -  9.4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
         1641  +  4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
  1516   1642       4,3,5,7,6    1,2
  1517   1643     }
  1518         -} {
  1519         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
  1520         -}  
         1644  +}
  1521   1645   
  1522   1646   # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
  1523   1647   # values are considered equal.
  1524   1648   #
  1525         -foreach {tn select res} {
  1526         -  10.1  "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1   3   2,4}
  1527         -  10.2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {
  1528         -    4 1
  1529         -  }
  1530         -} {
  1531         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
  1532         -}  
         1649  +do_select_tests e_select-4.10 {
         1650  +  1  "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1   3   2,4}
         1651  +  2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}
         1652  +} 
  1533   1653   
  1534   1654   # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
  1535   1655   # sequence with which to compare text values apply when evaluating
  1536   1656   # expressions in a GROUP BY clause.
  1537   1657   #
  1538         -foreach {tn select res} {
  1539         -  11.1  "SELECT count(*) FROM b3 GROUP BY b"      {1 1 1 1}
  1540         -  11.2  "SELECT count(*) FROM b3 GROUP BY a"      {2 2}
  1541         -  11.3  "SELECT count(*) FROM b3 GROUP BY +b"     {1 1 1 1}
  1542         -  11.4  "SELECT count(*) FROM b3 GROUP BY +a"     {2 2}
  1543         -  11.5  "SELECT count(*) FROM b3 GROUP BY b||''"  {1 1 1 1}
  1544         -  11.6  "SELECT count(*) FROM b3 GROUP BY a||''"  {1 1 1 1}
  1545         -} {
  1546         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
         1658  +do_select_tests e_select-4.11 {
         1659  +  1  "SELECT count(*) FROM b3 GROUP BY b"      {1 1 1 1}
         1660  +  2  "SELECT count(*) FROM b3 GROUP BY a"      {2 2}
         1661  +  3  "SELECT count(*) FROM b3 GROUP BY +b"     {1 1 1 1}
         1662  +  4  "SELECT count(*) FROM b3 GROUP BY +a"     {2 2}
         1663  +  5  "SELECT count(*) FROM b3 GROUP BY b||''"  {1 1 1 1}
         1664  +  6  "SELECT count(*) FROM b3 GROUP BY a||''"  {1 1 1 1}
  1547   1665   }
  1548   1666   
  1549   1667   # EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may
  1550   1668   # not be aggregate expressions.
  1551   1669   #
  1552   1670   foreach {tn select} {
  1553   1671     12.1  "SELECT * FROM b3 GROUP BY count(*)"
................................................................................
  1572   1690   #
  1573   1691   # EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate
  1574   1692   # expression, it is evaluated with respect to an arbitrarily selected
  1575   1693   # row from the group.
  1576   1694   #
  1577   1695   #   Tested by e_select-4.13.2.*
  1578   1696   #
         1697  +#   Tests in this block also show that this is not untrue:
         1698  +#
         1699  +# EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values,
         1700  +# even aggregate functions, that are not in the result.
         1701  +#
  1579   1702   do_execsql_test e_select-4.13.0 {
  1580   1703     CREATE TABLE c1(up, down);
  1581   1704     INSERT INTO c1 VALUES('x', 1);
  1582   1705     INSERT INTO c1 VALUES('x', 2);
  1583   1706     INSERT INTO c1 VALUES('x', 4);
  1584   1707     INSERT INTO c1 VALUES('x', 8);
  1585   1708     INSERT INTO c1 VALUES('y', 16);
................................................................................
  1601   1724     INSERT INTO c3 VALUES(2,  'helium');
  1602   1725     INSERT INTO c3 VALUES(3,  'lithium');
  1603   1726     INSERT INTO c3 VALUES(4,  'beryllium');
  1604   1727     INSERT INTO c3 VALUES(5,  'boron');
  1605   1728     INSERT INTO c3 VALUES(94, 'plutonium');
  1606   1729   } {}
  1607   1730   
  1608         -foreach {tn select res} {
  1609         -  13.1.1  "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}
  1610         -  13.1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
  1611         -  13.1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
  1612         -  13.1.4  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}
         1731  +do_select_tests e_select-4.13 {
         1732  +  1.1  "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}
         1733  +  1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
         1734  +  1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
         1735  +  1.4  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}
  1613   1736   
  1614         -  13.2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
  1615         -  13.2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}
         1737  +  2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
         1738  +  2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}
  1616   1739   
  1617         -  13.2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6"  {9 36}
  1618         -} {
  1619         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
         1740  +  2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6"  {9 36}
  1620   1741   }
  1621   1742   
  1622   1743   # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then
  1623   1744   # evaluated once for each group of rows.
  1624   1745   #
  1625   1746   # EVIDENCE-OF: R-53735-47017 If the expression is an aggregate
  1626   1747   # expression, it is evaluated across all rows in the group.
  1627   1748   #
  1628         -foreach {tn select res} {
  1629         -  14.1  "SELECT sum(down) FROM c1 GROUP BY up" {15 48}
  1630         -  14.2  "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)"     {54 36 27 21 39 28}
  1631         -  14.3  "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)"     {80 36 40 21}
  1632         -  14.4  "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22}
  1633         -  14.5  "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2"
         1749  +do_select_tests e_select-4.15 {
         1750  +  1  "SELECT sum(down) FROM c1 GROUP BY up" {15 48}
         1751  +  2  "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)"     {54 36 27 21 39 28}
         1752  +  3  "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)"     {80 36 40 21}
         1753  +  4  "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22}
         1754  +  5  "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2"
  1634   1755           {3 4.33 1 2.0}
  1635         -} {
  1636         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
  1637         -}
         1756  +} 
  1638   1757   
  1639   1758   # EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single
  1640   1759   # arbitrarily chosen row from within the group.
  1641   1760   #
  1642   1761   # EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate
  1643   1762   # expression in the result-set, then all such expressions are evaluated
  1644   1763   # for the same row.
  1645   1764   #
  1646         -foreach {tn select res} {
  1647         -  15.1  "SELECT i, j FROM c2 GROUP BY i%2"             {8 28   9 36}
  1648         -  15.2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28}
  1649         -  15.3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  1650         -  15.4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  1651         -  15.5  "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"
         1765  +do_select_tests e_select-4.15 {
         1766  +  1  "SELECT i, j FROM c2 GROUP BY i%2"             {8 28   9 36}
         1767  +  2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28}
         1768  +  3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
         1769  +  4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
         1770  +  5  "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"
  1652   1771           {2 5 boron   2 2 helium   1 3 lithium}
  1653         -} {
  1654         -  do_execsql_test e_select-4.$tn $select [list {*}$res]
  1655         -}
         1772  +} 
  1656   1773   
  1657   1774   # EVIDENCE-OF: R-19334-12811 Each group of input dataset rows
  1658   1775   # contributes a single row to the set of result rows.
  1659   1776   #
  1660   1777   # EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the
  1661   1778   # DISTINCT keyword, the number of rows returned by an aggregate query
  1662   1779   # with a GROUP BY clause is the same as the number of groups of rows
  1663   1780   # produced by applying the GROUP BY and HAVING clauses to the filtered
  1664   1781   # input dataset.
  1665   1782   #
  1666         -foreach {tn select nRow} {
  1667         -  16.1  "SELECT i, j FROM c2 GROUP BY i%2"          2
  1668         -  16.2  "SELECT i, j FROM c2 GROUP BY i"            9
  1669         -  16.3  "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4
  1670         -} {
  1671         -  set rows 0
  1672         -  db eval $select {incr rows}
  1673         -  do_test e_select-4.$tn [list set rows] $nRow
  1674         -}
         1783  +do_select_tests e_select.4.16 -count {
         1784  +  1  "SELECT i, j FROM c2 GROUP BY i%2"          2
         1785  +  2  "SELECT i, j FROM c2 GROUP BY i"            9
         1786  +  3  "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4
         1787  +} 
  1675   1788   
  1676   1789   #-------------------------------------------------------------------------
  1677   1790   # The following tests attempt to verify statements made regarding the ALL
  1678   1791   # and DISTINCT keywords.
  1679   1792   #
  1680   1793   drop_all_tables
  1681   1794   do_execsql_test e_select-5.1.0 {
................................................................................
  1708   1821     INSERT INTO h3 VALUES(8, '2,4');
  1709   1822     INSERT INTO h3 VALUES(9, '3');
  1710   1823   } {}
  1711   1824   
  1712   1825   # EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may
  1713   1826   # follow the SELECT keyword in a simple SELECT statement.
  1714   1827   #
  1715         -do_execsql_test e_select-5.1.1 { SELECT ALL a FROM h1      } {1 1 1 4 4 4}
  1716         -do_execsql_test e_select-5.1.2 { SELECT DISTINCT a FROM h1 } {1 4}
         1828  +do_select_tests e_select-5.1 {
         1829  +  1   "SELECT ALL a FROM h1"      {1 1 1 4 4 4}
         1830  +  2   "SELECT DISTINCT a FROM h1" {1 4}
         1831  +}
  1717   1832   
  1718   1833   # EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
  1719   1834   # the entire set of result rows are returned by the SELECT.
  1720   1835   #
  1721   1836   # EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present,
  1722   1837   # then the behaviour is as if ALL were specified.
  1723   1838   #
................................................................................
  1724   1839   # EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
  1725   1840   # then duplicate rows are removed from the set of result rows before it
  1726   1841   # is returned.
  1727   1842   #
  1728   1843   #   The three testable statements above are tested by e_select-5.2.*,
  1729   1844   #   5.3.* and 5.4.* respectively.
  1730   1845   #
  1731         -foreach {tn select res} {
         1846  +do_select_tests e_select-5 {
  1732   1847     3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four}
  1733   1848     3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four}
  1734   1849   
  1735   1850     3.1 "SELECT x FROM h2" {One Two Three Four one two three four}
  1736   1851     3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four}
  1737   1852   
  1738   1853     4.1 "SELECT DISTINCT x FROM h2" {four one three two}
  1739   1854     4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one}
  1740         -} {
  1741         -  do_execsql_test e_select-5.$tn $select [list {*}$res]
  1742         -}
         1855  +} 
  1743   1856   
  1744   1857   # EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate
  1745   1858   # rows, two NULL values are considered to be equal.
  1746   1859   #
  1747         -do_execsql_test e_select-5.5.1 { SELECT DISTINCT d FROM h3 } {{} 2 2,3 2,4 3}
         1860  +do_select_tests e_select-5.5 {
         1861  +  1  "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3}
         1862  +}
  1748   1863   
  1749   1864   # EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation
  1750   1865   # sequence to compare text values with apply.
  1751   1866   #
  1752         -foreach {tn select res} {
  1753         -  6.1  "SELECT DISTINCT b FROM h1"                  {I IV four i iv one}
  1754         -  6.2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {four i iv one}
  1755         -  6.3  "SELECT DISTINCT x FROM h2"                  {four one three two}
  1756         -  6.4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
         1867  +do_select_tests e_select-5.6 {
         1868  +  1  "SELECT DISTINCT b FROM h1"                  {I IV four i iv one}
         1869  +  2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {four i iv one}
         1870  +  3  "SELECT DISTINCT x FROM h2"                  {four one three two}
         1871  +  4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
  1757   1872       Four One Three Two four one three two
  1758   1873     }
  1759         -} {
  1760         -  do_execsql_test e_select-5.$tn $select [list {*}$res]
  1761   1874   }
  1762   1875   
  1763   1876   #-------------------------------------------------------------------------
  1764   1877   # The following tests - e_select-7.* - test that statements made to do
  1765   1878   # with compound SELECT statements are correct.
  1766   1879   #
  1767   1880   
................................................................................
  1774   1887   #
  1775   1888   drop_all_tables
  1776   1889   do_execsql_test e_select-7.1.0 {
  1777   1890     CREATE TABLE j1(a, b, c);
  1778   1891     CREATE TABLE j2(e, f);
  1779   1892     CREATE TABLE j3(g);
  1780   1893   } {}
  1781         -foreach {tn select op} {
         1894  +do_select_tests e_select-7.1 -error {
         1895  +  SELECTs to the left and right of %s do not have the same number of result columns
         1896  +} {
  1782   1897     1   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {UNION ALL}
  1783   1898     2   "SELECT *    FROM j1    UNION ALL SELECT * FROM j3"    {UNION ALL}
  1784   1899     3   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {UNION ALL}
  1785   1900     4   "SELECT a, b FROM j1    UNION ALL SELECT * FROM j3,j2" {UNION ALL}
  1786   1901     5   "SELECT *    FROM j3,j2 UNION ALL SELECT a, b FROM j1" {UNION ALL}
  1787   1902   
  1788   1903     6   "SELECT a, b FROM j1    UNION SELECT g FROM j3"        {UNION}
................................................................................
  1798   1913     15  "SELECT *    FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT}
  1799   1914   
  1800   1915     16  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
  1801   1916     17  "SELECT *    FROM j1    EXCEPT SELECT * FROM j3"       {EXCEPT}
  1802   1917     18  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
  1803   1918     19  "SELECT a, b FROM j1    EXCEPT SELECT * FROM j3,j2"    {EXCEPT}
  1804   1919     20  "SELECT *    FROM j3,j2 EXCEPT SELECT a, b FROM j1"    {EXCEPT}
  1805         -} {
  1806         -  set    err "SELECTs to the left and right of "
  1807         -  append err $op
  1808         -  append err " do not have the same number of result columns"
  1809         -  do_catchsql_test e_select-7.1.$tn $select [list 1 $err]
  1810         -}
         1920  +} 
  1811   1921   
  1812   1922   # EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must
  1813   1923   # be simple SELECT statements, they may not contain ORDER BY or LIMIT
  1814   1924   # clauses.
  1815   1925   # 
  1816   1926   foreach {tn select op1 op2} {
  1817   1927     1   "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" 
................................................................................
  1923   2033     INSERT INTO q2 VALUES('beauty', 2);
  1924   2034     INSERT INTO q2 VALUES(-65.91, 4);
  1925   2035     INSERT INTO q2 VALUES('emanating', -16.56);
  1926   2036   
  1927   2037     INSERT INTO q3 VALUES('beauty', 2);
  1928   2038     INSERT INTO q3 VALUES('beauty', 2);
  1929   2039   } {}
  1930         -foreach {tn select res} {
         2040  +do_select_tests e_select-7.4 {
  1931   2041     1   {SELECT a FROM q1 UNION ALL SELECT d FROM q2}
  1932   2042         {16 legible beauty legible beauty -65.91 emanating}
  1933   2043   
  1934   2044     2   {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1}
  1935   2045         {16 -87.66 {} x legible 1}
  1936   2046   
  1937   2047     3   {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 
  1938   2048         {3 -16.56}
  1939   2049   
  1940   2050     4   {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 
  1941   2051         {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2}
  1942         -} {
  1943         -  do_execsql_test e_select-7.4.$tn $select [list {*}$res]
  1944         -}
         2052  +} 
  1945   2053   
  1946   2054   # EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as
  1947   2055   # UNION ALL, except that duplicate rows are removed from the final
  1948   2056   # result set.
  1949   2057   #
  1950         -foreach {tn select res} {
         2058  +do_select_tests e_select-7.5 {
  1951   2059     1   {SELECT a FROM q1 UNION SELECT d FROM q2}
  1952   2060         {-65.91 16 beauty emanating legible}
  1953   2061   
  1954   2062     2   {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1}
  1955   2063         {16 -87.66 {} x legible 1}
  1956   2064   
  1957   2065     3   {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 
  1958   2066         {-16.56 3}
  1959   2067   
  1960   2068     4   {SELECT * FROM q2 UNION SELECT * FROM q3} 
  1961   2069         {-65.91 4 beauty 2 emanating -16.56 legible 1}
  1962         -} {
  1963         -  do_execsql_test e_select-7.5.$tn $select [list {*}$res]
  1964         -}
         2070  +} 
  1965   2071   
  1966   2072   # EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the
  1967   2073   # intersection of the results of the left and right SELECTs.
  1968   2074   #
  1969         -foreach {tn select res} {
         2075  +do_select_tests e_select-7.6 {
  1970   2076     1   {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible}
  1971   2077     2   {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2}
  1972         -} {
  1973         -  do_execsql_test e_select-7.6.$tn $select [list {*}$res]
  1974   2078   }
  1975   2079   
  1976   2080   # EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of
  1977   2081   # rows returned by the left SELECT that are not also returned by the
  1978   2082   # right-hand SELECT.
  1979   2083   #
  1980         -foreach {tn select res} {
         2084  +do_select_tests e_select-7.7 {
  1981   2085     1   {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16}
  1982   2086   
  1983   2087     2   {SELECT * FROM q2 EXCEPT SELECT * FROM q3} 
  1984   2088         {-65.91 4 emanating -16.56 legible 1}
  1985         -} {
  1986         -  do_execsql_test e_select-7.7.$tn $select [list {*}$res]
  1987   2089   }
  1988   2090   
  1989   2091   # EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results
  1990   2092   # of INTERSECT and EXCEPT operators before the result set is returned.
  1991   2093   #
  1992         -foreach {tn select res} {
         2094  +do_select_tests e_select-7.8 {
  1993   2095     0   {SELECT * FROM q3} {beauty 2 beauty 2}
  1994   2096   
  1995   2097     1   {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2}
  1996   2098     2   {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1}  {beauty 2}
  1997         -} {
  1998         -  do_execsql_test e_select-7.8.$tn $select [list {*}$res]
  1999   2099   }
  2000   2100   
  2001   2101   # EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate
  2002   2102   # rows for the results of compound SELECT operators, NULL values are
  2003   2103   # considered equal to other NULL values and distinct from all non-NULL
  2004   2104   # values.
  2005   2105   #
  2006   2106   db nullvalue null
  2007         -foreach {tn select res} {
         2107  +do_select_tests e_select-7.9 {
  2008   2108     1   {SELECT NULL UNION ALL SELECT NULL} {null null}
  2009   2109     2   {SELECT NULL UNION     SELECT NULL} {null}
  2010   2110     3   {SELECT NULL INTERSECT SELECT NULL} {null}
  2011   2111     4   {SELECT NULL EXCEPT    SELECT NULL} {}
  2012   2112   
  2013   2113     5   {SELECT NULL UNION ALL SELECT 'ab'} {null ab}
  2014   2114     6   {SELECT NULL UNION     SELECT 'ab'} {null ab}
................................................................................
  2020   2120     11  {SELECT NULL INTERSECT SELECT 0} {}
  2021   2121     12  {SELECT NULL EXCEPT    SELECT 0} {null}
  2022   2122   
  2023   2123     13  {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2}
  2024   2124     14  {SELECT c FROM q1 UNION     SELECT g FROM q3} {null -42.47 2}
  2025   2125     15  {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {}
  2026   2126     16  {SELECT c FROM q1 EXCEPT    SELECT g FROM q3} {null -42.47}
  2027         -} {
  2028         -  do_execsql_test e_select-7.9.$tn $select [list {*}$res]
  2029   2127   }
  2030   2128   db nullvalue {} 
  2031   2129   
  2032   2130   # EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two
  2033   2131   # text values is determined as if the columns of the left and right-hand
  2034   2132   # SELECT statements were the left and right-hand operands of the equals
  2035   2133   # (=) operator, except that greater precedence is not assigned to a
................................................................................
  2036   2134   # collation sequence specified with the postfix COLLATE operator.
  2037   2135   #
  2038   2136   drop_all_tables
  2039   2137   do_execsql_test e_select-7.10.0 {
  2040   2138     CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c);
  2041   2139     INSERT INTO y1 VALUES('Abc', 'abc', 'aBC');
  2042   2140   } {}
  2043         -foreach {tn select res} {
         2141  +do_select_tests e_select-7.10 {
  2044   2142     1   {SELECT 'abc'                UNION SELECT 'ABC'} {ABC abc}
  2045   2143     2   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC}
  2046   2144     3   {SELECT 'abc'                UNION SELECT 'ABC' COLLATE nocase} {ABC}
  2047   2145     4   {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc}
  2048   2146     5   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC}
  2049   2147   
  2050   2148     6   {SELECT a FROM y1 UNION SELECT b FROM y1}                {abc}
  2051   2149     7   {SELECT b FROM y1 UNION SELECT a FROM y1}                {Abc abc}
  2052   2150     8   {SELECT a FROM y1 UNION SELECT c FROM y1}                {aBC}
  2053   2151   
  2054   2152     9   {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC}
  2055         -
  2056         -} {
  2057         -  do_execsql_test e_select-7.10.$tn $select [list {*}$res]
  2058   2153   }
  2059   2154   
  2060   2155   # EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to
  2061   2156   # any values when comparing rows as part of a compound SELECT.
  2062   2157   #
  2063   2158   drop_all_tables
  2064   2159   do_execsql_test e_select-7.10.0 {
................................................................................
  2065   2160     CREATE TABLE w1(a TEXT, b NUMBER);
  2066   2161     CREATE TABLE w2(a, b TEXT);
  2067   2162   
  2068   2163     INSERT INTO w1 VALUES('1', 4.1);
  2069   2164     INSERT INTO w2 VALUES(1, 4.1);
  2070   2165   } {}
  2071   2166   
  2072         -foreach {tn select res} {
         2167  +do_select_tests e_select-7.11 {
  2073   2168     1  { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1}
  2074   2169     2  { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1}
  2075   2170     3  { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1}
  2076   2171     4  { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1}
  2077   2172   
  2078   2173     5  { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {}
  2079   2174     6  { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {}
................................................................................
  2080   2175     7  { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {}
  2081   2176     8  { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {}
  2082   2177   
  2083   2178     9  { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1}
  2084   2179     10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1}
  2085   2180     11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1}
  2086   2181     12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1}
  2087         -} {
  2088         -  do_execsql_test e_select-7.11.$tn $select [list {*}$res]
  2089   2182   }
  2090   2183   
  2091   2184   
  2092   2185   # EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are
  2093   2186   # connected into a compound SELECT, they group from left to right. In
  2094   2187   # other words, if "A", "B" and "C" are all simple SELECT statements, (A
  2095   2188   # op B op C) is processed as ((A op B) op C).
................................................................................
  2169   2262     INSERT INTO d2 VALUES('lad', 'relenting');
  2170   2263   } {}
  2171   2264   
  2172   2265   # EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results
  2173   2266   # of evaluating the left-most expression in the ORDER BY list, then ties
  2174   2267   # are broken by evaluating the second left-most expression and so on.
  2175   2268   #
  2176         -foreach {tn select res} {
         2269  +do_select_tests e_select-8.1 {
  2177   2270     1  "SELECT * FROM d1 ORDER BY x, y, z" {
  2178   2271        1 2 -20    1 2 3    1 2 7    1 2 8    
  2179   2272        1 4  93    1 5 -1   2 4 93   2 5 -1
  2180   2273     }
  2181         -} {
  2182         -  do_execsql_test e_select-8.1.$tn $select [list {*}$res]
  2183   2274   }
  2184   2275   
  2185   2276   # EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally
  2186   2277   # followed by one of the keywords ASC (smaller values are returned
  2187   2278   # first) or DESC (larger values are returned first).
  2188   2279   #
  2189   2280   #   Test cases e_select-8.2.* test the above.
................................................................................
  2190   2281   #
  2191   2282   # EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows
  2192   2283   # are sorted in ascending (smaller values first) order by default.
  2193   2284   #
  2194   2285   #   Test cases e_select-8.3.* test the above. All 8.3 test cases are
  2195   2286   #   copies of 8.2 test cases with the explicit "ASC" removed.
  2196   2287   #
  2197         -foreach {tn select res} {
         2288  +do_select_tests e_select-8 {
  2198   2289     2.1  "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" {
  2199   2290        1 2 -20    1 2 3    1 2 7    1 2 8    
  2200   2291        1 4  93    1 5 -1   2 4 93   2 5 -1
  2201   2292     }
  2202   2293     2.2  "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" {
  2203   2294        2 5 -1     2 4 93   1 5 -1   1 4  93    
  2204   2295        1 2 8      1 2 7    1 2 3    1 2 -20    
................................................................................
  2220   2311        2 4 93   2 5 -1     1 2 8      1 2 7    
  2221   2312        1 2 3    1 2 -20    1 4  93    1 5 -1   
  2222   2313     }
  2223   2314     3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" {
  2224   2315        2 4 93   2 5 -1     1 2 -20    1 2 3    
  2225   2316        1 2 7    1 2 8      1 4  93    1 5 -1   
  2226   2317     }
  2227         -} {
  2228         -  do_execsql_test e_select-8.$tn $select [list {*}$res]
  2229   2318   }
  2230   2319   
  2231   2320   # EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant
  2232   2321   # integer K then the expression is considered an alias for the K-th
  2233   2322   # column of the result set (columns are numbered from left to right
  2234   2323   # starting with 1).
  2235   2324   #
  2236         -foreach {tn select res} {
         2325  +do_select_tests e_select-8.4 {
  2237   2326     1  "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" {
  2238   2327        1 2 -20    1 2 3    1 2 7    1 2 8    
  2239   2328        1 4  93    1 5 -1   2 4 93   2 5 -1
  2240   2329     }
  2241   2330     2  "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" {
  2242   2331        2 5 -1     2 4 93   1 5 -1   1 4  93    
  2243   2332        1 2 8      1 2 7    1 2 3    1 2 -20    
................................................................................
  2266   2355        3 1     8 1    7 1   -20 1 
  2267   2356        93 1   -1 1   -1 2   93 2
  2268   2357     }
  2269   2358     9  "SELECT z, x FROM d1 ORDER BY 1" {
  2270   2359        -20 1  -1 2   -1 1   3 1     
  2271   2360        7 1     8 1   93 2   93 1   
  2272   2361     }
  2273         -} {
  2274         -  do_execsql_test e_select-8.4.$tn $select [list {*}$res]
  2275   2362   }
  2276   2363   
  2277   2364   # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
  2278   2365   # that corresponds to the alias of one of the output columns, then the
  2279   2366   # expression is considered an alias for that column.
  2280   2367   #
  2281         -foreach {tn select res} {
         2368  +do_select_tests e_select-8.5 {
  2282   2369     1   "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
  2283   2370       -19 0 0 4 8 9 94 94
  2284   2371     }
  2285   2372     2   "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
  2286   2373       94 94 9 8 4 0 0 -19
  2287   2374     }
  2288   2375     3  "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
  2289   2376       3 1    8 1    7 1    -20 1    93 1    -1 1    -1 2    93 2
  2290   2377     }
  2291   2378     4  "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
  2292   2379       -20 1    -1 2    -1 1    3 1    7 1    8 1    93 2    93 1
  2293   2380     }
  2294         -} {
  2295         -  do_execsql_test e_select-8.5.$tn $select [list {*}$res]
  2296   2381   }
  2297   2382   
  2298   2383   # EVIDENCE-OF: R-27923-38747 Otherwise, if the ORDER BY expression is
  2299   2384   # any other expression, it is evaluated and the the returned value used
  2300   2385   # to order the output rows.
  2301   2386   #
  2302   2387   # EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT,
  2303   2388   # then an ORDER BY may contain any arbitrary expressions.
  2304   2389   #
  2305         -foreach {tn select res} {
         2390  +do_select_tests e_select-8.6 {
  2306   2391     1   "SELECT * FROM d1 ORDER BY x+y+z" {
  2307   2392       1 2 -20    1 5 -1    1 2 3    2 5 -1 
  2308   2393       1 2 7      1 2 8     1 4 93   2 4 93
  2309   2394     }
  2310   2395     2   "SELECT * FROM d1 ORDER BY x*z" {
  2311   2396       1 2 -20    2 5 -1    1 5 -1    1 2 3 
  2312   2397       1 2 7      1 2 8     1 4 93    2 4 93
  2313   2398     }
  2314   2399     3   "SELECT * FROM d1 ORDER BY y*z" {
  2315   2400       1 2 -20    2 5 -1    1 5 -1    1 2 3 
  2316   2401       1 2 7      1 2 8     2 4 93    1 4 93
  2317   2402     }
  2318         -} {
  2319         -  do_execsql_test e_select-8.6.$tn $select [list {*}$res]
  2320   2403   }
  2321   2404   
  2322   2405   # EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound
  2323   2406   # SELECT, then ORDER BY expressions that are not aliases to output
  2324   2407   # columns must be exactly the same as an expression used as an output
  2325   2408   # column.
  2326   2409   #
  2327         -foreach {tn select violation} {
         2410  +do_select_tests e_select-8.7.1 -error {
         2411  +  %s ORDER BY term does not match any column in the result set
         2412  +} {
  2328   2413     1   "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z"        1st
  2329   2414     2   "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd
  2330         -} {
  2331         -  set err "$violation ORDER BY term does not match any column in the result set"
  2332         -  do_catchsql_test e_select-8.7.1.$tn $select [list 1 $err]
  2333         -}
  2334         -foreach {tn select res} {
         2415  +} 
         2416  +
         2417  +do_select_tests e_select-8.7.2 {
  2335   2418     1   "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" {
  2336   2419       -20 -2 -1 3 7 8 93 186 babied barked commercials gently 
  2337   2420       iterate lad pragmatist reemphasizes rejoicing solemnness
  2338   2421     }
  2339   2422     2   "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" {
  2340   2423       1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 
  2341   2424       babied charitableness barked interrupted commercials bathrobe gently
  2342   2425       failings iterate sexton lad relenting pragmatist guarded reemphasizes reply
  2343   2426       rejoicing liabilities solemnness annexed
  2344   2427     }
  2345         -} {
  2346         -  do_execsql_test e_select-8.7.2.$tn $select [list {*}$res]
  2347         -}
         2428  +} 
  2348   2429   
  2349   2430   do_execsql_test e_select-8.8.0 {
  2350   2431     CREATE TABLE d3(a);
  2351   2432     INSERT INTO d3 VALUES('text');
  2352   2433     INSERT INTO d3 VALUES(14.1);
  2353   2434     INSERT INTO d3 VALUES(13);
  2354   2435     INSERT INTO d3 VALUES(X'78787878');
................................................................................
  2450   2531     CREATE TABLE d9(y COLLATE nocase);
  2451   2532   
  2452   2533     INSERT INTO d8 VALUES('a');
  2453   2534     INSERT INTO d9 VALUES('B');
  2454   2535     INSERT INTO d8 VALUES('c');
  2455   2536     INSERT INTO d9 VALUES('D');
  2456   2537   } {}
  2457         -foreach {tn select res} {
         2538  +do_select_tests e_select-8.13 {
  2458   2539     1   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
  2459   2540            ORDER BY a
  2460   2541         } {1 2 3 4 5 6}
  2461   2542     2   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
  2462   2543            ORDER BY c
  2463   2544         } {1 2 3 4 5 6}
  2464   2545     3   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
................................................................................
  2483   2564     10  { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } 
  2484   2565         {f 2   c 5   4 c   1 f}
  2485   2566   
  2486   2567     11  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 
  2487   2568         {2 f   5 c   c 5   f 2}
  2488   2569     12  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } 
  2489   2570         {2 f   5 c   c 5   f 2}
  2490         -
  2491         -} {
  2492         -  do_execsql_test e_select-8.13.$tn $select [list {*}$res]
  2493         -}
         2571  +} 
  2494   2572   
  2495   2573   # EVIDENCE-OF: R-39265-04070 If no matching expression can be found in
  2496   2574   # the result columns of any constituent SELECT, it is an error.
  2497   2575   #
  2498         -foreach {tn select idx} {
         2576  +do_select_tests e_select-8.14 -error {
         2577  +  %s ORDER BY term does not match any column in the result set
         2578  +} {
  2499   2579     1   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 }          1st
  2500   2580     2   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 }       2nd
  2501   2581     3   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' }  1st
  2502   2582     4   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah    }  1st
  2503   2583     5   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d }  3rd
  2504   2584     6   { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b  }  4th
  2505         -} {
  2506         -  set err "$idx ORDER BY term does not match any column in the result set"
  2507         -  do_catchsql_test e_select-8.14.$tn $select [list 1 $err]
  2508         -}
         2585  +} 
  2509   2586   
  2510   2587   # EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is
  2511   2588   # processed separately and may be matched against result columns from
  2512   2589   # different SELECT statements in the compound.
  2513   2590   # 
  2514         -foreach {tn select res} {
         2591  +do_select_tests e_select-8.15 {
  2515   2592     1  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d }
  2516   2593        {1 e   1 f   4 b   4 c}
  2517   2594     2  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b }
  2518   2595        {1 e   1 f   4 b   4 c}
  2519   2596     3  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 }
  2520   2597        {1 e   1 f   4 b   4 c}
  2521         -} {
  2522         -  do_execsql_test e_select-8.15.$tn $select [list {*}$res]
  2523         -}
         2598  +} 
  2524   2599   
  2525   2600   
  2526   2601   #-------------------------------------------------------------------------
  2527   2602   # Tests related to statements made about the LIMIT/OFFSET clause.
  2528   2603   #
  2529   2604   do_execsql_test e_select-9.0 {
  2530   2605     CREATE TABLE f1(a, b);
................................................................................
  2556   2631     INSERT INTO f1 VALUES(1, 'a');
  2557   2632   } {}
  2558   2633   
  2559   2634   # EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the
  2560   2635   # LIMIT clause, so long as it evaluates to an integer or a value that
  2561   2636   # can be losslessly converted to an integer.
  2562   2637   #
  2563         -foreach {tn select res} {
         2638  +do_select_tests e_select-9.1 {
  2564   2639     1  { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e}
  2565   2640     2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e}
  2566   2641     3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } 
  2567   2642        {a b c d e}
  2568   2643     4  { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e}
  2569   2644     5  { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e}
  2570         -} {
  2571         -  do_execsql_test e_select-9.1.$tn $select [list {*}$res]
  2572   2645   }
  2573   2646   
  2574   2647   # EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value
  2575   2648   # or any other value that cannot be losslessly converted to an integer,
  2576   2649   # an error is returned.
  2577   2650   #
  2578         -foreach {tn select} {
  2579         -  1  { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } 
  2580         -  2  { SELECT b FROM f1 ORDER BY a LIMIT NULL } 
  2581         -  3  { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } 
  2582         -  4  { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } 
  2583         -  5  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } 
  2584         -} {
  2585         -  do_catchsql_test e_select-9.2.$tn $select {1 {datatype mismatch}}
  2586         -}
         2651  +
         2652  +do_select_tests e_select-9.2 -error "datatype mismatch" {
         2653  +  1  { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } {}
         2654  +  2  { SELECT b FROM f1 ORDER BY a LIMIT NULL } {}
         2655  +  3  { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } {}
         2656  +  4  { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } {}
         2657  +  5  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } {}
         2658  +} 
  2587   2659   
  2588   2660   # EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a
  2589   2661   # negative value, then there is no upper bound on the number of rows
  2590   2662   # returned.
  2591   2663   #
  2592         -foreach {tn select res} {
         2664  +do_select_tests e_select-9.4 {
  2593   2665     1  { SELECT b FROM f1 ORDER BY a LIMIT -1 } 
  2594   2666        {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}
  2595   2667     2  { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } 
  2596   2668        {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}
  2597   2669     3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 }
  2598   2670        {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}
  2599         -} {
  2600         -  do_execsql_test e_select-9.4.$tn $select [list {*}$res]
  2601   2671   }
  2602   2672   
  2603   2673   # EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N
  2604   2674   # rows of its result set only, where N is the value that the LIMIT
  2605   2675   # expression evaluates to.
  2606   2676   #
  2607         -foreach {tn select res} {
         2677  +do_select_tests e_select-9.5 {
  2608   2678     1  { SELECT b FROM f1 ORDER BY a LIMIT 0 } {}
  2609   2679     2  { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w}
  2610   2680     3  { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s}
  2611   2681     4  { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o}
  2612         -} {
  2613         -  do_execsql_test e_select-9.5.$tn $select [list {*}$res]
  2614   2682   }
  2615   2683   
  2616   2684   # EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return
  2617   2685   # less than N rows without a LIMIT clause, then the entire result set is
  2618   2686   # returned.
  2619   2687   #
  2620         -foreach {tn select res} {
         2688  +do_select_tests e_select-9.6 {
  2621   2689     1  { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z}
  2622   2690     2  { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5}
  2623         -} {
  2624         -  do_execsql_test e_select-9.6.$tn $select [list {*}$res]
  2625         -}
         2691  +} 
  2626   2692   
  2627   2693   
  2628   2694   # EVIDENCE-OF: R-24188-24349 The expression attached to the optional
  2629   2695   # OFFSET clause that may follow a LIMIT clause must also evaluate to an
  2630   2696   # integer, or a value that can be losslessly converted to an integer.
  2631   2697   #
  2632   2698   foreach {tn select} {
................................................................................
  2643   2709   
  2644   2710   # EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then
  2645   2711   # the first M rows are omitted from the result set returned by the
  2646   2712   # SELECT statement and the next N rows are returned, where M and N are
  2647   2713   # the values that the OFFSET and LIMIT clauses evaluate to,
  2648   2714   # respectively.
  2649   2715   #
  2650         -foreach {tn select res} {
         2716  +do_select_tests e_select-9.8 {
  2651   2717     1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o}
  2652   2718     2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o}
  2653   2719     3  { SELECT b FROM f1 ORDER BY a 
  2654   2720          LIMIT  (SELECT a FROM f1 WHERE b='j') 
  2655   2721          OFFSET (SELECT a FROM f1 WHERE b='b') 
  2656   2722        } {c d e f g h i j k l}
  2657   2723     4  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h}
  2658   2724     5  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e}
  2659   2725     6  { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {}
  2660   2726     7  { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r}
  2661         -} {
  2662         -  do_execsql_test e_select-9.8.$tn $select [list {*}$res]
  2663   2727   }
  2664   2728   
  2665   2729   # EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than
  2666   2730   # M+N rows if it did not have a LIMIT clause, then the first M rows are
  2667   2731   # skipped and the remaining rows (if any) are returned.
  2668   2732   #
  2669         -foreach {tn select res} {
         2733  +do_select_tests e_select-9.9 {
  2670   2734     1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z}
  2671   2735     2  { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1}
  2672         -} {
  2673         -  do_execsql_test e_select-9.9.$tn $select [list {*}$res]
  2674   2736   }
  2675   2737   
  2676   2738   
  2677   2739   # EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a
  2678   2740   # negative value, the results are the same as if it had evaluated to
  2679   2741   # zero.
  2680   2742   #
  2681         -foreach {tn select res} {
         2743  +do_select_tests e_select-9.10 {
  2682   2744     1  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e}
  2683   2745     2  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e}
  2684   2746     3  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0  } {a b c d e}
  2685         -} {
  2686         -  do_execsql_test e_select-9.10.$tn $select [list {*}$res]
  2687         -}
         2747  +} 
  2688   2748   
  2689   2749   # EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the
  2690   2750   # LIMIT clause may specify two scalar expressions separated by a comma.
  2691   2751   #
  2692   2752   # EVIDENCE-OF: R-33788-46243 In this case, the first expression is used
  2693   2753   # as the OFFSET expression and the second as the LIMIT expression.
  2694   2754   #
  2695         -foreach {tn select res} {
         2755  +do_select_tests e_select-9.11 {
  2696   2756     1  { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o}
  2697   2757     2  { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o}
  2698   2758     3  { SELECT b FROM f1 ORDER BY a 
  2699   2759          LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') 
  2700   2760        } {c d e f g h i j k l}
  2701   2761     4  { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h}
  2702   2762     5  { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e}
................................................................................
  2705   2765   
  2706   2766     8  { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z}
  2707   2767     9  { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1}
  2708   2768   
  2709   2769     10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e}
  2710   2770     11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e}
  2711   2771     12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e}
  2712         -} {
  2713         -  do_execsql_test e_select-9.11.$tn $select [list {*}$res]
  2714   2772   }
  2715   2773   
  2716   2774   
  2717   2775   finish_test