SQLite

** An instance of the following structure is used by the tree walker
** to count references to table columns in the arguments of an 
** aggregate function, in order to implement the
** sqlite3FunctionThisSrc() routine.
*/
struct SrcCount {
  SrcList *pSrc;   /* One particular FROM clause in a nested query */
  int iSrcInner;   /* Smallest cursor number in this context */
  int nThis;       /* Number of references to columns in pSrcList */
  int nOther;      /* Number of references to columns in other FROM clauses */
};

/*
** xSelect callback for sqlite3FunctionUsesThisSrc(). If this is the first
** SELECT with a FROM clause encountered during this iteration, set
** SrcCount.iSrcInner to the cursor number of the leftmost object in
** the FROM cause.
*/
static int selectSrcCount(Walker *pWalker, Select *pSel){
  struct SrcCount *p = pWalker->u.pSrcCount;
  if( p->iSrcInner==0x7FFFFFFF && ALWAYS(pSel->pSrc) && pSel->pSrc->nSrc ){
    pWalker->u.pSrcCount->iSrcInner = pSel->pSrc->a[0].iCursor;
  }
  return WRC_Continue;
}

/*
** Count the number of references to columns.
*/
static int exprSrcCount(Walker *pWalker, Expr *pExpr){
  /* There was once a NEVER() on the second term on the grounds that
  ** sqlite3FunctionUsesThisSrc() was always called before 

    SrcList *pSrc = p->pSrc;
    int nSrc = pSrc ? pSrc->nSrc : 0;
    for(i=0; i<nSrc; i++){
      if( pExpr->iTable==pSrc->a[i].iCursor ) break;
    }
    if( i<nSrc ){
      p->nThis++;
    }else if( pExpr->iTable<p->iSrcInner ){
      /* In a well-formed parse tree (no name resolution errors),
      ** TK_COLUMN nodes with smaller Expr.iTable values are in an
      ** outer context.  Those are the only ones to count as "other" */
      p->nOther++;
    }
  }
  return WRC_Continue;

*/
int sqlite3FunctionUsesThisSrc(Expr *pExpr, SrcList *pSrcList){
  Walker w;
  struct SrcCount cnt;
  assert( pExpr->op==TK_AGG_FUNCTION );
  memset(&w, 0, sizeof(w));
  w.xExprCallback = exprSrcCount;
  w.xSelectCallback = selectSrcCount;
  w.u.pSrcCount = &cnt;
  cnt.pSrc = pSrcList;
  cnt.iSrcInner = (pSrcList&&pSrcList->nSrc)?pSrcList->a[0].iCursor:0x7FFFFFFF;
  cnt.nThis = 0;
  cnt.nOther = 0;
  sqlite3WalkExprList(&w, pExpr->x.pList);
#ifndef SQLITE_OMIT_WINDOWFUNC
  if( ExprHasProperty(pExpr, EP_WinFunc) ){
    sqlite3WalkExpr(&w, pExpr->y.pWin->pFilter);
  }

# This file implements tests for processing aggregate queries with 
# subqueries in which the subqueries hold the aggregate functions
# or in which the subqueries are themselves aggregate queries
#

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

do_test aggnested-1.1 {
  db eval {
    CREATE TABLE t1(a1 INTEGER);
    INSERT INTO t1 VALUES(1), (2), (3);
    CREATE TABLE t2(b1 INTEGER);
    INSERT INTO t2 VALUES(4), (5);

  CREATE TABLE ty(y INT);
  INSERT INTO ty VALUES(91),(92),(93);
  SELECT min((SELECT count(y) FROM ty)) FROM tx;
} {3}
do_execsql_test aggnested-4.4 {
  SELECT max((SELECT a FROM (SELECT count(*) AS a FROM ty) AS s)) FROM tx;
} {3}

#--------------------------------------------------------------------------
#
reset_db
do_execsql_test 5.0 {
  CREATE TABLE x1(a, b);
  INSERT INTO x1 VALUES(1, 2);
  CREATE TABLE x2(x);
  INSERT INTO x2 VALUES(NULL), (NULL), (NULL);
}

# At one point, aggregate "total()" in the query below was being processed
# as part of the outer SELECT, not as part of the sub-select with no FROM
# clause.
do_execsql_test 5.1 {
  SELECT ( SELECT total( (SELECT b FROM x1) ) ) FROM x2;
} {2.0 2.0 2.0}

do_execsql_test 5.2 {
  SELECT ( SELECT total( (SELECT 2 FROM x1) ) ) FROM x2;
} {2.0 2.0 2.0}

do_execsql_test 5.3 {
  CREATE TABLE t1(a);
  CREATE TABLE t2(b);
}

do_execsql_test 5.4 {
  SELECT(
    SELECT max(b) LIMIT (
      SELECT total( (SELECT a FROM t1) )
    )
  )
  FROM t2;
} {{}}

do_execsql_test 5.5 {
  CREATE TABLE a(b);
  WITH c AS(SELECT a)
    SELECT(SELECT(SELECT group_concat(b, b)
          LIMIT(SELECT 0.100000 *
            AVG(DISTINCT(SELECT 0 FROM a ORDER BY b, b, b))))
        FROM a GROUP BY b,
        b, b) FROM a EXCEPT SELECT b FROM a ORDER BY b,
    b, b;
}


 

finish_test

    SELECT count() OVER() FROM (SELECT 0) 
    ORDER BY 1
  ;
}

do_catchsql_test 8.3 {
  SELECT min( max((SELECT x FROM v1)) ) OVER()
} {0 0}

do_execsql_test 8.4 {
  SELECT(
      SELECT x UNION 
      SELECT sum( avg((SELECT x FROM v1)) ) OVER()
  )
  FROM v1;
} {0.0 0.0}

finish_test