gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/autoindex4.test (about) 1 # 2014-10-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 regression tests for SQLite library. The 13 # focus of this script is testing automatic index creation logic, 14 # and specifically creation of automatic partial indexes. 15 # 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 do_execsql_test autoindex4-1.0 { 21 CREATE TABLE t1(a,b); 22 INSERT INTO t1 VALUES(123,'abc'),(234,'def'),(234,'ghi'),(345,'jkl'); 23 CREATE TABLE t2(x,y); 24 INSERT INTO t2 VALUES(987,'zyx'),(654,'wvu'),(987,'rqp'); 25 26 SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=987 ORDER BY +b; 27 } {234 def 987 rqp | 234 def 987 zyx | 234 ghi 987 rqp | 234 ghi 987 zyx |} 28 do_execsql_test autoindex4-1.1 { 29 SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=555; 30 } {} 31 32 do_execsql_test autoindex4-1.2 { 33 SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555; 34 } {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |} 35 do_execsql_test autoindex4-1.2-rj { 36 SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON a=234 AND x=555; 37 } {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |} 38 do_execsql_test autoindex4-1.3 { 39 SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234; 40 } {234 def {} {} | 234 ghi {} {} |} 41 do_execsql_test autoindex4-1.3-rj { 42 SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON x=555 WHERE a=234; 43 } {234 def {} {} | 234 ghi {} {} |} 44 do_execsql_test autoindex4-1.4 { 45 SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555; 46 } {} 47 do_execsql_test autoindex4-1.4-rj { 48 SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 WHERE a=234 AND x=555; 49 } {} 50 51 52 do_execsql_test autoindex4-2.0 { 53 CREATE TABLE t3(e,f); 54 INSERT INTO t3 VALUES(123,654),(555,444),(234,987); 55 56 SELECT (SELECT count(*) FROM t1, t2 WHERE a=e AND x=f), e, f, '|' 57 FROM t3 58 ORDER BY rowid; 59 } {1 123 654 | 0 555 444 | 4 234 987 |} 60 61 # Ticket [2326c258d02ead33d] 62 # Two joins, one with and the other without an ORDER BY clause. 63 # The one without ORDER BY correctly returns two rows of result. 64 # The one with ORDER BY returns no rows. 65 # 66 do_execsql_test autoindex4-3.0 { 67 CREATE TABLE A(Name text); 68 CREATE TABLE Items(ItemName text , Name text); 69 INSERT INTO Items VALUES('Item1','Parent'); 70 INSERT INTO Items VALUES('Item2','Parent'); 71 CREATE TABLE B(Name text); 72 73 SELECT Items.ItemName 74 FROM Items 75 LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy') 76 LEFT JOIN B ON (B.Name = Items.ItemName) 77 WHERE Items.Name = 'Parent' 78 ORDER BY Items.ItemName; 79 } {Item1 Item2} 80 do_execsql_test autoindex4-3.1 { 81 SELECT Items.ItemName 82 FROM A 83 RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy') 84 LEFT JOIN B ON (B.Name = Items.ItemName) 85 WHERE Items.Name = 'Parent' 86 ORDER BY Items.ItemName; 87 } {Item1 Item2} 88 do_execsql_test autoindex4-3.10 { 89 CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy'; 90 91 SELECT Items.ItemName 92 FROM Items 93 LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy') 94 LEFT JOIN B ON (B.Name = Items.ItemName) 95 WHERE Items.Name = 'Parent' 96 ORDER BY Items.ItemName; 97 } {Item1 Item2} 98 do_execsql_test autoindex4-3.11 { 99 SELECT Items.ItemName 100 FROM A 101 RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy') 102 LEFT JOIN B ON (B.Name = Items.ItemName) 103 WHERE Items.Name = 'Parent' 104 ORDER BY Items.ItemName; 105 } {Item1 Item2} 106 107 # 2021-11-30 - Enhancement to help the automatic index mechanism to 108 # create a partial index more often. 109 # 110 unset -nocomplain id data1 data2 jointype onclause whereclause answer 111 foreach {id data1 data2 jointype onclause whereclause answer} { 112 1 113 VALUES(1,2),(3,4) 114 VALUES(1,2),(3,4) 115 {LEFT JOIN} 116 a=x 117 {y=4 OR y IS NULL} 118 {3 4 3 4} 119 120 2 121 VALUES(1,2),(3,4) 122 VALUES(1,2),(3,4) 123 {LEFT JOIN} 124 {a=x AND y=4} 125 {coalesce(y,4)==4} 126 {1 2 {} {} 3 4 3 4} 127 128 3 129 VALUES(1,2),(3,4) 130 VALUES(1,2),(3,4) 131 {JOIN} 132 {a=x} 133 {y=4 OR y IS NULL} 134 {3 4 3 4} 135 136 4 137 VALUES(1,2),(3,4) 138 VALUES(1,2),(3,4) 139 {JOIN} 140 {a=x AND y=4} 141 {coalesce(y,4)==4} 142 {3 4 3 4} 143 144 5 145 VALUES(1,2),(3,4),(NULL,4) 146 VALUES(1,2),(3,4) 147 {LEFT JOIN} 148 a=x 149 {y=4 OR y IS NULL} 150 {3 4 3 4 {} 4 {} {}} 151 152 6 153 VALUES(1,2),(3,4) 154 VALUES(1,2),(3,4),(NULL,4) 155 {LEFT JOIN} 156 {a=x AND y=4} 157 {coalesce(y,4)==4} 158 {1 2 {} {} 3 4 3 4} 159 160 7 161 VALUES(1,2),(3,4),(NULL,4) 162 VALUES(1,2),(3,4),(NULL,4) 163 {JOIN} 164 {a=x} 165 {y=4 OR y IS NULL} 166 {3 4 3 4} 167 168 8 169 VALUES(1,2),(3,4) 170 VALUES(1,2),(3,4) 171 {JOIN} 172 {a=x AND y=4} 173 {coalesce(y,4)==4} 174 {3 4 3 4} 175 } { 176 do_test autoindex4-4.$id.0 { 177 db eval { 178 DROP TABLE IF EXISTS t1; 179 CREATE TABLE t1(a INT, b INT); 180 DROP TABLE IF EXISTS t2; 181 CREATE TABLE t2(x INT, y INT); 182 } 183 db eval "INSERT INTO t1(a,b) $data1;" 184 db eval "INSERT INTO t2(x,y) $data2;" 185 } {} 186 set sql "SELECT * FROM t1 $jointype t2 ON $onclause WHERE $whereclause" 187 # puts "sql = $sql" 188 do_test autoindex4-4.$id.1 { 189 db eval {PRAGMA automatic_index=ON;} 190 db eval $sql 191 } $answer 192 do_test autoindex4-4.$id.2 { 193 db eval {PRAGMA automatic_index=OFF;} 194 db eval $sql 195 } $answer 196 } 197 198 199 200 201 finish_test