3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #*************************************************************************
12 # Test cases for the sqlite_offset() function.
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.
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 ifcapable !offset_sql_func {
25 do_execsql_test func6-100 {
26 PRAGMA page_size=4096;
27 PRAGMA auto_vacuum=NONE;
28 CREATE TABLE t1(a,b,c,d);
29 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
30 INSERT INTO t1(a,b,c,d) SELECT printf('abc%03x',x), x, 1000-x, NULL FROM c;
31 CREATE INDEX t1a ON t1(a);
32 CREATE INDEX t1bc ON t1(b,c);
33 CREATE TABLE t2(x TEXT PRIMARY KEY, y) WITHOUT ROWID;
34 INSERT INTO t2(x,y) SELECT a, b FROM t1;
36 do_execsql_test func6-110 {
37 SELECT a, sqlite_offset(d)/4096 + 1,
38 sqlite_offset(d)%4096 FROM t1
39 ORDER BY rowid LIMIT 2;
40 } {abc001 2 4084 abc002 2 4069}
41 do_execsql_test func6-120 {
42 SELECT a, typeof(sqlite_offset(+a)) FROM t1
43 ORDER BY rowid LIMIT 2;
44 } {abc001 null abc002 null}
45 do_execsql_test func6-130 {
46 SELECT a, sqlite_offset(a)/4096+1,
50 } {abc001 3 4087 abc002 3 4076}
51 do_execsql_test func6-140 {
52 SELECT a, sqlite_offset(d)/4096+1,
56 } {abc001 2 4084 abc002 2 4069}
57 do_execsql_test func6-150 {
59 sqlite_offset(a)/4096+1,
60 sqlite_offset(a)%4096,
61 sqlite_offset(d)/4096+1,
65 } {abc001 3 4087 2 4084 abc002 3 4076 2 4069}
66 do_execsql_test func6-160 {
68 sqlite_offset(b)/4096+1,
69 sqlite_offset(b)%4096,
70 sqlite_offset(c)/4096+1,
71 sqlite_offset(c)%4096,
72 sqlite_offset(d)/4096+1,
76 } {1 4 4090 4 4090 2 4084 2 4 4081 4 4081 2 4069}
79 do_execsql_test func6-200 {
80 SELECT y, sqlite_offset(y)/4096+1,