# 2016 June 17 # # 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. The # focus is on testing that cursor-hints are correct for queries # involving LEFT JOIN. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix cursorhint2 ifcapable !cursorhints { finish_test return } proc extract_hints {sql} { db eval "SELECT tbl_name, rootpage FROM sqlite_master where rootpage" { set lookup($rootpage) $tbl_name } set ret [list] db eval "EXPLAIN $sql" a { switch -- $a(opcode) { OpenRead { set csr($a(p1)) $lookup($a(p2)) } CursorHint { lappend ret $csr($a(p1)) $a(p4) } } } set ret } proc do_extract_hints_test {tn sql ret} { uplevel [list do_test $tn [list extract_hints $sql] [list {*}$ret]] } do_execsql_test 1.0 { PRAGMA automatic_index = 0; CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); CREATE TABLE t3(e, f); } do_extract_hints_test 1.1 { SELECT * FROM t1 WHERE a=1; } { t1 EQ(c0,1) } do_extract_hints_test 1.2 { SELECT * FROM t1 CROSS JOIN t2 ON (a=c) WHERE d IS NULL; } { t2 {AND(ISNULL(c1),EQ(r[1],c0))} } do_extract_hints_test 1.3 { SELECT * FROM t1 LEFT JOIN t2 ON (a=c) WHERE d IS NULL; } { t2 {EQ(r[2],c0)} } do_extract_hints_test 1.4 { SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND a=10) WHERE d IS NULL; } { t2 {AND(EQ(r[2],c0),EQ(r[3],10))} } do_extract_hints_test 1.5 { SELECT * FROM t1 CROSS JOIN t2 ON (a=c AND a=10) WHERE d IS NULL; } { t1 EQ(c0,10) t2 {AND(ISNULL(c1),EQ(r[3],c0))} } do_extract_hints_test 1.6 { SELECT * FROM t1 LEFT JOIN t2 ON (a=c) LEFT JOIN t3 ON (d=f); } { t2 {EQ(r[2],c0)} t3 {EQ(r[6],c1)} } if 0 { do_extract_hints_test 1.7 { SELECT * FROM t1 LEFT JOIN t2 ON (a=c AND d=e) LEFT JOIN t3 ON (d=f); } { t2 {EQ(r[2],c0)} t3 {AND(EQ(r[6],c0),EQ(r[7],c1))} } } #------------------------------------------------------------------------- # do_execsql_test 2.0 { CREATE TABLE x1(x, y); CREATE TABLE x2(a, b); } do_extract_hints_test 2.1 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NULL; } { x2 {EQ(c0,r[2])} } do_extract_hints_test 2.2 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS +NULL; } { x2 {EQ(c0,r[2])} } do_extract_hints_test 2.3 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = (b IS NULL) } { x2 {EQ(c0,r[2])} } do_extract_hints_test 2.4 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1) } { x2 {EQ(c0,r[2])} } do_extract_hints_test 2.5 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 1 = coalesce(b, 1) } { x2 {EQ(c0,r[2])} } if {0} { # These tests no longer work due to the LEFT-JOIN strength reduction # optimization do_extract_hints_test 2.6 { SELECT * FROM x1 CROSS JOIN x2 ON (a=x) WHERE 0 = (b IS NOT NULL) } { x2 {EQ(c0,r[2])} } do_extract_hints_test 2.7 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE 0 = (b IS NOT +NULL) } { x2 {EQ(c0,r[2])} } do_extract_hints_test 2.8 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE b IS NOT +NULL } { x2 {EQ(c0,r[2])} } do_extract_hints_test 2.9 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE CASE b WHEN 0 THEN 0 ELSE 1 END; } { x2 {EQ(c0,r[2])} } do_extract_hints_test 2.10 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b = 32+32 } { x2 {AND(EQ(c1,ADD(32,32)),EQ(c0,r[2]))} } ifcapable !icu { # This test only works using the built-in LIKE, not the ICU LIKE extension. do_extract_hints_test 2.11 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE x2.b LIKE 'abc%' } { x2 {AND(expr,EQ(c0,r[2]))} } } } do_extract_hints_test 2.12 { SELECT * FROM x1 LEFT JOIN x2 ON (a=x) WHERE coalesce(x2.b, 1) } { x2 {EQ(c0,r[2])} } finish_test