AryaWu/sqlite

1# 2014 July 152#3# The author disclaims copyright to this source code.  In place of4# 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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix cursorhint17 18ifcapable !cursorhints {19  finish_test20  return21}22 23do_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 $opcode36# opcodes.37#38proc 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 $res44}45 46# Run EXPLAIN on $sql.  Return a list of P5 values for all $opcode47# opcodes that contain regexp $comment in their comment48#49proc 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 $res57}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 that61# each t2 access has a cursor hint based on the current t1.a value.62#63do_test 1.1 {64  p4_of_opcode db CursorHint {65     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x66  }67} {{EQ(r[1],c0)}}68do_test 1.2 {69  p5_of_opcode db OpenRead {70     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x71  }72} {0 0}73 74# Do the same test the other way around.75#76do_test 2.1 {77  p4_of_opcode db CursorHint {78     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x79  }80} {{EQ(c0,r[1])}}81do_test 2.2 {82  p5_of_opcode db OpenRead {83     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x84  }85} {0 0}86 87# Various expressions captured by CursorHint88#89do_test 3.1 {90  p4_of_opcode db CursorHint {91    SELECT * FROM t1 WHERE a=15 AND c=22 AND rowid!=9892  }93} {AND(AND(EQ(c0,15),EQ(c2,22)),NE(rowid,98))}94do_test 3.2 {95  p4_of_opcode db CursorHint {96    SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=9897  }98} {AND(AND(LT(c1,15),GT(c2,22)),NE(c0,98))}99 100# Indexed queries101#102do_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} {}111do_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)}116do_test 4.2 {117  p5_of_opcode db OpenRead {118    SELECT * FROM t1 WHERE b>11;119  }120} {2 0}121do_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)}126do_test 4.3desc {127  p4_of_opcode db CursorHint {128    SELECT c FROM t1 WHERE b<11 ORDER BY b DESC;129  }130} {}131do_test 4.4 {132  p5_of_opcode db OpenRead {133    SELECT c FROM t1 WHERE b<11;134  }135} {0}136 137do_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)}142do_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 terms149# should be hinted.150#151do_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))}156do_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/591006b1cc163#164reset_db165do_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=c171# which is a different way to acces the same problem.172#173do_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 a5180    ORDER BY 1;181}182 183# 2023-04-10 https://sqlite.org/forum/forumpost/0b53708c95184#185do_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 true190   GROUP BY 1191  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/29a47cf6d1196#197# codeCursorHint() should not walk expressions that have been optimized198# out and converted to TRUE or FALSE.  This only comes up when compiling199# with SQLITE_ENABLE_CURSOR_HINTS200#201reset_db202do_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 214finish_test215