Index: third_party/sqlite/sqlite-src-3170000/test/cursorhint.test |
diff --git a/third_party/sqlite/sqlite-src-3170000/test/cursorhint.test b/third_party/sqlite/sqlite-src-3170000/test/cursorhint.test |
new file mode 100644 |
index 0000000000000000000000000000000000000000..ae86120fa8912ed18aaac704e709c45d7708ef98 |
--- /dev/null |
+++ b/third_party/sqlite/sqlite-src-3170000/test/cursorhint.test |
@@ -0,0 +1,162 @@ |
+# 2014 July 15 |
+# |
+# 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. |
+# |
+#*********************************************************************** |
+# This file implements regression tests for SQLite library. |
+# |
+ |
+set testdir [file dirname $argv0] |
+source $testdir/tester.tcl |
+set testprefix cursorhint |
+ |
+ifcapable !cursorhints { |
+ finish_test |
+ return |
+} |
+ |
+do_execsql_test 1.0 { |
+ CREATE TABLE t1(a,b,c,d); |
+ CREATE TABLE t2(x,y,z); |
+ INSERT INTO t1(a,b) VALUES(10, 15); |
+ INSERT INTO t1(a,b) VALUES(20, 25); |
+ INSERT INTO t2(x,y) VALUES('ten', 'fifteen'); |
+ INSERT INTO t2(x,y) VALUES('twenty', 'twentyfive'); |
+ CREATE TABLE t3(id TEXT PRIMARY KEY, a, b, c, d) WITHOUT ROWID; |
+ INSERT INTO t3(id,a,b,c,d) SELECT rowid, a, b, c, d FROM t1; |
+ PRAGMA automatic_index = 0; |
+} |
+ |
+# Run EXPLAIN on $sql. Return a list of P4 values for all $opcode |
+# opcodes. |
+# |
+proc p4_of_opcode {db opcode sql} { |
+ set res {} |
+ $db eval "EXPLAIN $sql" x { |
+ if {$x(opcode)==$opcode} {lappend res $x(p4)} |
+ } |
+ return $res |
+} |
+ |
+# Run EXPLAIN on $sql. Return a list of P5 values for all $opcode |
+# opcodes that contain regexp $comment in their comment |
+# |
+proc p5_of_opcode {db opcode sql} { |
+ set res {} |
+ $db eval "EXPLAIN $sql" x { |
+ if {$x(opcode)==$opcode} { |
+ lappend res $x(p5) |
+ } |
+ } |
+ return $res |
+} |
+ |
+# Verify that when t1 is in the outer loop and t2 is in the inner loop, |
+# no cursor hints occur for t1 (since it is a full table scan) but that |
+# each t2 access has a cursor hint based on the current t1.a value. |
+# |
+do_test 1.1 { |
+ p4_of_opcode db CursorHint { |
+ SELECT * FROM t1 CROSS JOIN t2 WHERE a=x |
+ } |
+} {{EQ(r[1],c0)}} |
+do_test 1.2 { |
+ p5_of_opcode db OpenRead { |
+ SELECT * FROM t1 CROSS JOIN t2 WHERE a=x |
+ } |
+} {00 00} |
+ |
+# Do the same test the other way around. |
+# |
+do_test 2.1 { |
+ p4_of_opcode db CursorHint { |
+ SELECT * FROM t2 CROSS JOIN t1 WHERE a=x |
+ } |
+} {{EQ(c0,r[1])}} |
+do_test 2.2 { |
+ p5_of_opcode db OpenRead { |
+ SELECT * FROM t2 CROSS JOIN t1 WHERE a=x |
+ } |
+} {00 00} |
+ |
+# Various expressions captured by CursorHint |
+# |
+do_test 3.1 { |
+ p4_of_opcode db CursorHint { |
+ SELECT * FROM t1 WHERE a=15 AND c=22 AND rowid!=98 |
+ } |
+} {AND(AND(EQ(c0,15),EQ(c2,22)),NE(rowid,98))} |
+do_test 3.2 { |
+ p4_of_opcode db CursorHint { |
+ SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=98 |
+ } |
+} {AND(AND(LT(c1,15),GT(c2,22)),NE(c0,98))} |
+ |
+# Indexed queries |
+# |
+do_test 4.1asc { |
+ db eval { |
+ CREATE INDEX t1bc ON t1(b,c); |
+ CREATE INDEX t2yz ON t2(y,z); |
+ } |
+ p4_of_opcode db CursorHint { |
+ SELECT * FROM t1 WHERE b>11 ORDER BY b ASC; |
+ } |
+} {} |
+do_test 4.1desc { |
+ p4_of_opcode db CursorHint { |
+ SELECT * FROM t1 WHERE b>11 ORDER BY b DESC; |
+ } |
+} {GT(c0,11)} |
+do_test 4.2 { |
+ p5_of_opcode db OpenRead { |
+ SELECT * FROM t1 WHERE b>11; |
+ } |
+} {02 00} |
+do_test 4.3asc { |
+ p4_of_opcode db CursorHint { |
+ SELECT c FROM t1 WHERE b<11 ORDER BY b ASC; |
+ } |
+} {LT(c0,11)} |
+do_test 4.3desc { |
+ p4_of_opcode db CursorHint { |
+ SELECT c FROM t1 WHERE b<11 ORDER BY b DESC; |
+ } |
+} {} |
+do_test 4.4 { |
+ p5_of_opcode db OpenRead { |
+ SELECT c FROM t1 WHERE b<11; |
+ } |
+} {00} |
+ |
+do_test 4.5asc { |
+ p4_of_opcode db CursorHint { |
+ SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b ASC; |
+ } |
+} {LE(c0,20)} |
+do_test 4.5desc { |
+ p4_of_opcode db CursorHint { |
+ SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b DESC; |
+ } |
+} {GE(c0,10)} |
+ |
+# If there are any equality terms used in the constraint, then all terms |
+# should be hinted. |
+# |
+do_test 4.6asc { |
+ p4_of_opcode db CursorHint { |
+ SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c ASC; |
+ } |
+} {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))} |
+do_test 4.6desc { |
+ p4_of_opcode db CursorHint { |
+ SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c DESC; |
+ } |
+} {AND(AND(EQ(c0,22),GE(c1,10)),LE(c1,20))} |
+ |
+finish_test |