gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/cursorhint.test (about) 1 # 2014 July 15 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. 12 # 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix cursorhint 17 18 ifcapable !cursorhints { 19 finish_test 20 return 21 } 22 23 do_execsql_test 1.0 { 24 CREATE TABLE t1(a,b,c,d); 25 CREATE TABLE t2(x,y,z); 26 INSERT INTO t1(a,b) VALUES(10, 15); 27 INSERT INTO t1(a,b) VALUES(20, 25); 28 INSERT INTO t2(x,y) VALUES('ten', 'fifteen'); 29 INSERT INTO t2(x,y) VALUES('twenty', 'twentyfive'); 30 CREATE TABLE t3(id TEXT PRIMARY KEY, a, b, c, d) WITHOUT ROWID; 31 INSERT INTO t3(id,a,b,c,d) SELECT rowid, a, b, c, d FROM t1; 32 PRAGMA automatic_index = 0; 33 } 34 35 # Run EXPLAIN on $sql. Return a list of P4 values for all $opcode 36 # opcodes. 37 # 38 proc p4_of_opcode {db opcode sql} { 39 set res {} 40 $db eval "EXPLAIN $sql" x { 41 if {$x(opcode)==$opcode} {lappend res $x(p4)} 42 } 43 return $res 44 } 45 46 # Run EXPLAIN on $sql. Return a list of P5 values for all $opcode 47 # opcodes that contain regexp $comment in their comment 48 # 49 proc p5_of_opcode {db opcode sql} { 50 set res {} 51 $db eval "EXPLAIN $sql" x { 52 if {$x(opcode)==$opcode} { 53 lappend res $x(p5) 54 } 55 } 56 return $res 57 } 58 59 # Verify that when t1 is in the outer loop and t2 is in the inner loop, 60 # no cursor hints occur for t1 (since it is a full table scan) but that 61 # each t2 access has a cursor hint based on the current t1.a value. 62 # 63 do_test 1.1 { 64 p4_of_opcode db CursorHint { 65 SELECT * FROM t1 CROSS JOIN t2 WHERE a=x 66 } 67 } {{EQ(r[1],c0)}} 68 do_test 1.2 { 69 p5_of_opcode db OpenRead { 70 SELECT * FROM t1 CROSS JOIN t2 WHERE a=x 71 } 72 } {0 0} 73 74 # Do the same test the other way around. 75 # 76 do_test 2.1 { 77 p4_of_opcode db CursorHint { 78 SELECT * FROM t2 CROSS JOIN t1 WHERE a=x 79 } 80 } {{EQ(c0,r[1])}} 81 do_test 2.2 { 82 p5_of_opcode db OpenRead { 83 SELECT * FROM t2 CROSS JOIN t1 WHERE a=x 84 } 85 } {0 0} 86 87 # Various expressions captured by CursorHint 88 # 89 do_test 3.1 { 90 p4_of_opcode db CursorHint { 91 SELECT * FROM t1 WHERE a=15 AND c=22 AND rowid!=98 92 } 93 } {AND(AND(EQ(c0,15),EQ(c2,22)),NE(rowid,98))} 94 do_test 3.2 { 95 p4_of_opcode db CursorHint { 96 SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=98 97 } 98 } {AND(AND(LT(c1,15),GT(c2,22)),NE(c0,98))} 99 100 # Indexed queries 101 # 102 do_test 4.1asc { 103 db eval { 104 CREATE INDEX t1bc ON t1(b,c); 105 CREATE INDEX t2yz ON t2(y,z); 106 } 107 p4_of_opcode db CursorHint { 108 SELECT * FROM t1 WHERE b>11 ORDER BY b ASC; 109 } 110 } {} 111 do_test 4.1desc { 112 p4_of_opcode db CursorHint { 113 SELECT * FROM t1 WHERE b>11 ORDER BY b DESC; 114 } 115 } {GT(c0,11)} 116 do_test 4.2 { 117 p5_of_opcode db OpenRead { 118 SELECT * FROM t1 WHERE b>11; 119 } 120 } {2 0} 121 do_test 4.3asc { 122 p4_of_opcode db CursorHint { 123 SELECT c FROM t1 WHERE b<11 ORDER BY b ASC; 124 } 125 } {LT(c0,11)} 126 do_test 4.3desc { 127 p4_of_opcode db CursorHint { 128 SELECT c FROM t1 WHERE b<11 ORDER BY b DESC; 129 } 130 } {} 131 do_test 4.4 { 132 p5_of_opcode db OpenRead { 133 SELECT c FROM t1 WHERE b<11; 134 } 135 } {0} 136 137 do_test 4.5asc { 138 p4_of_opcode db CursorHint { 139 SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b ASC; 140 } 141 } {LE(c0,20)} 142 do_test 4.5desc { 143 p4_of_opcode db CursorHint { 144 SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b DESC; 145 } 146 } {GE(c0,10)} 147 148 # If there are any equality terms used in the constraint, then all terms 149 # should be hinted. 150 # 151 do_test 4.6asc { 152 p4_of_opcode db CursorHint { 153 SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c ASC; 154 } 155 } {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))} 156 do_test 4.6desc { 157 p4_of_opcode db CursorHint { 158 SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c DESC; 159 } 160 } {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))} 161 162 finish_test