Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -25,10 +25,18 @@ ** 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( @@ -1332,15 +1340,16 @@ ){ Mem *pArg = (Mem *)argv[0]; Mem *pBest; UNUSED_PARAMETER(NotUsed); - if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return; pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest)); if( !pBest ) return; - if( pBest->flags ){ + 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 @@ -1352,20 +1361,22 @@ */ 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( ALWAYS(pRes->flags) ){ + if( pRes->flags ){ sqlite3_result_value(context, pRes); } sqlite3VdbeMemRelease(pRes); } } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3594,10 +3594,12 @@ ** 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); @@ -3629,11 +3631,12 @@ pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr); } if( !pColl ){ pColl = pParse->db->pDfltColl; } - sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ); + 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); @@ -3652,16 +3655,22 @@ ** 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; inAccumulator; 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"). Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -1329,23 +1329,30 @@ arithmetic_result_is_null: sqlite3VdbeMemSetNull(pOut); break; } -/* Opcode: CollSeq * * P4 +/* 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 ** @@ -5354,10 +5361,11 @@ 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; @@ -5365,10 +5373,15 @@ (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; } Index: src/vdbeInt.h ================================================================== --- src/vdbeInt.h +++ src/vdbeInt.h @@ -256,10 +256,11 @@ 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. Index: test/e_select.test ================================================================== --- test/e_select.test +++ test/e_select.test @@ -798,11 +798,11 @@ 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" {63 born -26 -5} + 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" { ADDED test/minmax4.test Index: test/minmax4.test ================================================================== --- /dev/null +++ test/minmax4.test @@ -0,0 +1,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