1# 2016 June 17 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# This file implements regression tests for SQLite library. The 12# focus is on testing that cursor-hints are correct for queries 13# involving LEFT JOIN. 14# 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set ::testprefix cursorhint2 20 21ifcapable !cursorhints { 22 finish_test 23 return 24} 25 26proc extract_hints {sql} { 27 28 db eval "SELECT tbl_name, rootpage FROM sqlite_master where rootpage" { 29 set lookup($rootpage) $tbl_name 30 } 31 32 set ret [list] 33 db eval "EXPLAIN $sql" a { 34 switch -- $a(opcode) { 35 OpenRead { 36 set csr($a(p1)) $lookup($a(p2)) 37 } 38 CursorHint { 39 lappend ret $csr($a(p1)) $a(p4) 40 } 41 } 42 } 43 44 set ret 45} 46 47proc do_extract_hints_test {tn sql ret} { 48 uplevel [list do_test $tn [list extract_hints $sql] [list {*}$ret]] 49} 50 51do_execsql_test 1.0 { 52 PRAGMA automatic_index = 0; 53 CREATE TABLE t1(a, b); 54 CREATE TABLE t2(c, d); 55 CREATE TABLE t3(e, f); 56} 57 58do_extract_hints_test 1.1 { 59 SELECT * FROM t1 WHERE a=1; 60} { 61 t1 EQ(c0,1) 62} 63 64do_extract_hints_test 1.2 { 65 SELECT * FROM t1 CROSS JOIN t2 ON (a=c) WHERE d IS NULL; 66} { 67 t2 {AND(ISNULL(c1),EQ(r[1],c0))} 68} 69 70do_extract_hints_test 1.3 { 71 SELECT * FROM t1 LEFT JOIN t2 ON (a=c) WHERE d IS NULL; 72} { 73 t2 {EQ(r[2],c0)} 74} 75 76do_extract_hints_test 1.4 { 77 SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND a=10) WHERE d IS NULL; 78} { 79 t2 {AND(EQ(r[2],c0),EQ(r[3],10))} 80} 81 82do_extract_hints_test 1.5 { 83 SELECT * FROM t1 CROSS JOIN t2 ON (a=c AND a=10) WHERE d IS NULL; 84} { 85 t1 EQ(c0,10) t2 {AND(ISNULL(c1),EQ(r[3],c0))} 86} 87 88do_extract_hints_test 1.6 { 89 SELECT * FROM t1 LEFT JOIN t2 ON (a=c) LEFT JOIN t3 ON (d=f); 90} { 91 t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)} 92} 93 94do_extract_hints_test 1.7 { 95 SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND d=e) LEFT JOIN t3 ON (d=f); 96} { 97 t2 {EQ(r[2],c0)} t3 {AND(EQ(r[6],c0),EQ(r[7],c1))} 98} 99 100#------------------------------------------------------------------------- 101# 102do_execsql_test 2.0 { 103 CREATE TABLE x1(x, y); 104 CREATE TABLE x2(a, b); 105} 106 107do_extract_hints_test 2.1 { 108 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NULL; 109} { 110 x2 {EQ(c0,r[2])} 111} 112 113do_extract_hints_test 2.2 { 114 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS +NULL; 115} { 116 x2 {EQ(c0,r[2])} 117} 118 119do_extract_hints_test 2.3 { 120 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = (b IS NULL) 121} { 122 x2 {EQ(c0,r[2])} 123} 124 125do_extract_hints_test 2.4 { 126 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1) 127} { 128 x2 {EQ(c0,r[2])} 129} 130 131do_extract_hints_test 2.5 { 132 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1) 133} { 134 x2 {EQ(c0,r[2])} 135} 136 137do_extract_hints_test 2.6 { 138 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL) 139} { 140 x2 {EQ(c0,r[2])} 141} 142 143do_extract_hints_test 2.7 { 144 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL) 145} { 146 x2 {EQ(c0,r[2])} 147} 148 149do_extract_hints_test 2.8 { 150 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL 151} { 152 x2 {EQ(c0,r[2])} 153} 154 155do_extract_hints_test 2.9 { 156 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE CASE b WHEN 0 THEN 0 ELSE 1 END; 157} { 158 x2 {EQ(c0,r[2])} 159} 160 161do_extract_hints_test 2.10 { 162 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32 163} { 164 x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))} 165} 166 167do_extract_hints_test 2.11 { 168 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%' 169} { 170 x2 {AND(expr,EQ(c0,r[2]))} 171} 172 173do_extract_hints_test 2.11 { 174 SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1) 175} { 176 x2 {EQ(c0,r[2])} 177} 178 179finish_test 180 181