Debian Codegraph @

test/bloom1.test

raw
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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 source $testdir/lock_common.tcl
17 source $testdir/malloc_common.tcl
18
19 set 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 #
25 do_execsql_test 1.0 {
26 CREATE TABLE t1(a, b);
27 CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
28 }
29
30 do_execsql_test 1.1 {
31 INSERT INTO t1 VALUES('hello', 'world');
32 INSERT INTO t2 VALUES(14, 'fourteen');
33 }
34
35 do_execsql_test 1.2 {
36 ANALYZE sqlite_schema;
37 INSERT INTO sqlite_stat1 VALUES('t2','idx1','6 6');
38 ANALYZE sqlite_schema;
39 }
40
41 do_execsql_test 1.3 {
42 SELECT 'affinity!' FROM t1 CROSS JOIN t2 WHERE t2.c = '14';
43 } {affinity!}
44
45
46 reset_db
47 do_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
63 do_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
69 # 2023-02-05
70 # https://sqlite.org/forum/forumpost/56de336385
71 #
72 # Do not employ a Bloom filter if the table being filtered or any table
73 # wo the left of the table being filtered lacks STAT1 data, since we
74 # cannot make a good Bloom filter usefulness determination without STAT1
75 # data.
76 #
77 reset_db
78 do_execsql_test 2.0 {
79 CREATE TABLE objs(c INTEGER, s INTEGER, p INTEGER, o INTEGER);
80 CREATE UNIQUE INDEX objs_cspo ON objs(o,p,c,s);
81 ANALYZE;
82 DELETE FROM sqlite_stat1;
83 INSERT INTO sqlite_stat1 VALUES('objs','objs_cspo','520138 21 20 19 1');
84 ANALYZE sqlite_schema;
85 }
86 do_eqp_test 2.1 {
87 WITH RECURSIVE transit(x) AS (
88 SELECT s FROM objs WHERE p=9 AND o=32805
89 UNION
90 SELECT objs.s FROM objs, transit WHERE objs.p=9 AND objs.o=transit.x
91 )
92 SELECT x FROM transit;
93 } {
94 QUERY PLAN
95 |--CO-ROUTINE transit
96 | |--SETUP
97 | | `--SEARCH objs USING COVERING INDEX objs_cspo (o=? AND p=?)
98 | `--RECURSIVE STEP
99 | |--SCAN transit
100 | `--SEARCH objs USING COVERING INDEX objs_cspo (o=? AND p=?)
101 `--SCAN transit
102 }
103
104 # 2023-02-28
105 # https://sqlite.org/forum/forumpost/0846211821
106 #
107 # Bloom filter gives an incorrect result if the collating sequence is
108 # anything other than binary.
109 #
110 reset_db
111 do_execsql_test 3.1 {
112 CREATE TABLE t0(x TEXT COLLATE rtrim);
113 INSERT INTO t0(x) VALUES ('a'), ('b'), ('c');
114 CREATE VIEW v0(y) AS SELECT DISTINCT x FROM t0;
115 SELECT count(*) FROM t0, v0 WHERE x='b ';
116 } 3
117 do_eqp_test 3.2 {
118 SELECT count(*) FROM t0, v0 WHERE x='b ';
119 } {
120 QUERY PLAN
121 |--CO-ROUTINE v0
122 | |--SCAN t0
123 | `--USE TEMP B-TREE FOR DISTINCT
124 |--SCAN v0
125 `--SEARCH t0 USING AUTOMATIC PARTIAL COVERING INDEX (x=?)
126 }
127 # ^^^^^--- The key feature in the previous result is that no Bloom filter
128 # is used. In the following, a Bloom filter is used because the data type
129 # is INT instead of TEXT.
130 do_execsql_test 3.3 {
131 CREATE TABLE t1(x INT COLLATE rtrim);
132 INSERT INTO t1(x) VALUES ('a'), ('b'), ('c');
133 CREATE VIEW v1(y) AS SELECT DISTINCT x FROM t1;
134 SELECT count(*) FROM t1, v1 WHERE x='b ';
135 } 3
136 do_eqp_test 3.4 {
137 SELECT count(*) FROM t1, v1 WHERE x='b ';
138 } {
139 QUERY PLAN
140 |--CO-ROUTINE v1
141 | |--SCAN t1
142 | `--USE TEMP B-TREE FOR DISTINCT
143 |--SCAN v1
144 |--BLOOM FILTER ON t1 (x=?)
145 `--SEARCH t1 USING AUTOMATIC PARTIAL COVERING INDEX (x=?)
146 }
147
148 # 2023-03-14
149 # https://sqlite.org/forum/forumpost/d47a0e8e3a
150 # https://sqlite.org/forum/forumpost/2e427099d5
151 #
152 # Both reports are for the same problem - using a Bloom filter on an
153 # expression index can cause issues.
154 #
155 reset_db
156 do_execsql_test 4.1 {
157 CREATE TABLE t1(x TEXT, y INT, z TEXT);
158 INSERT INTO t1(rowid,x,y,z) VALUES(12,'aa','bb','aa');
159 CREATE INDEX i1x ON t1(1 IS true,z);
160 CREATE TABLE t0(x TEXT);
161 INSERT INTO t0(rowid,x) VALUES(4,'aa');
162 ANALYZE sqlite_schema;
163 INSERT INTO sqlite_stat1 VALUES('t0',NULL,'20');
164 INSERT INTO sqlite_stat1 VALUES('t1','i1x','18 18 2');
165 ANALYZE sqlite_schema;
166 }
167 do_execsql_test 4.2 {
168 SELECT * FROM t0 NATURAL JOIN t1 WHERE z=t1.x;
169 } {aa bb aa}
170 do_execsql_test 4.3 {
171 DROP TABLE t0;
172 CREATE TABLE t0(a TEXT);
173 INSERT INTO t0 VALUES ('xyz');
174 CREATE INDEX t0x ON t0(a IS FALSE) WHERE false;
175 DROP TABLE t1;
176 CREATE TABLE t1(b INT);
177 INSERT INTO t1 VALUES('aaa'),('bbb'),('ccc'),('ddd'),(NULL);
178 CREATE TABLE t2(c REAL);
179 INSERT INTO t2 VALUES(7);
180 ANALYZE;
181 CREATE INDEX t2x ON t2(true IN ());
182 }
183 do_execsql_test 4.4 {
184 SELECT * FROM t0 LEFT JOIN t1 LEFT JOIN t2 ON (b NOTNULL)==(c IN ()) WHERE c;
185 } {xyz {} 7.0}
186
187 reset_db
188 do_execsql_test 5.0 {
189 CREATE TABLE t1 (c1);
190 INSERT INTO t1 VALUES (101);
191 CREATE TABLE t2 ( x );
192 INSERT INTO t2 VALUES(404);
193 }
194
195 do_execsql_test 5.1 {
196 SELECT 'val' in (
197 select 'val' from ( select 'valueB' from t1 order by 1 )
198 union all
199 select 'val'
200 );
201 } {1}
202
203 do_execsql_test 5.2 {
204 select * from t2
205 where 'val' in (
206 select 'val' from ( select 'valueB' from t1 order by 1 )
207 union all
208 select 'val'
209 );
210 } {404}
211
212 do_execsql_test 5.3 {
213 SELECT subq_1.c_0 as c_0
214 FROM ( SELECT 0 as c_0) as subq_1
215 WHERE (subq_1.c_0) IN (
216 SELECT subq_2.c_0 as c_0
217 FROM (
218 SELECT 0 as c_0
219 FROM t1 as ref_1
220 WHERE (ref_1.c1) = (2)
221 ORDER BY c_0 desc
222 ) as subq_2
223 UNION ALL
224 SELECT 0 as c_0
225 );
226 } {0}
227
228 # 2025-04-30 https://sqlite.org/forum/forumpost/792a09cb3df9e69f
229 # A continuation of the above.
230 #
231 do_execsql_test 6.1 {
232 DROP TABLE IF EXISTS t1;
233 CREATE TABLE t1(a);
234 SELECT 111 IN (
235 SELECT 222 FROM (SELECT 333 ORDER BY 1)
236 UNION ALL
237 SELECT 444 FROM (SELECT 555 FROM t1 ORDER BY 1)
238 );
239 } 0
240
241 finish_test