1# 2022 October 06 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# 12# Tests for queries that use bloom filters 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16source $testdir/lock_common.tcl 17source $testdir/malloc_common.tcl 18 19set testprefix bloom1 20 21# Tests 1.* verify that the bloom filter code correctly handles the 22# case where the RHS of an (<ipk-column> = ?) expression must be coerced 23# to an integer before the comparison made. 24# 25do_execsql_test 1.0 { 26 CREATE TABLE t1(a, b); 27 CREATE TABLE t2(c INTEGER PRIMARY KEY, d); 28} 29 30do_execsql_test 1.1 { 31 INSERT INTO t1 VALUES('hello', 'world'); 32 INSERT INTO t2 VALUES(14, 'fourteen'); 33} 34 35do_execsql_test 1.2 { 36 ANALYZE sqlite_schema; 37 INSERT INTO sqlite_stat1 VALUES('t2','idx1','6 6'); 38 ANALYZE sqlite_schema; 39} 40 41do_execsql_test 1.3 { 42 SELECT 'affinity!' FROM t1 CROSS JOIN t2 WHERE t2.c = '14'; 43} {affinity!} 44 45 46reset_db 47do_execsql_test 1.4 { 48 CREATE TABLE t1(a, b TEXT); 49 CREATE TABLE t2(c INTEGER PRIMARY KEY, d); 50 CREATE TABLE t3(e INTEGER PRIMARY KEY, f); 51 52 ANALYZE sqlite_schema; 53 INSERT INTO sqlite_stat1 VALUES('t1','idx1','600 6'); 54 INSERT INTO sqlite_stat1 VALUES('t2','idx1','6 6'); 55 INSERT INTO sqlite_stat1 VALUES('t3','idx2','6 6'); 56 ANALYZE sqlite_schema; 57 58 INSERT INTO t1 VALUES(1, '123'); 59 INSERT INTO t2 VALUES(123, 'one'); 60 INSERT INTO t3 VALUES(123, 'two'); 61} 62 63do_execsql_test 1.5 { 64 SELECT 'result' FROM t1, t2, t3 65 WHERE t2.c=t1.b AND t2.d!='silly' 66 AND t3.e=t1.b AND t3.f!='silly' 67} {result} 68 69finish_test 70 71