Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch index-scan-rate Excluding Merge-Ins
This is equivalent to a diff from 087af29e to de78250a
2013-10-06
| ||
22:52 | Remove an incorrect debugging assert() that was accidently added during the STAT4 enhancement. (check-in: 2bb7f74b user: drh tags: trunk) | |
2013-10-05
| ||
18:16 | Begin an experimental refactoring to estimate the average number of bytes in table and index rows and to use that information in query planner. Begin by renaming WhereCost to LogEst and making that type and its conversion routines available outside of where.c. (check-in: 66c4a251 user: drh tags: row-size-est) | |
02:56 | In the index_list pragma, make sure the "r" column is the same on output as it was on input in the sqlite_stat1 table. (Closed-Leaf check-in: de78250a user: drh tags: index-scan-rate) | |
2013-10-04
| ||
20:39 | Merge trunk changes. (check-in: c6ac80ed user: drh tags: index-scan-rate) | |
18:17 | If an "INSERT INTO ... SELECT" can use the xfer optimization, pass the OPFLAG_BULKCSR hint to btree cursors used to update indices. This results in a tighter key packing. (check-in: 087af29e user: dan tags: trunk) | |
00:00 | Make sure the count(*) optimization works correctly even when partial indices are present. Ticket [a5c8ed66cae]. (check-in: 9f2f4c0a user: drh tags: trunk) | |
Changes to src/analyze.c.
︙ | ︙ | |||
1246 1247 1248 1249 1250 1251 1252 | /* ** 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( | | | | | < < > | | > > > > > > > > > > > > | 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 | /* ** 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, /* 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; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 if( z==0 ) z = ""; #else if( NEVER(z==0) ) z = ""; #endif for(i=0; *z && i<nOut; i++){ v = 0; while( (c=z[0])>='0' && c<='9' ){ v = v*10 + c - '0'; z++; } aOut[i] = v; if( *z==' ' ) z++; } 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 ** sqlite_stat1 table. ** |
︙ | ︙ | |||
1312 1313 1314 1315 1316 1317 1318 | pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); }else{ pIndex = 0; } z = argv[2]; if( pIndex ){ | < | < | 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 | pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); }else{ pIndex = 0; } z = argv[2]; if( pIndex ){ decodeIntArray((char*)z, pIndex->nColumn+1, pIndex->aiRowEst, pIndex); if( pIndex->pPartIdxWhere==0 ) pTable->nRowEst = pIndex->aiRowEst[0]; }else{ decodeIntArray((char*)z, 1, &pTable->nRowEst, 0); } return 0; } |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 | pCol->zName = z; /* 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; p->nCol++; } /* ** This routine is called by the parser while in the middle of ** parsing a CREATE TABLE statement. A "NOT NULL" constraint has ** been seen on a column. This routine sets the notNull flag on | > | 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 | pCol->zName = z; /* 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 ** parsing a CREATE TABLE statement. A "NOT NULL" constraint has ** been seen on a column. This routine sets the notNull flag on |
︙ | ︙ | |||
1063 1064 1065 1066 1067 1068 1069 | ** 'REAL' | SQLITE_AFF_REAL ** 'FLOA' | SQLITE_AFF_REAL ** 'DOUB' | SQLITE_AFF_REAL ** ** If none of the substrings in the above table are found, ** SQLITE_AFF_NUMERIC is returned. */ | | > > | | > > > > > > > > > > > > > > > > > > > > > > > > | 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 | ** 'REAL' | SQLITE_AFF_REAL ** 'FLOA' | SQLITE_AFF_REAL ** '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, u8 *pszEst){ u32 h = 0; char aff = SQLITE_AFF_NUMERIC; const char *zChar = 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; 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 */ && aff==SQLITE_AFF_NUMERIC ){ aff = SQLITE_AFF_REAL; }else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b') /* DOUB */ && aff==SQLITE_AFF_NUMERIC ){ aff = SQLITE_AFF_REAL; #endif }else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){ /* INT */ 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 ** parsing a CREATE TABLE statement. The pFirst token is the first ** token in the sequence of tokens that describe the type of the |
︙ | ︙ | |||
1117 1118 1119 1120 1121 1122 1123 | Column *pCol; 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); | | | 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 | Column *pCol; 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->szEst); } /* ** The expression is the default value for the most recently added column ** of the table currently under construction. ** ** Default value expressions must be constant. Raise an exception if this |
︙ | ︙ | |||
1465 1466 1467 1468 1469 1470 1471 | testcase( pCol->affinity==SQLITE_AFF_NUMERIC ); testcase( pCol->affinity==SQLITE_AFF_INTEGER ); testcase( pCol->affinity==SQLITE_AFF_REAL ); zType = azType[pCol->affinity - SQLITE_AFF_TEXT]; len = sqlite3Strlen30(zType); assert( pCol->affinity==SQLITE_AFF_NONE | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 | testcase( pCol->affinity==SQLITE_AFF_NUMERIC ); testcase( pCol->affinity==SQLITE_AFF_INTEGER ); 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, 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; i<pIdx->nColumn; i++){ assert( pIdx->aiColumn[i]>=0 && pIdx->aiColumn[i]<pIdx->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. ** ** The table structure that other action routines have been building ** is added to the internal hash tables, assuming no errors have |
︙ | ︙ | |||
1500 1501 1502 1503 1504 1505 1506 | */ void sqlite3EndTable( 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" */ ){ | | | | > > > > > > > > | 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 | */ void sqlite3EndTable( 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; /* 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; if( p==0 ) return; assert( !db->init.busy || !pSelect ); iDb = sqlite3SchemaToIndex(db, p->pSchema); #ifndef SQLITE_OMIT_CHECK /* Resolve names in all CHECK constraint expressions. */ 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 ** should have been put there by the sqliteOpenCb routine.) */ |
︙ | ︙ | |||
2480 2481 2482 2483 2484 2485 2486 | DbFixer sFix; /* For assigning database names to pTable */ int sortOrderMask; /* 1 to honor DESC in index. 0 to ignore. */ 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 */ | > | | | | 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 | DbFixer sFix; /* For assigning database names to pTable */ int sortOrderMask; /* 1 to honor DESC in index. 0 to ignore. */ 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 */ 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; } if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){ goto exit_create_index; |
︙ | ︙ | |||
2709 2710 2711 2712 2713 2714 2715 | ** more than once within the same index. Only the first instance of ** the column will ever be used by the optimizer. Note that using the ** 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; i<pList->nExpr; i++, pListItem++){ const char *zColName = pListItem->zName; | < | 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 | ** more than once within the same index. Only the first instance of ** the column will ever be used by the optimizer. Note that using the ** 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; i<pList->nExpr; i++, pListItem++){ const char *zColName = pListItem->zName; int requestedSortOrder; char *zColl; /* Collation sequence name */ for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){ if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break; } if( j>=pTab->nCol ){ |
︙ | ︙ | |||
2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 | } pIndex->azColl[i] = zColl; requestedSortOrder = pListItem->sortOrder & sortOrderMask; pIndex->aSortOrder[i] = (u8)requestedSortOrder; if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0; } sqlite3DefaultRowEst(pIndex); 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. ** i.e. one of: ** | > > > | 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 | } pIndex->azColl[i] = zColl; 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. ** i.e. one of: ** |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
37 38 39 40 41 42 43 | if( op==TK_SELECT ){ assert( pExpr->flags&EP_xIsSelect ); return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr); } #ifndef SQLITE_OMIT_CAST if( op==TK_CAST ){ assert( !ExprHasProperty(pExpr, EP_IntValue) ); | | | 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | if( op==TK_SELECT ){ assert( pExpr->flags&EP_xIsSelect ); 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, 0); } #endif if( (op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_REGISTER) && pExpr->pTab!=0 ){ /* op==TK_REGISTER && pExpr->pTab!=0 happens when pExpr was originally ** a TK_COLUMN but was previously evaluated and cached in a register */ |
︙ | ︙ | |||
2457 2458 2459 2460 2461 2462 2463 | } #ifndef SQLITE_OMIT_CAST 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) ); | | | 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 | } #ifndef SQLITE_OMIT_CAST 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, 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 ); assert( to_op==OP_ToReal || aff!=SQLITE_AFF_REAL ); testcase( to_op==OP_ToText ); |
︙ | ︙ | |||
3124 3125 3126 3127 3128 3129 3130 | sqlite3ExplainExpr(pOut, pExpr->pLeft); break; } #ifndef SQLITE_OMIT_CAST case TK_CAST: { /* Expressions of the form: CAST(pLeft AS token) */ const char *zAff = "unk"; | | | 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 | sqlite3ExplainExpr(pOut, pExpr->pLeft); break; } #ifndef SQLITE_OMIT_CAST case TK_CAST: { /* Expressions of the form: CAST(pLeft AS token) */ const char *zAff = "unk"; 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; } sqlite3ExplainPrintf(pOut, "CAST-%s(", zAff); |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1451 1452 1453 1454 1455 1456 1457 | Table *pTab; pTab = sqlite3FindTable(db, zRight, zDb); if( pTab ){ v = sqlite3GetVdbe(pParse); pIdx = pTab->pIndex; if( pIdx ){ int i = 0; | | | > > | | 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 | Table *pTab; pTab = sqlite3FindTable(db, zRight, zDb); if( pTab ){ v = sqlite3GetVdbe(pParse); pIdx = pTab->pIndex; if( pIdx ){ int i = 0; 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_Integer, (pIdx->iScanRatio*100+127)/128, 4); sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4); ++i; pIdx = pIdx->pNext; } } } } break; |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
4593 4594 4595 4596 4597 4598 4599 | KeyInfo *pKeyInfo = 0; /* Keyinfo for scanned index */ Index *pBest = 0; /* Best index found so far */ int iRoot = pTab->tnum; /* Root page of scanned b-tree */ sqlite3CodeVerifySchema(pParse, iDb); sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); | | < < < < < < > | | | 4593 4594 4595 4596 4597 4598 4599 4600 4601 4602 4603 4604 4605 4606 4607 4608 4609 4610 4611 4612 4613 4614 4615 4616 4617 4618 4619 4620 4621 4622 4623 4624 4625 | KeyInfo *pKeyInfo = 0; /* Keyinfo for scanned index */ Index *pBest = 0; /* Best index found so far */ 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 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. ** ** 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->iScanRatio<pBest->iScanRatio) ){ pBest = pIdx; } } if( pBest ){ iRoot = pBest->tnum; pKeyInfo = sqlite3IndexKeyinfo(pParse, pBest); } /* Open a read-only cursor, execute the OP_Count, close the cursor. */ sqlite3VdbeAddOp3(v, OP_OpenRead, iCsr, iRoot, iDb); if( pKeyInfo ){ |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1193 1194 1195 1196 1197 1198 1199 | char *zName; /* Name of this column */ Expr *pDflt; /* Default value of this column */ 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 */ | > | | 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 | char *zName; /* Name of this column */ Expr *pDflt; /* Default value of this column */ 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 */ 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 */ #define COLFLAG_HIDDEN 0x0002 /* A hidden column in a virtual table */ |
︙ | ︙ | |||
1468 1469 1470 1471 1472 1473 1474 | #define OE_Replace 5 /* Delete existing record, then do INSERT or UPDATE */ #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 */ | | | 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 | #define OE_Replace 5 /* Delete existing record, then do INSERT or UPDATE */ #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 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 ** comparison of the two index keys. ** |
︙ | ︙ | |||
1556 1557 1558 1559 1560 1561 1562 | Index *pNext; /* The next index associated with the same table */ Schema *pSchema; /* Schema containing this index */ 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 */ | > | | 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 | Index *pNext; /* The next index associated with the same table */ Schema *pSchema; /* Schema containing this index */ 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 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[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ tRowcnt *aAvgEq; /* Average nEq values for keys not in aSample */ |
︙ | ︙ | |||
3077 3078 3079 3080 3081 3082 3083 | void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*); void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*); 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*); | | | 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 | void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*); void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*); 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*, u8*); void sqlite3Analyze(Parse*, Token*, Token*); int sqlite3InvokeBusyHandler(BusyHandler*); int sqlite3FindDb(sqlite3*, Token*); int sqlite3FindDbName(sqlite3 *, const char *); int sqlite3AnalysisLoad(sqlite3*,int iDB); void sqlite3DeleteIndexSamples(sqlite3*,Index*); void sqlite3DefaultRowEst(Index*); |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
4665 4666 4667 4668 4669 4670 4671 | if( pProbe->tnum<=0 ){ /* Integer primary key index */ pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; /* TUNING: Cost of full table scan is 3*(N + log2(N)). | | | < < > | > | < | | < < < | | 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 4706 4707 | if( pProbe->tnum<=0 ){ /* Integer primary key index */ 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 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; }else{ Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe); pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED; /* 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 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; } whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor); |
︙ | ︙ |
Changes to test/analyze6.test.
︙ | ︙ | |||
26 27 28 29 30 31 32 | proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db } do_test analyze6-1.0 { db eval { | | | | | | | | 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db } do_test analyze6-1.0 { db eval { CREATE TABLE cat(x INT, yz TEXT); CREATE UNIQUE INDEX catx ON cat(x); /* Give cat 16 unique integers */ 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; INSERT INTO ev SELECT x FROM cat; INSERT INTO ev SELECT y FROM ev; |
︙ | ︙ |
Changes to test/eqp.test.
︙ | ︙ | |||
29 30 31 32 33 34 35 | # ... # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count). # proc det {args} { uplevel do_eqp_test $args } do_execsql_test 1.1 { | | | | | 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | # ... # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count). # proc det {args} { uplevel do_eqp_test $args } do_execsql_test 1.1 { 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 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; } { 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} |
︙ | ︙ | |||
118 119 120 121 122 123 124 | } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { | | | | 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 | } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { CREATE TABLE t1(x INT, y INT, ex TEXT); 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} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} |
︙ | ︙ | |||
370 371 372 373 374 375 376 | # drop_all_tables # EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b # FROM t1 WHERE a=1; # 0|0|0|SCAN TABLE t1 # | | | 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 | # drop_all_tables # 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 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); # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; # 0|0|0|SEARCH TABLE t1 USING INDEX i1 |
︙ | ︙ | |||
398 399 400 401 402 403 404 | } # 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 # | | | | | | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 | } # 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 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.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); # 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 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 # SELECT c, d FROM t2 ORDER BY c; # 0|0|0|SCAN TABLE t2 |
︙ | ︙ | |||
481 482 483 484 485 486 487 | } # 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 # | | | | | 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 | } # 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 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 # 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,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)} } # EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN # SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1; # 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 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)} } #------------------------------------------------------------------------- |
︙ | ︙ | |||
544 545 546 547 548 549 550 | set data [read $fd] close $fd set data }] [list $res] } do_peqp_test 6.1 { | | | | | | | | | | 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 | set data [read $fd] close $fd set data }] [list $res] } do_peqp_test 6.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) }] #------------------------------------------------------------------------- # The following tests - eqp-7.* - test that queries that use the OP_Count # optimization return something sensible with EQP. # drop_all_tables do_execsql_test 7.0 { 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} } 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(a,b) VALUES(1, 2); INSERT INTO t1(a,b) VALUES(3, 4); 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 sqlite3 db test.db |
︙ | ︙ |
Changes to test/pragma.test.
︙ | ︙ | |||
570 571 572 573 574 575 576 | pragma foreign_key_list(t5); } } {} do_test pragma-6.4 { execsql { pragma index_list(t3); } | | | 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 | pragma foreign_key_list(t5); } } {} do_test pragma-6.4 { execsql { pragma index_list(t3); } } {/0 sqlite_autoindex_t3_1 1 \d+/} } ifcapable {!foreignkey} { execsql {CREATE TABLE t3(a,b UNIQUE)} } do_test pragma-6.5.1 { execsql { CREATE INDEX t3i1 ON t3(a,b); |
︙ | ︙ | |||
643 644 645 646 647 648 649 | do_test pragma-7.1.1 { # Make sure a pragma knows to read the schema if it needs to db close sqlite3 db test.db execsql { pragma index_list(t3); } | | | 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 | do_test pragma-7.1.1 { # Make sure a pragma knows to read the schema if it needs to db close sqlite3 db test.db execsql { pragma index_list(t3); } } {/0 t3i1 0 \d+ 1 sqlite_autoindex_t3_1 1 \d+/} do_test pragma-7.1.2 { execsql { pragma index_list(t3_bogus); } } {} } ;# ifcapable schema_pragmas ifcapable {utf16} { |
︙ | ︙ | |||
1657 1658 1659 1660 1661 1662 1663 | db2 eval {PRAGMA index_info(i2)} } {0 2 c 1 3 d 2 1 b} do_test 23.3 { db eval { CREATE INDEX i3 ON t1(d,b,c); } db2 eval {PRAGMA index_list(t1)} | | | 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 | db2 eval {PRAGMA index_info(i2)} } {0 2 c 1 3 d 2 1 b} do_test 23.3 { db eval { CREATE INDEX i3 ON t1(d,b,c); } db2 eval {PRAGMA index_list(t1)} } {/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 { PRAGMA table_info(t1); } |
︙ | ︙ |
Changes to test/subquery.test.
︙ | ︙ | |||
241 242 243 244 245 246 247 | } {10.0} do_test subquery-2.5.3.2 { # 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); } | | | 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 | } {10.0} do_test subquery-2.5.3.2 { # 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); } } {~/t4i/} do_test subquery-2.5.4 { execsql { DROP TABLE t3; DROP TABLE t4; } } {} |
︙ | ︙ |
Changes to test/tkt-78e04e52ea.test.
︙ | ︙ | |||
14 15 16 17 18 19 20 | # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tkt-78e04-1.0 { execsql { | | | | | | | | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tkt-78e04-1.0 { execsql { CREATE TABLE ""("" UNIQUE, x CHAR(100)); CREATE TABLE t2(x); INSERT INTO ""("") VALUES(1); INSERT INTO t2 VALUES(2); SELECT * FROM "", t2; } } {1 {} 2} do_test tkt-78e04-1.1 { catchsql { INSERT INTO ""("") VALUES(1); } } {1 {column is not unique}} do_test tkt-78e04-1.2 { execsql { PRAGMA table_info(""); } } {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%'; } } {0 0 0 {SCAN TABLE USING COVERING INDEX i1}} do_test tkt-78e04-1.5 { execsql { DROP TABLE ""; SELECT name FROM sqlite_master; } |
︙ | ︙ |
Changes to test/where.test.
︙ | ︙ | |||
1121 1122 1123 1124 1125 1126 1127 | # # When optimizing out ORDER BY clauses, make sure that trailing terms # of the ORDER BY clause do not reference other tables in a join. # if {[permutation] != "no_optimization"} { do_test where-14.1 { execsql { | | | | | 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 | # # When optimizing out ORDER BY clauses, make sure that trailing terms # 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, 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} do_test where-14.2 { cksort { |
︙ | ︙ |
Changes to test/where2.test.
︙ | ︙ | |||
310 311 312 313 314 315 316 | if {[permutation] != "no_optimization"} { # 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 { | | | | | | | | | | | | | | | | > | > | > | | 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 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 | if {[permutation] != "no_optimization"} { # 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, x CHAR(100)); CREATE TABLE t2249b(b INTEGER); 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 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 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 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 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 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 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 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 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 # "a IN (1, 2)" optimisation transformation, which is not enabled if # subqueries and the IN operator is not available. # 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 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 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 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 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 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 # "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 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 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 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 # row of result) do not call the sorter. But all tables must give # a unique result. If any one table in the join does not give a unique |
︙ | ︙ | |||
699 700 701 702 703 704 705 | } } {4 8 10} # 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 { | | | 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 | } } {4 8 10} # 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 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); } {/.*SEARCH TABLE t12 AS b .*SEARCH TABLE t12 AS b .*/} } finish_test |