[sql] Import reference version of SQLite 3.17..

third_party/sqlite/sqlite-src-3170000/test/cursorhint.test

+# 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