Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -1248,22 +1248,20 @@ ** The first argument points to a nul-terminated string containing a ** list of space separated integers. Read the first nOut of these into ** the array aOut[]. */ static void decodeIntArray( - char *zIntArray, - int nOut, - tRowcnt *aOut, - int *pbUnordered + char *zIntArray, /* String containing int array to decode */ + int nOut, /* Number of slots in aOut[] */ + tRowcnt *aOut, /* Store integers here */ + Index *pIndex /* Handle extra flags for this index, if not NULL */ ){ char *z = zIntArray; int c; int i; tRowcnt v; - assert( pbUnordered==0 || *pbUnordered==0 ); - #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 if( z==0 ) z = ""; #else if( NEVER(z==0) ) z = ""; #endif @@ -1274,12 +1272,25 @@ z++; } aOut[i] = v; if( *z==' ' ) z++; } - if( pbUnordered && strcmp(z, "unordered")==0 ){ - *pbUnordered = 1; + if( pIndex ){ + if( strcmp(z, "unordered")==0 ){ + pIndex->bUnordered = 1; + }else if( sqlite3_strglob("r=[0-9]*", z)==0 ){ + int v32 = 0; + sqlite3GetInt32(z+2, &v32); + if( v32>=200 ){ + v32 = 255; + }else if( v32<=0 ){ + v32 = 1; + }else{ + v32 = (128*v32)/100; + } + pIndex->iScanRatio = (u8)v32; + } } } /* ** This callback is invoked once for each index when reading the @@ -1314,14 +1325,12 @@ pIndex = 0; } z = argv[2]; if( pIndex ){ - int bUnordered = 0; - decodeIntArray((char*)z, pIndex->nColumn+1, pIndex->aiRowEst,&bUnordered); + decodeIntArray((char*)z, pIndex->nColumn+1, pIndex->aiRowEst, pIndex); if( pIndex->pPartIdxWhere==0 ) pTable->nRowEst = pIndex->aiRowEst[0]; - pIndex->bUnordered = bUnordered; }else{ decodeIntArray((char*)z, 1, &pTable->nRowEst, 0); } return 0; Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -1024,10 +1024,11 @@ /* If there is no type specified, columns have the default affinity ** 'NONE'. If there is a type specified, then sqlite3AddColumnType() will ** be called next to set pCol->affinity correctly. */ pCol->affinity = SQLITE_AFF_NONE; + pCol->szEst = 1; p->nCol++; } /* ** This routine is called by the parser while in the middle of @@ -1065,26 +1066,30 @@ ** 'DOUB' | SQLITE_AFF_REAL ** ** If none of the substrings in the above table are found, ** SQLITE_AFF_NUMERIC is returned. */ -char sqlite3AffinityType(const char *zIn){ +char sqlite3AffinityType(const char *zIn, u8 *pszEst){ u32 h = 0; char aff = SQLITE_AFF_NUMERIC; + const char *zChar = 0; - if( zIn ) while( zIn[0] ){ + if( zIn==0 ) return aff; + while( zIn[0] ){ h = (h<<8) + sqlite3UpperToLower[(*zIn)&0xff]; zIn++; if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){ /* CHAR */ - aff = SQLITE_AFF_TEXT; + aff = SQLITE_AFF_TEXT; + zChar = zIn; }else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){ /* CLOB */ aff = SQLITE_AFF_TEXT; }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){ /* TEXT */ aff = SQLITE_AFF_TEXT; }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b') /* BLOB */ && (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){ aff = SQLITE_AFF_NONE; + if( zIn[0]=='(' ) zChar = zIn; #ifndef SQLITE_OMIT_FLOATING_POINT }else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l') /* REAL */ && aff==SQLITE_AFF_NUMERIC ){ aff = SQLITE_AFF_REAL; }else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a') /* FLOA */ @@ -1098,10 +1103,32 @@ aff = SQLITE_AFF_INTEGER; break; } } + /* If pszEst is not NULL, store an estimate of the field size. The + ** estimate is scaled so that the size of an integer is 1. */ + if( pszEst ){ + *pszEst = 1; /* default size is approx 4 bytes */ + if( aff<=SQLITE_AFF_NONE ){ + if( zChar ){ + while( zChar[0] ){ + if( sqlite3Isdigit(zChar[0]) ){ + int v; + sqlite3GetInt32(zChar, &v); + v = v/4 + 1; + if( v>255 ) v = 255; + *pszEst = v; /* BLOB(k), VARCHAR(k), CHAR(k) -> r=(k/4+1) */ + break; + } + zChar++; + } + }else{ + *pszEst = 5; /* BLOB, TEXT, CLOB -> r=5 (approx 20 bytes)*/ + } + } + } return aff; } /* ** This routine is called by the parser while in the middle of @@ -1119,11 +1146,11 @@ p = pParse->pNewTable; if( p==0 || NEVER(p->nCol<1) ) return; pCol = &p->aCol[p->nCol-1]; assert( pCol->zType==0 ); pCol->zType = sqlite3NameFromToken(pParse->db, pType); - pCol->affinity = sqlite3AffinityType(pCol->zType); + pCol->affinity = sqlite3AffinityType(pCol->zType, &pCol->szEst); } /* ** The expression is the default value for the most recently added column ** of the table currently under construction. @@ -1467,18 +1494,51 @@ testcase( pCol->affinity==SQLITE_AFF_REAL ); zType = azType[pCol->affinity - SQLITE_AFF_TEXT]; len = sqlite3Strlen30(zType); assert( pCol->affinity==SQLITE_AFF_NONE - || pCol->affinity==sqlite3AffinityType(zType) ); + || pCol->affinity==sqlite3AffinityType(zType, 0) ); memcpy(&zStmt[k], zType, len); k += len; assert( k<=n ); } sqlite3_snprintf(n-k, &zStmt[k], "%s", zEnd); return zStmt; } + +/* +** Estimate the total row width for a table. +*/ +static unsigned estimatedTableWidth(const Table *pTab){ + unsigned wTable = 0; + const Column *pTabCol; + int i; + for(i=pTab->nCol, pTabCol=pTab->aCol; i>0; i--, pTabCol++){ + wTable += pTabCol->szEst; + } + if( pTab->iPKey<0 ) wTable++; + return wTable; +} + +/* +** Set the iScanRatio for an index based on estimates of the average +** table row width and average index row width. Estimates are derived +** from the declared datatypes of the various columns. +*/ +static void setIndexScanRatio(Index *pIdx, unsigned wTable){ + unsigned wIndex = 1; + int i; + const Column *aCol = pIdx->pTable->aCol; + for(i=0; inColumn; i++){ + assert( pIdx->aiColumn[i]>=0 && pIdx->aiColumn[i]pTable->nCol ); + wIndex += aCol[pIdx->aiColumn[i]].szEst; + } + assert( 100*wIndex/wTable <= 255 ); + pIdx->iScanRatio = (u8)(128*wIndex/wTable); + /* printf("%s: wIndex=%d wTable=%d ratio=%d\n", + ** pIdx->zName, wIndex, wTable, (100*pIdx->iScanRatio)/128); */ +} /* ** This routine is called to report the final ")" that terminates ** a CREATE TABLE statement. ** @@ -1502,13 +1562,15 @@ Parse *pParse, /* Parse context */ Token *pCons, /* The ',' token after the last column defn. */ Token *pEnd, /* The final ')' token in the CREATE TABLE */ Select *pSelect /* Select from a "CREATE ... AS SELECT" */ ){ - Table *p; - sqlite3 *db = pParse->db; - int iDb; + Table *p; /* The new table */ + sqlite3 *db = pParse->db; /* The database connection */ + int iDb; /* Database in which the table lives */ + Index *pIdx; /* An implied index of the table */ + unsigned wTable; /* Estimated average width of a row in the table */ if( (pEnd==0 && pSelect==0) || db->mallocFailed ){ return; } p = pParse->pNewTable; @@ -1523,10 +1585,16 @@ */ if( p->pCheck ){ sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck); } #endif /* !defined(SQLITE_OMIT_CHECK) */ + + /* Compute the iScanRatio of implied indices */ + wTable = estimatedTableWidth(p); + for(pIdx=p->pIndex; pIdx; pIdx=pIdx->pNext){ + setIndexScanRatio(pIdx, wTable); + } /* If the db->init.busy is 1 it means we are reading the SQL off the ** "sqlite_master" or "sqlite_temp_master" table on the disk. ** So do not write to the disk again. Extract the root page number ** for the table from the db->init.newTnum field. (The page number @@ -2482,13 +2550,14 @@ sqlite3 *db = pParse->db; Db *pDb; /* The specific table containing the indexed database */ int iDb; /* Index of the database that is being written */ Token *pName = 0; /* Unqualified name of the index to create */ struct ExprList_item *pListItem; /* For looping over pList */ - int nCol; - int nExtra = 0; - char *zExtra; + const Column *pTabCol; /* A column in the table */ + int nCol; /* Number of columns */ + int nExtra = 0; /* Space allocated for zExtra[] */ + char *zExtra; /* Extra space after the Index object */ assert( pParse->nErr==0 ); /* Never called with prior errors */ if( db->mallocFailed || IN_DECLARE_VTAB ){ goto exit_create_index; } @@ -2711,11 +2780,10 @@ ** same column more than once cannot be an error because that would ** break backwards compatibility - it needs to be a warning. */ for(i=0, pListItem=pList->a; inExpr; i++, pListItem++){ const char *zColName = pListItem->zName; - Column *pTabCol; int requestedSortOrder; char *zColl; /* Collation sequence name */ for(j=0, pTabCol=pTab->aCol; jnCol; j++, pTabCol++){ if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break; @@ -2748,10 +2816,13 @@ requestedSortOrder = pListItem->sortOrder & sortOrderMask; pIndex->aSortOrder[i] = (u8)requestedSortOrder; if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0; } sqlite3DefaultRowEst(pIndex); + if( pParse->pNewTable==0 ){ + setIndexScanRatio(pIndex, estimatedTableWidth(pTab)); + } if( pTab==pParse->pNewTable ){ /* This routine has been called to create an automatic index as a ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or ** a PRIMARY KEY or UNIQUE clause following the column definitions. Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -39,11 +39,11 @@ return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr); } #ifndef SQLITE_OMIT_CAST if( op==TK_CAST ){ assert( !ExprHasProperty(pExpr, EP_IntValue) ); - return sqlite3AffinityType(pExpr->u.zToken); + return sqlite3AffinityType(pExpr->u.zToken, 0); } #endif if( (op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_REGISTER) && pExpr->pTab!=0 ){ @@ -2459,11 +2459,11 @@ case TK_CAST: { /* Expressions of the form: CAST(pLeft AS token) */ int aff, to_op; inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target); assert( !ExprHasProperty(pExpr, EP_IntValue) ); - aff = sqlite3AffinityType(pExpr->u.zToken); + aff = sqlite3AffinityType(pExpr->u.zToken, 0); to_op = aff - SQLITE_AFF_TEXT + OP_ToText; assert( to_op==OP_ToText || aff!=SQLITE_AFF_TEXT ); assert( to_op==OP_ToBlob || aff!=SQLITE_AFF_NONE ); assert( to_op==OP_ToNumeric || aff!=SQLITE_AFF_NUMERIC ); assert( to_op==OP_ToInt || aff!=SQLITE_AFF_INTEGER ); @@ -3126,11 +3126,11 @@ } #ifndef SQLITE_OMIT_CAST case TK_CAST: { /* Expressions of the form: CAST(pLeft AS token) */ const char *zAff = "unk"; - switch( sqlite3AffinityType(pExpr->u.zToken) ){ + switch( sqlite3AffinityType(pExpr->u.zToken, 0) ){ case SQLITE_AFF_TEXT: zAff = "TEXT"; break; case SQLITE_AFF_NONE: zAff = "NONE"; break; case SQLITE_AFF_NUMERIC: zAff = "NUMERIC"; break; case SQLITE_AFF_INTEGER: zAff = "INTEGER"; break; case SQLITE_AFF_REAL: zAff = "REAL"; break; Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1453,21 +1453,23 @@ if( pTab ){ v = sqlite3GetVdbe(pParse); pIdx = pTab->pIndex; if( pIdx ){ int i = 0; - sqlite3VdbeSetNumCols(v, 3); - pParse->nMem = 3; + sqlite3VdbeSetNumCols(v, 4); + pParse->nMem = 4; sqlite3CodeVerifySchema(pParse, iDb); sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", SQLITE_STATIC); sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC); sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC); + sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "r", SQLITE_STATIC); while(pIdx){ sqlite3VdbeAddOp2(v, OP_Integer, i, 1); sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0); sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3); - sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3); + sqlite3VdbeAddOp2(v, OP_Integer, (pIdx->iScanRatio*100+127)/128, 4); + sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4); ++i; pIdx = pIdx->pNext; } } } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -4595,17 +4595,11 @@ int iRoot = pTab->tnum; /* Root page of scanned b-tree */ sqlite3CodeVerifySchema(pParse, iDb); sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); - /* Search for the index that has the least amount of columns. If - ** there is such an index, and it has less columns than the table - ** does, then we can assume that it consumes less space on disk and - ** will therefore be cheaper to scan to determine the query result. - ** In this case set iRoot to the root page number of the index b-tree - ** and pKeyInfo to the KeyInfo structure required to navigate the - ** index. + /* Search for the index that has the lowest scan cost. ** ** (2011-04-15) Do not do a full scan of an unordered index. ** ** (2013-10-03) Do not count the entires in a partial index. ** @@ -4612,17 +4606,18 @@ ** In practice the KeyInfo structure will not be used. It is only ** passed to keep OP_OpenRead happy. */ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->bUnordered==0 + && pIdx->iScanRatio<128 && pIdx->pPartIdxWhere==0 - && (!pBest || pIdx->nColumnnColumn) + && (!pBest || pIdx->iScanRatioiScanRatio) ){ pBest = pIdx; } } - if( pBest && pBest->nColumnnCol ){ + if( pBest ){ iRoot = pBest->tnum; pKeyInfo = sqlite3IndexKeyinfo(pParse, pBest); } /* Open a read-only cursor, execute the OP_Count, close the cursor. */ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1195,11 +1195,12 @@ char *zDflt; /* Original text of the default value */ char *zType; /* Data type for this column */ char *zColl; /* Collating sequence. If NULL, use the default */ u8 notNull; /* An OE_ code for handling a NOT NULL constraint */ char affinity; /* One of the SQLITE_AFF_... values */ - u16 colFlags; /* Boolean properties. See COLFLAG_ defines below */ + u8 szEst; /* Estimated size of this column. INT==1 */ + u8 colFlags; /* Boolean properties. See COLFLAG_ defines below */ }; /* Allowed values for Column.colFlags: */ #define COLFLAG_PRIMKEY 0x0001 /* Column is part of the primary key */ @@ -1470,11 +1471,11 @@ #define OE_Restrict 6 /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */ #define OE_SetNull 7 /* Set the foreign key value to NULL */ #define OE_SetDflt 8 /* Set the foreign key value to its default */ #define OE_Cascade 9 /* Cascade the changes */ -#define OE_Default 99 /* Do whatever the default action is */ +#define OE_Default 10 /* Do whatever the default action is */ /* ** An instance of the following structure is passed as the first ** argument to sqlite3VdbeKeyCompare and is used to control the @@ -1558,11 +1559,12 @@ u8 *aSortOrder; /* for each column: True==DESC, False==ASC */ char **azColl; /* Array of collation sequence names for index */ Expr *pPartIdxWhere; /* WHERE clause for partial indices */ int tnum; /* DB Page containing root of this index */ u16 nColumn; /* Number of columns in table used by this index */ - u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ + u8 iScanRatio; /* Scan rate relative to the main table */ + unsigned onError:4; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ unsigned autoIndex:2; /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nSample; /* Number of elements in aSample[] */ @@ -3079,11 +3081,11 @@ int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*); void sqlite3ColumnDefault(Vdbe *, Table *, int, int); void sqlite3AlterFinishAddColumn(Parse *, Token *); void sqlite3AlterBeginAddColumn(Parse *, SrcList *); CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*); -char sqlite3AffinityType(const char*); +char sqlite3AffinityType(const char*, u8*); void sqlite3Analyze(Parse*, Token*, Token*); int sqlite3InvokeBusyHandler(BusyHandler*); int sqlite3FindDb(sqlite3*, Token*); int sqlite3FindDbName(sqlite3 *, const char *); int sqlite3AnalysisLoad(sqlite3*,int iDB); Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -4667,14 +4667,12 @@ pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; /* TUNING: Cost of full table scan is 3*(N + log2(N)). - ** + The extra 3 factor is to encourage the use of indexed lookups - ** over full scans. A smaller constant 2 is used for covering - ** index scans so that a covering index scan will be favored over - ** a table scan. */ + ** + The extra 4 factor is to encourage the use of indexed lookups + ** over full scans. */ pNew->rRun = whereCostAdd(rSize,rLogSize) + 16; whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor); rc = whereLoopInsert(pBuilder, pNew); pNew->nOut = rSize; if( rc ) break; @@ -4684,26 +4682,24 @@ /* Full scan via index */ if( b || ( m==0 && pProbe->bUnordered==0 + && pProbe->iScanRatio<128 && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 && sqlite3GlobalConfig.bUseCis && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan) ) ){ pNew->iSortIdx = b ? iSortIdx : 0; if( m==0 ){ - /* TUNING: Cost of a covering index scan is 2*(N + log2(N)). - ** + The extra 2 factor is to encourage the use of indexed lookups - ** over index scans. A table scan uses a factor of 3 so that - ** index scans are favored over table scans. - ** + If this covering index might also help satisfy the ORDER BY - ** clause, then the cost is fudged down slightly so that this - ** index is favored above other indices that have no hope of - ** helping with the ORDER BY. */ - pNew->rRun = 10 + whereCostAdd(rSize,rLogSize) - b; + /* TUNING: Cost of a covering index scan is K*(N + log2(N)). + ** + The extra factor K of between 1.0 and 3.0 is added to + ** encourage the use of indexed lookups. The value of K + ** depends on the iScanRatio value for the index. + */ + pNew->rRun = whereCostAdd(rSize,rLogSize) + pProbe->iScanRatio/9 + 1; }else{ assert( b!=0 ); /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N) ** which we will simplify to just N*log2(N) */ pNew->rRun = rSize + rLogSize; Index: test/analyze6.test ================================================================== --- test/analyze6.test +++ test/analyze6.test @@ -28,18 +28,18 @@ uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db } do_test analyze6-1.0 { db eval { - CREATE TABLE cat(x INT); + CREATE TABLE cat(x INT, yz TEXT); CREATE UNIQUE INDEX catx ON cat(x); /* Give cat 16 unique integers */ - INSERT INTO cat VALUES(1); - INSERT INTO cat VALUES(2); - INSERT INTO cat SELECT x+2 FROM cat; - INSERT INTO cat SELECT x+4 FROM cat; - INSERT INTO cat SELECT x+8 FROM cat; + INSERT INTO cat(x) VALUES(1); + INSERT INTO cat(x) VALUES(2); + INSERT INTO cat(x) SELECT x+2 FROM cat; + INSERT INTO cat(x) SELECT x+4 FROM cat; + INSERT INTO cat(x) SELECT x+8 FROM cat; CREATE TABLE ev(y INT); CREATE INDEX evy ON ev(y); /* ev will hold 32 copies of 16 integers found in cat */ INSERT INTO ev SELECT x FROM cat; Index: test/eqp.test ================================================================== --- test/eqp.test +++ test/eqp.test @@ -31,15 +31,15 @@ # proc det {args} { uplevel do_eqp_test $args } do_execsql_test 1.1 { - CREATE TABLE t1(a, b); + CREATE TABLE t1(a INT, b INT, ex TEXT); CREATE INDEX i1 ON t1(a); CREATE INDEX i2 ON t1(b); - CREATE TABLE t2(a, b); - CREATE TABLE t3(a, b); + CREATE TABLE t2(a INT, b INT, ex TEXT); + CREATE TABLE t3(a INT, b INT, ex TEXT); } do_eqp_test 1.2 { SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; } { @@ -120,13 +120,13 @@ #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { - CREATE TABLE t1(x, y); + CREATE TABLE t1(x INT, y INT, ex TEXT); - CREATE TABLE t2(x, y); + CREATE TABLE t2(x INT, y INT, ex TEXT); CREATE INDEX t2i1 ON t2(x); } det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" { 0 0 0 {SCAN TABLE t1} @@ -372,11 +372,11 @@ # EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b # FROM t1 WHERE a=1; # 0|0|0|SCAN TABLE t1 # -do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) } +do_execsql_test 5.1.0 { CREATE TABLE t1(a INT, b INT, ex TEXT) } det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { 0 0 0 {SCAN TABLE t1} } # EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a); @@ -400,22 +400,22 @@ # EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN # SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) # 0|1|1|SCAN TABLE t2 # -do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} -det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { +do_execsql_test 5.4.0 {CREATE TABLE t2(c INT, d INT, ex TEXT)} +det 5.4.1 "SELECT t1.a, t2.c FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 0 1 1 {SCAN TABLE t2} } # EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN # SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; # 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) # 0|1|0|SCAN TABLE t2 # -det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { +det 5.5 "SELECT t1.a, t2.c FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 0 1 0 {SCAN TABLE t2} } # EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b); @@ -422,11 +422,11 @@ # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) # do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} -det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" { +det 5.6.1 "SELECT a, b FROM t1 WHERE a=1 OR b=2" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} } # EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN @@ -483,11 +483,11 @@ # EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN # SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1; # 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) # 0|1|1|SCAN TABLE t1 # -det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { +det 5.11 "SELECT a, b FROM (SELECT * FROM t2 WHERE c=1), t1" { 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)} 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2} } # EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN @@ -494,11 +494,11 @@ # SELECT a FROM t1 UNION SELECT c FROM t2; # 1|0|0|SCAN TABLE t1 # 2|0|0|SCAN TABLE t2 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) # -det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { +det 5.12 "SELECT a,b FROM t1 UNION SELECT c, 99 FROM t2" { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } @@ -507,11 +507,11 @@ # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 # 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) # det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { - 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} } @@ -546,11 +546,11 @@ set data }] [list $res] } do_peqp_test 6.1 { - SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1 + SELECT a, b FROM t1 EXCEPT SELECT d, 99 FROM t2 ORDER BY 1 } [string trimleft { 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 2 0 0 SCAN TABLE t2 2 0 0 USE TEMP B-TREE FOR ORDER BY 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) @@ -561,12 +561,12 @@ # optimization return something sensible with EQP. # drop_all_tables do_execsql_test 7.0 { - CREATE TABLE t1(a, b); - CREATE TABLE t2(a, b); + CREATE TABLE t1(a INT, b INT, ex CHAR(100)); + CREATE TABLE t2(a INT, b INT, ex CHAR(100)); CREATE INDEX i1 ON t2(a); } det 7.1 "SELECT count(*) FROM t1" { 0 0 0 {SCAN TABLE t1} @@ -575,16 +575,16 @@ det 7.2 "SELECT count(*) FROM t2" { 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} } do_execsql_test 7.3 { - INSERT INTO t1 VALUES(1, 2); - INSERT INTO t1 VALUES(3, 4); + INSERT INTO t1(a,b) VALUES(1, 2); + INSERT INTO t1(a,b) VALUES(3, 4); - INSERT INTO t2 VALUES(1, 2); - INSERT INTO t2 VALUES(3, 4); - INSERT INTO t2 VALUES(5, 6); + INSERT INTO t2(a,b) VALUES(1, 2); + INSERT INTO t2(a,b) VALUES(3, 4); + INSERT INTO t2(a,b) VALUES(5, 6); ANALYZE; } db close Index: test/pragma.test ================================================================== --- test/pragma.test +++ test/pragma.test @@ -572,11 +572,11 @@ } {} do_test pragma-6.4 { execsql { pragma index_list(t3); } - } {0 sqlite_autoindex_t3_1 1} + } {/0 sqlite_autoindex_t3_1 1 \d+/} } ifcapable {!foreignkey} { execsql {CREATE TABLE t3(a,b UNIQUE)} } do_test pragma-6.5.1 { @@ -645,11 +645,11 @@ db close sqlite3 db test.db execsql { pragma index_list(t3); } -} {0 t3i1 0 1 sqlite_autoindex_t3_1 1} +} {/0 t3i1 0 \d+ 1 sqlite_autoindex_t3_1 1 \d+/} do_test pragma-7.1.2 { execsql { pragma index_list(t3_bogus); } } {} @@ -1659,11 +1659,11 @@ do_test 23.3 { db eval { CREATE INDEX i3 ON t1(d,b,c); } db2 eval {PRAGMA index_list(t1)} -} {0 i3 0 1 i2 0 2 i1 0} +} {/0 i3 0 \d+ 1 i2 0 \d+ 2 i1 0 \d+/} do_test 23.4 { db eval { ALTER TABLE t1 ADD COLUMN e; } db2 eval { Index: test/subquery.test ================================================================== --- test/subquery.test +++ test/subquery.test @@ -243,11 +243,11 @@ # Verify that the t4i index was not used in the previous query execsql { EXPLAIN QUERY PLAN SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); } -} {/SCAN TABLE t4 /} +} {~/t4i/} do_test subquery-2.5.4 { execsql { DROP TABLE t3; DROP TABLE t4; } Index: test/tkt-78e04e52ea.test ================================================================== --- test/tkt-78e04e52ea.test +++ test/tkt-78e04e52ea.test @@ -16,35 +16,35 @@ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tkt-78e04-1.0 { execsql { - CREATE TABLE ""("" UNIQUE); + CREATE TABLE ""("" UNIQUE, x CHAR(100)); CREATE TABLE t2(x); - INSERT INTO "" VALUES(1); + INSERT INTO ""("") VALUES(1); INSERT INTO t2 VALUES(2); SELECT * FROM "", t2; } -} {1 2} +} {1 {} 2} do_test tkt-78e04-1.1 { catchsql { - INSERT INTO "" VALUES(1); + INSERT INTO ""("") VALUES(1); } } {1 {column is not unique}} do_test tkt-78e04-1.2 { execsql { PRAGMA table_info(""); } -} {0 {} {} 0 {} 0} +} {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0} do_test tkt-78e04-1.3 { execsql { CREATE INDEX i1 ON ""("" COLLATE nocase); } } {} do_test tkt-78e04-1.4 { execsql { - EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%'; + EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE 'abc%'; } } {0 0 0 {SCAN TABLE USING COVERING INDEX i1}} do_test tkt-78e04-1.5 { execsql { DROP TABLE ""; Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -1123,13 +1123,13 @@ # of the ORDER BY clause do not reference other tables in a join. # if {[permutation] != "no_optimization"} { do_test where-14.1 { execsql { - CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE); - INSERT INTO t8 VALUES(1,'one'); - INSERT INTO t8 VALUES(4,'four'); + CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c CHAR(100)); + INSERT INTO t8(a,b) VALUES(1,'one'); + INSERT INTO t8(a,b) VALUES(4,'four'); } cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b } } {1/4 1/1 4/4 4/1 nosort} Index: test/where2.test ================================================================== --- test/where2.test +++ test/where2.test @@ -312,68 +312,68 @@ # Ticket #2249. Make sure the OR optimization is not attempted if # comparisons between columns of different affinities are needed. # do_test where2-6.7 { execsql { - CREATE TABLE t2249a(a TEXT UNIQUE); + CREATE TABLE t2249a(a TEXT UNIQUE, x CHAR(100)); CREATE TABLE t2249b(b INTEGER); - INSERT INTO t2249a VALUES('0123'); + INSERT INTO t2249a(a) VALUES('0123'); INSERT INTO t2249b VALUES(123); } queryplan { -- Because a is type TEXT and b is type INTEGER, both a and b -- will attempt to convert to NUMERIC before the comparison. -- They will thus compare equal. -- - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b; } } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.9 { queryplan { -- The + operator removes affinity from the rhs. No conversions -- occur and the comparison is false. The result is an empty set. -- - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b; } } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.9.2 { # The same thing but with the expression flipped around. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a } } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.10 { queryplan { -- Use + on both sides of the comparison to disable indices -- completely. Make sure we get the same result. -- - SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b; } } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11 { # This will not attempt the OR optimization because of the a=b # comparison. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello'; } } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.2 { # Permutations of the expression terms. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; } } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.3 { # Permutations of the expression terms. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; } } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.11.4 { # Permutations of the expression terms. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; } } {123 0123 nosort t2249b * t2249a sqlite_autoindex_t2249a_1} ifcapable explain&&subquery { # These tests are not run if subquery support is not included in the # build. This is because these tests test the "a = 1 OR a = 2" to @@ -383,43 +383,43 @@ do_test where2-6.12 { # In this case, the +b disables the affinity conflict and allows # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; } } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.12.2 { # In this case, the +b disables the affinity conflict and allows # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; } } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.12.3 { # In this case, the +b disables the affinity conflict and allows # the OR optimization to be used again. The result is now an empty # set, the same as in where2-6.9. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; } } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} do_test where2-6.13 { # The addition of +a on the second term disabled the OR optimization. # But we should still get the same empty-set result as in where2-6.9. queryplan { - SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello'; + SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello'; } } {nosort t2249b * t2249a sqlite_autoindex_t2249a_1} } # Variations on the order of terms in a WHERE clause in order # to make sure the OR optimizer can recognize them all. do_test where2-6.20 { queryplan { - SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a + SELECT x.a, y.a FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a } } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} ifcapable explain&&subquery { # These tests are not run if subquery support is not included in the # build. This is because these tests test the "a = 1 OR a = 2" to @@ -426,21 +426,24 @@ # "a IN (1, 2)" optimisation transformation, which is not enabled if # subqueries and the IN operator is not available. # do_test where2-6.21 { queryplan { - SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello' + SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y + WHERE x.a=y.a OR y.a='hello' } } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} do_test where2-6.22 { queryplan { - SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello' + SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y + WHERE y.a=x.a OR y.a='hello' } } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} do_test where2-6.23 { queryplan { - SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a + SELECT x.a,y.a FROM t2249a x CROSS JOIN t2249a y + WHERE y.a='hello' OR x.a=y.a } } {0123 0123 nosort x sqlite_autoindex_t2249a_1 y sqlite_autoindex_t2249a_1} } # Unique queries (queries that are guaranteed to return only a single @@ -701,11 +704,11 @@ # Verify that the OR clause is used in an outer loop even when # the OR clause scores slightly better on an inner loop. if {[permutation] != "no_optimization"} { do_execsql_test where2-12.1 { - CREATE TABLE t12(x INTEGER PRIMARY KEY, y); + CREATE TABLE t12(x INTEGER PRIMARY KEY, y INT, z CHAR(100)); CREATE INDEX t12y ON t12(y); EXPLAIN QUERY PLAN SELECT a.x, b.x FROM t12 AS a JOIN t12 AS b ON a.y=b.x WHERE (b.x=$abc OR b.y=$abc);