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_unsupported_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# 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20ifcapable !offset_sql_func { 21 finish_test 22 return 23} 24 25do_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; 35} 36do_execsql_test func6-110 { 37 SELECT a, sqlite_unsupported_offset(d)/4096 + 1, 38 sqlite_unsupported_offset(d)%4096 FROM t1 39 ORDER BY rowid LIMIT 2; 40} {abc001 2 4084 abc002 2 4069} 41do_execsql_test func6-120 { 42 SELECT a, typeof(sqlite_unsupported_offset(+a)) FROM t1 43 ORDER BY rowid LIMIT 2; 44} {abc001 null abc002 null} 45do_execsql_test func6-130 { 46 SELECT a, sqlite_unsupported_offset(a)/4096+1, 47 sqlite_unsupported_offset(a)%4096 48 FROM t1 49 ORDER BY a LIMIT 2; 50} {abc001 3 4087 abc002 3 4076} 51do_execsql_test func6-140 { 52 SELECT a, sqlite_unsupported_offset(d)/4096+1, 53 sqlite_unsupported_offset(d)%4096 54 FROM t1 55 ORDER BY a LIMIT 2; 56} {abc001 2 4084 abc002 2 4069} 57do_execsql_test func6-150 { 58 SELECT a, 59 sqlite_unsupported_offset(a)/4096+1, 60 sqlite_unsupported_offset(a)%4096, 61 sqlite_unsupported_offset(d)/4096+1, 62 sqlite_unsupported_offset(d)%4096 63 FROM t1 64 ORDER BY a LIMIT 2; 65} {abc001 3 4087 2 4084 abc002 3 4076 2 4069} 66do_execsql_test func6-160 { 67 SELECT b, 68 sqlite_unsupported_offset(b)/4096+1, 69 sqlite_unsupported_offset(b)%4096, 70 sqlite_unsupported_offset(c)/4096+1, 71 sqlite_unsupported_offset(c)%4096, 72 sqlite_unsupported_offset(d)/4096+1, 73 sqlite_unsupported_offset(d)%4096 74 FROM t1 75 ORDER BY b LIMIT 2; 76} {1 4 4090 4 4090 2 4084 2 4 4081 4 4081 2 4069} 77 78 79do_execsql_test func6-200 { 80 SELECT y, sqlite_unsupported_offset(y)/4096+1, 81 sqlite_unsupported_offset(y)%4096 82 FROM t2 83 ORDER BY x LIMIT 2; 84} {1 5 4087 2 5 4076} 85 86finish_test 87