AryaWu/sqlite

1# 2017-12-162#3# The author disclaims copyright to this source code.  In place of4# 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 content15# 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.tcl20ifcapable !offset_sql_func {21  finish_test22  return23}24 25set bNullTrim 026ifcapable null_trim {27  set bNullTrim 128}29 30do_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 hex43# string.44proc loadhex {file} {45  set fd [open $file]46  fconfigure $fd -translation binary47  set data [read $fd]48  close $fd49  binary encode hex $data 50}51 52# Each argument is either an integer between 0 and 65535, a text value, or53# an empty string representing an SQL NULL. This command builds an SQLite54# record containing the values passed as arguments and returns it encoded55# as a hex string.56proc 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 0069    } 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 0875    } elseif {$x == 1} {76      append hdr 0977    } elseif {$x <= 127} {78      append hdr 0179      append body [format %02x $x]80    } else {81      append hdr 0282      append body [format %04x $x]83    }84  }85  set res [format %02x [expr 1 + [string length $hdr]/2]]86  append res $hdr87  append res $body88}89 90# Argument $off is an offset into the database image encoded as a hex string91# in argument $hexdb. This command returns 0 if the offset contains the hex92# $hexrec, or throws an exception otherwise.93#94proc 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 0101}102 103# This command is the implementation of SQL function "offrec()". The first104# argument to this is an offset value. The remaining values are used to105# formulate an SQLite record. If database file test.db does not contain106# an equivalent record at the specified offset, an exception is thrown.107# Otherwise, 0 is returned.108#109proc offrec {args} {110  set offset [lindex $args 0]111  set rec [hexrecord {*}[lrange $args 1 end]]112  offset_contains_record $offset $::F $rec113}114set F [loadhex test.db]115db func offrec offrec116 117# Test the sanity of the tests.118if {$bNullTrim} {119  set offset 8180120} else {121  set offset 8179122}123do_execsql_test func6-105 {124  SELECT sqlite_offset(d) FROM t1 ORDER BY rowid LIMIT 1;125} $offset126do_test func6-106 {127  set r [hexrecord abc001 1 999 {}]128  offset_contains_record $offset $F $r129} 0130 131set z100 [string trim [string repeat "0 " 100]]132 133# Test offsets within table b-tree t1.134do_execsql_test func6-110 {135  SELECT offrec(sqlite_offset(d), a, b, c, d) FROM t1 ORDER BY rowid136} $z100137 138do_execsql_test func6-120 {139  SELECT a, typeof(sqlite_offset(+a)) FROM t1140   ORDER BY rowid LIMIT 2;141} {abc001 null abc002 null}142 143# Test offsets within index b-tree t1a.144do_execsql_test func6-130 {145  SELECT offrec(sqlite_offset(a), a, rowid) FROM t1 ORDER BY a146} $z100147 148# Test offsets within table b-tree t1 with a temp b-tree ORDER BY.149do_execsql_test func6-140 {150  SELECT offrec(sqlite_offset(d), a, b, c, d) FROM t1 ORDER BY a151} $z100152 153# Test offsets from both index t1a and table t1 in the same query.154do_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 a158} [concat $z100 $z100]159 160# Test offsets from both index t1bc and table t1 in the same query.161do_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 t1166  ORDER BY b167} [concat $z100 $z100 $z100]168 169# Test offsets in WITHOUT ROWID table t2.170do_execsql_test func6-200 {171  SELECT offrec( sqlite_offset(y), x, y ) FROM t2 ORDER BY x172} $z100173 174# 2022-03-14 dbsqlfuzz 474499f3977d95fdf2dbcd99c50be1d0082e4c92175reset_db176do_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 183finish_test184