AryaWu/sqlite
0
1# 2014-10-242#3# The author disclaims copyright to this source code. In place of4# 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. The13# focus of this script is testing automatic index creation logic,14# and specifically creation of automatic partial indexes.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20do_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 |}28do_execsql_test autoindex4-1.1 {29 SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=555;30} {}31 32do_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 {} {} |}35do_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 {} {} |}38do_execsql_test autoindex4-1.3 {39 SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234;40} {234 def {} {} | 234 ghi {} {} |}41do_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 {} {} |}44do_execsql_test autoindex4-1.4 {45 SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555;46} {}47do_execsql_test autoindex4-1.4-rj {48 SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 WHERE a=234 AND x=555;49} {}50 51 52do_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 t358 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#66do_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.ItemName74 FROM Items75 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}80do_execsql_test autoindex4-3.1 {81 SELECT Items.ItemName82 FROM A83 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}88do_execsql_test autoindex4-3.10 {89 CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';90 91 SELECT Items.ItemName92 FROM Items93 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}98do_execsql_test autoindex4-3.11 {99 SELECT Items.ItemName100 FROM A101 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 to108# create a partial index more often.109#110unset -nocomplain id data1 data2 jointype onclause whereclause answer111foreach {id data1 data2 jointype onclause whereclause answer} {112 1113 VALUES(1,2),(3,4)114 VALUES(1,2),(3,4)115 {LEFT JOIN}116 a=x117 {y=4 OR y IS NULL}118 {3 4 3 4}119 120 2121 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 3129 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 4137 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.1145 VALUES(1,2),(3,4),(NULL,4)146 VALUES(1,2),(3,4)147 {LEFT JOIN}148 a=x149 {y=4 OR y IS NULL}150 {3 4 3 4 {} 4 {} {}}151 152 5.2153 VALUES(1,2),(3,4),(NULL,4)154 VALUES(1,2),(3,4)155 {LEFT JOIN}156 a=x157 {y NOT IN ()}158 {1 2 1 2 3 4 3 4 {} 4 {} {}}159 160 5.3161 VALUES(1,2),(3,4),(NULL,4)162 VALUES(1,2),(3,4)163 {LEFT JOIN}164 a=x165 {y NOT IN (SELECT 1 WHERE false)}166 {1 2 1 2 3 4 3 4 {} 4 {} {}}167 168 6169 VALUES(1,2),(3,4)170 VALUES(1,2),(3,4),(NULL,4)171 {LEFT JOIN}172 {a=x AND y=4}173 {coalesce(y,4)==4}174 {1 2 {} {} 3 4 3 4}175 176 7177 VALUES(1,2),(3,4),(NULL,4)178 VALUES(1,2),(3,4),(NULL,4)179 {JOIN}180 {a=x}181 {y=4 OR y IS NULL}182 {3 4 3 4}183 184 8185 VALUES(1,2),(3,4)186 VALUES(1,2),(3,4)187 {JOIN}188 {a=x AND y=4}189 {coalesce(y,4)==4}190 {3 4 3 4}191} {192 do_test autoindex4-4.$id.0 {193 db eval {194 DROP TABLE IF EXISTS t1;195 CREATE TABLE t1(a INT, b INT);196 DROP TABLE IF EXISTS t2;197 CREATE TABLE t2(x INT, y INT);198 }199 db eval "INSERT INTO t1(a,b) $data1;"200 db eval "INSERT INTO t2(x,y) $data2;"201 } {}202 set sql "SELECT * FROM t1 $jointype t2 ON $onclause WHERE $whereclause"203 # puts "sql = $sql"204 do_test autoindex4-4.$id.1 {205 db eval {PRAGMA automatic_index=ON;}206 db eval $sql207 } $answer208 do_test autoindex4-4.$id.2 {209 db eval {PRAGMA automatic_index=OFF;}210 db eval $sql211 } $answer212 do_test autoindex4-4.$id.3 {213 db eval {PRAGMA automatic_index=ON;}214 optimization_control db all 0215 db eval $sql216 } $answer217 optimization_control db all 1218}219 220 221 222 223finish_test224