| OLD | NEW |
|---|
| (Empty) |
| 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 } {00 00} | |
| 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 } {00 00} | |
| 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 } {02 00} | |
| 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 } {00} | |
| 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 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
