Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Merge experimental changes improving optimization of DISTINCT queries with the trunk. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
45e581bff7a75db6c9a2c45b73d034d0 |
User & Date: | dan 2011-07-02 09:46:52.496 |
References
2014-02-08
| ||
18:47 | • New ticket [fccbde530a] DISTINCT thinks a zeroblob() and blob of all zeros are different. (artifact: 74e2e6591f user: drh) | |
2012-04-20
| ||
17:27 | • Ticket [385a5b56b9] A DISTINCT SELECT optimized using a UNIQUE index may allow duplicate NULL values. status still Open with 1 other change (artifact: 2a938c789f user: dan) | |
2012-03-03
| ||
01:35 | • Ticket [3557ad65a0] Incorrect DISTINCT on an indexed query with IN status still Open with 3 other changes (artifact: bf8e83bac7 user: drh) | |
2011-07-02
| ||
13:34 | Cherrypick [45e581bff7] into the 3.7.2 branch. (check-in: c593792ce0 user: dan tags: branch-3.7.2) | |
Context
2011-07-02
| ||
15:32 | Ensure that automatic indexes are only created in scenarios where they may be used more than once. (check-in: 27c65d4d9c user: dan tags: trunk) | |
09:46 | Merge experimental changes improving optimization of DISTINCT queries with the trunk. (check-in: 45e581bff7 user: dan tags: trunk) | |
06:44 | Fix a broken assert() in where.c. (Closed-Leaf check-in: 090b29177f user: dan tags: experimental) | |
2011-07-01
| ||
14:22 | Test case for ticket [d6ddba6706353915ceed] (check-in: 953e169e8a user: drh tags: trunk) | |
Changes
Changes to src/delete.c.
︙ | ︙ | |||
367 368 369 370 371 372 373 | int iRowSet = ++pParse->nMem; /* Register for rowset of rows to delete */ int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ int regRowid; /* Actual register containing rowids */ /* Collect rowids of every row to be deleted. */ sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); | | > > | 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 | int iRowSet = ++pParse->nMem; /* Register for rowset of rows to delete */ int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ int regRowid; /* Actual register containing rowids */ /* Collect rowids of every row to be deleted. */ sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); pWInfo = sqlite3WhereBegin( pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK ); if( pWInfo==0 ) goto delete_from_cleanup; regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid); sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid); if( db->flags & SQLITE_CountRows ){ sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); } sqlite3WhereEnd(pWInfo); |
︙ | ︙ |
Changes to src/fkey.c.
︙ | ︙ | |||
556 557 558 559 560 561 562 | sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. If the constraint is not deferred, throw an exception for ** each row found. Otherwise, for deferred constraints, increment the ** deferred constraint counter by nIncr for each row selected. */ | | | 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 | sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. If the constraint is not deferred, throw an exception for ** each row found. Otherwise, for deferred constraints, increment the ** deferred constraint counter by nIncr for each row selected. */ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0); if( nIncr>0 && pFKey->isDeferred==0 ){ sqlite3ParseToplevel(pParse)->mayAbort = 1; } sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); if( pWInfo ){ sqlite3WhereEnd(pWInfo); } |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 | ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */ ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */ Expr *pHaving; /* The HAVING clause. May be NULL */ int isDistinct; /* True if the DISTINCT keyword is present */ int distinct; /* Table to use for the distinct set */ int rc = 1; /* Value to return from this function */ int addrSortIndex; /* Address of an OP_OpenEphemeral instruction */ AggInfo sAggInfo; /* Information used by aggregate queries */ int iEnd; /* Address of the end of the query */ sqlite3 *db; /* The database connection */ #ifndef SQLITE_OMIT_EXPLAIN int iRestoreSelectId = pParse->iSelectId; pParse->iSelectId = pParse->iNextSelectId++; | > | 3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 3731 | ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */ ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */ Expr *pHaving; /* The HAVING clause. May be NULL */ int isDistinct; /* True if the DISTINCT keyword is present */ int distinct; /* Table to use for the distinct set */ int rc = 1; /* Value to return from this function */ int addrSortIndex; /* Address of an OP_OpenEphemeral instruction */ int addrDistinctIndex; /* Address of an OP_OpenEphemeral instruction */ AggInfo sAggInfo; /* Information used by aggregate queries */ int iEnd; /* Address of the end of the query */ sqlite3 *db; /* The database connection */ #ifndef SQLITE_OMIT_EXPLAIN int iRestoreSelectId = pParse->iSelectId; pParse->iSelectId = pParse->iNextSelectId++; |
︙ | ︙ | |||
3843 3844 3845 3846 3847 3848 3849 | } rc = multiSelect(pParse, p, pDest); explainSetInteger(pParse->iSelectId, iRestoreSelectId); return rc; } #endif | < < < < < < < < < < > > > > > > > > > > > > > > > > > > > > > > > > | 3844 3845 3846 3847 3848 3849 3850 3851 3852 3853 3854 3855 3856 3857 3858 3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 3879 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 | } rc = multiSelect(pParse, p, pDest); explainSetInteger(pParse->iSelectId, iRestoreSelectId); return rc; } #endif /* If there is both a GROUP BY and an ORDER BY clause and they are ** identical, then disable the ORDER BY clause since the GROUP BY ** will cause elements to come out in the correct order. This is ** an optimization - the correct answer should result regardless. ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER ** to disable this optimization for testing purposes. */ if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy)==0 && (db->flags & SQLITE_GroupByOrder)==0 ){ pOrderBy = 0; } /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and ** if the select-list is the same as the ORDER BY list, then this query ** can be rewritten as a GROUP BY. In other words, this: ** ** SELECT DISTINCT xyz FROM ... ORDER BY xyz ** ** is transformed to: ** ** SELECT xyz FROM ... GROUP BY xyz ** ** The second form is preferred as a single index (or temp-table) may be ** used for both the ORDER BY and DISTINCT processing. As originally ** written the query must use a temp-table for at least one of the ORDER ** BY and DISTINCT, and an index or separate temp-table for the other. */ if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct && sqlite3ExprListCompare(pOrderBy, p->pEList)==0 ){ p->selFlags &= ~SF_Distinct; p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0); pGroupBy = p->pGroupBy; pOrderBy = 0; } /* If there is an ORDER BY clause, then this sorting ** index might end up being unused if the data can be ** extracted in pre-sorted order. If that is the case, then the ** OP_OpenEphemeral instruction will be changed to an OP_Noop once ** we figure out that the sorting index is not needed. The addrSortIndex ** variable is used to facilitate that change. |
︙ | ︙ | |||
3900 3901 3902 3903 3904 3905 3906 | p->nSelectRow = (double)LARGEST_INT64; computeLimitRegisters(pParse, p, iEnd); /* Open a virtual index to use for the distinct set. */ if( p->selFlags & SF_Distinct ){ KeyInfo *pKeyInfo; | < | | > | | < | > > | > | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 3943 3944 3945 3946 3947 3948 3949 3950 3951 3952 3953 3954 3955 3956 3957 3958 3959 3960 3961 3962 3963 3964 3965 3966 3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 3982 3983 3984 3985 3986 3987 3988 3989 3990 3991 3992 3993 3994 3995 3996 3997 3998 3999 4000 | p->nSelectRow = (double)LARGEST_INT64; computeLimitRegisters(pParse, p, iEnd); /* Open a virtual index to use for the distinct set. */ if( p->selFlags & SF_Distinct ){ KeyInfo *pKeyInfo; distinct = pParse->nTab++; pKeyInfo = keyInfoFromExprList(pParse, p->pEList); addrDistinctIndex = sqlite3VdbeAddOp4(v, OP_OpenEphemeral, distinct, 0, 0, (char*)pKeyInfo, P4_KEYINFO_HANDOFF); sqlite3VdbeChangeP5(v, BTREE_UNORDERED); }else{ distinct = -1; } /* Aggregate and non-aggregate queries are handled differently */ if( !isAgg && pGroupBy==0 ){ ExprList *pDist = (isDistinct ? p->pEList : 0); /* Begin the database scan. */ pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, pDist, 0); if( pWInfo==0 ) goto select_end; if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut; /* If sorting index that was created by a prior OP_OpenEphemeral ** instruction ended up not being needed, then change the OP_OpenEphemeral ** into an OP_Noop. */ if( addrSortIndex>=0 && pOrderBy==0 ){ sqlite3VdbeChangeToNoop(v, addrSortIndex, 1); p->addrOpenEphm[2] = -1; } if( pWInfo->eDistinct ){ VdbeOp *pOp; /* No longer required OpenEphemeral instr. */ pOp = sqlite3VdbeGetOp(v, addrDistinctIndex); assert( isDistinct ); assert( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED || pWInfo->eDistinct==WHERE_DISTINCT_UNIQUE ); distinct = -1; if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED ){ int iJump; int iExpr; int iFlag = ++pParse->nMem; int iBase = pParse->nMem+1; int iBase2 = iBase + pEList->nExpr; pParse->nMem += (pEList->nExpr*2); /* Change the OP_OpenEphemeral coded earlier to an OP_Integer. The ** OP_Integer initializes the "first row" flag. */ pOp->opcode = OP_Integer; pOp->p1 = 1; pOp->p2 = iFlag; sqlite3ExprCodeExprList(pParse, pEList, iBase, 1); iJump = sqlite3VdbeCurrentAddr(v) + 1 + pEList->nExpr + 1 + 1; sqlite3VdbeAddOp2(v, OP_If, iFlag, iJump-1); for(iExpr=0; iExpr<pEList->nExpr; iExpr++){ CollSeq *pColl = sqlite3ExprCollSeq(pParse, pEList->a[iExpr].pExpr); sqlite3VdbeAddOp3(v, OP_Ne, iBase+iExpr, iJump, iBase2+iExpr); sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); } sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iContinue); sqlite3VdbeAddOp2(v, OP_Integer, 0, iFlag); assert( sqlite3VdbeCurrentAddr(v)==iJump ); sqlite3VdbeAddOp3(v, OP_Move, iBase, iBase2, pEList->nExpr); }else{ pOp->opcode = OP_Noop; } } /* Use the standard inner loop. */ selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, pDest, pWInfo->iContinue, pWInfo->iBreak); /* End the database scan loop. */ sqlite3WhereEnd(pWInfo); }else{ /* This is the processing for aggregate queries */ |
︙ | ︙ | |||
4041 4042 4043 4044 4045 4046 4047 | /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); | | | 4096 4097 4098 4099 4100 4101 4102 4103 4104 4105 4106 4107 4108 4109 4110 | /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0, 0); if( pWInfo==0 ) goto select_end; if( pGroupBy==0 ){ /* The optimizer is able to deliver rows in group by order so ** we do not have to sort. The OP_OpenEphemeral table will be ** cancelled later because we still need to use the pKeyInfo */ pGroupBy = p->pGroupBy; |
︙ | ︙ | |||
4303 4304 4305 4306 4307 4308 4309 | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); | | | 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax, 0, flag); if( pWInfo==0 ){ sqlite3ExprListDelete(db, pDel); goto select_end; } updateAccumulator(pParse, &sAggInfo); if( !pMinMax && flag ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak); |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 | ** into the second half to give some continuity. */ struct WhereInfo { Parse *pParse; /* Parsing and code generating context */ u16 wctrlFlags; /* Flags originally passed to sqlite3WhereBegin() */ u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE or DELETE */ u8 untestedTerms; /* Not all WHERE terms resolved by outer loop */ SrcList *pTabList; /* List of tables in the join */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ struct WhereClause *pWC; /* Decomposition of the WHERE clause */ double savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ double nRowOut; /* Estimated number of output rows */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; /* ** A NameContext defines a context in which to resolve table and column ** names. The context consists of a list of tables (the pSrcList) field and ** a list of named expression (pEList). The named expression list may ** be NULL. The pSrc corresponds to the FROM clause of a SELECT or ** to the table being operated on by INSERT, UPDATE, or DELETE. The ** pEList corresponds to the result set of a SELECT and is NULL for | > > > > | 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 | ** into the second half to give some continuity. */ struct WhereInfo { Parse *pParse; /* Parsing and code generating context */ u16 wctrlFlags; /* Flags originally passed to sqlite3WhereBegin() */ u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE or DELETE */ u8 untestedTerms; /* Not all WHERE terms resolved by outer loop */ u8 eDistinct; SrcList *pTabList; /* List of tables in the join */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int nLevel; /* Number of nested loop */ struct WhereClause *pWC; /* Decomposition of the WHERE clause */ double savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ double nRowOut; /* Estimated number of output rows */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; #define WHERE_DISTINCT_UNIQUE 1 #define WHERE_DISTINCT_ORDERED 2 /* ** A NameContext defines a context in which to resolve table and column ** names. The context consists of a list of tables (the pSrcList) field and ** a list of named expression (pEList). The named expression list may ** be NULL. The pSrc corresponds to the FROM clause of a SELECT or ** to the table being operated on by INSERT, UPDATE, or DELETE. The ** pEList corresponds to the result set of a SELECT and is NULL for |
︙ | ︙ | |||
2734 2735 2736 2737 2738 2739 2740 | int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); | | | 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 | int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**,ExprList*,u16); void sqlite3WhereEnd(WhereInfo*); int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int); void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int); void sqlite3ExprCodeMove(Parse*, int, int, int); void sqlite3ExprCodeCopy(Parse*, int, int, int); void sqlite3ExprCacheStore(Parse*, int, int, int); void sqlite3ExprCachePush(Parse*); |
︙ | ︙ |
Changes to src/update.c.
︙ | ︙ | |||
307 308 309 310 311 312 313 | if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto update_cleanup; } /* Begin the database scan */ sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); | | > > | 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 | if( sqlite3ResolveExprNames(&sNC, pWhere) ){ goto update_cleanup; } /* Begin the database scan */ sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); pWInfo = sqlite3WhereBegin( pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED ); if( pWInfo==0 ) goto update_cleanup; okOnePass = pWInfo->okOnePass; /* Remember the rowid of every item to be updated. */ sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regOldRowid); if( !okOnePass ){ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
249 250 251 252 253 254 255 256 257 258 259 260 261 262 | #define WHERE_IDX_ONLY 0x00800000 /* Use index only - omit table */ #define WHERE_ORDERBY 0x01000000 /* Output will appear in correct order */ #define WHERE_REVERSE 0x02000000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x04000000 /* Selects no more than one row */ #define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */ #define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */ #define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */ /* ** Initialize a preallocated WhereClause structure. */ static void whereClauseInit( WhereClause *pWC, /* The WhereClause to be initialized */ Parse *pParse, /* The parsing context */ | > | 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 | #define WHERE_IDX_ONLY 0x00800000 /* Use index only - omit table */ #define WHERE_ORDERBY 0x01000000 /* Output will appear in correct order */ #define WHERE_REVERSE 0x02000000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x04000000 /* Selects no more than one row */ #define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */ #define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */ #define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */ #define WHERE_DISTINCT 0x40000000 /* Correct order for DISTINCT */ /* ** Initialize a preallocated WhereClause structure. */ static void whereClauseInit( WhereClause *pWC, /* The WhereClause to be initialized */ Parse *pParse, /* The parsing context */ |
︙ | ︙ | |||
1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 | if( (exprTableUsage(pMaskSet, pList->a[iFirst++].pExpr)&allowed)!=0 ){ return 1; } } return 0; } /* ** This routine decides if pIdx can be used to satisfy the ORDER BY ** clause. If it can, it returns 1. If pIdx cannot satisfy the ** ORDER BY clause, this routine returns 0. ** ** pOrderBy is an ORDER BY clause from a SELECT statement. pTab is the | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 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 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 | if( (exprTableUsage(pMaskSet, pList->a[iFirst++].pExpr)&allowed)!=0 ){ return 1; } } return 0; } /* ** This function searches the expression list passed as the second argument ** for an expression of type TK_COLUMN that refers to the same column and ** uses the same collation sequence as the iCol'th column of index pIdx. ** Argument iBase is the cursor number used for the table that pIdx refers ** to. ** ** If such an expression is found, its index in pList->a[] is returned. If ** no expression is found, -1 is returned. */ static int findIndexCol( Parse *pParse, /* Parse context */ ExprList *pList, /* Expression list to search */ int iBase, /* Cursor for table associated with pIdx */ Index *pIdx, /* Index to match column of */ int iCol /* Column of index to match */ ){ int i; const char *zColl = pIdx->azColl[iCol]; for(i=0; i<pList->nExpr; i++){ Expr *p = pList->a[i].pExpr; if( pIdx->aiColumn[iCol]==p->iColumn && iBase==p->iTable ){ CollSeq *pColl = sqlite3ExprCollSeq(pParse, p); if( pColl && 0==sqlite3StrICmp(pColl->zName, zColl) ){ return i; } } } return -1; } /* ** This routine determines if pIdx can be used to assist in processing a ** DISTINCT qualifier. In other words, it tests whether or not using this ** index for the outer loop guarantees that rows with equal values for ** all expressions in the pDistinct list are delivered grouped together. ** ** For example, the query ** ** SELECT DISTINCT a, b, c FROM tbl WHERE a = ? ** ** can benefit from any index on columns "b" and "c". */ static int isDistinctIndex( Parse *pParse, /* Parsing context */ WhereClause *pWC, /* The WHERE clause */ Index *pIdx, /* The index being considered */ int base, /* Cursor number for the table pIdx is on */ ExprList *pDistinct, /* The DISTINCT expressions */ int nEqCol /* Number of index columns with == */ ){ Bitmask mask = 0; /* Mask of unaccounted for pDistinct exprs */ int i; /* Iterator variable */ if( pIdx->zName==0 || pDistinct==0 || pDistinct->nExpr>=BMS ) return 0; /* Loop through all the expressions in the distinct list. If any of them ** are not simple column references, return early. Otherwise, test if the ** WHERE clause contains a "col=X" clause. If it does, the expression ** can be ignored. If it does not, and the column does not belong to the ** same table as index pIdx, return early. Finally, if there is no ** matching "col=X" expression and the column is on the same table as pIdx, ** set the corresponding bit in variable mask. */ for(i=0; i<pDistinct->nExpr; i++){ WhereTerm *pTerm; Expr *p = pDistinct->a[i].pExpr; if( p->op!=TK_COLUMN ) return 0; pTerm = findTerm(pWC, p->iTable, p->iColumn, ~(Bitmask)0, WO_EQ, 0); if( pTerm ){ Expr *pX = pTerm->pExpr; CollSeq *p1 = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight); CollSeq *p2 = sqlite3ExprCollSeq(pParse, p); if( p1==p2 ) continue; } if( p->iTable!=base ) return 0; mask |= (((Bitmask)1) << i); } for(i=nEqCol; mask && i<pIdx->nColumn; i++){ int iExpr = findIndexCol(pParse, pDistinct, base, pIdx, i); if( iExpr<0 ) break; mask &= ~(((Bitmask)1) << iExpr); } return (mask==0); } /* ** Return true if the DISTINCT expression-list passed as the third argument ** is redundant. A DISTINCT list is redundant if the database contains a ** UNIQUE index that guarantees that the result of the query will be distinct ** anyway. */ static int isDistinctRedundant( Parse *pParse, SrcList *pTabList, WhereClause *pWC, ExprList *pDistinct ){ Table *pTab; Index *pIdx; int i; int iBase; /* If there is more than one table or sub-select in the FROM clause of ** this query, then it will not be possible to show that the DISTINCT ** clause is redundant. */ if( pTabList->nSrc!=1 ) return 0; iBase = pTabList->a[0].iCursor; pTab = pTabList->a[0].pTab; /* If any of the expressions is an IPK column on table iBase, then return ** true. Note: The (p->iTable==iBase) part of this test may be false if the ** current SELECT is a correlated sub-query. */ for(i=0; i<pDistinct->nExpr; i++){ Expr *p = pDistinct->a[i].pExpr; if( p->op==TK_COLUMN && p->iTable==iBase && p->iColumn<0 ) return 1; } /* Loop through all indices on the table, checking each to see if it makes ** the DISTINCT qualifier redundant. It does so if: ** ** 1. The index is itself UNIQUE, and ** ** 2. All of the columns in the index are either part of the pDistinct ** list, or else the WHERE clause contains a term of the form "col=X", ** where X is a constant value. The collation sequences of the ** comparison and select-list expressions must match those of the index. */ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_None ) continue; for(i=0; i<pIdx->nColumn; i++){ int iCol = pIdx->aiColumn[i]; if( 0==findTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) && 0>findIndexCol(pParse, pDistinct, iBase, pIdx, i) ){ break; } } if( i==pIdx->nColumn ){ /* This index implies that the DISTINCT qualifier is redundant. */ return 1; } } return 0; } /* ** This routine decides if pIdx can be used to satisfy the ORDER BY ** clause. If it can, it returns 1. If pIdx cannot satisfy the ** ORDER BY clause, this routine returns 0. ** ** pOrderBy is an ORDER BY clause from a SELECT statement. pTab is the |
︙ | ︙ | |||
1429 1430 1431 1432 1433 1434 1435 | ){ int i, j; /* Loop counters */ int sortOrder = 0; /* XOR of index and ORDER BY sort direction */ int nTerm; /* Number of ORDER BY terms */ struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ sqlite3 *db = pParse->db; | | > > > | 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 | ){ int i, j; /* Loop counters */ int sortOrder = 0; /* XOR of index and ORDER BY sort direction */ int nTerm; /* Number of ORDER BY terms */ struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ sqlite3 *db = pParse->db; if( !pOrderBy ) return 0; if( wsFlags & WHERE_COLUMN_IN ) return 0; if( pIdx->bUnordered ) return 0; nTerm = pOrderBy->nExpr; assert( nTerm>0 ); /* Argument pIdx must either point to a 'real' named index structure, ** or an index structure allocated on the stack by bestBtreeIndex() to ** represent the rowid index that is part of every table. */ assert( pIdx->zName || (pIdx->nColumn==1 && pIdx->aiColumn[0]==-1) ); |
︙ | ︙ | |||
1519 1520 1521 1522 1523 1524 1525 | ** to sort because the primary key is unique and so none of the other ** columns will make any difference */ j = nTerm; } } | | | 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 | ** to sort because the primary key is unique and so none of the other ** columns will make any difference */ j = nTerm; } } if( pbRev ) *pbRev = sortOrder!=0; if( j>=nTerm ){ /* All terms of the ORDER BY clause are covered by this index so ** this index can be used for sorting. */ return 1; } if( pIdx->onError!=OE_None && i==pIdx->nColumn && (wsFlags & WHERE_COLUMN_NULL)==0 |
︙ | ︙ | |||
2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 | static void bestBtreeIndex( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause */ struct SrcList_item *pSrc, /* The FROM clause term to search */ Bitmask notReady, /* Mask of cursors not available for indexing */ Bitmask notValid, /* Cursors not available for any purpose */ ExprList *pOrderBy, /* The ORDER BY clause */ WhereCost *pCost /* Lowest cost query plan */ ){ int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */ Index *pProbe; /* An index we are evaluating */ Index *pIdx; /* Copy of pProbe, or zero for IPK index */ int eqTermMask; /* Current mask of valid equality operators */ int idxEqTermMask; /* Index mask of valid equality operators */ | > | 2841 2842 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 | static void bestBtreeIndex( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause */ struct SrcList_item *pSrc, /* The FROM clause term to search */ Bitmask notReady, /* Mask of cursors not available for indexing */ Bitmask notValid, /* Cursors not available for any purpose */ ExprList *pOrderBy, /* The ORDER BY clause */ ExprList *pDistinct, /* The select-list if query is DISTINCT */ WhereCost *pCost /* Lowest cost query plan */ ){ int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */ Index *pProbe; /* An index we are evaluating */ Index *pIdx; /* Copy of pProbe, or zero for IPK index */ int eqTermMask; /* Current mask of valid equality operators */ int idxEqTermMask; /* Index mask of valid equality operators */ |
︙ | ︙ | |||
2825 2826 2827 2828 2829 2830 2831 | ** SELECT a, b, c FROM tbl WHERE a = 1; */ int nEq; /* Number of == or IN terms matching index */ int bInEst = 0; /* True if "x IN (SELECT...)" seen */ int nInMul = 1; /* Number of distinct equalities to lookup */ int estBound = 100; /* Estimated reduction in search space */ int nBound = 0; /* Number of range constraints seen */ | | > | 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 | ** SELECT a, b, c FROM tbl WHERE a = 1; */ int nEq; /* Number of == or IN terms matching index */ int bInEst = 0; /* True if "x IN (SELECT...)" seen */ int nInMul = 1; /* Number of distinct equalities to lookup */ int estBound = 100; /* Estimated reduction in search space */ int nBound = 0; /* Number of range constraints seen */ int bSort = !!pOrderBy; /* True if external sort required */ int bDist = !!pDistinct; /* True if index cannot help with DISTINCT */ int bLookup = 0; /* True if not a covering index */ WhereTerm *pTerm; /* A single term of the WHERE clause */ #ifdef SQLITE_ENABLE_STAT2 WhereTerm *pFirstTerm = 0; /* First term matching the index */ #endif /* Determine the values of nEq and nInMul */ |
︙ | ︙ | |||
2889 2890 2891 2892 2893 2894 2895 | } } /* If there is an ORDER BY clause and the index being considered will ** naturally scan rows in the required order, set the appropriate flags ** in wsFlags. Otherwise, if there is an ORDER BY clause but the index ** will scan rows in a different order, set the bSort variable. */ | | < < | < | > | | < < | > > > > > > > | 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 3074 | } } /* If there is an ORDER BY clause and the index being considered will ** naturally scan rows in the required order, set the appropriate flags ** in wsFlags. Otherwise, if there is an ORDER BY clause but the index ** will scan rows in a different order, set the bSort variable. */ if( isSortingIndex( pParse, pWC->pMaskSet, pProbe, iCur, pOrderBy, nEq, wsFlags, &rev) ){ bSort = 0; wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_ORDERBY; wsFlags |= (rev ? WHERE_REVERSE : 0); } /* If there is a DISTINCT qualifier and this index will scan rows in ** order of the DISTINCT expressions, clear bDist and set the appropriate ** flags in wsFlags. */ if( isDistinctIndex(pParse, pWC, pProbe, iCur, pDistinct, nEq) ){ bDist = 0; wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_DISTINCT; } /* If currently calculating the cost of using an index (not the IPK ** index), determine if all required column data may be obtained without ** using the main table (i.e. if the index is a covering ** index for this query). If it is, set the WHERE_IDX_ONLY flag in ** wsFlags. Otherwise, set the bLookup variable to true. */ |
︙ | ︙ | |||
3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 | ** adds C*N*log10(N) to the cost, where N is the number of rows to be ** sorted and C is a factor between 1.95 and 4.3. We will split the ** difference and select C of 3.0. */ if( bSort ){ cost += nRow*estLog(nRow)*3; } /**** Cost of using this index has now been computed ****/ /* If there are additional constraints on this table that cannot ** be used with the current index, but which might lower the number ** of output rows, adjust the nRow value accordingly. This only ** matters if the current index is the least costly, so do not bother | > > > | 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 3187 3188 3189 3190 3191 3192 3193 | ** adds C*N*log10(N) to the cost, where N is the number of rows to be ** sorted and C is a factor between 1.95 and 4.3. We will split the ** difference and select C of 3.0. */ if( bSort ){ cost += nRow*estLog(nRow)*3; } if( bDist ){ cost += nRow*estLog(nRow)*3; } /**** Cost of using this index has now been computed ****/ /* If there are additional constraints on this table that cannot ** be used with the current index, but which might lower the number ** of output rows, adjust the nRow value accordingly. This only ** matters if the current index is the least costly, so do not bother |
︙ | ︙ | |||
3161 3162 3163 3164 3165 3166 3167 | if( p->needToFreeIdxStr ){ sqlite3_free(p->idxStr); } sqlite3DbFree(pParse->db, p); }else #endif { | | | 3325 3326 3327 3328 3329 3330 3331 3332 3333 3334 3335 3336 3337 3338 3339 | if( p->needToFreeIdxStr ){ sqlite3_free(p->idxStr); } sqlite3DbFree(pParse->db, p); }else #endif { bestBtreeIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, 0, pCost); } } /* ** Disable a term in the WHERE clause. Except, do not disable the term ** if it controls a LEFT OUTER JOIN and it did not originate in the ON ** or USING clause of that join. |
︙ | ︙ | |||
4123 4124 4125 4126 4127 4128 4129 | iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn); for(ii=0; ii<pOrWc->nTerm; ii++){ WhereTerm *pOrTerm = &pOrWc->a[ii]; if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ /* Loop through table entries that match term pOrTerm. */ | | | 4287 4288 4289 4290 4291 4292 4293 4294 4295 4296 4297 4298 4299 4300 4301 | iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn); for(ii=0; ii<pOrWc->nTerm; ii++){ WhereTerm *pOrTerm = &pOrWc->a[ii]; if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0, 0, WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY); if( pSubWInfo ){ explainOneScan( pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0 ); if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ |
︙ | ︙ | |||
4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 4376 4377 | ** output order, then the *ppOrderBy is unchanged. */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */ u16 wctrlFlags /* One of the WHERE_* flags defined in sqliteInt.h */ ){ int i; /* Loop counter */ int nByteWInfo; /* Num. bytes allocated for WhereInfo struct */ int nTabList; /* Number of elements in pTabList */ WhereInfo *pWInfo; /* Will become the return value of this function */ Vdbe *v = pParse->pVdbe; /* The virtual database engine */ | > | 4528 4529 4530 4531 4532 4533 4534 4535 4536 4537 4538 4539 4540 4541 4542 | ** output order, then the *ppOrderBy is unchanged. */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */ ExprList *pDistinct, /* The select-list for DISTINCT queries - or NULL */ u16 wctrlFlags /* One of the WHERE_* flags defined in sqliteInt.h */ ){ int i; /* Loop counter */ int nByteWInfo; /* Num. bytes allocated for WhereInfo struct */ int nTabList; /* Number of elements in pTabList */ WhereInfo *pWInfo; /* Will become the return value of this function */ Vdbe *v = pParse->pVdbe; /* The virtual database engine */ |
︙ | ︙ | |||
4490 4491 4492 4493 4494 4495 4496 4497 4498 4499 4500 4501 4502 4503 | ** want to analyze these virtual terms, so start analyzing at the end ** and work forward so that the added virtual terms are never processed. */ exprAnalyzeAll(pTabList, pWC); if( db->mallocFailed ){ goto whereBeginError; } /* Chose the best index to use for each table in the FROM clause. ** ** This loop fills in the following fields: ** ** pWInfo->a[].pIdx The index to use for this level of the loop. ** pWInfo->a[].wsFlags WHERE_xxx flags associated with pIdx | > > > > > > > > > | 4655 4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 4675 4676 4677 | ** want to analyze these virtual terms, so start analyzing at the end ** and work forward so that the added virtual terms are never processed. */ exprAnalyzeAll(pTabList, pWC); if( db->mallocFailed ){ goto whereBeginError; } /* Check if the DISTINCT qualifier, if there is one, is redundant. ** If it is, then set pDistinct to NULL and WhereInfo.eDistinct to ** WHERE_DISTINCT_UNIQUE to tell the caller to ignore the DISTINCT. */ if( pDistinct && isDistinctRedundant(pParse, pTabList, pWC, pDistinct) ){ pDistinct = 0; pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } /* Chose the best index to use for each table in the FROM clause. ** ** This loop fills in the following fields: ** ** pWInfo->a[].pIdx The index to use for this level of the loop. ** pWInfo->a[].wsFlags WHERE_xxx flags associated with pIdx |
︙ | ︙ | |||
4574 4575 4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 4598 4599 4600 4601 4602 4603 4604 | notIndexed = 0; for(isOptimal=(iFrom<nTabList-1); isOptimal>=0 && bestJ<0; isOptimal--){ Bitmask mask; /* Mask of tables not yet ready */ for(j=iFrom, pTabItem=&pTabList->a[j]; j<nTabList; j++, pTabItem++){ int doNotReorder; /* True if this table should not be reordered */ WhereCost sCost; /* Cost information from best[Virtual]Index() */ ExprList *pOrderBy; /* ORDER BY clause for index to optimize */ doNotReorder = (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0; if( j!=iFrom && doNotReorder ) break; m = getMask(pMaskSet, pTabItem->iCursor); if( (m & notReady)==0 ){ if( j==iFrom ) iFrom++; continue; } mask = (isOptimal ? m : notReady); pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0); if( pTabItem->pIndex==0 ) nUnconstrained++; WHERETRACE(("=== trying table %d with isOptimal=%d ===\n", j, isOptimal)); assert( pTabItem->pTab ); #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTabItem->pTab) ){ sqlite3_index_info **pp = &pWInfo->a[j].pIdxInfo; bestVirtualIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy, &sCost, pp); }else #endif { bestBtreeIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy, | > > | | 4748 4749 4750 4751 4752 4753 4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 4768 4769 4770 4771 4772 4773 4774 4775 4776 4777 4778 4779 4780 4781 4782 4783 4784 4785 4786 4787 4788 | notIndexed = 0; for(isOptimal=(iFrom<nTabList-1); isOptimal>=0 && bestJ<0; isOptimal--){ Bitmask mask; /* Mask of tables not yet ready */ for(j=iFrom, pTabItem=&pTabList->a[j]; j<nTabList; j++, pTabItem++){ int doNotReorder; /* True if this table should not be reordered */ WhereCost sCost; /* Cost information from best[Virtual]Index() */ ExprList *pOrderBy; /* ORDER BY clause for index to optimize */ ExprList *pDist; /* DISTINCT clause for index to optimize */ doNotReorder = (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0; if( j!=iFrom && doNotReorder ) break; m = getMask(pMaskSet, pTabItem->iCursor); if( (m & notReady)==0 ){ if( j==iFrom ) iFrom++; continue; } mask = (isOptimal ? m : notReady); pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0); pDist = (i==0 ? pDistinct : 0); if( pTabItem->pIndex==0 ) nUnconstrained++; WHERETRACE(("=== trying table %d with isOptimal=%d ===\n", j, isOptimal)); assert( pTabItem->pTab ); #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTabItem->pTab) ){ sqlite3_index_info **pp = &pWInfo->a[j].pIdxInfo; bestVirtualIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy, &sCost, pp); }else #endif { bestBtreeIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy, pDist, &sCost); } assert( isOptimal || (sCost.used¬Ready)==0 ); /* If an INDEXED BY clause is present, then the plan must use that ** index if it uses any index at all */ assert( pTabItem->pIndex==0 || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 |
︙ | ︙ | |||
4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 4671 | assert( bestJ>=0 ); assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); WHERETRACE(("*** Optimizer selects table %d for loop %d" " with cost=%g and nRow=%g\n", bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow)); if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ *ppOrderBy = 0; } andFlags &= bestPlan.plan.wsFlags; pLevel->plan = bestPlan.plan; testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){ pLevel->iIdxCur = pParse->nTab++; | > > > > | 4834 4835 4836 4837 4838 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 | assert( bestJ>=0 ); assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); WHERETRACE(("*** Optimizer selects table %d for loop %d" " with cost=%g and nRow=%g\n", bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow)); if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ *ppOrderBy = 0; } if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){ assert( pWInfo->eDistinct==0 ); pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } andFlags &= bestPlan.plan.wsFlags; pLevel->plan = bestPlan.plan; testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){ pLevel->iIdxCur = pParse->nTab++; |
︙ | ︙ |
Changes to test/collate5.test.
︙ | ︙ | |||
53 54 55 56 57 58 59 | INSERT INTO collate5t1 VALUES('N', NULL); } } {} do_test collate5-1.1 { execsql { SELECT DISTINCT a FROM collate5t1; } | | | | | 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 | INSERT INTO collate5t1 VALUES('N', NULL); } } {} do_test collate5-1.1 { execsql { SELECT DISTINCT a FROM collate5t1; } } {a b n} do_test collate5-1.2 { execsql { SELECT DISTINCT b FROM collate5t1; } } {apple Apple banana {}} do_test collate5-1.3 { execsql { SELECT DISTINCT a, b FROM collate5t1; } } {a apple A Apple b banana n {}} # Ticket #3376 # do_test collate5-1.11 { execsql { CREATE TABLE tkt3376(a COLLATE nocase PRIMARY KEY); INSERT INTO tkt3376 VALUES('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'); |
︙ | ︙ |
Added test/distinct.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 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 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 87 88 89 90 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 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 | # 2011 July 1 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this script is the DISTINCT modifier. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix distinct proc is_distinct_noop {sql} { set sql1 $sql set sql2 [string map {DISTINCT ""} $sql] set program1 [list] set program2 [list] db eval "EXPLAIN $sql1" { if {$opcode != "Noop"} { lappend program1 $opcode } } db eval "EXPLAIN $sql2" { if {$opcode != "Noop"} { lappend program2 $opcode } } return [expr {$program1==$program2}] } proc do_distinct_noop_test {tn sql} { uplevel [list do_test $tn [list is_distinct_noop $sql] 1] } proc do_distinct_not_noop_test {tn sql} { uplevel [list do_test $tn [list is_distinct_noop $sql] 0] } proc do_temptables_test {tn sql temptables} { uplevel [list do_test $tn [subst -novar { set ret "" db eval "EXPLAIN [set sql]" { if {$opcode == "OpenEphemeral"} { if {$p5 != "10" && $p5!="00"} { error "p5 = $p5" } if {$p5 == "10"} { lappend ret hash } else { lappend ret btree } } } set ret }] $temptables] } #------------------------------------------------------------------------- # The following tests - distinct-1.* - check that the planner correctly # detects cases where a UNIQUE index means that a DISTINCT clause is # redundant. Currently the planner only detects such cases when there # is a single table in the FROM clause. # do_execsql_test 1.0 { CREATE TABLE t1(a, b, c, d); CREATE UNIQUE INDEX i1 ON t1(b, c); CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase); CREATE TABLE t2(x INTEGER PRIMARY KEY, y); CREATE TABLE t3(c1 PRIMARY KEY, c2); CREATE INDEX i3 ON t3(c2); } foreach {tn noop sql} { 1 1 "SELECT DISTINCT b, c FROM t1" 2 1 "SELECT DISTINCT c FROM t1 WHERE b = ?" 3 1 "SELECT DISTINCT rowid FROM t1" 4 1 "SELECT DISTINCT rowid, a FROM t1" 5 1 "SELECT DISTINCT x FROM t2" 6 1 "SELECT DISTINCT * FROM t2" 7 1 "SELECT DISTINCT * FROM (SELECT * FROM t2)" 8 1 "SELECT DISTINCT * FROM t1" 8 0 "SELECT DISTINCT a, b FROM t1" 9 0 "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)" 10 0 "SELECT DISTINCT c FROM t1" 11 0 "SELECT DISTINCT b FROM t1" 12 0 "SELECT DISTINCT a, d FROM t1" 13 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t1" 14 1 "SELECT DISTINCT a, d COLLATE nocase FROM t1" 15 0 "SELECT DISTINCT a, d COLLATE binary FROM t1" 16 1 "SELECT DISTINCT a, b, c COLLATE binary FROM t1" 16 0 "SELECT DISTINCT t1.rowid FROM t1, t2" 17 0 { /* Technically, it would be possible to detect that DISTINCT ** is a no-op in cases like the following. But SQLite does not ** do so. */ SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid } 18 1 "SELECT DISTINCT c1, c2 FROM t3" 19 1 "SELECT DISTINCT c1 FROM t3" 20 1 "SELECT DISTINCT * FROM t3" 21 0 "SELECT DISTINCT c2 FROM t3" 22 0 "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)" 23 1 "SELECT DISTINCT rowid FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)" 24 0 "SELECT DISTINCT rowid/2 FROM t1" 25 1 "SELECT DISTINCT rowid/2, rowid FROM t1" 26 1 "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?" } { if {$noop} { do_distinct_noop_test 1.$tn $sql } else { do_distinct_not_noop_test 1.$tn $sql } } #------------------------------------------------------------------------- # The following tests - distinct-2.* - test cases where an index is # used to deliver results in order of the DISTINCT expressions. # drop_all_tables do_execsql_test 2.0 { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a, b); CREATE INDEX i2 ON t1(b COLLATE nocase, c COLLATE nocase); INSERT INTO t1 VALUES('a', 'b', 'c'); INSERT INTO t1 VALUES('A', 'B', 'C'); INSERT INTO t1 VALUES('a', 'b', 'c'); INSERT INTO t1 VALUES('A', 'B', 'C'); } foreach {tn sql temptables res} { 1 "a, b FROM t1" {} {A B a b} 2 "b, a FROM t1" {} {B A b a} 3 "a, b, c FROM t1" {hash} {a b c A B C} 4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c} 5 "b FROM t1 WHERE a = 'a'" {} {b} 6 "b FROM t1" {hash} {b B} 7 "a FROM t1" {} {A a} 8 "b COLLATE nocase FROM t1" {} {b} 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {B} } { do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables } do_execsql_test 2.A { SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o; } {a A a A} finish_test |
Changes to test/e_select.test.
︙ | ︙ | |||
1234 1235 1236 1237 1238 1239 1240 | do_select_tests e_select-5 { 3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four} 3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four} 3.1 "SELECT x FROM h2" {One Two Three Four one two three four} 3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four} | | | | | | | | 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 | do_select_tests e_select-5 { 3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four} 3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four} 3.1 "SELECT x FROM h2" {One Two Three Four one two three four} 3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four} 4.1 "SELECT DISTINCT x FROM h2" {One Two Three Four} 4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {One Four} } # EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate # rows, two NULL values are considered to be equal. # do_select_tests e_select-5.5 { 1 "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3} } # EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation # sequence to compare text values with apply. # do_select_tests e_select-5.6 { 1 "SELECT DISTINCT b FROM h1" {one I i four IV iv} 2 "SELECT DISTINCT b COLLATE nocase FROM h1" {one I four IV} 3 "SELECT DISTINCT x FROM h2" {One Two Three Four} 4 "SELECT DISTINCT x COLLATE binary FROM h2" { One Two Three Four one two three four } } #------------------------------------------------------------------------- # The following tests - e_select-7.* - test that statements made to do # with compound SELECT statements are correct. # |
︙ | ︙ |
Changes to test/fuzzer1.test.
︙ | ︙ | |||
1372 1373 1374 1375 1376 1377 1378 | do_test fuzzer1-2.3 { execsql { SELECT DISTINCT streetname.n FROM f2, streetname WHERE f2.word MATCH 'tayle' AND f2.distance<=200 AND streetname.n>=f2.word AND streetname.n<=(f2.word || x'F7BFBFBF') } | | | 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 | do_test fuzzer1-2.3 { execsql { SELECT DISTINCT streetname.n FROM f2, streetname WHERE f2.word MATCH 'tayle' AND f2.distance<=200 AND streetname.n>=f2.word AND streetname.n<=(f2.word || x'F7BFBFBF') } } {{tyler finley} trailer taymouth steelewood tallia tallu talwyn thelema} finish_test |
Changes to test/insert4.test.
︙ | ︙ | |||
108 109 110 111 112 113 114 | # do_test insert4-2.4.1 { execsql { DELETE FROM t3; INSERT INTO t3 SELECT DISTINCT * FROM t2; SELECT * FROM t3; } | | | 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | # do_test insert4-2.4.1 { execsql { DELETE FROM t3; INSERT INTO t3 SELECT DISTINCT * FROM t2; SELECT * FROM t3; } } {9 1 1 9} xferopt_test insert4-2.4.2 0 do_test insert4-2.4.3 { catchsql { DELETE FROM t1; INSERT INTO t1 SELECT DISTINCT * FROM t2; } } {1 {constraint failed}} |
︙ | ︙ |
Changes to test/misc5.test.
︙ | ︙ | |||
501 502 503 504 505 506 507 | ) WHERE artist <> '' ) ) ) ORDER BY LOWER(artist) ASC; } | | | 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 | ) WHERE artist <> '' ) ) ) ORDER BY LOWER(artist) ASC; } } {two} } # Ticket #1370. Do not overwrite small files (less than 1024 bytes) # when trying to open them as a database. # if {[permutation] == ""} { do_test misc5-4.1 { |
︙ | ︙ |
Changes to test/selectB.test.
︙ | ︙ | |||
351 352 353 354 355 356 357 | do_test selectB-$ii.19 { execsql { SELECT * FROM ( SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2 ) } | | | 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 | do_test selectB-$ii.19 { execsql { SELECT * FROM ( SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2 ) } } {0 1 1 0} do_test selectB-$ii.20 { execsql { SELECT DISTINCT * FROM ( SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2 ) } |
︙ | ︙ |
Changes to test/tester.tcl.
︙ | ︙ | |||
16 17 18 19 20 21 22 | #------------------------------------------------------------------------- # The commands provided by the code in this file to help with creating # test cases are as follows: # # Commands to manipulate the db and the file-system at a high level: # # copy_file FROM TO | | | 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | #------------------------------------------------------------------------- # The commands provided by the code in this file to help with creating # test cases are as follows: # # Commands to manipulate the db and the file-system at a high level: # # copy_file FROM TO # drop_all_tables ?DB? # forcedelete FILENAME # # Test the capability of the SQLite version built into the interpreter to # determine if a specific test can be run: # # ifcapable EXPR # |
︙ | ︙ |