/ Check-in [d5880abd]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Correctly handle an ORDER BY clause on an outer query when applying the compound-subquery flattening optimization. Ticket [d11a6e908f]. Also add the SQLITE_ENABLE_SELECTTRACE option for additional debugging and analysis information about select statement processing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d5880abd63c83c88e135257373afa0a3fd88297e
User & Date: drh 2014-09-21 22:31:52
Original Comment: Correctly handle an ORDER BY clause on an outer query when applying the compound-subquery flattening optimization. Ticket [89398880bcfff]. Also add the SQLITE_ENABLE_SELECTTRACE option for additional debugging and analysis information about select statement processing.
Context
2014-09-22
03:22
Disable shared memory operations using the unix-nolock VFS. check-in: 10a6e510 user: drh tags: trunk
2014-09-21
23:08
Merge in all recent changes from trunk. check-in: 3967ebe8 user: drh tags: apple-osx
22:49
Merge all recent trunk changes into the sessions branch. check-in: 6406b77f user: drh tags: sessions
22:31
Correctly handle an ORDER BY clause on an outer query when applying the compound-subquery flattening optimization. Ticket [d11a6e908f]. Also add the SQLITE_ENABLE_SELECTTRACE option for additional debugging and analysis information about select statement processing. check-in: d5880abd user: drh tags: trunk
20:31
Add test cases for ticket [d11a6e908f]. Closed-Leaf check-in: 9683e001 user: drh tags: select-trace
2014-09-20
00:35
Fix the usage of the _GNU_SOURCE and _BSD_SOURCE macros in the main internal header file, sqliteInt.h. Set HAVE_STRCHRNUL to 1 by default on Linux only. check-in: 59e2c9df user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/misc/showauth.c.

            1  +/*
            2  +** 2014-09-21
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +******************************************************************************
           12  +**
           13  +** This SQLite extension adds a debug "authorizer" callback to the database
           14  +** connection.  The callback merely writes the authorization request to
           15  +** standard output and returns SQLITE_OK.
           16  +**
           17  +** This extension can be used (for example) in the command-line shell to
           18  +** trace the operation of the authorizer.
           19  +*/
           20  +#include "sqlite3ext.h"
           21  +SQLITE_EXTENSION_INIT1
           22  +#include <stdio.h>
           23  +
           24  +/*
           25  +** Display the authorization request
           26  +*/
           27  +static int authCallback(
           28  +  void *pClientData,
           29  +  int op,
           30  +  const char *z1,
           31  +  const char *z2,
           32  +  const char *z3,
           33  +  const char *z4
           34  +){
           35  +  const char *zOp;
           36  +  char zOpSpace[50];
           37  +  switch( op ){
           38  +    case SQLITE_CREATE_INDEX:        zOp = "CREATE_INDEX";        break;
           39  +    case SQLITE_CREATE_TABLE:        zOp = "CREATE_TABLE";        break;
           40  +    case SQLITE_CREATE_TEMP_INDEX:   zOp = "CREATE_TEMP_INDEX";   break;
           41  +    case SQLITE_CREATE_TEMP_TABLE:   zOp = "CREATE_TEMP_TABLE";   break;
           42  +    case SQLITE_CREATE_TEMP_TRIGGER: zOp = "CREATE_TEMP_TRIGGER"; break;
           43  +    case SQLITE_CREATE_TEMP_VIEW:    zOp = "CREATE_TEMP_VIEW";    break;
           44  +    case SQLITE_CREATE_TRIGGER:      zOp = "CREATE_TRIGGER";      break;
           45  +    case SQLITE_CREATE_VIEW:         zOp = "CREATE_VIEW";         break;
           46  +    case SQLITE_DELETE:              zOp = "DELETE";              break;
           47  +    case SQLITE_DROP_INDEX:          zOp = "DROP_INDEX";          break;
           48  +    case SQLITE_DROP_TABLE:          zOp = "DROP_TABLE";          break;
           49  +    case SQLITE_DROP_TEMP_INDEX:     zOp = "DROP_TEMP_INDEX";     break;
           50  +    case SQLITE_DROP_TEMP_TABLE:     zOp = "DROP_TEMP_TABLE";     break;
           51  +    case SQLITE_DROP_TEMP_TRIGGER:   zOp = "DROP_TEMP_TRIGGER";   break;
           52  +    case SQLITE_DROP_TEMP_VIEW:      zOp = "DROP_TEMP_VIEW";      break;
           53  +    case SQLITE_DROP_TRIGGER:        zOp = "DROP_TRIGGER";        break;
           54  +    case SQLITE_DROP_VIEW:           zOp = "DROP_VIEW";           break;
           55  +    case SQLITE_INSERT:              zOp = "INSERT";              break;
           56  +    case SQLITE_PRAGMA:              zOp = "PRAGMA";              break;
           57  +    case SQLITE_READ:                zOp = "READ";                break;
           58  +    case SQLITE_SELECT:              zOp = "SELECT";              break;
           59  +    case SQLITE_TRANSACTION:         zOp = "TRANSACTION";         break;
           60  +    case SQLITE_UPDATE:              zOp = "UPDATE";              break;
           61  +    case SQLITE_ATTACH:              zOp = "ATTACH";              break;
           62  +    case SQLITE_DETACH:              zOp = "DETACH";              break;
           63  +    case SQLITE_ALTER_TABLE:         zOp = "ALTER_TABLE";         break;
           64  +    case SQLITE_REINDEX:             zOp = "REINDEX";             break;
           65  +    case SQLITE_ANALYZE:             zOp = "ANALYZE";             break;
           66  +    case SQLITE_CREATE_VTABLE:       zOp = "CREATE_VTABLE";       break;
           67  +    case SQLITE_DROP_VTABLE:         zOp = "DROP_VTABLE";         break;
           68  +    case SQLITE_FUNCTION:            zOp = "FUNCTION";            break;
           69  +    case SQLITE_SAVEPOINT:           zOp = "SAVEPOINT";           break;
           70  +    case SQLITE_COPY:                zOp = "COPY";                break;
           71  +    case SQLITE_RECURSIVE:           zOp = "RECURSIVE";           break;
           72  +
           73  +
           74  +    default: {
           75  +      sqlite3_snprintf(sizeof(zOpSpace), zOpSpace, "%d", op);
           76  +      zOp = zOpSpace;
           77  +      break;
           78  +    }
           79  +  }
           80  +  if( z1==0 ) z1 = "NULL";
           81  +  if( z2==0 ) z2 = "NULL";
           82  +  if( z3==0 ) z3 = "NULL";
           83  +  if( z4==0 ) z4 = "NULL";
           84  +  printf("AUTH: %s,%s,%s,%s,%s\n", zOp, z1, z2, z3, z4);
           85  +  return SQLITE_OK;
           86  +}
           87  +
           88  +
           89  +
           90  +#ifdef _WIN32
           91  +__declspec(dllexport)
           92  +#endif
           93  +int sqlite3_showauth_init(
           94  +  sqlite3 *db, 
           95  +  char **pzErrMsg, 
           96  +  const sqlite3_api_routines *pApi
           97  +){
           98  +  int rc = SQLITE_OK;
           99  +  SQLITE_EXTENSION_INIT2(pApi);
          100  +  (void)pzErrMsg;  /* Unused parameter */
          101  +  rc = sqlite3_set_authorizer(db, authCallback, 0);
          102  +  return rc;
          103  +}

Changes to src/expr.c.

  1065   1065     pNew->iLimit = 0;
  1066   1066     pNew->iOffset = 0;
  1067   1067     pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  1068   1068     pNew->addrOpenEphm[0] = -1;
  1069   1069     pNew->addrOpenEphm[1] = -1;
  1070   1070     pNew->nSelectRow = p->nSelectRow;
  1071   1071     pNew->pWith = withDup(db, p->pWith);
         1072  +  sqlite3SelectSetName(pNew, p->zSelName);
  1072   1073     return pNew;
  1073   1074   }
  1074   1075   #else
  1075   1076   Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){
  1076   1077     assert( p==0 );
  1077   1078     return 0;
  1078   1079   }

Changes to src/parse.y.

   455    455     A = pRhs;
   456    456   }
   457    457   %type multiselect_op {int}
   458    458   multiselect_op(A) ::= UNION(OP).             {A = @OP;}
   459    459   multiselect_op(A) ::= UNION ALL.             {A = TK_ALL;}
   460    460   multiselect_op(A) ::= EXCEPT|INTERSECT(OP).  {A = @OP;}
   461    461   %endif SQLITE_OMIT_COMPOUND_SELECT
   462         -oneselect(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y)
          462  +oneselect(A) ::= SELECT(S) distinct(D) selcollist(W) from(X) where_opt(Y)
   463    463                    groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). {
   464    464     A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset);
          465  +#if SELECTTRACE_ENABLED
          466  +  /* Populate the Select.zSelName[] string that is used to help with
          467  +  ** query planner debugging, to differentiate between multiple Select
          468  +  ** objects in a complex query.
          469  +  **
          470  +  ** If the SELECT keyword is immediately followed by a C-style comment
          471  +  ** then extract the first few alphanumeric characters from within that
          472  +  ** comment to be the zSelName value.  Otherwise, the label is #N where
          473  +  ** is an integer that is incremented with each SELECT statement seen.
          474  +  */
          475  +  if( A!=0 ){
          476  +    const char *z = S.z+6;
          477  +    int i;
          478  +    sqlite3_snprintf(sizeof(A->zSelName), A->zSelName, "#%d",
          479  +                     ++pParse->nSelect);
          480  +    while( z[0]==' ' ) z++;
          481  +    if( z[0]=='/' && z[1]=='*' ){
          482  +      z += 2;
          483  +      while( z[0]==' ' ) z++;
          484  +      for(i=0; sqlite3Isalnum(z[i]); i++){}
          485  +      sqlite3_snprintf(sizeof(A->zSelName), A->zSelName, "%.*s", i, z);
          486  +    }
          487  +  }
          488  +#endif /* SELECTRACE_ENABLED */
   465    489   }
   466    490   oneselect(A) ::= values(X).    {A = X;}
   467    491   
   468    492   %type values {Select*}
   469    493   %destructor values {sqlite3SelectDelete(pParse->db, $$);}
   470    494   values(A) ::= VALUES LP nexprlist(X) RP. {
   471    495     A = sqlite3SelectNew(pParse,X,0,0,0,0,0,SF_Values,0,0);

Changes to src/select.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   */
    15     15   #include "sqliteInt.h"
           16  +
           17  +/*
           18  +** Trace output macros
           19  +*/
           20  +#if SELECTTRACE_ENABLED
           21  +/***/ int sqlite3SelectTrace = 0;
           22  +# define SELECTTRACE(K,P,S,X)  \
           23  +  if(sqlite3SelectTrace&(K))   \
           24  +    sqlite3DebugPrintf("%*s%s.%p: ",(P)->nSelectIndent*2-2,"",(S)->zSelName,(S)),\
           25  +    sqlite3DebugPrintf X
           26  +#else
           27  +# define SELECTTRACE(K,P,S,X)
           28  +#endif
           29  +
    16     30   
    17     31   /*
    18     32   ** An instance of the following object is used to record information about
    19     33   ** how to process the DISTINCT keyword, to simplify passing that information
    20     34   ** into the selectInnerLoop() routine.
    21     35   */
    22     36   typedef struct DistinctCtx DistinctCtx;
................................................................................
   121    135       pNew = 0;
   122    136     }else{
   123    137       assert( pNew->pSrc!=0 || pParse->nErr>0 );
   124    138     }
   125    139     assert( pNew!=&standin );
   126    140     return pNew;
   127    141   }
          142  +
          143  +#if SELECTTRACE_ENABLED
          144  +/*
          145  +** Set the name of a Select object
          146  +*/
          147  +void sqlite3SelectSetName(Select *p, const char *zName){
          148  +  if( p && zName ){
          149  +    sqlite3_snprintf(sizeof(p->zSelName), p->zSelName, "%s", zName);
          150  +  }
          151  +}
          152  +#endif
          153  +
   128    154   
   129    155   /*
   130    156   ** Delete the given Select structure and all of its substructures.
   131    157   */
   132    158   void sqlite3SelectDelete(sqlite3 *db, Select *p){
   133    159     if( p ){
   134    160       clearSelect(db, p);
................................................................................
  3351   3377         for(ii=0; ii<p->pOrderBy->nExpr; ii++){
  3352   3378           if( p->pOrderBy->a[ii].u.x.iOrderByCol==0 ) return 0;
  3353   3379         }
  3354   3380       }
  3355   3381     }
  3356   3382   
  3357   3383     /***** If we reach this point, flattening is permitted. *****/
         3384  +  SELECTTRACE(1,pParse,p,("flatten %s.%p from term %d\n",
         3385  +                   pSub->zSelName, pSub, iFrom));
  3358   3386   
  3359   3387     /* Authorize the subquery */
  3360   3388     pParse->zAuthContext = pSubitem->zName;
  3361   3389     TESTONLY(i =) sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0);
  3362   3390     testcase( i==SQLITE_DENY );
  3363   3391     pParse->zAuthContext = zSavedAuthContext;
  3364   3392   
................................................................................
  3403   3431       Select *pPrior = p->pPrior;
  3404   3432       p->pOrderBy = 0;
  3405   3433       p->pSrc = 0;
  3406   3434       p->pPrior = 0;
  3407   3435       p->pLimit = 0;
  3408   3436       p->pOffset = 0;
  3409   3437       pNew = sqlite3SelectDup(db, p, 0);
         3438  +    sqlite3SelectSetName(pNew, pSub->zSelName);
  3410   3439       p->pOffset = pOffset;
  3411   3440       p->pLimit = pLimit;
  3412   3441       p->pOrderBy = pOrderBy;
  3413   3442       p->pSrc = pSrc;
  3414   3443       p->op = TK_ALL;
  3415   3444       if( pNew==0 ){
  3416   3445         p->pPrior = pPrior;
  3417   3446       }else{
  3418   3447         pNew->pPrior = pPrior;
  3419   3448         if( pPrior ) pPrior->pNext = pNew;
  3420   3449         pNew->pNext = p;
  3421   3450         p->pPrior = pNew;
         3451  +      SELECTTRACE(2,pParse,p,
         3452  +         ("compound-subquery flattener creates %s.%p as peer\n",
         3453  +         pNew->zSelName, pNew));
  3422   3454       }
  3423   3455       if( db->mallocFailed ) return 1;
  3424   3456     }
  3425   3457   
  3426   3458     /* Begin flattening the iFrom-th entry of the FROM clause 
  3427   3459     ** in the outer query.
  3428   3460     */
................................................................................
  3544   3576       }
  3545   3577       substExprList(db, pParent->pEList, iParent, pSub->pEList);
  3546   3578       if( isAgg ){
  3547   3579         substExprList(db, pParent->pGroupBy, iParent, pSub->pEList);
  3548   3580         pParent->pHaving = substExpr(db, pParent->pHaving, iParent, pSub->pEList);
  3549   3581       }
  3550   3582       if( pSub->pOrderBy ){
         3583  +      /* At this point, any non-zero iOrderByCol values indicate that the
         3584  +      ** ORDER BY column expression is identical to the iOrderByCol'th
         3585  +      ** expression returned by SELECT statement pSub. Since these values
         3586  +      ** do not necessarily correspond to columns in SELECT statement pParent,
         3587  +      ** zero them before transfering the ORDER BY clause.
         3588  +      **
         3589  +      ** Not doing this may cause an error if a subsequent call to this
         3590  +      ** function attempts to flatten a compound sub-query into pParent
         3591  +      ** (the only way this can happen is if the compound sub-query is
         3592  +      ** currently part of pSub->pSrc). See ticket [d11a6e908f].  */
         3593  +      ExprList *pOrderBy = pSub->pOrderBy;
         3594  +      for(i=0; i<pOrderBy->nExpr; i++){
         3595  +        pOrderBy->a[i].u.x.iOrderByCol = 0;
         3596  +      }
  3551   3597         assert( pParent->pOrderBy==0 );
  3552         -      pParent->pOrderBy = pSub->pOrderBy;
         3598  +      assert( pSub->pPrior==0 );
         3599  +      pParent->pOrderBy = pOrderBy;
  3553   3600         pSub->pOrderBy = 0;
  3554   3601       }else if( pParent->pOrderBy ){
  3555   3602         substExprList(db, pParent->pOrderBy, iParent, pSub->pEList);
  3556   3603       }
  3557   3604       if( pSub->pWhere ){
  3558   3605         pWhere = sqlite3ExprDup(db, pSub->pWhere, 0);
  3559   3606       }else{
................................................................................
  4061   4108         pTab->nRef++;
  4062   4109   #if !defined(SQLITE_OMIT_VIEW) || !defined (SQLITE_OMIT_VIRTUALTABLE)
  4063   4110         if( pTab->pSelect || IsVirtual(pTab) ){
  4064   4111           /* We reach here if the named table is a really a view */
  4065   4112           if( sqlite3ViewGetColumnNames(pParse, pTab) ) return WRC_Abort;
  4066   4113           assert( pFrom->pSelect==0 );
  4067   4114           pFrom->pSelect = sqlite3SelectDup(db, pTab->pSelect, 0);
         4115  +        sqlite3SelectSetName(pFrom->pSelect, pTab->zName);
  4068   4116           sqlite3WalkSelect(pWalker, pFrom->pSelect);
  4069   4117         }
  4070   4118   #endif
  4071   4119       }
  4072   4120   
  4073   4121       /* Locate the index named by the INDEXED BY clause, if any. */
  4074   4122       if( sqlite3IndexedByLookup(pParse, pFrom) ){
................................................................................
  4595   4643   
  4596   4644     db = pParse->db;
  4597   4645     if( p==0 || db->mallocFailed || pParse->nErr ){
  4598   4646       return 1;
  4599   4647     }
  4600   4648     if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  4601   4649     memset(&sAggInfo, 0, sizeof(sAggInfo));
         4650  +#if SELECTTRACE_ENABLED
         4651  +  pParse->nSelectIndent++;
         4652  +  SELECTTRACE(1,pParse,p, ("begin processing\n"));
         4653  +#endif
  4602   4654   
  4603   4655     assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistFifo );
  4604   4656     assert( p->pOrderBy==0 || pDest->eDest!=SRT_Fifo );
  4605   4657     assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistQueue );
  4606   4658     assert( p->pOrderBy==0 || pDest->eDest!=SRT_Queue );
  4607   4659     if( IgnorableOrderby(pDest) ){
  4608   4660       assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
................................................................................
  4751   4803   
  4752   4804   #ifndef SQLITE_OMIT_COMPOUND_SELECT
  4753   4805     /* If there is are a sequence of queries, do the earlier ones first.
  4754   4806     */
  4755   4807     if( p->pPrior ){
  4756   4808       rc = multiSelect(pParse, p, pDest);
  4757   4809       explainSetInteger(pParse->iSelectId, iRestoreSelectId);
         4810  +#if SELECTTRACE_ENABLED
         4811  +    SELECTTRACE(1,pParse,p,("end compound-select processing\n"));
         4812  +    pParse->nSelectIndent--;
         4813  +#endif
  4758   4814       return rc;
  4759   4815     }
  4760   4816   #endif
  4761   4817   
  4762   4818     /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 
  4763   4819     ** if the select-list is the same as the ORDER BY list, then this query
  4764   4820     ** can be rewritten as a GROUP BY. In other words, this:
................................................................................
  5350   5406     */
  5351   5407     if( rc==SQLITE_OK && pDest->eDest==SRT_Output ){
  5352   5408       generateColumnNames(pParse, pTabList, pEList);
  5353   5409     }
  5354   5410   
  5355   5411     sqlite3DbFree(db, sAggInfo.aCol);
  5356   5412     sqlite3DbFree(db, sAggInfo.aFunc);
         5413  +#if SELECTTRACE_ENABLED
         5414  +  SELECTTRACE(1,pParse,p,("end processing\n"));
         5415  +  pParse->nSelectIndent--;
         5416  +#endif
  5357   5417     return rc;
  5358   5418   }
  5359   5419   
  5360   5420   #if defined(SQLITE_ENABLE_TREE_EXPLAIN)
  5361   5421   /*
  5362   5422   ** Generate a human-readable description of a the Select object.
  5363   5423   */

Changes to src/shell.c.

  3096   3096       }else if( rc != SQLITE_OK ){
  3097   3097         fprintf(stderr,"Error: querying schema information\n");
  3098   3098         rc = 1;
  3099   3099       }else{
  3100   3100         rc = 0;
  3101   3101       }
  3102   3102     }else
         3103  +
         3104  +
         3105  +#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
         3106  +  if( c=='s' && n==11 && strncmp(azArg[0], "selecttrace", n)==0 ){
         3107  +    extern int sqlite3SelectTrace;
         3108  +    sqlite3SelectTrace = nArg>=2 ? booleanValue(azArg[1]) : 0xff;
         3109  +  }else
         3110  +#endif
         3111  +
  3103   3112   
  3104   3113   #ifdef SQLITE_DEBUG
  3105   3114     /* Undocumented commands for internal testing.  Subject to change
  3106   3115     ** without notice. */
  3107   3116     if( c=='s' && n>=10 && strncmp(azArg[0], "selftest-", 9)==0 ){
  3108   3117       if( strncmp(azArg[0]+9, "boolean", n-9)==0 ){
  3109   3118         int i, v;

Changes to src/sqliteInt.h.

   702    702   # define SQLITE_ENABLE_STAT3_OR_STAT4 1
   703    703   #elif SQLITE_ENABLE_STAT3
   704    704   # define SQLITE_ENABLE_STAT3_OR_STAT4 1
   705    705   #elif SQLITE_ENABLE_STAT3_OR_STAT4
   706    706   # undef SQLITE_ENABLE_STAT3_OR_STAT4
   707    707   #endif
   708    708   
          709  +/*
          710  +** SELECTTRACE_ENABLED will be either 1 or 0 depending on whether or not
          711  +** the Select query generator tracing logic is turned on.
          712  +*/
          713  +#if defined(SQLITE_DEBUG) || defined(SQLITE_ENABLE_SELECTTRACE)
          714  +# define SELECTTRACE_ENABLED 1
          715  +#else
          716  +# define SELECTTRACE_ENABLED 0
          717  +#endif
          718  +
   709    719   /*
   710    720   ** An instance of the following structure is used to store the busy-handler
   711    721   ** callback for a given sqlite handle. 
   712    722   **
   713    723   ** The sqlite.busyHandler member of the sqlite struct contains the busy
   714    724   ** callback for the database handle. Each pager opened via the sqlite
   715    725   ** handle is passed a pointer to sqlite.busyHandler. The busy-handler
................................................................................
  2296   2306   ** sequences for the ORDER BY clause.
  2297   2307   */
  2298   2308   struct Select {
  2299   2309     ExprList *pEList;      /* The fields of the result */
  2300   2310     u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  2301   2311     u16 selFlags;          /* Various SF_* values */
  2302   2312     int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
         2313  +#if SELECTTRACE_ENABLED
         2314  +  char zSelName[12];     /* Symbolic name of this SELECT use for debugging */
         2315  +#endif
  2303   2316     int addrOpenEphm[2];   /* OP_OpenEphem opcodes related to this select */
  2304   2317     u64 nSelectRow;        /* Estimated number of result rows */
  2305   2318     SrcList *pSrc;         /* The FROM clause */
  2306   2319     Expr *pWhere;          /* The WHERE clause */
  2307   2320     ExprList *pGroupBy;    /* The GROUP BY clause */
  2308   2321     Expr *pHaving;         /* The HAVING clause */
  2309   2322     ExprList *pOrderBy;    /* The ORDER BY clause */
................................................................................
  2554   2567     Token constraintName;/* Name of the constraint currently being parsed */
  2555   2568     yDbMask writeMask;   /* Start a write transaction on these databases */
  2556   2569     yDbMask cookieMask;  /* Bitmask of schema verified databases */
  2557   2570     int cookieValue[SQLITE_MAX_ATTACHED+2];  /* Values of cookies to verify */
  2558   2571     int regRowid;        /* Register holding rowid of CREATE TABLE entry */
  2559   2572     int regRoot;         /* Register holding root page number for new objects */
  2560   2573     int nMaxArg;         /* Max args passed to user function by sub-program */
         2574  +#if SELECTTRACE_ENABLED
         2575  +  int nSelect;         /* Number of SELECT statements seen */
         2576  +  int nSelectIndent;   /* How far to indent SELECTTRACE() output */
         2577  +#endif
  2561   2578   #ifndef SQLITE_OMIT_SHARED_CACHE
  2562   2579     int nTableLock;        /* Number of locks in aTableLock */
  2563   2580     TableLock *aTableLock; /* Required table locks for shared-cache mode */
  2564   2581   #endif
  2565   2582     AutoincInfo *pAinc;  /* Information about AUTOINCREMENT counters */
  2566   2583   
  2567   2584     /* Information used while coding trigger programs. */
................................................................................
  3288   3305   void sqlite3UniqueConstraint(Parse*, int, Index*);
  3289   3306   void sqlite3RowidConstraint(Parse*, int, Table*);
  3290   3307   Expr *sqlite3ExprDup(sqlite3*,Expr*,int);
  3291   3308   ExprList *sqlite3ExprListDup(sqlite3*,ExprList*,int);
  3292   3309   SrcList *sqlite3SrcListDup(sqlite3*,SrcList*,int);
  3293   3310   IdList *sqlite3IdListDup(sqlite3*,IdList*);
  3294   3311   Select *sqlite3SelectDup(sqlite3*,Select*,int);
         3312  +#if SELECTTRACE_ENABLED
         3313  +void sqlite3SelectSetName(Select*,const char*);
         3314  +#else
         3315  +# define sqlite3SelectSetName(A,B)
         3316  +#endif
  3295   3317   void sqlite3FuncDefInsert(FuncDefHash*, FuncDef*);
  3296   3318   FuncDef *sqlite3FindFunction(sqlite3*,const char*,int,int,u8,u8);
  3297   3319   void sqlite3RegisterBuiltinFunctions(sqlite3*);
  3298   3320   void sqlite3RegisterDateTimeFunctions(void);
  3299   3321   void sqlite3RegisterGlobalFunctions(void);
  3300   3322   int sqlite3SafetyCheckOk(sqlite3*);
  3301   3323   int sqlite3SafetyCheckSickOrOk(sqlite3*);

Changes to test/subquery2.test.

    98     98   }
    99     99   
   100    100   do_execsql_test 2.2 {
   101    101     SELECT * 
   102    102     FROM (SELECT * FROM t4 ORDER BY a LIMIT -1 OFFSET 1) 
   103    103     LIMIT (SELECT a FROM t5)
   104    104   } {2 3   3 6   4 10}
          105  +
          106  +############################################################################
          107  +# Ticket http://www.sqlite.org/src/info/d11a6e908f (2014-09-20)
          108  +# Query planner fault on three-way nested join with compound inner SELECT 
          109  +#
          110  +do_execsql_test 3.0 {
          111  +  DROP TABLE IF EXISTS t1;
          112  +  DROP TABLE IF EXISTS t2;
          113  +  CREATE TABLE t1 (id INTEGER PRIMARY KEY, data TEXT);
          114  +  INSERT INTO t1(id,data) VALUES(9,'nine-a');
          115  +  INSERT INTO t1(id,data) VALUES(10,'ten-a');
          116  +  INSERT INTO t1(id,data) VALUES(11,'eleven-a');
          117  +  CREATE TABLE t2 (id INTEGER PRIMARY KEY, data TEXT);
          118  +  INSERT INTO t2(id,data) VALUES(9,'nine-b');
          119  +  INSERT INTO t2(id,data) VALUES(10,'ten-b');
          120  +  INSERT INTO t2(id,data) VALUES(11,'eleven-b');
          121  +  
          122  +  SELECT id FROM (
          123  +    SELECT id,data FROM (
          124  +       SELECT * FROM t1 UNION ALL SELECT * FROM t2
          125  +    )
          126  +    WHERE id=10 ORDER BY data
          127  +  );
          128  +} {10 10}
          129  +do_execsql_test 3.1 {
          130  +  SELECT data FROM (
          131  +     SELECT 'dummy', data FROM (
          132  +       SELECT data FROM t1 UNION ALL SELECT data FROM t1
          133  +     ) ORDER BY data
          134  +  );
          135  +} {eleven-a eleven-a nine-a nine-a ten-a ten-a}
          136  +do_execsql_test 3.2 {
          137  +  DROP TABLE IF EXISTS t3;
          138  +  DROP TABLE IF EXISTS t4;
          139  +  CREATE TABLE t3(id INTEGER, data TEXT);
          140  +  CREATE TABLE t4(id INTEGER, data TEXT);
          141  +  INSERT INTO t3 VALUES(4, 'a'),(2,'c');
          142  +  INSERT INTO t4 VALUES(3, 'b'),(1,'d');
          143  +
          144  +  SELECT data, id FROM (
          145  +    SELECT id, data FROM (
          146  +       SELECT * FROM t3 UNION ALL SELECT * FROM t4
          147  +    ) ORDER BY data
          148  +  );
          149  +} {a 4 b 3 c 2 d 1}
   105    150   
   106    151   
   107    152   finish_test