/ Check-in [f0d3ca10]
Login

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

Overview
Comment:Fix for correlated subqueries where the parent is an aggregate. Ticket #1105. (CVS 2318)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:f0d3ca10c5bccf8fca7143028ebb3e604c0e3f20
User & Date: danielk1977 2005-02-08 07:50:41
Context
2005-02-08
08:42
Fix a buggy interaction between "INSERT ... SELECT" processing and optimization (2170). (CVS 2319) check-in: c54ad212 user: danielk1977 tags: trunk
07:50
Fix for correlated subqueries where the parent is an aggregate. Ticket #1105. (CVS 2318) check-in: f0d3ca10 user: danielk1977 tags: trunk
2005-02-06
02:45
Add the (highly experimental) omit_readlock pragma that disables the use of readlocks on read-only databases that are connected using ATTACH. (CVS 2317) check-in: 2155448d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
185
186
187
188
189
190
191

192
193
194
195
196
197
198
...
585
586
587
588
589
590
591

592
593
594
595
596
597
598
599
600
601








602
603

604
605
606

607
608











609
610

611
612
613
614
615
616
617
....
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
....
1964
1965
1966
1967
1968
1969
1970

1971

1972
1973
1974


1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989

1990
1991



1992

1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012






2013
2014
2015
2016
2017
2018
2019
....
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
**    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.192 2005/02/05 12:48:48 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
  if( pNew==0 ){
    /* When malloc fails, we leak memory from pLeft and pRight */
    return 0;
  }
  pNew->op = op;
  pNew->pLeft = pLeft;
  pNew->pRight = pRight;

  if( pToken ){
    assert( pToken->dyn==0 );
    pNew->span = pNew->token = *pToken;
  }else if( pLeft && pRight ){
    sqlite3ExprSpan(pNew, &pLeft->span, &pRight->span);
  }
  return pNew;
................................................................................
** 0 means continue walking the tree.  1 means do not walk children
** of the current node but continue with siblings.  2 means abandon
** the tree walk completely.
**
** The return value from this routine is 1 to abandon the tree walk
** and 0 to continue.
*/

static int walkExprTree(Expr *pExpr, int (*xFunc)(void*,Expr*), void *pArg){
  ExprList *pList;
  int rc;
  if( pExpr==0 ) return 0;
  rc = (*xFunc)(pArg, pExpr);
  if( rc==0 ){
    if( walkExprTree(pExpr->pLeft, xFunc, pArg) ) return 1;
    if( walkExprTree(pExpr->pRight, xFunc, pArg) ) return 1;
    pList = pExpr->pList;
    if( pList ){








      int i;
      struct ExprList_item *pItem;

      for(i=pList->nExpr, pItem=pList->a; i>0; i--, pItem++){
        if( walkExprTree(pItem->pExpr, xFunc, pArg) ) return 1;
      }

    }
  }











  return rc>1;
}


/*
** This routine is designed as an xFunc for walkExprTree().
**
** pArg is really a pointer to an integer.  If we can tell by looking
** at pExpr that the expression that contains pExpr is not a constant
** expression, then set *pArg to 0 and return 2 to abandon the tree walk.
................................................................................
  if( pExpr==0 ){
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);  /* Empty expression evals to NULL */
    return;
  }
  op = pExpr->op;
  switch( op ){
    case TK_COLUMN: {
      if( pParse->useAgg ){
        sqlite3VdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg);
      }else if( pExpr->iColumn>=0 ){
        sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
#ifndef NDEBUG
        if( pExpr->span.z && pExpr->span.n>0 && pExpr->span.n<100 ){
          VdbeComment((v, "# %T", &pExpr->span));
        }
#endif
................................................................................
** for additional information.
**
** This routine analyzes the aggregate function at pExpr.
*/
static int analyzeAggregate(void *pArg, Expr *pExpr){
  int i;
  AggExpr *aAgg;

  Parse *pParse = (Parse*)pArg;


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


      aAgg = pParse->aAgg;
      for(i=0; i<pParse->nAgg; i++){
        if( aAgg[i].isAgg ) continue;
        if( aAgg[i].pExpr->iTable==pExpr->iTable
         && aAgg[i].pExpr->iColumn==pExpr->iColumn ){
          break;
        }
      }
      if( i>=pParse->nAgg ){
        i = appendAggInfo(pParse);
        if( i<0 ) return 1;
        pParse->aAgg[i].isAgg = 0;
        pParse->aAgg[i].pExpr = pExpr;
      }
      pExpr->iAgg = i;

      return 1;
    }



    case TK_AGG_FUNCTION: {

      aAgg = pParse->aAgg;
      for(i=0; i<pParse->nAgg; i++){
        if( !aAgg[i].isAgg ) continue;
        if( sqlite3ExprCompare(aAgg[i].pExpr, pExpr) ){
          break;
        }
      }
      if( i>=pParse->nAgg ){
        u8 enc = pParse->db->enc;
        i = appendAggInfo(pParse);
        if( i<0 ) return 1;
        pParse->aAgg[i].isAgg = 1;
        pParse->aAgg[i].pExpr = pExpr;
        pParse->aAgg[i].pFunc = sqlite3FindFunction(pParse->db,
             pExpr->token.z, pExpr->token.n,
             pExpr->pList ? pExpr->pList->nExpr : 0, enc, 0);
      }
      pExpr->iAgg = i;
      return 1;
    }






  }
  return 0;
}

/*
** Analyze the given expression looking for aggregate functions and
** for variables that need to be added to the pParse->aAgg[] array.
................................................................................
**
** This routine should only be called after the expression has been
** analyzed by sqlite3ExprResolveNames().
**
** If errors are seen, leave an error message in zErrMsg and return
** the number of errors.
*/
int sqlite3ExprAnalyzeAggregates(Parse *pParse, Expr *pExpr){
  int nErr = pParse->nErr;
  walkExprTree(pExpr, analyzeAggregate, pParse);
  return pParse->nErr - nErr;
}

/*
** Locate a user function given a name, a number of arguments and a flag
** indicating whether the function prefers UTF-16 over UTF-8.  Return a
** pointer to the FuncDef structure that defines that function, or return
** NULL if the function does not exist.







|







 







>







 







>

<






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

>







 







|
|







 







>
|
>



>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
|
|
>
>
>

>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>







 







|
|
|
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
...
586
587
588
589
590
591
592
593
594

595
596
597
598
599
600
601

602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
....
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
....
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
....
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
**    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.193 2005/02/08 07:50:41 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
  if( pNew==0 ){
    /* When malloc fails, we leak memory from pLeft and pRight */
    return 0;
  }
  pNew->op = op;
  pNew->pLeft = pLeft;
  pNew->pRight = pRight;
  pNew->iAgg = -1;
  if( pToken ){
    assert( pToken->dyn==0 );
    pNew->span = pNew->token = *pToken;
  }else if( pLeft && pRight ){
    sqlite3ExprSpan(pNew, &pLeft->span, &pRight->span);
  }
  return pNew;
................................................................................
** 0 means continue walking the tree.  1 means do not walk children
** of the current node but continue with siblings.  2 means abandon
** the tree walk completely.
**
** The return value from this routine is 1 to abandon the tree walk
** and 0 to continue.
*/
static int walkExprList(ExprList *, int (*)(void *, Expr*), void *);
static int walkExprTree(Expr *pExpr, int (*xFunc)(void*,Expr*), void *pArg){

  int rc;
  if( pExpr==0 ) return 0;
  rc = (*xFunc)(pArg, pExpr);
  if( rc==0 ){
    if( walkExprTree(pExpr->pLeft, xFunc, pArg) ) return 1;
    if( walkExprTree(pExpr->pRight, xFunc, pArg) ) return 1;
    if( walkExprList(pExpr->pList, xFunc, pArg) ) return 1;

  }
  return rc>1;
}

/*
** Call walkExprTree() for every expression in list p.
*/
static int walkExprList(ExprList *p, int (*xFunc)(void *, Expr*), void *pArg){
  int i;
  struct ExprList_item *pItem;
  if( !p ) return 0;
  for(i=p->nExpr, pItem=p->a; i>0; i--, pItem++){
    if( walkExprTree(pItem->pExpr, xFunc, pArg) ) return 1;
  }
  return 0;
}

/*
** Call walkExprTree() for every expression in Select p, not including
** expressions that are part of sub-selects in any FROM clause or the LIMIT
** or OFFSET expressions..
*/
static int walkSelectExpr(Select *p, int (*xFunc)(void *, Expr*), void *pArg){
  walkExprList(p->pEList, xFunc, pArg);
  walkExprTree(p->pWhere, xFunc, pArg);
  walkExprList(p->pGroupBy, xFunc, pArg);
  walkExprTree(p->pHaving, xFunc, pArg);
  walkExprList(p->pOrderBy, xFunc, pArg);
  return 0;
}


/*
** This routine is designed as an xFunc for walkExprTree().
**
** pArg is really a pointer to an integer.  If we can tell by looking
** at pExpr that the expression that contains pExpr is not a constant
** expression, then set *pArg to 0 and return 2 to abandon the tree walk.
................................................................................
  if( pExpr==0 ){
    sqlite3VdbeAddOp(v, OP_String8, 0, 0);  /* Empty expression evals to NULL */
    return;
  }
  op = pExpr->op;
  switch( op ){
    case TK_COLUMN: {
      if( !pParse->fillAgg && pExpr->iAgg>=0 ){
        sqlite3VdbeAddOp(v, OP_AggGet, pExpr->iAggCtx, pExpr->iAgg);
      }else if( pExpr->iColumn>=0 ){
        sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
#ifndef NDEBUG
        if( pExpr->span.z && pExpr->span.n>0 && pExpr->span.n<100 ){
          VdbeComment((v, "# %T", &pExpr->span));
        }
#endif
................................................................................
** for additional information.
**
** This routine analyzes the aggregate function at pExpr.
*/
static int analyzeAggregate(void *pArg, Expr *pExpr){
  int i;
  AggExpr *aAgg;
  NameContext *pNC = (NameContext *)pArg;
  Parse *pParse = pNC->pParse;
  SrcList *pSrcList = pNC->pSrcList;

  switch( pExpr->op ){
    case TK_COLUMN: {
      for(i=0; pSrcList && i<pSrcList->nSrc; i++){
        if( pExpr->iTable==pSrcList->a[i].iCursor ){
          aAgg = pParse->aAgg;
          for(i=0; i<pParse->nAgg; i++){
            if( aAgg[i].isAgg ) continue;
            if( aAgg[i].pExpr->iTable==pExpr->iTable
             && aAgg[i].pExpr->iColumn==pExpr->iColumn ){
              break;
            }
          }
          if( i>=pParse->nAgg ){
            i = appendAggInfo(pParse);
            if( i<0 ) return 1;
            pParse->aAgg[i].isAgg = 0;
            pParse->aAgg[i].pExpr = pExpr;
          }
          pExpr->iAgg = i;
          pExpr->iAggCtx = pNC->nDepth;
          return 1;
        }
      }
      return 1;
    }
    case TK_AGG_FUNCTION: {
      if( pNC->nDepth==0 ){
        aAgg = pParse->aAgg;
        for(i=0; i<pParse->nAgg; i++){
          if( !aAgg[i].isAgg ) continue;
          if( sqlite3ExprCompare(aAgg[i].pExpr, pExpr) ){
            break;
          }
        }
        if( i>=pParse->nAgg ){
          u8 enc = pParse->db->enc;
          i = appendAggInfo(pParse);
          if( i<0 ) return 1;
          pParse->aAgg[i].isAgg = 1;
          pParse->aAgg[i].pExpr = pExpr;
          pParse->aAgg[i].pFunc = sqlite3FindFunction(pParse->db,
               pExpr->token.z, pExpr->token.n,
               pExpr->pList ? pExpr->pList->nExpr : 0, enc, 0);
        }
        pExpr->iAgg = i;
        return 1;
      }
    }
  }
  if( pExpr->pSelect ){
    pNC->nDepth++;
    walkSelectExpr(pExpr->pSelect, analyzeAggregate, pNC);
    pNC->nDepth--;
  }
  return 0;
}

/*
** Analyze the given expression looking for aggregate functions and
** for variables that need to be added to the pParse->aAgg[] array.
................................................................................
**
** This routine should only be called after the expression has been
** analyzed by sqlite3ExprResolveNames().
**
** If errors are seen, leave an error message in zErrMsg and return
** the number of errors.
*/
int sqlite3ExprAnalyzeAggregates(NameContext *pNC, Expr *pExpr){
  int nErr = pNC->pParse->nErr;
  walkExprTree(pExpr, analyzeAggregate, pNC);
  return pNC->pParse->nErr - nErr;
}

/*
** Locate a user function given a name, a number of arguments and a flag
** indicating whether the function prefers UTF-16 over UTF-8.  Return a
** pointer to the FuncDef structure that defines that function, or return
** NULL if the function does not exist.

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1237
1238
1239
1240
1241
1242
1243

1244
1245
1246
1247
1248
1249
1250
....
2091
2092
2093
2094
2095
2096
2097



2098
2099
2100
2101
2102
2103
2104
....
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
....
2670
2671
2672
2673
2674
2675
2676





2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
....
2761
2762
2763
2764
2765
2766
2767


2768
2769
2770
2771
2772
2773
2774
2775
2776
2777

2778
2779
2780
2781
2782


2783
2784
2785
2786
2787
2788
2789
....
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
**    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
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.239 2005/02/05 12:48:48 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
        iCol = j;
      }
    }
    if( iCol>=0 ){
      pE->op = TK_COLUMN;
      pE->iColumn = iCol;
      pE->iTable = iTable;

      pOrderBy->a[i].done = 1;
    }
    if( iCol<0 && mustComplete ){
      sqlite3ErrorMsg(pParse,
        "ORDER BY term number %d does not match any result column", i+1);
      nErr++;
      break;
................................................................................
  }

  /* The flattened query is distinct if either the inner or the
  ** outer query is distinct. 
  */
  p->isDistinct = p->isDistinct || pSub->isDistinct;




  if( pSub->pLimit ){
    p->pLimit = pSub->pLimit;
    pSub->pLimit = 0;
  }

  /* Finially, delete what is left of the subquery and return
  ** success.
................................................................................
/*
** An instance of the following struct is used by sqlite3Select()
** to save aggregate related information from the Parse object
** at the start of each call and to restore it at the end. See
** saveAggregateInfo() and restoreAggregateInfo().
*/ 
struct AggregateInfo {
  u8 useAgg;
  int nAgg;
  AggExpr *aAgg;
};
typedef struct AggregateInfo AggregateInfo;

/* 
** Copy aggregate related information from the Parse structure
** into the AggregateInfo structure. Zero the aggregate related
** values in the Parse struct.
*/
static void saveAggregateInfo(Parse *pParse, AggregateInfo *pInfo){
  pInfo->aAgg = pParse->aAgg;
  pInfo->nAgg = pParse->nAgg;
  pInfo->useAgg = pParse->useAgg;
  pParse->aAgg = 0;
  pParse->nAgg = 0;
  pParse->useAgg = 0;
}

/*
** Copy aggregate related information from the AggregateInfo struct
** back into the Parse structure. The aggregate related information
** currently stored in the Parse structure is deleted.
*/
static void restoreAggregateInfo(Parse *pParse, AggregateInfo *pInfo){
  sqliteFree(pParse->aAgg);
  pParse->aAgg = pInfo->aAgg;
  pParse->nAgg = pInfo->nAgg;
  pParse->useAgg = pInfo->useAgg;
}
  
/*
** Generate code for the given SELECT statement.
**
** The results are distributed in various ways depending on the
** value of eDest and iParm.
................................................................................
    sqlite3VdbeAddOp(v, OP_OpenTemp, iParm, 0);
    sqlite3VdbeAddOp(v, OP_SetNumColumns, iParm, pEList->nExpr);
  }

  /* Do an analysis of aggregate expressions.
  */
  if( isAgg || pGroupBy ){





    assert( pParse->nAgg==0 );
    isAgg = 1;
    for(i=0; i<pEList->nExpr; i++){
      if( sqlite3ExprAnalyzeAggregates(pParse, pEList->a[i].pExpr) ){
        goto select_end;
      }
    }
    if( pGroupBy ){
      for(i=0; i<pGroupBy->nExpr; i++){
        if( sqlite3ExprAnalyzeAggregates(pParse, pGroupBy->a[i].pExpr) ){
          goto select_end;
        }
      }
    }
    if( pHaving && sqlite3ExprAnalyzeAggregates(pParse, pHaving) ){
      goto select_end;
    }
    if( pOrderBy ){
      for(i=0; i<pOrderBy->nExpr; i++){
        if( sqlite3ExprAnalyzeAggregates(pParse, pOrderBy->a[i].pExpr) ){
          goto select_end;
        }
      }
    }
  }

  /* Reset the aggregator
................................................................................
  }

  /* If we are dealing with aggregates, then do the special aggregate
  ** processing.  
  */
  else{
    AggExpr *pAgg;


    if( pGroupBy ){
      int lbl1;
      for(i=0; i<pGroupBy->nExpr; i++){
        sqlite3ExprCode(pParse, pGroupBy->a[i].pExpr);
      }
      /* No affinity string is attached to the following OP_MakeRecord 
      ** because we do not need to do any coercion of datatypes. */
      sqlite3VdbeAddOp(v, OP_MakeRecord, pGroupBy->nExpr, 0);
      lbl1 = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp(v, OP_AggFocus, 0, lbl1);

      for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
        if( pAgg->isAgg ) continue;
        sqlite3ExprCode(pParse, pAgg->pExpr);
        sqlite3VdbeAddOp(v, OP_AggSet, 0, i);
      }


      sqlite3VdbeResolveLabel(v, lbl1);
    }
    for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
      Expr *pE;
      int nExpr;
      FuncDef *pDef;
      if( !pAgg->isAgg ) continue;
................................................................................
  /* If we are processing aggregates, we need to set up a second loop
  ** over all of the aggregate values and process them.
  */
  if( isAgg ){
    int endagg = sqlite3VdbeMakeLabel(v);
    int startagg;
    startagg = sqlite3VdbeAddOp(v, OP_AggNext, 0, endagg);
    pParse->useAgg = 1;
    if( pHaving ){
      sqlite3ExprIfFalse(pParse, pHaving, startagg, 1);
    }
    if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
                    iParm, startagg, endagg, aff) ){
      goto select_end;
    }
    sqlite3VdbeAddOp(v, OP_Goto, 0, startagg);
    sqlite3VdbeResolveLabel(v, endagg);
    sqlite3VdbeAddOp(v, OP_Noop, 0, 0);
    pParse->useAgg = 0;
  }

  /* If there is an ORDER BY clause, then we need to sort the results
  ** and send them to the callback one by one.
  */
  if( pOrderBy ){
    generateSortTail(pParse, p, v, pEList->nExpr, eDest, iParm);







|







 







>







 







>
>
>







 







<













<


<











<







 







>
>
>
>
>



|





|




|




|







 







>
>

<








>
|
|
|
|
|
>
>







 







<










<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
....
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
....
2412
2413
2414
2415
2416
2417
2418

2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431

2432
2433

2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444

2445
2446
2447
2448
2449
2450
2451
....
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
....
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775

2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
....
2824
2825
2826
2827
2828
2829
2830

2831
2832
2833
2834
2835
2836
2837
2838
2839
2840

2841
2842
2843
2844
2845
2846
2847
**    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
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.240 2005/02/08 07:50:41 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
        iCol = j;
      }
    }
    if( iCol>=0 ){
      pE->op = TK_COLUMN;
      pE->iColumn = iCol;
      pE->iTable = iTable;
      pE->iAgg = -1;
      pOrderBy->a[i].done = 1;
    }
    if( iCol<0 && mustComplete ){
      sqlite3ErrorMsg(pParse,
        "ORDER BY term number %d does not match any result column", i+1);
      nErr++;
      break;
................................................................................
  }

  /* The flattened query is distinct if either the inner or the
  ** outer query is distinct. 
  */
  p->isDistinct = p->isDistinct || pSub->isDistinct;

  /*
  ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y;
  */
  if( pSub->pLimit ){
    p->pLimit = pSub->pLimit;
    pSub->pLimit = 0;
  }

  /* Finially, delete what is left of the subquery and return
  ** success.
................................................................................
/*
** An instance of the following struct is used by sqlite3Select()
** to save aggregate related information from the Parse object
** at the start of each call and to restore it at the end. See
** saveAggregateInfo() and restoreAggregateInfo().
*/ 
struct AggregateInfo {

  int nAgg;
  AggExpr *aAgg;
};
typedef struct AggregateInfo AggregateInfo;

/* 
** Copy aggregate related information from the Parse structure
** into the AggregateInfo structure. Zero the aggregate related
** values in the Parse struct.
*/
static void saveAggregateInfo(Parse *pParse, AggregateInfo *pInfo){
  pInfo->aAgg = pParse->aAgg;
  pInfo->nAgg = pParse->nAgg;

  pParse->aAgg = 0;
  pParse->nAgg = 0;

}

/*
** Copy aggregate related information from the AggregateInfo struct
** back into the Parse structure. The aggregate related information
** currently stored in the Parse structure is deleted.
*/
static void restoreAggregateInfo(Parse *pParse, AggregateInfo *pInfo){
  sqliteFree(pParse->aAgg);
  pParse->aAgg = pInfo->aAgg;
  pParse->nAgg = pInfo->nAgg;

}
  
/*
** Generate code for the given SELECT statement.
**
** The results are distributed in various ways depending on the
** value of eDest and iParm.
................................................................................
    sqlite3VdbeAddOp(v, OP_OpenTemp, iParm, 0);
    sqlite3VdbeAddOp(v, OP_SetNumColumns, iParm, pEList->nExpr);
  }

  /* Do an analysis of aggregate expressions.
  */
  if( isAgg || pGroupBy ){
    NameContext sNC;
    memset(&sNC, 0, sizeof(sNC));
    sNC.pParse = pParse;
    sNC.pSrcList = pTabList;

    assert( pParse->nAgg==0 );
    isAgg = 1;
    for(i=0; i<pEList->nExpr; i++){
      if( sqlite3ExprAnalyzeAggregates(&sNC, pEList->a[i].pExpr) ){
        goto select_end;
      }
    }
    if( pGroupBy ){
      for(i=0; i<pGroupBy->nExpr; i++){
        if( sqlite3ExprAnalyzeAggregates(&sNC, pGroupBy->a[i].pExpr) ){
          goto select_end;
        }
      }
    }
    if( pHaving && sqlite3ExprAnalyzeAggregates(&sNC, pHaving) ){
      goto select_end;
    }
    if( pOrderBy ){
      for(i=0; i<pOrderBy->nExpr; i++){
        if( sqlite3ExprAnalyzeAggregates(&sNC, pOrderBy->a[i].pExpr) ){
          goto select_end;
        }
      }
    }
  }

  /* Reset the aggregator
................................................................................
  }

  /* If we are dealing with aggregates, then do the special aggregate
  ** processing.  
  */
  else{
    AggExpr *pAgg;
    int lbl1 = 0;
    pParse->fillAgg = 1;
    if( pGroupBy ){

      for(i=0; i<pGroupBy->nExpr; i++){
        sqlite3ExprCode(pParse, pGroupBy->a[i].pExpr);
      }
      /* No affinity string is attached to the following OP_MakeRecord 
      ** because we do not need to do any coercion of datatypes. */
      sqlite3VdbeAddOp(v, OP_MakeRecord, pGroupBy->nExpr, 0);
      lbl1 = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp(v, OP_AggFocus, 0, lbl1);
    }
    for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
      if( pAgg->isAgg ) continue;
      sqlite3ExprCode(pParse, pAgg->pExpr);
      sqlite3VdbeAddOp(v, OP_AggSet, 0, i);
    }
    pParse->fillAgg = 0;
    if( lbl1<0 ){
      sqlite3VdbeResolveLabel(v, lbl1);
    }
    for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
      Expr *pE;
      int nExpr;
      FuncDef *pDef;
      if( !pAgg->isAgg ) continue;
................................................................................
  /* If we are processing aggregates, we need to set up a second loop
  ** over all of the aggregate values and process them.
  */
  if( isAgg ){
    int endagg = sqlite3VdbeMakeLabel(v);
    int startagg;
    startagg = sqlite3VdbeAddOp(v, OP_AggNext, 0, endagg);

    if( pHaving ){
      sqlite3ExprIfFalse(pParse, pHaving, startagg, 1);
    }
    if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
                    iParm, startagg, endagg, aff) ){
      goto select_end;
    }
    sqlite3VdbeAddOp(v, OP_Goto, 0, startagg);
    sqlite3VdbeResolveLabel(v, endagg);
    sqlite3VdbeAddOp(v, OP_Noop, 0, 0);

  }

  /* If there is an ORDER BY clause, then we need to sort the results
  ** and send them to the callback one by one.
  */
  if( pOrderBy ){
    generateSortTail(pParse, p, v, pEList->nExpr, eDest, iParm);

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
808
809
810
811
812
813
814
815
816

817
818
819
820
821
822
823
....
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
....
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
**    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.368 2005/02/06 02:45:43 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
................................................................................
  Expr *pLeft, *pRight;  /* Left and right subnodes */
  ExprList *pList;       /* A list of expressions used as function arguments
                         ** or in "<expr> IN (<expr-list)" */
  Token token;           /* An operand token */
  Token span;            /* Complete text of the expression */
  int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
                         ** iColumn-th field of the iTable-th table. */
  int iAgg;              /* When op==TK_COLUMN and pParse->useAgg==TRUE, pull
                         ** result from the iAgg-th element of the aggregator */

  Select *pSelect;       /* When the expression is a sub-select.  Also the
                         ** right side of "<expr> IN (<select>)" */
};

/*
** The following are the meanings of bits in the Expr.flags field.
*/
................................................................................
  int nTab;            /* Number of previously allocated VDBE cursors */
  int nMem;            /* Number of memory cells used so far */
  int nSet;            /* Number of sets used so far */
  u32 cookieMask;      /* Bitmask of schema verified databases */
  int cookieValue[MAX_ATTACHED+2];  /* Values of cookies to verify */
  int cookieGoto;      /* Address of OP_Goto to cookie verifier subroutine */
  u32 writeMask;       /* Start a write transaction on these databases */
  u8 useAgg;           /* If true, extract field values from the aggregator
                       ** while generating expressions.  Normally false */

  /* 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[] */
................................................................................
void sqlite3Vacuum(Parse*, Token*);
int sqlite3RunVacuum(char**, sqlite3*);
char *sqlite3NameFromToken(Token*);
int sqlite3ExprCheck(Parse*, Expr*, int, int*);
int sqlite3ExprCompare(Expr*, Expr*);
int sqliteFuncId(Token*);
int sqlite3ExprResolveNames(NameContext *, Expr *);
int sqlite3ExprAnalyzeAggregates(Parse*, Expr*);
Vdbe *sqlite3GetVdbe(Parse*);
void sqlite3Randomness(int, void*);
void sqlite3RollbackAll(sqlite3*);
void sqlite3CodeVerifySchema(Parse*, int);
void sqlite3BeginTransaction(Parse*, int);
void sqlite3CommitTransaction(Parse*);
void sqlite3RollbackTransaction(Parse*);







|







 







|

>







 







|
<







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
....
1088
1089
1090
1091
1092
1093
1094
1095

1096
1097
1098
1099
1100
1101
1102
....
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
**    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.369 2005/02/08 07:50:42 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
................................................................................
  Expr *pLeft, *pRight;  /* Left and right subnodes */
  ExprList *pList;       /* A list of expressions used as function arguments
                         ** or in "<expr> IN (<expr-list)" */
  Token token;           /* An operand token */
  Token span;            /* Complete text of the expression */
  int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
                         ** iColumn-th field of the iTable-th table. */
  int iAgg;              /* When op==TK_COLUMN and pParse->fillAgg==FALSE, pull
                         ** result from the iAgg-th element of the aggregator */
  int iAggCtx;           /* The value to pass as P1 of OP_AggGet. */
  Select *pSelect;       /* When the expression is a sub-select.  Also the
                         ** right side of "<expr> IN (<select>)" */
};

/*
** The following are the meanings of bits in the Expr.flags field.
*/
................................................................................
  int nTab;            /* Number of previously allocated VDBE cursors */
  int nMem;            /* Number of memory cells used so far */
  int nSet;            /* Number of sets used so far */
  u32 cookieMask;      /* Bitmask of schema verified databases */
  int cookieValue[MAX_ATTACHED+2];  /* Values of cookies to verify */
  int cookieGoto;      /* Address of OP_Goto to cookie verifier subroutine */
  u32 writeMask;       /* Start a write transaction on these databases */
  u8 fillAgg;          /* If true, ignore the Expr.iAgg field. Normally false */


  /* 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[] */
................................................................................
void sqlite3Vacuum(Parse*, Token*);
int sqlite3RunVacuum(char**, sqlite3*);
char *sqlite3NameFromToken(Token*);
int sqlite3ExprCheck(Parse*, Expr*, int, int*);
int sqlite3ExprCompare(Expr*, Expr*);
int sqliteFuncId(Token*);
int sqlite3ExprResolveNames(NameContext *, Expr *);
int sqlite3ExprAnalyzeAggregates(NameContext*, Expr*);
Vdbe *sqlite3GetVdbe(Parse*);
void sqlite3Randomness(int, void*);
void sqlite3RollbackAll(sqlite3*);
void sqlite3CodeVerifySchema(Parse*, int);
void sqlite3BeginTransaction(Parse*, int);
void sqlite3CommitTransaction(Parse*);
void sqlite3RollbackTransaction(Parse*);

Changes to src/vdbe.c.

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402






4403
4404
4405
4406


4407
4408
4409
4410
4411
4412
4413
4414
4415
4416
4417
4418
4419
4420
4421
4422
4423
4424
4425
4426
4427
4428
4429
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.453 2005/02/05 12:48:48 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
  if( pFocus==0 ) goto no_mem;
  assert( i>=0 && i<p->pAgg->nMem );
  rc = sqlite3VdbeMemMove(&pFocus->aMem[i], pTos);
  pTos--;
  break;
}

/* Opcode: AggGet * P2 *
**
** Push a new entry onto the stack which is a copy of the P2-th field
** of the current aggregate.  Strings are not duplicated so
** string values will be ephemeral.






*/
case OP_AggGet: {
  AggElem *pFocus;
  int i = pOp->p2;


  pFocus = p->pAgg->pCurrent;
  if( pFocus==0 ){
    int res;
    if( sqlite3_malloc_failed ) goto no_mem;
    rc = sqlite3BtreeFirst(p->pAgg->pCsr, &res);
    if( rc!=SQLITE_OK ){
      return rc;
    }
    if( res!=0 ){
      rc = AggInsert(p->pAgg, "", 1);
      pFocus = p->pAgg->pCurrent;
    }else{
      rc = sqlite3BtreeData(p->pAgg->pCsr, 0, 4, (char *)&pFocus);
    }
  }
  assert( i>=0 && i<p->pAgg->nMem );
  pTos++;
  sqlite3VdbeMemShallowCopy(pTos, &pFocus->aMem[i], MEM_Ephem);
  if( pTos->flags&MEM_Str ){
    sqlite3VdbeChangeEncoding(pTos, db->enc);
  }
  break;
}







|







 







|




>
>
>
>
>
>




>
>
|



|




|
|

|


|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402
4403
4404
4405
4406
4407
4408
4409
4410
4411
4412
4413
4414
4415
4416
4417
4418
4419
4420
4421
4422
4423
4424
4425
4426
4427
4428
4429
4430
4431
4432
4433
4434
4435
4436
4437
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.454 2005/02/08 07:50:42 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
  if( pFocus==0 ) goto no_mem;
  assert( i>=0 && i<p->pAgg->nMem );
  rc = sqlite3VdbeMemMove(&pFocus->aMem[i], pTos);
  pTos--;
  break;
}

/* Opcode: AggGet P1 P2 *
**
** Push a new entry onto the stack which is a copy of the P2-th field
** of the current aggregate.  Strings are not duplicated so
** string values will be ephemeral.
**
** If P1 is zero, then the value is pulled out of the current aggregate
** in the current aggregate context. If P1 is greater than zero, then
** the value is taken from the P1th outer aggregate context. (i.e. if
** P1==1 then read from the aggregate context that will be restored
** by the next OP_AggContextPop opcode).
*/
case OP_AggGet: {
  AggElem *pFocus;
  int i = pOp->p2;
  Agg *pAgg = &p->pAgg[-pOp->p1];
  assert( pAgg>=p->apAgg );
  pFocus = pAgg->pCurrent;
  if( pFocus==0 ){
    int res;
    if( sqlite3_malloc_failed ) goto no_mem;
    rc = sqlite3BtreeFirst(pAgg->pCsr, &res);
    if( rc!=SQLITE_OK ){
      return rc;
    }
    if( res!=0 ){
      rc = AggInsert(pAgg, "", 1);
      pFocus = pAgg->pCurrent;
    }else{
      rc = sqlite3BtreeData(pAgg->pCsr, 0, 4, (char *)&pFocus);
    }
  }
  assert( i>=0 && i<pAgg->nMem );
  pTos++;
  sqlite3VdbeMemShallowCopy(pTos, &pFocus->aMem[i], MEM_Ephem);
  if( pTos->flags&MEM_Str ){
    sqlite3VdbeChangeEncoding(pTos, db->enc);
  }
  break;
}

Changes to test/subquery.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
211
212
213
214
215
216
217






























218
219
220
221
222
223
224
#    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 testing correlated subqueries
#
# $Id: subquery.test,v 1.4 2005/01/30 11:11:44 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !subquery {
  finish_test
................................................................................
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
  }
  execsql {
    SELECT (SELECT t1.a) FROM t1;
  }
} {1}































#------------------------------------------------------------------
# These tests - subquery-4.* - use the TCL statement cache to try 
# and expose bugs to do with re-using statements that have been 
# passed to sqlite3_reset().
#
# One problem was that VDBE memory cells were not being initialised







|







 







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







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
211
212
213
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
247
248
249
250
251
252
253
254
#    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 testing correlated subqueries
#
# $Id: subquery.test,v 1.5 2005/02/08 07:50:42 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !subquery {
  finish_test
................................................................................
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
  }
  execsql {
    SELECT (SELECT t1.a) FROM t1;
  }
} {1}

# Test Cases subquery-3.3.* test correlated subqueries where the
# parent query is an aggregate query. Ticket #1105 is an example
# of such a query.
#
do_test subquery-3.3.1 {
  execsql {
    SELECT a, (SELECT b) FROM t1 GROUP BY a;
  }
} {1 2}
do_test subquery-3.3.2 {
  catchsql {DROP TABLE t2}
  execsql {
    CREATE TABLE t2(c, d);
    INSERT INTO t2 VALUES(1, 'one');
    INSERT INTO t2 VALUES(2, 'two');
    SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a;
  }
} {1 one}
do_test subquery-3.3.3 {
  execsql {
    INSERT INTO t1 VALUES(2, 4);
    SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1;
  }
} {2 two}
do_test subquery-3.3.3 {
  execsql {
    SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
  }
} {1 one 2 two}

#------------------------------------------------------------------
# These tests - subquery-4.* - use the TCL statement cache to try 
# and expose bugs to do with re-using statements that have been 
# passed to sqlite3_reset().
#
# One problem was that VDBE memory cells were not being initialised