/ Check-in [439cc5c5]
Login

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

Overview
Comment:In the query flattener, only add AS clauses to output columns of the outer query that are copied directly from the inner query. Formerly, all columns of the outer query received an AS clause if they did not have one already. This is a proposed fix for ticket [de3403bf5ae5f72].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | flattener-column-names
Files: files | file ages | folders
SHA3-256: 439cc5c52cbe6e67bbf0b6de0610f7d95ca9eb994f032547dc3535fd2c9dfc78
User & Date: drh 2017-07-29 03:33:21
Context
2017-07-29
14:56
Use the subquery column name, not the original SQL statement text, as the added AS clause in the query flattener. Closed-Leaf check-in: 5df7f0e6 user: drh tags: flattener-column-names
03:33
In the query flattener, only add AS clauses to output columns of the outer query that are copied directly from the inner query. Formerly, all columns of the outer query received an AS clause if they did not have one already. This is a proposed fix for ticket [de3403bf5ae5f72]. check-in: 439cc5c5 user: drh tags: flattener-column-names
2017-07-28
22:22
Update Tcl version used by the TclKit batch tool for MSVC. check-in: bcec155e user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  3765   3765         sqlite3IdListDelete(db, pSrc->a[i+iFrom].pUsing);
  3766   3766         assert( pSrc->a[i+iFrom].fg.isTabFunc==0 );
  3767   3767         pSrc->a[i+iFrom] = pSubSrc->a[i];
  3768   3768         iNewParent = pSubSrc->a[i].iCursor;
  3769   3769         memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
  3770   3770       }
  3771   3771       pSrc->a[iFrom].fg.jointype = jointype;
  3772         -  
         3772  +
         3773  +    /* For every result column in the outer query that does not have an AS
         3774  +    ** clause, if that column is a reference to an output column from the
         3775  +    ** inner query, then preserve the name of the column as it was written
         3776  +    ** in the original SQL text of the outer query by added an AS clause.
         3777  +    ** This prevents the outer query column from taking on a name derived
         3778  +    ** from inner query column name.
         3779  +    **
         3780  +    ** Example:
         3781  +    **     CREATE TABLE t1(a,b);
         3782  +    **     CREATE VIEW v1(x,y) AS SELECT a,b FROM t1;
         3783  +    **     SELECT x,y FROM v1;
         3784  +    **
         3785  +    ** The inner "v1" subquery will get flattened into the outer query.  After
         3786  +    ** flattening, the outer query becomes:  "SELECT a,b FROM t1".  But the
         3787  +    ** new query gives column names of "a" and "b", not the "x" and "y" that
         3788  +    ** the programmer expected.  This step adds AS clauses so that the
         3789  +    ** flattened query becomes:  "SELECT a AS x, b AS y FROM t1".
         3790  +    **
         3791  +    ** This is not a perfect solution.  The added AS clause is the same text as
         3792  +    ** the original input SQL.  So if the input SQL used goofy column names
         3793  +    ** like "SELECT v1.X,(y) FROM v1", then the added AS clauses will be those
         3794  +    ** same goofy colum names "v1.X" and "(y)", not just "x" and "y".  We could
         3795  +    ** improve that, but doing so might break lots of legacy code that depends
         3796  +    ** on the current behavior which dates back to around 2004.
         3797  +    **
         3798  +    ** Update on 2017-07-29:  The AS clause is only inserted into outer query
         3799  +    ** result columns that get substituted for inner query columns.  Formerly
         3800  +    ** an AS clause was added to *all* columns in the outer query that did not
         3801  +    ** already have one, even columns that had nothing to do with the inner
         3802  +    ** query.
         3803  +    */
         3804  +    pList = pParent->pEList;
         3805  +    for(i=0; i<pList->nExpr; i++){
         3806  +      Expr *p;
         3807  +      if( pList->a[i].zName==0
         3808  +       && (p = pList->a[i].pExpr)->op==TK_COLUMN
         3809  +       && p->iTable==iParent
         3810  +      ){
         3811  +        char *zName = sqlite3DbStrDup(db, pList->a[i].zSpan);
         3812  +        sqlite3Dequote(zName);
         3813  +        pList->a[i].zName = zName;
         3814  +      }
         3815  +    }
         3816  +
  3773   3817       /* Now begin substituting subquery result set expressions for 
  3774   3818       ** references to the iParent in the outer query.
  3775   3819       ** 
  3776   3820       ** Example:
  3777   3821       **
  3778   3822       **   SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
  3779   3823       **   \                     \_____________ subquery __________/          /
  3780   3824       **    \_____________________ outer query ______________________________/
  3781   3825       **
  3782   3826       ** We look at every expression in the outer query and every place we see
  3783   3827       ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
  3784   3828       */
  3785         -    pList = pParent->pEList;
  3786         -    for(i=0; i<pList->nExpr; i++){
  3787         -      if( pList->a[i].zName==0 ){
  3788         -        char *zName = sqlite3DbStrDup(db, pList->a[i].zSpan);
  3789         -        sqlite3Dequote(zName);
  3790         -        pList->a[i].zName = zName;
  3791         -      }
  3792         -    }
  3793   3829       if( pSub->pOrderBy ){
  3794   3830         /* At this point, any non-zero iOrderByCol values indicate that the
  3795   3831         ** ORDER BY column expression is identical to the iOrderByCol'th
  3796   3832         ** expression returned by SELECT statement pSub. Since these values
  3797   3833         ** do not necessarily correspond to columns in SELECT statement pParent,
  3798   3834         ** zero them before transfering the ORDER BY clause.
  3799   3835         **