# 2012 November 9 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Test cases for query planning decisions. # # The tests in this file demonstrate the behaviour of the query planner # in determining the order in which joined tables are scanned. # # Assume there are two tables being joined - t1 and t2. Each has a cost # if it is the outer loop, and a cost if it is the inner loop. As follows: # # t1(outer) - cost of scanning t1 as the outer loop. # t1(inner) - cost of scanning t1 as the inner loop. # t2(outer) - cost of scanning t2 as the outer loop. # t2(inner) - cost of scanning t2 as the inner loop. # # Depending on the order in which the planner nests the scans, the total # cost of the join query is one of: # # t1(outer) * t2(inner) # t2(outer) * t1(inner) # # The tests in this file attempt to verify that the planner nests joins in # the correct order when the following are true: # # + (t1(outer) * t2(inner)) > (t1(inner) * t2(outer) # + t1(outer) < t2(outer) # # In other words, when the best overall query plan has t2 as the outer loop, # but when the outer loop is considered independent of the inner, t1 is the # most efficient choice. # # In order to make them more predictable, automatic indexes are turned off for # the tests in this file. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix whereF do_execsql_test 1.0 { PRAGMA automatic_index = 0; CREATE TABLE t1(a, b, c); CREATE TABLE t2(d, e, f); CREATE UNIQUE INDEX i1 ON t1(a); CREATE UNIQUE INDEX i2 ON t2(d); } {} foreach {tn sql} { 1 "SELECT * FROM t1, t2 WHERE t1.a=t2.e AND t2.d? AND t2.d>t1.c AND t1.b=t2.e" 2 "SELECT * FROM t2, t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" 3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e" } { do_test 2.$tn { db eval "EXPLAIN QUERY PLAN $sql" } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} } do_execsql_test 3.0 { DROP TABLE t1; DROP TABLE t2; CREATE TABLE t1(a, b, c); CREATE TABLE t2(d, e, f); CREATE UNIQUE INDEX i1 ON t1(a, b); CREATE INDEX i2 ON t2(d); } {} foreach {tn sql} { 1 {SELECT t1.a, t1.b, t2.d, t2.e FROM t1, t2 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} 2 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2, t1 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} 3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)} } { do_test 3.$tn { db eval "EXPLAIN QUERY PLAN $sql" } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/} } do_execsql_test 4.0 { CREATE TABLE t4(a,b,c,d,e, PRIMARY KEY(a,b,c)); CREATE INDEX t4adc ON t4(a,d,c); CREATE UNIQUE INDEX t4aebc ON t4(a,e,b,c); EXPLAIN QUERY PLAN SELECT rowid FROM t4 WHERE a=? AND b=?; } {/a=. AND b=./} #------------------------------------------------------------------------- # Test the following case: # # ... FROM t1, t2 WHERE ( # t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1) # ) # # where there is an index on t2(f2). The planner should use "t1" as the # outer loop. The inner loop, on "t2", is an OR optimization. One pass # for: # # t2.rowid = $1 # # and another for: # # t2.f2=$1 AND $1!=-1 # # the test is to ensure that on the second pass, the ($1!=-1) condition # is tested before any seek operations are performed - i.e. outside of # the loop through the f2=$1 range of the t2(f2) index. # reset_db do_execsql_test 5.0 { CREATE TABLE t1(f1); CREATE TABLE t2(f2); CREATE INDEX t2f ON t2(f2); INSERT INTO t1 VALUES(-1); INSERT INTO t1 VALUES(-1); INSERT INTO t1 VALUES(-1); INSERT INTO t1 VALUES(-1); WITH w(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM w WHERE i<1000 ) INSERT INTO t2 SELECT -1 FROM w; } do_execsql_test 5.1 { SELECT count(*) FROM t1, t2 WHERE t2.rowid = +t1.rowid } {4} do_test 5.2 { expr [db status vmstep]<200 } 1 do_execsql_test 5.3 { SELECT count(*) FROM t1, t2 WHERE ( t2.rowid = +t1.rowid OR t2.f2 = t1.f1 ) } {4000} do_test 5.4 { expr [db status vmstep]>1000 } 1 do_execsql_test 5.5 { SELECT count(*) FROM t1, t2 WHERE ( t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1) ) } {4} do_test 5.6 { expr [db status vmstep]<200 } 1 # 2017-09-04 ticket b899b6042f97f52d # Segfault on correlated subquery... # ifcapable json1&&vtab { do_execsql_test 6.1 { CREATE TABLE t6(x); SELECT * FROM t6 WHERE 1 IN (SELECT value FROM json_each(x)); } {} do_execsql_test 6.2 { DROP TABLE t6; CREATE TABLE t6(a,b,c); INSERT INTO t6 VALUES (0,null,'{"a":0,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}'), (1,null,'{"a":1,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}'), (2,null,'{"a":9,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}'); SELECT * FROM t6 WHERE (EXISTS (SELECT 1 FROM json_each(t6.c) AS x WHERE x.value=1)); } {1 {} {{"a":1,"b":[3,4,5],"c":{"x":4.5,"y":7.8}}}} # Another test case derived from a posting by Wout Mertens on the # sqlite-users mailing list on 2017-10-04. do_execsql_test 6.3 { DROP TABLE IF EXISTS t; CREATE TABLE t(json JSON); SELECT * FROM t WHERE(EXISTS(SELECT 1 FROM json_each(t.json,"$.foo") j WHERE j.value = 'meep')); } {} do_execsql_test 6.4 { INSERT INTO t VALUES('{"xyzzy":null}'); INSERT INTO t VALUES('{"foo":"meep","other":12345}'); INSERT INTO t VALUES('{"foo":"bingo","alt":5.25}'); SELECT * FROM t WHERE(EXISTS(SELECT 1 FROM json_each(t.json,"$.foo") j WHERE j.value = 'meep')); } {{{"foo":"meep","other":12345}}} } # 2018-01-27 # Ticket https://sqlite.org/src/tktview/ec32177c99ccac2b180fd3ea2083 # Incorrect result when using the new OR clause factoring optimization # # This is the original test case as reported on the sqlite-users mailing # list # do_execsql_test 7.1 { DROP TABLE IF EXISTS cd; CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer ); CREATE INDEX cd_idx_genreid ON cd (genreid); INSERT INTO cd ( cdid, genreid ) VALUES ( 1, 1 ), ( 2, NULL ), ( 3, NULL ), ( 4, NULL ), ( 5, NULL ); SELECT cdid FROM cd me WHERE 2 > ( SELECT COUNT( * ) FROM cd rownum__emulation WHERE ( me.genreid IS NOT NULL AND rownum__emulation.genreid IS NULL ) OR ( me.genreid IS NOT NULL AND rownum__emulation.genreid IS NOT NULL AND rownum__emulation.genreid < me.genreid ) OR ( ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL AND rownum__emulation.genreid IS NULL ) ) AND rownum__emulation.cdid > me.cdid ) ); } {4 5} # Simplified test cases from the ticket # do_execsql_test 7.2 { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY, b); INSERT INTO t1(a,b) VALUES(1,1); CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb); INSERT INTO t2(aa,bb) VALUES(1,1),(2,NULL),(3,NULL); SELECT ( SELECT COUNT(*) FROM t2 WHERE ( t1.b IS NOT NULL AND t2.bb IS NULL ) OR ( t2.bb < t1.b ) OR ( t1.b IS t2.bb AND t2.aa > t1.a ) ) FROM t1; } {2} # The fix for ticket ec32177c99ccac2b180fd3ea2083 only makes a difference # in the output when there is a TERM_VNULL entry in the WhereClause array. # And TERM_VNULL entries are only generated when compiling with # SQLITE_ENABLE_STAT4. Nevertheless, it is correct that TERM_VIRTUAL terms # should not participate in the factoring optimization. In all cases other # than TERM_VNULL, participation is harmless, but it does consume a few # extra CPU cycles. # # The following test verifies that the TERM_VIRTUAL terms resulting from # a GLOB operator do not appear anywhere in the generated code. This # confirms that the problem is fixed, even on builds that omit STAT4. # do_execsql_test 7.3 { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT); INSERT INTO t1(a,b) VALUES(1,'abcxyz'); CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb TEXT); INSERT INTO t2(aa,bb) VALUES(1,'abc'),(2,'wxyz'),(3,'xyz'); CREATE INDEX t2bb ON t2(bb); EXPLAIN SELECT ( SELECT COUNT(*) FROM t2 WHERE ( t1.b GLOB 'a*z' AND t2.bb='xyz' ) OR ( t2.bb = t1.b ) OR ( t2.aa = t1.a ) ) FROM t1; } {~/ (Lt|Ge) /} finish_test