/ Check-in [4f1f1f52]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add support for the sqlite_unsupported_offset() SQL function if and only if compiled using -DSQLITE_ENABLE_OFFSET_SQL_FUNC. Use that definition when compiling the command-line shell.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4f1f1f521ae6da96b899e10bfeff6bc1ab7a45de0705076febaae20b441f48c6
User & Date: drh 2017-12-29 17:21:21
Context
2018-01-01
19:33
Enable the introspection pragmas for command-line shell builds. check-in: 0b04223f user: drh tags: trunk
16:59
Experiments with the regexp.c extension, trying to get it to report the exact substring that matches the RE. Leaf check-in: 3d6fba62 user: drh tags: regexp-span
2017-12-29
17:21
Add support for the sqlite_unsupported_offset() SQL function if and only if compiled using -DSQLITE_ENABLE_OFFSET_SQL_FUNC. Use that definition when compiling the command-line shell. check-in: 4f1f1f52 user: drh tags: trunk
16:37
Stricter test cases. Closed-Leaf check-in: 9406c0a6 user: drh tags: location-function
12:50
Add test cases for the undocumented behavior of duplicate columns on an INSERT or UPDATE. check-in: f4349c0c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to Makefile.in.

   578    578   SHELL_OPT = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4
   579    579   # SHELL_OPT += -DSQLITE_ENABLE_FTS5
   580    580   SHELL_OPT += -DSQLITE_ENABLE_EXPLAIN_COMMENTS
   581    581   SHELL_OPT += -DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
   582    582   SHELL_OPT += -DSQLITE_ENABLE_STMTVTAB
   583    583   SHELL_OPT += -DSQLITE_ENABLE_DBPAGE_VTAB
   584    584   SHELL_OPT += -DSQLITE_ENABLE_DBSTAT_VTAB
          585  +SHELL_OPT += -DSQLITE_ENABLE_OFFSET_SQL_FUNC
   585    586   FUZZERSHELL_OPT = -DSQLITE_ENABLE_JSON1
   586    587   FUZZCHECK_OPT = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_MEMSYS5 -DSQLITE_OSS_FUZZ
   587    588   FUZZCHECK_OPT += -DSQLITE_MAX_MEMORY=50000000
   588    589   FUZZCHECK_SRC = $(TOP)/test/fuzzcheck.c $(TOP)/test/ossfuzz.c
   589    590   DBFUZZ_OPT = 
   590    591   
   591    592   # This is the default Makefile target.  The objects listed here

Changes to Makefile.msc.

  1509   1509   
  1510   1510   # Additional compiler options for the shell.  These are only effective
  1511   1511   # when the shell is not being dynamically linked.
  1512   1512   #
  1513   1513   !IF $(DYNAMIC_SHELL)==0 && $(FOR_WIN10)==0
  1514   1514   SHELL_COMPILE_OPTS = $(SHELL_COMPILE_OPTS) -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_STMTVTAB
  1515   1515   SHELL_COMPILE_OPTS = $(SHELL_COMPILE_OPTS) -DSQLITE_ENABLE_DBPAGE_VTAB -DSQLITE_ENABLE_DBSTAT_VTAB
         1516  +SHELL_COMPILE_OPTS = $(SHELL_COMPILE_OPTS) -DSQLITE_ENABLE_OFFSET_SQL_FUNC
  1516   1517   !ENDIF
  1517   1518   
  1518   1519   # <<mark>>
  1519   1520   # Extra compiler options for various test tools.
  1520   1521   #
  1521   1522   MPTESTER_COMPILE_OPTS = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS5
  1522   1523   FUZZERSHELL_COMPILE_OPTS = -DSQLITE_ENABLE_JSON1

Changes to main.mk.

   505    505   #
   506    506   SHELL_OPT += -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5
   507    507   SHELL_OPT += -DSQLITE_ENABLE_EXPLAIN_COMMENTS
   508    508   SHELL_OPT += -DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
   509    509   SHELL_OPT += -DSQLITE_ENABLE_STMTVTAB
   510    510   SHELL_OPT += -DSQLITE_ENABLE_DBPAGE_VTAB
   511    511   SHELL_OPT += -DSQLITE_ENABLE_DBSTAT_VTAB
          512  +SHELL_OPT += -DSQLITE_ENABLE_OFFSET_SQL_FUNC
   512    513   FUZZERSHELL_OPT = -DSQLITE_ENABLE_JSON1
   513    514   FUZZCHECK_OPT = -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_MEMSYS5
   514    515   FUZZCHECK_OPT += -DSQLITE_MAX_MEMORY=50000000
   515    516   DBFUZZ_OPT =
   516    517   KV_OPT = -DSQLITE_THREADSAFE=0 -DSQLITE_DIRECT_OVERFLOW_READ
   517    518   ST_OPT = -DSQLITE_THREADSAFE=0
   518    519   

Changes to src/btree.c.

  4428   4428     assert( cursorHoldsMutex(pCur) );
  4429   4429     assert( pCur->eState==CURSOR_VALID );
  4430   4430     assert( pCur->curIntKey );
  4431   4431     getCellInfo(pCur);
  4432   4432     return pCur->info.nKey;
  4433   4433   }
  4434   4434   
         4435  +#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
         4436  +/*
         4437  +** Return the offset into the database file for the start of the
         4438  +** payload to which the cursor is pointing.
         4439  +*/
         4440  +i64 sqlite3BtreeOffset(BtCursor *pCur){
         4441  +  assert( cursorHoldsMutex(pCur) );
         4442  +  assert( pCur->eState==CURSOR_VALID );
         4443  +  getCellInfo(pCur);
         4444  +  return (i64)pCur->pBt->pageSize*((i64)pCur->pPage->pgno - 1) +
         4445  +         (i64)(pCur->info.pPayload - pCur->pPage->aData);
         4446  +}
         4447  +#endif /* SQLITE_ENABLE_OFFSET_SQL_FUNC */
         4448  +
  4435   4449   /*
  4436   4450   ** Return the number of bytes of payload for the entry that pCur is
  4437   4451   ** currently pointing to.  For table btrees, this will be the amount
  4438   4452   ** of data.  For index btrees, this will be the size of the key.
  4439   4453   **
  4440   4454   ** The caller must guarantee that the cursor is pointing to a non-NULL
  4441   4455   ** valid entry.  In other words, the calling procedure must guarantee

Changes to src/btree.h.

   287    287                          int flags, int seekResult);
   288    288   int sqlite3BtreeFirst(BtCursor*, int *pRes);
   289    289   int sqlite3BtreeLast(BtCursor*, int *pRes);
   290    290   int sqlite3BtreeNext(BtCursor*, int flags);
   291    291   int sqlite3BtreeEof(BtCursor*);
   292    292   int sqlite3BtreePrevious(BtCursor*, int flags);
   293    293   i64 sqlite3BtreeIntegerKey(BtCursor*);
          294  +#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
          295  +i64 sqlite3BtreeOffset(BtCursor*);
          296  +#endif
   294    297   int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*);
   295    298   const void *sqlite3BtreePayloadFetch(BtCursor*, u32 *pAmt);
   296    299   u32 sqlite3BtreePayloadSize(BtCursor*);
   297    300   
   298    301   char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*);
   299    302   struct Pager *sqlite3BtreePager(Btree*);
   300    303   i64 sqlite3BtreeRowCountEst(BtCursor*);

Changes to src/expr.c.

  3866   3866           pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[0].pExpr);
  3867   3867         }
  3868   3868   #endif
  3869   3869         if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  3870   3870           if( !pColl ) pColl = db->pDfltColl; 
  3871   3871           sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
  3872   3872         }
         3873  +#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
         3874  +      if( pDef->funcFlags & SQLITE_FUNC_OFFSET ){
         3875  +        Expr *pArg = pFarg->a[0].pExpr;
         3876  +        if( pArg->op==TK_COLUMN ){
         3877  +          sqlite3VdbeAddOp3(v, OP_Offset, pArg->iTable, pArg->iColumn, target);
         3878  +        }else{
         3879  +          sqlite3VdbeAddOp2(v, OP_Null, 0, target);
         3880  +        }
         3881  +      }else
         3882  +#endif
         3883  +      {
  3873   3884         sqlite3VdbeAddOp4(v, pParse->iSelfTab ? OP_PureFunc0 : OP_Function0,
  3874   3885                           constMask, r1, target, (char*)pDef, P4_FUNCDEF);
  3875   3886         sqlite3VdbeChangeP5(v, (u8)nFarg);
         3887  +      }
  3876   3888         if( nFarg && constMask==0 ){
  3877   3889           sqlite3ReleaseTempRange(pParse, r1, nFarg);
  3878   3890         }
  3879   3891         return target;
  3880   3892       }
  3881   3893   #ifndef SQLITE_OMIT_SUBQUERY
  3882   3894       case TK_EXISTS:

Changes to src/func.c.

  1795   1795   #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
  1796   1796       FUNCTION2(unlikely,          1, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1797   1797       FUNCTION2(likelihood,        2, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1798   1798       FUNCTION2(likely,            1, 0, 0, noopFunc,  SQLITE_FUNC_UNLIKELY),
  1799   1799   #ifdef SQLITE_DEBUG
  1800   1800       FUNCTION2(affinity,          1, 0, 0, noopFunc,  SQLITE_FUNC_AFFINITY),
  1801   1801   #endif
         1802  +#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
         1803  +    FUNCTION2(sqlite_unsupported_offset,
         1804  +                                 1, 0, 0, noopFunc,  SQLITE_FUNC_OFFSET|
         1805  +                                                     SQLITE_FUNC_TYPEOF),
         1806  +#endif
  1802   1807       FUNCTION(ltrim,              1, 1, 0, trimFunc         ),
  1803   1808       FUNCTION(ltrim,              2, 1, 0, trimFunc         ),
  1804   1809       FUNCTION(rtrim,              1, 2, 0, trimFunc         ),
  1805   1810       FUNCTION(rtrim,              2, 2, 0, trimFunc         ),
  1806   1811       FUNCTION(trim,               1, 3, 0, trimFunc         ),
  1807   1812       FUNCTION(trim,               2, 3, 0, trimFunc         ),
  1808   1813       FUNCTION(min,               -1, 0, 1, minmaxFunc       ),

Changes to src/sqliteInt.h.

  1625   1625   #define SQLITE_FUNC_COALESCE 0x0200 /* Built-in coalesce() or ifnull() */
  1626   1626   #define SQLITE_FUNC_UNLIKELY 0x0400 /* Built-in unlikely() function */
  1627   1627   #define SQLITE_FUNC_CONSTANT 0x0800 /* Constant inputs give a constant output */
  1628   1628   #define SQLITE_FUNC_MINMAX   0x1000 /* True for min() and max() aggregates */
  1629   1629   #define SQLITE_FUNC_SLOCHNG  0x2000 /* "Slow Change". Value constant during a
  1630   1630                                       ** single query - might change over time */
  1631   1631   #define SQLITE_FUNC_AFFINITY 0x4000 /* Built-in affinity() function */
         1632  +#define SQLITE_FUNC_OFFSET   0x8000 /* Built-in sqlite_offset() function */
  1632   1633   
  1633   1634   /*
  1634   1635   ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are
  1635   1636   ** used to create the initializers for the FuncDef structures.
  1636   1637   **
  1637   1638   **   FUNCTION(zName, nArg, iArg, bNC, xFunc)
  1638   1639   **     Used to create a scalar function definition of a function zName

Changes to src/test_config.c.

   155    155   #endif
   156    156   
   157    157   #ifdef SQLITE_ENABLE_MEMSYS5
   158    158     Tcl_SetVar2(interp, "sqlite_options", "mem5", "1", TCL_GLOBAL_ONLY);
   159    159   #else
   160    160     Tcl_SetVar2(interp, "sqlite_options", "mem5", "0", TCL_GLOBAL_ONLY);
   161    161   #endif
          162  +
          163  +#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
          164  +  Tcl_SetVar2(interp, "sqlite_options", "offset_sql_func","1",TCL_GLOBAL_ONLY);
          165  +#else
          166  +  Tcl_SetVar2(interp, "sqlite_options", "offset_sql_func","0",TCL_GLOBAL_ONLY);
          167  +#endif
   162    168   
   163    169   #ifdef SQLITE_ENABLE_PREUPDATE_HOOK
   164    170     Tcl_SetVar2(interp, "sqlite_options", "preupdate", "1", TCL_GLOBAL_ONLY);
   165    171   #else
   166    172     Tcl_SetVar2(interp, "sqlite_options", "preupdate", "0", TCL_GLOBAL_ONLY);
   167    173   #endif
   168    174   

Changes to src/vdbe.c.

  2345   2345     if( p->apCsr[pOp->p1]->nullRow ){
  2346   2346       sqlite3VdbeMemSetNull(aMem + pOp->p3);
  2347   2347       goto jump_to_p2;
  2348   2348     }
  2349   2349     break;
  2350   2350   }
  2351   2351   
         2352  +#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
         2353  +/* Opcode: Offset P1 P2 P3 * *
         2354  +** Synopsis: r[P3] = sqlite_offset(P1)
         2355  +**
         2356  +** Store in register r[P3] the byte offset into the database file that is the
         2357  +** start of the payload for the record at which that cursor P1 is currently
         2358  +** pointing.
         2359  +**
         2360  +** P2 is the column number for the argument to the sqlite_offset() function.
         2361  +** This opcode does not use P2 itself, but the P2 value is used by the
         2362  +** code generator.  The P1, P2, and P3 operands to this opcode are the
         2363  +** as as for OP_Column.
         2364  +**
         2365  +** This opcode is only available if SQLite is compiled with the
         2366  +** -DSQLITE_ENABLE_OFFSET_SQL_FUNC option.
         2367  +*/
         2368  +case OP_Offset: {          /* out3 */
         2369  +  VdbeCursor *pC;    /* The VDBE cursor */
         2370  +  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
         2371  +  pC = p->apCsr[pOp->p1];
         2372  +  pOut = &p->aMem[pOp->p3];
         2373  +  if( NEVER(pC==0) || pC->eCurType!=CURTYPE_BTREE ){
         2374  +    sqlite3VdbeMemSetNull(pOut);
         2375  +  }else{
         2376  +    sqlite3VdbeMemSetInt64(pOut, sqlite3BtreeOffset(pC->uc.pCursor));
         2377  +  }
         2378  +  break;
         2379  +}
         2380  +#endif /* SQLITE_ENABLE_OFFSET_SQL_FUNC */
         2381  +
  2352   2382   /* Opcode: Column P1 P2 P3 P4 P5
  2353   2383   ** Synopsis: r[P3]=PX
  2354   2384   **
  2355   2385   ** Interpret the data that cursor P1 points to as a structure built using
  2356   2386   ** the MakeRecord instruction.  (See the MakeRecord opcode for additional
  2357   2387   ** information about the format of the data.)  Extract the P2-th column
  2358   2388   ** from this record.  If there are less that (P2+1) 

Changes to src/where.c.

  5142   5142        && !db->mallocFailed
  5143   5143       ){
  5144   5144         last = sqlite3VdbeCurrentAddr(v);
  5145   5145         k = pLevel->addrBody;
  5146   5146         pOp = sqlite3VdbeGetOp(v, k);
  5147   5147         for(; k<last; k++, pOp++){
  5148   5148           if( pOp->p1!=pLevel->iTabCur ) continue;
  5149         -        if( pOp->opcode==OP_Column ){
         5149  +        if( pOp->opcode==OP_Column
         5150  +#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
         5151  +         || pOp->opcode==OP_Offset
         5152  +#endif
         5153  +        ){
  5150   5154             int x = pOp->p2;
  5151   5155             assert( pIdx->pTable==pTab );
  5152   5156             if( !HasRowid(pTab) ){
  5153   5157               Index *pPk = sqlite3PrimaryKeyIndex(pTab);
  5154   5158               x = pPk->aiColumn[x];
  5155   5159               assert( x>=0 );
  5156   5160             }

Added test/func6.test.

            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  +#
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +ifcapable !offset_sql_func {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           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;
           35  +}
           36  +do_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}
           41  +do_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}
           45  +do_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}
           51  +do_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}
           57  +do_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}
           66  +do_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  +
           79  +do_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  +
           86  +finish_test