diff options
Diffstat (limited to 'src/select.c')
| -rw-r--r-- | src/select.c | 708 |
1 files changed, 403 insertions, 305 deletions
diff --git a/src/select.c b/src/select.c index 6a4d0397f..fd99dc91f 100644 --- a/src/select.c +++ b/src/select.c @@ -154,7 +154,7 @@ Select *sqlite3SelectNew( pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->nSelectRow = 0; - if( pSrc==0 ) pSrc = sqlite3DbMallocZero(pParse->db, sizeof(*pSrc)); + if( pSrc==0 ) pSrc = sqlite3DbMallocZero(pParse->db, SZ_SRCLIST_1); pNew->pSrc = pSrc; pNew->pWhere = pWhere; pNew->pGroupBy = pGroupBy; @@ -319,10 +319,33 @@ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){ */ int sqlite3ColumnIndex(Table *pTab, const char *zCol){ int i; - u8 h = sqlite3StrIHash(zCol); - Column *pCol; - for(pCol=pTab->aCol, i=0; i<pTab->nCol; pCol++, i++){ - if( pCol->hName==h && sqlite3StrICmp(pCol->zCnName, zCol)==0 ) return i; + u8 h; + const Column *aCol; + int nCol; + + h = sqlite3StrIHash(zCol); + aCol = pTab->aCol; + nCol = pTab->nCol; + + /* See if the aHx gives us a lucky match */ + i = pTab->aHx[h % sizeof(pTab->aHx)]; + assert( i<nCol ); + if( aCol[i].hName==h + && sqlite3StrICmp(aCol[i].zCnName, zCol)==0 + ){ + return i; + } + + /* No lucky match from the hash table. Do a full search. */ + i = 0; + while( 1 /*exit-by-break*/ ){ + if( aCol[i].hName==h + && sqlite3StrICmp(aCol[i].zCnName, zCol)==0 + ){ + return i; + } + i++; + if( i>=nCol ) break; } return -1; } @@ -332,11 +355,13 @@ int sqlite3ColumnIndex(Table *pTab, const char *zCol){ */ void sqlite3SrcItemColumnUsed(SrcItem *pItem, int iCol){ assert( pItem!=0 ); - assert( (int)pItem->fg.isNestedFrom == IsNestedFrom(pItem->pSelect) ); + assert( (int)pItem->fg.isNestedFrom == IsNestedFrom(pItem) ); if( pItem->fg.isNestedFrom ){ ExprList *pResults; - assert( pItem->pSelect!=0 ); - pResults = pItem->pSelect->pEList; + assert( pItem->fg.isSubquery ); + assert( pItem->u4.pSubq!=0 ); + assert( pItem->u4.pSubq->pSelect!=0 ); + pResults = pItem->u4.pSubq->pSelect->pEList; assert( pResults!=0 ); assert( iCol>=0 && iCol<pResults->nExpr ); pResults->a[iCol].fg.bUsed = 1; @@ -359,7 +384,7 @@ static int tableAndColumnIndex( int iEnd, /* Last member of pSrc->a[] to check */ const char *zCol, /* Name of the column we are looking for */ int *piTab, /* Write index of pSrc->a[] here */ - int *piCol, /* Write index of pSrc->a[*piTab].pTab->aCol[] here */ + int *piCol, /* Write index of pSrc->a[*piTab].pSTab->aCol[] here */ int bIgnoreHidden /* Ignore hidden columns */ ){ int i; /* For looping over tables in pSrc */ @@ -370,9 +395,9 @@ static int tableAndColumnIndex( assert( (piTab==0)==(piCol==0) ); /* Both or neither are NULL */ for(i=iStart; i<=iEnd; i++){ - iCol = sqlite3ColumnIndex(pSrc->a[i].pTab, zCol); + iCol = sqlite3ColumnIndex(pSrc->a[i].pSTab, zCol); if( iCol>=0 - && (bIgnoreHidden==0 || IsHiddenColumn(&pSrc->a[i].pTab->aCol[iCol])==0) + && (bIgnoreHidden==0 || IsHiddenColumn(&pSrc->a[i].pSTab->aCol[iCol])==0) ){ if( piTab ){ sqlite3SrcItemColumnUsed(&pSrc->a[i], iCol); @@ -501,10 +526,10 @@ static int sqlite3ProcessJoin(Parse *pParse, Select *p){ pLeft = &pSrc->a[0]; pRight = &pLeft[1]; for(i=0; i<pSrc->nSrc-1; i++, pRight++, pLeft++){ - Table *pRightTab = pRight->pTab; + Table *pRightTab = pRight->pSTab; u32 joinType; - if( NEVER(pLeft->pTab==0 || pRightTab==0) ) continue; + if( NEVER(pLeft->pSTab==0 || pRightTab==0) ) continue; joinType = (pRight->fg.jointype & JT_OUTER)!=0 ? EP_OuterON : EP_InnerON; /* If this is a NATURAL join, synthesize an appropriate USING clause @@ -571,7 +596,7 @@ static int sqlite3ProcessJoin(Parse *pParse, Select *p){ } pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol); sqlite3SrcItemColumnUsed(&pSrc->a[iLeft], iLeftCol); - if( (pSrc->a[0].fg.jointype & JT_LTORJ)!=0 ){ + if( (pSrc->a[0].fg.jointype & JT_LTORJ)!=0 && pParse->nErr==0 ){ /* This branch runs if the query contains one or more RIGHT or FULL ** JOINs. If only a single table on the left side of this join ** contains the zName column, then this branch is a no-op. @@ -587,6 +612,8 @@ static int sqlite3ProcessJoin(Parse *pParse, Select *p){ */ ExprList *pFuncArgs = 0; /* Arguments to the coalesce() */ static const Token tkCoalesce = { "coalesce", 8 }; + assert( pE1!=0 ); + ExprSetProperty(pE1, EP_CanBeNull); while( tableAndColumnIndex(pSrc, iLeft+1, i, zName, &iLeft, &iLeftCol, pRight->fg.isSynthUsing)!=0 ){ if( pSrc->a[iLeft].fg.isUsing==0 @@ -603,7 +630,13 @@ static int sqlite3ProcessJoin(Parse *pParse, Select *p){ if( pFuncArgs ){ pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1); pE1 = sqlite3ExprFunction(pParse, pFuncArgs, &tkCoalesce, 0); + if( pE1 ){ + pE1->affExpr = SQLITE_AFF_DEFER; + } } + }else if( (pSrc->a[i+1].fg.jointype & JT_LEFT)!=0 && pParse->nErr==0 ){ + assert( pE1!=0 ); + ExprSetProperty(pE1, EP_CanBeNull); } pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol); sqlite3SrcItemColumnUsed(pRight, iRightCol); @@ -1512,8 +1545,8 @@ static void selectInnerLoop( ** X extra columns. */ KeyInfo *sqlite3KeyInfoAlloc(sqlite3 *db, int N, int X){ - int nExtra = (N+X)*(sizeof(CollSeq*)+1) - sizeof(CollSeq*); - KeyInfo *p = sqlite3DbMallocRawNN(db, sizeof(KeyInfo) + nExtra); + int nExtra = (N+X)*(sizeof(CollSeq*)+1); + KeyInfo *p = sqlite3DbMallocRawNN(db, SZ_KEYINFO(0) + nExtra); if( p ){ p->aSortFlags = (u8*)&p->aColl[N+X]; p->nKeyField = (u16)N; @@ -1521,7 +1554,7 @@ KeyInfo *sqlite3KeyInfoAlloc(sqlite3 *db, int N, int X){ p->enc = ENC(db); p->db = db; p->nRef = 1; - memset(&p[1], 0, nExtra); + memset(p->aColl, 0, nExtra); }else{ return (KeyInfo*)sqlite3OomFault(db); } @@ -1930,8 +1963,12 @@ static const char *columnTypeImpl( SrcList *pTabList = pNC->pSrcList; for(j=0;j<pTabList->nSrc && pTabList->a[j].iCursor!=pExpr->iTable;j++); if( j<pTabList->nSrc ){ - pTab = pTabList->a[j].pTab; - pS = pTabList->a[j].pSelect; + pTab = pTabList->a[j].pSTab; + if( pTabList->a[j].fg.isSubquery ){ + pS = pTabList->a[j].u4.pSubq->pSelect; + }else{ + pS = 0; + } }else{ pNC = pNC->pNext; } @@ -2076,6 +2113,10 @@ static void generateColumnTypes( #endif sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT); } +#else + UNUSED_PARAMETER(pParse); + UNUSED_PARAMETER(pTabList); + UNUSED_PARAMETER(pEList); #endif /* !defined(SQLITE_OMIT_DECLTYPE) */ } @@ -2995,7 +3036,9 @@ static int multiSelect( int priorOp; /* The SRT_ operation to apply to prior selects */ Expr *pLimit; /* Saved values of p->nLimit */ int addr; + int emptyBypass = 0; /* IfEmpty opcode to bypass RHS */ SelectDest uniondest; + testcase( p->op==TK_EXCEPT ); testcase( p->op==TK_UNION ); @@ -3034,6 +3077,8 @@ static int multiSelect( */ if( p->op==TK_EXCEPT ){ op = SRT_Except; + emptyBypass = sqlite3VdbeAddOp1(v, OP_IfEmpty, unionTab); + VdbeCoverage(v); }else{ assert( p->op==TK_UNION ); op = SRT_Union; @@ -3054,6 +3099,7 @@ static int multiSelect( if( p->op==TK_UNION ){ p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow); } + if( emptyBypass ) sqlite3VdbeJumpHere(v, emptyBypass); sqlite3ExprDelete(db, p->pLimit); p->pLimit = pLimit; p->iLimit = 0; @@ -3087,6 +3133,7 @@ static int multiSelect( int addr; SelectDest intersectdest; int r1; + int emptyBypass; /* INTERSECT is different from the others since it requires ** two temporary tables. Hence it has its own case. Begin @@ -3110,6 +3157,7 @@ static int multiSelect( if( rc ){ goto multi_select_end; } + emptyBypass = sqlite3VdbeAddOp1(v, OP_IfEmpty, tab1); VdbeCoverage(v); /* Code the current SELECT into temporary table "tab2" */ @@ -3141,7 +3189,7 @@ static int multiSelect( iBreak = sqlite3VdbeMakeLabel(pParse); iCont = sqlite3VdbeMakeLabel(pParse); computeLimitRegisters(pParse, p, iBreak); - sqlite3VdbeAddOp2(v, OP_Rewind, tab1, iBreak); VdbeCoverage(v); + sqlite3VdbeAddOp1(v, OP_Rewind, tab1); r1 = sqlite3GetTempReg(pParse); iStart = sqlite3VdbeAddOp2(v, OP_RowData, tab1, r1); sqlite3VdbeAddOp4Int(v, OP_NotFound, tab2, iCont, r1, 0); @@ -3153,6 +3201,7 @@ static int multiSelect( sqlite3VdbeAddOp2(v, OP_Next, tab1, iStart); VdbeCoverage(v); sqlite3VdbeResolveLabel(v, iBreak); sqlite3VdbeAddOp2(v, OP_Close, tab2, 0); + sqlite3VdbeJumpHere(v, emptyBypass); sqlite3VdbeAddOp2(v, OP_Close, tab1, 0); break; } @@ -3218,6 +3267,7 @@ static int multiSelect( multi_select_end: pDest->iSdst = dest.iSdst; pDest->nSdst = dest.nSdst; + pDest->iSDParm2 = dest.iSDParm2; if( pDelete ){ sqlite3ParserAddCleanup(pParse, sqlite3SelectDeleteGeneric, pDelete); } @@ -3830,6 +3880,7 @@ typedef struct SubstContext { int iTable; /* Replace references to this table */ int iNewTable; /* New table number */ int isOuterJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */ + int nSelDepth; /* Depth of sub-query recursion. Top==1 */ ExprList *pEList; /* Replacement expressions */ ExprList *pCList; /* Collation sequences for replacement expr */ } SubstContext; @@ -3905,38 +3956,41 @@ static Expr *substExpr( if( pSubst->isOuterJoin ){ ExprSetProperty(pNew, EP_CanBeNull); } - if( ExprHasProperty(pExpr,EP_OuterON|EP_InnerON) ){ - sqlite3SetJoinExpr(pNew, pExpr->w.iJoin, - pExpr->flags & (EP_OuterON|EP_InnerON)); - } - sqlite3ExprDelete(db, pExpr); - pExpr = pNew; - if( pExpr->op==TK_TRUEFALSE ){ - pExpr->u.iValue = sqlite3ExprTruthValue(pExpr); - pExpr->op = TK_INTEGER; - ExprSetProperty(pExpr, EP_IntValue); + if( pNew->op==TK_TRUEFALSE ){ + pNew->u.iValue = sqlite3ExprTruthValue(pNew); + pNew->op = TK_INTEGER; + ExprSetProperty(pNew, EP_IntValue); } /* Ensure that the expression now has an implicit collation sequence, ** just as it did when it was a column of a view or sub-query. */ { - CollSeq *pNat = sqlite3ExprCollSeq(pSubst->pParse, pExpr); + CollSeq *pNat = sqlite3ExprCollSeq(pSubst->pParse, pNew); CollSeq *pColl = sqlite3ExprCollSeq(pSubst->pParse, pSubst->pCList->a[iColumn].pExpr ); - if( pNat!=pColl || (pExpr->op!=TK_COLUMN && pExpr->op!=TK_COLLATE) ){ - pExpr = sqlite3ExprAddCollateString(pSubst->pParse, pExpr, + if( pNat!=pColl || (pNew->op!=TK_COLUMN && pNew->op!=TK_COLLATE) ){ + pNew = sqlite3ExprAddCollateString(pSubst->pParse, pNew, (pColl ? pColl->zName : "BINARY") ); } } - ExprClearProperty(pExpr, EP_Collate); + ExprClearProperty(pNew, EP_Collate); + if( ExprHasProperty(pExpr,EP_OuterON|EP_InnerON) ){ + sqlite3SetJoinExpr(pNew, pExpr->w.iJoin, + pExpr->flags & (EP_OuterON|EP_InnerON)); + } + sqlite3ExprDelete(db, pExpr); + pExpr = pNew; } } }else{ if( pExpr->op==TK_IF_NULL_ROW && pExpr->iTable==pSubst->iTable ){ pExpr->iTable = pSubst->iNewTable; } + if( pExpr->op==TK_AGG_FUNCTION && pExpr->op2>=pSubst->nSelDepth ){ + pExpr->op2--; + } pExpr->pLeft = substExpr(pSubst, pExpr->pLeft); pExpr->pRight = substExpr(pSubst, pExpr->pRight); if( ExprUseXSelect(pExpr) ){ @@ -3974,6 +4028,7 @@ static void substSelect( SrcItem *pItem; int i; if( !p ) return; + pSubst->nSelDepth++; do{ substExprList(pSubst, p->pEList); substExprList(pSubst, p->pGroupBy); @@ -3983,12 +4038,15 @@ static void substSelect( pSrc = p->pSrc; assert( pSrc!=0 ); for(i=pSrc->nSrc, pItem=pSrc->a; i>0; i--, pItem++){ - substSelect(pSubst, pItem->pSelect, 1); + if( pItem->fg.isSubquery ){ + substSelect(pSubst, pItem->u4.pSubq->pSelect, 1); + } if( pItem->fg.isTabFunc ){ substExprList(pSubst, pItem->u1.pFuncArg); } } }while( doPrior && (p = p->pPrior)!=0 ); + pSubst->nSelDepth--; } #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */ @@ -4014,7 +4072,7 @@ static void recomputeColumnsUsed( SrcItem *pSrcItem /* Which FROM clause item to recompute */ ){ Walker w; - if( NEVER(pSrcItem->pTab==0) ) return; + if( NEVER(pSrcItem->pSTab==0) ) return; memset(&w, 0, sizeof(w)); w.xExprCallback = recomputeColumnsUsedExpr; w.xSelectCallback = sqlite3SelectWalkNoop; @@ -4054,8 +4112,10 @@ static void srclistRenumberCursors( aCsrMap[pItem->iCursor+1] = pParse->nTab++; } pItem->iCursor = aCsrMap[pItem->iCursor+1]; - for(p=pItem->pSelect; p; p=p->pPrior){ - srclistRenumberCursors(pParse, aCsrMap, p->pSrc, -1); + if( pItem->fg.isSubquery ){ + for(p=pItem->u4.pSubq->pSelect; p; p=p->pPrior){ + srclistRenumberCursors(pParse, aCsrMap, p->pSrc, -1); + } } } } @@ -4202,9 +4262,9 @@ static int compoundHasDifferentAffinities(Select *p){ ** from 2015-02-09.) ** ** (3) If the subquery is the right operand of a LEFT JOIN then -** (3a) the subquery may not be a join and -** (3b) the FROM clause of the subquery may not contain a virtual -** table and +** (3a) the subquery may not be a join +** (**) Was (3b): "the FROM clause of the subquery may not contain +** a virtual table" ** (**) Was: "The outer query may not have a GROUP BY." This case ** is now managed correctly ** (3d) the outer query may not be DISTINCT. @@ -4366,7 +4426,8 @@ static int flattenSubquery( assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); pSubitem = &pSrc->a[iFrom]; iParent = pSubitem->iCursor; - pSub = pSubitem->pSelect; + assert( pSubitem->fg.isSubquery ); + pSub = pSubitem->u4.pSubq->pSelect; assert( pSub!=0 ); #ifndef SQLITE_OMIT_WINDOWFUNC @@ -4419,7 +4480,7 @@ static int flattenSubquery( */ if( (pSubitem->fg.jointype & (JT_OUTER|JT_LTORJ))!=0 ){ if( pSubSrc->nSrc>1 /* (3a) */ - || IsVirtual(pSubSrc->a[0].pTab) /* (3b) */ + /**** || IsVirtual(pSubSrc->a[0].pSTab) (3b)-omitted */ || (p->selFlags & SF_Distinct)!=0 /* (3d) */ || (pSubitem->fg.jointype & JT_RIGHT)!=0 /* (26) */ ){ @@ -4505,14 +4566,18 @@ static int flattenSubquery( pParse->zAuthContext = zSavedAuthContext; /* Delete the transient structures associated with the subquery */ - pSub1 = pSubitem->pSelect; - sqlite3DbFree(db, pSubitem->zDatabase); + + if( ALWAYS(pSubitem->fg.isSubquery) ){ + pSub1 = sqlite3SubqueryDetach(db, pSubitem); + }else{ + pSub1 = 0; + } + assert( pSubitem->fg.isSubquery==0 ); + assert( pSubitem->fg.fixedSchema==0 ); sqlite3DbFree(db, pSubitem->zName); sqlite3DbFree(db, pSubitem->zAlias); - pSubitem->zDatabase = 0; pSubitem->zName = 0; pSubitem->zAlias = 0; - pSubitem->pSelect = 0; assert( pSubitem->fg.isUsing!=0 || pSubitem->u3.pOn==0 ); /* If the sub-query is a compound SELECT statement, then (by restrictions @@ -4553,8 +4618,8 @@ static int flattenSubquery( ExprList *pOrderBy = p->pOrderBy; Expr *pLimit = p->pLimit; Select *pPrior = p->pPrior; - Table *pItemTab = pSubitem->pTab; - pSubitem->pTab = 0; + Table *pItemTab = pSubitem->pSTab; + pSubitem->pSTab = 0; p->pOrderBy = 0; p->pPrior = 0; p->pLimit = 0; @@ -4562,7 +4627,7 @@ static int flattenSubquery( p->pLimit = pLimit; p->pOrderBy = pOrderBy; p->op = TK_ALL; - pSubitem->pTab = pItemTab; + pSubitem->pSTab = pItemTab; if( pNew==0 ){ p->pPrior = pPrior; }else{ @@ -4577,11 +4642,14 @@ static int flattenSubquery( TREETRACE(0x4,pParse,p,("compound-subquery flattener" " creates %u as peer\n",pNew->selId)); } - assert( pSubitem->pSelect==0 ); + assert( pSubitem->fg.isSubquery==0 ); } sqlite3DbFree(db, aCsrMap); if( db->mallocFailed ){ - pSubitem->pSelect = pSub1; + assert( pSubitem->fg.fixedSchema==0 ); + assert( pSubitem->fg.isSubquery==0 ); + assert( pSubitem->u4.zDatabase==0 ); + sqlite3SrcItemAttachSubquery(pParse, pSubitem, pSub1, 0); return 1; } @@ -4590,10 +4658,10 @@ static int flattenSubquery( ** complete, since there may still exist Expr.pTab entries that ** refer to the subquery even after flattening. Ticket #3346. ** - ** pSubitem->pTab is always non-NULL by test restrictions and tests above. + ** pSubitem->pSTab is always non-NULL by test restrictions and tests above. */ - if( ALWAYS(pSubitem->pTab!=0) ){ - Table *pTabToDel = pSubitem->pTab; + if( ALWAYS(pSubitem->pSTab!=0) ){ + Table *pTabToDel = pSubitem->pSTab; if( pTabToDel->nTabRef==1 ){ Parse *pToplevel = sqlite3ParseToplevel(pParse); sqlite3ParserAddCleanup(pToplevel, sqlite3DeleteTableGeneric, pTabToDel); @@ -4601,7 +4669,7 @@ static int flattenSubquery( }else{ pTabToDel->nTabRef--; } - pSubitem->pTab = 0; + pSubitem->pSTab = 0; } /* The following loop runs once for each term in a compound-subquery @@ -4655,13 +4723,16 @@ static int flattenSubquery( /* Transfer the FROM clause terms from the subquery into the ** outer query. */ + iNewParent = pSubSrc->a[0].iCursor; for(i=0; i<nSubSrc; i++){ SrcItem *pItem = &pSrc->a[i+iFrom]; - if( pItem->fg.isUsing ) sqlite3IdListDelete(db, pItem->u3.pUsing); assert( pItem->fg.isTabFunc==0 ); + assert( pItem->fg.isSubquery + || pItem->fg.fixedSchema + || pItem->u4.zDatabase==0 ); + if( pItem->fg.isUsing ) sqlite3IdListDelete(db, pItem->u3.pUsing); *pItem = pSubSrc->a[i]; pItem->fg.jointype |= ltorj; - iNewParent = pSubSrc->a[i].iCursor; memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i])); } pSrc->a[iFrom].fg.jointype &= JT_LTORJ; @@ -4701,6 +4772,7 @@ static int flattenSubquery( pWhere = pSub->pWhere; pSub->pWhere = 0; if( isOuterJoin>0 ){ + assert( pSubSrc->nSrc==1 ); sqlite3SetJoinExpr(pWhere, iNewParent, EP_OuterON); } if( pWhere ){ @@ -4716,6 +4788,7 @@ static int flattenSubquery( x.iTable = iParent; x.iNewTable = iNewParent; x.isOuterJoin = isOuterJoin; + x.nSelDepth = 0; x.pEList = pSub->pEList; x.pCList = findLeftmostExprlist(pSub); substSelect(&x, pParent, 0); @@ -4812,7 +4885,8 @@ static void constInsert( return; /* Already present. Return without doing anything. */ } } - if( sqlite3ExprAffinity(pColumn)==SQLITE_AFF_BLOB ){ + assert( SQLITE_AFF_NONE<SQLITE_AFF_BLOB ); + if( sqlite3ExprAffinity(pColumn)<=SQLITE_AFF_BLOB ){ pConst->bHasAffBlob = 1; } @@ -4887,7 +4961,8 @@ static int propagateConstantExprRewriteOne( if( pColumn==pExpr ) continue; if( pColumn->iTable!=pExpr->iTable ) continue; if( pColumn->iColumn!=pExpr->iColumn ) continue; - if( bIgnoreAffBlob && sqlite3ExprAffinity(pColumn)==SQLITE_AFF_BLOB ){ + assert( SQLITE_AFF_NONE<SQLITE_AFF_BLOB ); + if( bIgnoreAffBlob && sqlite3ExprAffinity(pColumn)<=SQLITE_AFF_BLOB ){ break; } /* A match is found. Add the EP_FixedCol property */ @@ -5342,10 +5417,10 @@ static int disableUnusedSubqueryResultColumns(SrcItem *pItem){ if( pItem->fg.isCorrelated || pItem->fg.isCte ){ return 0; } - assert( pItem->pTab!=0 ); - pTab = pItem->pTab; - assert( pItem->pSelect!=0 ); - pSub = pItem->pSelect; + assert( pItem->pSTab!=0 ); + pTab = pItem->pSTab; + assert( pItem->fg.isSubquery ); + pSub = pItem->u4.pSubq->pSelect; assert( pSub->pEList->nExpr==pTab->nCol ); for(pX=pSub; pX; pX=pX->pPrior){ if( (pX->selFlags & (SF_Distinct|SF_Aggregate))!=0 ){ @@ -5474,13 +5549,13 @@ static Table *isSimpleCount(Select *p, AggInfo *pAggInfo){ if( p->pWhere || p->pEList->nExpr!=1 || p->pSrc->nSrc!=1 - || p->pSrc->a[0].pSelect + || p->pSrc->a[0].fg.isSubquery || pAggInfo->nFunc!=1 || p->pHaving ){ return 0; } - pTab = p->pSrc->a[0].pTab; + pTab = p->pSrc->a[0].pSTab; assert( pTab!=0 ); assert( !IsView(pTab) ); if( !IsOrdinaryTable(pTab) ) return 0; @@ -5505,7 +5580,7 @@ static Table *isSimpleCount(Select *p, AggInfo *pAggInfo){ ** pFrom->pIndex and return SQLITE_OK. */ int sqlite3IndexedByLookup(Parse *pParse, SrcItem *pFrom){ - Table *pTab = pFrom->pTab; + Table *pTab = pFrom->pSTab; char *zIndexedBy = pFrom->u1.zIndexedBy; Index *pIdx; assert( pTab!=0 ); @@ -5540,7 +5615,7 @@ int sqlite3IndexedByLookup(Parse *pParse, SrcItem *pFrom){ ** above that generates the code for a compound SELECT with an ORDER BY clause ** uses a merge algorithm that requires the same collating sequence on the ** result columns as on the ORDER BY clause. See ticket -** http://www.sqlite.org/src/info/6709574d2a +** http://sqlite.org/src/info/6709574d2a ** ** This transformation is only needed for EXCEPT, INTERSECT, and UNION. ** The UNION ALL operator works fine with multiSelectOrderBy() even when @@ -5582,7 +5657,11 @@ static int convertCompoundSelectToSubquery(Walker *pWalker, Select *p){ if( pNew==0 ) return WRC_Abort; memset(&dummy, 0, sizeof(dummy)); pNewSrc = sqlite3SrcListAppendFromTerm(pParse,0,0,0,&dummy,pNew,0); - if( pNewSrc==0 ) return WRC_Abort; + assert( pNewSrc!=0 || pParse->nErr ); + if( pParse->nErr ){ + sqlite3SrcListDelete(db, pNewSrc); + return WRC_Abort; + } *pNew = *p; p->pSrc = pNewSrc; p->pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ASTERISK, 0)); @@ -5597,7 +5676,7 @@ static int convertCompoundSelectToSubquery(Walker *pWalker, Select *p){ #ifndef SQLITE_OMIT_WINDOWFUNC p->pWinDefn = 0; #endif - p->selFlags &= ~SF_Compound; + p->selFlags &= ~(u32)SF_Compound; assert( (p->selFlags & SF_Converted)==0 ); p->selFlags |= SF_Converted; assert( pNew->pPrior!=0 ); @@ -5637,7 +5716,7 @@ static struct Cte *searchWith( ){ const char *zName = pItem->zName; With *p; - assert( pItem->zDatabase==0 ); + assert( pItem->fg.fixedSchema || pItem->u4.zDatabase==0 ); assert( zName!=0 ); for(p=pWith; p; p=p->pOuter){ int i; @@ -5692,7 +5771,7 @@ With *sqlite3WithPush(Parse *pParse, With *pWith, u8 bFree){ ** CTE expression, through routine checks to see if the reference is ** a recursive reference to the CTE. ** -** If pFrom matches a CTE according to either of these two above, pFrom->pTab +** If pFrom matches a CTE according to either of these two above, pFrom->pSTab ** and other fields are populated accordingly. ** ** Return 0 if no match is found. @@ -5707,7 +5786,7 @@ static int resolveFromTermToCte( Cte *pCte; /* Matched CTE (or NULL if no match) */ With *pWith; /* The matching WITH */ - assert( pFrom->pTab==0 ); + assert( pFrom->pSTab==0 ); if( pParse->pWith==0 ){ /* There are no WITH clauses in the stack. No match is possible */ return 0; @@ -5717,7 +5796,8 @@ static int resolveFromTermToCte( ** go no further. */ return 0; } - if( pFrom->zDatabase!=0 ){ + assert( pFrom->fg.hadSchema==0 || pFrom->fg.notCte!=0 ); + if( pFrom->fg.fixedSchema==0 && pFrom->u4.zDatabase!=0 ){ /* The FROM term contains a schema qualifier (ex: main.t1) and so ** it cannot possibly be a CTE reference. */ return 0; @@ -5753,7 +5833,7 @@ static int resolveFromTermToCte( } if( cannotBeFunction(pParse, pFrom) ) return 2; - assert( pFrom->pTab==0 ); + assert( pFrom->pSTab==0 ); pTab = sqlite3DbMallocZero(db, sizeof(Table)); if( pTab==0 ) return 2; pCteUse = pCte->pUse; @@ -5767,26 +5847,29 @@ static int resolveFromTermToCte( } pCteUse->eM10d = pCte->eM10d; } - pFrom->pTab = pTab; + pFrom->pSTab = pTab; pTab->nTabRef = 1; pTab->zName = sqlite3DbStrDup(db, pCte->zName); pTab->iPKey = -1; pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); pTab->tabFlags |= TF_Ephemeral | TF_NoVisibleRowid; - pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0); + sqlite3SrcItemAttachSubquery(pParse, pFrom, pCte->pSelect, 1); if( db->mallocFailed ) return 2; - pFrom->pSelect->selFlags |= SF_CopyCte; - assert( pFrom->pSelect ); + assert( pFrom->fg.isSubquery && pFrom->u4.pSubq ); + pSel = pFrom->u4.pSubq->pSelect; + assert( pSel!=0 ); + pSel->selFlags |= SF_CopyCte; if( pFrom->fg.isIndexedBy ){ sqlite3ErrorMsg(pParse, "no such index: \"%s\"", pFrom->u1.zIndexedBy); return 2; } + assert( !pFrom->fg.isIndexedBy ); pFrom->fg.isCte = 1; pFrom->u2.pCteUse = pCteUse; pCteUse->nUse++; /* Check if this is a recursive CTE. */ - pRecTerm = pSel = pFrom->pSelect; + pRecTerm = pSel; bMayRecursive = ( pSel->op==TK_ALL || pSel->op==TK_UNION ); while( bMayRecursive && pRecTerm->op==pSel->op ){ int i; @@ -5794,11 +5877,13 @@ static int resolveFromTermToCte( assert( pRecTerm->pPrior!=0 ); for(i=0; i<pSrc->nSrc; i++){ SrcItem *pItem = &pSrc->a[i]; - if( pItem->zDatabase==0 - && pItem->zName!=0 + if( pItem->zName!=0 + && !pItem->fg.hadSchema + && ALWAYS( !pItem->fg.isSubquery ) + && (pItem->fg.fixedSchema || pItem->u4.zDatabase==0) && 0==sqlite3StrICmp(pItem->zName, pCte->zName) ){ - pItem->pTab = pTab; + pItem->pSTab = pTab; pTab->nTabRef++; pItem->fg.isRecursive = 1; if( pRecTerm->selFlags & SF_Recursive ){ @@ -5900,11 +5985,14 @@ void sqlite3SelectPopWith(Walker *pWalker, Select *p){ ** SQLITE_NOMEM. */ int sqlite3ExpandSubquery(Parse *pParse, SrcItem *pFrom){ - Select *pSel = pFrom->pSelect; + Select *pSel; Table *pTab; + assert( pFrom->fg.isSubquery ); + assert( pFrom->u4.pSubq!=0 ); + pSel = pFrom->u4.pSubq->pSelect; assert( pSel ); - pFrom->pTab = pTab = sqlite3DbMallocZero(pParse->db, sizeof(Table)); + pFrom->pSTab = pTab = sqlite3DbMallocZero(pParse->db, sizeof(Table)); if( pTab==0 ) return SQLITE_NOMEM; pTab->nTabRef = 1; if( pFrom->zAlias ){ @@ -6004,7 +6092,7 @@ static int selectExpander(Walker *pWalker, Select *p){ pEList = p->pEList; if( pParse->pWith && (p->selFlags & SF_View) ){ if( p->pWith==0 ){ - p->pWith = (With*)sqlite3DbMallocZero(db, sizeof(With)); + p->pWith = (With*)sqlite3DbMallocZero(db, SZ_WITH(1) ); if( p->pWith==0 ){ return WRC_Abort; } @@ -6024,33 +6112,35 @@ static int selectExpander(Walker *pWalker, Select *p){ */ for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){ Table *pTab; - assert( pFrom->fg.isRecursive==0 || pFrom->pTab!=0 ); - if( pFrom->pTab ) continue; + assert( pFrom->fg.isRecursive==0 || pFrom->pSTab!=0 ); + if( pFrom->pSTab ) continue; assert( pFrom->fg.isRecursive==0 ); if( pFrom->zName==0 ){ #ifndef SQLITE_OMIT_SUBQUERY - Select *pSel = pFrom->pSelect; + Select *pSel; + assert( pFrom->fg.isSubquery && pFrom->u4.pSubq!=0 ); + pSel = pFrom->u4.pSubq->pSelect; /* A sub-query in the FROM clause of a SELECT */ assert( pSel!=0 ); - assert( pFrom->pTab==0 ); + assert( pFrom->pSTab==0 ); if( sqlite3WalkSelect(pWalker, pSel) ) return WRC_Abort; if( sqlite3ExpandSubquery(pParse, pFrom) ) return WRC_Abort; #endif #ifndef SQLITE_OMIT_CTE }else if( (rc = resolveFromTermToCte(pParse, pWalker, pFrom))!=0 ){ if( rc>1 ) return WRC_Abort; - pTab = pFrom->pTab; + pTab = pFrom->pSTab; assert( pTab!=0 ); #endif }else{ /* An ordinary table or view name in the FROM clause */ - assert( pFrom->pTab==0 ); - pFrom->pTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom); + assert( pFrom->pSTab==0 ); + pFrom->pSTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom); if( pTab==0 ) return WRC_Abort; if( pTab->nTabRef>=0xffff ){ sqlite3ErrorMsg(pParse, "too many references to \"%s\": max 65535", pTab->zName); - pFrom->pTab = 0; + pFrom->pSTab = 0; return WRC_Abort; } pTab->nTabRef++; @@ -6062,7 +6152,7 @@ static int selectExpander(Walker *pWalker, Select *p){ i16 nCol; u8 eCodeOrig = pWalker->eCode; if( sqlite3ViewGetColumnNames(pParse, pTab) ) return WRC_Abort; - assert( pFrom->pSelect==0 ); + assert( pFrom->fg.isSubquery==0 ); if( IsView(pTab) ){ if( (db->flags & SQLITE_EnableView)==0 && pTab->pSchema!=db->aDb[1].pSchema @@ -6070,7 +6160,7 @@ static int selectExpander(Walker *pWalker, Select *p){ sqlite3ErrorMsg(pParse, "access to view \"%s\" prohibited", pTab->zName); } - pFrom->pSelect = sqlite3SelectDup(db, pTab->u.view.pSelect, 0); + sqlite3SrcItemAttachSubquery(pParse, pFrom, pTab->u.view.pSelect, 1); } #ifndef SQLITE_OMIT_VIRTUALTABLE else if( ALWAYS(IsVirtual(pTab)) @@ -6086,7 +6176,9 @@ static int selectExpander(Walker *pWalker, Select *p){ nCol = pTab->nCol; pTab->nCol = -1; pWalker->eCode = 1; /* Turn on Select.selId renumbering */ - sqlite3WalkSelect(pWalker, pFrom->pSelect); + if( pFrom->fg.isSubquery ){ + sqlite3WalkSelect(pWalker, pFrom->u4.pSubq->pSelect); + } pWalker->eCode = eCodeOrig; pTab->nCol = nCol; } @@ -6173,7 +6265,7 @@ static int selectExpander(Walker *pWalker, Select *p){ } for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){ int nAdd; /* Number of cols including rowid */ - Table *pTab = pFrom->pTab; /* Table for this data source */ + Table *pTab = pFrom->pSTab; /* Table for this data source */ ExprList *pNestedFrom; /* Result-set of a nested FROM clause */ char *zTabName; /* AS name for this data source */ const char *zSchemaName = 0; /* Schema name for this data source */ @@ -6184,10 +6276,11 @@ static int selectExpander(Walker *pWalker, Select *p){ zTabName = pTab->zName; } if( db->mallocFailed ) break; - assert( (int)pFrom->fg.isNestedFrom == IsNestedFrom(pFrom->pSelect) ); + assert( (int)pFrom->fg.isNestedFrom == IsNestedFrom(pFrom) ); if( pFrom->fg.isNestedFrom ){ - assert( pFrom->pSelect!=0 ); - pNestedFrom = pFrom->pSelect->pEList; + assert( pFrom->fg.isSubquery && pFrom->u4.pSubq ); + assert( pFrom->u4.pSubq->pSelect!=0 ); + pNestedFrom = pFrom->u4.pSubq->pSelect->pEList; assert( pNestedFrom!=0 ); assert( pNestedFrom->nExpr==pTab->nCol ); assert( VisibleRowid(pTab)==0 || ViewCanHaveRowid ); @@ -6426,14 +6519,12 @@ static void selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){ assert( (p->selFlags & SF_Resolved) ); pTabList = p->pSrc; for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){ - Table *pTab = pFrom->pTab; + Table *pTab = pFrom->pSTab; assert( pTab!=0 ); - if( (pTab->tabFlags & TF_Ephemeral)!=0 ){ + if( (pTab->tabFlags & TF_Ephemeral)!=0 && pFrom->fg.isSubquery ){ /* A sub-query in the FROM clause of a SELECT */ - Select *pSel = pFrom->pSelect; - if( pSel ){ - sqlite3SubqueryColumnTypes(pParse, pTab, pSel, SQLITE_AFF_NONE); - } + Select *pSel = pFrom->u4.pSubq->pSelect; + sqlite3SubqueryColumnTypes(pParse, pTab, pSel, SQLITE_AFF_NONE); } } } @@ -6747,6 +6838,7 @@ static void finalizeAggFunctions(Parse *pParse, AggInfo *pAggInfo){ for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ ExprList *pList; assert( ExprUseXList(pF->pFExpr) ); + if( pParse->nErr ) return; pList = pF->pFExpr->x.pList; if( pF->iOBTab>=0 ){ /* For an ORDER BY aggregate, calls to OP_AggStep were deferred. Inputs @@ -6787,7 +6879,7 @@ static void finalizeAggFunctions(Parse *pParse, AggInfo *pAggInfo){ } sqlite3VdbeAddOp3(v, OP_AggStep, 0, regAgg, AggInfoFuncReg(pAggInfo,i)); sqlite3VdbeAppendP4(v, pF->pFunc, P4_FUNCDEF); - sqlite3VdbeChangeP5(v, (u8)nArg); + sqlite3VdbeChangeP5(v, (u16)nArg); sqlite3VdbeAddOp2(v, OP_Next, pF->iOBTab, iTop+1); VdbeCoverage(v); sqlite3VdbeJumpHere(v, iTop); sqlite3ReleaseTempRange(pParse, regAgg, nArg); @@ -6950,12 +7042,13 @@ static void updateAccumulator( } sqlite3VdbeAddOp3(v, OP_AggStep, 0, regAgg, AggInfoFuncReg(pAggInfo,i)); sqlite3VdbeAppendP4(v, pF->pFunc, P4_FUNCDEF); - sqlite3VdbeChangeP5(v, (u8)nArg); + sqlite3VdbeChangeP5(v, (u16)nArg); sqlite3ReleaseTempRange(pParse, regAgg, nArg); } if( addrNext ){ sqlite3VdbeResolveLabel(v, addrNext); } + if( pParse->nErr ) return; } if( regHit==0 && pAggInfo->nAccumulator ){ regHit = regAcc; @@ -6965,6 +7058,7 @@ static void updateAccumulator( } for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){ sqlite3ExprCode(pParse, pC->pCExpr, AggInfoColumnReg(pAggInfo,i)); + if( pParse->nErr ) return; } pAggInfo->directMode = 0; @@ -7080,25 +7174,28 @@ static SrcItem *isSelfJoinView( int iFirst, int iEnd /* Range of FROM-clause entries to search. */ ){ SrcItem *pItem; - assert( pThis->pSelect!=0 ); - if( pThis->pSelect->selFlags & SF_PushDown ) return 0; + Select *pSel; + assert( pThis->fg.isSubquery ); + pSel = pThis->u4.pSubq->pSelect; + assert( pSel!=0 ); + if( pSel->selFlags & SF_PushDown ) return 0; while( iFirst<iEnd ){ Select *pS1; pItem = &pTabList->a[iFirst++]; - if( pItem->pSelect==0 ) continue; + if( !pItem->fg.isSubquery ) continue; if( pItem->fg.viaCoroutine ) continue; if( pItem->zName==0 ) continue; - assert( pItem->pTab!=0 ); - assert( pThis->pTab!=0 ); - if( pItem->pTab->pSchema!=pThis->pTab->pSchema ) continue; + assert( pItem->pSTab!=0 ); + assert( pThis->pSTab!=0 ); + if( pItem->pSTab->pSchema!=pThis->pSTab->pSchema ) continue; if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue; - pS1 = pItem->pSelect; - if( pItem->pTab->pSchema==0 && pThis->pSelect->selId!=pS1->selId ){ + pS1 = pItem->u4.pSubq->pSelect; + if( pItem->pSTab->pSchema==0 && pSel->selId!=pS1->selId ){ /* The query flattener left two different CTE tables with identical ** names in the same FROM clause. */ continue; } - if( pItem->pSelect->selFlags & SF_PushDown ){ + if( pS1->selFlags & SF_PushDown ){ /* The view was modified by some other optimization such as ** pushDownWhereTerms() */ continue; @@ -7134,6 +7231,7 @@ static void agginfoFree(sqlite3 *db, void *pArg){ ** * There is no WHERE or GROUP BY or HAVING clauses on the subqueries ** * The outer query is a simple count(*) with no WHERE clause or other ** extraneous syntax. +** * None of the subqueries are DISTINCT (forumpost/a860f5fb2e 2025-03-10) ** ** Return TRUE if the optimization is undertaken. */ @@ -7142,6 +7240,7 @@ static int countOfViewOptimization(Parse *pParse, Select *p){ Expr *pExpr; Expr *pCount; sqlite3 *db; + SrcItem *pFrom; if( (p->selFlags & SF_Aggregate)==0 ) return 0; /* This is an aggregate */ if( p->pEList->nExpr!=1 ) return 0; /* Single result column */ if( p->pWhere ) return 0; @@ -7156,17 +7255,22 @@ static int countOfViewOptimization(Parse *pParse, Select *p){ if( pExpr->x.pList!=0 ) return 0; /* Must be count(*) */ if( p->pSrc->nSrc!=1 ) return 0; /* One table in FROM */ if( ExprHasProperty(pExpr, EP_WinFunc) ) return 0;/* Not a window function */ - pSub = p->pSrc->a[0].pSelect; - if( pSub==0 ) return 0; /* The FROM is a subquery */ + pFrom = p->pSrc->a; + if( pFrom->fg.isSubquery==0 ) return 0; /* FROM is a subquery */ + pSub = pFrom->u4.pSubq->pSelect; if( pSub->pPrior==0 ) return 0; /* Must be a compound */ if( pSub->selFlags & SF_CopyCte ) return 0; /* Not a CTE */ do{ if( pSub->op!=TK_ALL && pSub->pPrior ) return 0; /* Must be UNION ALL */ if( pSub->pWhere ) return 0; /* No WHERE clause */ if( pSub->pLimit ) return 0; /* No LIMIT clause */ - if( pSub->selFlags & SF_Aggregate ) return 0; /* Not an aggregate */ + if( pSub->selFlags & (SF_Aggregate|SF_Distinct) ){ + testcase( pSub->selFlags & SF_Aggregate ); + testcase( pSub->selFlags & SF_Distinct ); + return 0; /* Not an aggregate nor DISTINCT */ + } assert( pSub->pHaving==0 ); /* Due to the previous */ - pSub = pSub->pPrior; /* Repeat over compound */ + pSub = pSub->pPrior; /* Repeat over compound */ }while( pSub ); /* If we reach this point then it is OK to perform the transformation */ @@ -7174,17 +7278,16 @@ static int countOfViewOptimization(Parse *pParse, Select *p){ db = pParse->db; pCount = pExpr; pExpr = 0; - pSub = p->pSrc->a[0].pSelect; - p->pSrc->a[0].pSelect = 0; + pSub = sqlite3SubqueryDetach(db, pFrom); sqlite3SrcListDelete(db, p->pSrc); - p->pSrc = sqlite3DbMallocZero(pParse->db, sizeof(*p->pSrc)); + p->pSrc = sqlite3DbMallocZero(pParse->db, SZ_SRCLIST_1); while( pSub ){ Expr *pTerm; pPrior = pSub->pPrior; pSub->pPrior = 0; pSub->pNext = 0; pSub->selFlags |= SF_Aggregate; - pSub->selFlags &= ~SF_Compound; + pSub->selFlags &= ~(u32)SF_Compound; pSub->nSelectRow = 0; sqlite3ParserAddCleanup(pParse, sqlite3ExprListDeleteGeneric, pSub->pEList); pTerm = pPrior ? sqlite3ExprDup(db, pCount, 0) : pCount; @@ -7199,7 +7302,7 @@ static int countOfViewOptimization(Parse *pParse, Select *p){ pSub = pPrior; } p->pEList->a[0].pExpr = pExpr; - p->selFlags &= ~SF_Aggregate; + p->selFlags &= ~(u32)SF_Aggregate; #if TREETRACE_ENABLED if( sqlite3TreeTrace & 0x200 ){ @@ -7220,12 +7323,12 @@ static int sameSrcAlias(SrcItem *p0, SrcList *pSrc){ for(i=0; i<pSrc->nSrc; i++){ SrcItem *p1 = &pSrc->a[i]; if( p1==p0 ) continue; - if( p0->pTab==p1->pTab && 0==sqlite3_stricmp(p0->zAlias, p1->zAlias) ){ + if( p0->pSTab==p1->pSTab && 0==sqlite3_stricmp(p0->zAlias, p1->zAlias) ){ return 1; } - if( p1->pSelect - && (p1->pSelect->selFlags & SF_NestedFrom)!=0 - && sameSrcAlias(p0, p1->pSelect->pSrc) + if( p1->fg.isSubquery + && (p1->u4.pSubq->pSelect->selFlags & SF_NestedFrom)!=0 + && sameSrcAlias(p0, p1->u4.pSubq->pSelect->pSrc) ){ return 1; } @@ -7290,90 +7393,12 @@ static int fromClauseTermCanBeCoroutine( if( i==0 ) break; i--; pItem--; - if( pItem->pSelect!=0 ) return 0; /* (1c-i) */ + if( pItem->fg.isSubquery ) return 0; /* (1c-i) */ } return 1; } /* -** sqlite3WalkExpr() callback used by exprReferencesTable(). -*/ -static int exprReferencesTableExprCb(Walker *pWalker, Expr *pExpr){ - if( pExpr->op==TK_COLUMN && pExpr->iTable==pWalker->u.iCur ){ - pWalker->eCode = 1; - } - return WRC_Continue; -} - -/* -** Return true if the expression passed as the first argument refers -** to cursor number iCur. Otherwise return false. -*/ -static int exprReferencesTable(Expr *pExpr, int iCur){ - Walker w; - memset(&w, 0, sizeof(w)); - w.u.iCur = iCur; - w.xExprCallback = exprReferencesTableExprCb; - w.xSelectCallback = sqlite3SelectWalkNoop; - sqlite3WalkExpr(&w, pExpr); - return w.eCode; -} - -/* -** Index pIdx is a UNIQUE index on the table accessed by cursor number -** iCsr. This function returns a mask of the index columns that are -** constrained to match a single, non-NULL value by the WHERE clause -** passed as the 4th argument. For example, if the index is: -** -** CREATE UNIQUE INDEX idx ON tbl(a, b, c); -** -** and pWhere: -** -** WHERE a=? AND c=? -** -** then this function returns 5. -*/ -static u64 findConstIdxTerms( - Parse *pParse, - int iCsr, - Index *pIdx, - Expr *pWhere -){ - u64 m = 0; - if( pWhere->op==TK_AND ){ - m = findConstIdxTerms(pParse, iCsr, pIdx, pWhere->pLeft); - m |= findConstIdxTerms(pParse, iCsr, pIdx, pWhere->pRight); - }else if( pWhere->op==TK_EQ ){ - Expr *pLeft = sqlite3ExprSkipCollateAndLikely(pWhere->pLeft); - Expr *pRight = sqlite3ExprSkipCollateAndLikely(pWhere->pRight); - if( pRight->op==TK_COLUMN && pRight->iTable==iCsr ){ - SWAP(Expr*, pLeft, pRight); - } - if( pLeft->op==TK_COLUMN - && pLeft->iTable==iCsr - && exprReferencesTable(pRight, iCsr)==0 - ){ - if( pIdx ){ - int ii; - for(ii=0; ii<pIdx->nKeyCol; ii++){ - assert( pIdx->azColl[ii] ); - if( pLeft->iColumn==pIdx->aiColumn[ii] ){ - CollSeq *pColl = sqlite3ExprCompareCollSeq(pParse, pWhere); - if( pColl && sqlite3StrICmp(pColl->zName, pIdx->azColl[ii])==0 ){ - m |= ((u64)1 << ii); - break; - } - } - } - }else{ - if( pLeft->iColumn<0 ) m = 1; - } - } - } - return m; -} - -/* ** Argument pWhere is the WHERE clause belonging to SELECT statement p. This ** function attempts to transform expressions of the form: ** @@ -7393,7 +7418,11 @@ static u64 findConstIdxTerms( ** SELECT name FROM sailors AS S, reserves AS R ** WHERE S.sid = R.sid AND R.day = '2022-10-25'; */ -static void existsToJoin(Parse *pParse, Select *p, Expr *pWhere){ +static SQLITE_NOINLINE void existsToJoin( + Parse *pParse, + Select *p, + Expr *pWhere +){ if( pWhere && !ExprHasProperty(pWhere, EP_OuterON|EP_InnerON) && p->pSrc->nSrc>0 @@ -7411,49 +7440,34 @@ static void existsToJoin(Parse *pParse, Select *p, Expr *pWhere){ && (pSub->selFlags & (SF_Aggregate|SF_Correlated))==SF_Correlated && pSub->pWhere ){ - int bTransform = 0; /* True if EXISTS can be made into join */ - Table *pTab = pSub->pSrc->a[0].pTab; - int iCsr = pSub->pSrc->a[0].iCursor; - Index *pIdx; - if( HasRowid(pTab) && findConstIdxTerms(pParse, iCsr, 0,pSub->pWhere) ){ - bTransform = 1; - } - for(pIdx=pTab->pIndex; pIdx && bTransform==0; pIdx=pIdx->pNext){ - if( pIdx->onError && pIdx->nKeyCol<=63 ){ - u64 c = findConstIdxTerms(pParse, iCsr, pIdx, pSub->pWhere); - if( c==((u64)1 << pIdx->nKeyCol)-1 ){ - bTransform = 1; - } - } - } - if( bTransform ){ - memset(pWhere, 0, sizeof(*pWhere)); - pWhere->op = TK_INTEGER; - pWhere->u.iValue = 1; - ExprSetProperty(pWhere, EP_IntValue); - - assert( p->pWhere!=0 ); - p->pSrc = sqlite3SrcListAppendList(pParse, p->pSrc, pSub->pSrc); - p->pWhere = sqlite3PExpr(pParse, TK_AND, p->pWhere, pSub->pWhere); - - pSub->pWhere = 0; - pSub->pSrc = 0; - sqlite3ParserAddCleanup(pParse, sqlite3SelectDeleteGeneric, pSub); + memset(pWhere, 0, sizeof(*pWhere)); + pWhere->op = TK_INTEGER; + pWhere->u.iValue = 1; + ExprSetProperty(pWhere, EP_IntValue); + + assert( p->pWhere!=0 ); + pSub->pSrc->a[0].fg.fromExists = 1; + pSub->pSrc->a[0].fg.jointype |= JT_CROSS; + p->pSrc = sqlite3SrcListAppendList(pParse, p->pSrc, pSub->pSrc); + p->pWhere = sqlite3PExpr(pParse, TK_AND, p->pWhere, pSub->pWhere); + + pSub->pWhere = 0; + pSub->pSrc = 0; + sqlite3ParserAddCleanup(pParse, sqlite3SelectDeleteGeneric, pSub); #if TREETRACE_ENABLED - if( sqlite3TreeTrace & 0x100000 ){ - TREETRACE(0x100000,pParse,p, - ("After EXISTS-to-JOIN optimization:\n")); - sqlite3TreeViewSelect(0, p, 0); - } -#endif + if( sqlite3TreeTrace & 0x100000 ){ + TREETRACE(0x100000,pParse,p, + ("After EXISTS-to-JOIN optimization:\n")); + sqlite3TreeViewSelect(0, p, 0); } +#endif } } } } /* -** Generate code for the SELECT statement given in the p argument. +** Generate byte-code for the SELECT statement given in the p argument. ** ** The results are returned according to the SelectDest structure. ** See comments in sqliteInt.h for further information. @@ -7464,6 +7478,40 @@ static void existsToJoin(Parse *pParse, Select *p, Expr *pWhere){ ** ** This routine does NOT free the Select structure passed in. The ** calling function needs to do that. +** +** This is a long function. The following is an outline of the processing +** steps, with tags referencing various milestones: +** +** * Resolve names and similar preparation tag-select-0100 +** * Scan of the FROM clause tag-select-0200 +** + OUTER JOIN strength reduction tag-select-0220 +** + Sub-query ORDER BY removal tag-select-0230 +** + Query flattening tag-select-0240 +** * Separate subroutine for compound-SELECT tag-select-0300 +** * WHERE-clause constant propagation tag-select-0330 +** * Count()-of-VIEW optimization tag-select-0350 +** * Scan of the FROM clause again tag-select-0400 +** + Authorize unreferenced tables tag-select-0410 +** + Predicate push-down optimization tag-select-0420 +** + Omit unused subquery columns optimization tag-select-0440 +** + Generate code to implement subqueries tag-select-0480 +** - Co-routines tag-select-0482 +** - Reuse previously computed CTE tag-select-0484 +** - REuse previously computed VIEW tag-select-0486 +** - Materialize a VIEW or CTE tag-select-0488 +** * DISTINCT ORDER BY -> GROUP BY optimization tag-select-0500 +** * Set up for ORDER BY tag-select-0600 +** * Create output table tag-select-0630 +** * Prepare registers for LIMIT tag-select-0650 +** * Setup for DISTINCT tag-select-0680 +** * Generate code for non-aggregate and non-GROUP BY tag-select-0700 +** * Generate code for aggregate and/or GROUP BY tag-select-0800 +** + GROUP BY queries tag-select-0810 +** + non-GROUP BY queries tag-select-0820 +** - Special case of count() w/o GROUP BY tag-select-0821 +** - General case of non-GROUP BY aggregates tag-select-0822 +** * Sort results, as needed tag-select-0900 +** * Internal self-checks tag-select-1000 */ int sqlite3Select( Parse *pParse, /* The parser context */ @@ -7507,6 +7555,7 @@ int sqlite3Select( } #endif + /* tag-select-0100 */ assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistFifo ); assert( p->pOrderBy==0 || pDest->eDest!=SRT_Fifo ); assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistQueue ); @@ -7528,7 +7577,7 @@ int sqlite3Select( testcase( pParse->earlyCleanup ); p->pOrderBy = 0; } - p->selFlags &= ~SF_Distinct; + p->selFlags &= ~(u32)SF_Distinct; p->selFlags |= SF_NoopOrderBy; } sqlite3SelectPrep(pParse, p, 0); @@ -7558,7 +7607,7 @@ int sqlite3Select( if( sameSrcAlias(p0, p->pSrc) ){ sqlite3ErrorMsg(pParse, "target object/alias may not appear in FROM clause: %s", - p0->zAlias ? p0->zAlias : p0->pTab->zName + p0->zAlias ? p0->zAlias : p0->pSTab->zName ); goto select_end; } @@ -7567,7 +7616,7 @@ int sqlite3Select( ** and leaving this flag set can cause errors if a compound sub-query ** in p->pSrc is flattened into this query and this function called ** again as part of compound SELECT processing. */ - p->selFlags &= ~SF_UFSrcCheck; + p->selFlags &= ~(u32)SF_UFSrcCheck; } if( pDest->eDest==SRT_Output ){ @@ -7593,12 +7642,13 @@ int sqlite3Select( /* Try to do various optimizations (flattening subqueries, and strength ** reduction of join operators) in the FROM clause up into the main query + ** tag-select-0200 */ #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) for(i=0; !p->pPrior && i<pTabList->nSrc; i++){ SrcItem *pItem = &pTabList->a[i]; - Select *pSub = pItem->pSelect; - Table *pTab = pItem->pTab; + Select *pSub = pItem->fg.isSubquery ? pItem->u4.pSubq->pSelect : 0; + Table *pTab = pItem->pSTab; /* The expander should have already created transient Table objects ** even for FROM clause elements such as subqueries that do not correspond @@ -7615,6 +7665,7 @@ int sqlite3Select( ** way that the i-th table cannot be the NULL row of a join, then ** perform the appropriate simplification. This is called ** "OUTER JOIN strength reduction" in the SQLite documentation. + ** tag-select-0220 */ if( (pItem->fg.jointype & (JT_LEFT|JT_LTORJ))!=0 && sqlite3ExprImpliesNonNullRow(p->pWhere, pItem->iCursor, @@ -7685,7 +7736,8 @@ int sqlite3Select( if( (pSub->selFlags & SF_Aggregate)!=0 ) continue; assert( pSub->pGroupBy==0 ); - /* If a FROM-clause subquery has an ORDER BY clause that is not + /* tag-select-0230: + ** If a FROM-clause subquery has an ORDER BY clause that is not ** really doing anything, then delete it now so that it does not ** interfere with query flattening. See the discussion at ** https://sqlite.org/forum/forumpost/2d76f2bcf65d256a @@ -7751,6 +7803,7 @@ int sqlite3Select( continue; } + /* tag-select-0240 */ if( flattenSubquery(pParse, p, i, isAgg) ){ if( pParse->nErr ) goto select_end; /* This subquery can be absorbed into its parent. */ @@ -7766,7 +7819,7 @@ int sqlite3Select( #ifndef SQLITE_OMIT_COMPOUND_SELECT /* Handle compound SELECT statements using the separate multiSelect() - ** procedure. + ** procedure. tag-select-0300 */ if( p->pPrior ){ rc = multiSelect(pParse, p, pDest); @@ -7789,9 +7842,9 @@ int sqlite3Select( } /* Do the WHERE-clause constant propagation optimization if this is - ** a join. No need to speed time on this operation for non-join queries + ** a join. No need to spend time on this operation for non-join queries ** as the equivalent optimization will be handled by query planner in - ** sqlite3WhereBegin(). + ** sqlite3WhereBegin(). tag-select-0330 */ if( p->pWhere!=0 && p->pWhere->op==TK_AND @@ -7808,6 +7861,7 @@ int sqlite3Select( TREETRACE(0x2000,pParse,p,("Constant propagation not helpful\n")); } + /* tag-select-0350 */ if( OptimizationEnabled(db, SQLITE_QueryFlattener|SQLITE_CountOfView) && countOfViewOptimization(pParse, p) ){ @@ -7815,20 +7869,26 @@ int sqlite3Select( pTabList = p->pSrc; } - /* For each term in the FROM clause, do two things: - ** (1) Authorized unreferenced tables - ** (2) Generate code for all sub-queries + /* Loop over all terms in the FROM clause and do two things for each term: + ** + ** (1) Authorize unreferenced tables + ** (2) Generate code for all sub-queries + ** + ** tag-select-0400 */ for(i=0; i<pTabList->nSrc; i++){ SrcItem *pItem = &pTabList->a[i]; SrcItem *pPrior; SelectDest dest; + Subquery *pSubq; Select *pSub; #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) const char *zSavedAuthContext; #endif - /* Issue SQLITE_READ authorizations with a fake column name for any + /* Authorized unreferenced tables. tag-select-0410 + ** + ** Issue SQLITE_READ authorizations with a fake column name for any ** tables that are referenced but from which no values are extracted. ** Examples of where these kinds of null SQLITE_READ authorizations ** would occur: @@ -7845,17 +7905,28 @@ int sqlite3Select( ** string for the fake column name seems safer. */ if( pItem->colUsed==0 && pItem->zName!=0 ){ - sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", pItem->zDatabase); + const char *zDb; + if( pItem->fg.fixedSchema ){ + int iDb = sqlite3SchemaToIndex(pParse->db, pItem->u4.pSchema); + zDb = db->aDb[iDb].zDbSName; + }else if( pItem->fg.isSubquery ){ + zDb = 0; + }else{ + zDb = pItem->u4.zDatabase; + } + sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", zDb); } #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) /* Generate code for all sub-queries in the FROM clause */ - pSub = pItem->pSelect; - if( pSub==0 || pItem->addrFillSub!=0 ) continue; + if( pItem->fg.isSubquery==0 ) continue; + pSubq = pItem->u4.pSubq; + assert( pSubq!=0 ); + pSub = pSubq->pSelect; /* The code for a subquery should only be generated once. */ - assert( pItem->addrFillSub==0 ); + if( pSubq->addrFillSub!=0 ) continue; /* Increment Parse.nHeight by the height of the largest expression ** tree referred to by this, the parent select. The child select @@ -7868,6 +7939,7 @@ int sqlite3Select( /* Make copies of constant WHERE-clause terms in the outer query down ** inside the subquery. This can help the subquery to run more efficiently. + ** This is the "predicate push-down optimization". tag-select-0420 */ if( OptimizationEnabled(db, SQLITE_PushDown) && (pItem->fg.isCte==0 @@ -7881,13 +7953,14 @@ int sqlite3Select( sqlite3TreeViewSelect(0, p, 0); } #endif - assert( pItem->pSelect && (pItem->pSelect->selFlags & SF_PushDown)!=0 ); + assert( pSubq->pSelect && (pSub->selFlags & SF_PushDown)!=0 ); }else{ - TREETRACE(0x4000,pParse,p,("WHERE-lcause push-down not possible\n")); + TREETRACE(0x4000,pParse,p,("WHERE-clause push-down not possible\n")); } /* Convert unused result columns of the subquery into simple NULL ** expressions, to avoid unneeded searching and computation. + ** tag-select-0440 */ if( OptimizationEnabled(db, SQLITE_NullUnusedCols) && disableUnusedSubqueryResultColumns(pItem) @@ -7905,32 +7978,33 @@ int sqlite3Select( zSavedAuthContext = pParse->zAuthContext; pParse->zAuthContext = pItem->zName; - /* Generate code to implement the subquery + /* Generate byte-code to implement the subquery tag-select-0480 */ if( fromClauseTermCanBeCoroutine(pParse, pTabList, i, p->selFlags) ){ /* Implement a co-routine that will return a single row of the result - ** set on each invocation. + ** set on each invocation. tag-select-0482 */ int addrTop = sqlite3VdbeCurrentAddr(v)+1; - pItem->regReturn = ++pParse->nMem; - sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop); + pSubq->regReturn = ++pParse->nMem; + sqlite3VdbeAddOp3(v, OP_InitCoroutine, pSubq->regReturn, 0, addrTop); VdbeComment((v, "%!S", pItem)); - pItem->addrFillSub = addrTop; - sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn); + pSubq->addrFillSub = addrTop; + sqlite3SelectDestInit(&dest, SRT_Coroutine, pSubq->regReturn); ExplainQueryPlan((pParse, 1, "CO-ROUTINE %!S", pItem)); sqlite3Select(pParse, pSub, &dest); - pItem->pTab->nRowLogEst = pSub->nSelectRow; + pItem->pSTab->nRowLogEst = pSub->nSelectRow; pItem->fg.viaCoroutine = 1; - pItem->regResult = dest.iSdst; - sqlite3VdbeEndCoroutine(v, pItem->regReturn); + pSubq->regResult = dest.iSdst; + sqlite3VdbeEndCoroutine(v, pSubq->regReturn); + VdbeComment((v, "end %!S", pItem)); sqlite3VdbeJumpHere(v, addrTop-1); sqlite3ClearTempRegCache(pParse); }else if( pItem->fg.isCte && pItem->u2.pCteUse->addrM9e>0 ){ /* This is a CTE for which materialization code has already been ** generated. Invoke the subroutine to compute the materialization, - ** the make the pItem->iCursor be a copy of the ephemeral table that - ** holds the result of the materialization. */ + ** then make the pItem->iCursor be a copy of the ephemeral table that + ** holds the result of the materialization. tag-select-0484 */ CteUse *pCteUse = pItem->u2.pCteUse; sqlite3VdbeAddOp2(v, OP_Gosub, pCteUse->regRtn, pCteUse->addrM9e); if( pItem->iCursor!=pCteUse->iCur ){ @@ -7940,25 +8014,30 @@ int sqlite3Select( pSub->nSelectRow = pCteUse->nRowEst; }else if( (pPrior = isSelfJoinView(pTabList, pItem, 0, i))!=0 ){ /* This view has already been materialized by a prior entry in - ** this same FROM clause. Reuse it. */ - if( pPrior->addrFillSub ){ - sqlite3VdbeAddOp2(v, OP_Gosub, pPrior->regReturn, pPrior->addrFillSub); + ** this same FROM clause. Reuse it. tag-select-0486 */ + Subquery *pPriorSubq; + assert( pPrior->fg.isSubquery ); + pPriorSubq = pPrior->u4.pSubq; + assert( pPriorSubq!=0 ); + if( pPriorSubq->addrFillSub ){ + sqlite3VdbeAddOp2(v, OP_Gosub, pPriorSubq->regReturn, + pPriorSubq->addrFillSub); } sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor); - pSub->nSelectRow = pPrior->pSelect->nSelectRow; + pSub->nSelectRow = pPriorSubq->pSelect->nSelectRow; }else{ /* Materialize the view. If the view is not correlated, generate a ** subroutine to do the materialization so that subsequent uses of - ** the same view can reuse the materialization. */ + ** the same view can reuse the materialization. tag-select-0488 */ int topAddr; int onceAddr = 0; #ifdef SQLITE_ENABLE_STMT_SCANSTATUS int addrExplain; #endif - pItem->regReturn = ++pParse->nMem; + pSubq->regReturn = ++pParse->nMem; topAddr = sqlite3VdbeAddOp0(v, OP_Goto); - pItem->addrFillSub = topAddr+1; + pSubq->addrFillSub = topAddr+1; pItem->fg.isMaterialized = 1; if( pItem->fg.isCorrelated==0 ){ /* If the subquery is not correlated and if we are not inside of @@ -7973,17 +8052,17 @@ int sqlite3Select( ExplainQueryPlan2(addrExplain, (pParse, 1, "MATERIALIZE %!S", pItem)); sqlite3Select(pParse, pSub, &dest); - pItem->pTab->nRowLogEst = pSub->nSelectRow; + pItem->pSTab->nRowLogEst = pSub->nSelectRow; if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); - sqlite3VdbeAddOp2(v, OP_Return, pItem->regReturn, topAddr+1); + sqlite3VdbeAddOp2(v, OP_Return, pSubq->regReturn, topAddr+1); VdbeComment((v, "end %!S", pItem)); sqlite3VdbeScanStatusRange(v, addrExplain, addrExplain, -1); sqlite3VdbeJumpHere(v, topAddr); sqlite3ClearTempRegCache(pParse); if( pItem->fg.isCte && pItem->fg.isCorrelated==0 ){ CteUse *pCteUse = pItem->u2.pCteUse; - pCteUse->addrM9e = pItem->addrFillSub; - pCteUse->regRtn = pItem->regReturn; + pCteUse->addrM9e = pSubq->addrFillSub; + pCteUse->regRtn = pSubq->regReturn; pCteUse->iCur = pItem->iCursor; pCteUse->nRowEst = pSub->nSelectRow; } @@ -8009,7 +8088,9 @@ int sqlite3Select( } #endif - /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and + /* tag-select-0500 + ** + ** If the query is DISTINCT with an ORDER BY but is not an aggregate, and ** if the select-list is the same as the ORDER BY list, then this query ** can be rewritten as a GROUP BY. In other words, this: ** @@ -8031,7 +8112,7 @@ int sqlite3Select( && p->pWin==0 #endif ){ - p->selFlags &= ~SF_Distinct; + p->selFlags &= ~(u32)SF_Distinct; pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, pEList, 0); if( pGroupBy ){ for(i=0; i<pGroupBy->nExpr; i++){ @@ -8059,7 +8140,7 @@ int sqlite3Select( ** If that is the case, then the OP_OpenEphemeral instruction will be ** changed to an OP_Noop once we figure out that the sorting index is ** not needed. The sSort.addrSortIndex variable is used to facilitate - ** that change. + ** that change. tag-select-0600 */ if( sSort.pOrderBy ){ KeyInfo *pKeyInfo; @@ -8076,6 +8157,7 @@ int sqlite3Select( } /* If the output is destined for a temporary table, open that table. + ** tag-select-0630 */ if( pDest->eDest==SRT_EphemTab ){ sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr); @@ -8093,7 +8175,7 @@ int sqlite3Select( } } - /* Set the limiter. + /* Set the limiter. tag-select-0650 */ iEnd = sqlite3VdbeMakeLabel(pParse); if( (p->selFlags & SF_FixedLimit)==0 ){ @@ -8105,7 +8187,7 @@ int sqlite3Select( sSort.sortFlags |= SORTFLAG_UseSorter; } - /* Open an ephemeral index to use for the distinct set. + /* Open an ephemeral index to use for the distinct set. tag-select-0680 */ if( p->selFlags & SF_Distinct ){ sDistinct.tabTnct = pParse->nTab++; @@ -8120,7 +8202,7 @@ int sqlite3Select( } if( !isAgg && pGroupBy==0 ){ - /* No aggregate functions and no GROUP BY clause */ + /* No aggregate functions and no GROUP BY clause. tag-select-0700 */ u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0) | (p->selFlags & SF_FixedLimit); #ifndef SQLITE_OMIT_WINDOWFUNC @@ -8139,6 +8221,12 @@ int sqlite3Select( if( pWInfo==0 ) goto select_end; if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){ p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo); + if( pDest->eDest<=SRT_DistQueue && pDest->eDest>=SRT_DistFifo ){ + /* TUNING: For a UNION CTE, because UNION is implies DISTINCT, + ** reduce the estimated output row count by 8 (LogEst 30). + ** Search for tag-20250414a to see other cases */ + p->nSelectRow -= 30; + } } if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){ sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo); @@ -8193,8 +8281,8 @@ int sqlite3Select( sqlite3WhereEnd(pWInfo); } }else{ - /* This case when there exist aggregate functions or a GROUP BY clause - ** or both */ + /* This case is for when there exist aggregate functions or a GROUP BY + ** clause or both. tag-select-0800 */ NameContext sNC; /* Name context for processing aggregate information */ int iAMem; /* First Mem address for storing current GROUP BY */ int iBMem; /* First Mem address for previous GROUP BY */ @@ -8313,7 +8401,7 @@ int sqlite3Select( /* Processing for aggregates with GROUP BY is very different and - ** much more complex than aggregates without a GROUP BY. + ** much more complex than aggregates without a GROUP BY. tag-select-0810 */ if( pGroupBy ){ KeyInfo *pKeyInfo; /* Keying information for the group by clause */ @@ -8512,6 +8600,10 @@ int sqlite3Select( if( iOrderByCol ){ Expr *pX = p->pEList->a[iOrderByCol-1].pExpr; Expr *pBase = sqlite3ExprSkipCollateAndLikely(pX); + while( ALWAYS(pBase!=0) && pBase->op==TK_IF_NULL_ROW ){ + pX = pBase->pLeft; + pBase = sqlite3ExprSkipCollateAndLikely(pX); + } if( ALWAYS(pBase!=0) && pBase->op!=TK_AGG_COLUMN && pBase->op!=TK_REGISTER @@ -8535,12 +8627,12 @@ int sqlite3Select( ** for the next GROUP BY batch. */ sqlite3VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow); - VdbeComment((v, "output one row")); + VdbeComment((v, "output one row of %d", p->selId)); sqlite3ExprCodeMove(pParse, iBMem, iAMem, pGroupBy->nExpr); sqlite3VdbeAddOp2(v, OP_IfPos, iAbortFlag, addrEnd); VdbeCoverage(v); VdbeComment((v, "check abort flag")); sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); - VdbeComment((v, "reset accumulator")); + VdbeComment((v, "reset accumulator %d", p->selId)); /* Update the aggregate accumulators based on the content of ** the current row @@ -8548,7 +8640,7 @@ int sqlite3Select( sqlite3VdbeJumpHere(v, addr1); updateAccumulator(pParse, iUseFlag, pAggInfo, eDist); sqlite3VdbeAddOp2(v, OP_Integer, 1, iUseFlag); - VdbeComment((v, "indicate data in accumulator")); + VdbeComment((v, "indicate data in accumulator %d", p->selId)); /* End of the loop */ @@ -8565,7 +8657,7 @@ int sqlite3Select( /* Output the final row of result */ sqlite3VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow); - VdbeComment((v, "output final row")); + VdbeComment((v, "output final row of %d", p->selId)); /* Jump over the subroutines */ @@ -8586,7 +8678,7 @@ int sqlite3Select( addrOutputRow = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_IfPos, iUseFlag, addrOutputRow+2); VdbeCoverage(v); - VdbeComment((v, "Groupby result generator entry point")); + VdbeComment((v, "Groupby result generator entry point %d", p->selId)); sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); finalizeAggFunctions(pParse, pAggInfo); sqlite3ExprIfFalse(pParse, pHaving, addrOutputRow+1, SQLITE_JUMPIFNULL); @@ -8594,14 +8686,14 @@ int sqlite3Select( &sDistinct, pDest, addrOutputRow+1, addrSetAbort); sqlite3VdbeAddOp1(v, OP_Return, regOutputRow); - VdbeComment((v, "end groupby result generator")); + VdbeComment((v, "end groupby result generator %d", p->selId)); /* Generate a subroutine that will reset the group-by accumulator */ sqlite3VdbeResolveLabel(v, addrReset); resetAccumulator(pParse, pAggInfo); sqlite3VdbeAddOp2(v, OP_Integer, 0, iUseFlag); - VdbeComment((v, "indicate accumulator empty")); + VdbeComment((v, "indicate accumulator %d empty", p->selId)); sqlite3VdbeAddOp1(v, OP_Return, regReset); if( distFlag!=0 && eDist!=WHERE_DISTINCT_NOOP ){ @@ -8610,9 +8702,12 @@ int sqlite3Select( } } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */ else { + /* Aggregate functions without GROUP BY. tag-select-0820 */ Table *pTab; if( (pTab = isSimpleCount(p, pAggInfo))!=0 ){ - /* If isSimpleCount() returns a pointer to a Table structure, then + /* tag-select-0821 + ** + ** If isSimpleCount() returns a pointer to a Table structure, then ** the SQL statement is of the form: ** ** SELECT count(*) FROM <tbl> @@ -8671,6 +8766,8 @@ int sqlite3Select( sqlite3VdbeAddOp1(v, OP_Close, iCsr); explainSimpleCount(pParse, pTab, pBest); }else{ + /* The general case of an aggregate query without GROUP BY + ** tag-select-0822 */ int regAcc = 0; /* "populate accumulators" flag */ ExprList *pDistinct = 0; u16 distFlag = 0; @@ -8759,7 +8856,7 @@ int sqlite3Select( } /* If there is an ORDER BY clause, then we need to sort the results - ** and send them to the callback one by one. + ** and send them to the callback one by one. tag-select-0900 */ if( sSort.pOrderBy ){ assert( p->pEList==pEList ); @@ -8782,6 +8879,7 @@ select_end: assert( db->mallocFailed==0 || pParse->nErr!=0 ); sqlite3ExprListDelete(db, pMinMaxOrderBy); #ifdef SQLITE_DEBUG + /* Internal self-checks. tag-select-1000 */ if( pAggInfo && !db->mallocFailed ){ #if TREETRACE_ENABLED if( sqlite3TreeTrace & 0x20 ){ |