# 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 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 comment sql} { set res {} $db eval "EXPLAIN $sql" x { if {$x(opcode)==$opcode && [regexp $comment $x(comment)]} { 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.1 { 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; } } {GT(c0,11)} do_test 4.2 { p5_of_opcode db OpenRead . { SELECT * FROM t1 WHERE b>11; } } {02 00} do_test 4.3 { p4_of_opcode db CursorHint { SELECT c FROM t1 WHERE b>11; } } {GT(c0,11)} do_test 4.4 { p5_of_opcode db OpenRead . { SELECT c FROM t1 WHERE b>11; } } {00} finish_test