Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | When optimizing out an ORDER BY clause due to uniqueness constraints, make sure unused terms to the right in the ORDER BY clause to not reference other tables in a join. Ticket #2211. Additional test cases needed before closing this ticket. (CVS 3629) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
912faf18d86416b1a36660851f8a4554 |
User & Date: | drh 2007-02-06 13:26:33.000 |
Context
2007-02-06
| ||
23:41 | Additional tests to give full coverage testing to ticket #2211. (CVS 3630) (check-in: ecb1f2fd7b user: drh tags: trunk) | |
13:26 | When optimizing out an ORDER BY clause due to uniqueness constraints, make sure unused terms to the right in the ORDER BY clause to not reference other tables in a join. Ticket #2211. Additional test cases needed before closing this ticket. (CVS 3629) (check-in: 912faf18d8 user: drh tags: trunk) | |
11:11 | Check the return value of lseek() in os_unix.c to make sure it really worked. (CVS 3628) (check-in: e4408dd1fd user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.237 2007/02/06 13:26:33 drh Exp $ */ #include "sqliteInt.h" /* ** The number of bits in a Bitmask. "BMS" means "BitMask Size". */ #define BMS (sizeof(Bitmask)*8) |
︙ | ︙ | |||
39 40 41 42 43 44 45 46 47 48 49 50 51 52 | #else # define TRACE(X) #endif /* Forward reference */ typedef struct WhereClause WhereClause; /* ** The query generator uses an array of instances of this structure to ** help it analyze the subexpressions of the WHERE clause. Each WHERE ** clause subexpression is separated from the others by an AND operator. ** ** All WhereTerms are collected into a single WhereClause structure. | > | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | #else # define TRACE(X) #endif /* Forward reference */ typedef struct WhereClause WhereClause; typedef struct ExprMaskSet ExprMaskSet; /* ** The query generator uses an array of instances of this structure to ** help it analyze the subexpressions of the WHERE clause. Each WHERE ** clause subexpression is separated from the others by an AND operator. ** ** All WhereTerms are collected into a single WhereClause structure. |
︙ | ︙ | |||
102 103 104 105 106 107 108 109 110 111 112 113 114 115 | /* ** An instance of the following structure holds all information about a ** WHERE clause. Mostly this is a container for one or more WhereTerms. */ struct WhereClause { Parse *pParse; /* The parser context */ int nTerm; /* Number of terms */ int nSlot; /* Number of entries in a[] */ WhereTerm *a; /* Each a[] describes a term of the WHERE cluase */ WhereTerm aStatic[10]; /* Initial static space for a[] */ }; /* | > | 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | /* ** An instance of the following structure holds all information about a ** WHERE clause. Mostly this is a container for one or more WhereTerms. */ struct WhereClause { Parse *pParse; /* The parser context */ ExprMaskSet *pMaskSet; /* Mapping of table indices to bitmasks */ int nTerm; /* Number of terms */ int nSlot; /* Number of entries in a[] */ WhereTerm *a; /* Each a[] describes a term of the WHERE cluase */ WhereTerm aStatic[10]; /* Initial static space for a[] */ }; /* |
︙ | ︙ | |||
134 135 136 137 138 139 140 | ** Note that the mapping is not necessarily ordered. In the example ** above, the mapping might go like this: 4->3, 5->1, 8->2, 29->0, ** 57->5, 73->4. Or one of 719 other combinations might be used. It ** does not really matter. What is important is that sparse cursor ** numbers all get mapped into bit numbers that begin with 0 and contain ** no gaps. */ | < | 136 137 138 139 140 141 142 143 144 145 146 147 148 149 | ** Note that the mapping is not necessarily ordered. In the example ** above, the mapping might go like this: 4->3, 5->1, 8->2, 29->0, ** 57->5, 73->4. Or one of 719 other combinations might be used. It ** does not really matter. What is important is that sparse cursor ** numbers all get mapped into bit numbers that begin with 0 and contain ** no gaps. */ struct ExprMaskSet { int n; /* Number of assigned cursor values */ int ix[sizeof(Bitmask)*8]; /* Cursor assigned to each bit */ }; /* |
︙ | ︙ | |||
182 183 184 185 186 187 188 | #define WHERE_REVERSE 0x200000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x400000 /* Selects no more than one row */ #define WHERE_VIRTUALTABLE 0x800000 /* Use virtual-table processing */ /* ** Initialize a preallocated WhereClause structure. */ | | > > > > > | 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 | #define WHERE_REVERSE 0x200000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x400000 /* Selects no more than one row */ #define WHERE_VIRTUALTABLE 0x800000 /* Use virtual-table processing */ /* ** Initialize a preallocated WhereClause structure. */ static void whereClauseInit( WhereClause *pWC, /* The WhereClause to be initialized */ Parse *pParse, /* The parsing context */ ExprMaskSet *pMaskSet /* Mapping from table indices to bitmasks */ ){ pWC->pParse = pParse; pWC->pMaskSet = pMaskSet; pWC->nTerm = 0; pWC->nSlot = ARRAYSIZE(pWC->aStatic); pWC->a = pWC->aStatic; } /* ** Deallocate a WhereClause structure. The WhereClause structure |
︙ | ︙ | |||
459 460 461 462 463 464 465 | return pTerm; } } return 0; } /* Forward reference */ | | < | | 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 | return pTerm; } } return 0; } /* Forward reference */ static void exprAnalyze(SrcList*, WhereClause*, int); /* ** Call exprAnalyze on all terms in a WHERE clause. ** ** */ static void exprAnalyzeAll( SrcList *pTabList, /* the FROM clause */ WhereClause *pWC /* the WHERE clause to be analyzed */ ){ int i; for(i=pWC->nTerm-1; i>=0; i--){ exprAnalyze(pTabList, pWC, i); } } #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION /* ** Check to see if the given expression is a LIKE or GLOB operator that ** can be optimized using inequality constraints. Return TRUE if it is |
︙ | ︙ | |||
588 589 590 591 592 593 594 | ** to the standard form of "X <op> <expr>". If the expression is of ** the form "X <op> Y" where both X and Y are columns, then the original ** expression is unchanged and a new virtual expression of the form ** "Y <op> X" is added to the WHERE clause and analyzed separately. */ static void exprAnalyze( SrcList *pSrc, /* the FROM clause */ | < > | 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 | ** to the standard form of "X <op> <expr>". If the expression is of ** the form "X <op> Y" where both X and Y are columns, then the original ** expression is unchanged and a new virtual expression of the form ** "Y <op> X" is added to the WHERE clause and analyzed separately. */ static void exprAnalyze( SrcList *pSrc, /* the FROM clause */ WhereClause *pWC, /* the WHERE clause */ int idxTerm /* Index of the term to be analyzed */ ){ WhereTerm *pTerm = &pWC->a[idxTerm]; ExprMaskSet *pMaskSet = pWC->pMaskSet; Expr *pExpr = pTerm->pExpr; Bitmask prereqLeft; Bitmask prereqAll; int nPattern; int isComplete; int op; |
︙ | ︙ | |||
675 676 677 678 679 680 681 | assert( pList->nExpr==2 ); for(i=0; i<2; i++){ Expr *pNewExpr; int idxNew; pNewExpr = sqlite3Expr(ops[i], sqlite3ExprDup(pExpr->pLeft), sqlite3ExprDup(pList->a[i].pExpr), 0); idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); | | | 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 | assert( pList->nExpr==2 ); for(i=0; i<2; i++){ Expr *pNewExpr; int idxNew; pNewExpr = sqlite3Expr(ops[i], sqlite3ExprDup(pExpr->pLeft), sqlite3ExprDup(pList->a[i].pExpr), 0); idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); exprAnalyze(pSrc, pWC, idxNew); pTerm = &pWC->a[idxTerm]; pWC->a[idxNew].iParent = idxTerm; } pTerm->nChild = 2; } #endif /* SQLITE_OMIT_BETWEEN_OPTIMIZATION */ |
︙ | ︙ | |||
704 705 706 707 708 709 710 | int ok; int i, j; int iColumn, iCursor; WhereClause sOr; WhereTerm *pOrTerm; assert( (pTerm->flags & TERM_DYNAMIC)==0 ); | | | | 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 | int ok; int i, j; int iColumn, iCursor; WhereClause sOr; WhereTerm *pOrTerm; assert( (pTerm->flags & TERM_DYNAMIC)==0 ); whereClauseInit(&sOr, pWC->pParse, pMaskSet); whereSplit(&sOr, pExpr, TK_OR); exprAnalyzeAll(pSrc, &sOr); assert( sOr.nTerm>0 ); j = 0; do{ iColumn = sOr.a[j].leftColumn; iCursor = sOr.a[j].leftCursor; ok = iCursor>=0; for(i=sOr.nTerm-1, pOrTerm=sOr.a; i>=0 && ok; i--, pOrTerm++){ |
︙ | ︙ | |||
746 747 748 749 750 751 752 | pDup = sqlite3ExprDup(pLeft); pNew = sqlite3Expr(TK_IN, pDup, 0, 0); if( pNew ){ int idxNew; transferJoinMarkings(pNew, pExpr); pNew->pList = pList; idxNew = whereClauseInsert(pWC, pNew, TERM_VIRTUAL|TERM_DYNAMIC); | | | 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 | pDup = sqlite3ExprDup(pLeft); pNew = sqlite3Expr(TK_IN, pDup, 0, 0); if( pNew ){ int idxNew; transferJoinMarkings(pNew, pExpr); pNew->pList = pList; idxNew = whereClauseInsert(pWC, pNew, TERM_VIRTUAL|TERM_DYNAMIC); exprAnalyze(pSrc, pWC, idxNew); pTerm = &pWC->a[idxTerm]; pWC->a[idxNew].iParent = idxTerm; pTerm->nChild = 1; }else{ sqlite3ExprListDelete(pList); } } |
︙ | ︙ | |||
783 784 785 786 787 788 789 | pStr2 = sqlite3ExprDup(pStr1); if( pStr2 ){ assert( pStr2->token.dyn ); ++*(u8*)&pStr2->token.z[nPattern-1]; } pNewExpr1 = sqlite3Expr(TK_GE, sqlite3ExprDup(pLeft), pStr1, 0); idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC); | | | | 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 | pStr2 = sqlite3ExprDup(pStr1); if( pStr2 ){ assert( pStr2->token.dyn ); ++*(u8*)&pStr2->token.z[nPattern-1]; } pNewExpr1 = sqlite3Expr(TK_GE, sqlite3ExprDup(pLeft), pStr1, 0); idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC); exprAnalyze(pSrc, pWC, idxNew1); pNewExpr2 = sqlite3Expr(TK_LT, sqlite3ExprDup(pLeft), pStr2, 0); idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC); exprAnalyze(pSrc, pWC, idxNew2); pTerm = &pWC->a[idxTerm]; if( isComplete ){ pWC->a[idxNew1].iParent = idxTerm; pWC->a[idxNew2].iParent = idxTerm; pTerm->nChild = 2; } } |
︙ | ︙ | |||
832 833 834 835 836 837 838 839 840 841 842 843 844 845 | pTerm->flags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ } /* ** 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 | > > > > > > > > > > > > > > > > > > > | 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 | pTerm->flags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ } /* ** Return TRUE if any of the expressions in pList->a[iFirst...] contain ** a reference to any table other than the iBase table. */ static int referencesOtherTables( ExprList *pList, /* Search expressions in ths list */ ExprMaskSet *pMaskSet, /* Mapping from tables to bitmaps */ int iFirst, /* Be searching with the iFirst-th expression */ int iBase /* Ignore references to this table */ ){ Bitmask allowed = ~getMask(pMaskSet, iBase); while( iFirst<pList->nExpr ){ 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 |
︙ | ︙ | |||
854 855 856 857 858 859 860 861 862 863 864 865 866 867 | ** ASC or DESC. (Terms of the ORDER BY clause past the end of a UNIQUE ** index do not need to satisfy this constraint.) The *pbRev value is ** set to 1 if the ORDER BY clause is all DESC and it is set to 0 if ** the ORDER BY clause is all ASC. */ static int isSortingIndex( Parse *pParse, /* Parsing context */ Index *pIdx, /* The index we are testing */ int base, /* Cursor number for the table to be sorted */ ExprList *pOrderBy, /* The ORDER BY clause */ int nEqCol, /* Number of index columns with == constraints */ int *pbRev /* Set to 1 if ORDER BY is DESC */ ){ int i, j; /* Loop counters */ | > | 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 | ** ASC or DESC. (Terms of the ORDER BY clause past the end of a UNIQUE ** index do not need to satisfy this constraint.) The *pbRev value is ** set to 1 if the ORDER BY clause is all DESC and it is set to 0 if ** the ORDER BY clause is all ASC. */ static int isSortingIndex( Parse *pParse, /* Parsing context */ ExprMaskSet *pMaskSet, /* Mapping from table indices to bitmaps */ Index *pIdx, /* The index we are testing */ int base, /* Cursor number for the table to be sorted */ ExprList *pOrderBy, /* The ORDER BY clause */ int nEqCol, /* Number of index columns with == constraints */ int *pbRev /* Set to 1 if ORDER BY is DESC */ ){ int i, j; /* Loop counters */ |
︙ | ︙ | |||
890 891 892 893 894 895 896 | int iSortOrder; /* 1 for DESC, 0 for ASC on the i-th index term */ const char *zColl; /* Name of the collating sequence for i-th index term */ pExpr = pTerm->pExpr; if( pExpr->op!=TK_COLUMN || pExpr->iTable!=base ){ /* Can not use an index sort on anything that is not a column in the ** left-most table of the FROM clause */ | | | 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 | int iSortOrder; /* 1 for DESC, 0 for ASC on the i-th index term */ const char *zColl; /* Name of the collating sequence for i-th index term */ pExpr = pTerm->pExpr; if( pExpr->op!=TK_COLUMN || pExpr->iTable!=base ){ /* Can not use an index sort on anything that is not a column in the ** left-most table of the FROM clause */ break; } pColl = sqlite3ExprCollSeq(pParse, pExpr); if( !pColl ){ pColl = db->pDfltColl; } if( i<pIdx->nColumn ){ iColumn = pIdx->aiColumn[i]; |
︙ | ︙ | |||
937 938 939 940 941 942 943 | return 0; } }else{ sortOrder = termSortOrder; } j++; pTerm++; | | > | | | | > | > > > | > | 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 | return 0; } }else{ sortOrder = termSortOrder; } j++; pTerm++; if( iColumn<0 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){ /* If the indexed column is the primary key and everything matches ** so far and none of the ORDER BY terms to the right reference other ** tables in the join, then we are assured that the index can be used ** to sort because the primary key is unique and so none of the other ** columns will make any difference */ j = nTerm; } } *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 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){ /* All terms of this index match some prefix of the ORDER BY clause ** and the index is UNIQUE and no terms on the tail of the ORDER BY ** clause reference other tables in a join. If this is all true then ** the order by clause is superfluous. */ return 1; } return 0; } /* ** Check table to see if the ORDER BY clause in pOrderBy can be satisfied ** by sorting in order of ROWID. Return true if so and set *pbRev to be ** true for reverse ROWID and false for forward ROWID order. */ static int sortableByRowid( int base, /* Cursor number for table to be sorted */ ExprList *pOrderBy, /* The ORDER BY clause */ ExprMaskSet *pMaskSet, /* Mapping from tables to bitmaps */ int *pbRev /* Set to 1 if ORDER BY is DESC */ ){ Expr *p; assert( pOrderBy!=0 ); assert( pOrderBy->nExpr>0 ); p = pOrderBy->a[0].pExpr; if( p->op==TK_COLUMN && p->iTable==base && p->iColumn==-1 && !referencesOtherTables(pOrderBy, pMaskSet, 1, base) ){ *pbRev = pOrderBy->a[0].sortOrder; return 1; } return 0; } /* |
︙ | ︙ | |||
1294 1295 1296 1297 1298 1299 1300 | ** clause that refer to the ROWID, then we will never be able to do ** anything other than a full table scan on this table. We might as ** well put it first in the join order. That way, perhaps it can be ** referenced by other tables in the join. */ if( pProbe==0 && findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IN|WO_LT|WO_LE|WO_GT|WO_GE,0)==0 && | | | 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 | ** clause that refer to the ROWID, then we will never be able to do ** anything other than a full table scan on this table. We might as ** well put it first in the join order. That way, perhaps it can be ** referenced by other tables in the join. */ if( pProbe==0 && findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IN|WO_LT|WO_LE|WO_GT|WO_GE,0)==0 && (pOrderBy==0 || !sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev)) ){ *pFlags = 0; *ppIndex = 0; *pnEq = 0; return 0.0; } /* Check for a rowid=EXPR or rowid IN (...) constraints |
︙ | ︙ | |||
1356 1357 1358 1359 1360 1361 1362 | }else{ flags = 0; } /* If the table scan does not satisfy the ORDER BY clause, increase ** the cost by NlogN to cover the expense of sorting. */ if( pOrderBy ){ | | | 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 | }else{ flags = 0; } /* If the table scan does not satisfy the ORDER BY clause, increase ** the cost by NlogN to cover the expense of sorting. */ if( pOrderBy ){ if( sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev) ){ flags |= WHERE_ORDERBY|WHERE_ROWID_RANGE; if( rev ){ flags |= WHERE_REVERSE; } }else{ cost += cost*estLog(cost); TRACE(("... sorting increases cost to %.9g\n", cost)); |
︙ | ︙ | |||
1440 1441 1442 1443 1444 1445 1446 | } } /* Add the additional cost of sorting if that is a factor. */ if( pOrderBy ){ if( (flags & WHERE_COLUMN_IN)==0 && | | | 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 | } } /* Add the additional cost of sorting if that is a factor. */ if( pOrderBy ){ if( (flags & WHERE_COLUMN_IN)==0 && isSortingIndex(pParse,pWC->pMaskSet,pProbe,iCur,pOrderBy,nEq,&rev) ){ if( flags==0 ){ flags = WHERE_COLUMN_RANGE; } flags |= WHERE_ORDERBY; if( rev ){ flags |= WHERE_REVERSE; } |
︙ | ︙ | |||
1828 1829 1830 1831 1832 1833 1834 | return 0; } /* Split the WHERE clause into separate subexpressions where each ** subexpression is separated by an AND operator. */ initMaskSet(&maskSet); | | | 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 | return 0; } /* Split the WHERE clause into separate subexpressions where each ** subexpression is separated by an AND operator. */ initMaskSet(&maskSet); whereClauseInit(&wc, pParse, &maskSet); whereSplit(&wc, pWhere, TK_AND); /* Allocate and initialize the WhereInfo structure that will become the ** return value. */ pWInfo = sqliteMalloc( sizeof(WhereInfo) + pTabList->nSrc*sizeof(WhereLevel)); if( sqlite3MallocFailed() ){ |
︙ | ︙ | |||
1859 1860 1861 1862 1863 1864 1865 | ** add new virtual terms onto the end of the WHERE clause. We do not ** want to analyze these virtual terms, so start analyzing at the end ** and work forward so that the added virtual terms are never processed. */ for(i=0; i<pTabList->nSrc; i++){ createMask(&maskSet, pTabList->a[i].iCursor); } | | | 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 | ** add new virtual terms onto the end of the WHERE clause. We do not ** want to analyze these virtual terms, so start analyzing at the end ** and work forward so that the added virtual terms are never processed. */ for(i=0; i<pTabList->nSrc; i++){ createMask(&maskSet, pTabList->a[i].iCursor); } exprAnalyzeAll(pTabList, &wc); if( sqlite3MallocFailed() ){ goto whereBeginNoMem; } /* Chose the best index to use for each table in the FROM clause. ** ** This loop fills in the following fields: |
︙ | ︙ |
Changes to test/where.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # 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 file is testing the use of indices in WHERE clases. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # 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 file is testing the use of indices in WHERE clases. # # $Id: where.test,v 1.40 2007/02/06 13:26:34 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # do_test where-1.0 { |
︙ | ︙ | |||
1033 1034 1035 1036 1037 1038 1039 | } {4 four 1 one nosort} do_test where-13.12 { cksort { SELECT * FROM t7 ORDER BY a ASC, b DESC; } } {1 one 4 four nosort} | > | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 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 | } {4 four 1 one nosort} do_test where-13.12 { cksort { SELECT * FROM t7 ORDER BY a ASC, b DESC; } } {1 one 4 four nosort} # Ticket #2211. # # When optimizing out ORDER BY clauses, make sure that trailing terms # of the ORDER BY clause do not reference other tables in a join. # do_test where-14.1 { execsql { CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE); INSERT INTO t8 VALUES(1,'one'); INSERT INTO t8 VALUES(4,'four'); } 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 sort} do_test where-14.2 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC } } {1/1 1/4 4/1 4/4 sort} do_test where-14.3 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b } } {1/1 1/4 4/1 4/4 nosort} do_test where-14.4 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC } } {1/1 1/4 4/1 4/4 nosort} btree_breakpoint do_test where-14.5 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b } } {4/1 4/4 1/1 1/4 nosort} do_test where-14.6 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC } } {4/1 4/4 1/1 1/4 nosort} do_test where-14.7 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b } } {4/1 4/4 1/1 1/4 sort} do_test where-14.8 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC } } {4/4 4/1 1/4 1/1 sort} do_test where-14.9 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b } } {4/4 4/1 1/4 1/1 sort} do_test where-14.10 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC } } {4/1 4/4 1/1 1/4 sort} do_test where-14.11 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b } } {4/1 4/4 1/1 1/4 sort} do_test where-14.12 { cksort { SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC } } {4/4 4/1 1/4 1/1 sort} integrity_check {where-99.0} finish_test |