Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch output-minmax-row Excluding Merge-Ins
This is equivalent to a diff from 0bc594e861 to f27c7b4fb1
2012-02-02
| ||
18:46 | When non-aggregate columns occur in an aggregate query with a single min() or max(), then the values of the non-aggregate columns are taken from one of the rows that was the min() or max(). (check-in: fa13edd39c user: drh tags: trunk) | |
18:42 | Fix a problem with NULL handling in aggregate min/max when returning values from the row containing the min or max. (Closed-Leaf check-in: f27c7b4fb1 user: drh tags: output-minmax-row) | |
17:35 | For queries of the form "SELECT p, max(q) FROM t1", the value of column p returned is the one on the same row that holds the maximum value of q. (check-in: adb29232b6 user: drh tags: output-minmax-row) | |
15:50 | Data structure cleanup. Remove unused fields. Rearrange other files for tighter packing and reduced memory usage. (check-in: 0bc594e861 user: drh tags: trunk) | |
03:38 | Simplified array allocation in the IdList and AggInfo objects. (check-in: 25df2a7458 user: drh tags: trunk) | |
Changes to src/func.c.
︙ | ︙ | |||
23 24 25 26 27 28 29 30 31 32 33 34 35 36 | /* ** Return the collating function associated with a function. */ static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){ return context->pColl; } /* ** Implementation of the non-aggregate min() and max() functions */ static void minmaxFunc( sqlite3_context *context, int argc, | > > > > > > > > | 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | /* ** Return the collating function associated with a function. */ static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){ return context->pColl; } /* ** Indicate that the accumulator load should be skipped on this ** iteration of the aggregate loop. */ static void sqlite3SkipAccumulatorLoad(sqlite3_context *context){ context->skipFlag = 1; } /* ** Implementation of the non-aggregate min() and max() functions */ static void minmaxFunc( sqlite3_context *context, int argc, |
︙ | ︙ | |||
1330 1331 1332 1333 1334 1335 1336 | int NotUsed, sqlite3_value **argv ){ Mem *pArg = (Mem *)argv[0]; Mem *pBest; UNUSED_PARAMETER(NotUsed); | < > > | > > | | 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 | int NotUsed, sqlite3_value **argv ){ Mem *pArg = (Mem *)argv[0]; Mem *pBest; UNUSED_PARAMETER(NotUsed); pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest)); if( !pBest ) return; if( sqlite3_value_type(argv[0])==SQLITE_NULL ){ if( pBest->flags ) sqlite3SkipAccumulatorLoad(context); }else if( pBest->flags ){ int max; int cmp; CollSeq *pColl = sqlite3GetFuncCollSeq(context); /* This step function is used for both the min() and max() aggregates, ** the only difference between the two being that the sense of the ** comparison is inverted. For the max() aggregate, the ** sqlite3_user_data() function returns (void *)-1. For min() it ** returns (void *)db, where db is the sqlite3* database pointer. ** Therefore the next statement sets variable 'max' to 1 for the max() ** aggregate, or 0 for min(). */ max = sqlite3_user_data(context)!=0; cmp = sqlite3MemCompare(pBest, pArg, pColl); if( (max && cmp<0) || (!max && cmp>0) ){ sqlite3VdbeMemCopy(pBest, pArg); }else{ sqlite3SkipAccumulatorLoad(context); } }else{ sqlite3VdbeMemCopy(pBest, pArg); } } static void minMaxFinalize(sqlite3_context *context){ sqlite3_value *pRes; pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0); if( pRes ){ if( pRes->flags ){ sqlite3_result_value(context, pRes); } sqlite3VdbeMemRelease(pRes); } } /* |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 | /* ** Update the accumulator memory cells for an aggregate based on ** the current cursor position. */ static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){ Vdbe *v = pParse->pVdbe; int i; struct AggInfo_func *pF; struct AggInfo_col *pC; pAggInfo->directMode = 1; sqlite3ExprCacheClear(pParse); for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ int nArg; | > > | 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 3606 3607 | /* ** Update the accumulator memory cells for an aggregate based on ** the current cursor position. */ static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){ Vdbe *v = pParse->pVdbe; int i; int regHit = 0; int addrHitTest = 0; struct AggInfo_func *pF; struct AggInfo_col *pC; pAggInfo->directMode = 1; sqlite3ExprCacheClear(pParse); for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ int nArg; |
︙ | ︙ | |||
3627 3628 3629 3630 3631 3632 3633 | assert( pList!=0 ); /* pList!=0 if pF->pFunc has NEEDCOLL */ for(j=0, pItem=pList->a; !pColl && j<nArg; j++, pItem++){ pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr); } if( !pColl ){ pColl = pParse->db->pDfltColl; } | > | | 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 | assert( pList!=0 ); /* pList!=0 if pF->pFunc has NEEDCOLL */ for(j=0, pItem=pList->a; !pColl && j<nArg; j++, pItem++){ pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr); } if( !pColl ){ pColl = pParse->db->pDfltColl; } if( regHit==0 && pAggInfo->nAccumulator ) regHit = ++pParse->nMem; sqlite3VdbeAddOp4(v, OP_CollSeq, regHit, 0, 0, (char *)pColl, P4_COLLSEQ); } sqlite3VdbeAddOp4(v, OP_AggStep, 0, regAgg, pF->iMem, (void*)pF->pFunc, P4_FUNCDEF); sqlite3VdbeChangeP5(v, (u8)nArg); sqlite3ExprCacheAffinityChange(pParse, regAgg, nArg); sqlite3ReleaseTempRange(pParse, regAgg, nArg); if( addrNext ){ |
︙ | ︙ | |||
3650 3651 3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 3664 3665 3666 3667 3668 3669 | ** to pC->iMem. But by the time the value is used, the original register ** may have been used, invalidating the underlying buffer holding the ** text or blob value. See ticket [883034dcb5]. ** ** Another solution would be to change the OP_SCopy used to copy cached ** values to an OP_Copy. */ sqlite3ExprCacheClear(pParse); for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){ sqlite3ExprCode(pParse, pC->pExpr, pC->iMem); } pAggInfo->directMode = 0; sqlite3ExprCacheClear(pParse); } /* ** Add a single OP_Explain instruction to the VDBE to explain a simple ** count(*) query ("SELECT count(*) FROM pTab"). */ #ifndef SQLITE_OMIT_EXPLAIN | > > > > > > | 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 3664 3665 3666 3667 3668 3669 3670 3671 3672 3673 3674 3675 3676 3677 3678 | ** to pC->iMem. But by the time the value is used, the original register ** may have been used, invalidating the underlying buffer holding the ** text or blob value. See ticket [883034dcb5]. ** ** Another solution would be to change the OP_SCopy used to copy cached ** values to an OP_Copy. */ if( regHit ){ addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); } sqlite3ExprCacheClear(pParse); for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){ sqlite3ExprCode(pParse, pC->pExpr, pC->iMem); } pAggInfo->directMode = 0; sqlite3ExprCacheClear(pParse); if( addrHitTest ){ sqlite3VdbeJumpHere(v, addrHitTest); } } /* ** Add a single OP_Explain instruction to the VDBE to explain a simple ** count(*) query ("SELECT count(*) FROM pTab"). */ #ifndef SQLITE_OMIT_EXPLAIN |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
1327 1328 1329 1330 1331 1332 1333 | break; arithmetic_result_is_null: sqlite3VdbeMemSetNull(pOut); break; } | | > > > > > > > | 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 | break; arithmetic_result_is_null: sqlite3VdbeMemSetNull(pOut); break; } /* Opcode: CollSeq P1 * * P4 ** ** P4 is a pointer to a CollSeq struct. If the next call to a user function ** or aggregate calls sqlite3GetFuncCollSeq(), this collation sequence will ** be returned. This is used by the built-in min(), max() and nullif() ** functions. ** ** If P1 is not zero, then it is a register that a subsequent min() or ** max() aggregate will set to 1 if the current row is not the minimum or ** maximum. The P1 register is initialized to 0 by this instruction. ** ** The interface used by the implementation of the aforementioned functions ** to retrieve the collation sequence set by this opcode is not available ** publicly, only to user functions defined in func.c. */ case OP_CollSeq: { assert( pOp->p4type==P4_COLLSEQ ); if( pOp->p1 ){ sqlite3VdbeMemSetInt64(&aMem[pOp->p1], 0); } break; } /* Opcode: Function P1 P2 P3 P4 P5 ** ** Invoke a user function (P4 is a pointer to a Function structure that ** defines the function) with P5 arguments taken from register P2 and |
︙ | ︙ | |||
5352 5353 5354 5355 5356 5357 5358 5359 5360 5361 5362 5363 5364 5365 5366 5367 5368 5369 5370 5371 5372 5373 5374 5375 5376 | ctx.s.flags = MEM_Null; ctx.s.z = 0; ctx.s.zMalloc = 0; ctx.s.xDel = 0; ctx.s.db = db; ctx.isError = 0; ctx.pColl = 0; if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ assert( pOp>p->aOp ); assert( pOp[-1].p4type==P4_COLLSEQ ); assert( pOp[-1].opcode==OP_CollSeq ); ctx.pColl = pOp[-1].p4.pColl; } (ctx.pFunc->xStep)(&ctx, n, apVal); /* IMP: R-24505-23230 */ if( ctx.isError ){ sqlite3SetString(&p->zErrMsg, db, "%s", sqlite3_value_text(&ctx.s)); rc = ctx.isError; } sqlite3VdbeMemRelease(&ctx.s); break; } /* Opcode: AggFinal P1 P2 * P4 * | > > > > > > | 5359 5360 5361 5362 5363 5364 5365 5366 5367 5368 5369 5370 5371 5372 5373 5374 5375 5376 5377 5378 5379 5380 5381 5382 5383 5384 5385 5386 5387 5388 5389 | ctx.s.flags = MEM_Null; ctx.s.z = 0; ctx.s.zMalloc = 0; ctx.s.xDel = 0; ctx.s.db = db; ctx.isError = 0; ctx.pColl = 0; ctx.skipFlag = 0; if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ assert( pOp>p->aOp ); assert( pOp[-1].p4type==P4_COLLSEQ ); assert( pOp[-1].opcode==OP_CollSeq ); ctx.pColl = pOp[-1].p4.pColl; } (ctx.pFunc->xStep)(&ctx, n, apVal); /* IMP: R-24505-23230 */ if( ctx.isError ){ sqlite3SetString(&p->zErrMsg, db, "%s", sqlite3_value_text(&ctx.s)); rc = ctx.isError; } if( ctx.skipFlag ){ assert( pOp[-1].opcode==OP_CollSeq ); i = pOp[-1].p1; if( i ) sqlite3VdbeMemSetInt64(&aMem[i], 1); } sqlite3VdbeMemRelease(&ctx.s); break; } /* Opcode: AggFinal P1 P2 * P4 * |
︙ | ︙ |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
254 255 256 257 258 259 260 261 262 263 264 265 266 267 | struct sqlite3_context { FuncDef *pFunc; /* Pointer to function information. MUST BE FIRST */ VdbeFunc *pVdbeFunc; /* Auxilary data, if created. */ Mem s; /* The return value is stored here */ Mem *pMem; /* Memory cell used to store aggregate context */ int isError; /* Error code returned by the function. */ CollSeq *pColl; /* Collating sequence */ }; /* ** An Explain object accumulates indented output which is helpful ** in describing recursive data structures. */ struct Explain { | > | 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 | struct sqlite3_context { FuncDef *pFunc; /* Pointer to function information. MUST BE FIRST */ VdbeFunc *pVdbeFunc; /* Auxilary data, if created. */ Mem s; /* The return value is stored here */ Mem *pMem; /* Memory cell used to store aggregate context */ int isError; /* Error code returned by the function. */ CollSeq *pColl; /* Collating sequence */ int skipFlag; /* Skip skip accumulator loading if true */ }; /* ** An Explain object accumulates indented output which is helpful ** in describing recursive data structures. */ struct Explain { |
︙ | ︙ |
Changes to test/e_select.test.
︙ | ︙ | |||
796 797 798 799 800 801 802 | 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} 6 "SELECT count(*), * FROM z1" {6 63 born -26} 7 "SELECT max(a), * FROM z1" {63 63 born -26} | | | 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 | 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} 6 "SELECT count(*), * FROM z1" {6 63 born -26} 7 "SELECT max(a), * FROM z1" {63 63 born -26} 8 "SELECT *, min(a) FROM z1" {-5 {} 75 -5} 9 "SELECT *,* FROM z1,z2 LIMIT 1" { 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 } 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { 51.65 -59.58 belfries 51.65 -59.58 belfries } |
︙ | ︙ |
Added test/minmax4.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 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 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | # 2012 February 02 # # 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. # #*********************************************************************** # # Test for queries of the form: # # SELECT p, max(q) FROM t1; # # Demonstration that the value returned for p is on the same row as # the maximum q. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test minmax4-1.1 { db eval { CREATE TABLE t1(p,q); SELECT p, max(q) FROM t1; } } {{} {}} do_test minmax4-1.2 { db eval { SELECT p, min(q) FROM t1; } } {{} {}} do_test minmax4-1.3 { db eval { INSERT INTO t1 VALUES(1,2); SELECT p, max(q) FROM t1; } } {1 2} do_test minmax4-1.4 { db eval { SELECT p, min(q) FROM t1; } } {1 2} do_test minmax4-1.5 { db eval { INSERT INTO t1 VALUES(3,4); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.6 { db eval { SELECT p, min(q) FROM t1; } } {1 2} do_test minmax4-1.7 { db eval { INSERT INTO t1 VALUES(5,0); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.8 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.9 { db eval { INSERT INTO t1 VALUES(6,1); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.10 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.11 { db eval { INSERT INTO t1 VALUES(7,NULL); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.12 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.13 { db eval { DELETE FROM t1 WHERE q IS NOT NULL; SELECT p, max(q) FROM t1; } } {7 {}} do_test minmax4-1.14 { db eval { SELECT p, min(q) FROM t1; } } {7 {}} do_test minmax4-2.1 { db eval { CREATE TABLE t2(a,b,c); INSERT INTO t2 VALUES (1,null,2), (1,2,3), (1,1,4), (2,3,5); SELECT a, max(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 3 2 3 5} do_test minmax4-2.2 { db eval { SELECT a, min(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 4 2 3 5} do_test minmax4-2.3 { db eval { SELECT a, min(b), avg(b), count(b), c FROM t2 GROUP BY a ORDER BY a DESC; } } {2 3 3.0 1 5 1 1 1.5 2 4} do_test minmax4-2.4 { db eval { SELECT a, min(b), max(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 2 3 2 3 3 5} do_test minmax4-2.5 { db eval { SELECT a, max(b), min(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 1 4 2 3 3 5} do_test minmax4-2.6 { db eval { SELECT a, max(b), b, max(c), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 1 4 4 2 3 3 5 5} do_test minmax4-2.7 { db eval { SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 {} 2 2 2 3 3 5 5} finish_test |