SQLite

Check-in [ab0292caa5]
Login

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

Overview
Comment:Avoid reevaluating WHERE and ORDER BY expressions that alias terms in the result set. Ticket #3343. Note that aliased GROUP BY expressions are still evaluated twice. (CVS 5637)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ab0292caa5887cc1bdc0e8c9d3f3502b83975440
User & Date: drh 2008-08-29 02:14:03.000
Context
2008-08-29
09:10
If a page is made eligible for recycling when more than the configured maximum number of pages are allocated, free it immediately instead of adding it to the LRU list. (CVS 5638) (check-in: 4b12922dcb user: danielk1977 tags: trunk)
02:14
Avoid reevaluating WHERE and ORDER BY expressions that alias terms in the result set. Ticket #3343. Note that aliased GROUP BY expressions are still evaluated twice. (CVS 5637) (check-in: ab0292caa5 user: drh tags: trunk)
2008-08-28
18:35
Fix ioerr5.test so that it works with the new pcache module. (CVS 5636) (check-in: 83e6a75e7d user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/delete.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.173 2008/08/22 12:30:52 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.174 2008/08/29 02:14:03 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

  pDup = sqlite3SelectDup(db, pView->pSelect);
  if( pWhere ){
    SrcList *pFrom;
    Token viewName;
    
    pWhere = sqlite3ExprDup(db, pWhere);
    viewName.z = pView->zName;
    viewName.n = strlen(viewName.z);
    pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &viewName, pDup, 0,0);
    pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
  }
  sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
  sqlite3Select(pParse, pDup, &dest);
  sqlite3SelectDelete(db, pDup);
}







|
|







100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

  pDup = sqlite3SelectDup(db, pView->pSelect);
  if( pWhere ){
    SrcList *pFrom;
    Token viewName;
    
    pWhere = sqlite3ExprDup(db, pWhere);
    viewName.z = (u8*)pView->zName;
    viewName.n = (unsigned int)strlen((const char*)viewName.z);
    pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, &viewName, pDup, 0,0);
    pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
  }
  sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
  sqlite3Select(pParse, pDup, &dest);
  sqlite3SelectDelete(db, pDup);
}
Changes to src/expr.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.391 2008/08/22 17:34:45 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.392 2008/08/29 02:14:03 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
697
698
699
700
701
702
703

704
705
706
707
708
709
710
    assert( pNewExpr==0 || pNewExpr->span.z!=0 
            || pOldExpr->span.z==0
            || db->mallocFailed );
    pItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
    pItem->sortOrder = pOldItem->sortOrder;
    pItem->done = 0;
    pItem->iCol = pOldItem->iCol;

  }
  return pNew;
}

/*
** If cursors, triggers, views and subqueries are all omitted from
** the build, then none of the following routines, except for 







>







697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
    assert( pNewExpr==0 || pNewExpr->span.z!=0 
            || pOldExpr->span.z==0
            || db->mallocFailed );
    pItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
    pItem->sortOrder = pOldItem->sortOrder;
    pItem->done = 0;
    pItem->iCol = pOldItem->iCol;
    pItem->iAlias = pOldItem->iAlias;
  }
  return pNew;
}

/*
** If cursors, triggers, views and subqueries are all omitted from
** the build, then none of the following routines, except for 
825
826
827
828
829
830
831

832
833
834
835
836
837
838
  }
  assert( pList->a!=0 );
  if( pExpr || pName ){
    struct ExprList_item *pItem = &pList->a[pList->nExpr++];
    memset(pItem, 0, sizeof(*pItem));
    pItem->zName = sqlite3NameFromToken(db, pName);
    pItem->pExpr = pExpr;

  }
  return pList;

no_mem:     
  /* Avoid leaking memory if malloc has failed. */
  sqlite3ExprDelete(db, pExpr);
  sqlite3ExprListDelete(db, pList);







>







826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
  }
  assert( pList->a!=0 );
  if( pExpr || pName ){
    struct ExprList_item *pItem = &pList->a[pList->nExpr++];
    memset(pItem, 0, sizeof(*pItem));
    pItem->zName = sqlite3NameFromToken(db, pName);
    pItem->pExpr = pExpr;
    pItem->iAlias = 0;
  }
  return pList;

no_mem:     
  /* Avoid leaking memory if malloc has failed. */
  sqlite3ExprDelete(db, pExpr);
  sqlite3ExprListDelete(db, pList);
1659
1660
1661
1662
1663
1664
1665



































1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684

1685

1686
1687
1688
1689
1690
1691
1692
1693
  addr = sqlite3VdbeCurrentAddr(v);
  pOp = sqlite3VdbeGetOp(v, addr-1);
  assert( pOp || pParse->db->mallocFailed );
  if( pOp && pOp->opcode==OP_SCopy && pOp->p1>=iReg && pOp->p1<iReg+nReg ){
    pOp->opcode = OP_Copy;
  }
}




































/*
** Generate code into the current Vdbe to evaluate the given
** expression.  Attempt to store the results in register "target".
** Return the register where results are stored.
**
** With this routine, there is no guaranteed that results will
** be stored in target.  The result might be stored in some other
** register if it is convenient to do so.  The calling function
** must check the return code and move the results to the desired
** register.
*/
int sqlite3ExprCodeTarget(Parse *pParse, Expr *pExpr, int target){
  Vdbe *v = pParse->pVdbe;  /* The VM under construction */
  int op;                   /* The opcode being coded */
  int inReg = target;       /* Results stored in register inReg */
  int regFree1 = 0;         /* If non-zero free this temporary register */
  int regFree2 = 0;         /* If non-zero free this temporary register */
  int r1, r2, r3, r4;       /* Various register numbers */



  assert( v!=0 || pParse->db->mallocFailed );
  assert( target>0 && target<=pParse->nMem );
  if( v==0 ) return 0;

  if( pExpr==0 ){
    op = TK_NULL;
  }else{
    op = pExpr->op;







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






|












>

>
|







1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
  addr = sqlite3VdbeCurrentAddr(v);
  pOp = sqlite3VdbeGetOp(v, addr-1);
  assert( pOp || pParse->db->mallocFailed );
  if( pOp && pOp->opcode==OP_SCopy && pOp->p1>=iReg && pOp->p1<iReg+nReg ){
    pOp->opcode = OP_Copy;
  }
}

/*
** Generate code to store the value of the iAlias-th alias in register
** target.  The first time this is called, pExpr is evaluated to compute
** the value of the alias.  The value is stored in an auxiliary register
** and the number of that register is returned.  On subsequent calls,
** the register number is returned without generating any code.
**
** Note that in order for this to work, code must be generated in the
** same order that it is executed.
**
** Aliases are numbered starting with 1.  So iAlias is in the range
** of 1 to pParse->nAlias inclusive.  
**
** pParse->aAlias[iAlias-1] records the register number where the value
** of the iAlias-th alias is stored.  If zero, that means that the
** alias has not yet been computed.
*/
static int codeAlias(Parse *pParse, int iAlias, Expr *pExpr){
  sqlite3 *db = pParse->db;
  int iReg;
  if( pParse->aAlias==0 ){
    pParse->aAlias = sqlite3DbMallocZero(db, 
                                 sizeof(pParse->aAlias[0])*pParse->nAlias );
    if( db->mallocFailed ) return 0;
  }
  assert( iAlias>0 && iAlias<=pParse->nAlias );
  iReg = pParse->aAlias[iAlias-1];
  if( iReg==0 ){
    iReg = ++pParse->nMem;
    sqlite3ExprCode(pParse, pExpr, iReg);
    pParse->aAlias[iAlias-1] = iReg;
  }
  return iReg;
}

/*
** Generate code into the current Vdbe to evaluate the given
** expression.  Attempt to store the results in register "target".
** Return the register where results are stored.
**
** With this routine, there is no guarantee that results will
** be stored in target.  The result might be stored in some other
** register if it is convenient to do so.  The calling function
** must check the return code and move the results to the desired
** register.
*/
int sqlite3ExprCodeTarget(Parse *pParse, Expr *pExpr, int target){
  Vdbe *v = pParse->pVdbe;  /* The VM under construction */
  int op;                   /* The opcode being coded */
  int inReg = target;       /* Results stored in register inReg */
  int regFree1 = 0;         /* If non-zero free this temporary register */
  int regFree2 = 0;         /* If non-zero free this temporary register */
  int r1, r2, r3, r4;       /* Various register numbers */
  sqlite3 *db;

  db = pParse->db;
  assert( v!=0 || db->mallocFailed );
  assert( target>0 && target<=pParse->nMem );
  if( v==0 ) return 0;

  if( pExpr==0 ){
    op = TK_NULL;
  }else{
    op = pExpr->op;
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
      break;
    }
    case TK_FLOAT: {
      codeReal(v, (char*)pExpr->token.z, pExpr->token.n, 0, target);
      break;
    }
    case TK_STRING: {
      sqlite3DequoteExpr(pParse->db, pExpr);
      sqlite3VdbeAddOp4(v,OP_String8, 0, target, 0,
                        (char*)pExpr->token.z, pExpr->token.n);
      break;
    }
    case TK_NULL: {
      sqlite3VdbeAddOp2(v, OP_Null, 0, target);
      break;







|







1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
      break;
    }
    case TK_FLOAT: {
      codeReal(v, (char*)pExpr->token.z, pExpr->token.n, 0, target);
      break;
    }
    case TK_STRING: {
      sqlite3DequoteExpr(db, pExpr);
      sqlite3VdbeAddOp4(v,OP_String8, 0, target, 0,
                        (char*)pExpr->token.z, pExpr->token.n);
      break;
    }
    case TK_NULL: {
      sqlite3VdbeAddOp2(v, OP_Null, 0, target);
      break;
1760
1761
1762
1763
1764
1765
1766




1767
1768
1769
1770
1771
1772
1773
        sqlite3VdbeChangeP4(v, -1, (char*)pExpr->token.z, pExpr->token.n);
      }
      break;
    }
    case TK_REGISTER: {
      inReg = pExpr->iTable;
      break;




    }
#ifndef SQLITE_OMIT_CAST
    case TK_CAST: {
      /* Expressions of the form:   CAST(pLeft AS token) */
      int aff, to_op;
      inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
      aff = sqlite3AffinityType(&pExpr->token);







>
>
>
>







1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
        sqlite3VdbeChangeP4(v, -1, (char*)pExpr->token.z, pExpr->token.n);
      }
      break;
    }
    case TK_REGISTER: {
      inReg = pExpr->iTable;
      break;
    }
    case TK_AS: {
      inReg = codeAlias(pParse, pExpr->iTable, pExpr->pLeft);
      break;
    }
#ifndef SQLITE_OMIT_CAST
    case TK_CAST: {
      /* Expressions of the form:   CAST(pLeft AS token) */
      int aff, to_op;
      inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
      aff = sqlite3AffinityType(&pExpr->token);
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
      ExprList *pList = pExpr->pList;
      int nExpr = pList ? pList->nExpr : 0;
      FuncDef *pDef;
      int nId;
      const char *zId;
      int constMask = 0;
      int i;
      sqlite3 *db = pParse->db;
      u8 enc = ENC(db);
      CollSeq *pColl = 0;

      testcase( op==TK_CONST_FUNC );
      testcase( op==TK_FUNCTION );
      zId = (char*)pExpr->token.z;
      nId = pExpr->token.n;
      pDef = sqlite3FindFunction(pParse->db, zId, nId, nExpr, enc, 0);
      assert( pDef!=0 );
      if( pList ){
        nExpr = pList->nExpr;
        r1 = sqlite3GetTempRange(pParse, nExpr);
        sqlite3ExprCodeExprList(pParse, pList, r1, 1);
      }else{
        nExpr = r1 = 0;







<







|







1960
1961
1962
1963
1964
1965
1966

1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
      ExprList *pList = pExpr->pList;
      int nExpr = pList ? pList->nExpr : 0;
      FuncDef *pDef;
      int nId;
      const char *zId;
      int constMask = 0;
      int i;

      u8 enc = ENC(db);
      CollSeq *pColl = 0;

      testcase( op==TK_CONST_FUNC );
      testcase( op==TK_FUNCTION );
      zId = (char*)pExpr->token.z;
      nId = pExpr->token.n;
      pDef = sqlite3FindFunction(db, zId, nId, nExpr, enc, 0);
      assert( pDef!=0 );
      if( pList ){
        nExpr = pList->nExpr;
        r1 = sqlite3GetTempRange(pParse, nExpr);
        sqlite3ExprCodeExprList(pParse, pList, r1, 1);
      }else{
        nExpr = r1 = 0;
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
          constMask |= (1<<i);
        }
        if( pDef->needCollSeq && !pColl ){
          pColl = sqlite3ExprCollSeq(pParse, pList->a[i].pExpr);
        }
      }
      if( pDef->needCollSeq ){
        if( !pColl ) pColl = pParse->db->pDfltColl; 
        sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
      }
      sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target,
                        (char*)pDef, P4_FUNCDEF);
      sqlite3VdbeChangeP5(v, nExpr);
      if( nExpr ){
        sqlite3ReleaseTempRange(pParse, r1, nExpr);







|







2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
          constMask |= (1<<i);
        }
        if( pDef->needCollSeq && !pColl ){
          pColl = sqlite3ExprCollSeq(pParse, pList->a[i].pExpr);
        }
      }
      if( pDef->needCollSeq ){
        if( !pColl ) pColl = db->pDfltColl; 
        sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ);
      }
      sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target,
                        (char*)pDef, P4_FUNCDEF);
      sqlite3VdbeChangeP5(v, nExpr);
      if( nExpr ){
        sqlite3ReleaseTempRange(pParse, r1, nExpr);
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
                       "RAISE() may only be used within a trigger-program");
        return 0;
      }
      if( pExpr->iColumn!=OE_Ignore ){
         assert( pExpr->iColumn==OE_Rollback ||
                 pExpr->iColumn == OE_Abort ||
                 pExpr->iColumn == OE_Fail );
         sqlite3DequoteExpr(pParse->db, pExpr);
         sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, pExpr->iColumn, 0,
                        (char*)pExpr->token.z, pExpr->token.n);
      } else {
         assert( pExpr->iColumn == OE_Ignore );
         sqlite3VdbeAddOp2(v, OP_ContextPop, 0, 0);
         sqlite3VdbeAddOp2(v, OP_Goto, 0, pParse->trigStack->ignoreJump);
         VdbeComment((v, "raise(IGNORE)"));







|







2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
                       "RAISE() may only be used within a trigger-program");
        return 0;
      }
      if( pExpr->iColumn!=OE_Ignore ){
         assert( pExpr->iColumn==OE_Rollback ||
                 pExpr->iColumn == OE_Abort ||
                 pExpr->iColumn == OE_Fail );
         sqlite3DequoteExpr(db, pExpr);
         sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_CONSTRAINT, pExpr->iColumn, 0,
                        (char*)pExpr->token.z, pExpr->token.n);
      } else {
         assert( pExpr->iColumn == OE_Ignore );
         sqlite3VdbeAddOp2(v, OP_ContextPop, 0, 0);
         sqlite3VdbeAddOp2(v, OP_Goto, 0, pParse->trigStack->ignoreJump);
         VdbeComment((v, "raise(IGNORE)"));
2418
2419
2420
2421
2422
2423
2424





2425

2426
2427
2428
2429
2430
2431
2432
){
  struct ExprList_item *pItem;
  int i, n;
  assert( pList!=0 );
  assert( target>0 );
  n = pList->nExpr;
  for(pItem=pList->a, i=0; i<n; i++, pItem++){





    sqlite3ExprCode(pParse, pItem->pExpr, target+i);

    if( doHardCopy ) sqlite3ExprHardCopy(pParse, target, n);
  }
  return n;
}

/*
** Generate code for a boolean expression such that a jump is made







>
>
>
>
>
|
>







2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
){
  struct ExprList_item *pItem;
  int i, n;
  assert( pList!=0 );
  assert( target>0 );
  n = pList->nExpr;
  for(pItem=pList->a, i=0; i<n; i++, pItem++){
    if( pItem->iAlias ){
      int iReg = codeAlias(pParse, pItem->iAlias, pItem->pExpr);
      Vdbe *v = sqlite3GetVdbe(pParse);
      sqlite3VdbeAddOp2(v, OP_SCopy, iReg, target+i);
    }else{
      sqlite3ExprCode(pParse, pItem->pExpr, target+i);
    }
    if( doHardCopy ) sqlite3ExprHardCopy(pParse, target, n);
  }
  return n;
}

/*
** Generate code for a boolean expression such that a jump is made
2807
2808
2809
2810
2811
2812
2813


2814
2815
2816
2817
2818
2819
2820
  Parse *pParse = pNC->pParse;
  SrcList *pSrcList = pNC->pSrcList;
  AggInfo *pAggInfo = pNC->pAggInfo;

  switch( pExpr->op ){
    case TK_AGG_COLUMN:
    case TK_COLUMN: {


      /* Check to see if the column is in one of the tables in the FROM
      ** clause of the aggregate query */
      if( pSrcList ){
        struct SrcList_item *pItem = pSrcList->a;
        for(i=0; i<pSrcList->nSrc; i++, pItem++){
          struct AggInfo_col *pCol;
          if( pExpr->iTable==pItem->iCursor ){







>
>







2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
  Parse *pParse = pNC->pParse;
  SrcList *pSrcList = pNC->pSrcList;
  AggInfo *pAggInfo = pNC->pAggInfo;

  switch( pExpr->op ){
    case TK_AGG_COLUMN:
    case TK_COLUMN: {
      testcase( pExpr->op==TK_AGG_COLUMN );
      testcase( pExpr->op==TK_COLUMN );
      /* Check to see if the column is in one of the tables in the FROM
      ** clause of the aggregate query */
      if( pSrcList ){
        struct SrcList_item *pItem = pSrcList->a;
        for(i=0; i<pSrcList->nSrc; i++, pItem++){
          struct AggInfo_col *pCol;
          if( pExpr->iTable==pItem->iCursor ){
Changes to src/resolve.c.
10
11
12
13
14
15
16
17
18
19
20
21































































22
23
24
25
26
27
28
**
*************************************************************************
**
** This file contains routines used for walking the parser tree and
** resolve all identifiers by associating them with a particular
** table and column.
**
** $Id: resolve.c,v 1.4 2008/08/25 17:23:29 drh Exp $
*/
#include "sqliteInt.h"
#include <stdlib.h>
#include <string.h>
































































/*
** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
** that name in the set of source tables in pSrcList and make the pExpr 
** expression node refer back to that source column.  The following changes
** are made to pExpr:
**







|




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







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
**
*************************************************************************
**
** This file contains routines used for walking the parser tree and
** resolve all identifiers by associating them with a particular
** table and column.
**
** $Id: resolve.c,v 1.5 2008/08/29 02:14:03 drh Exp $
*/
#include "sqliteInt.h"
#include <stdlib.h>
#include <string.h>

/*
** Turn the pExpr expression into an alias for the iCol-th column of the
** result set in pEList.
**
** If the result set column is a simple column reference, then this routine
** makes an exact copy.  But for any other kind of expression, this
** routine make a copy of the result set column as the argument to the
** TK_AS operator.  The TK_AS operator causes the expression to be
** evaluated just once and then reused for each alias.
**
** The reason for suppressing the TK_AS term when the expression is a simple
** column reference is so that the column reference will be recognized as
** usable by indices within the WHERE clause processing logic. 
**
** Hack:  The TK_AS operator is inhibited if zType[0]=='G'.  This means
** that in a GROUP BY clause, the expression is evaluated twice.  Hence:
**
**     SELECT random()%5 AS x, count(*) FROM tab GROUP BY x
**
** Is equivalent to:
**
**     SELECT random()%5 AS x, count(*) FROM tab GROUP BY random()%5
**
** The result of random()%5 in the GROUP BY clause is probably different
** from the result in the result-set.  We might fix this someday.  Or
** then again, we might not...
*/
static void resolveAlias(
  Parse *pParse,         /* Parsing context */
  ExprList *pEList,      /* A result set */
  int iCol,              /* A column in the result set.  0..pEList->nExpr-1 */
  Expr *pExpr,           /* Transform this into an alias to the result set */
  const char *zType      /* "GROUP" or "ORDER" or "" */
){
  Expr *pOrig;           /* The iCol-th column of the result set */
  Expr *pDup;            /* Copy of pOrig */
  sqlite3 *db;           /* The database connection */

  assert( iCol>=0 && iCol<pEList->nExpr );
  pOrig = pEList->a[iCol].pExpr;
  assert( pOrig!=0 );
  assert( pOrig->flags & EP_Resolved );
  db = pParse->db;
  pDup = sqlite3ExprDup(db, pOrig);
  if( pDup==0 ) return;
  if( pDup->op!=TK_COLUMN && zType[0]!='G' ){
    pDup = sqlite3PExpr(pParse, TK_AS, pDup, 0, 0);
    if( pDup==0 ) return;
    if( pEList->a[iCol].iAlias==0 ){
      pEList->a[iCol].iAlias = ++pParse->nAlias;
    }
    pDup->iTable = pEList->a[iCol].iAlias;
  }
  if( pExpr->flags & EP_ExpCollate ){
    pDup->pColl = pExpr->pColl;
    pDup->flags |= EP_ExpCollate;
  }
  if( pExpr->span.dyn ) sqlite3DbFree(db, (char*)pExpr->span.z);
  if( pExpr->token.dyn ) sqlite3DbFree(db, (char*)pExpr->token.z);
  memcpy(pExpr, pDup, sizeof(*pExpr));
  sqlite3DbFree(db, pDup);
}

/*
** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
** that name in the set of source tables in pSrcList and make the pExpr 
** expression node refer back to that source column.  The following changes
** are made to pExpr:
**
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    */
    if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){
      for(j=0; j<pEList->nExpr; j++){
        char *zAs = pEList->a[j].zName;
        if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
          Expr *pDup, *pOrig;
          assert( pExpr->pLeft==0 && pExpr->pRight==0 );
          assert( pExpr->pList==0 );
          assert( pExpr->pSelect==0 );
          pOrig = pEList->a[j].pExpr;
          if( !pNC->allowAgg && ExprHasProperty(pOrig, EP_Agg) ){
            sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs);
            sqlite3DbFree(db, zCol);
            return 2;
          }
          pDup = sqlite3ExprDup(db, pOrig);
          if( pExpr->flags & EP_ExpCollate ){
            pDup->pColl = pExpr->pColl;
            pDup->flags |= EP_ExpCollate;
          }
          if( pExpr->span.dyn ) sqlite3DbFree(db, (char*)pExpr->span.z);
          if( pExpr->token.dyn ) sqlite3DbFree(db, (char*)pExpr->token.z);
          memcpy(pExpr, pDup, sizeof(*pExpr));
          sqlite3DbFree(db, pDup);
          cnt = 1;
          pMatch = 0;
          assert( zTab==0 && zDb==0 );
          goto lookupname_end_2;
        }
      } 
    }







|









<
<
<
<
<
<
<
|
<







277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293







294

295
296
297
298
299
300
301
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    */
    if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){
      for(j=0; j<pEList->nExpr; j++){
        char *zAs = pEList->a[j].zName;
        if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
          Expr *pOrig;
          assert( pExpr->pLeft==0 && pExpr->pRight==0 );
          assert( pExpr->pList==0 );
          assert( pExpr->pSelect==0 );
          pOrig = pEList->a[j].pExpr;
          if( !pNC->allowAgg && ExprHasProperty(pOrig, EP_Agg) ){
            sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs);
            sqlite3DbFree(db, zCol);
            return 2;
          }







          resolveAlias(pParse, pEList, j, pExpr, "");

          cnt = 1;
          pMatch = 0;
          assert( zTab==0 && zDb==0 );
          goto lookupname_end_2;
        }
      } 
    }
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
    return 1;
  }
#endif
  pEList = pSelect->pEList;
  assert( pEList!=0 );  /* sqlite3SelectNew() guarantees this */
  for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){
    if( pItem->iCol ){
      Expr *pE;
      CollSeq *pColl;
      int flags;

      if( pItem->iCol>pEList->nExpr ){
        resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr);
        return 1;
      }
      pE = pItem->pExpr;
      pColl = pE->pColl;
      flags = pE->flags & EP_ExpCollate;
      sqlite3ExprDelete(db, pE);
      pE = sqlite3ExprDup(db, pEList->a[pItem->iCol-1].pExpr);
      pItem->pExpr = pE;
      if( pE && flags ){
        pE->pColl = pColl;
        pE->flags |= flags;
      }
    }
  }
  return 0;
}

/*
** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.







<
<
<
<




|
<
<
<
<
<
<
<
<
<







782
783
784
785
786
787
788




789
790
791
792
793









794
795
796
797
798
799
800
    return 1;
  }
#endif
  pEList = pSelect->pEList;
  assert( pEList!=0 );  /* sqlite3SelectNew() guarantees this */
  for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){
    if( pItem->iCol ){




      if( pItem->iCol>pEList->nExpr ){
        resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr);
        return 1;
      }
      resolveAlias(pParse, pEList, pItem->iCol-1, pItem->pExpr, zType);









    }
  }
  return 0;
}

/*
** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.761 2008/08/22 17:34:45 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.762 2008/08/29 02:14:03 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
1341
1342
1343
1344
1345
1346
1347

1348
1349
1350
1351
1352
1353
1354
  int iECursor;          /* VDBE Cursor associated with this ExprList */
  struct ExprList_item {
    Expr *pExpr;           /* The list of expressions */
    char *zName;           /* Token associated with this expression */
    u8 sortOrder;          /* 1 for DESC or 0 for ASC */
    u8 done;               /* A flag to indicate when processing is finished */
    u16 iCol;              /* For ORDER BY, column number in result set */

  } *a;                  /* One entry for each expression */
};

/*
** An instance of this structure can hold a simple list of identifiers,
** such as the list "a,b,c" in the following statements:
**







>







1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
  int iECursor;          /* VDBE Cursor associated with this ExprList */
  struct ExprList_item {
    Expr *pExpr;           /* The list of expressions */
    char *zName;           /* Token associated with this expression */
    u8 sortOrder;          /* 1 for DESC or 0 for ASC */
    u8 done;               /* A flag to indicate when processing is finished */
    u16 iCol;              /* For ORDER BY, column number in result set */
    u16 iAlias;            /* Index into Parse.aAlias[] for zName */
  } *a;                  /* One entry for each expression */
};

/*
** An instance of this structure can hold a simple list of identifiers,
** such as the list "a,b,c" in the following statements:
**
1677
1678
1679
1680
1681
1682
1683


1684
1685
1686
1687
1688
1689
1690
  /* Above is constant between recursions.  Below is reset before and after
  ** each recursion */

  int nVar;            /* Number of '?' variables seen in the SQL so far */
  int nVarExpr;        /* Number of used slots in apVarExpr[] */
  int nVarExprAlloc;   /* Number of allocated slots in apVarExpr[] */
  Expr **apVarExpr;    /* Pointers to :aaa and $aaaa wildcard expressions */


  u8 explain;          /* True if the EXPLAIN flag is found on the query */
  Token sErrToken;     /* The token at which the error occurred */
  Token sNameToken;    /* Token with unqualified schema object name */
  Token sLastToken;    /* The last token parsed */
  const char *zSql;    /* All SQL text */
  const char *zTail;   /* All SQL text past the last semicolon parsed */
  Table *pNewTable;    /* A table being constructed by CREATE TABLE */







>
>







1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
  /* Above is constant between recursions.  Below is reset before and after
  ** each recursion */

  int nVar;            /* Number of '?' variables seen in the SQL so far */
  int nVarExpr;        /* Number of used slots in apVarExpr[] */
  int nVarExprAlloc;   /* Number of allocated slots in apVarExpr[] */
  Expr **apVarExpr;    /* Pointers to :aaa and $aaaa wildcard expressions */
  int nAlias;          /* Number of aliased result set columns */
  int *aAlias;         /* Register used to hold aliased result */
  u8 explain;          /* True if the EXPLAIN flag is found on the query */
  Token sErrToken;     /* The token at which the error occurred */
  Token sNameToken;    /* Token with unqualified schema object name */
  Token sLastToken;    /* The last token parsed */
  const char *zSql;    /* All SQL text */
  const char *zTail;   /* All SQL text past the last semicolon parsed */
  Table *pNewTable;    /* A table being constructed by CREATE TABLE */
Changes to src/tokenize.c.
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.150 2008/08/08 14:19:41 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <stdlib.h>

/*
** The charMap() macro maps alphabetic characters into their







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.151 2008/08/29 02:14:03 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <stdlib.h>

/*
** The charMap() macro maps alphabetic characters into their
499
500
501
502
503
504
505

506
507
508
509
510
    ** will take responsibility for freeing the Table structure.
    */
    sqlite3DeleteTable(pParse->pNewTable);
  }

  sqlite3DeleteTrigger(db, pParse->pNewTrigger);
  sqlite3DbFree(db, pParse->apVarExpr);

  if( nErr>0 && (pParse->rc==SQLITE_OK || pParse->rc==SQLITE_DONE) ){
    pParse->rc = SQLITE_ERROR;
  }
  return nErr;
}







>





499
500
501
502
503
504
505
506
507
508
509
510
511
    ** will take responsibility for freeing the Table structure.
    */
    sqlite3DeleteTable(pParse->pNewTable);
  }

  sqlite3DeleteTrigger(db, pParse->pNewTrigger);
  sqlite3DbFree(db, pParse->apVarExpr);
  sqlite3DbFree(db, pParse->aAlias);
  if( nErr>0 && (pParse->rc==SQLITE_OK || pParse->rc==SQLITE_DONE) ){
    pParse->rc = SQLITE_ERROR;
  }
  return nErr;
}
Added test/alias.test.


















































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
# 2008 August 28
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements regression tests for SQLite library.  The
# focus of this script is correct code generation of aliased result-set
# values.  See ticket #3343.
#
# $Id: alias.test,v 1.1 2008/08/29 02:14:03 drh Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl

# A procedure to return a sequence of increasing integers.
#
namespace eval ::seq {
  variable counter 0
  proc value {args} {
    variable counter
    incr counter
    return $counter
  }
  proc reset {} {
    variable counter
    set counter 0
  }
}


do_test alias-1.1 {
  db function sequence ::seq::value
  db eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(9);
    INSERT INTO t1 VALUES(8);
    INSERT INTO t1 VALUES(7);
    SELECT x, sequence() FROM t1;
  }
} {9 1 8 2 7 3}
do_test alias-1.2 {
  ::seq::reset
  db eval {
--pragma vdbe_listing=on; pragma vdbe_trace=on;
    SELECT x, sequence() AS y FROM t1 WHERE y>0
  }
} {9 1 8 2 7 3}
do_test alias-1.3 {
  ::seq::reset
  db eval {
    SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99
  }
} {9 1 8 2 7 3}
do_test alias-1.4 {
  ::seq::reset
  db eval {
    SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55
  }
} {9 1 8 2 7 3}
do_test alias-1.5 {
  ::seq::reset
  db eval {
    SELECT x, sequence() AS y FROM t1
     WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58)
       AND y NOT LIKE 'abc%' AND y%10==2
  }
} {8 2}
do_test alias-1.6 {
  ::seq::reset
  db eval {
    SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99
  }
} {9 1 8 2 7 3}
do_test alias-1.7 {
  ::seq::reset
  db eval {
    SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3)
  }
} {7 3}
do_test alias-1.8 {
  ::seq::reset
  db eval {
    SELECT x, 1-sequence() AS y FROM t1 ORDER BY y
  }
} {7 -2 8 -1 9 0}
do_test alias-1.9 {
  ::seq::reset
  db eval {
    SELECT x, sequence() AS y FROM t1 ORDER BY -y
  }
} {7 3 8 2 9 1}
do_test alias-1.10 {
  ::seq::reset
  db eval {
    SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y
  }
} {8 2 9 1 7 3}

unset -nocomplain random_int_list
set random_int_list [db eval {
   SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r
}]
do_test alias-1.11 {
  lsort -integer $::random_int_list
} $random_int_list


do_test alias-2.1 {
  db eval {
    SELECT 4 UNION SELECT 1 ORDER BY 1
  }
} {1 4}
do_test alias-2.2 {
  db eval {
    SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1
  }
} {1 4 9}

if 0 {
  # Aliases in the GROUP BY clause cause the expression to be evaluated
  # twice in the current implementation.  This might change in the future.
  #
  do_test alias-3.1 {
    ::seq::reset
    db eval {
      SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y
    }
  } {1 1 2 1 3 1}
}

finish_test