SQLite4
Check-in [2244abfac2]
Not logged in

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

Overview
Comment:Partially reenable automatic indexes. Six legacy tests cases in autoindex1.test are still failing as of this commit.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2244abfac27e7671b5d2d8cd500e6d76de0d25af
User & Date: dan 2013-07-25 20:02:41
Context
2013-07-25
23:26
Add the kvreplace(K,V) pragma for use in testing. check-in: b5ceafc26f user: drh tags: trunk
20:02
Partially reenable automatic indexes. Six legacy tests cases in autoindex1.test are still failing as of this commit. check-in: 2244abfac2 user: dan tags: trunk
15:13
Reenable the whereShortCut() optimization. check-in: 97c7b5580f user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to main.mk.

48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
OPTS += -DHAVE_GMTIME_R
OPTS += -DHAVE_LOCALTIME_R
OPTS += -DHAVE_MALLOC_USABLE_SIZE
OPTS += -DHAVE_USLEEP
#OPTS += -DSQLITE4_MEMDEBUG=1
#OPTS += -DSQLITE4_NO_SYNC=1 -DLSM_NO_SYNC=1
#OPTS += -DSQLITE4_OMIT_ANALYZE
OPTS += -DSQLITE4_OMIT_AUTOMATIC_INDEX
OPTS += -DSQLITE4_OMIT_VIRTUALTABLE=1
OPTS += -DSQLITE4_OMIT_XFER_OPT
OPTS += -DSQLITE4_THREADSAFE=0

# This is how we compile
#
TCCX =  $(TCC) $(OPTS) -I. -I$(TOP)/src -I$(TOP) 







|







48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
OPTS += -DHAVE_GMTIME_R
OPTS += -DHAVE_LOCALTIME_R
OPTS += -DHAVE_MALLOC_USABLE_SIZE
OPTS += -DHAVE_USLEEP
#OPTS += -DSQLITE4_MEMDEBUG=1
#OPTS += -DSQLITE4_NO_SYNC=1 -DLSM_NO_SYNC=1
#OPTS += -DSQLITE4_OMIT_ANALYZE
#OPTS += -DSQLITE4_OMIT_AUTOMATIC_INDEX
OPTS += -DSQLITE4_OMIT_VIRTUALTABLE=1
OPTS += -DSQLITE4_OMIT_XFER_OPT
OPTS += -DSQLITE4_THREADSAFE=0

# This is how we compile
#
TCCX =  $(TCC) $(OPTS) -I. -I$(TOP)/src -I$(TOP) 

Changes to src/build.c.

2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
....
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342

/*
** Cursor iPkCsr is open on a primary key index. This function generates
** code that creates the corresponding covering index record required
** by pIdx by reading values from this cursor. The record is stored in
** register regOut.
*/
static void encodeCoveringRecord(
  Parse *pParse,                  /* Parse context */
  int iPkCsr,                     /* Cursor open on primary key */
  Index *pIdx,                    /* Index to create record for */
  int regOut                      /* Register to write record to */
){
  Vdbe *v = pParse->pVdbe;        /* Generate code into this virtual machine */
  Table *pTab = pIdx->pTable;     /* The table that is indexed */
................................................................................

      addrTest = sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, 0, regKey, 0);
      sqlite4HaltConstraint(pParse, OE_Abort, (char *)zErr, P4_STATIC);
      sqlite4VdbeJumpHere(v, addrTest);
    }
    if( pIdx->nCover>0 ){
      regData = regKey+1;
      encodeCoveringRecord(pParse, iTab, pIdx, regData);
    }
    sqlite4VdbeAddOp3(v, OP_IdxInsert, iIdx, regData, regKey);  
    sqlite4ReleaseTempRange(pParse, regKey, 2);
  }

  sqlite4VdbeAddOp2(v, OP_Next, iTab, addr1+1);
  sqlite4VdbeJumpHere(v, addr1);







|







 







|







2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
....
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342

/*
** Cursor iPkCsr is open on a primary key index. This function generates
** code that creates the corresponding covering index record required
** by pIdx by reading values from this cursor. The record is stored in
** register regOut.
*/
void sqlite4EncodeIndexValue(
  Parse *pParse,                  /* Parse context */
  int iPkCsr,                     /* Cursor open on primary key */
  Index *pIdx,                    /* Index to create record for */
  int regOut                      /* Register to write record to */
){
  Vdbe *v = pParse->pVdbe;        /* Generate code into this virtual machine */
  Table *pTab = pIdx->pTable;     /* The table that is indexed */
................................................................................

      addrTest = sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, 0, regKey, 0);
      sqlite4HaltConstraint(pParse, OE_Abort, (char *)zErr, P4_STATIC);
      sqlite4VdbeJumpHere(v, addrTest);
    }
    if( pIdx->nCover>0 ){
      regData = regKey+1;
      sqlite4EncodeIndexValue(pParse, iTab, pIdx, regData);
    }
    sqlite4VdbeAddOp3(v, OP_IdxInsert, iIdx, regData, regKey);  
    sqlite4ReleaseTempRange(pParse, regKey, 2);
  }

  sqlite4VdbeAddOp2(v, OP_Next, iTab, addr1+1);
  sqlite4VdbeJumpHere(v, addr1);

Changes to src/env.c.

175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
    sqlite4_mutex_free(pEnv->pFactoryMutex);
    sqlite4_mutex_free(pEnv->pPrngMutex);
    sqlite4_mutex_free(pEnv->pMemMutex);
    pEnv->pMemMutex = 0;
    while( (pMkr = pEnv->pFactory)!=0 && pMkr->isPerm==0 ){
      KVFactory *pNext = pMkr->pNext;
      sqlite4_free(pEnv, pMkr);
      pMkr = pNext;
    }
    sqlite4MutexEnd(pEnv);
    sqlite4MallocEnd(pEnv);
    pEnv->isInit = 0;
  }
  return SQLITE4_OK;
}







|







175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
    sqlite4_mutex_free(pEnv->pFactoryMutex);
    sqlite4_mutex_free(pEnv->pPrngMutex);
    sqlite4_mutex_free(pEnv->pMemMutex);
    pEnv->pMemMutex = 0;
    while( (pMkr = pEnv->pFactory)!=0 && pMkr->isPerm==0 ){
      KVFactory *pNext = pMkr->pNext;
      sqlite4_free(pEnv, pMkr);
      pEnv->pFactory = pNext;
    }
    sqlite4MutexEnd(pEnv);
    sqlite4MallocEnd(pEnv);
    pEnv->isInit = 0;
  }
  return SQLITE4_OK;
}

Changes to src/sqliteInt.h.

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
....
2791
2792
2793
2794
2795
2796
2797

2798
2799
2800
2801
2802
2803
2804
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

#define SQLITE4_OMIT_PROGRESS_CALLBACK 1
#define SQLITE4_OMIT_VIRTUALTABLE 1
#define SQLITE4_OMIT_LOCALTIME 1
#define SQLITE4_OMIT_AUTOMATIC_INDEX 1

/*
** These #defines should enable >2GB file support on POSIX if the
** underlying operating system supports it.  If the OS lacks
** large file support, or if the OS is windows, these should be no-ops.
**
** Ticket #2739:  The _LARGEFILE_SOURCE macro must appear before any
................................................................................
int sqlite4ExprIsInteger(Expr*, int*);
int sqlite4ExprCanBeNull(const Expr*);
void sqlite4ExprCodeIsNullJump(Vdbe*, const Expr*, int, int);
int sqlite4ExprNeedsNoAffinityChange(const Expr*, char);
void sqlite4GenerateRowDelete(Parse*, Table*, int, int, int, Trigger *, int);
void sqlite4GenerateRowIndexDelete(Parse*, Table*, int, int, int*);
void sqlite4EncodeIndexKey(Parse *, Index *, int, Index *, int, int, int);

void sqlite4GenerateConstraintChecks(Parse*,Table*,int,int,
                                     int*,int,int,int,int,int*);
void sqlite4CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int);
int sqlite4OpenTableAndIndices(Parse*, Table*, int, int);
void sqlite4BeginWriteOperation(Parse*, int, int);
void sqlite4MultiWrite(Parse*);
void sqlite4MayAbort(Parse*);







<







 







>







14
15
16
17
18
19
20

21
22
23
24
25
26
27
....
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

#define SQLITE4_OMIT_PROGRESS_CALLBACK 1
#define SQLITE4_OMIT_VIRTUALTABLE 1
#define SQLITE4_OMIT_LOCALTIME 1


/*
** These #defines should enable >2GB file support on POSIX if the
** underlying operating system supports it.  If the OS lacks
** large file support, or if the OS is windows, these should be no-ops.
**
** Ticket #2739:  The _LARGEFILE_SOURCE macro must appear before any
................................................................................
int sqlite4ExprIsInteger(Expr*, int*);
int sqlite4ExprCanBeNull(const Expr*);
void sqlite4ExprCodeIsNullJump(Vdbe*, const Expr*, int, int);
int sqlite4ExprNeedsNoAffinityChange(const Expr*, char);
void sqlite4GenerateRowDelete(Parse*, Table*, int, int, int, Trigger *, int);
void sqlite4GenerateRowIndexDelete(Parse*, Table*, int, int, int*);
void sqlite4EncodeIndexKey(Parse *, Index *, int, Index *, int, int, int);
void sqlite4EncodeIndexValue(Parse*, int, Index*, int);
void sqlite4GenerateConstraintChecks(Parse*,Table*,int,int,
                                     int*,int,int,int,int,int*);
void sqlite4CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int);
int sqlite4OpenTableAndIndices(Parse*, Table*, int, int);
void sqlite4BeginWriteOperation(Parse*, int, int);
void sqlite4MultiWrite(Parse*);
void sqlite4MayAbort(Parse*);

Changes to src/where.c.

967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
....
2108
2109
2110
2111
2112
2113
2114
2115

2116
2117
2118

2119
2120
2121
2122
2123
2124
2125
....
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
....
2189
2190
2191
2192
2193
2194
2195



2196
2197
2198
2199
2200
2201
2202
....
2236
2237
2238
2239
2240
2241
2242

2243
2244
2245



2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
}

/*
** Return the total number of fields in the index pIdx, including any
** trailing primary key fields.
*/
static int idxColumnCount(Index *pIdx, Index *pPk){
  return (pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn));
}

/*
** Initialize a WHERE clause scanner object.  Return a pointer to the
** first match.  Return NULL if there are no matches.
**
** The scanner will be searching the WHERE clause pWC.  It will look
................................................................................
  int nByte;                  /* Byte of memory needed for pIdx */
  Index *pIdx;                /* Object describing the transient index */
  Vdbe *v;                    /* Prepared statement under construction */
  int addrInit;               /* Address of the initialization bypass jump */
  Table *pTable;              /* The table being indexed */
  KeyInfo *pKeyinfo;          /* Key information for the index */   
  int addrTop;                /* Top of the index fill loop */
  int regRecord;              /* Register holding an index record */

  int n;                      /* Column counter */
  int i;                      /* Loop counter */
  int mxBitCol;               /* Maximum column in pSrc->colUsed */

  CollSeq *pColl;             /* Collating sequence to on a column */
  WhereLoop *pLoop;           /* The Loop object */
  Bitmask idxCols;            /* Bitmap of columns used for indexing */
  Bitmask extraCols;          /* Bitmap of additional columns */
  u8 sentWarning = 0;         /* True if a warnning has been issued */

  /* Generate code to skip over the creation and initialization of the
................................................................................
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
      int iCol = pTerm->u.leftColumn;
      Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
      testcase( iCol==BMS );
      testcase( iCol==BMS-1 );
      if( !sentWarning ){
        sqlite4_log(SQLITE4_WARNING_AUTOINDEX,
            "automatic index on %s(%s)", pTable->zName,
            pTable->aCol[iCol].zName);
        sentWarning = 1;
      }
      if( (idxCols & cMask)==0 ){
        if( whereLoopResize(pParse->db, pLoop, nColumn+1) ) return;
        pLoop->aLTerm[nColumn++] = pTerm;
................................................................................
  pLoop->u.btree.pIndex = pIdx;
  pIdx->azColl = (char**)&pIdx[1];
  pIdx->aiColumn = (int*)&pIdx->azColl[nColumn];
  pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
  pIdx->zName = "auto-index";
  pIdx->nColumn = nColumn;
  pIdx->pTable = pTable;



  n = 0;
  idxCols = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
      int iCol = pTerm->u.leftColumn;
      Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
      testcase( iCol==BMS-1 );
................................................................................
  assert( pLevel->iIdxCur>=0 );
  pLevel->iIdxCur = pParse->nTab++;
  sqlite4VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
                    (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
  VdbeComment((v, "for %s", pTable->zName));

  /* Fill the automatic index with content */

  addrTop = sqlite4VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur);
  regRecord = sqlite4GetTempReg(pParse);
  sqlite4GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1);



  sqlite4VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
  sqlite4VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  sqlite4VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1);
  sqlite4VdbeChangeP5(v, SQLITE4_STMTSTATUS_AUTOINDEX);
  sqlite4VdbeJumpHere(v, addrTop);
  sqlite4ReleaseTempReg(pParse, regRecord);
  
  /* Jump here when skipping the initialization */
  sqlite4VdbeJumpHere(v, addrInit);
}
#endif /* SQLITE4_OMIT_AUTOMATIC_INDEX */

#ifndef SQLITE4_OMIT_VIRTUALTABLE







|







 







|
>



>







 







|







 







>
>
>







 







>
|
|
<
>
>
>
|
|



|







967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
....
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
....
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
....
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
....
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250

2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
}

/*
** Return the total number of fields in the index pIdx, including any
** trailing primary key fields.
*/
static int idxColumnCount(Index *pIdx, Index *pPk){
  return (pIdx->nColumn + ((pPk==0 || pIdx==pPk) ? 0 : pPk->nColumn));
}

/*
** Initialize a WHERE clause scanner object.  Return a pointer to the
** first match.  Return NULL if there are no matches.
**
** The scanner will be searching the WHERE clause pWC.  It will look
................................................................................
  int nByte;                  /* Byte of memory needed for pIdx */
  Index *pIdx;                /* Object describing the transient index */
  Vdbe *v;                    /* Prepared statement under construction */
  int addrInit;               /* Address of the initialization bypass jump */
  Table *pTable;              /* The table being indexed */
  KeyInfo *pKeyinfo;          /* Key information for the index */   
  int addrTop;                /* Top of the index fill loop */
  int regRecord;              /* Register holding an index value */
  int regKey;                 /* Register holding an index key */
  int n;                      /* Column counter */
  int i;                      /* Loop counter */
  int mxBitCol;               /* Maximum column in pSrc->colUsed */
  int iPkCsr;                 /* PK cursor number */
  CollSeq *pColl;             /* Collating sequence to on a column */
  WhereLoop *pLoop;           /* The Loop object */
  Bitmask idxCols;            /* Bitmap of columns used for indexing */
  Bitmask extraCols;          /* Bitmap of additional columns */
  u8 sentWarning = 0;         /* True if a warnning has been issued */

  /* Generate code to skip over the creation and initialization of the
................................................................................
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
      int iCol = pTerm->u.leftColumn;
      Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
      testcase( iCol==BMS );
      testcase( iCol==BMS-1 );
      if( !sentWarning ){
        sqlite4_log(pParse->db->pEnv, SQLITE4_WARNING_AUTOINDEX,
            "automatic index on %s(%s)", pTable->zName,
            pTable->aCol[iCol].zName);
        sentWarning = 1;
      }
      if( (idxCols & cMask)==0 ){
        if( whereLoopResize(pParse->db, pLoop, nColumn+1) ) return;
        pLoop->aLTerm[nColumn++] = pTerm;
................................................................................
  pLoop->u.btree.pIndex = pIdx;
  pIdx->azColl = (char**)&pIdx[1];
  pIdx->aiColumn = (int*)&pIdx->azColl[nColumn];
  pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
  pIdx->zName = "auto-index";
  pIdx->nColumn = nColumn;
  pIdx->pTable = pTable;
  pIdx->aiCover = pIdx->aiColumn;
  pIdx->nCover = pIdx->nColumn;
  pIdx->eIndexType = SQLITE4_INDEX_TEMP;
  n = 0;
  idxCols = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
      int iCol = pTerm->u.leftColumn;
      Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
      testcase( iCol==BMS-1 );
................................................................................
  assert( pLevel->iIdxCur>=0 );
  pLevel->iIdxCur = pParse->nTab++;
  sqlite4VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
                    (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
  VdbeComment((v, "for %s", pTable->zName));

  /* Fill the automatic index with content */
  iPkCsr = pLevel->iTabCur;
  addrTop = sqlite4VdbeAddOp1(v, OP_Rewind, iPkCsr);
  regRecord = sqlite4GetTempRange(pParse, 2);

  regKey = regRecord + 1;
  sqlite4EncodeIndexKey(pParse, 0, iPkCsr, pIdx, pLevel->iIdxCur, 1, regKey);
  sqlite4EncodeIndexValue(pParse, iPkCsr, pIdx, regRecord);
  sqlite4VdbeAddOp3(v, OP_IdxInsert, pLevel->iIdxCur, regRecord, regKey);
  /* sqlite4VdbeChangeP5(v, OPFLAG_USESEEKRESULT); */
  sqlite4VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1);
  sqlite4VdbeChangeP5(v, SQLITE4_STMTSTATUS_AUTOINDEX);
  sqlite4VdbeJumpHere(v, addrTop);
  sqlite4ReleaseTempRange(pParse, regRecord, 2);
  
  /* Jump here when skipping the initialization */
  sqlite4VdbeJumpHere(v, addrInit);
}
#endif /* SQLITE4_OMIT_AUTOMATIC_INDEX */

#ifndef SQLITE4_OMIT_VIRTUALTABLE

Changes to test/autoindex1.test.

18
19
20
21
22
23
24








25
26
27
28
29
30
31
..
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63









64
65
66
67
68
69
70
..
75
76
77
78
79
80
81



82
83
84
85
86
87
88
89
90
91
92
93
94






95
96
97
98
99
100
101
102
103
104
...
140
141
142
143
144
145
146



147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
...
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260


261


























































































































262
# If the library is not compiled with automatic index support then
# skip all tests in this file.
#
ifcapable {!autoindex} {
  finish_test
  return
}









# With automatic index turned off, we do a full scan of the T2 table
do_test autoindex1-100 {
  db eval {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,11);
    INSERT INTO t1 VALUES(2,22);
................................................................................
} {63}
do_test autoindex1-102 {
  db status autoindex
} {0}

# With autoindex turned on, we build an index once and then use that index
# to find T2 values.
#
do_test autoindex1-110 {
  db eval {
    PRAGMA automatic_index=ON;
    SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
  }
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-111 {
  db status step
} {7}
do_test autoindex1-112 {
  db status autoindex
} {7}










# The same test as above, but this time the T2 query is a subquery rather
# than a join.
do_test autoindex1-200 {
  db eval {
    PRAGMA automatic_index=OFF;
    SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
................................................................................
} {35}
do_test autoindex1-202 {
  db status autoindex
} {0}
do_test autoindex1-210 {
  db eval {
    PRAGMA automatic_index=ON;



    SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
  }
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-211 {
  db status step
} {7}
do_test autoindex1-212 {
  db status autoindex
} {7}


# Modify the second table of the join while the join is in progress
#






do_test autoindex1-300 {
  set r {}
  db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} {
    lappend r $b $d
    db eval {UPDATE t2 SET d=d+1}
  }
  set r
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-310 {
  db eval {SELECT d FROM t2 ORDER BY d}
................................................................................
# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_execsql_test autoindex1-500 {
  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);



  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
  0 0 0 {SEARCH TABLE t501 USING PRIMARY KEY (a=?) (~25 rows)} 
  0 0 0 {EXECUTE LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 (~100000 rows)}
}
do_execsql_test autoindex1-501 {
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SCAN TABLE t501 (~500000 rows)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC INDEX (y=?) (~7 rows)}
}
do_execsql_test autoindex1-502 {
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a=123
     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SEARCH TABLE t501 USING PRIMARY KEY (a=?) (~1 rows)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 (~100000 rows)}
}


# The following code checks a performance regression reported on the
# mailing list on 2010-10-19.  The problem is that the nRowEst field
# of ephermeral tables was not being initialized correctly and so no
# automatic index was being created for the emphemeral table when it was
................................................................................
           WHERE prev.flock_no = later.flock_no
           AND later.owner_change_date > prev.owner_change_date
           AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
       ) y ON x.sheep_no = y.sheep_no
   WHERE y.sheep_no IS NULL
   ORDER BY x.registering_flock;
} {
  1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_2 (flock_no=? AND owner_change_date<?) (~2 rows)} 
  1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
  2 0 0 {SEARCH TABLE flock_owner AS later USING INDEX sqlite_autoindex_flock_owner_2 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 
  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC INDEX (sheep_no=?) (~8 rows)}
}


do_execsql_test autoindex1-700 {
  CREATE TABLE t5(a, b, c);
  EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
} {
  0 0 0 {SCAN TABLE t5 (~100000 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}






























































































































finish_test







>
>
>
>
>
>
>
>







 







<












>
>
>
>
>
>
>
>
>







 







>
>
>













>
>
>
>
>
>


|







 







>
>
>




|

|






|

|







|

|







 







|
|

|
|
|







|



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

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
..
52
53
54
55
56
57
58

59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
..
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
...
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
# If the library is not compiled with automatic index support then
# skip all tests in this file.
#
ifcapable {!autoindex} {
  finish_test
  return
}

# Setup for logging 
#db close
#sqlite4_shutdown
#test_sqlite4_log [list lappend ::log]
#set ::log [list]
#sqlite4 db test.db


# With automatic index turned off, we do a full scan of the T2 table
do_test autoindex1-100 {
  db eval {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,11);
    INSERT INTO t1 VALUES(2,22);
................................................................................
} {63}
do_test autoindex1-102 {
  db status autoindex
} {0}

# With autoindex turned on, we build an index once and then use that index
# to find T2 values.

do_test autoindex1-110 {
  db eval {
    PRAGMA automatic_index=ON;
    SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
  }
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-111 {
  db status step
} {7}
do_test autoindex1-112 {
  db status autoindex
} {7}
#do_test autoindex1-113 {
  #set ::log
#} {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}}

#db close
#sqlite4_shutdown
#test_sqlite4_log
#sqlite4_initialize
#sqlite4 db test.db

# The same test as above, but this time the T2 query is a subquery rather
# than a join.
do_test autoindex1-200 {
  db eval {
    PRAGMA automatic_index=OFF;
    SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
................................................................................
} {35}
do_test autoindex1-202 {
  db status autoindex
} {0}
do_test autoindex1-210 {
  db eval {
    PRAGMA automatic_index=ON;
    ANALYZE;
    UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1';
    ANALYZE sqlite_master;
    SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
  }
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-211 {
  db status step
} {7}
do_test autoindex1-212 {
  db status autoindex
} {7}


# Modify the second table of the join while the join is in progress
#
do_execsql_test autoindex1-299 {
  UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2';
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN
  SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a);
} {/AUTOMATIC COVERING INDEX/}
do_test autoindex1-300 {
  set r {}
  db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} {
    lappend r $b $d
    db eval {UPDATE t2 SET d=d+1}
  }
  set r
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-310 {
  db eval {SELECT d FROM t2 ORDER BY d}
................................................................................
# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_execsql_test autoindex1-500 {
  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000');
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000');
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
  0 0 0 {SEARCH TABLE t501 USING INDEX t501 (a=?)} 
  0 0 0 {EXECUTE LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 USING INDEX t502}
}
do_execsql_test autoindex1-501 {
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SCAN TABLE t501 USING INDEX t501} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)}
}
do_execsql_test autoindex1-502 {
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a=123
     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502}
}


# The following code checks a performance regression reported on the
# mailing list on 2010-10-19.  The problem is that the nRowEst field
# of ephermeral tables was not being initialized correctly and so no
# automatic index was being created for the emphemeral table when it was
................................................................................
           WHERE prev.flock_no = later.flock_no
           AND later.owner_change_date > prev.owner_change_date
           AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
       ) y ON x.sheep_no = y.sheep_no
   WHERE y.sheep_no IS NULL
   ORDER BY x.registering_flock;
} {
  1 0 0 {SCAN TABLE sheep AS s} 
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 
  1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
  2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 
  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)}
}


do_execsql_test autoindex1-700 {
  CREATE TABLE t5(a, b, c);
  EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
} {
  0 0 0 {SCAN TABLE t5} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

# The following checks a performance issue reported on the sqlite-dev
# mailing list on 2013-01-10
#
do_execsql_test autoindex1-800 {
  CREATE TABLE accounts(
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_name TEXT,
    account_type TEXT,
    data_set TEXT
  );
  CREATE TABLE data(
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    package_id INTEGER REFERENCES package(_id),
    mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,
    raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,
    is_read_only INTEGER NOT NULL DEFAULT 0,
    is_primary INTEGER NOT NULL DEFAULT 0,
    is_super_primary INTEGER NOT NULL DEFAULT 0,
    data_version INTEGER NOT NULL DEFAULT 0,
    data1 TEXT,
    data2 TEXT,
    data3 TEXT,
    data4 TEXT,
    data5 TEXT,
    data6 TEXT,
    data7 TEXT,
    data8 TEXT,
    data9 TEXT,
    data10 TEXT,
    data11 TEXT,
    data12 TEXT,
    data13 TEXT,
    data14 TEXT,
    data15 TEXT,
    data_sync1 TEXT,
    data_sync2 TEXT,
    data_sync3 TEXT,
    data_sync4 TEXT 
  );
  CREATE TABLE mimetypes(
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    mimetype TEXT NOT NULL
  );
  CREATE TABLE raw_contacts(
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id INTEGER REFERENCES accounts(_id),
    sourceid TEXT,
    raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0,
    version INTEGER NOT NULL DEFAULT 1,
    dirty INTEGER NOT NULL DEFAULT 0,
    deleted INTEGER NOT NULL DEFAULT 0,
    contact_id INTEGER REFERENCES contacts(_id),
    aggregation_mode INTEGER NOT NULL DEFAULT 0,
    aggregation_needed INTEGER NOT NULL DEFAULT 1,
    custom_ringtone TEXT,
    send_to_voicemail INTEGER NOT NULL DEFAULT 0,
    times_contacted INTEGER NOT NULL DEFAULT 0,
    last_time_contacted INTEGER,
    starred INTEGER NOT NULL DEFAULT 0,
    display_name TEXT,
    display_name_alt TEXT,
    display_name_source INTEGER NOT NULL DEFAULT 0,
    phonetic_name TEXT,
    phonetic_name_style TEXT,
    sort_key TEXT,
    sort_key_alt TEXT,
    name_verified INTEGER NOT NULL DEFAULT 0,
    sync1 TEXT,
    sync2 TEXT,
    sync3 TEXT,
    sync4 TEXT,
    sync_uid TEXT,
    sync_version INTEGER NOT NULL DEFAULT 1,
    has_calendar_event INTEGER NOT NULL DEFAULT 0,
    modified_time INTEGER,
    is_restricted INTEGER DEFAULT 0,
    yp_source TEXT,
    method_selected INTEGER DEFAULT 0,
    custom_vibration_type INTEGER DEFAULT 0,
    custom_ringtone_path TEXT,
    message_notification TEXT,
    message_notification_path TEXT
  );
  CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);
  CREATE INDEX data_raw_contact_id ON data (raw_contact_id);
  CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
  CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key);
  CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt);
  CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id);
  CREATE INDEX raw_contacts_source_id_account_id_index
      ON raw_contacts (sourceid, account_id);
  ANALYZE sqlite_master;
  INSERT INTO sqlite_stat1
     VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4');
  INSERT INTO sqlite_stat1
     VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
  INSERT INTO sqlite_stat1
     VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
            '1600 1600 1600');
  INSERT INTO sqlite_stat1
     VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');
  INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');
  INSERT INTO sqlite_stat1
     VALUES('data','data_mimetype_data1_index','9819 2455 3');
  INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');
  INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1');
  DROP TABLE IF EXISTS sqlite_stat3;
  ANALYZE sqlite_master;
  
  EXPLAIN QUERY PLAN
  SELECT * FROM 
        data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 
             JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 
             JOIN accounts ON (raw_contacts.account_id=accounts._id)
   WHERE mimetype_id=10 AND data14 IS NOT NULL;
} {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/}
do_execsql_test autoindex1-801 {
  EXPLAIN QUERY PLAN
  SELECT * FROM 
        data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 
             JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 
             JOIN accounts ON (raw_contacts.account_id=accounts._id)
   WHERE mimetypes._id=10 AND data14 IS NOT NULL;
} {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/}

finish_test

Changes to test/collate2.test.

637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
# Test that when one side has a default collation type and the other
# does not, the collation type is used.
do_test collate2-4.3 {
  execsql {
    SELECT collate2t1.a FROM collate2t1, collate2t3 
      WHERE collate2t1.b = collate2t3.b||'';
  }
} {aa aA Aa AA}
do_test collate2-4.4 {
  execsql {
    SELECT collate2t1.a FROM collate2t1, collate2t3 
      WHERE collate2t3.b||'' = collate2t1.b;
  }
} {aa aA Aa AA}

do_test collate2-4.5 {
  execsql {
    DROP TABLE collate2t3;
  }
} {}








|





|







637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
# Test that when one side has a default collation type and the other
# does not, the collation type is used.
do_test collate2-4.3 {
  execsql {
    SELECT collate2t1.a FROM collate2t1, collate2t3 
      WHERE collate2t1.b = collate2t3.b||'';
  }
} {AA Aa aA aa}
do_test collate2-4.4 {
  execsql {
    SELECT collate2t1.a FROM collate2t1, collate2t3 
      WHERE collate2t3.b||'' = collate2t1.b;
  }
} {AA Aa aA aa}

do_test collate2-4.5 {
  execsql {
    DROP TABLE collate2t3;
  }
} {}