/ Check-in [83ecec5d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Merge experimental branch back into trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 83ecec5d158c48e2fb93b0111ade62f039613540
User & Date: dan 2010-09-24 18:08:01
Context
2010-09-24
19:14
Modify some evidence tags to match updated gifs. check-in: f44de500 user: dan tags: trunk
18:08
Merge experimental branch back into trunk. check-in: 83ecec5d user: dan tags: trunk
18:04
Add new file e_vacuum.test. Move part of e_select.test into e_select2.test. Closed-Leaf check-in: 30801892 user: dan
08:00
Modify testable statement ids in a few test files to account for recent docsrc changes. check-in: 7893e525 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vacuum.c.

   105    105     int isMemDb;            /* True if vacuuming a :memory: database */
   106    106     int nRes;               /* Bytes of reserved space at the end of each page */
   107    107     int nDb;                /* Number of attached databases */
   108    108   
   109    109     if( !db->autoCommit ){
   110    110       sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
   111    111       return SQLITE_ERROR;
          112  +  }
          113  +  if( db->activeVdbeCnt>1 ){
          114  +    sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
          115  +    return SQLITE_ERROR;
   112    116     }
   113    117   
   114    118     /* Save the current value of the database flags so that it can be 
   115    119     ** restored before returning. Then set the writable-schema flag, and
   116    120     ** disable CHECK and foreign key constraints.  */
   117    121     saved_flags = db->flags;
   118    122     saved_nChange = db->nChange;

Changes to test/e_select.test.

   702    702     3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)}
   703    703   } {
   704    704     do_catchsql_test e_select-1.12.$tn "
   705    705       $sql
   706    706     " {1 {a NATURAL join may not have an ON or USING clause}}
   707    707   }
   708    708   
   709         -#-------------------------------------------------------------------------
   710         -# te_* commands:
   711         -#
   712         -#
   713         -#   te_read_sql DB SELECT-STATEMENT
   714         -#   te_read_tbl DB TABLENAME
   715         -#
   716         -# These two commands are used to read a dataset from the database. A dataset
   717         -# consists of N rows of M named columns of values each, where each value has a
   718         -# type (null, integer, real, text or blob) and a value within the types domain.
   719         -# The tcl format for a "dataset" is a list of two elements:
   720         -#
   721         -#   * A list of the column names.
   722         -#   * A list of data rows. Each row is itself a list, where each element is
   723         -#     the contents of a column of the row. Each of these is a list of two
   724         -#     elements, the type name and the actual value.
   725         -#
   726         -# For example, the contents of table [t1] as a dataset is:
   727         -#
   728         -#   CREATE TABLE t1(a, b);
   729         -#   INSERT INTO t1 VALUES('abc', NULL);
   730         -#   INSERT INTO t1 VALUES(43.1, 22);
   731         -#
   732         -#   {a b} {{{TEXT abc} {NULL {}}} {{REAL 43.1} {INTEGER 22}}}
   733         -#
   734         -# The [te_read_tbl] command returns a dataset read from a table. The
   735         -# [te_read_sql] returns the dataset that results from executing a SELECT
   736         -# command.
   737         -#
   738         -#
   739         -#   te_tbljoin ?SWITCHES? LHS-TABLE RHS-TABLE
   740         -#   te_join ?SWITCHES? LHS-DATASET RHS-DATASET
   741         -#
   742         -# This command joins the two datasets and returns the resulting dataset. If 
   743         -# there are no switches specified, then the results is the cartesian product
   744         -# of the two inputs.  The [te_tbljoin] command reads the left and right-hand
   745         -# datasets from the specified tables. The [te_join] command is passed the
   746         -# datasets directly.
   747         -#
   748         -# Optional switches are as follows:
   749         -#
   750         -#   -on SCRIPT
   751         -#   -using COLUMN-LIST
   752         -#   -left
   753         -#
   754         -# The -on option specifies a tcl script that is executed for each row in the
   755         -# cartesian product of the two datasets. The script has 4 arguments appended
   756         -# to it, in the following order:
   757         -#
   758         -#   * The list of column-names from the left-hand dataset.
   759         -#   * A single row from the left-hand dataset (one "data row" list as 
   760         -#     described above.
   761         -#   * The list of column-names from the right-hand dataset.
   762         -#   * A single row from the right-hand dataset.
   763         -#
   764         -# The script must return a boolean value - true if the combination of rows
   765         -# should be included in the output dataset, or false otherwise.
   766         -#
   767         -# The -using option specifies a list of the columns from the right-hand
   768         -# dataset that should be omitted from the output dataset.
   769         -#
   770         -# If the -left option is present, the join is done LEFT JOIN style. 
   771         -# Specifically, an extra row is inserted if after the -on script is run there
   772         -# exist rows in the left-hand dataset that have no corresponding rows in
   773         -# the output. See the implementation for more specific comments.
   774         -#
   775         -#
   776         -#   te_equals ?SWITCHES? COLNAME1 COLNAME2 <-on script args>
   777         -#
   778         -# The only supported switch is "-nocase". If it is present, then text values
   779         -# are compared in a case-independent fashion. Otherwise, they are compared
   780         -# as if using the SQLite BINARY collation sequence.
   781         -#
   782         -#
   783         -#   te_and ONSCRIPT1 ONSCRIPT2...
   784         -#
   785         -#
   786         -
   787         -
   788         -#
   789         -#   te_read_tbl DB TABLENAME
   790         -#   te_read_sql DB SELECT-STATEMENT
   791         -#
   792         -# These two procs are used to extract datasets from the database, either
   793         -# by reading the contents of a named table (te_read_tbl), or by executing
   794         -# a SELECT statement (t3_read_sql).  
   795         -#
   796         -# See the comment above, describing "te_* commands", for details of the
   797         -# return values.
   798         -#
   799         -proc te_read_tbl {db tbl} {
   800         - te_read_sql $db "SELECT * FROM '$tbl'"
   801         -}
   802         -proc te_read_sql {db sql} {
   803         -  set S [sqlite3_prepare_v2 $db $sql -1 DUMMY]
   804         -
   805         -  set cols [list]
   806         -  for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} {
   807         -    lappend cols [sqlite3_column_name $S $i]
   808         -  }
   809         -
   810         -  set rows [list]
   811         -  while {[sqlite3_step $S] == "SQLITE_ROW"} {
   812         -    set r [list]
   813         -    for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} {
   814         -      lappend r [list [sqlite3_column_type $S $i] [sqlite3_column_text $S $i]]
   815         -    }
   816         -    lappend rows $r
   817         -  }
   818         -  sqlite3_finalize $S
   819         -
   820         -  return [list $cols $rows]
   821         -}
   822         -
   823         -#-------
   824         -# Usage:   te_join <table-data1> <table-data2> <join spec>...
   825         -#
   826         -# Where a join-spec is an optional list of arguments as follows:
   827         -#
   828         -#   ?-left?
   829         -#   ?-using colname-list?
   830         -#   ?-on on-expr-proc?
   831         -#
   832         -proc te_join {data1 data2 args} {
   833         -
   834         -  set testproc ""
   835         -  set usinglist [list]
   836         -  set isleft 0
   837         -  for {set i 0} {$i < [llength $args]} {incr i} {
   838         -    set a [lindex $args $i]
   839         -    switch -- $a {
   840         -      -on     { set testproc [lindex $args [incr i]] }
   841         -      -using  { set usinglist [lindex $args [incr i]] }
   842         -      -left   { set isleft 1 }
   843         -      default {
   844         -        error "Unknown argument: $a"
   845         -      }
   846         -    }
   847         -  }
   848         -
   849         -  set c1 [lindex $data1 0]
   850         -  set c2 [lindex $data2 0]
   851         -  set omitlist [list]
   852         -  set nullrowlist [list]
   853         -  set cret $c1
   854         -
   855         -  set cidx 0
   856         -  foreach col $c2 {
   857         -    set idx [lsearch $usinglist $col]
   858         -    if {$idx>=0} {lappend omitlist $cidx}
   859         -    if {$idx<0} {
   860         -      lappend nullrowlist {NULL {}}
   861         -      lappend cret $col
   862         -    }
   863         -    incr cidx
   864         -  }
   865         -  set omitlist [lsort -integer -decreasing $omitlist]
   866         -
   867         -
   868         -  set rret [list]
   869         -  foreach r1 [lindex $data1 1] {
   870         -    set one 0
   871         -    foreach r2 [lindex $data2 1] {
   872         -      set ok 1
   873         -      if {$testproc != ""} {
   874         -        set ok [eval $testproc [list $c1 $r1 $c2 $r2]]
   875         -      }
   876         -      if {$ok} {
   877         -        set one 1
   878         -        foreach idx $omitlist {set r2 [lreplace $r2 $idx $idx]}
   879         -        lappend rret [concat $r1 $r2]
   880         -      }
   881         -    }
   882         -
   883         -    if {$isleft && $one==0} {
   884         -      lappend rret [concat $r1 $nullrowlist]
   885         -    }
   886         -  }
   887         -  
   888         -  list $cret $rret
   889         -}
   890         -
   891         -proc te_tbljoin {db t1 t2 args} {
   892         -  te_join [te_read_tbl $db $t1] [te_read_tbl $db $t2] {*}$args
   893         -}
   894         -
   895         -proc te_apply_affinity {affinity typevar valvar} {
   896         -  upvar $typevar type
   897         -  upvar $valvar val
   898         -
   899         -  switch -- $affinity {
   900         -    integer {
   901         -      if {[string is double $val]} { set type REAL }
   902         -      if {[string is wideinteger $val]} { set type INTEGER }
   903         -      if {$type == "REAL" && int($val)==$val} { 
   904         -        set type INTEGER 
   905         -        set val [expr {int($val)}]
   906         -      }
   907         -    }
   908         -    text {
   909         -      set type TEXT
   910         -    }
   911         -    none { }
   912         -
   913         -    default { error "invalid affinity: $affinity" }
   914         -  }
   915         -}
   916         -
   917         -#----------
   918         -# te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2
   919         -#
   920         -proc te_equals {args} {
   921         -
   922         -  if {[llength $args]<6} {error "invalid arguments to te_equals"}
   923         -  foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break
   924         -
   925         -  set nocase 0
   926         -  set affinity none
   927         -
   928         -  for {set i 0} {$i < ([llength $args]-6)} {incr i} {
   929         -    set a [lindex $args $i]
   930         -    switch -- $a {
   931         -      -nocase {
   932         -        set nocase 1
   933         -      }
   934         -      -affinity {
   935         -        set affinity [string tolower [lindex $args [incr i]]]
   936         -      }
   937         -      default {
   938         -        error "invalid arguments to te_equals"
   939         -      }
   940         -    }
   941         -  }
   942         -
   943         -  set idx2 [if {[string is integer $c2]} { set c2 } else { lsearch $cols2 $c2 }]
   944         -  set idx1 [if {[string is integer $c1]} { set c1 } else { lsearch $cols1 $c1 }]
   945         -
   946         -  set t1 [lindex $row1 $idx1 0]
   947         -  set t2 [lindex $row2 $idx2 0]
   948         -  set v1 [lindex $row1 $idx1 1]
   949         -  set v2 [lindex $row2 $idx2 1]
   950         -
   951         -  te_apply_affinity $affinity t1 v1
   952         -  te_apply_affinity $affinity t2 v2
   953         -
   954         -  if {$t1 == "NULL" || $t2 == "NULL"} { return 0 }
   955         -  if {$nocase && $t1 == "TEXT"} { set v1 [string tolower $v1] }
   956         -  if {$nocase && $t2 == "TEXT"} { set v2 [string tolower $v2] }
   957         -
   958         -
   959         -  set res [expr {$t1 == $t2 && [string equal $v1 $v2]}]
   960         -  return $res
   961         -}
   962         -
   963         -proc te_false {args} { return 0 }
   964         -proc te_true  {args} { return 1 }
   965         -
   966         -proc te_and {args} {
   967         -  foreach a [lrange $args 0 end-4] {
   968         -    set res [eval $a [lrange $args end-3 end]]
   969         -    if {$res == 0} {return 0}
   970         -  }
   971         -  return 1
   972         -}
   973         -
   974         -
   975         -proc te_dataset_eq {testname got expected} {
   976         -  uplevel #0 [list do_test $testname [list set {} $got] $expected]
   977         -}
   978         -proc te_dataset_eq_unordered {testname got expected} {
   979         -  lset got      1 [lsort [lindex $got 1]]
   980         -  lset expected 1 [lsort [lindex $expected 1]]
   981         -  te_dataset_eq $testname $got $expected
   982         -}
   983         -
   984         -proc te_dataset_ne {testname got unexpected} {
   985         -  uplevel #0 [list do_test $testname [list string equal $got $unexpected] 0]
   986         -}
   987         -proc te_dataset_ne_unordered {testname got unexpected} {
   988         -  lset got      1 [lsort [lindex $got 1]]
   989         -  lset unexpected 1 [lsort [lindex $unexpected 1]]
   990         -  te_dataset_ne $testname $got $unexpected
   991         -}
   992         -
   993         -
   994         -#-------------------------------------------------------------------------
   995         -#
   996         -proc test_join {tn sqljoin tbljoinargs} {
   997         -  set sql [te_read_sql db "SELECT * FROM $sqljoin"]
   998         -  set te  [te_tbljoin db {*}$tbljoinargs]
   999         -  te_dataset_eq_unordered $tn $sql $te
  1000         -}
  1001         -
  1002         -drop_all_tables
  1003         -do_execsql_test e_select-2.0 {
  1004         -  CREATE TABLE t1(a, b);
  1005         -  CREATE TABLE t2(a, b);
  1006         -  CREATE TABLE t3(b COLLATE nocase);
  1007         -
  1008         -  INSERT INTO t1 VALUES(2, 'B');
  1009         -  INSERT INTO t1 VALUES(1, 'A');
  1010         -  INSERT INTO t1 VALUES(4, 'D');
  1011         -  INSERT INTO t1 VALUES(NULL, NULL);
  1012         -  INSERT INTO t1 VALUES(3, NULL);
  1013         -
  1014         -  INSERT INTO t2 VALUES(1, 'A');
  1015         -  INSERT INTO t2 VALUES(2, NULL);
  1016         -  INSERT INTO t2 VALUES(5, 'E');
  1017         -  INSERT INTO t2 VALUES(NULL, NULL);
  1018         -  INSERT INTO t2 VALUES(3, 'C');
  1019         -
  1020         -  INSERT INTO t3 VALUES('a');
  1021         -  INSERT INTO t3 VALUES('c');
  1022         -  INSERT INTO t3 VALUES('b');
  1023         -} {}
  1024         -
  1025         -foreach {tn indexes} {
  1026         -  e_select-2.1.1 { }
  1027         -  e_select-2.1.2 { CREATE INDEX i1 ON t1(a) }
  1028         -  e_select-2.1.3 { CREATE INDEX i1 ON t2(a) }
  1029         -  e_select-2.1.4 { CREATE INDEX i1 ON t3(b) }
  1030         -} {
  1031         -
  1032         -  catchsql { DROP INDEX i1 }
  1033         -  catchsql { DROP INDEX i2 }
  1034         -  catchsql { DROP INDEX i3 }
  1035         -  execsql $indexes
  1036         -
  1037         -  # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
  1038         -  # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
  1039         -  # then the result of the join is simply the cartesian product of the
  1040         -  # left and right-hand datasets.
  1041         -  #
  1042         -  # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
  1043         -  # JOIN", "JOIN" and "," join operators.
  1044         -  #
  1045         -  # EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the
  1046         -  # same data as the "INNER JOIN", "JOIN" and "," operators
  1047         -  #
  1048         -  test_join $tn.1.1  "t1, t2"                {t1 t2}
  1049         -  test_join $tn.1.2  "t1 INNER JOIN t2"      {t1 t2}
  1050         -  test_join $tn.1.3  "t1 CROSS JOIN t2"      {t1 t2}
  1051         -  test_join $tn.1.4  "t1 JOIN t2"            {t1 t2}
  1052         -  test_join $tn.1.5  "t2, t3"                {t2 t3}
  1053         -  test_join $tn.1.6  "t2 INNER JOIN t3"      {t2 t3}
  1054         -  test_join $tn.1.7  "t2 CROSS JOIN t3"      {t2 t3}
  1055         -  test_join $tn.1.8  "t2 JOIN t3"            {t2 t3}
  1056         -  test_join $tn.1.9  "t2, t2 AS x"           {t2 t2}
  1057         -  test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
  1058         -  test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
  1059         -  test_join $tn.1.12 "t2 JOIN t2 AS x"       {t2 t2}
  1060         -
  1061         -  # EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
  1062         -  # the ON expression is evaluated for each row of the cartesian product
  1063         -  # as a boolean expression. All rows for which the expression evaluates
  1064         -  # to false are excluded from the dataset.
  1065         -  #
  1066         -  test_join $tn.2.1  "t1, t2 ON (t1.a=t2.a)"  {t1 t2 -on {te_equals a a}}
  1067         -  test_join $tn.2.2  "t2, t1 ON (t1.a=t2.a)"  {t2 t1 -on {te_equals a a}}
  1068         -  test_join $tn.2.3  "t2, t1 ON (1)"          {t2 t1 -on te_true}
  1069         -  test_join $tn.2.4  "t2, t1 ON (NULL)"       {t2 t1 -on te_false}
  1070         -  test_join $tn.2.5  "t2, t1 ON (1.1-1.1)"    {t2 t1 -on te_false}
  1071         -  test_join $tn.2.6  "t1, t2 ON (1.1-1.0)"    {t1 t2 -on te_true}
  1072         -
  1073         -
  1074         -  test_join $tn.3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}}
  1075         -  test_join $tn.4 "t1 LEFT JOIN t2 USING (a)" {
  1076         -    t1 t2 -left -using a -on {te_equals a a}
  1077         -  }
  1078         -  test_join $tn.5 "t1 CROSS JOIN t2 USING(b, a)" {
  1079         -    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1080         -  }
  1081         -  test_join $tn.6 "t1 NATURAL JOIN t2" {
  1082         -    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1083         -  }
  1084         -  test_join $tn.7 "t1 NATURAL INNER JOIN t2" {
  1085         -    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1086         -  }
  1087         -  test_join $tn.8 "t1 NATURAL CROSS JOIN t2" {
  1088         -    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1089         -  }
  1090         -  test_join $tn.9 "t1 NATURAL INNER JOIN t2" {
  1091         -    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1092         -  }
  1093         -  test_join $tn.10 "t1 NATURAL LEFT JOIN t2" {
  1094         -    t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1095         -  }
  1096         -  test_join $tn.11 "t1 NATURAL LEFT OUTER JOIN t2" {
  1097         -    t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1098         -  }
  1099         -  test_join $tn.12 "t2 NATURAL JOIN t1" {
  1100         -    t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1101         -  }
  1102         -  test_join $tn.13 "t2 NATURAL INNER JOIN t1" {
  1103         -    t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1104         -  }
  1105         -  test_join $tn.14 "t2 NATURAL CROSS JOIN t1" {
  1106         -    t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1107         -  }
  1108         -  test_join $tn.15 "t2 NATURAL INNER JOIN t1" {
  1109         -    t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1110         -  }
  1111         -  test_join $tn.16 "t2 NATURAL LEFT JOIN t1" {
  1112         -    t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1113         -  }
  1114         -  test_join $tn.17 "t2 NATURAL LEFT OUTER JOIN t1" {
  1115         -    t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
  1116         -  }
  1117         -  test_join $tn.18 "t1 LEFT JOIN t2 USING (b)" {
  1118         -    t1 t2 -left -using b -on {te_equals b b}
  1119         -  }
  1120         -  test_join $tn.19 "t1 JOIN t3 USING(b)" {t1 t3 -using b -on {te_equals b b}}
  1121         -  test_join $tn.20 "t3 JOIN t1 USING(b)" {
  1122         -    t3 t1 -using b -on {te_equals -nocase b b}
  1123         -  }
  1124         -  test_join $tn.21 "t1 NATURAL JOIN t3"  {
  1125         -    t1 t3 -using b -on {te_equals b b}
  1126         -  }
  1127         -  test_join $tn.22 "t3 NATURAL JOIN t1"  {
  1128         -    t3 t1 -using b -on {te_equals -nocase b b}
  1129         -  }
  1130         -  test_join $tn.23 "t1 NATURAL LEFT JOIN t3" {
  1131         -    t1 t3 -left -using b -on {te_equals b b}
  1132         -  }
  1133         -  test_join $tn.24 "t3 NATURAL LEFT JOIN t1" {
  1134         -    t3 t1 -left -using b -on {te_equals -nocase b b}
  1135         -  }
  1136         -  test_join $tn.25 "t1 LEFT JOIN t3 ON (t3.b=t1.b)" {
  1137         -    t1 t3 -left -on {te_equals -nocase b b}
  1138         -  }
  1139         -  test_join $tn.26 "t1 LEFT JOIN t3 ON (t1.b=t3.b)" {
  1140         -    t1 t3 -left -on {te_equals b b}
  1141         -  }
  1142         -  test_join $tn.27 "t1 JOIN t3 ON (t1.b=t3.b)" { t1 t3 -on {te_equals b b} }
  1143         -
  1144         -  # EVIDENCE-OF: R-28760-53843 When more than two tables are joined
  1145         -  # together as part of a FROM clause, the join operations are processed
  1146         -  # in order from left to right. In other words, the FROM clause (A
  1147         -  # join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C).
  1148         -  #
  1149         -  #   Tests 28a and 28b show that the statement above is true for this case.
  1150         -  #   Test 28c shows that if the parenthesis force a different order of
  1151         -  #   evaluation the result is different. Test 28d verifies that the result
  1152         -  #   of the query with the parenthesis forcing a different order of evaluation
  1153         -  #   is as calculated by the [te_*] procs.
  1154         -  #
  1155         -  set t3_natural_left_join_t2 [
  1156         -    te_tbljoin db t3 t2 -left -using {b} -on {te_equals -nocase b b}
  1157         -  ]
  1158         -  set t1 [te_read_tbl db t1]
  1159         -  te_dataset_eq_unordered $tn.28a [
  1160         -    te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1"
  1161         -  ] [te_join $t3_natural_left_join_t2 $t1                                \
  1162         -      -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}}  \
  1163         -  ]
  1164         -
  1165         -  te_dataset_eq_unordered $tn.28b [
  1166         -    te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1"
  1167         -  ] [te_join $t3_natural_left_join_t2 $t1                                \
  1168         -      -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}}  \
  1169         -  ]
  1170         -
  1171         -  te_dataset_ne_unordered $tn.28c [
  1172         -    te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1"
  1173         -  ] [
  1174         -    te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)"
  1175         -  ]
  1176         -
  1177         -  set t2_natural_join_t1 [te_tbljoin db t2 t1 -using {a b}                 \
  1178         -        -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}}  \
  1179         -  ]
  1180         -  set t3 [te_read_tbl db t3]
  1181         -  te_dataset_eq_unordered $tn.28d [
  1182         -    te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)"
  1183         -  ] [te_join $t3 $t2_natural_join_t1                                       \
  1184         -      -left -using {b} -on {te_equals -nocase b b}                         \
  1185         -  ]
  1186         -}
  1187         -
  1188         -do_execsql_test e_select-2.2.0 {
  1189         -  CREATE TABLE t4(x TEXT COLLATE nocase);
  1190         -  CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary);
  1191         -
  1192         -  INSERT INTO t4 VALUES('2.0');
  1193         -  INSERT INTO t4 VALUES('TWO');
  1194         -  INSERT INTO t5 VALUES(2, 'two');
  1195         -} {}
  1196         -
  1197         -# EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source
  1198         -# following the FROM clause in a simple SELECT statement is handled as
  1199         -# if it was a table containing the data returned by executing the
  1200         -# sub-select statement.
  1201         -#
  1202         -# EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset
  1203         -# inherits the collation sequence and affinity of the corresponding
  1204         -# expression in the sub-select statement.
  1205         -#
  1206         -foreach {tn subselect select spec} {
  1207         -  1   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss%" 
  1208         -      {t1 %ss%}
  1209         -
  1210         -  2   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" 
  1211         -      {t1 %ss% -on {te_equals 0 0}}
  1212         -
  1213         -  3   "SELECT * FROM t2"   "SELECT * FROM %ss% AS x JOIN t1 ON (t1.a=x.a)" 
  1214         -      {%ss% t1 -on {te_equals 0 0}}
  1215         -
  1216         -  4   "SELECT * FROM t1, t2" "SELECT * FROM %ss% AS x JOIN t3"
  1217         -      {%ss% t3}
  1218         -
  1219         -  5   "SELECT * FROM t1, t2" "SELECT * FROM %ss% NATURAL JOIN t3"
  1220         -      {%ss% t3 -using b -on {te_equals 1 0}}
  1221         -
  1222         -  6   "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL JOIN %ss%"
  1223         -      {t3 %ss% -using b -on {te_equals -nocase 0 1}}
  1224         -
  1225         -  7   "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL LEFT JOIN %ss%"
  1226         -      {t3 %ss% -left -using b -on {te_equals -nocase 0 1}}
  1227         -
  1228         -  8   "SELECT count(*) AS y FROM t4"   "SELECT * FROM t5, %ss% USING (y)"
  1229         -      {t5 %ss% -using y -on {te_equals -affinity text 0 0}}
  1230         -
  1231         -  9   "SELECT count(*) AS y FROM t4"   "SELECT * FROM %ss%, t5 USING (y)"
  1232         -      {%ss% t5 -using y -on {te_equals -affinity text 0 0}}
  1233         -
  1234         -  10  "SELECT x AS y FROM t4"   "SELECT * FROM %ss% JOIN t5 USING (y)"
  1235         -      {%ss% t5 -using y -on {te_equals -nocase -affinity integer 0 0}}
  1236         -
  1237         -  11  "SELECT x AS y FROM t4"   "SELECT * FROM t5 JOIN %ss% USING (y)"
  1238         -      {t5 %ss% -using y -on {te_equals -nocase -affinity integer 0 0}}
  1239         -
  1240         -  12  "SELECT y AS x FROM t5"   "SELECT * FROM %ss% JOIN t4 USING (x)"
  1241         -      {%ss% t4 -using x -on {te_equals -nocase -affinity integer 0 0}}
  1242         -
  1243         -  13  "SELECT y AS x FROM t5"   "SELECT * FROM t4 JOIN %ss% USING (x)"
  1244         -      {t4 %ss% -using x -on {te_equals -nocase -affinity integer 0 0}}
  1245         -
  1246         -  14  "SELECT +y AS x FROM t5"   "SELECT * FROM %ss% JOIN t4 USING (x)"
  1247         -      {%ss% t4 -using x -on {te_equals -nocase -affinity text 0 0}}
  1248         -
  1249         -  15  "SELECT +y AS x FROM t5"   "SELECT * FROM t4 JOIN %ss% USING (x)"
  1250         -      {t4 %ss% -using x -on {te_equals -nocase -affinity text 0 0}}
  1251         -} {
  1252         -
  1253         -  # Create a temporary table named %ss% containing the data returned by
  1254         -  # the sub-select. Then have the [te_tbljoin] proc use this table to
  1255         -  # compute the expected results of the $select query. Drop the temporary
  1256         -  # table before continuing.
  1257         -  #
  1258         -  execsql "CREATE TEMP TABLE '%ss%' AS $subselect"
  1259         -  set te [eval te_tbljoin db $spec]
  1260         -  execsql "DROP TABLE '%ss%'"
  1261         -
  1262         -  # Check that the actual data returned by the $select query is the same
  1263         -  # as the expected data calculated using [te_tbljoin] above.
  1264         -  #
  1265         -  te_dataset_eq_unordered e_select-2.2.1.$tn [
  1266         -    te_read_sql db [string map [list %ss% "($subselect)"] $select]
  1267         -  ] $te
  1268         -}
  1269         -
  1270    709   #-------------------------------------------------------------------------
  1271    710   # The next block of tests - e_select-3.* - concentrate on verifying 
  1272    711   # statements made regarding WHERE clause processing.
  1273    712   #
  1274    713   drop_all_tables
  1275    714   do_execsql_test e_select-3.0 {
  1276    715     CREATE TABLE x1(k, x, y, z);
................................................................................
  2713   2152     8  { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z}
  2714   2153     9  { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1}
  2715   2154   
  2716   2155     10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e}
  2717   2156     11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e}
  2718   2157     12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e}
  2719   2158   }
  2720         -
  2721   2159   
  2722   2160   finish_test

Added test/e_select2.test.

            1  +# 2010 September 24
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file implements tests to verify that the "testable statements" in 
           13  +# the lang_select.html document are correct.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +#-------------------------------------------------------------------------
           20  +# te_* commands:
           21  +#
           22  +#
           23  +#   te_read_sql DB SELECT-STATEMENT
           24  +#   te_read_tbl DB TABLENAME
           25  +#
           26  +# These two commands are used to read a dataset from the database. A dataset
           27  +# consists of N rows of M named columns of values each, where each value has a
           28  +# type (null, integer, real, text or blob) and a value within the types domain.
           29  +# The tcl format for a "dataset" is a list of two elements:
           30  +#
           31  +#   * A list of the column names.
           32  +#   * A list of data rows. Each row is itself a list, where each element is
           33  +#     the contents of a column of the row. Each of these is a list of two
           34  +#     elements, the type name and the actual value.
           35  +#
           36  +# For example, the contents of table [t1] as a dataset is:
           37  +#
           38  +#   CREATE TABLE t1(a, b);
           39  +#   INSERT INTO t1 VALUES('abc', NULL);
           40  +#   INSERT INTO t1 VALUES(43.1, 22);
           41  +#
           42  +#   {a b} {{{TEXT abc} {NULL {}}} {{REAL 43.1} {INTEGER 22}}}
           43  +#
           44  +# The [te_read_tbl] command returns a dataset read from a table. The
           45  +# [te_read_sql] returns the dataset that results from executing a SELECT
           46  +# command.
           47  +#
           48  +#
           49  +#   te_tbljoin ?SWITCHES? LHS-TABLE RHS-TABLE
           50  +#   te_join ?SWITCHES? LHS-DATASET RHS-DATASET
           51  +#
           52  +# This command joins the two datasets and returns the resulting dataset. If 
           53  +# there are no switches specified, then the results is the cartesian product
           54  +# of the two inputs.  The [te_tbljoin] command reads the left and right-hand
           55  +# datasets from the specified tables. The [te_join] command is passed the
           56  +# datasets directly.
           57  +#
           58  +# Optional switches are as follows:
           59  +#
           60  +#   -on SCRIPT
           61  +#   -using COLUMN-LIST
           62  +#   -left
           63  +#
           64  +# The -on option specifies a tcl script that is executed for each row in the
           65  +# cartesian product of the two datasets. The script has 4 arguments appended
           66  +# to it, in the following order:
           67  +#
           68  +#   * The list of column-names from the left-hand dataset.
           69  +#   * A single row from the left-hand dataset (one "data row" list as 
           70  +#     described above.
           71  +#   * The list of column-names from the right-hand dataset.
           72  +#   * A single row from the right-hand dataset.
           73  +#
           74  +# The script must return a boolean value - true if the combination of rows
           75  +# should be included in the output dataset, or false otherwise.
           76  +#
           77  +# The -using option specifies a list of the columns from the right-hand
           78  +# dataset that should be omitted from the output dataset.
           79  +#
           80  +# If the -left option is present, the join is done LEFT JOIN style. 
           81  +# Specifically, an extra row is inserted if after the -on script is run there
           82  +# exist rows in the left-hand dataset that have no corresponding rows in
           83  +# the output. See the implementation for more specific comments.
           84  +#
           85  +#
           86  +#   te_equals ?SWITCHES? COLNAME1 COLNAME2 <-on script args>
           87  +#
           88  +# The only supported switch is "-nocase". If it is present, then text values
           89  +# are compared in a case-independent fashion. Otherwise, they are compared
           90  +# as if using the SQLite BINARY collation sequence.
           91  +#
           92  +#
           93  +#   te_and ONSCRIPT1 ONSCRIPT2...
           94  +#
           95  +#
           96  +
           97  +
           98  +#
           99  +#   te_read_tbl DB TABLENAME
          100  +#   te_read_sql DB SELECT-STATEMENT
          101  +#
          102  +# These two procs are used to extract datasets from the database, either
          103  +# by reading the contents of a named table (te_read_tbl), or by executing
          104  +# a SELECT statement (t3_read_sql).  
          105  +#
          106  +# See the comment above, describing "te_* commands", for details of the
          107  +# return values.
          108  +#
          109  +proc te_read_tbl {db tbl} {
          110  + te_read_sql $db "SELECT * FROM '$tbl'"
          111  +}
          112  +proc te_read_sql {db sql} {
          113  +  set S [sqlite3_prepare_v2 $db $sql -1 DUMMY]
          114  +
          115  +  set cols [list]
          116  +  for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} {
          117  +    lappend cols [sqlite3_column_name $S $i]
          118  +  }
          119  +
          120  +  set rows [list]
          121  +  while {[sqlite3_step $S] == "SQLITE_ROW"} {
          122  +    set r [list]
          123  +    for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} {
          124  +      lappend r [list [sqlite3_column_type $S $i] [sqlite3_column_text $S $i]]
          125  +    }
          126  +    lappend rows $r
          127  +  }
          128  +  sqlite3_finalize $S
          129  +
          130  +  return [list $cols $rows]
          131  +}
          132  +
          133  +#-------
          134  +# Usage:   te_join <table-data1> <table-data2> <join spec>...
          135  +#
          136  +# Where a join-spec is an optional list of arguments as follows:
          137  +#
          138  +#   ?-left?
          139  +#   ?-using colname-list?
          140  +#   ?-on on-expr-proc?
          141  +#
          142  +proc te_join {data1 data2 args} {
          143  +
          144  +  set testproc ""
          145  +  set usinglist [list]
          146  +  set isleft 0
          147  +  for {set i 0} {$i < [llength $args]} {incr i} {
          148  +    set a [lindex $args $i]
          149  +    switch -- $a {
          150  +      -on     { set testproc [lindex $args [incr i]] }
          151  +      -using  { set usinglist [lindex $args [incr i]] }
          152  +      -left   { set isleft 1 }
          153  +      default {
          154  +        error "Unknown argument: $a"
          155  +      }
          156  +    }
          157  +  }
          158  +
          159  +  set c1 [lindex $data1 0]
          160  +  set c2 [lindex $data2 0]
          161  +  set omitlist [list]
          162  +  set nullrowlist [list]
          163  +  set cret $c1
          164  +
          165  +  set cidx 0
          166  +  foreach col $c2 {
          167  +    set idx [lsearch $usinglist $col]
          168  +    if {$idx>=0} {lappend omitlist $cidx}
          169  +    if {$idx<0} {
          170  +      lappend nullrowlist {NULL {}}
          171  +      lappend cret $col
          172  +    }
          173  +    incr cidx
          174  +  }
          175  +  set omitlist [lsort -integer -decreasing $omitlist]
          176  +
          177  +
          178  +  set rret [list]
          179  +  foreach r1 [lindex $data1 1] {
          180  +    set one 0
          181  +    foreach r2 [lindex $data2 1] {
          182  +      set ok 1
          183  +      if {$testproc != ""} {
          184  +        set ok [eval $testproc [list $c1 $r1 $c2 $r2]]
          185  +      }
          186  +      if {$ok} {
          187  +        set one 1
          188  +        foreach idx $omitlist {set r2 [lreplace $r2 $idx $idx]}
          189  +        lappend rret [concat $r1 $r2]
          190  +      }
          191  +    }
          192  +
          193  +    if {$isleft && $one==0} {
          194  +      lappend rret [concat $r1 $nullrowlist]
          195  +    }
          196  +  }
          197  +  
          198  +  list $cret $rret
          199  +}
          200  +
          201  +proc te_tbljoin {db t1 t2 args} {
          202  +  te_join [te_read_tbl $db $t1] [te_read_tbl $db $t2] {*}$args
          203  +}
          204  +
          205  +proc te_apply_affinity {affinity typevar valvar} {
          206  +  upvar $typevar type
          207  +  upvar $valvar val
          208  +
          209  +  switch -- $affinity {
          210  +    integer {
          211  +      if {[string is double $val]} { set type REAL }
          212  +      if {[string is wideinteger $val]} { set type INTEGER }
          213  +      if {$type == "REAL" && int($val)==$val} { 
          214  +        set type INTEGER 
          215  +        set val [expr {int($val)}]
          216  +      }
          217  +    }
          218  +    text {
          219  +      set type TEXT
          220  +    }
          221  +    none { }
          222  +
          223  +    default { error "invalid affinity: $affinity" }
          224  +  }
          225  +}
          226  +
          227  +#----------
          228  +# te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2
          229  +#
          230  +proc te_equals {args} {
          231  +
          232  +  if {[llength $args]<6} {error "invalid arguments to te_equals"}
          233  +  foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break
          234  +
          235  +  set nocase 0
          236  +  set affinity none
          237  +
          238  +  for {set i 0} {$i < ([llength $args]-6)} {incr i} {
          239  +    set a [lindex $args $i]
          240  +    switch -- $a {
          241  +      -nocase {
          242  +        set nocase 1
          243  +      }
          244  +      -affinity {
          245  +        set affinity [string tolower [lindex $args [incr i]]]
          246  +      }
          247  +      default {
          248  +        error "invalid arguments to te_equals"
          249  +      }
          250  +    }
          251  +  }
          252  +
          253  +  set idx2 [if {[string is integer $c2]} { set c2 } else { lsearch $cols2 $c2 }]
          254  +  set idx1 [if {[string is integer $c1]} { set c1 } else { lsearch $cols1 $c1 }]
          255  +
          256  +  set t1 [lindex $row1 $idx1 0]
          257  +  set t2 [lindex $row2 $idx2 0]
          258  +  set v1 [lindex $row1 $idx1 1]
          259  +  set v2 [lindex $row2 $idx2 1]
          260  +
          261  +  te_apply_affinity $affinity t1 v1
          262  +  te_apply_affinity $affinity t2 v2
          263  +
          264  +  if {$t1 == "NULL" || $t2 == "NULL"} { return 0 }
          265  +  if {$nocase && $t1 == "TEXT"} { set v1 [string tolower $v1] }
          266  +  if {$nocase && $t2 == "TEXT"} { set v2 [string tolower $v2] }
          267  +
          268  +
          269  +  set res [expr {$t1 == $t2 && [string equal $v1 $v2]}]
          270  +  return $res
          271  +}
          272  +
          273  +proc te_false {args} { return 0 }
          274  +proc te_true  {args} { return 1 }
          275  +
          276  +proc te_and {args} {
          277  +  foreach a [lrange $args 0 end-4] {
          278  +    set res [eval $a [lrange $args end-3 end]]
          279  +    if {$res == 0} {return 0}
          280  +  }
          281  +  return 1
          282  +}
          283  +
          284  +
          285  +proc te_dataset_eq {testname got expected} {
          286  +  uplevel #0 [list do_test $testname [list set {} $got] $expected]
          287  +}
          288  +proc te_dataset_eq_unordered {testname got expected} {
          289  +  lset got      1 [lsort [lindex $got 1]]
          290  +  lset expected 1 [lsort [lindex $expected 1]]
          291  +  te_dataset_eq $testname $got $expected
          292  +}
          293  +
          294  +proc te_dataset_ne {testname got unexpected} {
          295  +  uplevel #0 [list do_test $testname [list string equal $got $unexpected] 0]
          296  +}
          297  +proc te_dataset_ne_unordered {testname got unexpected} {
          298  +  lset got      1 [lsort [lindex $got 1]]
          299  +  lset unexpected 1 [lsort [lindex $unexpected 1]]
          300  +  te_dataset_ne $testname $got $unexpected
          301  +}
          302  +
          303  +
          304  +#-------------------------------------------------------------------------
          305  +#
          306  +proc test_join {tn sqljoin tbljoinargs} {
          307  +  set sql [te_read_sql db "SELECT * FROM $sqljoin"]
          308  +  set te  [te_tbljoin db {*}$tbljoinargs]
          309  +  te_dataset_eq_unordered $tn $sql $te
          310  +}
          311  +
          312  +drop_all_tables
          313  +do_execsql_test e_select-2.0 {
          314  +  CREATE TABLE t1(a, b);
          315  +  CREATE TABLE t2(a, b);
          316  +  CREATE TABLE t3(b COLLATE nocase);
          317  +
          318  +  INSERT INTO t1 VALUES(2, 'B');
          319  +  INSERT INTO t1 VALUES(1, 'A');
          320  +  INSERT INTO t1 VALUES(4, 'D');
          321  +  INSERT INTO t1 VALUES(NULL, NULL);
          322  +  INSERT INTO t1 VALUES(3, NULL);
          323  +
          324  +  INSERT INTO t2 VALUES(1, 'A');
          325  +  INSERT INTO t2 VALUES(2, NULL);
          326  +  INSERT INTO t2 VALUES(5, 'E');
          327  +  INSERT INTO t2 VALUES(NULL, NULL);
          328  +  INSERT INTO t2 VALUES(3, 'C');
          329  +
          330  +  INSERT INTO t3 VALUES('a');
          331  +  INSERT INTO t3 VALUES('c');
          332  +  INSERT INTO t3 VALUES('b');
          333  +} {}
          334  +
          335  +foreach {tn indexes} {
          336  +  e_select-2.1.1 { }
          337  +  e_select-2.1.2 { CREATE INDEX i1 ON t1(a) }
          338  +  e_select-2.1.3 { CREATE INDEX i1 ON t2(a) }
          339  +  e_select-2.1.4 { CREATE INDEX i1 ON t3(b) }
          340  +} {
          341  +
          342  +  catchsql { DROP INDEX i1 }
          343  +  catchsql { DROP INDEX i2 }
          344  +  catchsql { DROP INDEX i3 }
          345  +  execsql $indexes
          346  +
          347  +  # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
          348  +  # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
          349  +  # then the result of the join is simply the cartesian product of the
          350  +  # left and right-hand datasets.
          351  +  #
          352  +  # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
          353  +  # JOIN", "JOIN" and "," join operators.
          354  +  #
          355  +  # EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the
          356  +  # same data as the "INNER JOIN", "JOIN" and "," operators
          357  +  #
          358  +  test_join $tn.1.1  "t1, t2"                {t1 t2}
          359  +  test_join $tn.1.2  "t1 INNER JOIN t2"      {t1 t2}
          360  +  test_join $tn.1.3  "t1 CROSS JOIN t2"      {t1 t2}
          361  +  test_join $tn.1.4  "t1 JOIN t2"            {t1 t2}
          362  +  test_join $tn.1.5  "t2, t3"                {t2 t3}
          363  +  test_join $tn.1.6  "t2 INNER JOIN t3"      {t2 t3}
          364  +  test_join $tn.1.7  "t2 CROSS JOIN t3"      {t2 t3}
          365  +  test_join $tn.1.8  "t2 JOIN t3"            {t2 t3}
          366  +  test_join $tn.1.9  "t2, t2 AS x"           {t2 t2}
          367  +  test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
          368  +  test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
          369  +  test_join $tn.1.12 "t2 JOIN t2 AS x"       {t2 t2}
          370  +
          371  +  # EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
          372  +  # the ON expression is evaluated for each row of the cartesian product
          373  +  # as a boolean expression. All rows for which the expression evaluates
          374  +  # to false are excluded from the dataset.
          375  +  #
          376  +  test_join $tn.2.1  "t1, t2 ON (t1.a=t2.a)"  {t1 t2 -on {te_equals a a}}
          377  +  test_join $tn.2.2  "t2, t1 ON (t1.a=t2.a)"  {t2 t1 -on {te_equals a a}}
          378  +  test_join $tn.2.3  "t2, t1 ON (1)"          {t2 t1 -on te_true}
          379  +  test_join $tn.2.4  "t2, t1 ON (NULL)"       {t2 t1 -on te_false}
          380  +  test_join $tn.2.5  "t2, t1 ON (1.1-1.1)"    {t2 t1 -on te_false}
          381  +  test_join $tn.2.6  "t1, t2 ON (1.1-1.0)"    {t1 t2 -on te_true}
          382  +
          383  +
          384  +  test_join $tn.3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}}
          385  +  test_join $tn.4 "t1 LEFT JOIN t2 USING (a)" {
          386  +    t1 t2 -left -using a -on {te_equals a a}
          387  +  }
          388  +  test_join $tn.5 "t1 CROSS JOIN t2 USING(b, a)" {
          389  +    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          390  +  }
          391  +  test_join $tn.6 "t1 NATURAL JOIN t2" {
          392  +    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          393  +  }
          394  +  test_join $tn.7 "t1 NATURAL INNER JOIN t2" {
          395  +    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          396  +  }
          397  +  test_join $tn.8 "t1 NATURAL CROSS JOIN t2" {
          398  +    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          399  +  }
          400  +  test_join $tn.9 "t1 NATURAL INNER JOIN t2" {
          401  +    t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          402  +  }
          403  +  test_join $tn.10 "t1 NATURAL LEFT JOIN t2" {
          404  +    t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          405  +  }
          406  +  test_join $tn.11 "t1 NATURAL LEFT OUTER JOIN t2" {
          407  +    t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          408  +  }
          409  +  test_join $tn.12 "t2 NATURAL JOIN t1" {
          410  +    t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          411  +  }
          412  +  test_join $tn.13 "t2 NATURAL INNER JOIN t1" {
          413  +    t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          414  +  }
          415  +  test_join $tn.14 "t2 NATURAL CROSS JOIN t1" {
          416  +    t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          417  +  }
          418  +  test_join $tn.15 "t2 NATURAL INNER JOIN t1" {
          419  +    t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          420  +  }
          421  +  test_join $tn.16 "t2 NATURAL LEFT JOIN t1" {
          422  +    t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          423  +  }
          424  +  test_join $tn.17 "t2 NATURAL LEFT OUTER JOIN t1" {
          425  +    t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}}
          426  +  }
          427  +  test_join $tn.18 "t1 LEFT JOIN t2 USING (b)" {
          428  +    t1 t2 -left -using b -on {te_equals b b}
          429  +  }
          430  +  test_join $tn.19 "t1 JOIN t3 USING(b)" {t1 t3 -using b -on {te_equals b b}}
          431  +  test_join $tn.20 "t3 JOIN t1 USING(b)" {
          432  +    t3 t1 -using b -on {te_equals -nocase b b}
          433  +  }
          434  +  test_join $tn.21 "t1 NATURAL JOIN t3"  {
          435  +    t1 t3 -using b -on {te_equals b b}
          436  +  }
          437  +  test_join $tn.22 "t3 NATURAL JOIN t1"  {
          438  +    t3 t1 -using b -on {te_equals -nocase b b}
          439  +  }
          440  +  test_join $tn.23 "t1 NATURAL LEFT JOIN t3" {
          441  +    t1 t3 -left -using b -on {te_equals b b}
          442  +  }
          443  +  test_join $tn.24 "t3 NATURAL LEFT JOIN t1" {
          444  +    t3 t1 -left -using b -on {te_equals -nocase b b}
          445  +  }
          446  +  test_join $tn.25 "t1 LEFT JOIN t3 ON (t3.b=t1.b)" {
          447  +    t1 t3 -left -on {te_equals -nocase b b}
          448  +  }
          449  +  test_join $tn.26 "t1 LEFT JOIN t3 ON (t1.b=t3.b)" {
          450  +    t1 t3 -left -on {te_equals b b}
          451  +  }
          452  +  test_join $tn.27 "t1 JOIN t3 ON (t1.b=t3.b)" { t1 t3 -on {te_equals b b} }
          453  +
          454  +  # EVIDENCE-OF: R-28760-53843 When more than two tables are joined
          455  +  # together as part of a FROM clause, the join operations are processed
          456  +  # in order from left to right. In other words, the FROM clause (A
          457  +  # join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C).
          458  +  #
          459  +  #   Tests 28a and 28b show that the statement above is true for this case.
          460  +  #   Test 28c shows that if the parenthesis force a different order of
          461  +  #   evaluation the result is different. Test 28d verifies that the result
          462  +  #   of the query with the parenthesis forcing a different order of evaluation
          463  +  #   is as calculated by the [te_*] procs.
          464  +  #
          465  +  set t3_natural_left_join_t2 [
          466  +    te_tbljoin db t3 t2 -left -using {b} -on {te_equals -nocase b b}
          467  +  ]
          468  +  set t1 [te_read_tbl db t1]
          469  +  te_dataset_eq_unordered $tn.28a [
          470  +    te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1"
          471  +  ] [te_join $t3_natural_left_join_t2 $t1                                \
          472  +      -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}}  \
          473  +  ]
          474  +
          475  +  te_dataset_eq_unordered $tn.28b [
          476  +    te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1"
          477  +  ] [te_join $t3_natural_left_join_t2 $t1                                \
          478  +      -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}}  \
          479  +  ]
          480  +
          481  +  te_dataset_ne_unordered $tn.28c [
          482  +    te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1"
          483  +  ] [
          484  +    te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)"
          485  +  ]
          486  +
          487  +  set t2_natural_join_t1 [te_tbljoin db t2 t1 -using {a b}                 \
          488  +        -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}}  \
          489  +  ]
          490  +  set t3 [te_read_tbl db t3]
          491  +  te_dataset_eq_unordered $tn.28d [
          492  +    te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)"
          493  +  ] [te_join $t3 $t2_natural_join_t1                                       \
          494  +      -left -using {b} -on {te_equals -nocase b b}                         \
          495  +  ]
          496  +}
          497  +
          498  +do_execsql_test e_select-2.2.0 {
          499  +  CREATE TABLE t4(x TEXT COLLATE nocase);
          500  +  CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary);
          501  +
          502  +  INSERT INTO t4 VALUES('2.0');
          503  +  INSERT INTO t4 VALUES('TWO');
          504  +  INSERT INTO t5 VALUES(2, 'two');
          505  +} {}
          506  +
          507  +# EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source
          508  +# following the FROM clause in a simple SELECT statement is handled as
          509  +# if it was a table containing the data returned by executing the
          510  +# sub-select statement.
          511  +#
          512  +# EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset
          513  +# inherits the collation sequence and affinity of the corresponding
          514  +# expression in the sub-select statement.
          515  +#
          516  +foreach {tn subselect select spec} {
          517  +  1   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss%" 
          518  +      {t1 %ss%}
          519  +
          520  +  2   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" 
          521  +      {t1 %ss% -on {te_equals 0 0}}
          522  +
          523  +  3   "SELECT * FROM t2"   "SELECT * FROM %ss% AS x JOIN t1 ON (t1.a=x.a)" 
          524  +      {%ss% t1 -on {te_equals 0 0}}
          525  +
          526  +  4   "SELECT * FROM t1, t2" "SELECT * FROM %ss% AS x JOIN t3"
          527  +      {%ss% t3}
          528  +
          529  +  5   "SELECT * FROM t1, t2" "SELECT * FROM %ss% NATURAL JOIN t3"
          530  +      {%ss% t3 -using b -on {te_equals 1 0}}
          531  +
          532  +  6   "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL JOIN %ss%"
          533  +      {t3 %ss% -using b -on {te_equals -nocase 0 1}}
          534  +
          535  +  7   "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL LEFT JOIN %ss%"
          536  +      {t3 %ss% -left -using b -on {te_equals -nocase 0 1}}
          537  +
          538  +  8   "SELECT count(*) AS y FROM t4"   "SELECT * FROM t5, %ss% USING (y)"
          539  +      {t5 %ss% -using y -on {te_equals -affinity text 0 0}}
          540  +
          541  +  9   "SELECT count(*) AS y FROM t4"   "SELECT * FROM %ss%, t5 USING (y)"
          542  +      {%ss% t5 -using y -on {te_equals -affinity text 0 0}}
          543  +
          544  +  10  "SELECT x AS y FROM t4"   "SELECT * FROM %ss% JOIN t5 USING (y)"
          545  +      {%ss% t5 -using y -on {te_equals -nocase -affinity integer 0 0}}
          546  +
          547  +  11  "SELECT x AS y FROM t4"   "SELECT * FROM t5 JOIN %ss% USING (y)"
          548  +      {t5 %ss% -using y -on {te_equals -nocase -affinity integer 0 0}}
          549  +
          550  +  12  "SELECT y AS x FROM t5"   "SELECT * FROM %ss% JOIN t4 USING (x)"
          551  +      {%ss% t4 -using x -on {te_equals -nocase -affinity integer 0 0}}
          552  +
          553  +  13  "SELECT y AS x FROM t5"   "SELECT * FROM t4 JOIN %ss% USING (x)"
          554  +      {t4 %ss% -using x -on {te_equals -nocase -affinity integer 0 0}}
          555  +
          556  +  14  "SELECT +y AS x FROM t5"   "SELECT * FROM %ss% JOIN t4 USING (x)"
          557  +      {%ss% t4 -using x -on {te_equals -nocase -affinity text 0 0}}
          558  +
          559  +  15  "SELECT +y AS x FROM t5"   "SELECT * FROM t4 JOIN %ss% USING (x)"
          560  +      {t4 %ss% -using x -on {te_equals -nocase -affinity text 0 0}}
          561  +} {
          562  +
          563  +  # Create a temporary table named %ss% containing the data returned by
          564  +  # the sub-select. Then have the [te_tbljoin] proc use this table to
          565  +  # compute the expected results of the $select query. Drop the temporary
          566  +  # table before continuing.
          567  +  #
          568  +  execsql "CREATE TEMP TABLE '%ss%' AS $subselect"
          569  +  set te [eval te_tbljoin db $spec]
          570  +  execsql "DROP TABLE '%ss%'"
          571  +
          572  +  # Check that the actual data returned by the $select query is the same
          573  +  # as the expected data calculated using [te_tbljoin] above.
          574  +  #
          575  +  te_dataset_eq_unordered e_select-2.2.1.$tn [
          576  +    te_read_sql db [string map [list %ss% "($subselect)"] $select]
          577  +  ] $te
          578  +}
          579  +
          580  +finish_test

Added test/e_vacuum.test.

            1  +# 2010 September 24
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file implements tests to verify that the "testable statements" in 
           13  +# the lang_vacuum.html document are correct.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +sqlite3_test_control_pending_byte 0x1000000
           20  +
           21  +proc create_db {{sql ""}} {
           22  +  catch { db close }
           23  +  forcedelete test.db
           24  +  sqlite3 db test.db
           25  +
           26  +  db transaction {
           27  +    execsql { PRAGMA page_size = 1024; }
           28  +    execsql $sql
           29  +    execsql {
           30  +      CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
           31  +      INSERT INTO t1 VALUES(1, randomblob(400));
           32  +      INSERT INTO t1 SELECT a+1,  randomblob(400) FROM t1;
           33  +      INSERT INTO t1 SELECT a+2,  randomblob(400) FROM t1;
           34  +      INSERT INTO t1 SELECT a+4,  randomblob(400) FROM t1;
           35  +      INSERT INTO t1 SELECT a+8,  randomblob(400) FROM t1;
           36  +      INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1;
           37  +      INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1;
           38  +      INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1;
           39  +
           40  +      CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
           41  +      INSERT INTO t2 SELECT * FROM t1;
           42  +    }
           43  +  }
           44  +
           45  +  return [expr {[file size test.db] / 1024}]
           46  +}
           47  +
           48  +# This proc returns the number of contiguous blocks of pages that make up
           49  +# the table or index named by the only argument. For example, if the table
           50  +# occupies database pages 3, 4, 8 and 9, then this command returns 2 (there
           51  +# are 2 fragments - one consisting of pages 3 and 4, the other of fragments
           52  +# 8 and 9).
           53  +#
           54  +proc fragment_count {name} {
           55  +  execsql { CREATE VIRTUAL TABLE temp.stat USING dbstat }
           56  +  set nFrag 1
           57  +  db eval {SELECT pageno FROM stat WHERE name = 't1' ORDER BY pageno} {
           58  +    if {[info exists prevpageno] && $prevpageno != $pageno-1} {
           59  +      incr nFrag
           60  +    }
           61  +    set prevpageno $pageno
           62  +  }
           63  +  execsql { DROP TABLE temp.stat }
           64  +  set nFrag
           65  +}
           66  +
           67  +
           68  +# EVIDENCE-OF: R-63707-33375 -- syntax diagram vacuum-stmt
           69  +#
           70  +do_execsql_test e_vacuum-0.1 { VACUUM } {}
           71  +
           72  +# EVIDENCE-OF: R-51469-36013 Unless SQLite is running in
           73  +# "auto_vacuum=FULL" mode, when a large amount of data is deleted from
           74  +# the database file it leaves behind empty space, or "free" database
           75  +# pages.
           76  +#
           77  +# EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database
           78  +# reclaims this space and reduces the size of the database file.
           79  +#
           80  +foreach {tn avmode sz} {
           81  +  1 none        7 
           82  +  2 full        8 
           83  +  3 incremental 8
           84  +} {
           85  +  set nPage [create_db "PRAGMA auto_vacuum = $avmode"]
           86  +
           87  +  do_execsql_test e_vacuum-1.1.$tn.1 {
           88  +    DELETE FROM t1;
           89  +    DELETE FROM t2;
           90  +  } {}
           91  +
           92  +  if {$avmode == "full"} {
           93  +    # This branch tests the "unless ... auto_vacuum=FULL" in the requirement
           94  +    # above. If auto_vacuum is set to FULL, then no empty space is left in
           95  +    # the database file.
           96  +    do_execsql_test e_vacuum-1.1.$tn.2 {PRAGMA freelist_count} 0
           97  +  } else {
           98  +    set freelist [expr {$nPage - $sz}]
           99  +    if {$avmode == "incremental"} { 
          100  +      # The page size is 1024 bytes. Therefore, assuming the database contains
          101  +      # somewhere between 207 and 411 pages (it does), there are 2 pointer-map
          102  +      # pages.
          103  +      incr freelist -2
          104  +    }
          105  +    do_execsql_test e_vacuum-1.1.$tn.3 {PRAGMA freelist_count} $freelist
          106  +    do_execsql_test e_vacuum-1.1.$tn.4 {VACUUM} {}
          107  +  }
          108  +
          109  +  do_test e_vacuum-1.1.$tn.5 { expr {[file size test.db] / 1024} } $sz
          110  +}
          111  +
          112  +# EVIDENCE-OF: R-50943-18433 Frequent inserts, updates, and deletes can
          113  +# cause the database file to become fragmented - where data for a single
          114  +# table or index is scattered around the database file.
          115  +#
          116  +# EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and
          117  +# index is largely stored contiguously within the database file.
          118  +#
          119  +#   e_vacuum-1.2.1 - Perform many INSERT, UPDATE and DELETE ops on table t1.
          120  +#   e_vacuum-1.2.2 - Verify that t1 and its indexes are now quite fragmented.
          121  +#   e_vacuum-1.2.3 - Run VACUUM.
          122  +#   e_vacuum-1.2.4 - Verify that t1 and its indexes are now much 
          123  +#                    less fragmented.
          124  +#
          125  +create_db 
          126  +register_dbstat_vtab db
          127  +do_execsql_test e_vacuum-1.2.1 {
          128  +  DELETE FROM t1 WHERE a%2;
          129  +  INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2;
          130  +  UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0;
          131  +} {}
          132  +
          133  +do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1
          134  +do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1
          135  +do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1
          136  +
          137  +do_execsql_test e_vacuum-1.2.3 { VACUUM } {}
          138  +
          139  +# In practice, the tables and indexes each end up stored as two fragments -
          140  +# one containing the root page and another containing all other pages.
          141  +#
          142  +do_test e_vacuum-1.2.4.1 { fragment_count t1 }                    2
          143  +do_test e_vacuum-1.2.4.2 { fragment_count sqlite_autoindex_t1_1 } 2
          144  +do_test e_vacuum-1.2.4.3 { fragment_count sqlite_autoindex_t1_2 } 2
          145  +
          146  +# EVIDENCE-OF: R-20474-44465 Normally, the database page_size and
          147  +# whether or not the database supports auto_vacuum must be configured
          148  +# before the database file is actually created.
          149  +#
          150  +do_test e_vacuum-1.3.1.1 {
          151  +  create_db "PRAGMA page_size = 1024 ; PRAGMA auto_vacuum = FULL"
          152  +  execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
          153  +} {1024 1}
          154  +do_test e_vacuum-1.3.1.2 {
          155  +  execsql { PRAGMA page_size = 2048 }
          156  +  execsql { PRAGMA auto_vacuum = NONE }
          157  +  execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
          158  +} {1024 1}
          159  +
          160  +# EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode,
          161  +# the page_size and/or auto_vacuum properties of an existing database
          162  +# may be changed by using the page_size and/or pragma auto_vacuum
          163  +# pragmas and then immediately VACUUMing the database.
          164  +#
          165  +do_test e_vacuum-1.3.2.1 {
          166  +  execsql { PRAGMA journal_mode = delete }
          167  +  execsql { PRAGMA page_size = 2048 }
          168  +  execsql { PRAGMA auto_vacuum = NONE }
          169  +  execsql VACUUM
          170  +  execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
          171  +} {2048 0}
          172  +
          173  +# EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the
          174  +# auto_vacuum support property can be changed using VACUUM.
          175  +#
          176  +do_test e_vacuum-1.3.3.1 {
          177  +  execsql { PRAGMA journal_mode = wal }
          178  +  execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
          179  +} {2048 0}
          180  +do_test e_vacuum-1.3.3.2 {
          181  +  execsql { PRAGMA page_size = 1024 }
          182  +  execsql { PRAGMA auto_vacuum = FULL }
          183  +  execsql VACUUM
          184  +  execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
          185  +} {2048 1}
          186  +
          187  +# EVIDENCE-OF: R-38001-03952 VACUUM only works on the main database. It
          188  +# is not possible to VACUUM an attached database file.
          189  +forcedelete test.db2
          190  +create_db
          191  +do_execsql_test e_vacuum-2.1.1 {
          192  +  ATTACH 'test.db2' AS aux;
          193  +  PRAGMA aux.page_size = 1024;
          194  +  CREATE TABLE aux.t3 AS SELECT * FROM t1;
          195  +  DELETE FROM t3;
          196  +} {}
          197  +do_test e_vacuum-2.1.2 { expr { ([file size test.db2] / 1024)>50 } } 1
          198  +
          199  +# Try everything we can think of to get the aux database vacuumed:
          200  +do_execsql_test e_vacuum-2.1.3 { VACUUM } {}
          201  +do_execsql_test e_vacuum-2.1.4 { VACUUM aux } {}
          202  +do_execsql_test e_vacuum-2.1.5 { VACUUM 'test.db2' } {}
          203  +
          204  +# Despite our efforts, space in the aux database has not been reclaimed:
          205  +do_test e_vacuum-2.1.6 { expr { ([file size test.db2] / 1024)>50 } } 1
          206  +
          207  +# EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of
          208  +# entries in any tables that do not have an explicit INTEGER PRIMARY
          209  +# KEY.
          210  +#
          211  +#   Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when
          212  +#   a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding
          213  +#   an INTEGER PRIMARY KEY column to a table stops this from happening.
          214  +#
          215  +do_execsql_test e_vacuum-3.1.1 {
          216  +  CREATE TABLE t4(x);
          217  +  INSERT INTO t4(x) VALUES('x');
          218  +  INSERT INTO t4(x) VALUES('y');
          219  +  INSERT INTO t4(x) VALUES('z');
          220  +  DELETE FROM t4 WHERE x = 'y';
          221  +  SELECT rowid, x FROM t4;
          222  +} {1 x 3 z}
          223  +do_execsql_test e_vacuum-3.1.2 {
          224  +  VACUUM;
          225  +  SELECT rowid, x FROM t4;
          226  +} {1 x 2 z}
          227  +
          228  +do_execsql_test e_vacuum-3.1.3 {
          229  +  CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
          230  +  INSERT INTO t5(x) VALUES('x');
          231  +  INSERT INTO t5(x) VALUES('y');
          232  +  INSERT INTO t5(x) VALUES('z');
          233  +  DELETE FROM t5 WHERE x = 'y';
          234  +  SELECT rowid, x FROM t5;
          235  +} {1 x 3 z}
          236  +do_execsql_test e_vacuum-3.1.4 {
          237  +  VACUUM;
          238  +  SELECT rowid, x FROM t5;
          239  +} {1 x 3 z}
          240  +
          241  +# EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open
          242  +# transaction, or if there are one or more active SQL statements when it
          243  +# is run.
          244  +#
          245  +do_execsql_test  e_vacuum-3.2.1.1 { BEGIN } {}
          246  +do_catchsql_test e_vacuum-3.2.1.2 { 
          247  +  VACUUM 
          248  +} {1 {cannot VACUUM from within a transaction}}
          249  +do_execsql_test  e_vacuum-3.2.1.3 { COMMIT } {}
          250  +do_execsql_test  e_vacuum-3.2.1.4 { VACUUM } {}
          251  +do_execsql_test  e_vacuum-3.2.1.5 { SAVEPOINT x } {}
          252  +do_catchsql_test e_vacuum-3.2.1.6 { 
          253  +  VACUUM 
          254  +} {1 {cannot VACUUM from within a transaction}}
          255  +do_execsql_test  e_vacuum-3.2.1.7 { COMMIT } {}
          256  +do_execsql_test  e_vacuum-3.2.1.8 { VACUUM } {}
          257  +
          258  +create_db
          259  +do_test e_vacuum-3.2.2.1 {
          260  +  set res ""
          261  +  db eval { SELECT a FROM t1 } {
          262  +    if {$a == 10} { set res [catchsql VACUUM] }
          263  +  }
          264  +  set res
          265  +} {1 {cannot VACUUM - SQL statements in progress}}
          266  +
          267  +
          268  +# EVIDENCE-OF: R-38735-12540 As of SQLite version 3.1, an alternative to
          269  +# using the VACUUM command to reclaim space after data has been deleted
          270  +# is auto-vacuum mode, enabled using the auto_vacuum pragma.
          271  +#
          272  +do_test e_vacuum-3.3.1 {
          273  +  create_db { PRAGMA auto_vacuum = FULL }
          274  +  execsql { PRAGMA auto_vacuum }
          275  +} {1}
          276  +
          277  +# EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database
          278  +# free pages may be reclaimed after deleting data, causing the file to
          279  +# shrink, without rebuilding the entire database using VACUUM.
          280  +#
          281  +do_test e_vacuum-3.3.2.1 {
          282  +  create_db { PRAGMA auto_vacuum = FULL }
          283  +  execsql {
          284  +    DELETE FROM t1;
          285  +    DELETE FROM t2;
          286  +  }
          287  +  expr {[file size test.db] / 1024}
          288  +} {8}
          289  +do_test e_vacuum-3.3.2.2 {
          290  +  create_db { PRAGMA auto_vacuum = INCREMENTAL }
          291  +  execsql {
          292  +    DELETE FROM t1;
          293  +    DELETE FROM t2;
          294  +    PRAGMA incremental_vacuum;
          295  +  }
          296  +  expr {[file size test.db] / 1024}
          297  +} {8}
          298  +
          299  +finish_test

Changes to test/vacuum2.test.

   177    177       db close
   178    178       sqlite3 db test.db
   179    179       execsql {
   180    180         pragma auto_vacuum;
   181    181       }
   182    182     } {2}
   183    183   }
          184  +
          185  +
          186  +#-------------------------------------------------------------------------
          187  +# The following block of tests verify the behaviour of the library when
          188  +# a database is VACUUMed when there are one or more unfinalized SQL 
          189  +# statements reading the same database using the same db handle.
          190  +#
          191  +db close
          192  +forcedelete test.db
          193  +sqlite3 db test.db
          194  +do_execsql_test vacuum2-5.1 {
          195  +  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
          196  +  INSERT INTO t1 VALUES(1, randomblob(500));
          197  +  INSERT INTO t1 SELECT a+1, randomblob(500) FROM t1;      -- 2
          198  +  INSERT INTO t1 SELECT a+2, randomblob(500) FROM t1;      -- 4 
          199  +  INSERT INTO t1 SELECT a+4, randomblob(500) FROM t1;      -- 8 
          200  +  INSERT INTO t1 SELECT a+8, randomblob(500) FROM t1;      -- 16 
          201  +} {}
          202  +
          203  +do_test vacuum2-5.2 {
          204  +  list [catch {
          205  +    db eval {SELECT a, b FROM t1} { if {$a == 8} { execsql VACUUM } }
          206  +  } msg] $msg
          207  +} {1 {cannot VACUUM - SQL statements in progress}}
          208  +
          209  +do_test vacuum2-5.3 {
          210  +  list [catch {
          211  +    db eval {SELECT 1, 2, 3} { execsql VACUUM }
          212  +  } msg] $msg
          213  +} {1 {cannot VACUUM - SQL statements in progress}}
          214  +
          215  +do_test vacuum2-5.4 {
          216  +  set res ""
          217  +  set res2 ""
          218  +  db eval {SELECT a, b FROM t1 WHERE a<=10} {
          219  +    if {$a==6} { set res [catchsql VACUUM] }
          220  +    lappend res2 $a
          221  +  }
          222  +  lappend res2 $res
          223  +} {1 2 3 4 5 6 7 8 9 10 {1 {cannot VACUUM - SQL statements in progress}}}
          224  +
   184    225   
   185    226   finish_test