1 files changed, 214 insertions, 0 deletions
diff --git a/test/cursorhint.test b/test/cursorhint.test
new file mode 100644
index 0000000..d1bd4e8
--- /dev/null
+++ b/test/cursorhint.test
@@ -0,0 +1,214 @@
+# 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
+  }
+} {0 0}
+
+# 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
+  }
+} {0 0}
+
+# 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;
+  }
+} {2 0}
+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;
+  }
+} {0}
+
+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))}
+
+# 2023-03-24 https://sqlite.org/forum/forumpost/591006b1cc
+#
+reset_db
+do_execsql_test 5.0 {
+  CREATE TABLE t1(x TEXT PRIMARY KEY) WITHOUT ROWID;
+  CREATE VIEW t2 AS SELECT 0 FROM t1 WHERE x>='a' OR x='1';
+  SELECT * FROM t2 RIGHT JOIN t1 ON true;
+}
+# Additional test case from https://sqlite.org/forum/forumpost/d34ad68c36?t=c
+# which is a different way to acces the same problem.
+#
+do_execsql_test 5.1 {
+  CREATE TABLE  v1 (c1, PRIMARY KEY( c1 )) WITHOUT ROWID;
+  CREATE VIEW   v2 AS SELECT 0 FROM v1 WHERE c1 IS '' OR c1 > '';
+  CREATE VIEW   v3 AS SELECT 0 FROM v2 JOIN (v2 RIGHT JOIN v1);
+  CREATE VIEW   v4 AS SELECT 0 FROM v3, v3;
+  SELECT * FROM v3 JOIN v3 AS a0, v4 AS a1, v4 AS a2, v3 AS a3,
+                v3 AS a4, v4 AS a5
+    ORDER BY 1;
+}
+
+# 2023-04-10 https://sqlite.org/forum/forumpost/0b53708c95
+#
+do_execsql_test 6.0 {
+  CREATE TABLE t6(a TEXT UNIQUE, b TEXT);
+  INSERT INTO t6(a,b) VALUES('uvw','xyz'),('abc','def');
+  WITH v1(a) AS (SELECT a COLLATE NOCASE FROM t6)
+  SELECT v1.a, count(*) FROM t6 LEFT JOIN v1 ON true
+   GROUP BY 1
+  HAVING (SELECT true FROM t6 AS aa LEFT JOIN t6 AS bb ON length(v1.a)>5);
+} {abc 2 uvw 2}
+
+
+# 2023-05-04 https://sqlite.org/forum/forumpost/29a47cf6d1
+#
+# codeCursorHint() should not walk expressions that have been optimized
+# out and converted to TRUE or FALSE.  This only comes up when compiling
+# with SQLITE_ENABLE_CURSOR_HINTS
+#
+reset_db
+do_execsql_test 7.1 {
+  CREATE TABLE t1(a INT PRIMARY KEY) WITHOUT ROWID;
+  CREATE TABLE t2(b INT PRIMARY KEY) WITHOUT ROWID;
+  CREATE TABLE t3(c INT PRIMARY KEY) WITHOUT ROWID;
+  INSERT INTO t1(a) VALUES(1),(2);
+  INSERT INTO t2(b) VALUES(4),(8);
+  INSERT INTO t3(c) VALUES(16),(32);
+  CREATE VIEW v4(d) AS SELECT c FROM t3;
+  SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN v4 ON (d IS NULL);
+} {}
+
+
+finish_test