/ Check-in [7fecced4]
Login

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

Overview
Comment:Optimize the degenerate case of a FROM clause table name enclosed all by itself inside parentheses. Generate code as if the parentheses did not exist, rather than the old behavior of manifesting the parenthesized table into a transient table. Also, tag every FROM-clause SELECT subquery that is generated by a parenthesized FROM-clause expression using the SF_NestedFrom flag. The new SF_NestedFrom flag is not yet used for anything.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7fecced466d86a66b0b751c5b5608141e134fe2d
User & Date: drh 2012-12-18 19:36:11
References
2013-04-30
13:26 New ticket [28c6e830] Segfault while parsing/compiling an SQL statement. artifact: a81f8ab6 user: drh
Context
2012-12-19
16:58
Fix the segfault problem of ticket [a7b7803e8d1e869] which involved the use of "AS" named result columns as logical terms of the WHERE clause. check-in: b3c9e8f8 user: drh tags: trunk
15:53
Proposed fix for ticket [a7b7803e8d1e8699cd8a]. check-in: 3d0e00c7 user: drh tags: tkt-a7b7803e
02:36
Better resolution of table and column names in joins where some of the terms of the FROM clause are parenthesized. check-in: 7344e791 user: drh tags: name-resolution-fix
2012-12-18
19:36
Optimize the degenerate case of a FROM clause table name enclosed all by itself inside parentheses. Generate code as if the parentheses did not exist, rather than the old behavior of manifesting the parenthesized table into a transient table. Also, tag every FROM-clause SELECT subquery that is generated by a parenthesized FROM-clause expression using the SF_NestedFrom flag. The new SF_NestedFrom flag is not yet used for anything. check-in: 7fecced4 user: drh tags: trunk
16:07
Resolve names in FROM-clause subqueries prior to resolving names in the result set expressions of a SELECT statement. check-in: 9b67c633 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

   431    431                    groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). {
   432    432     A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset);
   433    433   }
   434    434   
   435    435   // The "distinct" nonterminal is true (1) if the DISTINCT keyword is
   436    436   // present and false (0) if it is not.
   437    437   //
   438         -%type distinct {int}
   439         -distinct(A) ::= DISTINCT.   {A = 1;}
          438  +%type distinct {u16}
          439  +distinct(A) ::= DISTINCT.   {A = SF_Distinct;}
   440    440   distinct(A) ::= ALL.        {A = 0;}
   441    441   distinct(A) ::= .           {A = 0;}
   442    442   
   443    443   // selcollist is a list of expressions that are to become the return
   444    444   // values of the SELECT statement.  The "*" in statements like
   445    445   // "SELECT * FROM ..." is encoded as a special expression with an
   446    446   // opcode of TK_ALL.
................................................................................
   509    509                       as(Z) on_opt(N) using_opt(U). {
   510    510       A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U);
   511    511     }
   512    512     seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP
   513    513                       as(Z) on_opt(N) using_opt(U). {
   514    514       if( X==0 && Z.n==0 && N==0 && U==0 ){
   515    515         A = F;
          516  +    }else if( F->nSrc==1 ){
          517  +      A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,0,N,U);
          518  +      if( A ){
          519  +        struct SrcList_item *pNew = &A->a[A->nSrc-1];
          520  +        struct SrcList_item *pOld = F->a;
          521  +        pNew->zName = pOld->zName;
          522  +        pNew->zDatabase = pOld->zDatabase;
          523  +        pOld->zName = pOld->zDatabase = 0;
          524  +      }
          525  +      sqlite3SrcListDelete(pParse->db, F);
   516    526       }else{
   517    527         Select *pSubquery;
   518    528         sqlite3SrcListShiftJoinType(F);
   519         -      pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,0,0,0);
          529  +      pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,SF_NestedFrom,0,0);
   520    530         A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,pSubquery,N,U);
   521    531       }
   522    532     }
   523    533   %endif  SQLITE_OMIT_SUBQUERY
   524    534   
   525    535   %type dbnm {Token}
   526    536   dbnm(A) ::= .          {A.z=0; A.n=0;}

Changes to src/select.c.

    51     51     Parse *pParse,        /* Parsing context */
    52     52     ExprList *pEList,     /* which columns to include in the result */
    53     53     SrcList *pSrc,        /* the FROM clause -- which tables to scan */
    54     54     Expr *pWhere,         /* the WHERE clause */
    55     55     ExprList *pGroupBy,   /* the GROUP BY clause */
    56     56     Expr *pHaving,        /* the HAVING clause */
    57     57     ExprList *pOrderBy,   /* the ORDER BY clause */
    58         -  int isDistinct,       /* true if the DISTINCT keyword is present */
           58  +  u16 selFlags,         /* Flag parameters, such as SF_Distinct */
    59     59     Expr *pLimit,         /* LIMIT value.  NULL means not used */
    60     60     Expr *pOffset         /* OFFSET value.  NULL means no offset */
    61     61   ){
    62     62     Select *pNew;
    63     63     Select standin;
    64     64     sqlite3 *db = pParse->db;
    65     65     pNew = sqlite3DbMallocZero(db, sizeof(*pNew) );
................................................................................
    75     75     pNew->pEList = pEList;
    76     76     if( pSrc==0 ) pSrc = sqlite3DbMallocZero(db, sizeof(*pSrc));
    77     77     pNew->pSrc = pSrc;
    78     78     pNew->pWhere = pWhere;
    79     79     pNew->pGroupBy = pGroupBy;
    80     80     pNew->pHaving = pHaving;
    81     81     pNew->pOrderBy = pOrderBy;
    82         -  pNew->selFlags = isDistinct ? SF_Distinct : 0;
           82  +  pNew->selFlags = selFlags;
    83     83     pNew->op = TK_SELECT;
    84     84     pNew->pLimit = pLimit;
    85     85     pNew->pOffset = pOffset;
    86     86     assert( pOffset==0 || pLimit!=0 );
    87     87     pNew->addrOpenEphm[0] = -1;
    88     88     pNew->addrOpenEphm[1] = -1;
    89     89     pNew->addrOpenEphm[2] = -1;

Changes to src/sqliteInt.h.

  2098   2098   #define SF_Aggregate       0x0004  /* Contains aggregate functions */
  2099   2099   #define SF_UsesEphemeral   0x0008  /* Uses the OpenEphemeral opcode */
  2100   2100   #define SF_Expanded        0x0010  /* sqlite3SelectExpand() called on this */
  2101   2101   #define SF_HasTypeInfo     0x0020  /* FROM subqueries have Table metadata */
  2102   2102   #define SF_UseSorter       0x0040  /* Sort using a sorter */
  2103   2103   #define SF_Values          0x0080  /* Synthesized from VALUES clause */
  2104   2104   #define SF_Materialize     0x0100  /* Force materialization of views */
         2105  +#define SF_NestedFrom      0x0200  /* Part of a parenthesized FROM clause */
  2105   2106   
  2106   2107   
  2107   2108   /*
  2108   2109   ** The results of a select can be distributed in several ways.  The
  2109   2110   ** "SRT" prefix means "SELECT Result Type".
  2110   2111   */
  2111   2112   #define SRT_Union        1  /* Store result as keys in an index */
................................................................................
  2810   2811   void sqlite3IdListDelete(sqlite3*, IdList*);
  2811   2812   void sqlite3SrcListDelete(sqlite3*, SrcList*);
  2812   2813   Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
  2813   2814                           Token*, int, int);
  2814   2815   void sqlite3DropIndex(Parse*, SrcList*, int);
  2815   2816   int sqlite3Select(Parse*, Select*, SelectDest*);
  2816   2817   Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
  2817         -                         Expr*,ExprList*,int,Expr*,Expr*);
         2818  +                         Expr*,ExprList*,u16,Expr*,Expr*);
  2818   2819   void sqlite3SelectDelete(sqlite3*, Select*);
  2819   2820   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  2820   2821   int sqlite3IsReadOnly(Parse*, Table*, int);
  2821   2822   void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
  2822   2823   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  2823   2824   Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *);
  2824   2825   #endif

Changes to test/where8.test.

   285    285     execsql_status {
   286    286       SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (
   287    287         SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d
   288    288       )
   289    289       ORDER BY c
   290    290     }
   291    291   } {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1}
          292  +
          293  +
          294  +do_test where8-3.21 {
          295  +  execsql_status {
          296  +    SELECT a, d FROM t1, (t2) WHERE (a=d OR b=e) AND a<5 ORDER BY a
          297  +  }
          298  +} {1 1 2 2 3 3 4 2 4 4 0 0}
          299  +do_test where8-3.22 {
          300  +  execsql_status {
          301  +    SELECT a, d FROM ((((((t1))), (((t2))))))
          302  +     WHERE (a=d OR b=e) AND a<5 ORDER BY a
          303  +  }
          304  +} {1 1 2 2 3 3 4 2 4 4 0 0}
          305  +
   292    306   
   293    307   #-----------------------------------------------------------------------
   294    308   # The following tests - where8-4.* - verify that adding or removing 
   295    309   # indexes does not change the results returned by various queries.
   296    310   #
   297    311   do_test where8-4.1 {
   298    312     execsql {

Changes to test/where9.test.

   228    228           OR (b IS NULL AND c NOT NULL AND d NOT NULL)
   229    229           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
   230    230       ORDER BY a
   231    231     }
   232    232   } {90 91 92 97 scan 98 sort 0}
   233    233   do_test where9-1.3.4 {
   234    234     count_steps {
   235         -    SELECT a FROM t4
          235  +    SELECT a FROM (t4)
   236    236        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
   237    237           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   238    238           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
   239    239       ORDER BY a
   240    240     }
   241    241   } {90 91 92 97 scan 98 sort 0}
   242    242   
................................................................................
   870    870       INSERT INTO t82 VALUES(2,4);
   871    871       INSERT INTO t83 VALUES(5,55);
   872    872       
   873    873       SELECT *
   874    874         FROM t81 LEFT JOIN t82 ON y=b JOIN t83
   875    875        WHERE c==p OR d==p
   876    876        ORDER BY +a;
          877  +  }
          878  +} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
          879  +do_test where9-8.2 {
          880  +  db eval {
          881  +    SELECT *
          882  +      FROM t81 LEFT JOIN (t82) ON y=b JOIN t83
          883  +     WHERE c==p OR d==p
          884  +     ORDER BY +a;
          885  +  }
          886  +} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
          887  +do_test where9-8.3 {
          888  +  db eval {
          889  +    SELECT *
          890  +      FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83
          891  +     WHERE c==p OR d==p
          892  +     ORDER BY +a;
   877    893     }
   878    894   } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
   879    895   
   880    896   finish_test