Debian Codegraph @

test/func6.test

raw
1 # 2017-12-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 # Test cases for the sqlite_offset() function.
13 #
14 # Some of the tests in this file depend on the exact placement of content
15 # within b-tree pages. Such placement is at the implementations discretion,
16 # and so it is possible for results to change from one release to the next.
17 #
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 ifcapable !offset_sql_func {
21 finish_test
22 return
23 }
24
25 set bNullTrim 0
26 ifcapable null_trim {
27 set bNullTrim 1
28 }
29
30 do_execsql_test func6-100 {
31 PRAGMA page_size=4096;
32 PRAGMA auto_vacuum=NONE;
33 CREATE TABLE t1(a,b,c,d);
34 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
35 INSERT INTO t1(a,b,c,d) SELECT printf('abc%03x',x), x, 1000-x, NULL FROM c;
36 CREATE INDEX t1a ON t1(a);
37 CREATE INDEX t1bc ON t1(b,c);
38 CREATE TABLE t2(x TEXT PRIMARY KEY, y) WITHOUT ROWID;
39 INSERT INTO t2(x,y) SELECT a, b FROM t1;
40 }
41
42 # Load the contents of $file from disk and return it encoded as a hex
43 # string.
44 proc loadhex {file} {
45 set fd [open $file]
46 fconfigure $fd -translation binary
47 set data [read $fd]
48 close $fd
49 binary encode hex $data
50 }
51
52 # Each argument is either an integer between 0 and 65535, a text value, or
53 # an empty string representing an SQL NULL. This command builds an SQLite
54 # record containing the values passed as arguments and returns it encoded
55 # as a hex string.
56 proc hexrecord {args} {
57 set hdr ""
58 set body ""
59
60 if {$::bNullTrim} {
61 while {[llength $args] && [lindex $args end]=={}} {
62 set args [lrange $args 0 end-1]
63 }
64 }
65
66 foreach x $args {
67 if {$x==""} {
68 append hdr 00
69 } elseif {[string is integer $x]==0} {
70 set n [string length $x]
71 append hdr [format %02x [expr $n*2 + 13]]
72 append body [binary encode hex $x]
73 } elseif {$x == 0} {
74 append hdr 08
75 } elseif {$x == 1} {
76 append hdr 09
77 } elseif {$x <= 127} {
78 append hdr 01
79 append body [format %02x $x]
80 } else {
81 append hdr 02
82 append body [format %04x $x]
83 }
84 }
85 set res [format %02x [expr 1 + [string length $hdr]/2]]
86 append res $hdr
87 append res $body
88 }
89
90 # Argument $off is an offset into the database image encoded as a hex string
91 # in argument $hexdb. This command returns 0 if the offset contains the hex
92 # $hexrec, or throws an exception otherwise.
93 #
94 proc offset_contains_record {off hexdb hexrec} {
95 set n [string length $hexrec]
96 set off [expr $off*2]
97 if { [string compare $hexrec [string range $hexdb $off [expr $off+$n-1]]] } {
98 error "record not found!"
99 }
100 return 0
101 }
102
103 # This command is the implementation of SQL function "offrec()". The first
104 # argument to this is an offset value. The remaining values are used to
105 # formulate an SQLite record. If database file test.db does not contain
106 # an equivalent record at the specified offset, an exception is thrown.
107 # Otherwise, 0 is returned.
108 #
109 proc offrec {args} {
110 set offset [lindex $args 0]
111 set rec [hexrecord {*}[lrange $args 1 end]]
112 offset_contains_record $offset $::F $rec
113 }
114 set F [loadhex test.db]
115 db func offrec offrec
116
117 # Test the sanity of the tests.
118 if {$bNullTrim} {
119 set offset 8180
120 } else {
121 set offset 8179
122 }
123 do_execsql_test func6-105 {
124 SELECT sqlite_offset(d) FROM t1 ORDER BY rowid LIMIT 1;
125 } $offset
126 do_test func6-106 {
127 set r [hexrecord abc001 1 999 {}]
128 offset_contains_record $offset $F $r
129 } 0
130
131 set z100 [string trim [string repeat "0 " 100]]
132
133 # Test offsets within table b-tree t1.
134 do_execsql_test func6-110 {
135 SELECT offrec(sqlite_offset(d), a, b, c, d) FROM t1 ORDER BY rowid
136 } $z100
137
138 do_execsql_test func6-120 {
139 SELECT a, typeof(sqlite_offset(+a)) FROM t1
140 ORDER BY rowid LIMIT 2;
141 } {abc001 null abc002 null}
142
143 # Test offsets within index b-tree t1a.
144 do_execsql_test func6-130 {
145 SELECT offrec(sqlite_offset(a), a, rowid) FROM t1 ORDER BY a
146 } $z100
147
148 # Test offsets within table b-tree t1 with a temp b-tree ORDER BY.
149 do_execsql_test func6-140 {
150 SELECT offrec(sqlite_offset(d), a, b, c, d) FROM t1 ORDER BY a
151 } $z100
152
153 # Test offsets from both index t1a and table t1 in the same query.
154 do_execsql_test func6-150 {
155 SELECT offrec(sqlite_offset(a), a, rowid),
156 offrec(sqlite_offset(d), a, b, c, d)
157 FROM t1 ORDER BY a
158 } [concat $z100 $z100]
159
160 # Test offsets from both index t1bc and table t1 in the same query.
161 do_execsql_test func6-160 {
162 SELECT offrec(sqlite_offset(b), b, c, rowid),
163 offrec(sqlite_offset(c), b, c, rowid),
164 offrec(sqlite_offset(d), a, b, c, d)
165 FROM t1
166 ORDER BY b
167 } [concat $z100 $z100 $z100]
168
169 # Test offsets in WITHOUT ROWID table t2.
170 do_execsql_test func6-200 {
171 SELECT offrec( sqlite_offset(y), x, y ) FROM t2 ORDER BY x
172 } $z100
173
174 # 2022-03-14 dbsqlfuzz 474499f3977d95fdf2dbcd99c50be1d0082e4c92
175 reset_db
176 do_execsql_test func6-300 {
177 CREATE TABLE t2(a INT, b INT PRIMARY KEY) WITHOUT ROWID;
178 CREATE INDEX x3 ON t2(b);
179 CREATE TABLE t1(a INT PRIMARY KEY, b TEXT);
180 SELECT * FROM t1 WHERE a IN (SELECT sqlite_offset(b) FROM t2);
181 } {}
182
183 finish_test