From 63847496f14c813a5d80efd5b7de0f1294ffe1e3 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 16:07:11 +0200 Subject: Adding upstream version 3.45.1. Signed-off-by: Daniel Baumann --- test/cursorhint2.test | 204 ++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 204 insertions(+) create mode 100644 test/cursorhint2.test (limited to 'test/cursorhint2.test') diff --git a/test/cursorhint2.test b/test/cursorhint2.test new file mode 100644 index 0000000..a78d151 --- /dev/null +++ b/test/cursorhint2.test @@ -0,0 +1,204 @@ +# 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])} +} + +reset_db +do_execsql_test 3.0 { + CREATE TABLE t1 (i1 TEXT); + CREATE TABLE t2 (i2 TEXT UNIQUE); + INSERT INTO t1 VALUES('0'); + INSERT INTO t2 VALUES('0'); +} + +do_extract_hints_test 3.1 { + SELECT * FROM t1 CROSS JOIN t2 WHERE (t1.i1 = t2.i2) AND t2.i2 = 1; +} { + t1 {EQ(c0,r[1])} t2 EQ(c0,1) +} + + +finish_test -- cgit v1.2.3