xref: /sqlite-3.40.0/test/btree01.test (revision ec27077c) 
1# 2014-11-27
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 b-tree logic.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set testprefix btree01
18
19# The refactoring on the b-tree balance() routine in check-in
20# http://www.sqlite.org/src/info/face33bea1ba3a (2014-10-27)
21# caused the integrity_check on the following SQL to fail.
22#
23do_execsql_test btree01-1.1 {
24  PRAGMA page_size=65536;
25  CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);
26  WITH RECURSIVE
27     c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)
28  INSERT INTO t1(a,b) SELECT i, zeroblob(6500) FROM c;
29  UPDATE t1 SET b=zeroblob(3000);
30  UPDATE t1 SET b=zeroblob(64000) WHERE a=2;
31  PRAGMA integrity_check;
32} {ok}
33
34# The previous test is sufficient to prevent a regression.  But we
35# add a number of additional tests to stress the balancer in similar
36# ways, looking for related problems.
37#
38for {set i 1} {$i<=30} {incr i} {
39  do_test btree01-1.2.$i {
40    db eval {
41      DELETE FROM t1;
42      WITH RECURSIVE
43        c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)
44      INSERT INTO t1(a,b) SELECT i, zeroblob(6500) FROM c;
45      UPDATE t1 SET b=zeroblob(3000);
46      UPDATE t1 SET b=zeroblob(64000) WHERE a=$::i;
47      PRAGMA integrity_check;
48    }
49  } {ok}
50}
51for {set i 1} {$i<=30} {incr i} {
52  do_test btree01-1.3.$i {
53    db eval {
54      DELETE FROM t1;
55      WITH RECURSIVE
56        c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)
57      INSERT INTO t1(a,b) SELECT i, zeroblob(6500) FROM c;
58      UPDATE t1 SET b=zeroblob(2000);
59      UPDATE t1 SET b=zeroblob(64000) WHERE a=$::i;
60      PRAGMA integrity_check;
61    }
62  } {ok}
63}
64for {set i 1} {$i<=30} {incr i} {
65  do_test btree01-1.4.$i {
66    db eval {
67      DELETE FROM t1;
68      WITH RECURSIVE
69        c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)
70      INSERT INTO t1(a,b) SELECT i, zeroblob(6500) FROM c;
71      UPDATE t1 SET b=zeroblob(6499) WHERE (a%3)==0;
72      UPDATE t1 SET b=zeroblob(6499) WHERE (a%3)==1;
73      UPDATE t1 SET b=zeroblob(6499) WHERE (a%3)==2;
74      UPDATE t1 SET b=zeroblob(64000) WHERE a=$::i;
75      PRAGMA integrity_check;
76    }
77  } {ok}
78}
79for {set i 1} {$i<=30} {incr i} {
80  do_test btree01-1.5.$i {
81    db eval {
82      DELETE FROM t1;
83      WITH RECURSIVE
84        c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)
85      INSERT INTO t1(a,b) SELECT i, zeroblob(6542) FROM c;
86      UPDATE t1 SET b=zeroblob(2331);
87      UPDATE t1 SET b=zeroblob(65496) WHERE a=$::i;
88      PRAGMA integrity_check;
89    }
90  } {ok}
91}
92for {set i 1} {$i<=30} {incr i} {
93  do_test btree01-1.6.$i {
94    db eval {
95      DELETE FROM t1;
96      WITH RECURSIVE
97        c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)
98      INSERT INTO t1(a,b) SELECT i, zeroblob(6542) FROM c;
99      UPDATE t1 SET b=zeroblob(2332);
100      UPDATE t1 SET b=zeroblob(65496) WHERE a=$::i;
101      PRAGMA integrity_check;
102    }
103  } {ok}
104}
105for {set i 1} {$i<=30} {incr i} {
106  do_test btree01-1.7.$i {
107    db eval {
108      DELETE FROM t1;
109      WITH RECURSIVE
110        c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<30)
111      INSERT INTO t1(a,b) SELECT i, zeroblob(6500) FROM c;
112      UPDATE t1 SET b=zeroblob(1);
113      UPDATE t1 SET b=zeroblob(65000) WHERE a=$::i;
114      PRAGMA integrity_check;
115    }
116  } {ok}
117}
118for {set i 1} {$i<=31} {incr i} {
119  do_test btree01-1.8.$i {
120    db eval {
121      DELETE FROM t1;
122      WITH RECURSIVE
123        c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<31)
124      INSERT INTO t1(a,b) SELECT i, zeroblob(6500) FROM c;
125      UPDATE t1 SET b=zeroblob(4000);
126      UPDATE t1 SET b=zeroblob(65000) WHERE a=$::i;
127      PRAGMA integrity_check;
128    }
129  } {ok}
130}
131
132# 2022-03-06 OSSFuzz issue 45329
133# An assertion fault due to the failure to clear a flag in an optimization
134# committed last night.
135#
136# When the stay-on-last page optimization of sqlite3BtreeIndexMoveto() is
137# invoked, it needs to clear the BTCF_ValidOvfl flag.
138#
139db close
140sqlite3 db :memory:
141do_execsql_test btree01-2.1 {
142  PRAGMA page_size=1024;
143  CREATE TABLE t1(a INT PRIMARY KEY, b BLOB, c INT) WITHOUT ROWID;
144  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
145    INSERT INTO t1(a,b,c) SELECT x*2, zeroblob(100), x FROM c;
146  UPDATE t1 SET b=zeroblob(1000) WHERE a=198;
147  CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT);
148  INSERT INTO t2(y) VALUES(198),(187),(100);
149  SELECT y, c FROM t2 LEFT JOIN t1 ON y=a ORDER BY x;
150} {198 99 187 {} 100 50}
151do_execsql_test btree01-2.2 {
152  SELECT y, c FROM t1 RIGHT JOIN t2 ON y=a ORDER BY x;
153} {198 99 187 {} 100 50}
154
155
156finish_test
157