test/cursorhint.test

   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 # 2023-03-24 https://sqlite.org/forum/forumpost/591006b1cc
 163 #
 164 reset_db
 165 do_execsql_test 5.0 {
 166   CREATE TABLE t1(x TEXT PRIMARY KEY) WITHOUT ROWID;
 167   CREATE VIEW t2 AS SELECT 0 FROM t1 WHERE x>='a' OR x='1';
 168   SELECT * FROM t2 RIGHT JOIN t1 ON true;
 169 }
 170 # Additional test case from https://sqlite.org/forum/forumpost/d34ad68c36?t=c
 171 # which is a different way to acces the same problem.
 172 #
 173 do_execsql_test 5.1 {
 174   CREATE TABLE  v1 (c1, PRIMARY KEY( c1 )) WITHOUT ROWID;
 175   CREATE VIEW   v2 AS SELECT 0 FROM v1 WHERE c1 IS '' OR c1 > '';
 176   CREATE VIEW   v3 AS SELECT 0 FROM v2 JOIN (v2 RIGHT JOIN v1);
 177   CREATE VIEW   v4 AS SELECT 0 FROM v3, v3;
 178   SELECT * FROM v3 JOIN v3 AS a0, v4 AS a1, v4 AS a2, v3 AS a3,
 179                 v3 AS a4, v4 AS a5
 180     ORDER BY 1;
 181 }
 182
 183 # 2023-04-10 https://sqlite.org/forum/forumpost/0b53708c95
 184 #
 185 do_execsql_test 6.0 {
 186   CREATE TABLE t6(a TEXT UNIQUE, b TEXT);
 187   INSERT INTO t6(a,b) VALUES('uvw','xyz'),('abc','def');
 188   WITH v1(a) AS (SELECT a COLLATE NOCASE FROM t6)
 189   SELECT v1.a, count(*) FROM t6 LEFT JOIN v1 ON true
 190    GROUP BY 1
 191   HAVING (SELECT true FROM t6 AS aa LEFT JOIN t6 AS bb ON length(v1.a)>5);
 192 } {abc 2 uvw 2}
 193
 194
 195 # 2023-05-04 https://sqlite.org/forum/forumpost/29a47cf6d1
 196 #
 197 # codeCursorHint() should not walk expressions that have been optimized
 198 # out and converted to TRUE or FALSE.  This only comes up when compiling
 199 # with SQLITE_ENABLE_CURSOR_HINTS
 200 #
 201 reset_db
 202 do_execsql_test 7.1 {
 203   CREATE TABLE t1(a INT PRIMARY KEY) WITHOUT ROWID;
 204   CREATE TABLE t2(b INT PRIMARY KEY) WITHOUT ROWID;
 205   CREATE TABLE t3(c INT PRIMARY KEY) WITHOUT ROWID;
 206   INSERT INTO t1(a) VALUES(1),(2);
 207   INSERT INTO t2(b) VALUES(4),(8);
 208   INSERT INTO t3(c) VALUES(16),(32);
 209   CREATE VIEW v4(d) AS SELECT c FROM t3;
 210   SELECT * FROM t1 RIGHT JOIN t2 ON true JOIN v4 ON (d IS NULL);
 211 } {}
 212
 213
 214 finish_test