Debian Codegraph @

test/expridx1.test

raw
1 # 2026-03-16
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 # This file contains test cases for handling stale expression indexes -
13 # expression indexes for which the value of the expression is different
14 # from (though usually very close to) the value stored on disk.
15 #
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set testprefix expridx1
19
20 # Check that OP_IdxDelete works when:
21 #
22 # 1.1.* The real value in the index is greater than that in the table,
23 # 1.2.* The real value in the index is smaller than that in the table,
24 # 1.3.* Duplicate distorted real values.
25 #
26
27 do_execsql_test 1.0.1 {
28 CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL);
29 INSERT INTO t1 VALUES(10, 10.0);
30 INSERT INTO t1 VALUES(15, 15.0);
31 INSERT INTO t1 VALUES(20, 20.0);
32 INSERT INTO t1 VALUES(25, 25.0);
33 INSERT INTO t1 VALUES(30, 30.0);
34 CREATE INDEX i1 ON t1((b+0.0));
35 }
36
37 # Return a list of rowids from table t1 for which there is no exact
38 # match in the index.
39 set idxcheck {
40 SELECT rowid FROM t1 AS o NOT INDEXED
41 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE +a=o.a AND b+0.0=o.b+0.0)
42 }
43
44 set root [db one {SELECT rootpage FROM sqlite_schema WHERE name='i1'}]
45
46 do_test 1.0.2 {
47 # Create an imposter table for index i1
48 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $root
49 db eval {
50 CREATE TABLE x1(b, rowid, PRIMARY KEY(b, rowid)) WITHOUT ROWID;
51 }
52 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
53
54 db one { PRAGMA integrity_check }
55 } ok
56
57 do_execsql_test 1.1.1 {
58 UPDATE x1 SET b=21.0 WHERE rowid=20;
59 }
60 do_execsql_test 1.1.1b {
61 PRAGMA integrity_check;
62 } {{row 3 missing from index i1}}
63 do_execsql_test 1.1.1c $idxcheck {20}
64
65 do_execsql_test 1.1.2 {
66 DELETE FROM t1 WHERE a=20;
67 } {}
68 do_execsql_test 1.1.3 {
69 PRAGMA integrity_check;
70 } {ok}
71 do_execsql_test 1.1.4 $idxcheck {}
72
73 do_execsql_test 1.2.1 {
74 UPDATE x1 SET b=26.0 WHERE rowid=25;
75 PRAGMA integrity_check;
76 } {
77 {row 3 missing from index i1}
78 }
79 do_execsql_test 1.2.2 $idxcheck {25}
80
81 do_execsql_test 1.2.3 {
82 DELETE FROM t1 WHERE a=25;
83 } {}
84 do_execsql_test 1.2.4 {
85 PRAGMA integrity_check;
86 } {ok}
87 do_execsql_test 1.2.5 $idxcheck {}
88
89 do_execsql_test 1.3.1 {
90 DELETE FROM t1;
91 INSERT INTO t1 VALUES(5, 15.0);
92 INSERT INTO t1 VALUES(10, 20.0);
93 INSERT INTO t1 VALUES(15, 20.0);
94 INSERT INTO t1 VALUES(20, 20.0);
95 INSERT INTO t1 VALUES(25, 20.0);
96 INSERT INTO t1 VALUES(30, 20.0);
97 INSERT INTO t1 VALUES(35, 25.0);
98
99 UPDATE x1 SET b=19.0 WHERE b=20.0;
100 PRAGMA integrity_check;
101 } {
102 {row 2 missing from index i1}
103 {row 3 missing from index i1}
104 {row 4 missing from index i1}
105 {row 5 missing from index i1}
106 {row 6 missing from index i1}
107 }
108
109 do_execsql_test 1.3.2 $idxcheck {10 15 20 25 30}
110
111 foreach {tn a} {
112 1 15 2 30 3 20 4 10 5 25
113 } {
114 do_execsql_test 1.3.3.$tn {
115 DELETE FROM t1 WHERE a=$a
116 }
117 }
118 do_execsql_test 1.3.4 {
119 PRAGMA integrity_check
120 } {ok}
121 do_execsql_test 1.3.5 $idxcheck {}
122
123 #-------------------------------------------------------------------------
124 reset_db
125 set nRow [expr 1000]
126 do_execsql_test 2.0 {
127 CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
128 WITH s(i) AS (
129 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<$nRow
130 )
131 INSERT INTO t1 SELECT i, random(), hex(randomblob(50)) FROM s;
132 CREATE INDEX t1c ON t1(+c);
133 }
134
135 set idxcheck {
136 SELECT a, b FROM t1 AS o NOT INDEXED
137 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE +a=o.a AND +b=o.b AND +c=o.c)
138 }
139
140 set root [db one {SELECT rootpage FROM sqlite_schema WHERE name='t1c'}]
141
142 do_test 2.1 {
143 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $root
144 db eval {
145 CREATE TABLE x1(a, b, c, PRIMARY KEY(c, a, b)) WITHOUT ROWID;
146 }
147 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
148 } {}
149
150 do_execsql_test 2.2 {
151 UPDATE x1 SET c=hex(randomblob(50)) WHERE (a%2)!=0
152 }
153
154 do_execsql_test 2.3 "
155 SELECT count(*) FROM ( $idxcheck )
156 " [expr $nRow/2]
157
158 do_test 2.4 {
159 for {set ii 1} {$ii<$nRow} {incr ii 2} {
160 execsql { DELETE FROM t1 WHERE a=$ii }
161 }
162 execsql {PRAGMA integrity_check}
163 } {ok}
164
165 do_execsql_test 2.5 "
166 SELECT count(*) FROM ( $idxcheck )
167 " 0
168
169 #-------------------------------------------------------------------------
170 reset_db
171 do_execsql_test 3.0 {
172 CREATE TABLE y1(a, b, c GENERATED ALWAYS AS (a*b) VIRTUAL);
173 CREATE INDEX i1 ON y1(c);
174 INSERT INTO y1 VALUES(2, 3);
175 INSERT INTO y1 VALUES(4, 5);
176 }
177
178 set idxcheck {
179 SELECT rowid FROM y1 AS o NOT INDEXED
180 WHERE NOT EXISTS (SELECT 1 FROM y1 WHERE +rowid=o.rowid AND c=o.c)
181 }
182
183 set root [db one {SELECT rootpage FROM sqlite_schema WHERE name='i1'}]
184
185 do_test 3.1 {
186 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $root
187 db eval { CREATE TABLE x1(c, rowid, PRIMARY KEY(c, rowid)) WITHOUT ROWID; }
188 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
189 } {}
190
191 do_execsql_test 3.2 {
192 UPDATE x1 SET c=19 WHERE rowid=2;
193 }
194
195 do_execsql_test 3.3 $idxcheck 2
196
197 do_execsql_test 3.4 {
198 DELETE FROM y1 WHERE a=4;
199 }
200 do_execsql_test 3.5 $idxcheck {}
201
202 #-------------------------------------------------------------------------
203 reset_db
204 do_execsql_test 4.0 {
205 CREATE TABLE z1(a INTEGER PRIMARY KEY, b);
206 CREATE INDEX z1b ON z1(b+0.0);
207 INSERT INTO z1 VALUES(1, 1.0);
208 INSERT INTO z1 VALUES(2, 4.0);
209 INSERT INTO z1 VALUES(3, 4.0);
210 INSERT INTO z1 VALUES(4, 4.0);
211 INSERT INTO z1 VALUES(5, 4.0);
212 INSERT INTO z1 VALUES(6, 4.0);
213 INSERT INTO z1 VALUES(7, 4.0);
214 INSERT INTO z1 VALUES(8, 1.0);
215 }
216
217 set root [db one {SELECT rootpage FROM sqlite_schema WHERE name='z1b'}]
218 do_test 4.1 {
219 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $root
220 db eval { CREATE TABLE x1(b, a, PRIMARY KEY(b, a)) WITHOUT ROWID; }
221 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
222 } {}
223
224 do_execsql_test 4.2 {
225 UPDATE x1 SET b=4.000000000000001 WHERE a=2; -- 1 ULP
226 UPDATE x1 SET b=4.000000000000002 WHERE a=3; -- 2 ULP
227 UPDATE x1 SET b=4.000000000000003 WHERE a=4; -- 3 ULP
228 UPDATE x1 SET b=3.9999999999999996 WHERE a=5; -- -1 ULP
229 UPDATE x1 SET b=3.9999999999999992 WHERE a=6; -- -2 ULP
230 UPDATE x1 SET b=3.9999999999999988 WHERE a=7; -- -3 ULP
231 }
232
233 do_execsql_test 4.3 {
234 PRAGMA integrity_check
235 } {
236 {index z1b stores an imprecise floating-point value for row 2}
237 {index z1b stores an imprecise floating-point value for row 3}
238 {row 4 missing from index z1b}
239 {index z1b stores an imprecise floating-point value for row 5}
240 {index z1b stores an imprecise floating-point value for row 6}
241 {row 7 missing from index z1b}
242 }
243
244 do_execsql_test 4.4 {
245 UPDATE z1 SET b=-4.0 WHERE b=4.0;
246 PRAGMA integrity_check;
247 } {ok}
248
249 do_execsql_test 4.5 {
250 UPDATE x1 SET b=-4.000000000000001 WHERE a=2; -- -1 ULP
251 UPDATE x1 SET b=-4.000000000000002 WHERE a=3; -- -2 ULP
252 UPDATE x1 SET b=-4.000000000000003 WHERE a=4; -- -3 ULP
253 UPDATE x1 SET b=-3.9999999999999996 WHERE a=5; -- 1 ULP
254 UPDATE x1 SET b=-3.9999999999999992 WHERE a=6; -- 2 ULP
255 UPDATE x1 SET b=-3.9999999999999988 WHERE a=7; -- 3 ULP
256 }
257
258 do_execsql_test 4.6 {
259 PRAGMA integrity_check
260 } {
261 {index z1b stores an imprecise floating-point value for row 2}
262 {index z1b stores an imprecise floating-point value for row 3}
263 {row 4 missing from index z1b}
264 {index z1b stores an imprecise floating-point value for row 5}
265 {index z1b stores an imprecise floating-point value for row 6}
266 {row 7 missing from index z1b}
267 }
268
269
270
271
272
273 finish_test