/ Check-in [b82efd2a]
Login

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

Overview
Comment:Add SQLITE_DBCONFIG_ENABLE_QPSG that forces the query planner stability guarantee. This is the fix for ticket [b9f010107724c]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b82efd2a24914ace39acf99df75175042471fd73a8d31d66a40b15f63b342e10
User & Date: drh 2017-06-27 23:36:44
Context
2017-06-28
00:55
Avoid an unnecessary call to sqlite3XPrintf() in the code generator, for a performance improvement. check-in: 29d6ceb3 user: drh tags: trunk
2017-06-27
23:36
Add SQLITE_DBCONFIG_ENABLE_QPSG that forces the query planner stability guarantee. This is the fix for ticket [b9f010107724c] check-in: b82efd2a user: drh tags: trunk
16:39
Fix a virtual table problem that can occur when the vtab is on the RHS of a LEFT JOIN and there is a MATCH constraint in the ON clause, or when the vtab is in a sub-query that is the RHS of a LEFT JOIN and there is a MATCH constraint in the WHERE clause of the sub-query. check-in: 87b38166 user: dan tags: trunk
2017-06-26
14:46
Make sure sqlite3VdbeSetVarmask() is never invoked when QPSG is enabled. Closed-Leaf check-in: ebcfa73e user: drh tags: enable-QPSG
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
...
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
...
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
...
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
....
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
  BtLock *pLock;

  /* If this database is not shareable, or if the client is reading
  ** and has the read-uncommitted flag set, then no lock is required. 
  ** Return true immediately.
  */
  if( (pBtree->sharable==0)
   || (eLockType==READ_LOCK && (pBtree->db->flags & SQLITE_ReadUncommitted))
  ){
    return 1;
  }

  /* If the client is reading  or writing an index and the schema is
  ** not loaded, then it is too difficult to actually check to see if
  ** the correct locks are held.  So do not bother - just return true.
................................................................................
**    assert( !hasReadConflicts(pBtree, iRoot) );
*/
static int hasReadConflicts(Btree *pBtree, Pgno iRoot){
  BtCursor *p;
  for(p=pBtree->pBt->pCursor; p; p=p->pNext){
    if( p->pgnoRoot==iRoot 
     && p->pBtree!=pBtree
     && 0==(p->pBtree->db->flags & SQLITE_ReadUncommitted)
    ){
      return 1;
    }
  }
  return 0;
}
#endif    /* #ifdef SQLITE_DEBUG */
................................................................................
static int querySharedCacheTableLock(Btree *p, Pgno iTab, u8 eLock){
  BtShared *pBt = p->pBt;
  BtLock *pIter;

  assert( sqlite3BtreeHoldsMutex(p) );
  assert( eLock==READ_LOCK || eLock==WRITE_LOCK );
  assert( p->db!=0 );
  assert( !(p->db->flags&SQLITE_ReadUncommitted)||eLock==WRITE_LOCK||iTab==1 );
  
  /* If requesting a write-lock, then the Btree must have an open write
  ** transaction on this file. And, obviously, for this to be so there 
  ** must be an open write transaction on the file itself.
  */
  assert( eLock==READ_LOCK || (p==pBt->pWriter && p->inTrans==TRANS_WRITE) );
  assert( eLock==READ_LOCK || pBt->inTransaction==TRANS_WRITE );
................................................................................
  assert( eLock==READ_LOCK || eLock==WRITE_LOCK );
  assert( p->db!=0 );

  /* A connection with the read-uncommitted flag set will never try to
  ** obtain a read-lock using this function. The only read-lock obtained
  ** by a connection in read-uncommitted mode is on the sqlite_master 
  ** table, and that lock is obtained in BtreeBeginTrans().  */
  assert( 0==(p->db->flags&SQLITE_ReadUncommitted) || eLock==WRITE_LOCK );

  /* This function should only be called on a sharable b-tree after it 
  ** has been determined that no other b-tree holds a conflicting lock.  */
  assert( p->sharable );
  assert( SQLITE_OK==querySharedCacheTableLock(p, iTable, eLock) );

  /* First search the list for an existing lock on this table. */
................................................................................
      pBt->usableSize = usableSize;
      pBt->pageSize = pageSize;
      freeTempSpace(pBt);
      rc = sqlite3PagerSetPagesize(pBt->pPager, &pBt->pageSize,
                                   pageSize-usableSize);
      return rc;
    }
    if( (pBt->db->flags & SQLITE_RecoveryMode)==0 && nPage>nPageFile ){
      rc = SQLITE_CORRUPT_BKPT;
      goto page1_init_failed;
    }
    /* EVIDENCE-OF: R-28312-64704 However, the usable size is not allowed to
    ** be less than 480. In other words, if the page size is 512, then the
    ** reserved space size cannot exceed 32. */
    if( usableSize<480 ){







|







 







|







 







|







 







|







 







|







148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
...
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
...
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
...
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
....
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
  BtLock *pLock;

  /* If this database is not shareable, or if the client is reading
  ** and has the read-uncommitted flag set, then no lock is required. 
  ** Return true immediately.
  */
  if( (pBtree->sharable==0)
   || (eLockType==READ_LOCK && (pBtree->db->flags & SQLITE_ReadUncommit))
  ){
    return 1;
  }

  /* If the client is reading  or writing an index and the schema is
  ** not loaded, then it is too difficult to actually check to see if
  ** the correct locks are held.  So do not bother - just return true.
................................................................................
**    assert( !hasReadConflicts(pBtree, iRoot) );
*/
static int hasReadConflicts(Btree *pBtree, Pgno iRoot){
  BtCursor *p;
  for(p=pBtree->pBt->pCursor; p; p=p->pNext){
    if( p->pgnoRoot==iRoot 
     && p->pBtree!=pBtree
     && 0==(p->pBtree->db->flags & SQLITE_ReadUncommit)
    ){
      return 1;
    }
  }
  return 0;
}
#endif    /* #ifdef SQLITE_DEBUG */
................................................................................
static int querySharedCacheTableLock(Btree *p, Pgno iTab, u8 eLock){
  BtShared *pBt = p->pBt;
  BtLock *pIter;

  assert( sqlite3BtreeHoldsMutex(p) );
  assert( eLock==READ_LOCK || eLock==WRITE_LOCK );
  assert( p->db!=0 );
  assert( !(p->db->flags&SQLITE_ReadUncommit)||eLock==WRITE_LOCK||iTab==1 );
  
  /* If requesting a write-lock, then the Btree must have an open write
  ** transaction on this file. And, obviously, for this to be so there 
  ** must be an open write transaction on the file itself.
  */
  assert( eLock==READ_LOCK || (p==pBt->pWriter && p->inTrans==TRANS_WRITE) );
  assert( eLock==READ_LOCK || pBt->inTransaction==TRANS_WRITE );
................................................................................
  assert( eLock==READ_LOCK || eLock==WRITE_LOCK );
  assert( p->db!=0 );

  /* A connection with the read-uncommitted flag set will never try to
  ** obtain a read-lock using this function. The only read-lock obtained
  ** by a connection in read-uncommitted mode is on the sqlite_master 
  ** table, and that lock is obtained in BtreeBeginTrans().  */
  assert( 0==(p->db->flags&SQLITE_ReadUncommit) || eLock==WRITE_LOCK );

  /* This function should only be called on a sharable b-tree after it 
  ** has been determined that no other b-tree holds a conflicting lock.  */
  assert( p->sharable );
  assert( SQLITE_OK==querySharedCacheTableLock(p, iTable, eLock) );

  /* First search the list for an existing lock on this table. */
................................................................................
      pBt->usableSize = usableSize;
      pBt->pageSize = pageSize;
      freeTempSpace(pBt);
      rc = sqlite3PagerSetPagesize(pBt->pPager, &pBt->pageSize,
                                   pageSize-usableSize);
      return rc;
    }
    if( (pBt->db->flags & SQLITE_WriteSchema)==0 && nPage>nPageFile ){
      rc = SQLITE_CORRUPT_BKPT;
      goto page1_init_failed;
    }
    /* EVIDENCE-OF: R-28312-64704 However, the usable size is not allowed to
    ** be less than 480. In other words, if the page size is 512, then the
    ** reserved space size cannot exceed 32. */
    if( usableSize<480 ){

Changes to src/main.c.

807
808
809
810
811
812
813

814
815
816
817
818
819
820
....
2912
2913
2914
2915
2916
2917
2918



2919
2920
2921
2922
2923
2924
2925
        u32 mask;    /* Mask of the bit in sqlite3.flags to set/clear */
      } aFlagOp[] = {
        { SQLITE_DBCONFIG_ENABLE_FKEY,           SQLITE_ForeignKeys    },
        { SQLITE_DBCONFIG_ENABLE_TRIGGER,        SQLITE_EnableTrigger  },
        { SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, SQLITE_Fts3Tokenizer  },
        { SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, SQLITE_LoadExtension  },
        { SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE,      SQLITE_NoCkptOnClose  },

      };
      unsigned int i;
      rc = SQLITE_ERROR; /* IMP: R-42790-23372 */
      for(i=0; i<ArraySize(aFlagOp); i++){
        if( aFlagOp[i].op==op ){
          int onoff = va_arg(ap, int);
          int *pRes = va_arg(ap, int*);
................................................................................
#endif
#if defined(SQLITE_ENABLE_OVERSIZE_CELL_CHECK)
                 | SQLITE_CellSizeCk
#endif
#if defined(SQLITE_ENABLE_FTS3_TOKENIZER)
                 | SQLITE_Fts3Tokenizer
#endif



      ;
  sqlite3HashInit(&db->aCollSeq);
#ifndef SQLITE_OMIT_VIRTUALTABLE
  sqlite3HashInit(&db->aModule);
#endif

  /* Add the default collation sequence BINARY. BINARY works for both UTF-8







>







 







>
>
>







807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
....
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
        u32 mask;    /* Mask of the bit in sqlite3.flags to set/clear */
      } aFlagOp[] = {
        { SQLITE_DBCONFIG_ENABLE_FKEY,           SQLITE_ForeignKeys    },
        { SQLITE_DBCONFIG_ENABLE_TRIGGER,        SQLITE_EnableTrigger  },
        { SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, SQLITE_Fts3Tokenizer  },
        { SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, SQLITE_LoadExtension  },
        { SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE,      SQLITE_NoCkptOnClose  },
        { SQLITE_DBCONFIG_ENABLE_QPSG,           SQLITE_EnableQPSG     },
      };
      unsigned int i;
      rc = SQLITE_ERROR; /* IMP: R-42790-23372 */
      for(i=0; i<ArraySize(aFlagOp); i++){
        if( aFlagOp[i].op==op ){
          int onoff = va_arg(ap, int);
          int *pRes = va_arg(ap, int*);
................................................................................
#endif
#if defined(SQLITE_ENABLE_OVERSIZE_CELL_CHECK)
                 | SQLITE_CellSizeCk
#endif
#if defined(SQLITE_ENABLE_FTS3_TOKENIZER)
                 | SQLITE_Fts3Tokenizer
#endif
#if defined(SQLITE_ENABLE_QPSG)
                 | SQLITE_EnableQPSG
#endif
      ;
  sqlite3HashInit(&db->aCollSeq);
#ifndef SQLITE_OMIT_VIRTUALTABLE
  sqlite3HashInit(&db->aModule);
#endif

  /* Add the default collation sequence BINARY. BINARY works for both UTF-8

Changes to src/pragma.h.

454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
...
606
607
608
609
610
611
612
613
614
615
616
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
 {/* zName:     */ "read_uncommitted",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_ReadUncommitted },
 {/* zName:     */ "recursive_triggers",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_RecTriggers },
#endif
#if defined(SQLITE_HAS_CODEC)
................................................................................
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
 {/* zName:     */ "writable_schema",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
#endif
};
/* Number of pragmas: 60 on by default, 74 total. */







|







 







|



454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
...
606
607
608
609
610
611
612
613
614
615
616
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
 {/* zName:     */ "read_uncommitted",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_ReadUncommit },
 {/* zName:     */ "recursive_triggers",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_RecTriggers },
#endif
#if defined(SQLITE_HAS_CODEC)
................................................................................
  /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
 {/* zName:     */ "writable_schema",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_WriteSchema },
#endif
};
/* Number of pragmas: 60 on by default, 74 total. */

Changes to src/prepare.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
...
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
*/
static void corruptSchema(
  InitData *pData,     /* Initialization context */
  const char *zObj,    /* Object being parsed at the point of error */
  const char *zExtra   /* Error information */
){
  sqlite3 *db = pData->db;
  if( !db->mallocFailed && (db->flags & SQLITE_RecoveryMode)==0 ){
    char *z;
    if( zObj==0 ) zObj = "?";
    z = sqlite3MPrintf(db, "malformed database schema (%s)", zObj);
    if( zExtra ) z = sqlite3MPrintf(db, "%z - %s", z, zExtra);
    sqlite3DbFree(db, *pData->pzErrMsg);
    *pData->pzErrMsg = z;
  }
................................................................................
    }
#endif
  }
  if( db->mallocFailed ){
    rc = SQLITE_NOMEM_BKPT;
    sqlite3ResetAllSchemasOfConnection(db);
  }
  if( rc==SQLITE_OK || (db->flags&SQLITE_RecoveryMode)){
    /* Black magic: If the SQLITE_RecoveryMode flag is set, then consider
    ** the schema loaded, even if errors occurred. In this situation the 
    ** current sqlite3_prepare() operation will fail, but the following one
    ** will attempt to compile the supplied statement against whatever subset
    ** of the schema was loaded before the error occurred. The primary
    ** purpose of this is to allow access to the sqlite_master table
    ** even when its contents have been corrupted.
    */
................................................................................
    Btree *pBt = db->aDb[i].pBt;
    if( pBt ){
      assert( sqlite3BtreeHoldsMutex(pBt) );
      rc = sqlite3BtreeSchemaLocked(pBt);
      if( rc ){
        const char *zDb = db->aDb[i].zDbSName;
        sqlite3ErrorWithMsg(db, rc, "database schema is locked: %s", zDb);
        testcase( db->flags & SQLITE_ReadUncommitted );
        goto end_prepare;
      }
    }
  }

  sqlite3VtabUnlockList(db);








|







 







|
|







 







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
...
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
*/
static void corruptSchema(
  InitData *pData,     /* Initialization context */
  const char *zObj,    /* Object being parsed at the point of error */
  const char *zExtra   /* Error information */
){
  sqlite3 *db = pData->db;
  if( !db->mallocFailed && (db->flags & SQLITE_WriteSchema)==0 ){
    char *z;
    if( zObj==0 ) zObj = "?";
    z = sqlite3MPrintf(db, "malformed database schema (%s)", zObj);
    if( zExtra ) z = sqlite3MPrintf(db, "%z - %s", z, zExtra);
    sqlite3DbFree(db, *pData->pzErrMsg);
    *pData->pzErrMsg = z;
  }
................................................................................
    }
#endif
  }
  if( db->mallocFailed ){
    rc = SQLITE_NOMEM_BKPT;
    sqlite3ResetAllSchemasOfConnection(db);
  }
  if( rc==SQLITE_OK || (db->flags&SQLITE_WriteSchema)){
    /* Black magic: If the SQLITE_WriteSchema flag is set, then consider
    ** the schema loaded, even if errors occurred. In this situation the 
    ** current sqlite3_prepare() operation will fail, but the following one
    ** will attempt to compile the supplied statement against whatever subset
    ** of the schema was loaded before the error occurred. The primary
    ** purpose of this is to allow access to the sqlite_master table
    ** even when its contents have been corrupted.
    */
................................................................................
    Btree *pBt = db->aDb[i].pBt;
    if( pBt ){
      assert( sqlite3BtreeHoldsMutex(pBt) );
      rc = sqlite3BtreeSchemaLocked(pBt);
      if( rc ){
        const char *zDb = db->aDb[i].zDbSName;
        sqlite3ErrorWithMsg(db, rc, "database schema is locked: %s", zDb);
        testcase( db->flags & SQLITE_ReadUncommit );
        goto end_prepare;
      }
    }
  }

  sqlite3VtabUnlockList(db);

Changes to src/sqlite.h.in.

2003
2004
2005
2006
2007
2008
2009











2010
2011
2012
2013
2014
2015
2016
2017
2018

2019
2020
2021
2022
2023
2024
2025
** override this behaviour. The first parameter passed to this operation
** is an integer - non-zero to disable checkpoints-on-close, or zero (the
** default) to enable them. The second parameter is a pointer to an integer
** into which is written 0 or 1 to indicate whether checkpoints-on-close
** have been disabled - 0 if they are not disabled, 1 if they are.
** </dd>
**











** </dl>
*/
#define SQLITE_DBCONFIG_MAINDBNAME            1000 /* const char* */
#define SQLITE_DBCONFIG_LOOKASIDE             1001 /* void* int int */
#define SQLITE_DBCONFIG_ENABLE_FKEY           1002 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_TRIGGER        1003 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER 1004 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION 1005 /* int int* */
#define SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE      1006 /* int int* */



/*
** CAPI3REF: Enable Or Disable Extended Result Codes
** METHOD: sqlite3
**
** ^The sqlite3_extended_result_codes() routine enables or disables the







>
>
>
>
>
>
>
>
>
>
>









>







2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
** override this behaviour. The first parameter passed to this operation
** is an integer - non-zero to disable checkpoints-on-close, or zero (the
** default) to enable them. The second parameter is a pointer to an integer
** into which is written 0 or 1 to indicate whether checkpoints-on-close
** have been disabled - 0 if they are not disabled, 1 if they are.
** </dd>
**
** <dt>SQLITE_DBCONFIG_ENABLE_QPSG</dt>
** <dd>The SQLITE_DBCONFIG_ENABLE_QPSG option activates or deactivates
** the [query planner stability guarantee] (QPSG).  When the QPSG is active,
** a single SQL query statement will always use the same algorithm regardless
** of values of [bound parameters].  The QPSG disables some query optimizations
** that look at the values of bound parameters, which can make some queries
** slower.  But the QPSG has the advantage of more predictable behavior.  With
** the QPSG active, SQLite will always use the same query plan in the field as
** was used during testing in the lab.
** </dd>
**
** </dl>
*/
#define SQLITE_DBCONFIG_MAINDBNAME            1000 /* const char* */
#define SQLITE_DBCONFIG_LOOKASIDE             1001 /* void* int int */
#define SQLITE_DBCONFIG_ENABLE_FKEY           1002 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_TRIGGER        1003 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER 1004 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION 1005 /* int int* */
#define SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE      1006 /* int int* */
#define SQLITE_DBCONFIG_ENABLE_QPSG           1007 /* int int* */


/*
** CAPI3REF: Enable Or Disable Extended Result Codes
** METHOD: sqlite3
**
** ^The sqlite3_extended_result_codes() routine enables or disables the

Changes to src/sqliteInt.h.

1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487





1488
1489
1490
1491
1492
1493
1494
** Possible values for the sqlite3.flags.
**
** Value constraints (enforced via assert()):
**      SQLITE_FullFSync     == PAGER_FULLFSYNC
**      SQLITE_CkptFullFSync == PAGER_CKPT_FULLFSYNC
**      SQLITE_CacheSpill    == PAGER_CACHE_SPILL
*/
#define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */
#define SQLITE_InternChanges  0x00000002  /* Uncommitted Hash table changes */
#define SQLITE_FullColNames   0x00000004  /* Show full column names on SELECT */
#define SQLITE_FullFSync      0x00000008  /* Use full fsync on the backend */
#define SQLITE_CkptFullFSync  0x00000010  /* Use full fsync for checkpoint */
#define SQLITE_CacheSpill     0x00000020  /* OK to spill pager cache */
#define SQLITE_ShortColNames  0x00000040  /* Show short columns names */
#define SQLITE_CountRows      0x00000080  /* Count rows changed by INSERT, */
                                          /*   DELETE, or UPDATE and return */
                                          /*   the count using a callback. */
#define SQLITE_NullCallback   0x00000100  /* Invoke the callback once if the */
                                          /*   result set is empty */
#define SQLITE_SqlTrace       0x00000200  /* Debug print SQL as it executes */
#define SQLITE_VdbeListing    0x00000400  /* Debug listings of VDBE programs */
#define SQLITE_WriteSchema    0x00000800  /* OK to update SQLITE_MASTER */
#define SQLITE_VdbeAddopTrace 0x00001000  /* Trace sqlite3VdbeAddOp() calls */
#define SQLITE_IgnoreChecks   0x00002000  /* Do not enforce check constraints */
#define SQLITE_ReadUncommitted 0x0004000  /* For shared-cache mode */
#define SQLITE_LegacyFileFmt  0x00008000  /* Create new databases in format 1 */
#define SQLITE_RecoveryMode   0x00010000  /* Ignore schema errors */
#define SQLITE_ReverseOrder   0x00020000  /* Reverse unordered SELECTs */
#define SQLITE_RecTriggers    0x00040000  /* Enable recursive triggers */
#define SQLITE_ForeignKeys    0x00080000  /* Enforce foreign key constraints  */
#define SQLITE_AutoIndex      0x00100000  /* Enable automatic indexes */
#define SQLITE_PreferBuiltin  0x00200000  /* Preference to built-in funcs */
#define SQLITE_LoadExtension  0x00400000  /* Enable load_extension */
#define SQLITE_LoadExtFunc    0x00800000  /* Enable load_extension() SQL func */
#define SQLITE_EnableTrigger  0x01000000  /* True to enable triggers */
#define SQLITE_DeferFKs       0x02000000  /* Defer all FK constraints */
#define SQLITE_QueryOnly      0x04000000  /* Disable database changes */
#define SQLITE_VdbeEQP        0x08000000  /* Debug EXPLAIN QUERY PLAN */
#define SQLITE_Vacuum         0x10000000  /* Currently in a VACUUM */
#define SQLITE_CellSizeCk     0x20000000  /* Check btree cell sizes on load */
#define SQLITE_Fts3Tokenizer  0x40000000  /* Enable fts3_tokenizer(2) */
#define SQLITE_NoCkptOnClose  0x80000000  /* No checkpoint on close()/DETACH */







/*
** Bits of the sqlite3.dbOptFlags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
** selectively disable various optimizations.
*/







|
|










|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>







1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
** Possible values for the sqlite3.flags.
**
** Value constraints (enforced via assert()):
**      SQLITE_FullFSync     == PAGER_FULLFSYNC
**      SQLITE_CkptFullFSync == PAGER_CKPT_FULLFSYNC
**      SQLITE_CacheSpill    == PAGER_CACHE_SPILL
*/
#define SQLITE_WriteSchema    0x00000001  /* OK to update SQLITE_MASTER */
#define SQLITE_LegacyFileFmt  0x00000002  /* Create new databases in format 1 */
#define SQLITE_FullColNames   0x00000004  /* Show full column names on SELECT */
#define SQLITE_FullFSync      0x00000008  /* Use full fsync on the backend */
#define SQLITE_CkptFullFSync  0x00000010  /* Use full fsync for checkpoint */
#define SQLITE_CacheSpill     0x00000020  /* OK to spill pager cache */
#define SQLITE_ShortColNames  0x00000040  /* Show short columns names */
#define SQLITE_CountRows      0x00000080  /* Count rows changed by INSERT, */
                                          /*   DELETE, or UPDATE and return */
                                          /*   the count using a callback. */
#define SQLITE_NullCallback   0x00000100  /* Invoke the callback once if the */
                                          /*   result set is empty */
#define SQLITE_IgnoreChecks   0x00000200  /* Do not enforce check constraints */
#define SQLITE_ReadUncommit   0x00000400  /* READ UNCOMMITTED in shared-cache */
#define SQLITE_NoCkptOnClose  0x00000800  /* No checkpoint on close()/DETACH */
#define SQLITE_ReverseOrder   0x00001000  /* Reverse unordered SELECTs */
#define SQLITE_RecTriggers    0x00002000  /* Enable recursive triggers */
#define SQLITE_ForeignKeys    0x00004000  /* Enforce foreign key constraints  */
#define SQLITE_AutoIndex      0x00008000  /* Enable automatic indexes */
#define SQLITE_LoadExtension  0x00010000  /* Enable load_extension */
#define SQLITE_EnableTrigger  0x00020000  /* True to enable triggers */
#define SQLITE_DeferFKs       0x00040000  /* Defer all FK constraints */
#define SQLITE_QueryOnly      0x00080000  /* Disable database changes */
#define SQLITE_CellSizeCk     0x00100000  /* Check btree cell sizes on load */
#define SQLITE_Fts3Tokenizer  0x00200000  /* Enable fts3_tokenizer(2) */
#define SQLITE_EnableQPSG     0x00400000  /* Query Planner Stability Guarantee */
/* The next four values are not used by PRAGMAs or by sqlite3_dbconfig() and
** could be factored out into a separate bit vector of the sqlite3 object. */
#define SQLITE_InternChanges  0x00800000  /* Uncommitted Hash table changes */
#define SQLITE_LoadExtFunc    0x01000000  /* Enable load_extension() SQL func */
#define SQLITE_PreferBuiltin  0x02000000  /* Preference to built-in funcs */
#define SQLITE_Vacuum         0x04000000  /* Currently in a VACUUM */
/* Flags used only if debugging */
#ifdef SQLITE_DEBUG
#define SQLITE_SqlTrace       0x08000000  /* Debug print SQL as it executes */
#define SQLITE_VdbeListing    0x10000000  /* Debug listings of VDBE programs */
#define SQLITE_VdbeTrace      0x20000000  /* True to trace VDBE execution */
#define SQLITE_VdbeAddopTrace 0x40000000  /* Trace sqlite3VdbeAddOp() calls */
#define SQLITE_VdbeEQP        0x80000000  /* Debug EXPLAIN QUERY PLAN */
#endif


/*
** Bits of the sqlite3.dbOptFlags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
** selectively disable various optimizations.
*/

Changes to src/test1.c.

7313
7314
7315
7316
7317
7318
7319

7320
7321
7322
7323
7324
7325
7326
    int eVal;
  } aSetting[] = {
    { "FKEY",            SQLITE_DBCONFIG_ENABLE_FKEY },
    { "TRIGGER",         SQLITE_DBCONFIG_ENABLE_TRIGGER },
    { "FTS3_TOKENIZER",  SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER },
    { "LOAD_EXTENSION",  SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION },
    { "NO_CKPT_ON_CLOSE",SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE },

  };
  int i;
  int v;
  const char *zSetting;
  sqlite3 *db;

  if( objc!=4 ){







>







7313
7314
7315
7316
7317
7318
7319
7320
7321
7322
7323
7324
7325
7326
7327
    int eVal;
  } aSetting[] = {
    { "FKEY",            SQLITE_DBCONFIG_ENABLE_FKEY },
    { "TRIGGER",         SQLITE_DBCONFIG_ENABLE_TRIGGER },
    { "FTS3_TOKENIZER",  SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER },
    { "LOAD_EXTENSION",  SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION },
    { "NO_CKPT_ON_CLOSE",SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE },
    { "QPSG",            SQLITE_DBCONFIG_ENABLE_QPSG },
  };
  int i;
  int v;
  const char *zSetting;
  sqlite3 *db;

  if( objc!=4 ){

Changes to src/vdbe.c.

6567
6568
6569
6570
6571
6572
6573
6574
6575
6576
6577
6578
6579
6580
6581
** P2 contains the root-page of the table to lock.
**
** P4 contains a pointer to the name of the table being locked. This is only
** used to generate an error message if the lock cannot be obtained.
*/
case OP_TableLock: {
  u8 isWriteLock = (u8)pOp->p3;
  if( isWriteLock || 0==(db->flags&SQLITE_ReadUncommitted) ){
    int p1 = pOp->p1; 
    assert( p1>=0 && p1<db->nDb );
    assert( DbMaskTest(p->btreeMask, p1) );
    assert( isWriteLock==0 || isWriteLock==1 );
    rc = sqlite3BtreeLockTable(db->aDb[p1].pBt, pOp->p2, isWriteLock);
    if( rc ){
      if( (rc&0xFF)==SQLITE_LOCKED ){







|







6567
6568
6569
6570
6571
6572
6573
6574
6575
6576
6577
6578
6579
6580
6581
** P2 contains the root-page of the table to lock.
**
** P4 contains a pointer to the name of the table being locked. This is only
** used to generate an error message if the lock cannot be obtained.
*/
case OP_TableLock: {
  u8 isWriteLock = (u8)pOp->p3;
  if( isWriteLock || 0==(db->flags&SQLITE_ReadUncommit) ){
    int p1 = pOp->p1; 
    assert( p1>=0 && p1<db->nDb );
    assert( DbMaskTest(p->btreeMask, p1) );
    assert( isWriteLock==0 || isWriteLock==1 );
    rc = sqlite3BtreeLockTable(db->aDb[p1].pBt, pOp->p2, isWriteLock);
    if( rc ){
      if( (rc&0xFF)==SQLITE_LOCKED ){

Changes to src/vdbeaux.c.

4538
4539
4540
4541
4542
4543
4544

4545
4546
4547
4548
4549
4550
4551
....
4557
4558
4559
4560
4561
4562
4563

4564
4565
4566
4567
4568
4569
4570
**
** The returned value must be freed by the caller using sqlite3ValueFree().
*/
sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe *v, int iVar, u8 aff){
  assert( iVar>0 );
  if( v ){
    Mem *pMem = &v->aVar[iVar-1];

    if( 0==(pMem->flags & MEM_Null) ){
      sqlite3_value *pRet = sqlite3ValueNew(v->db);
      if( pRet ){
        sqlite3VdbeMemCopy((Mem *)pRet, pMem);
        sqlite3ValueApplyAffinity(pRet, aff, SQLITE_UTF8);
      }
      return pRet;
................................................................................
/*
** Configure SQL variable iVar so that binding a new value to it signals
** to sqlite3_reoptimize() that re-preparing the statement may result
** in a better query plan.
*/
void sqlite3VdbeSetVarmask(Vdbe *v, int iVar){
  assert( iVar>0 );

  if( iVar>=32 ){
    v->expmask |= 0x80000000;
  }else{
    v->expmask |= ((u32)1 << (iVar-1));
  }
}








>







 







>







4538
4539
4540
4541
4542
4543
4544
4545
4546
4547
4548
4549
4550
4551
4552
....
4558
4559
4560
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
**
** The returned value must be freed by the caller using sqlite3ValueFree().
*/
sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe *v, int iVar, u8 aff){
  assert( iVar>0 );
  if( v ){
    Mem *pMem = &v->aVar[iVar-1];
    assert( (v->db->flags & SQLITE_EnableQPSG)==0 );
    if( 0==(pMem->flags & MEM_Null) ){
      sqlite3_value *pRet = sqlite3ValueNew(v->db);
      if( pRet ){
        sqlite3VdbeMemCopy((Mem *)pRet, pMem);
        sqlite3ValueApplyAffinity(pRet, aff, SQLITE_UTF8);
      }
      return pRet;
................................................................................
/*
** Configure SQL variable iVar so that binding a new value to it signals
** to sqlite3_reoptimize() that re-preparing the statement may result
** in a better query plan.
*/
void sqlite3VdbeSetVarmask(Vdbe *v, int iVar){
  assert( iVar>0 );
  assert( (v->db->flags & SQLITE_EnableQPSG)==0 );
  if( iVar>=32 ){
    v->expmask |= 0x80000000;
  }else{
    v->expmask |= ((u32)1 << (iVar-1));
  }
}

Changes to src/vdbemem.c.

1478
1479
1480
1481
1482
1483
1484

1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
  int rc = SQLITE_OK;
  sqlite3_value *pVal = 0;
  sqlite3 *db = pParse->db;

  /* Skip over any TK_COLLATE nodes */
  pExpr = sqlite3ExprSkipCollate(pExpr);


  if( !pExpr ){
    pVal = valueNew(db, pAlloc);
    if( pVal ){
      sqlite3VdbeMemSetNull((Mem*)pVal);
    }
  }else if( pExpr->op==TK_VARIABLE
        || NEVER(pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
  ){
    Vdbe *v;
    int iBindVar = pExpr->iColumn;
    sqlite3VdbeSetVarmask(pParse->pVdbe, iBindVar);
    if( (v = pParse->pReprepare)!=0 ){
      pVal = valueNew(db, pAlloc);
      if( pVal ){
        rc = sqlite3VdbeMemCopy((Mem*)pVal, &v->aVar[iBindVar-1]);
        if( rc==SQLITE_OK ){
          sqlite3ValueApplyAffinity(pVal, affinity, ENC(db));
        }
        pVal->db = pParse->db;
      }
    }
  }else{
    rc = valueFromExpr(db, pExpr, ENC(db), affinity, &pVal, pAlloc);
  }








>





|
<
<







<
|
<







1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491


1492
1493
1494
1495
1496
1497
1498

1499

1500
1501
1502
1503
1504
1505
1506
  int rc = SQLITE_OK;
  sqlite3_value *pVal = 0;
  sqlite3 *db = pParse->db;

  /* Skip over any TK_COLLATE nodes */
  pExpr = sqlite3ExprSkipCollate(pExpr);

  assert( pExpr==0 || pExpr->op!=TK_REGISTER || pExpr->op2!=TK_VARIABLE );
  if( !pExpr ){
    pVal = valueNew(db, pAlloc);
    if( pVal ){
      sqlite3VdbeMemSetNull((Mem*)pVal);
    }
  }else if( pExpr->op==TK_VARIABLE && (db->flags & SQLITE_EnableQPSG)==0 ){


    Vdbe *v;
    int iBindVar = pExpr->iColumn;
    sqlite3VdbeSetVarmask(pParse->pVdbe, iBindVar);
    if( (v = pParse->pReprepare)!=0 ){
      pVal = valueNew(db, pAlloc);
      if( pVal ){
        rc = sqlite3VdbeMemCopy((Mem*)pVal, &v->aVar[iBindVar-1]);

        sqlite3ValueApplyAffinity(pVal, affinity, ENC(db));

        pVal->db = pParse->db;
      }
    }
  }else{
    rc = valueFromExpr(db, pExpr, ENC(db), affinity, &pVal, pAlloc);
  }

Changes to src/whereexpr.c.

212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
  if( *pnoCase ) return 0;
#endif
  pList = pExpr->x.pList;
  pLeft = pList->a[1].pExpr;

  pRight = sqlite3ExprSkipCollate(pList->a[0].pExpr);
  op = pRight->op;
  if( op==TK_VARIABLE ){
    Vdbe *pReprepare = pParse->pReprepare;
    int iCol = pRight->iColumn;
    pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_BLOB);
    if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
      z = (char *)sqlite3_value_text(pVal);
    }
    sqlite3VdbeSetVarmask(pParse->pVdbe, iCol);







|







212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
  if( *pnoCase ) return 0;
#endif
  pList = pExpr->x.pList;
  pLeft = pList->a[1].pExpr;

  pRight = sqlite3ExprSkipCollate(pList->a[0].pExpr);
  op = pRight->op;
  if( op==TK_VARIABLE && (db->flags & SQLITE_EnableQPSG)==0 ){
    Vdbe *pReprepare = pParse->pReprepare;
    int iCol = pRight->iColumn;
    pVal = sqlite3VdbeGetBoundValue(pReprepare, iCol, SQLITE_AFF_BLOB);
    if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
      z = (char *)sqlite3_value_text(pVal);
    }
    sqlite3VdbeSetVarmask(pParse->pVdbe, iCol);

Changes to test/analyze3.test.

118
119
120
121
122
123
124






























125
126
127
128
129
130
131
#
do_eqp_test analyze3-1.1.2 {
  SELECT sum(y) FROM t1 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
do_eqp_test analyze3-1.1.3 {
  SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
} {0 0 0 {SCAN TABLE t1}}































do_test analyze3-1.1.4 {
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
} {199 0 14850}
do_test analyze3-1.1.5 {
  set l [string range "200" 0 end]
  set u [string range "300" 0 end]







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
#
do_eqp_test analyze3-1.1.2 {
  SELECT sum(y) FROM t1 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
do_eqp_test analyze3-1.1.3 {
  SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
} {0 0 0 {SCAN TABLE t1}}

# 2017-06-26:  Verify that the SQLITE_DBCONFIG_ENABLE_QPSG setting disables
# the use of bound parameters by STAT4
#
db cache flush
unset -nocomplain l
unset -nocomplain u
do_eqp_test analyze3-1.1.3.100 {
  SELECT sum(y) FROM t1 WHERE x>$l AND x<$u
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
set l 200
set u 300
do_eqp_test analyze3-1.1.3.101 {
  SELECT sum(y) FROM t1 WHERE x>$l AND x<$u
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
set l 0
set u 1100
do_eqp_test analyze3-1.1.3.102 {
  SELECT sum(y) FROM t1 WHERE x>$l AND x<$u
} {0 0 0 {SCAN TABLE t1}}
db cache flush
sqlite3_db_config db ENABLE_QPSG 1
do_eqp_test analyze3-1.1.3.103 {
  SELECT sum(y) FROM t1 WHERE x>$l AND x<$u
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
db cache flush
sqlite3_db_config db ENABLE_QPSG 0
do_eqp_test analyze3-1.1.3.104 {
  SELECT sum(y) FROM t1 WHERE x>$l AND x<$u
} {0 0 0 {SCAN TABLE t1}}

do_test analyze3-1.1.4 {
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
} {199 0 14850}
do_test analyze3-1.1.5 {
  set l [string range "200" 0 end]
  set u [string range "300" 0 end]

Changes to test/like.test.

156
157
158
159
160
161
162

163
164
165
166
167
168
169
...
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211









































212
213
214
215
216
217
218

# This procedure executes the SQL.  Then it appends to the result the
# "sort" or "nosort" keyword (as in the cksort procedure above) then
# it appends the names of the table and index used.
#
proc queryplan {sql} {
  set ::sqlite_sort_count 0

  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  # puts eqp=$eqp
  foreach {a b c x} $eqp {
    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \
................................................................................
} {ABC {ABC abc xyz} abc abcd sort t1 *}
do_test like-3.2 {
  set sqlite_like_count
} {12}

# With an index on t1.x and case sensitivity on, optimize completely.
#
do_test like-3.3 {
  set sqlite_like_count 0
  execsql {
    PRAGMA case_sensitive_like=on;
    CREATE INDEX i1 ON t1(x);
  }
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.4 {
  set sqlite_like_count
} 0










































# The LIKE optimization still works when the RHS is a string with no
# wildcard.  Ticket [e090183531fc2747]
#
do_test like-3.4.2 {
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1;







>







 







|









|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
...
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260

# This procedure executes the SQL.  Then it appends to the result the
# "sort" or "nosort" keyword (as in the cksort procedure above) then
# it appends the names of the table and index used.
#
proc queryplan {sql} {
  set ::sqlite_sort_count 0
  db cache flush
  set data [execsql $sql]
  if {$::sqlite_sort_count} {set x sort} {set x nosort}
  lappend data $x
  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
  # puts eqp=$eqp
  foreach {a b c x} $eqp {
    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \
................................................................................
} {ABC {ABC abc xyz} abc abcd sort t1 *}
do_test like-3.2 {
  set sqlite_like_count
} {12}

# With an index on t1.x and case sensitivity on, optimize completely.
#
do_test like-3.3.100 {
  set sqlite_like_count 0
  execsql {
    PRAGMA case_sensitive_like=on;
    CREATE INDEX i1 ON t1(x);
  }
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.3.101 {
  set sqlite_like_count
} 0

# The like optimization works even when the pattern is a bound parameter
#
do_test like-3.3.102 {
  set sqlite_like_count 0
  unset -nocomplain ::likepat
  set ::likepat abc%
  queryplan {
    SELECT x FROM t1 WHERE x LIKE $::likepat ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.3.103 {
  set sqlite_like_count
} 0

# Except, the like optimization does not work for bound parameters if
# the query planner stability guarantee is active.
#
do_test like-3.3.104 {
  set sqlite_like_count 0
  sqlite3_db_config db QPSG 1
  queryplan {
    SELECT x FROM t1 WHERE x LIKE $::likepat ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.3.105 {
  set sqlite_like_count
} 12

# The query planner stability guarantee does not disrupt explicit patterns
#
do_test like-3.3.105 {
  set sqlite_like_count 0
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
  }
} {abc abcd nosort {} i1}
do_test like-3.3.106 {
  set sqlite_like_count
} 0
sqlite3_db_config db QPSG 0

# The LIKE optimization still works when the RHS is a string with no
# wildcard.  Ticket [e090183531fc2747]
#
do_test like-3.4.2 {
  queryplan {
    SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1;

Changes to tool/mkpragmatab.tcl.

116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
  TYPE: FLAG
  ARG:  SQLITE_IgnoreChecks
  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)
  IF:   !defined(SQLITE_OMIT_CHECK)

  NAME: writable_schema
  TYPE: FLAG
  ARG:  SQLITE_WriteSchema|SQLITE_RecoveryMode
  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)

  NAME: read_uncommitted
  TYPE: FLAG
  ARG:  SQLITE_ReadUncommitted
  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)

  NAME: recursive_triggers
  TYPE: FLAG
  ARG:  SQLITE_RecTriggers
  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)








|




|







116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
  TYPE: FLAG
  ARG:  SQLITE_IgnoreChecks
  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)
  IF:   !defined(SQLITE_OMIT_CHECK)

  NAME: writable_schema
  TYPE: FLAG
  ARG:  SQLITE_WriteSchema
  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)

  NAME: read_uncommitted
  TYPE: FLAG
  ARG:  SQLITE_ReadUncommit
  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)

  NAME: recursive_triggers
  TYPE: FLAG
  ARG:  SQLITE_RecTriggers
  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)