SQLite: Check-in [7fecced4]

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 Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

                 groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). {
  A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset);
}

// The "distinct" nonterminal is true (1) if the DISTINCT keyword is
// present and false (0) if it is not.
//
%type distinct {int}
distinct(A) ::= DISTINCT.   {A = 1;}
distinct(A) ::= ALL.        {A = 0;}
distinct(A) ::= .           {A = 0;}

// selcollist is a list of expressions that are to become the return
// values of the SELECT statement.  The "*" in statements like
// "SELECT * FROM ..." is encoded as a special expression with an
// opcode of TK_ALL.
................................................................................
                    as(Z) on_opt(N) using_opt(U). {
    A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U);
  }
  seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP
                    as(Z) on_opt(N) using_opt(U). {
    if( X==0 && Z.n==0 && N==0 && U==0 ){
      A = F;










    }else{
      Select *pSubquery;
      sqlite3SrcListShiftJoinType(F);
      pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,0,0,0);
      A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,pSubquery,N,U);
    }
  }
%endif  SQLITE_OMIT_SUBQUERY

%type dbnm {Token}
dbnm(A) ::= .          {A.z=0; A.n=0;}








|
|







 







>
>
>
>
>
>
>
>
>
>



|

                 groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). {
  A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset);
}

// The "distinct" nonterminal is true (1) if the DISTINCT keyword is
// present and false (0) if it is not.
//
%type distinct {u16}
distinct(A) ::= DISTINCT.   {A = SF_Distinct;}
distinct(A) ::= ALL.        {A = 0;}
distinct(A) ::= .           {A = 0;}

// selcollist is a list of expressions that are to become the return
// values of the SELECT statement.  The "*" in statements like
// "SELECT * FROM ..." is encoded as a special expression with an
// opcode of TK_ALL.
................................................................................
                    as(Z) on_opt(N) using_opt(U). {
    A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,S,N,U);
  }
  seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP
                    as(Z) on_opt(N) using_opt(U). {
    if( X==0 && Z.n==0 && N==0 && U==0 ){
      A = F;
    }else if( F->nSrc==1 ){
      A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,0,N,U);
      if( A ){
        struct SrcList_item *pNew = &A->a[A->nSrc-1];
        struct SrcList_item *pOld = F->a;
        pNew->zName = pOld->zName;
        pNew->zDatabase = pOld->zDatabase;
        pOld->zName = pOld->zDatabase = 0;
      }
      sqlite3SrcListDelete(pParse->db, F);
    }else{
      Select *pSubquery;
      sqlite3SrcListShiftJoinType(F);
      pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,SF_NestedFrom,0,0);
      A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,pSubquery,N,U);
    }
  }
%endif  SQLITE_OMIT_SUBQUERY

%type dbnm {Token}
dbnm(A) ::= .          {A.z=0; A.n=0;}

Changes to src/select.c.

  Parse *pParse,        /* Parsing context */
  ExprList *pEList,     /* which columns to include in the result */
  SrcList *pSrc,        /* the FROM clause -- which tables to scan */
  Expr *pWhere,         /* the WHERE clause */
  ExprList *pGroupBy,   /* the GROUP BY clause */
  Expr *pHaving,        /* the HAVING clause */
  ExprList *pOrderBy,   /* the ORDER BY clause */
  int isDistinct,       /* true if the DISTINCT keyword is present */
  Expr *pLimit,         /* LIMIT value.  NULL means not used */
  Expr *pOffset         /* OFFSET value.  NULL means no offset */
){
  Select *pNew;
  Select standin;
  sqlite3 *db = pParse->db;
  pNew = sqlite3DbMallocZero(db, sizeof(*pNew) );
................................................................................
  pNew->pEList = pEList;
  if( pSrc==0 ) pSrc = sqlite3DbMallocZero(db, sizeof(*pSrc));
  pNew->pSrc = pSrc;
  pNew->pWhere = pWhere;
  pNew->pGroupBy = pGroupBy;
  pNew->pHaving = pHaving;
  pNew->pOrderBy = pOrderBy;
  pNew->selFlags = isDistinct ? SF_Distinct : 0;
  pNew->op = TK_SELECT;
  pNew->pLimit = pLimit;
  pNew->pOffset = pOffset;
  assert( pOffset==0 || pLimit!=0 );
  pNew->addrOpenEphm[0] = -1;
  pNew->addrOpenEphm[1] = -1;
  pNew->addrOpenEphm[2] = -1;

  Parse *pParse,        /* Parsing context */
  ExprList *pEList,     /* which columns to include in the result */
  SrcList *pSrc,        /* the FROM clause -- which tables to scan */
  Expr *pWhere,         /* the WHERE clause */
  ExprList *pGroupBy,   /* the GROUP BY clause */
  Expr *pHaving,        /* the HAVING clause */
  ExprList *pOrderBy,   /* the ORDER BY clause */
  u16 selFlags,         /* Flag parameters, such as SF_Distinct */
  Expr *pLimit,         /* LIMIT value.  NULL means not used */
  Expr *pOffset         /* OFFSET value.  NULL means no offset */
){
  Select *pNew;
  Select standin;
  sqlite3 *db = pParse->db;
  pNew = sqlite3DbMallocZero(db, sizeof(*pNew) );
................................................................................
  pNew->pEList = pEList;
  if( pSrc==0 ) pSrc = sqlite3DbMallocZero(db, sizeof(*pSrc));
  pNew->pSrc = pSrc;
  pNew->pWhere = pWhere;
  pNew->pGroupBy = pGroupBy;
  pNew->pHaving = pHaving;
  pNew->pOrderBy = pOrderBy;
  pNew->selFlags = selFlags;
  pNew->op = TK_SELECT;
  pNew->pLimit = pLimit;
  pNew->pOffset = pOffset;
  assert( pOffset==0 || pLimit!=0 );
  pNew->addrOpenEphm[0] = -1;
  pNew->addrOpenEphm[1] = -1;
  pNew->addrOpenEphm[2] = -1;

Changes to src/sqliteInt.h.

#define SF_Aggregate       0x0004  /* Contains aggregate functions */
#define SF_UsesEphemeral   0x0008  /* Uses the OpenEphemeral opcode */
#define SF_Expanded        0x0010  /* sqlite3SelectExpand() called on this */
#define SF_HasTypeInfo     0x0020  /* FROM subqueries have Table metadata */
#define SF_UseSorter       0x0040  /* Sort using a sorter */
#define SF_Values          0x0080  /* Synthesized from VALUES clause */
#define SF_Materialize     0x0100  /* Force materialization of views */



/*
** The results of a select can be distributed in several ways.  The
** "SRT" prefix means "SELECT Result Type".
*/
#define SRT_Union        1  /* Store result as keys in an index */
................................................................................
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);
int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,int,Expr*,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
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

#define SF_Aggregate       0x0004  /* Contains aggregate functions */
#define SF_UsesEphemeral   0x0008  /* Uses the OpenEphemeral opcode */
#define SF_Expanded        0x0010  /* sqlite3SelectExpand() called on this */
#define SF_HasTypeInfo     0x0020  /* FROM subqueries have Table metadata */
#define SF_UseSorter       0x0040  /* Sort using a sorter */
#define SF_Values          0x0080  /* Synthesized from VALUES clause */
#define SF_Materialize     0x0100  /* Force materialization of views */
#define SF_NestedFrom      0x0200  /* Part of a parenthesized FROM clause */


/*
** The results of a select can be distributed in several ways.  The
** "SRT" prefix means "SELECT Result Type".
*/
#define SRT_Union        1  /* Store result as keys in an index */
................................................................................
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                        Token*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);
int sqlite3Select(Parse*, Select*, SelectDest*);
Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*,
                         Expr*,ExprList*,u16,Expr*,Expr*);
void sqlite3SelectDelete(sqlite3*, Select*);
Table *sqlite3SrcListLookup(Parse*, SrcList*);
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

Changes to test/where8.test.

  execsql_status {
    SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (
      SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d
    )
    ORDER BY c
  }
} {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}















#-----------------------------------------------------------------------
# The following tests - where8-4.* - verify that adding or removing 
# indexes does not change the results returned by various queries.
#
do_test where8-4.1 {
  execsql {








>
>
>
>
>
>
>
>
>
>
>
>
>
>

  execsql_status {
    SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (
      SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d
    )
    ORDER BY c
  }
} {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}


do_test where8-3.21 {
  execsql_status {
    SELECT a, d FROM t1, (t2) WHERE (a=d OR b=e) AND a<5 ORDER BY a
  }
} {1 1 2 2 3 3 4 2 4 4 0 0}
do_test where8-3.22 {
  execsql_status {
    SELECT a, d FROM ((((((t1))), (((t2))))))
     WHERE (a=d OR b=e) AND a<5 ORDER BY a
  }
} {1 1 2 2 3 3 4 2 4 4 0 0}


#-----------------------------------------------------------------------
# The following tests - where8-4.* - verify that adding or removing 
# indexes does not change the results returned by various queries.
#
do_test where8-4.1 {
  execsql {

Changes to test/where9.test.

        OR (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}
do_test where9-1.3.4 {
  count_steps {
    SELECT a FROM t4
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}

................................................................................
    INSERT INTO t82 VALUES(2,4);
    INSERT INTO t83 VALUES(5,55);
    
    SELECT *
      FROM t81 LEFT JOIN t82 ON y=b JOIN t83
     WHERE c==p OR d==p
     ORDER BY +a;
















  }
} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}

finish_test








|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

        OR (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}
do_test where9-1.3.4 {
  count_steps {
    SELECT a FROM (t4)
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}

................................................................................
    INSERT INTO t82 VALUES(2,4);
    INSERT INTO t83 VALUES(5,55);
    
    SELECT *
      FROM t81 LEFT JOIN t82 ON y=b JOIN t83
     WHERE c==p OR d==p
     ORDER BY +a;
  }
} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
do_test where9-8.2 {
  db eval {
    SELECT *
      FROM t81 LEFT JOIN (t82) ON y=b JOIN t83
     WHERE c==p OR d==p
     ORDER BY +a;
  }
} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
do_test where9-8.3 {
  db eval {
    SELECT *
      FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83
     WHERE c==p OR d==p
     ORDER BY +a;
  }
} {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}

finish_test