/ Check-in [f3dd1faf]
Login

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.

3961
3962
3963
3964
3965
3966
3967

3968

3969
3970
3971
3972
3973
3974
3975
....
3998
3999
4000
4001
4002
4003
4004
4005
4006

4007
4008
4009
4010
4011
4012
4013
            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);







>
|
>







 







<

>







3961
3962
3963
3964
3965
3966
3967
3968
3969
3970
3971
3972
3973
3974
3975
3976
3977
....
4000
4001
4002
4003
4004
4005
4006

4007
4008
4009
4010
4011
4012
4013
4014
4015
            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.

4136
4137
4138
4139
4140
4141
4142

4143

4144
4145
4146
4147
4148
4149
4150
    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 ){







>

>







4136
4137
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
    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.

2015
2016
2017
2018
2019
2020
2021

2022
2023
2024
2025
2026
2027
2028

/*
** 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







>







2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029

/*
** 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.

372
373
374
375
376
377
378






































379
380
381
382
383
384
385
                  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().
#







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







372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
                  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().
#