Overview
| SHA1 Hash: | 430bb59d798286a86c351de92c429345f016b3f0 |
|---|---|
| Date: | 2012-04-17 16:38:53 |
| User: | drh |
| Comment: | Improved handling of aggregate subqueries within an aggregate query. |
Tags And Properties
- branch=trunk inherited from [704b122e53]
- sym-trunk inherited from [704b122e53]
Changes
Changes to src/expr.c
3774 if( sqlite3ExprCompare(pA->pRight, pB->pRight) ) return 2; 3774 if( sqlite3ExprCompare(pA->pRight, pB->pRight) ) return 2; 3775 if( sqlite3ExprListCompare(pA->x.pList, pB->x.pList) ) return 2; 3775 if( sqlite3ExprListCompare(pA->x.pList, pB->x.pList) ) return 2; 3776 if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 2; 3776 if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 2; 3777 if( ExprHasProperty(pA, EP_IntValue) ){ 3777 if( ExprHasProperty(pA, EP_IntValue) ){ 3778 if( !ExprHasProperty(pB, EP_IntValue) || pA->u.iValue!=pB->u.iValue ){ 3778 if( !ExprHasProperty(pB, EP_IntValue) || pA->u.iValue!=pB->u.iValue ){ 3779 return 2; 3779 return 2; 3780 } 3780 } 3781 }else if( pA->op!=TK_COLUMN && pA->u.zToken ){ | 3781 }else if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){ 3782 if( ExprHasProperty(pB, EP_IntValue) || NEVER(pB->u.zToken==0) ) return 2; 3782 if( ExprHasProperty(pB, EP_IntValue) || NEVER(pB->u.zToken==0) ) return 2; 3783 if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ 3783 if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ 3784 return 2; 3784 return 2; 3785 } 3785 } 3786 } 3786 } 3787 if( (pA->flags & EP_ExpCollate)!=(pB->flags & EP_ExpCollate) ) return 1; 3787 if( (pA->flags & EP_ExpCollate)!=(pB->flags & EP_ExpCollate) ) return 1; 3788 if( (pA->flags & EP_ExpCollate)!=0 && pA->pColl!=pB->pColl ) return 2; 3788 if( (pA->flags & EP_ExpCollate)!=0 && pA->pColl!=pB->pColl ) return 2; ................................................................................................................................................................................ 3810 Expr *pExprA = pA->a[i].pExpr; 3810 Expr *pExprA = pA->a[i].pExpr; 3811 Expr *pExprB = pB->a[i].pExpr; 3811 Expr *pExprB = pB->a[i].pExpr; 3812 if( pA->a[i].sortOrder!=pB->a[i].sortOrder ) return 1; 3812 if( pA->a[i].sortOrder!=pB->a[i].sortOrder ) return 1; 3813 if( sqlite3ExprCompare(pExprA, pExprB) ) return 1; 3813 if( sqlite3ExprCompare(pExprA, pExprB) ) return 1; 3814 } 3814 } 3815 return 0; 3815 return 0; 3816 } 3816 } > 3817 > 3818 /* > 3819 ** This is the expression callback for sqlite3FunctionUsesOtherSrc(). > 3820 ** > 3821 ** Determine if an expression references any table other than one of the > 3822 ** tables in pWalker->u.pSrcList and abort if it does. > 3823 */ > 3824 static int exprUsesOtherSrc(Walker *pWalker, Expr *pExpr){ > 3825 if( pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN ){ > 3826 int i; > 3827 SrcList *pSrc = pWalker->u.pSrcList; > 3828 for(i=0; i<pSrc->nSrc; i++){ > 3829 if( pExpr->iTable==pSrc->a[i].iCursor ) return WRC_Continue; > 3830 } > 3831 return WRC_Abort; > 3832 }else{ > 3833 return WRC_Continue; > 3834 } > 3835 } > 3836 > 3837 /* > 3838 ** Determine if any of the arguments to the pExpr Function references > 3839 ** any SrcList other than pSrcList. Return true if they do. Return > 3840 ** false if pExpr has no argument or has only constant arguments or > 3841 ** only references tables named in pSrcList. > 3842 */ > 3843 static int sqlite3FunctionUsesOtherSrc(Expr *pExpr, SrcList *pSrcList){ > 3844 Walker w; > 3845 assert( pExpr->op==TK_AGG_FUNCTION ); > 3846 memset(&w, 0, sizeof(w)); > 3847 w.xExprCallback = exprUsesOtherSrc; > 3848 w.u.pSrcList = pSrcList; > 3849 if( sqlite3WalkExprList(&w, pExpr->x.pList)!=WRC_Continue ) return 1; > 3850 return 0; > 3851 } 3817 3852 3818 /* 3853 /* 3819 ** Add a new element to the pAggInfo->aCol[] array. Return the index of 3854 ** Add a new element to the pAggInfo->aCol[] array. Return the index of 3820 ** the new element. Return a negative number if malloc fails. 3855 ** the new element. Return a negative number if malloc fails. 3821 */ 3856 */ 3822 static int addAggInfoColumn(sqlite3 *db, AggInfo *pInfo){ 3857 static int addAggInfoColumn(sqlite3 *db, AggInfo *pInfo){ 3823 int i; 3858 int i; ................................................................................................................................................................................ 3926 break; 3961 break; 3927 } /* endif pExpr->iTable==pItem->iCursor */ 3962 } /* endif pExpr->iTable==pItem->iCursor */ 3928 } /* end loop over pSrcList */ 3963 } /* end loop over pSrcList */ 3929 } 3964 } 3930 return WRC_Prune; 3965 return WRC_Prune; 3931 } 3966 } 3932 case TK_AGG_FUNCTION: { 3967 case TK_AGG_FUNCTION: { 3933 /* The pNC->nDepth==0 test causes aggregate functions in subqueries | 3968 if( !sqlite3FunctionUsesOtherSrc(pExpr, pSrcList) ){ 3934 ** to be ignored */ < 3935 if( pNC->nDepth==0 ){ < 3936 /* Check to see if pExpr is a duplicate of another aggregate 3969 /* Check to see if pExpr is a duplicate of another aggregate 3937 ** function that is already in the pAggInfo structure 3970 ** function that is already in the pAggInfo structure 3938 */ 3971 */ 3939 struct AggInfo_func *pItem = pAggInfo->aFunc; 3972 struct AggInfo_func *pItem = pAggInfo->aFunc; 3940 for(i=0; i<pAggInfo->nFunc; i++, pItem++){ 3973 for(i=0; i<pAggInfo->nFunc; i++, pItem++){ 3941 if( sqlite3ExprCompare(pItem->pExpr, pExpr)==0 ){ 3974 if( sqlite3ExprCompare(pItem->pExpr, pExpr)==0 ){ 3942 break; 3975 break; ................................................................................................................................................................................ 3972 return WRC_Prune; 4005 return WRC_Prune; 3973 } 4006 } 3974 } 4007 } 3975 } 4008 } 3976 return WRC_Continue; 4009 return WRC_Continue; 3977 } 4010 } 3978 static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){ 4011 static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){ 3979 NameContext *pNC = pWalker->u.pNC; < 3980 if( pNC->nDepth==0 ){ < 3981 pNC->nDepth++; < 3982 sqlite3WalkSelect(pWalker, pSelect); < 3983 pNC->nDepth--; < 3984 return WRC_Prune; < 3985 }else{ < 3986 return WRC_Continue; | 4012 return WRC_Continue; 3987 } < 3988 } 4013 } 3989 4014 3990 /* 4015 /* 3991 ** Analyze the given expression looking for aggregate functions and 4016 ** Analyze the given expression looking for aggregate functions and 3992 ** for variables that need to be added to the pParse->aAgg[] array. 4017 ** for variables that need to be added to the pParse->aAgg[] array. 3993 ** Make additional entries to the pParse->aAgg[] array as necessary. 4018 ** Make additional entries to the pParse->aAgg[] array as necessary. 3994 ** 4019 ** 3995 ** This routine should only be called after the expression has been 4020 ** This routine should only be called after the expression has been 3996 ** analyzed by sqlite3ResolveExprNames(). 4021 ** analyzed by sqlite3ResolveExprNames(). 3997 */ 4022 */ 3998 void sqlite3ExprAnalyzeAggregates(NameContext *pNC, Expr *pExpr){ 4023 void sqlite3ExprAnalyzeAggregates(NameContext *pNC, Expr *pExpr){ 3999 Walker w; 4024 Walker w; > 4025 memset(&w, 0, sizeof(w)); 4000 w.xExprCallback = analyzeAggregate; 4026 w.xExprCallback = analyzeAggregate; 4001 w.xSelectCallback = analyzeAggregatesInSelect; 4027 w.xSelectCallback = analyzeAggregatesInSelect; 4002 w.u.pNC = pNC; 4028 w.u.pNC = pNC; 4003 assert( pNC->pSrcList!=0 ); 4029 assert( pNC->pSrcList!=0 ); 4004 sqlite3WalkExpr(&w, pExpr); 4030 sqlite3WalkExpr(&w, pExpr); 4005 } 4031 } 4006 4032
Changes to src/sqliteInt.h
2007 SrcList *pSrcList; /* One or more tables used to resolve names */ 2007 SrcList *pSrcList; /* One or more tables used to resolve names */ 2008 ExprList *pEList; /* Optional list of named expressions */ 2008 ExprList *pEList; /* Optional list of named expressions */ 2009 int nRef; /* Number of names resolved by this context */ 2009 int nRef; /* Number of names resolved by this context */ 2010 int nErr; /* Number of errors encountered while resolving names */ 2010 int nErr; /* Number of errors encountered while resolving names */ 2011 u8 allowAgg; /* Aggregate functions allowed here */ 2011 u8 allowAgg; /* Aggregate functions allowed here */ 2012 u8 hasAgg; /* True if aggregates are seen */ 2012 u8 hasAgg; /* True if aggregates are seen */ 2013 u8 isCheck; /* True if resolving names in a CHECK constraint */ 2013 u8 isCheck; /* True if resolving names in a CHECK constraint */ 2014 int nDepth; /* Depth of subquery recursion. 1 for no recursion */ < 2015 AggInfo *pAggInfo; /* Information about aggregates at this level */ 2014 AggInfo *pAggInfo; /* Information about aggregates at this level */ 2016 NameContext *pNext; /* Next outer name context. NULL for outermost */ 2015 NameContext *pNext; /* Next outer name context. NULL for outermost */ 2017 }; 2016 }; 2018 2017 2019 /* 2018 /* 2020 ** An instance of the following structure contains all information 2019 ** An instance of the following structure contains all information 2021 ** needed to generate code for a single SELECT statement. 2020 ** needed to generate code for a single SELECT statement. ................................................................................................................................................................................ 2473 struct Walker { 2472 struct Walker { 2474 int (*xExprCallback)(Walker*, Expr*); /* Callback for expressions */ 2473 int (*xExprCallback)(Walker*, Expr*); /* Callback for expressions */ 2475 int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ 2474 int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ 2476 Parse *pParse; /* Parser context. */ 2475 Parse *pParse; /* Parser context. */ 2477 union { /* Extra data for callback */ 2476 union { /* Extra data for callback */ 2478 NameContext *pNC; /* Naming context */ 2477 NameContext *pNC; /* Naming context */ 2479 int i; /* Integer value */ 2478 int i; /* Integer value */ > 2479 SrcList *pSrcList; /* FROM clause */ 2480 } u; 2480 } u; 2481 }; 2481 }; 2482 2482 2483 /* Forward declarations */ 2483 /* Forward declarations */ 2484 int sqlite3WalkExpr(Walker*, Expr*); 2484 int sqlite3WalkExpr(Walker*, Expr*); 2485 int sqlite3WalkExprList(Walker*, ExprList*); 2485 int sqlite3WalkExprList(Walker*, ExprList*); 2486 int sqlite3WalkSelect(Walker*, Select*); 2486 int sqlite3WalkSelect(Walker*, Select*);
Changes to test/subquery.test
326 } 326 } 327 } {1 one 2 two} 327 } {1 one 2 two} 328 do_test subquery-3.3.5 { 328 do_test subquery-3.3.5 { 329 execsql { 329 execsql { 330 SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1; 330 SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1; 331 } 331 } 332 } {1 1 2 1} 332 } {1 1 2 1} > 333 > 334 # The following tests check for aggregate subqueries in an aggregate > 335 # query. > 336 # > 337 do_test subquery-3.4.1 { > 338 execsql { > 339 CREATE TABLE t34(x,y); > 340 INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5); > 341 SELECT a.x, avg(a.y) > 342 FROM t34 AS a > 343 GROUP BY a.x > 344 HAVING NOT EXISTS( SELECT b.x, avg(b.y) > 345 FROM t34 AS b > 346 GROUP BY b.x > 347 HAVING avg(a.y) > avg(b.y)); > 348 } > 349 } {107 4.0} > 350 do_test subquery-3.4.2 { > 351 execsql { > 352 SELECT a.x, avg(a.y) AS avg1 > 353 FROM t34 AS a > 354 GROUP BY a.x > 355 HAVING NOT EXISTS( SELECT b.x, avg(b.y) AS avg2 > 356 FROM t34 AS b > 357 GROUP BY b.x > 358 HAVING avg1 > avg2); > 359 } > 360 } {107 4.0} > 361 do_test subquery-3.4.3 { > 362 execsql { > 363 SELECT > 364 a.x, > 365 avg(a.y), > 366 NOT EXISTS ( SELECT b.x, avg(b.y) > 367 FROM t34 AS b > 368 GROUP BY b.x > 369 HAVING avg(a.y) > avg(b.y)), > 370 EXISTS ( SELECT c.x, avg(c.y) > 371 FROM t34 AS c > 372 GROUP BY c.x > 373 HAVING avg(a.y) > avg(c.y)) > 374 FROM t34 AS a > 375 GROUP BY a.x > 376 ORDER BY a.x; > 377 } > 378 } {106 4.5 0 1 107 4.0 1 0} > 379 333 380 334 #------------------------------------------------------------------ 381 #------------------------------------------------------------------ 335 # These tests - subquery-4.* - use the TCL statement cache to try 382 # These tests - subquery-4.* - use the TCL statement cache to try 336 # and expose bugs to do with re-using statements that have been 383 # and expose bugs to do with re-using statements that have been 337 # passed to sqlite3_reset(). 384 # passed to sqlite3_reset(). 338 # 385 # 339 # One problem was that VDBE memory cells were not being initialised 386 # One problem was that VDBE memory cells were not being initialised