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: |
83ecec5d158c48e2fb93b0111ade62f0 |
| 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
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