SQLite: Check-in [f3dd1faf]

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

Overview

Comment:	Candidate fix for the nested aggregate query problem of ticket [c2ad16f997ee9c8e].
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| nested-agg
Files:	files \| file ages \| folders
SHA1:	f3dd1fafd4718558de1f06139419a8c560d727f5
User & Date:	drh 2012-05-21 20:13:39

Context

2012-05-21
21:20		Mark and always-true conditional as such. Add the fuzz-oss1.test test module. Closed-Leaf check-in: bdc01fd0 user: drh tags: nested-agg
20:13		Candidate fix for the nested aggregate query problem of ticket [c2ad16f997ee9c8e]. check-in: f3dd1faf user: drh tags: nested-agg
19:11		Convert the NameContext object from using u8 booleans to using individual bits in a single u8 as its booleans. This change might become a basis for a fix for [c2ad16f997ee9c]. check-in: 72226096 user: drh tags: nested-agg

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

            break;
          } /* endif pExpr->iTable==pItem->iCursor */
        } /* end loop over pSrcList */
      }
      return WRC_Prune;
    }
    case TK_AGG_FUNCTION: {

      if( !sqlite3FunctionUsesOtherSrc(pExpr, pSrcList) ){

        /* Check to see if pExpr is a duplicate of another aggregate 
        ** function that is already in the pAggInfo structure
        */
        struct AggInfo_func *pItem = pAggInfo->aFunc;
        for(i=0; i<pAggInfo->nFunc; i++, pItem++){
          if( sqlite3ExprCompare(pItem->pExpr, pExpr)==0 ){
            break;
................................................................................
        }
        /* Make pExpr point to the appropriate pAggInfo->aFunc[] entry
        */
        assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) );
        ExprSetIrreducible(pExpr);
        pExpr->iAgg = (i16)i;
        pExpr->pAggInfo = pAggInfo;
        return WRC_Prune;
      }

    }
  }
  return WRC_Continue;
}
static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){
  UNUSED_PARAMETER(pWalker);
  UNUSED_PARAMETER(pSelect);

            break;
          } /* endif pExpr->iTable==pItem->iCursor */
        } /* end loop over pSrcList */
      }
      return WRC_Prune;
    }
    case TK_AGG_FUNCTION: {
      if( (pNC->ncFlags & NC_InAggFunc)==0
       && !sqlite3FunctionUsesOtherSrc(pExpr, pSrcList)
      ){
        /* Check to see if pExpr is a duplicate of another aggregate 
        ** function that is already in the pAggInfo structure
        */
        struct AggInfo_func *pItem = pAggInfo->aFunc;
        for(i=0; i<pAggInfo->nFunc; i++, pItem++){
          if( sqlite3ExprCompare(pItem->pExpr, pExpr)==0 ){
            break;
................................................................................
        }
        /* Make pExpr point to the appropriate pAggInfo->aFunc[] entry
        */
        assert( !ExprHasAnyProperty(pExpr, EP_TokenOnly|EP_Reduced) );
        ExprSetIrreducible(pExpr);
        pExpr->iAgg = (i16)i;
        pExpr->pAggInfo = pAggInfo;

      }
      return WRC_Prune;
    }
  }
  return WRC_Continue;
}
static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){
  UNUSED_PARAMETER(pWalker);
  UNUSED_PARAMETER(pSelect);

Changes to src/select.c.

    sqlite3ExprAnalyzeAggList(&sNC, pOrderBy);
    if( pHaving ){
      sqlite3ExprAnalyzeAggregates(&sNC, pHaving);
    }
    sAggInfo.nAccumulator = sAggInfo.nColumn;
    for(i=0; i<sAggInfo.nFunc; i++){
      assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) );

      sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->x.pList);

    }
    if( db->mallocFailed ) goto select_end;

    /* Processing for aggregates with GROUP BY is very different and
    ** much more complex than aggregates without a GROUP BY.
    */
    if( pGroupBy ){








>

>

    sqlite3ExprAnalyzeAggList(&sNC, pOrderBy);
    if( pHaving ){
      sqlite3ExprAnalyzeAggregates(&sNC, pHaving);
    }
    sAggInfo.nAccumulator = sAggInfo.nColumn;
    for(i=0; i<sAggInfo.nFunc; i++){
      assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) );
      sNC.ncFlags |= NC_InAggFunc;
      sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->x.pList);
      sNC.ncFlags &= ~NC_InAggFunc;
    }
    if( db->mallocFailed ) goto select_end;

    /* Processing for aggregates with GROUP BY is very different and
    ** much more complex than aggregates without a GROUP BY.
    */
    if( pGroupBy ){

Changes to src/sqliteInt.h.


/*
** Allowed values for the NameContext, ncFlags field.
*/
#define NC_AllowAgg  0x01    /* Aggregate functions are allowed here */
#define NC_HasAgg    0x02    /* One or more aggregate functions seen */
#define NC_IsCheck   0x04    /* True if resolving names in a CHECK constraint */


/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
** If there is a LIMIT clause, the parser sets nLimit to the value of the


/*
** Allowed values for the NameContext, ncFlags field.
*/
#define NC_AllowAgg  0x01    /* Aggregate functions are allowed here */
#define NC_HasAgg    0x02    /* One or more aggregate functions seen */
#define NC_IsCheck   0x04    /* True if resolving names in a CHECK constraint */
#define NC_InAggFunc 0x08    /* True if analyzing arguments to an agg func */

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
** If there is a LIMIT clause, the parser sets nLimit to the value of the

Changes to test/subquery.test.

                  GROUP BY c.x
                 HAVING avg(a.y) > avg(c.y))
      FROM t34 AS a
     GROUP BY a.x
     ORDER BY a.x;
  }
} {106 4.5 0 1 107 4.0 1 0}








































#------------------------------------------------------------------
# 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().
#








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

                  GROUP BY c.x
                 HAVING avg(a.y) > avg(c.y))
      FROM t34 AS a
     GROUP BY a.x
     ORDER BY a.x;
  }
} {106 4.5 0 1 107 4.0 1 0}

do_test subquery-3.5.1 {
  execsql {
    CREATE TABLE t35a(x); INSERT INTO t35a VALUES(1),(2),(3);
    CREATE TABLE t35b(y); INSERT INTO t35b VALUES(98), (99);
    SELECT max((SELECT avg(y) FROM t35b)) FROM t35a;
  }
} {98.5}
do_test subquery-3.5.2 {
  execsql {
    SELECT max((SELECT count(y) FROM t35b)) FROM t35a;
  }
} {2}
do_test subquery-3.5.3 {
  execsql {
    SELECT max((SELECT count() FROM t35b)) FROM t35a;
  }
} {2}
do_test subquery-3.5.4 {
  catchsql {
    SELECT max((SELECT count(x) FROM t35b)) FROM t35a;
  }
} {1 {misuse of aggregate: count()}}
do_test subquery-3.5.5 {
  catchsql {
    SELECT max((SELECT count(x) FROM t35b)) FROM t35a;
  }
} {1 {misuse of aggregate: count()}}
do_test subquery-3.5.6 {
  catchsql {
    SELECT max((SELECT a FROM (SELECT count(x) AS a FROM t35b))) FROM t35a;
  }
} {1 {misuse of aggregate: count()}}
do_test subquery-3.5.7 {
  execsql {
    SELECT max((SELECT a FROM (SELECT count(y) AS a FROM t35b))) FROM t35a;
  }
} {2}


#------------------------------------------------------------------
# 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().
#