1# 2012 November 9 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# Test cases for query planning decisions. 13 14 15# 16# The tests in this file demonstrate the behaviour of the query planner 17# in determining the order in which joined tables are scanned. 18# 19# Assume there are two tables being joined - t1 and t2. Each has a cost 20# if it is the outer loop, and a cost if it is the inner loop. As follows: 21# 22# t1(outer) - cost of scanning t1 as the outer loop. 23# t1(inner) - cost of scanning t1 as the inner loop. 24# t2(outer) - cost of scanning t2 as the outer loop. 25# t2(inner) - cost of scanning t2 as the inner loop. 26# 27# Depending on the order in which the planner nests the scans, the total 28# cost of the join query is one of: 29# 30# t1(outer) * t2(inner) 31# t2(outer) * t1(inner) 32# 33# The tests in this file attempt to verify that the planner nests joins in 34# the correct order when the following are true: 35# 36# + (t1(outer) * t2(inner)) > (t1(inner) * t2(outer) 37# + t1(outer) < t2(outer) 38# 39# In other words, when the best overall query plan has t2 as the outer loop, 40# but when the outer loop is considered independent of the inner, t1 is the 41# most efficient choice. 42# 43# In order to make them more predictable, automatic indexes are turned off for 44# the tests in this file. 45# 46 47set testdir [file dirname $argv0] 48source $testdir/tester.tcl 49set testprefix whereF 50 51do_execsql_test 1.0 { 52 PRAGMA automatic_index = 0; 53 CREATE TABLE t1(a, b, c); 54 CREATE TABLE t2(d, e, f); 55 CREATE UNIQUE INDEX i1 ON t1(a); 56 CREATE UNIQUE INDEX i2 ON t2(d); 57} {} 58 59foreach {tn sql} { 60 1 "SELECT * FROM t1, t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" 61 2 "SELECT * FROM t2, t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" 62 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10" 63} { 64 do_test 1.$tn { 65 db eval "EXPLAIN QUERY PLAN $sql" 66 } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} 67} 68 69do_execsql_test 2.0 { 70 DROP TABLE t1; 71 DROP TABLE t2; 72 CREATE TABLE t1(a, b, c); 73 CREATE TABLE t2(d, e, f); 74 75 CREATE UNIQUE INDEX i1 ON t1(a); 76 CREATE UNIQUE INDEX i2 ON t1(b); 77 CREATE UNIQUE INDEX i3 ON t2(d); 78} {} 79 80foreach {tn sql} { 81 1 "SELECT * FROM t1, t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" 82 2 "SELECT * FROM t2, t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" 83 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" 84} { 85 do_test 2.$tn { 86 db eval "EXPLAIN QUERY PLAN $sql" 87 } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} 88} 89 90do_execsql_test 3.0 { 91 DROP TABLE t1; 92 DROP TABLE t2; 93 CREATE TABLE t1(a, b, c); 94 CREATE TABLE t2(d, e, f); 95 96 CREATE UNIQUE INDEX i1 ON t1(a, b); 97 CREATE INDEX i2 ON t2(d); 98} {} 99 100foreach {tn sql} { 101 1 {SELECT t1.a, t1.b, t2.d, t2.e FROM t1, t2 102 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} 103 104 2 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2, t1 105 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} 106 107 3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 108 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} 109} { 110 do_test 3.$tn { 111 db eval "EXPLAIN QUERY PLAN $sql" 112 } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} 113} 114 115do_execsql_test 4.0 { 116 CREATE TABLE t4(a,b,c,d,e, PRIMARY KEY(a,b,c)); 117 CREATE INDEX t4adc ON t4(a,d,c); 118 CREATE UNIQUE INDEX t4aebc ON t4(a,e,b,c); 119 EXPLAIN QUERY PLAN SELECT rowid FROM t4 WHERE a=? AND b=?; 120} {/a=. AND b=./} 121 122#------------------------------------------------------------------------- 123# Test the following case: 124# 125# ... FROM t1, t2 WHERE ( 126# t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1) 127# ) 128# 129# where there is an index on t2(f2). The planner should use "t1" as the 130# outer loop. The inner loop, on "t2", is an OR optimization. One pass 131# for: 132# 133# t2.rowid = $1 134# 135# and another for: 136# 137# t2.f2=$1 AND $1!=-1 138# 139# the test is to ensure that on the second pass, the ($1!=-1) condition 140# is tested before any seek operations are performed - i.e. outside of 141# the loop through the f2=$1 range of the t2(f2) index. 142# 143reset_db 144do_execsql_test 5.0 { 145 CREATE TABLE t1(f1); 146 CREATE TABLE t2(f2); 147 CREATE INDEX t2f ON t2(f2); 148 149 INSERT INTO t1 VALUES(-1); 150 INSERT INTO t1 VALUES(-1); 151 INSERT INTO t1 VALUES(-1); 152 INSERT INTO t1 VALUES(-1); 153 154 WITH w(i) AS ( 155 SELECT 1 UNION ALL SELECT i+1 FROM w WHERE i<1000 156 ) 157 INSERT INTO t2 SELECT -1 FROM w; 158} 159 160do_execsql_test 5.1 { 161 SELECT count(*) FROM t1, t2 WHERE t2.rowid = +t1.rowid 162} {4} 163do_test 5.2 { expr [db status vmstep]<200 } 1 164 165do_execsql_test 5.3 { 166 SELECT count(*) FROM t1, t2 WHERE ( 167 t2.rowid = +t1.rowid OR t2.f2 = t1.f1 168 ) 169} {4000} 170do_test 5.4 { expr [db status vmstep]>1000 } 1 171 172do_execsql_test 5.5 { 173 SELECT count(*) FROM t1, t2 WHERE ( 174 t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1) 175 ) 176} {4} 177do_test 5.6 { expr [db status vmstep]<200 } 1 178 179# 2017-09-04 ticket b899b6042f97f52d 180# Segfault on correlated subquery... 181# 182ifcapable json1 { 183 do_execsql_test 6.1 { 184 CREATE TABLE t6(x); 185 SELECT * FROM t6 WHERE 1 IN (SELECT value FROM json_each(x)); 186 } {} 187 188 do_execsql_test 6.2 { 189 DROP TABLE t6; 190 CREATE TABLE t6(a,b,c); 191 INSERT INTO t6 VALUES 192 (0,null,'{"a":0,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}'), 193 (1,null,'{"a":1,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}'), 194 (2,null,'{"a":9,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}'); 195 SELECT * FROM t6 196 WHERE (EXISTS (SELECT 1 FROM json_each(t6.c) AS x WHERE x.value=1)); 197 } {1 {} {{"a":1,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}}} 198 199} 200 201finish_test 202