diff options
| -rw-r--r-- | manifest | 24 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/build.c | 79 | ||||
| -rw-r--r-- | src/expr.c | 35 | ||||
| -rw-r--r-- | src/parse.y | 31 | ||||
| -rw-r--r-- | src/select.c | 31 | ||||
| -rw-r--r-- | src/sqliteInt.h | 6 | ||||
| -rw-r--r-- | src/where.c | 7 | ||||
| -rw-r--r-- | test/where3.test | 83 |
9 files changed, 212 insertions, 86 deletions
@@ -1,5 +1,5 @@ -C Fix\sa\sbug\sin\slemon\sthat\sleads\sto\san\sassertion\sfault\sgiven\san\sinvalid\ngrammar.\s\sThe\sbug\sand\sthis\sfix\sdo\snot\seffect\son\sSQLite.\s\sTicket\s#2107.\s(CVS\s3528) -D 2006-12-14T01:06:22 +C Query\soptimizer\senhancement:\sIn\s"FROM\sa,b,c\sleft\sjoin\sd"\sallow\sthe\sC\stable\nto\sbe\sreordered\swith\sA\sand\sB.\s\sThis\sused\sto\sbe\sthe\scase\sbut\sthe\scapability\nwas\sremoved\sby\s(3203)\sand\s(3052)\sin\sresponse\sto\sticket\s#1652.\s\sThis\schange\nrestores\sthe\scapability.\s(CVS\s3529) +D 2006-12-16T16:25:15 F Makefile.in 8e14898d41a53033ecb687d93c9cd5d109fb9ae3 F Makefile.linux-gcc 2d8574d1ba75f129aba2019f0b959db380a90935 F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028 @@ -59,13 +59,13 @@ F src/attach.c b11eb4d5d3fb99a10a626956bccc7215f6b68b16 F src/auth.c 902f4722661c796b97f007d9606bd7529c02597f F src/btree.c 116f748ee11ba4d3630882102e99b60ff58d2a84 F src/btree.h 061c50e37de7f50b58528e352d400cf33ead7418 -F src/build.c 7199949a6a3449e9ec69408cc79a48ed04fd0b85 +F src/build.c 02aedde724dc73295d6e9b8dc29afb5dd38de507 F src/callback.c fd9bb39f7ff6b52bad8365617abc61c720640429 F src/complete.c 7d1a44be8f37de125fcafd3d3a018690b3799675 F src/date.c 9602457c49210f77f860d1919a8249a3118cd43f F src/delete.c 804384761144fe1a5035b99f4bd7d706976831bd F src/experimental.c 1b2d1a6cd62ecc39610e97670332ca073c50792b -F src/expr.c 03420f31394b45a601c8aff5dd445c4e0d58999b +F src/expr.c 82603dc2882797734882507ed95dfcb7f7c583e3 F src/func.c af537156dfeb91dbac8ffed4db6c4fa1a3164cc9 F src/hash.c 449f3d6620193aa557f5d86cbc5cc6b87702b185 F src/hash.h 1b3f7e2609141fd571f62199fc38687d262e9564 @@ -87,17 +87,17 @@ F src/os_win.c cac5f9b60faacd46874aa8dfefdb7dc4d0437ff9 F src/os_win.h 41a946bea10f61c158ce8645e7646b29d44f122b F src/pager.c 18c9cb43b3e740c63eaa13da41c52926012e4853 F src/pager.h 2e6d42f4ae004ae748a037b8468112b851c447a7 -F src/parse.y 8c79a1debbd92a4f5609511e9bf0222de78f5ecb +F src/parse.y 9b3d1c0f8af81fe0cc909baf226865baa725bb23 F src/pragma.c d0891d3504b6291b506a5ec2226bbf79ffcef003 F src/prepare.c f4f45b4560defbb566cf8255763625d2c09a8023 F src/printf.c b179b6ed12f793e028dd169e2e2e2b2a37eedc63 F src/random.c d40f8d356cecbd351ccfab6eaedd7ec1b54f5261 -F src/select.c 6ba6d8ead43d0575ce1f8b418cc039f8f301389a +F src/select.c 52f09127b53697b1a95835a9b0db9309cca8079f F src/server.c 087b92a39d883e3fa113cae259d64e4c7438bc96 F src/shell.c 934cdcf67eccee6f5c1be87c98a477a2e263db3c F src/sqlite.h.in 2931f7ee2415e7a49fd12f386c23575046f0f540 F src/sqlite3ext.h 2c2156cc32a158e2b7bd9042d42accf94bff2e40 -F src/sqliteInt.h f6bac44ee7b8ee2614b046974551c22999ec674f +F src/sqliteInt.h 28d060f44a4a3ad1acb47bf1cef627c86ec45f5f F src/table.c 6d0da66dde26ee75614ed8f584a1996467088d06 F src/tclsqlite.c e029f739bed90071789fe81a226d53e97a80a4d8 F src/test1.c 19786ff3274635b6eac27a89f842416f388f3654 @@ -129,7 +129,7 @@ F src/vdbeaux.c 05cc6f0f82b86dfb4c356e06ab07ec8cc83a2eda F src/vdbefifo.c 9efb94c8c3f4c979ebd0028219483f88e57584f5 F src/vdbemem.c 26623176bf1c616aa478da958fac49502491a921 F src/vtab.c aa30e940058ea56a1b7a9a7019ec21d307316fb4 -F src/where.c 6e215af5a7b1eb2fc1b9d6fa653064753a84757f +F src/where.c b34cc2c7f753cdf76aa37a71e579c2e266e50958 F tclinstaller.tcl 046e3624671962dc50f0481d7c25b38ef803eb42 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/all.test b62fcd122052efaff1b0979aefa2dd65cfc8ee52 @@ -350,7 +350,7 @@ F test/vtab9.test 87afba55339b0c255e9697fbfb5bfb6120505d9d F test/vtab_err.test c07f7665dd90bc757f80f05e7951d826eda9bc48 F test/where.test ee7c9a6659b07e1ee61177f6e7ff71565ee2c9df F test/where2.test 61d5b20d9bedc8788a773bbdc5b2ef887725928e -F test/where3.test 3b5ad2c58069e12be2bd86bc5e211a82810521aa +F test/where3.test 0a30fe9808b0fa01c46d0fcf4fac0bf6cf75bb30 F tool/diffdb.c 7524b1b5df217c20cd0431f6789851a4e0cb191b F tool/lemon.c c200a81e346853c2902d5c43de8274af627a72bb F tool/lempar.c 0a2a5cf96a98a64a5594625ad8fbdbe41dbaca50 @@ -423,7 +423,7 @@ F www/tclsqlite.tcl bb0d1357328a42b1993d78573e587c6dcbc964b9 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0 F www/version3.tcl 890248cf7b70e60c383b0e84d77d5132b3ead42b F www/whentouse.tcl 97e2b5cd296f7d8057e11f44427dea8a4c2db513 -P 5f21c3a5f02b4f2c4550f5904e9d0e1e2eafb0f3 -R ff4e890d4e50d38dfc084d0df0ba3d75 +P f2ad230f6dce98d664370d77845b5f585de20f08 +R c3b5c634dcadafbfe73caa00b5b83dd9 U drh -Z c7ee88553b14e37d6f61a63315c281d7 +Z 31988b25529ff3c6bbd95097777774bb diff --git a/manifest.uuid b/manifest.uuid index 02d72d894..ab663f993 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -f2ad230f6dce98d664370d77845b5f585de20f08
\ No newline at end of file +7393c81b8cb9d4344ae744de9eabcb3af64f1db8
\ No newline at end of file diff --git a/src/build.c b/src/build.c index eeceded71..00b89f2fd 100644 --- a/src/build.c +++ b/src/build.c @@ -22,7 +22,7 @@ ** COMMIT ** ROLLBACK ** -** $Id: build.c,v 1.411 2006/09/11 23:45:49 drh Exp $ +** $Id: build.c,v 1.412 2006/12/16 16:25:15 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> @@ -2941,15 +2941,6 @@ void sqlite3SrcListAssignCursors(Parse *pParse, SrcList *pList){ } /* -** Add an alias to the last identifier on the given identifier list. -*/ -void sqlite3SrcListAddAlias(SrcList *pList, Token *pToken){ - if( pList && pList->nSrc>0 ){ - pList->a[pList->nSrc-1].zAlias = sqlite3NameFromToken(pToken); - } -} - -/* ** Delete an entire SrcList including all its substructure. */ void sqlite3SrcListDelete(SrcList *pList){ @@ -2969,6 +2960,74 @@ void sqlite3SrcListDelete(SrcList *pList){ } /* +** This routine is called by the parser to add a new term to the +** end of a growing FROM clause. The "p" parameter is the part of +** the FROM clause that has already been constructed. "p" is NULL +** if this is the first term of the FROM clause. pTable and pDatabase +** are the name of the table and database named in the FROM clause term. +** pDatabase is NULL if the database name qualifier is missing - the +** usual case. If the term has a alias, then pAlias points to the +** alias token. If the term is a subquery, then pSubquery is the +** SELECT statement that the subquery encodes. The pTable and +** pDatabase parameters are NULL for subqueries. The pOn and pUsing +** parameters are the content of the ON and USING clauses. +** +** Return a new SrcList which encodes is the FROM with the new +** term added. +*/ +SrcList *sqlite3SrcListAppendFromTerm( + SrcList *p, /* The left part of the FROM clause already seen */ + Token *pTable, /* Name of the table to add to the FROM clause */ + Token *pDatabase, /* Name of the database containing pTable */ + Token *pAlias, /* The right-hand side of the AS subexpression */ + Select *pSubquery, /* A subquery used in place of a table name */ + Expr *pOn, /* The ON clause of a join */ + IdList *pUsing /* The USING clause of a join */ +){ + struct SrcList_item *pItem; + p = sqlite3SrcListAppend(p, pTable, pDatabase); + if( p==0 || p->nSrc==0 ){ + sqlite3ExprDelete(pOn); + sqlite3IdListDelete(pUsing); + sqlite3SelectDelete(pSubquery); + return p; + } + pItem = &p->a[p->nSrc-1]; + if( pAlias && pAlias->n ){ + pItem->zAlias = sqlite3NameFromToken(pAlias); + } + pItem->pSelect = pSubquery; + pItem->pOn = pOn; + pItem->pUsing = pUsing; + return p; +} + +/* +** When building up a FROM clause in the parser, the join operator +** is initially attached to the left operand. But the code generator +** expects the join operator to be on the right operand. This routine +** Shifts all join operators from left to right for an entire FROM +** clause. +** +** Example: Suppose the join is like this: +** +** A natural cross join B +** +** The operator is "natural cross join". The A and B operands are stored +** in p->a[0] and p->a[1], respectively. The parser initially stores the +** operator with A. This routine shifts that operator over to B. +*/ +void sqlite3SrcListShiftJoinType(SrcList *p){ + if( p && p->a ){ + int i; + for(i=p->nSrc-1; i>0; i--){ + p->a[i].jointype = p->a[i-1].jointype; + } + p->a[0].jointype = 0; + } +} + +/* ** Begin a transaction */ void sqlite3BeginTransaction(Parse *pParse, int type){ diff --git a/src/expr.c b/src/expr.c index 2ea27f5b8..1d5e186a0 100644 --- a/src/expr.c +++ b/src/expr.c @@ -12,7 +12,7 @@ ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** -** $Id: expr.c,v 1.269 2006/11/23 11:59:13 drh Exp $ +** $Id: expr.c,v 1.270 2006/12/16 16:25:15 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> @@ -891,22 +891,23 @@ static int lookupName( pExpr->iColumn = j==pTab->iPKey ? -1 : j; pExpr->affinity = pTab->aCol[j].affinity; pExpr->pColl = sqlite3FindCollSeq(db, ENC(db), zColl,-1, 0); - if( pItem->jointype & JT_NATURAL ){ - /* If this match occurred in the left table of a natural join, - ** then skip the right table to avoid a duplicate match */ - pItem++; - i++; - } - if( (pUsing = pItem->pUsing)!=0 ){ - /* If this match occurs on a column that is in the USING clause - ** of a join, skip the search of the right table of the join - ** to avoid a duplicate match there. */ - int k; - for(k=0; k<pUsing->nId; k++){ - if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ){ - pItem++; - i++; - break; + if( i<pSrcList->nSrc-1 ){ + if( pItem[1].jointype & JT_NATURAL ){ + /* If this match occurred in the left table of a natural join, + ** then skip the right table to avoid a duplicate match */ + pItem++; + i++; + }else if( (pUsing = pItem[1].pUsing)!=0 ){ + /* If this match occurs on a column that is in the USING clause + ** of a join, skip the search of the right table of the join + ** to avoid a duplicate match there. */ + int k; + for(k=0; k<pUsing->nId; k++){ + if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ){ + pItem++; + i++; + break; + } } } } diff --git a/src/parse.y b/src/parse.y index 2ba60a7a3..eac525ea1 100644 --- a/src/parse.y +++ b/src/parse.y @@ -14,7 +14,7 @@ ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** -** @(#) $Id: parse.y,v 1.210 2006/09/21 11:02:17 drh Exp $ +** @(#) $Id: parse.y,v 1.211 2006/12/16 16:25:15 drh Exp $ */ // All token codes are small integers with #defines that begin with "TK_" @@ -444,7 +444,10 @@ as(X) ::= . {X.n = 0;} // A complete FROM clause. // from(A) ::= . {A = sqliteMalloc(sizeof(*A));} -from(A) ::= FROM seltablist(X). {A = X;} +from(A) ::= FROM seltablist(X). { + A = X; + sqlite3SrcListShiftJoinType(A); +} // "seltablist" is a "Select Table List" - the content of the FROM clause // in a SELECT statement. "stl_prefix" is a prefix of this list. @@ -455,31 +458,12 @@ stl_prefix(A) ::= seltablist(X) joinop(Y). { } stl_prefix(A) ::= . {A = 0;} seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) on_opt(N) using_opt(U). { - A = sqlite3SrcListAppend(X,&Y,&D); - if( Z.n ) sqlite3SrcListAddAlias(A,&Z); - if( N ){ - if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pOn = N; } - else { sqlite3ExprDelete(N); } - } - if( U ){ - if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pUsing = U; } - else { sqlite3IdListDelete(U); } - } + A = sqlite3SrcListAppendFromTerm(X,&Y,&D,&Z,0,N,U); } %ifndef SQLITE_OMIT_SUBQUERY seltablist(A) ::= stl_prefix(X) LP seltablist_paren(S) RP as(Z) on_opt(N) using_opt(U). { - A = sqlite3SrcListAppend(X,0,0); - if( A && A->nSrc>0 ) A->a[A->nSrc-1].pSelect = S; - if( Z.n ) sqlite3SrcListAddAlias(A,&Z); - if( N ){ - if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pOn = N; } - else { sqlite3ExprDelete(N); } - } - if( U ){ - if( A && A->nSrc>1 ){ A->a[A->nSrc-2].pUsing = U; } - else { sqlite3IdListDelete(U); } - } + A = sqlite3SrcListAppendFromTerm(X,0,0,&Z,S,N,U); } // A seltablist_paren nonterminal represents anything in a FROM that @@ -490,6 +474,7 @@ seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) on_opt(N) using_opt(U). { %destructor seltablist_paren {sqlite3SelectDelete($$);} seltablist_paren(A) ::= select(S). {A = S;} seltablist_paren(A) ::= seltablist(F). { + sqlite3SrcListShiftJoinType(F); A = sqlite3SelectNew(0,F,0,0,0,0,0,0,0); } %endif SQLITE_OMIT_SUBQUERY diff --git a/src/select.c b/src/select.c index 68e348ff9..343a30558 100644 --- a/src/select.c +++ b/src/select.c @@ -12,7 +12,7 @@ ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** -** $Id: select.c,v 1.322 2006/10/13 15:34:17 drh Exp $ +** $Id: select.c,v 1.323 2006/12/16 16:25:15 drh Exp $ */ #include "sqliteInt.h" @@ -301,8 +301,8 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ /* When the NATURAL keyword is present, add WHERE clause terms for ** every column that the two tables have in common. */ - if( pLeft->jointype & JT_NATURAL ){ - if( pLeft->pOn || pLeft->pUsing ){ + if( pRight->jointype & JT_NATURAL ){ + if( pRight->pOn || pRight->pUsing ){ sqlite3ErrorMsg(pParse, "a NATURAL join may not have " "an ON or USING clause", 0); return 1; @@ -320,7 +320,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ /* Disallow both ON and USING clauses in the same join */ - if( pLeft->pOn && pLeft->pUsing ){ + if( pRight->pOn && pRight->pUsing ){ sqlite3ErrorMsg(pParse, "cannot have both ON and USING " "clauses in the same join"); return 1; @@ -329,10 +329,10 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ /* Add the ON clause to the end of the WHERE clause, connected by ** an AND operator. */ - if( pLeft->pOn ){ - setJoinExpr(pLeft->pOn, pRight->iCursor); - p->pWhere = sqlite3ExprAnd(p->pWhere, pLeft->pOn); - pLeft->pOn = 0; + if( pRight->pOn ){ + setJoinExpr(pRight->pOn, pRight->iCursor); + p->pWhere = sqlite3ExprAnd(p->pWhere, pRight->pOn); + pRight->pOn = 0; } /* Create extra terms on the WHERE clause for each column named @@ -342,8 +342,8 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){ ** Report an error if any column mentioned in the USING clause is ** not contained in both tables to be joined. */ - if( pLeft->pUsing ){ - IdList *pList = pLeft->pUsing; + if( pRight->pUsing ){ + IdList *pList = pRight->pUsing; for(j=0; j<pList->nId; j++){ char *zName = pList->a[j].zName; if( columnIndex(pLeftTab, zName)<0 || columnIndex(pRightTab, zName)<0 ){ @@ -1309,13 +1309,13 @@ static int prepSelectStmt(Parse *pParse, Select *p){ if( i>0 ){ struct SrcList_item *pLeft = &pTabList->a[i-1]; - if( (pLeft->jointype & JT_NATURAL)!=0 && + if( (pLeft[1].jointype & JT_NATURAL)!=0 && columnIndex(pLeft->pTab, zName)>=0 ){ /* In a NATURAL join, omit the join columns from the ** table on the right */ continue; } - if( sqlite3IdListIndex(pLeft->pUsing, zName)>=0 ){ + if( sqlite3IdListIndex(pLeft[1].pUsing, zName)>=0 ){ /* In a join with a USING clause, omit columns in the ** using clause from the table on the right. */ continue; @@ -2175,7 +2175,7 @@ static int flattenSubquery( ** ** which is not at all the same thing. */ - if( pSubSrc->nSrc>1 && iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 ){ + if( pSubSrc->nSrc>1 && (pSubitem->jointype & JT_OUTER)!=0 ){ return 0; } @@ -2192,8 +2192,7 @@ static int flattenSubquery( ** But the t2.x>0 test will always fail on a NULL row of t2, which ** effectively converts the OUTER JOIN into an INNER JOIN. */ - if( iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 - && pSub->pWhere!=0 ){ + if( (pSubitem->jointype & JT_OUTER)!=0 && pSub->pWhere!=0 ){ return 0; } @@ -2232,7 +2231,7 @@ static int flattenSubquery( pSrc->a[i+iFrom] = pSubSrc->a[i]; memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i])); } - pSrc->a[iFrom+nSubSrc-1].jointype = jointype; + pSrc->a[iFrom].jointype = jointype; } /* Now begin substituting subquery result set expressions for diff --git a/src/sqliteInt.h b/src/sqliteInt.h index a3d4317d2..b000f0ae3 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -11,7 +11,7 @@ ************************************************************************* ** Internal interface definitions for SQLite. ** -** @(#) $Id: sqliteInt.h,v 1.530 2006/11/09 00:24:54 drh Exp $ +** @(#) $Id: sqliteInt.h,v 1.531 2006/12/16 16:25:16 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ @@ -1617,7 +1617,9 @@ int sqlite3ArrayAllocate(void**,int,int); IdList *sqlite3IdListAppend(IdList*, Token*); int sqlite3IdListIndex(IdList*,const char*); SrcList *sqlite3SrcListAppend(SrcList*, Token*, Token*); -void sqlite3SrcListAddAlias(SrcList*, Token*); +SrcList *sqlite3SrcListAppendFromTerm(SrcList*, Token*, Token*, Token*, + Select*, Expr*, IdList*); +void sqlite3SrcListShiftJoinType(SrcList*); void sqlite3SrcListAssignCursors(Parse*, SrcList*); void sqlite3IdListDelete(IdList*); void sqlite3SrcListDelete(SrcList*); diff --git a/src/where.c b/src/where.c index 700485ebf..1d640cb26 100644 --- a/src/where.c +++ b/src/where.c @@ -16,7 +16,7 @@ ** 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.232 2006/11/06 15:10:05 drh Exp $ +** $Id: where.c,v 1.233 2006/12/16 16:25:16 drh Exp $ */ #include "sqliteInt.h" @@ -1854,8 +1854,7 @@ WhereInfo *sqlite3WhereBegin( for(j=iFrom, pTabItem=&pTabList->a[j]; j<pTabList->nSrc; j++, pTabItem++){ int doNotReorder; /* True if this table should not be reordered */ - doNotReorder = (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0 - || (j>0 && (pTabItem[-1].jointype & (JT_LEFT|JT_CROSS))!=0); + doNotReorder = (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0; if( once && doNotReorder ) break; m = getMask(&maskSet, pTabItem->iCursor); if( (m & notReady)==0 ){ @@ -2031,7 +2030,7 @@ WhereInfo *sqlite3WhereBegin( ** initialize a memory cell that records if this table matches any ** row of the left table of the join. */ - if( pLevel->iFrom>0 && (pTabItem[-1].jointype & JT_LEFT)!=0 ){ + if( pLevel->iFrom>0 && (pTabItem[0].jointype & JT_LEFT)!=0 ){ if( !pParse->nMem ) pParse->nMem++; pLevel->iLeftJoin = pParse->nMem++; sqlite3VdbeAddOp(v, OP_MemInt, 0, pLevel->iLeftJoin); diff --git a/test/where3.test b/test/where3.test index d1f6d4a7d..30e0976e8 100644 --- a/test/where3.test +++ b/test/where3.test @@ -12,7 +12,7 @@ # focus of this file is testing the join reordering optimization # in cases that include a LEFT JOIN. # -# $Id: where3.test,v 1.2 2006/06/06 11:45:55 drh Exp $ +# $Id: where3.test,v 1.3 2006/12/16 16:25:17 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -78,4 +78,85 @@ do_test where3-1.2 { } } {1 {Value for C1.1} {Value for C2.1} 2 {} {Value for C2.2} 3 {Value for C1.3} {Value for C2.3}} +# This procedure executes the SQL. Then it appends +# the ::sqlite_query_plan variable. +# +proc queryplan {sql} { + set ::sqlite_sort_count 0 + set data [execsql $sql] + return [concat $data $::sqlite_query_plan] +} + + +# If you have a from clause of the form: A B C left join D +# then make sure the query optimizer is able to reorder the +# A B C part anyway it wants. +# +# Following the fix to ticket #1652, there was a time when +# the C table would not reorder. So the following reorderings +# were possible: +# +# A B C left join D +# B A C left join D +# +# But these reorders were not allowed +# +# C A B left join D +# A C B left join D +# C B A left join D +# B C A left join D +# +# The following tests are here to verify that the latter four +# reorderings are allowed again. +# +do_test where3-2.1 { + execsql { + CREATE TABLE tA(apk integer primary key, ax); + CREATE TABLE tB(bpk integer primary key, bx); + CREATE TABLE tC(cpk integer primary key, cx); + CREATE TABLE tD(dpk integer primary key, dx); + } + queryplan { + SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx + WHERE cpk=bx AND bpk=ax + } +} {tA {} tB * tC * tD *} +do_test where3-2.2 { + queryplan { + SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx + WHERE cpk=bx AND apk=bx + } +} {tB {} tA * tC * tD *} +do_test where3-2.3 { + queryplan { + SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx + WHERE cpk=bx AND apk=bx + } +} {tB {} tA * tC * tD *} +do_test where3-2.4 { + queryplan { + SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx + WHERE apk=cx AND bpk=ax + } +} {tC {} tA * tB * tD *} +do_test where3-2.5 { + queryplan { + SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx + WHERE cpk=ax AND bpk=cx + } +} {tA {} tC * tB * tD *} +do_test where3-2.5 { + queryplan { + SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx + WHERE bpk=cx AND apk=bx + } +} {tC {} tB * tA * tD *} +do_test where3-2.6 { + queryplan { + SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx + WHERE cpk=bx AND apk=cx + } +} {tB {} tC * tA * tD *} + + finish_test |