Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch nested-agg Excluding Merge-Ins
This is equivalent to a diff from b1dbf490 to 00b1dc71
2012-08-24
| ||
01:07 | Merge the nested aggregate query enhancements into trunk. (check-in: d4cd6017 user: drh tags: trunk) | |
2012-08-23
| ||
22:45 | Merge changes for the new sqlite3_win32_set_directory API to trunk. (check-in: 20f184f2 user: mistachkin tags: trunk) | |
19:46 | Add test cases and fix bugs associated with the previous check-in enhancements to nested aggregate subquery processing. (Closed-Leaf check-in: 00b1dc71 user: drh tags: nested-agg) | |
16:18 | Further improvements to the processing of nested aggregate queries. (check-in: 3c3ffa90 user: drh tags: nested-agg) | |
2012-08-22
| ||
00:39 | Modify the MSVC makefile to make it easier to select the heap subsystem to use. (check-in: b1dbf490 user: mistachkin tags: trunk) | |
2012-08-21
| ||
17:44 | Update the spellfix virtual table so that all OOM errors are reported out to the application. (check-in: 573770f5 user: drh tags: trunk) | |
Changes to src/expr.c.
︙ | ︙ | |||
3119 3120 3121 3122 3123 3124 3125 | case TK_FUNCTION: { ExprList *pFarg; /* List of function arguments */ if( ExprHasAnyProperty(pExpr, EP_TokenOnly) ){ pFarg = 0; }else{ pFarg = pExpr->x.pList; } | > | < > > | > | 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 | case TK_FUNCTION: { ExprList *pFarg; /* List of function arguments */ if( ExprHasAnyProperty(pExpr, EP_TokenOnly) ){ pFarg = 0; }else{ pFarg = pExpr->x.pList; } if( op==TK_AGG_FUNCTION ){ sqlite3ExplainPrintf(pOut, "AGG_FUNCTION%d:%s(", pExpr->op2, pExpr->u.zToken); }else{ sqlite3ExplainPrintf(pOut, "FUNCTION:%s(", pExpr->u.zToken); } if( pFarg ){ sqlite3ExplainExprList(pOut, pFarg); } sqlite3ExplainPrintf(pOut, ")"); break; } #ifndef SQLITE_OMIT_SUBQUERY |
︙ | ︙ | |||
3812 3813 3814 3815 3816 3817 3818 | if( pA->a[i].sortOrder!=pB->a[i].sortOrder ) return 1; if( sqlite3ExprCompare(pExprA, pExprB) ) return 1; } return 0; } /* | > > > | | > > > > > | < > > | > | | < > > | < > | | > | > | | | | | > | > | > > | | | 3815 3816 3817 3818 3819 3820 3821 3822 3823 3824 3825 3826 3827 3828 3829 3830 3831 3832 3833 3834 3835 3836 3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 3847 3848 3849 3850 3851 3852 3853 3854 3855 3856 3857 3858 3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 | if( pA->a[i].sortOrder!=pB->a[i].sortOrder ) return 1; if( sqlite3ExprCompare(pExprA, pExprB) ) return 1; } return 0; } /* ** 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 nThis; /* Number of references to columns in pSrcList */ int nOther; /* Number of references to columns in other FROM clauses */ }; /* ** Count the number of references to columns. */ static int exprSrcCount(Walker *pWalker, Expr *pExpr){ if( pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN ){ int i; struct SrcCount *p = pWalker->u.pSrcCount; SrcList *pSrc = p->pSrc; for(i=0; i<pSrc->nSrc; i++){ if( pExpr->iTable==pSrc->a[i].iCursor ) break; } if( i<pSrc->nSrc ){ p->nThis++; }else{ p->nOther++; } } return WRC_Continue; } /* ** Determine if any of the arguments to the pExpr Function reference ** pSrcList. Return true if they do. Also return true if the function ** has no arguments or has only constant arguments. Return false if pExpr ** references columns but not columns of tables found in pSrcList. */ 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.u.pSrcCount = &cnt; cnt.pSrc = pSrcList; cnt.nThis = 0; cnt.nOther = 0; sqlite3WalkExprList(&w, pExpr->x.pList); return cnt.nThis>0 || cnt.nOther==0; } /* ** Add a new element to the pAggInfo->aCol[] array. Return the index of ** the new element. Return a negative number if malloc fails. */ static int addAggInfoColumn(sqlite3 *db, AggInfo *pInfo){ |
︙ | ︙ | |||
3962 3963 3964 3965 3966 3967 3968 | } /* endif pExpr->iTable==pItem->iCursor */ } /* end loop over pSrcList */ } return WRC_Prune; } case TK_AGG_FUNCTION: { if( (pNC->ncFlags & NC_InAggFunc)==0 | | | 3982 3983 3984 3985 3986 3987 3988 3989 3990 3991 3992 3993 3994 3995 3996 | } /* endif pExpr->iTable==pItem->iCursor */ } /* end loop over pSrcList */ } return WRC_Prune; } case TK_AGG_FUNCTION: { if( (pNC->ncFlags & NC_InAggFunc)==0 && pWalker->walkerDepth==pExpr->op2 ){ /* 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 ){ |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This file contains routines used for walking the parser tree and ** resolve all identifiers by associating them with a particular ** table and column. */ #include "sqliteInt.h" #include <stdlib.h> #include <string.h> /* ** Turn the pExpr expression into an alias for the iCol-th column of the ** result set in pEList. ** ** If the result set column is a simple column reference, then this routine ** makes an exact copy. But for any other kind of expression, this | > > > > > > > > > > > > > > > > > > > > > > > | 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | ** This file contains routines used for walking the parser tree and ** resolve all identifiers by associating them with a particular ** table and column. */ #include "sqliteInt.h" #include <stdlib.h> #include <string.h> /* ** Walk the expression tree pExpr and increase the aggregate function ** depth (the Expr.op2 field) by N on every TK_AGG_FUNCTION node. ** This needs to occur when copying a TK_AGG_FUNCTION node from an ** outer query into an inner subquery. ** ** incrAggFunctionDepth(pExpr,n) is the main routine. incrAggDepth(..) ** is a helper function - a callback for the tree walker. */ static int incrAggDepth(Walker *pWalker, Expr *pExpr){ if( pExpr->op==TK_AGG_FUNCTION ) pExpr->op2 += pWalker->u.i; return WRC_Continue; } static void incrAggFunctionDepth(Expr *pExpr, int N){ if( N>0 ){ Walker w; memset(&w, 0, sizeof(w)); w.xExprCallback = incrAggDepth; w.u.i = N; sqlite3WalkExpr(&w, pExpr); } } /* ** Turn the pExpr expression into an alias for the iCol-th column of the ** result set in pEList. ** ** If the result set column is a simple column reference, then this routine ** makes an exact copy. But for any other kind of expression, this |
︙ | ︙ | |||
40 41 42 43 44 45 46 47 48 49 50 51 52 | ** Is equivalent to: ** ** SELECT random()%5 AS x, count(*) FROM tab GROUP BY random()%5 ** ** The result of random()%5 in the GROUP BY clause is probably different ** from the result in the result-set. We might fix this someday. Or ** then again, we might not... */ static void resolveAlias( Parse *pParse, /* Parsing context */ ExprList *pEList, /* A result set */ int iCol, /* A column in the result set. 0..pEList->nExpr-1 */ Expr *pExpr, /* Transform this into an alias to the result set */ | > > > > > > | > > | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | ** Is equivalent to: ** ** SELECT random()%5 AS x, count(*) FROM tab GROUP BY random()%5 ** ** The result of random()%5 in the GROUP BY clause is probably different ** from the result in the result-set. We might fix this someday. Or ** then again, we might not... ** ** The nSubquery parameter specifies how many levels of subquery the ** alias is removed from the original expression. The usually value is ** zero but it might be more if the alias is contained within a subquery ** of the original expression. The Expr.op2 field of TK_AGG_FUNCTION ** structures must be increased by the nSubquery amount. */ static void resolveAlias( Parse *pParse, /* Parsing context */ ExprList *pEList, /* A result set */ int iCol, /* A column in the result set. 0..pEList->nExpr-1 */ Expr *pExpr, /* Transform this into an alias to the result set */ const char *zType, /* "GROUP" or "ORDER" or "" */ int nSubquery /* Number of subqueries that the label is moving */ ){ Expr *pOrig; /* The iCol-th column of the result set */ Expr *pDup; /* Copy of pOrig */ sqlite3 *db; /* The database connection */ assert( iCol>=0 && iCol<pEList->nExpr ); pOrig = pEList->a[iCol].pExpr; assert( pOrig!=0 ); assert( pOrig->flags & EP_Resolved ); db = pParse->db; if( pOrig->op!=TK_COLUMN && zType[0]!='G' ){ pDup = sqlite3ExprDup(db, pOrig, 0); incrAggFunctionDepth(pDup, nSubquery); pDup = sqlite3PExpr(pParse, TK_AS, pDup, 0, 0); if( pDup==0 ) return; if( pEList->a[iCol].iAlias==0 ){ pEList->a[iCol].iAlias = (u16)(++pParse->nAlias); } pDup->iTable = pEList->a[iCol].iAlias; }else if( ExprHasProperty(pOrig, EP_IntValue) || pOrig->u.zToken==0 ){ |
︙ | ︙ | |||
147 148 149 150 151 152 153 | Parse *pParse, /* The parsing context */ const char *zDb, /* Name of the database containing table, or NULL */ const char *zTab, /* Name of table containing column, or NULL */ const char *zCol, /* Name of the column. */ NameContext *pNC, /* The name context used to resolve the name */ Expr *pExpr /* Make this EXPR node point to the selected column */ ){ | | > | 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 | Parse *pParse, /* The parsing context */ const char *zDb, /* Name of the database containing table, or NULL */ const char *zTab, /* Name of table containing column, or NULL */ const char *zCol, /* Name of the column. */ NameContext *pNC, /* The name context used to resolve the name */ Expr *pExpr /* Make this EXPR node point to the selected column */ ){ int i, j; /* Loop counters */ int cnt = 0; /* Number of matching column names */ int cntTab = 0; /* Number of matching table names */ int nSubquery = 0; /* How many levels of subquery */ sqlite3 *db = pParse->db; /* The database connection */ struct SrcList_item *pItem; /* Use for looping over pSrcList items */ struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ int isTrigger = 0; |
︙ | ︙ | |||
311 312 313 314 315 316 317 | assert( pExpr->x.pList==0 ); assert( pExpr->x.pSelect==0 ); pOrig = pEList->a[j].pExpr; if( (pNC->ncFlags&NC_AllowAgg)==0 && ExprHasProperty(pOrig, EP_Agg) ){ sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs); return WRC_Abort; } | | > | 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 | assert( pExpr->x.pList==0 ); assert( pExpr->x.pSelect==0 ); pOrig = pEList->a[j].pExpr; if( (pNC->ncFlags&NC_AllowAgg)==0 && ExprHasProperty(pOrig, EP_Agg) ){ sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs); return WRC_Abort; } resolveAlias(pParse, pEList, j, pExpr, "", nSubquery); cnt = 1; pMatch = 0; assert( zTab==0 && zDb==0 ); goto lookupname_end; } } } /* Advance to the next name context. The loop will exit when either ** we have a match (cnt>0) or when we run out of name contexts. */ if( cnt==0 ){ pNC = pNC->pNext; nSubquery++; } } /* ** If X and Y are NULL (in other words if only the column name Z is ** supplied) and the value of Z is enclosed in double-quotes, then ** Z is a string literal if it doesn't match any column names. In that |
︙ | ︙ | |||
564 565 566 567 568 569 570 571 572 | sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId); pNC->nErr++; }else if( wrong_num_args ){ sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()", nId, zId); pNC->nErr++; } if( is_agg ){ pExpr->op = TK_AGG_FUNCTION; | > > > > > > | | | < | > | 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 | sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId); pNC->nErr++; }else if( wrong_num_args ){ sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()", nId, zId); pNC->nErr++; } if( is_agg ) pNC->ncFlags &= ~NC_AllowAgg; sqlite3WalkExprList(pWalker, pList); if( is_agg ){ NameContext *pNC2 = pNC; pExpr->op = TK_AGG_FUNCTION; pExpr->op2 = 0; while( pNC2 && !sqlite3FunctionUsesThisSrc(pExpr, pNC2->pSrcList) ){ pExpr->op2++; pNC2 = pNC2->pNext; } if( pNC2 ) pNC2->ncFlags |= NC_HasAgg; pNC->ncFlags |= NC_AllowAgg; } /* FIX ME: Compute pExpr->affinity based on the expected return ** type of the function */ return WRC_Prune; } #ifndef SQLITE_OMIT_SUBQUERY case TK_SELECT: |
︙ | ︙ | |||
849 850 851 852 853 854 855 | assert( pEList!=0 ); /* sqlite3SelectNew() guarantees this */ for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){ if( pItem->iOrderByCol ){ if( pItem->iOrderByCol>pEList->nExpr ){ resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr); return 1; } | | | 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 | assert( pEList!=0 ); /* sqlite3SelectNew() guarantees this */ for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){ if( pItem->iOrderByCol ){ if( pItem->iOrderByCol>pEList->nExpr ){ resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr); return 1; } resolveAlias(pParse, pEList, pItem->iOrderByCol-1, pItem->pExpr, zType,0); } } return 0; } /* ** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect. |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
3517 3518 3519 3520 3521 3522 3523 | w.pParse = pParse; sqlite3WalkSelect(&w, pSelect); #endif } /* | | | 3517 3518 3519 3520 3521 3522 3523 3524 3525 3526 3527 3528 3529 3530 3531 | w.pParse = pParse; sqlite3WalkSelect(&w, pSelect); #endif } /* ** This routine sets up a SELECT statement for processing. The ** following is accomplished: ** ** * VDBE Cursor numbers are assigned to all FROM-clause terms. ** * Ephemeral Table objects are created for all FROM-clause subqueries. ** * ON and USING clauses are shifted into WHERE statements ** * Wildcards "*" and "TABLE.*" in result sets are expanded. ** * Identifiers in expression are matched to tables. |
︙ | ︙ | |||
3549 3550 3551 3552 3553 3554 3555 | } /* ** Reset the aggregate accumulator. ** ** The aggregate accumulator is a set of memory cells that hold ** intermediate results while calculating an aggregate. This | | > | 3549 3550 3551 3552 3553 3554 3555 3556 3557 3558 3559 3560 3561 3562 3563 3564 | } /* ** Reset the aggregate accumulator. ** ** The aggregate accumulator is a set of memory cells that hold ** intermediate results while calculating an aggregate. This ** routine generates code that stores NULLs in all of those memory ** cells. */ static void resetAccumulator(Parse *pParse, AggInfo *pAggInfo){ Vdbe *v = pParse->pVdbe; int i; struct AggInfo_func *pFunc; if( pAggInfo->nFunc+pAggInfo->nColumn==0 ){ return; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1686 1687 1688 1689 1690 1691 1692 | ** TK_REGISTER: register number ** TK_TRIGGER: 1 -> new, 0 -> old */ ynVar iColumn; /* TK_COLUMN: column index. -1 for rowid. ** TK_VARIABLE: variable number (always >= 1). */ i16 iAgg; /* Which entry in pAggInfo->aCol[] or ->aFunc[] */ i16 iRightJoinTable; /* If EP_FromJoin, the right table of the join */ u8 flags2; /* Second set of flags. EP2_... */ | | | > | 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 | ** TK_REGISTER: register number ** TK_TRIGGER: 1 -> new, 0 -> old */ ynVar iColumn; /* TK_COLUMN: column index. -1 for rowid. ** TK_VARIABLE: variable number (always >= 1). */ i16 iAgg; /* Which entry in pAggInfo->aCol[] or ->aFunc[] */ i16 iRightJoinTable; /* If EP_FromJoin, the right table of the join */ u8 flags2; /* Second set of flags. EP2_... */ u8 op2; /* TK_REGISTER: original value of Expr.op ** TK_COLUMN: the value of p5 for OP_Column ** TK_AGG_FUNCTION: nesting depth */ AggInfo *pAggInfo; /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */ Table *pTab; /* Table for TK_COLUMN expressions. */ #if SQLITE_MAX_EXPR_DEPTH>0 int nHeight; /* Height of the tree headed by this node */ #endif }; |
︙ | ︙ | |||
2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 | /* ** Context pointer passed down through the tree-walk. */ struct Walker { int (*xExprCallback)(Walker*, Expr*); /* Callback for expressions */ int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ Parse *pParse; /* Parser context. */ union { /* Extra data for callback */ NameContext *pNC; /* Naming context */ int i; /* Integer value */ SrcList *pSrcList; /* FROM clause */ } u; }; /* Forward declarations */ int sqlite3WalkExpr(Walker*, Expr*); int sqlite3WalkExprList(Walker*, ExprList*); int sqlite3WalkSelect(Walker*, Select*); | > > | 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 | /* ** Context pointer passed down through the tree-walk. */ struct Walker { int (*xExprCallback)(Walker*, Expr*); /* Callback for expressions */ int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ Parse *pParse; /* Parser context. */ int walkerDepth; /* Number of subqueries */ union { /* Extra data for callback */ NameContext *pNC; /* Naming context */ int i; /* Integer value */ SrcList *pSrcList; /* FROM clause */ struct SrcCount *pSrcCount; /* Counting column references */ } u; }; /* Forward declarations */ int sqlite3WalkExpr(Walker*, Expr*); int sqlite3WalkExprList(Walker*, ExprList*); int sqlite3WalkSelect(Walker*, Select*); |
︙ | ︙ | |||
2831 2832 2833 2834 2835 2836 2837 2838 2839 2840 2841 2842 2843 2844 | void sqlite3Vacuum(Parse*); int sqlite3RunVacuum(char**, sqlite3*); char *sqlite3NameFromToken(sqlite3*, Token*); int sqlite3ExprCompare(Expr*, Expr*); int sqlite3ExprListCompare(ExprList*, ExprList*); void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*); void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*); Vdbe *sqlite3GetVdbe(Parse*); void sqlite3PrngSaveState(void); void sqlite3PrngRestoreState(void); void sqlite3PrngResetState(void); void sqlite3RollbackAll(sqlite3*,int); void sqlite3CodeVerifySchema(Parse*, int); void sqlite3CodeVerifyNamedSchema(Parse*, const char *zDb); | > | 2834 2835 2836 2837 2838 2839 2840 2841 2842 2843 2844 2845 2846 2847 2848 | void sqlite3Vacuum(Parse*); int sqlite3RunVacuum(char**, sqlite3*); char *sqlite3NameFromToken(sqlite3*, Token*); int sqlite3ExprCompare(Expr*, Expr*); int sqlite3ExprListCompare(ExprList*, ExprList*); void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*); void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*); int sqlite3FunctionUsesThisSrc(Expr*, SrcList*); Vdbe *sqlite3GetVdbe(Parse*); void sqlite3PrngSaveState(void); void sqlite3PrngRestoreState(void); void sqlite3PrngResetState(void); void sqlite3RollbackAll(sqlite3*,int); void sqlite3CodeVerifySchema(Parse*, int); void sqlite3CodeVerifyNamedSchema(Parse*, const char *zDb); |
︙ | ︙ |
Changes to src/walker.c.
︙ | ︙ | |||
121 122 123 124 125 126 127 | ** If the Walker does not have an xSelectCallback() then this routine ** is a no-op returning WRC_Continue. */ int sqlite3WalkSelect(Walker *pWalker, Select *p){ int rc; if( p==0 || pWalker->xSelectCallback==0 ) return WRC_Continue; rc = WRC_Continue; | > | | | > > > > > | 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 | ** If the Walker does not have an xSelectCallback() then this routine ** is a no-op returning WRC_Continue. */ int sqlite3WalkSelect(Walker *pWalker, Select *p){ int rc; if( p==0 || pWalker->xSelectCallback==0 ) return WRC_Continue; rc = WRC_Continue; pWalker->walkerDepth++; while( p ){ rc = pWalker->xSelectCallback(pWalker, p); if( rc ) break; if( sqlite3WalkSelectExpr(pWalker, p) || sqlite3WalkSelectFrom(pWalker, p) ){ pWalker->walkerDepth--; return WRC_Abort; } p = p->pPrior; } pWalker->walkerDepth--; return rc & WRC_Abort; } |
Added test/aggnested.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | # 2012 August 23 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. # # 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 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); SELECT (SELECT group_concat(a1,'x') FROM t2) FROM t1; } } {1x2x3} do_test aggnested-1.2 { db eval { SELECT (SELECT group_concat(a1,'x') || '-' || group_concat(b1,'y') FROM t2) FROM t1; } } {1x2x3-4y5} do_test aggnested-1.3 { db eval { SELECT (SELECT group_concat(b1,a1) FROM t2) FROM t1; } } {415 425 435} do_test aggnested-1.4 { db eval { SELECT (SELECT group_concat(a1,b1) FROM t2) FROM t1; } } {151 252 353} # This test case is a copy of the one in # http://www.mail-archive.com/sqlite-users@sqlite.org/msg70787.html # do_test aggnested-2.0 { sqlite3 db2 :memory: db2 eval { CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1)); REPLACE INTO t1 VALUES(1,11,111,1111); REPLACE INTO t1 VALUES(2,22,222,2222); REPLACE INTO t1 VALUES(3,33,333,3333); CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1)); REPLACE INTO t2 VALUES(1,88,888,8888); REPLACE INTO t2 VALUES(2,99,999,9999); SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2), t1.* FROM t1; } } {A,B,B 3 33 333 3333} db2 close finish_test |