SQLite

Check-in [5df7f0e6a1]
Login

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

Overview
Comment:Use the subquery column name, not the original SQL statement text, as the added AS clause in the query flattener.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | flattener-column-names
Files: files | file ages | folders
SHA3-256: 5df7f0e6a1fbc770a68830ce88e78ecccbf023557ea446ce312ab53d5b32a6a9
User & Date: drh 2017-07-29 14:56:53.996
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: 5df7f0e6a1 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: 439cc5c52c user: drh tags: flattener-column-names)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802



3803
3804
3805
3806
3807
3808
3809


3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
      iNewParent = pSubSrc->a[i].iCursor;
      memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
    }
    pSrc->a[iFrom].fg.jointype = jointype;

    /* For every result column in the outer query that does not have an AS
    ** clause, if that column is a reference to an output column from the
    ** inner query, then preserve the name of the column as it was written
    ** in the original SQL text of the outer query by added an AS clause.
    ** This prevents the outer query column from taking on a name derived
    ** from inner query column name.
    **
    ** Example:
    **     CREATE TABLE t1(a,b);
    **     CREATE VIEW v1(x,y) AS SELECT a,b FROM t1;
    **     SELECT x,y FROM v1;
    **
    ** The inner "v1" subquery will get flattened into the outer query.  After
    ** flattening, the outer query becomes:  "SELECT a,b FROM t1".  But the
    ** new query gives column names of "a" and "b", not the "x" and "y" that
    ** the programmer expected.  This step adds AS clauses so that the
    ** flattened query becomes:  "SELECT a AS x, b AS y FROM t1".
    **
    ** This is not a perfect solution.  The added AS clause is the same text as
    ** the original input SQL.  So if the input SQL used goofy column names
    ** like "SELECT v1.X,(y) FROM v1", then the added AS clauses will be those
    ** same goofy colum names "v1.X" and "(y)", not just "x" and "y".  We could
    ** improve that, but doing so might break lots of legacy code that depends
    ** on the current behavior which dates back to around 2004.
    **
    ** Update on 2017-07-29:  The AS clause is only inserted into outer query
    ** result columns that get substituted for inner query columns.  Formerly
    ** an AS clause was added to *all* columns in the outer query that did not
    ** already have one, even columns that had nothing to do with the inner
    ** query.



    */
    pList = pParent->pEList;
    for(i=0; i<pList->nExpr; i++){
      Expr *p;
      if( pList->a[i].zName==0
       && (p = pList->a[i].pExpr)->op==TK_COLUMN
       && p->iTable==iParent


      ){
        char *zName = sqlite3DbStrDup(db, pList->a[i].zSpan);
        sqlite3Dequote(zName);
        pList->a[i].zName = zName;
      }
    }

    /* Now begin substituting subquery result set expressions for 
    ** references to the iParent in the outer query.
    ** 







|
<














<
<
<
<
<
<
<
|
|
|
<
|
>
>
>







>
>

|
<







3768
3769
3770
3771
3772
3773
3774
3775

3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789







3790
3791
3792

3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807

3808
3809
3810
3811
3812
3813
3814
      iNewParent = pSubSrc->a[i].iCursor;
      memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
    }
    pSrc->a[iFrom].fg.jointype = jointype;

    /* For every result column in the outer query that does not have an AS
    ** clause, if that column is a reference to an output column from the
    ** inner query, then preserve the name of the column by adding an AS clause.

    ** This prevents the outer query column from taking on a name derived
    ** from inner query column name.
    **
    ** Example:
    **     CREATE TABLE t1(a,b);
    **     CREATE VIEW v1(x,y) AS SELECT a,b FROM t1;
    **     SELECT x,y FROM v1;
    **
    ** The inner "v1" subquery will get flattened into the outer query.  After
    ** flattening, the outer query becomes:  "SELECT a,b FROM t1".  But the
    ** new query gives column names of "a" and "b", not the "x" and "y" that
    ** the programmer expected.  This step adds AS clauses so that the
    ** flattened query becomes:  "SELECT a AS x, b AS y FROM t1".
    **







    ** Update on 2017-07-29:  The current implementation only adds AS clauses
    ** to outer query result columns that are substituted directly for
    ** columns of the inner query.  Formerly, all result columns in the outer

    ** query got new AS clauses if they didn't have them all ready.  Also,
    ** the name of the AS clause is taken from the result column name of
    ** the inner query.  Formerly, the name was a copy of the text of the
    ** original SQL statement that specified the column.
    */
    pList = pParent->pEList;
    for(i=0; i<pList->nExpr; i++){
      Expr *p;
      if( pList->a[i].zName==0
       && (p = pList->a[i].pExpr)->op==TK_COLUMN
       && p->iTable==iParent
       && p->iColumn>=0
       && ALWAYS(p->pTab!=0)
      ){
        char *zName = sqlite3DbStrDup(db, p->pTab->aCol[p->iColumn].zName);

        pList->a[i].zName = zName;
      }
    }

    /* Now begin substituting subquery result set expressions for 
    ** references to the iParent in the outer query.
    **