4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
11 *************************************************************************
12 ** This file contains C code routines that are called by the parser
13 ** to handle SELECT statements in SQLite.
15 #include "sqliteInt.h"
18 ** Trace output macros
20 #if SELECTTRACE_ENABLED
21 /***/ int sqlite3SelectTrace
= 0;
22 # define SELECTTRACE(K,P,S,X) \
23 if(sqlite3SelectTrace&(K)) \
24 sqlite3DebugPrintf("%*s%s.%p: ",(P)->nSelectIndent*2-2,"",\
28 # define SELECTTRACE(K,P,S,X)
33 ** An instance of the following object is used to record information about
34 ** how to process the DISTINCT keyword, to simplify passing that information
35 ** into the selectInnerLoop() routine.
37 typedef struct DistinctCtx DistinctCtx
;
39 u8 isTnct
; /* True if the DISTINCT keyword is present */
40 u8 eTnctType
; /* One of the WHERE_DISTINCT_* operators */
41 int tabTnct
; /* Ephemeral table used for DISTINCT processing */
42 int addrTnct
; /* Address of OP_OpenEphemeral opcode for tabTnct */
46 ** An instance of the following object is used to record information about
47 ** the ORDER BY (or GROUP BY) clause of query is being coded.
49 typedef struct SortCtx SortCtx
;
51 ExprList
*pOrderBy
; /* The ORDER BY (or GROUP BY clause) */
52 int nOBSat
; /* Number of ORDER BY terms satisfied by indices */
53 int iECursor
; /* Cursor number for the sorter */
54 int regReturn
; /* Register holding block-output return address */
55 int labelBkOut
; /* Start label for the block-output subroutine */
56 int addrSortIndex
; /* Address of the OP_SorterOpen or OP_OpenEphemeral */
57 int labelDone
; /* Jump here when done, ex: LIMIT reached */
58 u8 sortFlags
; /* Zero or more SORTFLAG_* bits */
59 u8 bOrderedInnerLoop
; /* ORDER BY correctly sorts the inner loop */
61 #define SORTFLAG_UseSorter 0x01 /* Use SorterOpen instead of OpenEphemeral */
64 ** Delete all the content of a Select structure. Deallocate the structure
65 ** itself only if bFree is true.
67 static void clearSelect(sqlite3
*db
, Select
*p
, int bFree
){
69 Select
*pPrior
= p
->pPrior
;
70 sqlite3ExprListDelete(db
, p
->pEList
);
71 sqlite3SrcListDelete(db
, p
->pSrc
);
72 sqlite3ExprDelete(db
, p
->pWhere
);
73 sqlite3ExprListDelete(db
, p
->pGroupBy
);
74 sqlite3ExprDelete(db
, p
->pHaving
);
75 sqlite3ExprListDelete(db
, p
->pOrderBy
);
76 sqlite3ExprDelete(db
, p
->pLimit
);
77 sqlite3ExprDelete(db
, p
->pOffset
);
78 if( p
->pWith
) sqlite3WithDelete(db
, p
->pWith
);
79 if( bFree
) sqlite3DbFree(db
, p
);
86 ** Initialize a SelectDest structure.
88 void sqlite3SelectDestInit(SelectDest
*pDest
, int eDest
, int iParm
){
89 pDest
->eDest
= (u8
)eDest
;
90 pDest
->iSDParm
= iParm
;
98 ** Allocate a new Select structure and return a pointer to that
101 Select
*sqlite3SelectNew(
102 Parse
*pParse
, /* Parsing context */
103 ExprList
*pEList
, /* which columns to include in the result */
104 SrcList
*pSrc
, /* the FROM clause -- which tables to scan */
105 Expr
*pWhere
, /* the WHERE clause */
106 ExprList
*pGroupBy
, /* the GROUP BY clause */
107 Expr
*pHaving
, /* the HAVING clause */
108 ExprList
*pOrderBy
, /* the ORDER BY clause */
109 u32 selFlags
, /* Flag parameters, such as SF_Distinct */
110 Expr
*pLimit
, /* LIMIT value. NULL means not used */
111 Expr
*pOffset
/* OFFSET value. NULL means no offset */
115 sqlite3
*db
= pParse
->db
;
116 pNew
= sqlite3DbMallocRawNN(db
, sizeof(*pNew
) );
118 assert( db
->mallocFailed
);
122 pEList
= sqlite3ExprListAppend(pParse
, 0, sqlite3Expr(db
,TK_ASTERISK
,0));
124 pNew
->pEList
= pEList
;
125 pNew
->op
= TK_SELECT
;
126 pNew
->selFlags
= selFlags
;
129 #if SELECTTRACE_ENABLED
130 pNew
->zSelName
[0] = 0;
132 pNew
->addrOpenEphm
[0] = -1;
133 pNew
->addrOpenEphm
[1] = -1;
134 pNew
->nSelectRow
= 0;
135 if( pSrc
==0 ) pSrc
= sqlite3DbMallocZero(db
, sizeof(*pSrc
));
137 pNew
->pWhere
= pWhere
;
138 pNew
->pGroupBy
= pGroupBy
;
139 pNew
->pHaving
= pHaving
;
140 pNew
->pOrderBy
= pOrderBy
;
143 pNew
->pLimit
= pLimit
;
144 pNew
->pOffset
= pOffset
;
146 assert( pOffset
==0 || pLimit
!=0 || pParse
->nErr
>0 || db
->mallocFailed
!=0 );
147 if( db
->mallocFailed
) {
148 clearSelect(db
, pNew
, pNew
!=&standin
);
151 assert( pNew
->pSrc
!=0 || pParse
->nErr
>0 );
153 assert( pNew
!=&standin
);
157 #if SELECTTRACE_ENABLED
159 ** Set the name of a Select object
161 void sqlite3SelectSetName(Select
*p
, const char *zName
){
163 sqlite3_snprintf(sizeof(p
->zSelName
), p
->zSelName
, "%s", zName
);
170 ** Delete the given Select structure and all of its substructures.
172 void sqlite3SelectDelete(sqlite3
*db
, Select
*p
){
173 if( p
) clearSelect(db
, p
, 1);
177 ** Return a pointer to the right-most SELECT statement in a compound.
179 static Select
*findRightmost(Select
*p
){
180 while( p
->pNext
) p
= p
->pNext
;
185 ** Given 1 to 3 identifiers preceding the JOIN keyword, determine the
186 ** type of join. Return an integer constant that expresses that type
187 ** in terms of the following bit values:
196 ** A full outer join is the combination of JT_LEFT and JT_RIGHT.
198 ** If an illegal or unsupported join type is seen, then still return
199 ** a join type, but put an error in the pParse structure.
201 int sqlite3JoinType(Parse
*pParse
, Token
*pA
, Token
*pB
, Token
*pC
){
205 /* 0123456789 123456789 123456789 123 */
206 static const char zKeyText
[] = "naturaleftouterightfullinnercross";
207 static const struct {
208 u8 i
; /* Beginning of keyword text in zKeyText[] */
209 u8 nChar
; /* Length of the keyword in characters */
210 u8 code
; /* Join type mask */
212 /* natural */ { 0, 7, JT_NATURAL
},
213 /* left */ { 6, 4, JT_LEFT
|JT_OUTER
},
214 /* outer */ { 10, 5, JT_OUTER
},
215 /* right */ { 14, 5, JT_RIGHT
|JT_OUTER
},
216 /* full */ { 19, 4, JT_LEFT
|JT_RIGHT
|JT_OUTER
},
217 /* inner */ { 23, 5, JT_INNER
},
218 /* cross */ { 28, 5, JT_INNER
|JT_CROSS
},
224 for(i
=0; i
<3 && apAll
[i
]; i
++){
226 for(j
=0; j
<ArraySize(aKeyword
); j
++){
227 if( p
->n
==aKeyword
[j
].nChar
228 && sqlite3StrNICmp((char*)p
->z
, &zKeyText
[aKeyword
[j
].i
], p
->n
)==0 ){
229 jointype
|= aKeyword
[j
].code
;
233 testcase( j
==0 || j
==1 || j
==2 || j
==3 || j
==4 || j
==5 || j
==6 );
234 if( j
>=ArraySize(aKeyword
) ){
235 jointype
|= JT_ERROR
;
240 (jointype
& (JT_INNER
|JT_OUTER
))==(JT_INNER
|JT_OUTER
) ||
241 (jointype
& JT_ERROR
)!=0
243 const char *zSp
= " ";
245 if( pC
==0 ){ zSp
++; }
246 sqlite3ErrorMsg(pParse
, "unknown or unsupported join type: "
247 "%T %T%s%T", pA
, pB
, zSp
, pC
);
249 }else if( (jointype
& JT_OUTER
)!=0
250 && (jointype
& (JT_LEFT
|JT_RIGHT
))!=JT_LEFT
){
251 sqlite3ErrorMsg(pParse
,
252 "RIGHT and FULL OUTER JOINs are not currently supported");
259 ** Return the index of a column in a table. Return -1 if the column
260 ** is not contained in the table.
262 static int columnIndex(Table
*pTab
, const char *zCol
){
264 for(i
=0; i
<pTab
->nCol
; i
++){
265 if( sqlite3StrICmp(pTab
->aCol
[i
].zName
, zCol
)==0 ) return i
;
271 ** Search the first N tables in pSrc, from left to right, looking for a
272 ** table that has a column named zCol.
274 ** When found, set *piTab and *piCol to the table index and column index
275 ** of the matching column and return TRUE.
277 ** If not found, return FALSE.
279 static int tableAndColumnIndex(
280 SrcList
*pSrc
, /* Array of tables to search */
281 int N
, /* Number of tables in pSrc->a[] to search */
282 const char *zCol
, /* Name of the column we are looking for */
283 int *piTab
, /* Write index of pSrc->a[] here */
284 int *piCol
/* Write index of pSrc->a[*piTab].pTab->aCol[] here */
286 int i
; /* For looping over tables in pSrc */
287 int iCol
; /* Index of column matching zCol */
289 assert( (piTab
==0)==(piCol
==0) ); /* Both or neither are NULL */
291 iCol
= columnIndex(pSrc
->a
[i
].pTab
, zCol
);
304 ** This function is used to add terms implied by JOIN syntax to the
305 ** WHERE clause expression of a SELECT statement. The new term, which
306 ** is ANDed with the existing WHERE clause, is of the form:
308 ** (tab1.col1 = tab2.col2)
310 ** where tab1 is the iSrc'th table in SrcList pSrc and tab2 is the
311 ** (iSrc+1)'th. Column col1 is column iColLeft of tab1, and col2 is
312 ** column iColRight of tab2.
314 static void addWhereTerm(
315 Parse
*pParse
, /* Parsing context */
316 SrcList
*pSrc
, /* List of tables in FROM clause */
317 int iLeft
, /* Index of first table to join in pSrc */
318 int iColLeft
, /* Index of column in first table */
319 int iRight
, /* Index of second table in pSrc */
320 int iColRight
, /* Index of column in second table */
321 int isOuterJoin
, /* True if this is an OUTER join */
322 Expr
**ppWhere
/* IN/OUT: The WHERE clause to add to */
324 sqlite3
*db
= pParse
->db
;
329 assert( iLeft
<iRight
);
330 assert( pSrc
->nSrc
>iRight
);
331 assert( pSrc
->a
[iLeft
].pTab
);
332 assert( pSrc
->a
[iRight
].pTab
);
334 pE1
= sqlite3CreateColumnExpr(db
, pSrc
, iLeft
, iColLeft
);
335 pE2
= sqlite3CreateColumnExpr(db
, pSrc
, iRight
, iColRight
);
337 pEq
= sqlite3PExpr(pParse
, TK_EQ
, pE1
, pE2
, 0);
338 if( pEq
&& isOuterJoin
){
339 ExprSetProperty(pEq
, EP_FromJoin
);
340 assert( !ExprHasProperty(pEq
, EP_TokenOnly
|EP_Reduced
) );
341 ExprSetVVAProperty(pEq
, EP_NoReduce
);
342 pEq
->iRightJoinTable
= (i16
)pE2
->iTable
;
344 *ppWhere
= sqlite3ExprAnd(db
, *ppWhere
, pEq
);
348 ** Set the EP_FromJoin property on all terms of the given expression.
349 ** And set the Expr.iRightJoinTable to iTable for every term in the
352 ** The EP_FromJoin property is used on terms of an expression to tell
353 ** the LEFT OUTER JOIN processing logic that this term is part of the
354 ** join restriction specified in the ON or USING clause and not a part
355 ** of the more general WHERE clause. These terms are moved over to the
356 ** WHERE clause during join processing but we need to remember that they
357 ** originated in the ON or USING clause.
359 ** The Expr.iRightJoinTable tells the WHERE clause processing that the
360 ** expression depends on table iRightJoinTable even if that table is not
361 ** explicitly mentioned in the expression. That information is needed
362 ** for cases like this:
364 ** SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
366 ** The where clause needs to defer the handling of the t1.x=5
367 ** term until after the t2 loop of the join. In that way, a
368 ** NULL t2 row will be inserted whenever t1.x!=5. If we do not
369 ** defer the handling of t1.x=5, it will be processed immediately
370 ** after the t1 loop and rows with t1.x!=5 will never appear in
371 ** the output, which is incorrect.
373 static void setJoinExpr(Expr
*p
, int iTable
){
375 ExprSetProperty(p
, EP_FromJoin
);
376 assert( !ExprHasProperty(p
, EP_TokenOnly
|EP_Reduced
) );
377 ExprSetVVAProperty(p
, EP_NoReduce
);
378 p
->iRightJoinTable
= (i16
)iTable
;
379 if( p
->op
==TK_FUNCTION
&& p
->x
.pList
){
381 for(i
=0; i
<p
->x
.pList
->nExpr
; i
++){
382 setJoinExpr(p
->x
.pList
->a
[i
].pExpr
, iTable
);
385 setJoinExpr(p
->pLeft
, iTable
);
391 ** This routine processes the join information for a SELECT statement.
392 ** ON and USING clauses are converted into extra terms of the WHERE clause.
393 ** NATURAL joins also create extra WHERE clause terms.
395 ** The terms of a FROM clause are contained in the Select.pSrc structure.
396 ** The left most table is the first entry in Select.pSrc. The right-most
397 ** table is the last entry. The join operator is held in the entry to
398 ** the left. Thus entry 0 contains the join operator for the join between
399 ** entries 0 and 1. Any ON or USING clauses associated with the join are
400 ** also attached to the left entry.
402 ** This routine returns the number of errors encountered.
404 static int sqliteProcessJoin(Parse
*pParse
, Select
*p
){
405 SrcList
*pSrc
; /* All tables in the FROM clause */
406 int i
, j
; /* Loop counters */
407 struct SrcList_item
*pLeft
; /* Left table being joined */
408 struct SrcList_item
*pRight
; /* Right table being joined */
413 for(i
=0; i
<pSrc
->nSrc
-1; i
++, pRight
++, pLeft
++){
414 Table
*pLeftTab
= pLeft
->pTab
;
415 Table
*pRightTab
= pRight
->pTab
;
418 if( NEVER(pLeftTab
==0 || pRightTab
==0) ) continue;
419 isOuter
= (pRight
->fg
.jointype
& JT_OUTER
)!=0;
421 /* When the NATURAL keyword is present, add WHERE clause terms for
422 ** every column that the two tables have in common.
424 if( pRight
->fg
.jointype
& JT_NATURAL
){
425 if( pRight
->pOn
|| pRight
->pUsing
){
426 sqlite3ErrorMsg(pParse
, "a NATURAL join may not have "
427 "an ON or USING clause", 0);
430 for(j
=0; j
<pRightTab
->nCol
; j
++){
431 char *zName
; /* Name of column in the right table */
432 int iLeft
; /* Matching left table */
433 int iLeftCol
; /* Matching column in the left table */
435 zName
= pRightTab
->aCol
[j
].zName
;
436 if( tableAndColumnIndex(pSrc
, i
+1, zName
, &iLeft
, &iLeftCol
) ){
437 addWhereTerm(pParse
, pSrc
, iLeft
, iLeftCol
, i
+1, j
,
438 isOuter
, &p
->pWhere
);
443 /* Disallow both ON and USING clauses in the same join
445 if( pRight
->pOn
&& pRight
->pUsing
){
446 sqlite3ErrorMsg(pParse
, "cannot have both ON and USING "
447 "clauses in the same join");
451 /* Add the ON clause to the end of the WHERE clause, connected by
455 if( isOuter
) setJoinExpr(pRight
->pOn
, pRight
->iCursor
);
456 p
->pWhere
= sqlite3ExprAnd(pParse
->db
, p
->pWhere
, pRight
->pOn
);
460 /* Create extra terms on the WHERE clause for each column named
461 ** in the USING clause. Example: If the two tables to be joined are
462 ** A and B and the USING clause names X, Y, and Z, then add this
463 ** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
464 ** Report an error if any column mentioned in the USING clause is
465 ** not contained in both tables to be joined.
467 if( pRight
->pUsing
){
468 IdList
*pList
= pRight
->pUsing
;
469 for(j
=0; j
<pList
->nId
; j
++){
470 char *zName
; /* Name of the term in the USING clause */
471 int iLeft
; /* Table on the left with matching column name */
472 int iLeftCol
; /* Column number of matching column on the left */
473 int iRightCol
; /* Column number of matching column on the right */
475 zName
= pList
->a
[j
].zName
;
476 iRightCol
= columnIndex(pRightTab
, zName
);
478 || !tableAndColumnIndex(pSrc
, i
+1, zName
, &iLeft
, &iLeftCol
)
480 sqlite3ErrorMsg(pParse
, "cannot join using column %s - column "
481 "not present in both tables", zName
);
484 addWhereTerm(pParse
, pSrc
, iLeft
, iLeftCol
, i
+1, iRightCol
,
485 isOuter
, &p
->pWhere
);
492 /* Forward reference */
493 static KeyInfo
*keyInfoFromExprList(
494 Parse
*pParse
, /* Parsing context */
495 ExprList
*pList
, /* Form the KeyInfo object from this ExprList */
496 int iStart
, /* Begin with this column of pList */
497 int nExtra
/* Add this many extra columns to the end */
501 ** Generate code that will push the record in registers regData
502 ** through regData+nData-1 onto the sorter.
504 static void pushOntoSorter(
505 Parse
*pParse
, /* Parser context */
506 SortCtx
*pSort
, /* Information about the ORDER BY clause */
507 Select
*pSelect
, /* The whole SELECT statement */
508 int regData
, /* First register holding data to be sorted */
509 int regOrigData
, /* First register holding data before packing */
510 int nData
, /* Number of elements in the data array */
511 int nPrefixReg
/* No. of reg prior to regData available for use */
513 Vdbe
*v
= pParse
->pVdbe
; /* Stmt under construction */
514 int bSeq
= ((pSort
->sortFlags
& SORTFLAG_UseSorter
)==0);
515 int nExpr
= pSort
->pOrderBy
->nExpr
; /* No. of ORDER BY terms */
516 int nBase
= nExpr
+ bSeq
+ nData
; /* Fields in sorter record */
517 int regBase
; /* Regs for sorter record */
518 int regRecord
= ++pParse
->nMem
; /* Assembled sorter record */
519 int nOBSat
= pSort
->nOBSat
; /* ORDER BY terms to skip */
520 int op
; /* Opcode to add sorter record to sorter */
521 int iLimit
; /* LIMIT counter */
523 assert( bSeq
==0 || bSeq
==1 );
524 assert( nData
==1 || regData
==regOrigData
);
526 assert( nPrefixReg
==nExpr
+bSeq
);
527 regBase
= regData
- nExpr
- bSeq
;
529 regBase
= pParse
->nMem
+ 1;
530 pParse
->nMem
+= nBase
;
532 assert( pSelect
->iOffset
==0 || pSelect
->iLimit
!=0 );
533 iLimit
= pSelect
->iOffset
? pSelect
->iOffset
+1 : pSelect
->iLimit
;
534 pSort
->labelDone
= sqlite3VdbeMakeLabel(v
);
535 sqlite3ExprCodeExprList(pParse
, pSort
->pOrderBy
, regBase
, regOrigData
,
536 SQLITE_ECEL_DUP
|SQLITE_ECEL_REF
);
538 sqlite3VdbeAddOp2(v
, OP_Sequence
, pSort
->iECursor
, regBase
+nExpr
);
541 sqlite3ExprCodeMove(pParse
, regData
, regBase
+nExpr
+bSeq
, nData
);
543 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regBase
+nOBSat
, nBase
-nOBSat
, regRecord
);
545 int regPrevKey
; /* The first nOBSat columns of the previous row */
546 int addrFirst
; /* Address of the OP_IfNot opcode */
547 int addrJmp
; /* Address of the OP_Jump opcode */
548 VdbeOp
*pOp
; /* Opcode that opens the sorter */
549 int nKey
; /* Number of sorting key columns, including OP_Sequence */
550 KeyInfo
*pKI
; /* Original KeyInfo on the sorter table */
552 regPrevKey
= pParse
->nMem
+1;
553 pParse
->nMem
+= pSort
->nOBSat
;
554 nKey
= nExpr
- pSort
->nOBSat
+ bSeq
;
556 addrFirst
= sqlite3VdbeAddOp1(v
, OP_IfNot
, regBase
+nExpr
);
558 addrFirst
= sqlite3VdbeAddOp1(v
, OP_SequenceTest
, pSort
->iECursor
);
561 sqlite3VdbeAddOp3(v
, OP_Compare
, regPrevKey
, regBase
, pSort
->nOBSat
);
562 pOp
= sqlite3VdbeGetOp(v
, pSort
->addrSortIndex
);
563 if( pParse
->db
->mallocFailed
) return;
564 pOp
->p2
= nKey
+ nData
;
565 pKI
= pOp
->p4
.pKeyInfo
;
566 memset(pKI
->aSortOrder
, 0, pKI
->nField
); /* Makes OP_Jump below testable */
567 sqlite3VdbeChangeP4(v
, -1, (char*)pKI
, P4_KEYINFO
);
568 testcase( pKI
->nXField
>2 );
569 pOp
->p4
.pKeyInfo
= keyInfoFromExprList(pParse
, pSort
->pOrderBy
, nOBSat
,
571 addrJmp
= sqlite3VdbeCurrentAddr(v
);
572 sqlite3VdbeAddOp3(v
, OP_Jump
, addrJmp
+1, 0, addrJmp
+1); VdbeCoverage(v
);
573 pSort
->labelBkOut
= sqlite3VdbeMakeLabel(v
);
574 pSort
->regReturn
= ++pParse
->nMem
;
575 sqlite3VdbeAddOp2(v
, OP_Gosub
, pSort
->regReturn
, pSort
->labelBkOut
);
576 sqlite3VdbeAddOp1(v
, OP_ResetSorter
, pSort
->iECursor
);
578 sqlite3VdbeAddOp2(v
, OP_IfNot
, iLimit
, pSort
->labelDone
);
581 sqlite3VdbeJumpHere(v
, addrFirst
);
582 sqlite3ExprCodeMove(pParse
, regBase
, regPrevKey
, pSort
->nOBSat
);
583 sqlite3VdbeJumpHere(v
, addrJmp
);
585 if( pSort
->sortFlags
& SORTFLAG_UseSorter
){
586 op
= OP_SorterInsert
;
590 sqlite3VdbeAddOp2(v
, op
, pSort
->iECursor
, regRecord
);
594 /* Fill the sorter until it contains LIMIT+OFFSET entries. (The iLimit
595 ** register is initialized with value of LIMIT+OFFSET.) After the sorter
596 ** fills up, delete the least entry in the sorter after each insert.
597 ** Thus we never hold more than the LIMIT+OFFSET rows in memory at once */
598 addr
= sqlite3VdbeAddOp3(v
, OP_IfNotZero
, iLimit
, 0, 1); VdbeCoverage(v
);
599 sqlite3VdbeAddOp1(v
, OP_Last
, pSort
->iECursor
);
600 if( pSort
->bOrderedInnerLoop
){
602 sqlite3VdbeAddOp3(v
, OP_Column
, pSort
->iECursor
, nExpr
, r1
);
603 VdbeComment((v
, "seq"));
605 sqlite3VdbeAddOp1(v
, OP_Delete
, pSort
->iECursor
);
606 if( pSort
->bOrderedInnerLoop
){
607 /* If the inner loop is driven by an index such that values from
608 ** the same iteration of the inner loop are in sorted order, then
609 ** immediately jump to the next iteration of an inner loop if the
610 ** entry from the current iteration does not fit into the top
611 ** LIMIT+OFFSET entries of the sorter. */
612 int iBrk
= sqlite3VdbeCurrentAddr(v
) + 2;
613 sqlite3VdbeAddOp3(v
, OP_Eq
, regBase
+nExpr
, iBrk
, r1
);
614 sqlite3VdbeChangeP5(v
, SQLITE_NULLEQ
);
617 sqlite3VdbeJumpHere(v
, addr
);
622 ** Add code to implement the OFFSET
624 static void codeOffset(
625 Vdbe
*v
, /* Generate code into this VM */
626 int iOffset
, /* Register holding the offset counter */
627 int iContinue
/* Jump here to skip the current record */
630 sqlite3VdbeAddOp3(v
, OP_IfPos
, iOffset
, iContinue
, 1); VdbeCoverage(v
);
631 VdbeComment((v
, "OFFSET"));
636 ** Add code that will check to make sure the N registers starting at iMem
637 ** form a distinct entry. iTab is a sorting index that holds previously
638 ** seen combinations of the N values. A new entry is made in iTab
639 ** if the current N values are new.
641 ** A jump to addrRepeat is made and the N+1 values are popped from the
642 ** stack if the top N elements are not distinct.
644 static void codeDistinct(
645 Parse
*pParse
, /* Parsing and code generating context */
646 int iTab
, /* A sorting index used to test for distinctness */
647 int addrRepeat
, /* Jump to here if not distinct */
648 int N
, /* Number of elements */
649 int iMem
/* First element */
655 r1
= sqlite3GetTempReg(pParse
);
656 sqlite3VdbeAddOp4Int(v
, OP_Found
, iTab
, addrRepeat
, iMem
, N
); VdbeCoverage(v
);
657 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, iMem
, N
, r1
);
658 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iTab
, r1
);
659 sqlite3ReleaseTempReg(pParse
, r1
);
663 ** This routine generates the code for the inside of the inner loop
666 ** If srcTab is negative, then the pEList expressions
667 ** are evaluated in order to get the data for this row. If srcTab is
668 ** zero or more, then data is pulled from srcTab and pEList is used only
669 ** to get number columns and the datatype for each column.
671 static void selectInnerLoop(
672 Parse
*pParse
, /* The parser context */
673 Select
*p
, /* The complete select statement being coded */
674 ExprList
*pEList
, /* List of values being extracted */
675 int srcTab
, /* Pull data from this table */
676 SortCtx
*pSort
, /* If not NULL, info on how to process ORDER BY */
677 DistinctCtx
*pDistinct
, /* If not NULL, info on how to process DISTINCT */
678 SelectDest
*pDest
, /* How to dispose of the results */
679 int iContinue
, /* Jump here to continue with next row */
680 int iBreak
/* Jump here to break out of the inner loop */
682 Vdbe
*v
= pParse
->pVdbe
;
684 int hasDistinct
; /* True if the DISTINCT keyword is present */
685 int regResult
; /* Start of memory holding result set */
686 int eDest
= pDest
->eDest
; /* How to dispose of results */
687 int iParm
= pDest
->iSDParm
; /* First argument to disposal method */
688 int nResultCol
; /* Number of result columns */
689 int nPrefixReg
= 0; /* Number of extra registers before regResult */
693 hasDistinct
= pDistinct
? pDistinct
->eTnctType
: WHERE_DISTINCT_NOOP
;
694 if( pSort
&& pSort
->pOrderBy
==0 ) pSort
= 0;
695 if( pSort
==0 && !hasDistinct
){
696 assert( iContinue
!=0 );
697 codeOffset(v
, p
->iOffset
, iContinue
);
700 /* Pull the requested columns.
702 nResultCol
= pEList
->nExpr
;
704 if( pDest
->iSdst
==0 ){
706 nPrefixReg
= pSort
->pOrderBy
->nExpr
;
707 if( !(pSort
->sortFlags
& SORTFLAG_UseSorter
) ) nPrefixReg
++;
708 pParse
->nMem
+= nPrefixReg
;
710 pDest
->iSdst
= pParse
->nMem
+1;
711 pParse
->nMem
+= nResultCol
;
712 }else if( pDest
->iSdst
+nResultCol
> pParse
->nMem
){
713 /* This is an error condition that can result, for example, when a SELECT
714 ** on the right-hand side of an INSERT contains more result columns than
715 ** there are columns in the table on the left. The error will be caught
716 ** and reported later. But we need to make sure enough memory is allocated
717 ** to avoid other spurious errors in the meantime. */
718 pParse
->nMem
+= nResultCol
;
720 pDest
->nSdst
= nResultCol
;
721 regResult
= pDest
->iSdst
;
723 for(i
=0; i
<nResultCol
; i
++){
724 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, i
, regResult
+i
);
725 VdbeComment((v
, "%s", pEList
->a
[i
].zName
));
727 }else if( eDest
!=SRT_Exists
){
728 /* If the destination is an EXISTS(...) expression, the actual
729 ** values returned by the SELECT are not required.
732 if( eDest
==SRT_Mem
|| eDest
==SRT_Output
|| eDest
==SRT_Coroutine
){
733 ecelFlags
= SQLITE_ECEL_DUP
;
737 sqlite3ExprCodeExprList(pParse
, pEList
, regResult
, 0, ecelFlags
);
740 /* If the DISTINCT keyword was present on the SELECT statement
741 ** and this row has been seen before, then do not make this row
742 ** part of the result.
745 switch( pDistinct
->eTnctType
){
746 case WHERE_DISTINCT_ORDERED
: {
747 VdbeOp
*pOp
; /* No longer required OpenEphemeral instr. */
748 int iJump
; /* Jump destination */
749 int regPrev
; /* Previous row content */
751 /* Allocate space for the previous row */
752 regPrev
= pParse
->nMem
+1;
753 pParse
->nMem
+= nResultCol
;
755 /* Change the OP_OpenEphemeral coded earlier to an OP_Null
756 ** sets the MEM_Cleared bit on the first register of the
757 ** previous value. This will cause the OP_Ne below to always
758 ** fail on the first iteration of the loop even if the first
761 sqlite3VdbeChangeToNoop(v
, pDistinct
->addrTnct
);
762 pOp
= sqlite3VdbeGetOp(v
, pDistinct
->addrTnct
);
763 pOp
->opcode
= OP_Null
;
767 iJump
= sqlite3VdbeCurrentAddr(v
) + nResultCol
;
768 for(i
=0; i
<nResultCol
; i
++){
769 CollSeq
*pColl
= sqlite3ExprCollSeq(pParse
, pEList
->a
[i
].pExpr
);
770 if( i
<nResultCol
-1 ){
771 sqlite3VdbeAddOp3(v
, OP_Ne
, regResult
+i
, iJump
, regPrev
+i
);
774 sqlite3VdbeAddOp3(v
, OP_Eq
, regResult
+i
, iContinue
, regPrev
+i
);
777 sqlite3VdbeChangeP4(v
, -1, (const char *)pColl
, P4_COLLSEQ
);
778 sqlite3VdbeChangeP5(v
, SQLITE_NULLEQ
);
780 assert( sqlite3VdbeCurrentAddr(v
)==iJump
|| pParse
->db
->mallocFailed
);
781 sqlite3VdbeAddOp3(v
, OP_Copy
, regResult
, regPrev
, nResultCol
-1);
785 case WHERE_DISTINCT_UNIQUE
: {
786 sqlite3VdbeChangeToNoop(v
, pDistinct
->addrTnct
);
791 assert( pDistinct
->eTnctType
==WHERE_DISTINCT_UNORDERED
);
792 codeDistinct(pParse
, pDistinct
->tabTnct
, iContinue
, nResultCol
,
798 codeOffset(v
, p
->iOffset
, iContinue
);
803 /* In this mode, write each query result to the key of the temporary
806 #ifndef SQLITE_OMIT_COMPOUND_SELECT
809 r1
= sqlite3GetTempReg(pParse
);
810 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nResultCol
, r1
);
811 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
, r1
);
812 sqlite3ReleaseTempReg(pParse
, r1
);
816 /* Construct a record from the query result, but instead of
817 ** saving that record, use it as a key to delete elements from
818 ** the temporary table iParm.
821 sqlite3VdbeAddOp3(v
, OP_IdxDelete
, iParm
, regResult
, nResultCol
);
824 #endif /* SQLITE_OMIT_COMPOUND_SELECT */
826 /* Store the result as data using a unique key.
832 int r1
= sqlite3GetTempRange(pParse
, nPrefixReg
+1);
833 testcase( eDest
==SRT_Table
);
834 testcase( eDest
==SRT_EphemTab
);
835 testcase( eDest
==SRT_Fifo
);
836 testcase( eDest
==SRT_DistFifo
);
837 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nResultCol
, r1
+nPrefixReg
);
838 #ifndef SQLITE_OMIT_CTE
839 if( eDest
==SRT_DistFifo
){
840 /* If the destination is DistFifo, then cursor (iParm+1) is open
841 ** on an ephemeral index. If the current row is already present
842 ** in the index, do not write it to the output. If not, add the
843 ** current row to the index and proceed with writing it to the
844 ** output table as well. */
845 int addr
= sqlite3VdbeCurrentAddr(v
) + 4;
846 sqlite3VdbeAddOp4Int(v
, OP_Found
, iParm
+1, addr
, r1
, 0);
848 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
+1, r1
);
853 pushOntoSorter(pParse
, pSort
, p
, r1
+nPrefixReg
,regResult
,1,nPrefixReg
);
855 int r2
= sqlite3GetTempReg(pParse
);
856 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iParm
, r2
);
857 sqlite3VdbeAddOp3(v
, OP_Insert
, iParm
, r1
, r2
);
858 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
859 sqlite3ReleaseTempReg(pParse
, r2
);
861 sqlite3ReleaseTempRange(pParse
, r1
, nPrefixReg
+1);
865 #ifndef SQLITE_OMIT_SUBQUERY
866 /* If we are creating a set for an "expr IN (SELECT ...)" construct,
867 ** then there should be a single item on the stack. Write this
868 ** item into the set table with bogus data.
872 /* At first glance you would think we could optimize out the
873 ** ORDER BY in this case since the order of entries in the set
874 ** does not matter. But there might be a LIMIT clause, in which
875 ** case the order does matter */
877 pParse
, pSort
, p
, regResult
, regResult
, nResultCol
, nPrefixReg
);
879 int r1
= sqlite3GetTempReg(pParse
);
880 assert( sqlite3Strlen30(pDest
->zAffSdst
)==nResultCol
);
881 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regResult
, nResultCol
,
882 r1
, pDest
->zAffSdst
, nResultCol
);
883 sqlite3ExprCacheAffinityChange(pParse
, regResult
, nResultCol
);
884 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
, r1
);
885 sqlite3ReleaseTempReg(pParse
, r1
);
890 /* If any row exist in the result set, record that fact and abort.
893 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iParm
);
894 /* The LIMIT clause will terminate the loop for us */
898 /* If this is a scalar select that is part of an expression, then
899 ** store the results in the appropriate memory cell or array of
900 ** memory cells and break out of the scan loop.
903 assert( nResultCol
==pDest
->nSdst
);
906 pParse
, pSort
, p
, regResult
, regResult
, nResultCol
, nPrefixReg
);
908 assert( regResult
==iParm
);
909 /* The LIMIT clause will jump out of the loop for us */
913 #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
915 case SRT_Coroutine
: /* Send data to a co-routine */
916 case SRT_Output
: { /* Return the results */
917 testcase( eDest
==SRT_Coroutine
);
918 testcase( eDest
==SRT_Output
);
920 pushOntoSorter(pParse
, pSort
, p
, regResult
, regResult
, nResultCol
,
922 }else if( eDest
==SRT_Coroutine
){
923 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iSDParm
);
925 sqlite3VdbeAddOp2(v
, OP_ResultRow
, regResult
, nResultCol
);
926 sqlite3ExprCacheAffinityChange(pParse
, regResult
, nResultCol
);
931 #ifndef SQLITE_OMIT_CTE
932 /* Write the results into a priority queue that is order according to
933 ** pDest->pOrderBy (in pSO). pDest->iSDParm (in iParm) is the cursor for an
934 ** index with pSO->nExpr+2 columns. Build a key using pSO for the first
935 ** pSO->nExpr columns, then make sure all keys are unique by adding a
936 ** final OP_Sequence column. The last column is the record as a blob.
944 pSO
= pDest
->pOrderBy
;
947 r1
= sqlite3GetTempReg(pParse
);
948 r2
= sqlite3GetTempRange(pParse
, nKey
+2);
950 if( eDest
==SRT_DistQueue
){
951 /* If the destination is DistQueue, then cursor (iParm+1) is open
952 ** on a second ephemeral index that holds all values every previously
953 ** added to the queue. */
954 addrTest
= sqlite3VdbeAddOp4Int(v
, OP_Found
, iParm
+1, 0,
955 regResult
, nResultCol
);
958 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nResultCol
, r3
);
959 if( eDest
==SRT_DistQueue
){
960 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
+1, r3
);
961 sqlite3VdbeChangeP5(v
, OPFLAG_USESEEKRESULT
);
963 for(i
=0; i
<nKey
; i
++){
964 sqlite3VdbeAddOp2(v
, OP_SCopy
,
965 regResult
+ pSO
->a
[i
].u
.x
.iOrderByCol
- 1,
968 sqlite3VdbeAddOp2(v
, OP_Sequence
, iParm
, r2
+nKey
);
969 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, r2
, nKey
+2, r1
);
970 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
, r1
);
971 if( addrTest
) sqlite3VdbeJumpHere(v
, addrTest
);
972 sqlite3ReleaseTempReg(pParse
, r1
);
973 sqlite3ReleaseTempRange(pParse
, r2
, nKey
+2);
976 #endif /* SQLITE_OMIT_CTE */
980 #if !defined(SQLITE_OMIT_TRIGGER)
981 /* Discard the results. This is used for SELECT statements inside
982 ** the body of a TRIGGER. The purpose of such selects is to call
983 ** user-defined functions that have side effects. We do not care
984 ** about the actual results of the select.
987 assert( eDest
==SRT_Discard
);
993 /* Jump to the end of the loop if the LIMIT is reached. Except, if
994 ** there is a sorter, in which case the sorter has already limited
995 ** the output for us.
997 if( pSort
==0 && p
->iLimit
){
998 sqlite3VdbeAddOp2(v
, OP_DecrJumpZero
, p
->iLimit
, iBreak
); VdbeCoverage(v
);
1003 ** Allocate a KeyInfo object sufficient for an index of N key columns and
1006 KeyInfo
*sqlite3KeyInfoAlloc(sqlite3
*db
, int N
, int X
){
1007 int nExtra
= (N
+X
)*(sizeof(CollSeq
*)+1);
1008 KeyInfo
*p
= sqlite3DbMallocRawNN(db
, sizeof(KeyInfo
) + nExtra
);
1010 p
->aSortOrder
= (u8
*)&p
->aColl
[N
+X
];
1012 p
->nXField
= (u16
)X
;
1016 memset(&p
[1], 0, nExtra
);
1018 sqlite3OomFault(db
);
1024 ** Deallocate a KeyInfo object
1026 void sqlite3KeyInfoUnref(KeyInfo
*p
){
1028 assert( p
->nRef
>0 );
1030 if( p
->nRef
==0 ) sqlite3DbFree(p
->db
, p
);
1035 ** Make a new pointer to a KeyInfo object
1037 KeyInfo
*sqlite3KeyInfoRef(KeyInfo
*p
){
1039 assert( p
->nRef
>0 );
1047 ** Return TRUE if a KeyInfo object can be change. The KeyInfo object
1048 ** can only be changed if this is just a single reference to the object.
1050 ** This routine is used only inside of assert() statements.
1052 int sqlite3KeyInfoIsWriteable(KeyInfo
*p
){ return p
->nRef
==1; }
1053 #endif /* SQLITE_DEBUG */
1056 ** Given an expression list, generate a KeyInfo structure that records
1057 ** the collating sequence for each expression in that expression list.
1059 ** If the ExprList is an ORDER BY or GROUP BY clause then the resulting
1060 ** KeyInfo structure is appropriate for initializing a virtual index to
1061 ** implement that clause. If the ExprList is the result set of a SELECT
1062 ** then the KeyInfo structure is appropriate for initializing a virtual
1063 ** index to implement a DISTINCT test.
1065 ** Space to hold the KeyInfo structure is obtained from malloc. The calling
1066 ** function is responsible for seeing that this structure is eventually
1069 static KeyInfo
*keyInfoFromExprList(
1070 Parse
*pParse
, /* Parsing context */
1071 ExprList
*pList
, /* Form the KeyInfo object from this ExprList */
1072 int iStart
, /* Begin with this column of pList */
1073 int nExtra
/* Add this many extra columns to the end */
1077 struct ExprList_item
*pItem
;
1078 sqlite3
*db
= pParse
->db
;
1081 nExpr
= pList
->nExpr
;
1082 pInfo
= sqlite3KeyInfoAlloc(db
, nExpr
-iStart
, nExtra
+1);
1084 assert( sqlite3KeyInfoIsWriteable(pInfo
) );
1085 for(i
=iStart
, pItem
=pList
->a
+iStart
; i
<nExpr
; i
++, pItem
++){
1087 pColl
= sqlite3ExprCollSeq(pParse
, pItem
->pExpr
);
1088 if( !pColl
) pColl
= db
->pDfltColl
;
1089 pInfo
->aColl
[i
-iStart
] = pColl
;
1090 pInfo
->aSortOrder
[i
-iStart
] = pItem
->sortOrder
;
1097 ** Name of the connection operator, used for error messages.
1099 static const char *selectOpName(int id
){
1102 case TK_ALL
: z
= "UNION ALL"; break;
1103 case TK_INTERSECT
: z
= "INTERSECT"; break;
1104 case TK_EXCEPT
: z
= "EXCEPT"; break;
1105 default: z
= "UNION"; break;
1110 #ifndef SQLITE_OMIT_EXPLAIN
1112 ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
1113 ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
1114 ** where the caption is of the form:
1116 ** "USE TEMP B-TREE FOR xxx"
1118 ** where xxx is one of "DISTINCT", "ORDER BY" or "GROUP BY". Exactly which
1119 ** is determined by the zUsage argument.
1121 static void explainTempTable(Parse
*pParse
, const char *zUsage
){
1122 if( pParse
->explain
==2 ){
1123 Vdbe
*v
= pParse
->pVdbe
;
1124 char *zMsg
= sqlite3MPrintf(pParse
->db
, "USE TEMP B-TREE FOR %s", zUsage
);
1125 sqlite3VdbeAddOp4(v
, OP_Explain
, pParse
->iSelectId
, 0, 0, zMsg
, P4_DYNAMIC
);
1130 ** Assign expression b to lvalue a. A second, no-op, version of this macro
1131 ** is provided when SQLITE_OMIT_EXPLAIN is defined. This allows the code
1132 ** in sqlite3Select() to assign values to structure member variables that
1133 ** only exist if SQLITE_OMIT_EXPLAIN is not defined without polluting the
1134 ** code with #ifndef directives.
1136 # define explainSetInteger(a, b) a = b
1139 /* No-op versions of the explainXXX() functions and macros. */
1140 # define explainTempTable(y,z)
1141 # define explainSetInteger(y,z)
1144 #if !defined(SQLITE_OMIT_EXPLAIN) && !defined(SQLITE_OMIT_COMPOUND_SELECT)
1146 ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
1147 ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
1148 ** where the caption is of one of the two forms:
1150 ** "COMPOSITE SUBQUERIES iSub1 and iSub2 (op)"
1151 ** "COMPOSITE SUBQUERIES iSub1 and iSub2 USING TEMP B-TREE (op)"
1153 ** where iSub1 and iSub2 are the integers passed as the corresponding
1154 ** function parameters, and op is the text representation of the parameter
1155 ** of the same name. The parameter "op" must be one of TK_UNION, TK_EXCEPT,
1156 ** TK_INTERSECT or TK_ALL. The first form is used if argument bUseTmp is
1157 ** false, or the second form if it is true.
1159 static void explainComposite(
1160 Parse
*pParse
, /* Parse context */
1161 int op
, /* One of TK_UNION, TK_EXCEPT etc. */
1162 int iSub1
, /* Subquery id 1 */
1163 int iSub2
, /* Subquery id 2 */
1164 int bUseTmp
/* True if a temp table was used */
1166 assert( op
==TK_UNION
|| op
==TK_EXCEPT
|| op
==TK_INTERSECT
|| op
==TK_ALL
);
1167 if( pParse
->explain
==2 ){
1168 Vdbe
*v
= pParse
->pVdbe
;
1169 char *zMsg
= sqlite3MPrintf(
1170 pParse
->db
, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1
, iSub2
,
1171 bUseTmp
?"USING TEMP B-TREE ":"", selectOpName(op
)
1173 sqlite3VdbeAddOp4(v
, OP_Explain
, pParse
->iSelectId
, 0, 0, zMsg
, P4_DYNAMIC
);
1177 /* No-op versions of the explainXXX() functions and macros. */
1178 # define explainComposite(v,w,x,y,z)
1182 ** If the inner loop was generated using a non-null pOrderBy argument,
1183 ** then the results were placed in a sorter. After the loop is terminated
1184 ** we need to run the sorter and output the results. The following
1185 ** routine generates the code needed to do that.
1187 static void generateSortTail(
1188 Parse
*pParse
, /* Parsing context */
1189 Select
*p
, /* The SELECT statement */
1190 SortCtx
*pSort
, /* Information on the ORDER BY clause */
1191 int nColumn
, /* Number of columns of data */
1192 SelectDest
*pDest
/* Write the sorted results here */
1194 Vdbe
*v
= pParse
->pVdbe
; /* The prepared statement */
1195 int addrBreak
= pSort
->labelDone
; /* Jump here to exit loop */
1196 int addrContinue
= sqlite3VdbeMakeLabel(v
); /* Jump here for next cycle */
1200 ExprList
*pOrderBy
= pSort
->pOrderBy
;
1201 int eDest
= pDest
->eDest
;
1202 int iParm
= pDest
->iSDParm
;
1206 int iSortTab
; /* Sorter cursor to read from */
1207 int nSortData
; /* Trailing values to read from sorter */
1209 int bSeq
; /* True if sorter record includes seq. no. */
1210 #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
1211 struct ExprList_item
*aOutEx
= p
->pEList
->a
;
1214 assert( addrBreak
<0 );
1215 if( pSort
->labelBkOut
){
1216 sqlite3VdbeAddOp2(v
, OP_Gosub
, pSort
->regReturn
, pSort
->labelBkOut
);
1217 sqlite3VdbeGoto(v
, addrBreak
);
1218 sqlite3VdbeResolveLabel(v
, pSort
->labelBkOut
);
1220 iTab
= pSort
->iECursor
;
1221 if( eDest
==SRT_Output
|| eDest
==SRT_Coroutine
|| eDest
==SRT_Mem
){
1223 regRow
= pDest
->iSdst
;
1224 nSortData
= nColumn
;
1226 regRowid
= sqlite3GetTempReg(pParse
);
1227 regRow
= sqlite3GetTempRange(pParse
, nColumn
);
1228 nSortData
= nColumn
;
1230 nKey
= pOrderBy
->nExpr
- pSort
->nOBSat
;
1231 if( pSort
->sortFlags
& SORTFLAG_UseSorter
){
1232 int regSortOut
= ++pParse
->nMem
;
1233 iSortTab
= pParse
->nTab
++;
1234 if( pSort
->labelBkOut
){
1235 addrOnce
= sqlite3VdbeAddOp0(v
, OP_Once
); VdbeCoverage(v
);
1237 sqlite3VdbeAddOp3(v
, OP_OpenPseudo
, iSortTab
, regSortOut
, nKey
+1+nSortData
);
1238 if( addrOnce
) sqlite3VdbeJumpHere(v
, addrOnce
);
1239 addr
= 1 + sqlite3VdbeAddOp2(v
, OP_SorterSort
, iTab
, addrBreak
);
1241 codeOffset(v
, p
->iOffset
, addrContinue
);
1242 sqlite3VdbeAddOp3(v
, OP_SorterData
, iTab
, regSortOut
, iSortTab
);
1245 addr
= 1 + sqlite3VdbeAddOp2(v
, OP_Sort
, iTab
, addrBreak
); VdbeCoverage(v
);
1246 codeOffset(v
, p
->iOffset
, addrContinue
);
1250 for(i
=0; i
<nSortData
; i
++){
1251 sqlite3VdbeAddOp3(v
, OP_Column
, iSortTab
, nKey
+bSeq
+i
, regRow
+i
);
1252 VdbeComment((v
, "%s", aOutEx
[i
].zName
? aOutEx
[i
].zName
: aOutEx
[i
].zSpan
));
1255 case SRT_EphemTab
: {
1256 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iParm
, regRowid
);
1257 sqlite3VdbeAddOp3(v
, OP_Insert
, iParm
, regRow
, regRowid
);
1258 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
1261 #ifndef SQLITE_OMIT_SUBQUERY
1263 assert( nColumn
==sqlite3Strlen30(pDest
->zAffSdst
) );
1264 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regRow
, nColumn
, regRowid
,
1265 pDest
->zAffSdst
, nColumn
);
1266 sqlite3ExprCacheAffinityChange(pParse
, regRow
, nColumn
);
1267 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
, regRowid
);
1271 /* The LIMIT clause will terminate the loop for us */
1276 assert( eDest
==SRT_Output
|| eDest
==SRT_Coroutine
);
1277 testcase( eDest
==SRT_Output
);
1278 testcase( eDest
==SRT_Coroutine
);
1279 if( eDest
==SRT_Output
){
1280 sqlite3VdbeAddOp2(v
, OP_ResultRow
, pDest
->iSdst
, nColumn
);
1281 sqlite3ExprCacheAffinityChange(pParse
, pDest
->iSdst
, nColumn
);
1283 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iSDParm
);
1289 if( eDest
==SRT_Set
){
1290 sqlite3ReleaseTempRange(pParse
, regRow
, nColumn
);
1292 sqlite3ReleaseTempReg(pParse
, regRow
);
1294 sqlite3ReleaseTempReg(pParse
, regRowid
);
1296 /* The bottom of the loop
1298 sqlite3VdbeResolveLabel(v
, addrContinue
);
1299 if( pSort
->sortFlags
& SORTFLAG_UseSorter
){
1300 sqlite3VdbeAddOp2(v
, OP_SorterNext
, iTab
, addr
); VdbeCoverage(v
);
1302 sqlite3VdbeAddOp2(v
, OP_Next
, iTab
, addr
); VdbeCoverage(v
);
1304 if( pSort
->regReturn
) sqlite3VdbeAddOp1(v
, OP_Return
, pSort
->regReturn
);
1305 sqlite3VdbeResolveLabel(v
, addrBreak
);
1309 ** Return a pointer to a string containing the 'declaration type' of the
1310 ** expression pExpr. The string may be treated as static by the caller.
1312 ** Also try to estimate the size of the returned value and return that
1313 ** result in *pEstWidth.
1315 ** The declaration type is the exact datatype definition extracted from the
1316 ** original CREATE TABLE statement if the expression is a column. The
1317 ** declaration type for a ROWID field is INTEGER. Exactly when an expression
1318 ** is considered a column can be complex in the presence of subqueries. The
1319 ** result-set expression in all of the following SELECT statements is
1320 ** considered a column by this function.
1322 ** SELECT col FROM tbl;
1323 ** SELECT (SELECT col FROM tbl;
1324 ** SELECT (SELECT col FROM tbl);
1325 ** SELECT abc FROM (SELECT col AS abc FROM tbl);
1327 ** The declaration type for any expression other than a column is NULL.
1329 ** This routine has either 3 or 6 parameters depending on whether or not
1330 ** the SQLITE_ENABLE_COLUMN_METADATA compile-time option is used.
1332 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1333 # define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,C,D,E,F)
1334 #else /* if !defined(SQLITE_ENABLE_COLUMN_METADATA) */
1335 # define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,F)
1337 static const char *columnTypeImpl(
1340 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1341 const char **pzOrigDb
,
1342 const char **pzOrigTab
,
1343 const char **pzOrigCol
,
1347 char const *zType
= 0;
1350 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1351 char const *zOrigDb
= 0;
1352 char const *zOrigTab
= 0;
1353 char const *zOrigCol
= 0;
1357 assert( pNC
->pSrcList
!=0 );
1358 switch( pExpr
->op
){
1361 /* The expression is a column. Locate the table the column is being
1362 ** extracted from in NameContext.pSrcList. This table may be real
1363 ** database table or a subquery.
1365 Table
*pTab
= 0; /* Table structure column is extracted from */
1366 Select
*pS
= 0; /* Select the column is extracted from */
1367 int iCol
= pExpr
->iColumn
; /* Index of column in pTab */
1368 testcase( pExpr
->op
==TK_AGG_COLUMN
);
1369 testcase( pExpr
->op
==TK_COLUMN
);
1370 while( pNC
&& !pTab
){
1371 SrcList
*pTabList
= pNC
->pSrcList
;
1372 for(j
=0;j
<pTabList
->nSrc
&& pTabList
->a
[j
].iCursor
!=pExpr
->iTable
;j
++);
1373 if( j
<pTabList
->nSrc
){
1374 pTab
= pTabList
->a
[j
].pTab
;
1375 pS
= pTabList
->a
[j
].pSelect
;
1382 /* At one time, code such as "SELECT new.x" within a trigger would
1383 ** cause this condition to run. Since then, we have restructured how
1384 ** trigger code is generated and so this condition is no longer
1385 ** possible. However, it can still be true for statements like
1388 ** CREATE TABLE t1(col INTEGER);
1389 ** SELECT (SELECT t1.col) FROM FROM t1;
1391 ** when columnType() is called on the expression "t1.col" in the
1392 ** sub-select. In this case, set the column type to NULL, even
1393 ** though it should really be "INTEGER".
1395 ** This is not a problem, as the column type of "t1.col" is never
1396 ** used. When columnType() is called on the expression
1397 ** "(SELECT t1.col)", the correct type is returned (see the TK_SELECT
1402 assert( pTab
&& pExpr
->pTab
==pTab
);
1404 /* The "table" is actually a sub-select or a view in the FROM clause
1405 ** of the SELECT statement. Return the declaration type and origin
1406 ** data for the result-set column of the sub-select.
1408 if( iCol
>=0 && ALWAYS(iCol
<pS
->pEList
->nExpr
) ){
1409 /* If iCol is less than zero, then the expression requests the
1410 ** rowid of the sub-select or view. This expression is legal (see
1411 ** test case misc2.2.2) - it always evaluates to NULL.
1413 ** The ALWAYS() is because iCol>=pS->pEList->nExpr will have been
1414 ** caught already by name resolution.
1417 Expr
*p
= pS
->pEList
->a
[iCol
].pExpr
;
1418 sNC
.pSrcList
= pS
->pSrc
;
1420 sNC
.pParse
= pNC
->pParse
;
1421 zType
= columnType(&sNC
, p
,&zOrigDb
,&zOrigTab
,&zOrigCol
, &estWidth
);
1423 }else if( pTab
->pSchema
){
1426 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1427 assert( iCol
==-1 || (iCol
>=0 && iCol
<pTab
->nCol
) );
1428 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1433 zOrigCol
= pTab
->aCol
[iCol
].zName
;
1434 zType
= sqlite3ColumnType(&pTab
->aCol
[iCol
],0);
1435 estWidth
= pTab
->aCol
[iCol
].szEst
;
1437 zOrigTab
= pTab
->zName
;
1439 int iDb
= sqlite3SchemaToIndex(pNC
->pParse
->db
, pTab
->pSchema
);
1440 zOrigDb
= pNC
->pParse
->db
->aDb
[iDb
].zDbSName
;
1446 zType
= sqlite3ColumnType(&pTab
->aCol
[iCol
],0);
1447 estWidth
= pTab
->aCol
[iCol
].szEst
;
1453 #ifndef SQLITE_OMIT_SUBQUERY
1455 /* The expression is a sub-select. Return the declaration type and
1456 ** origin info for the single column in the result set of the SELECT
1460 Select
*pS
= pExpr
->x
.pSelect
;
1461 Expr
*p
= pS
->pEList
->a
[0].pExpr
;
1462 assert( ExprHasProperty(pExpr
, EP_xIsSelect
) );
1463 sNC
.pSrcList
= pS
->pSrc
;
1465 sNC
.pParse
= pNC
->pParse
;
1466 zType
= columnType(&sNC
, p
, &zOrigDb
, &zOrigTab
, &zOrigCol
, &estWidth
);
1472 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1474 assert( pzOrigTab
&& pzOrigCol
);
1475 *pzOrigDb
= zOrigDb
;
1476 *pzOrigTab
= zOrigTab
;
1477 *pzOrigCol
= zOrigCol
;
1480 if( pEstWidth
) *pEstWidth
= estWidth
;
1485 ** Generate code that will tell the VDBE the declaration types of columns
1486 ** in the result set.
1488 static void generateColumnTypes(
1489 Parse
*pParse
, /* Parser context */
1490 SrcList
*pTabList
, /* List of tables */
1491 ExprList
*pEList
/* Expressions defining the result set */
1493 #ifndef SQLITE_OMIT_DECLTYPE
1494 Vdbe
*v
= pParse
->pVdbe
;
1497 sNC
.pSrcList
= pTabList
;
1498 sNC
.pParse
= pParse
;
1499 for(i
=0; i
<pEList
->nExpr
; i
++){
1500 Expr
*p
= pEList
->a
[i
].pExpr
;
1502 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1503 const char *zOrigDb
= 0;
1504 const char *zOrigTab
= 0;
1505 const char *zOrigCol
= 0;
1506 zType
= columnType(&sNC
, p
, &zOrigDb
, &zOrigTab
, &zOrigCol
, 0);
1508 /* The vdbe must make its own copy of the column-type and other
1509 ** column specific strings, in case the schema is reset before this
1510 ** virtual machine is deleted.
1512 sqlite3VdbeSetColName(v
, i
, COLNAME_DATABASE
, zOrigDb
, SQLITE_TRANSIENT
);
1513 sqlite3VdbeSetColName(v
, i
, COLNAME_TABLE
, zOrigTab
, SQLITE_TRANSIENT
);
1514 sqlite3VdbeSetColName(v
, i
, COLNAME_COLUMN
, zOrigCol
, SQLITE_TRANSIENT
);
1516 zType
= columnType(&sNC
, p
, 0, 0, 0, 0);
1518 sqlite3VdbeSetColName(v
, i
, COLNAME_DECLTYPE
, zType
, SQLITE_TRANSIENT
);
1520 #endif /* !defined(SQLITE_OMIT_DECLTYPE) */
1524 ** Generate code that will tell the VDBE the names of columns
1525 ** in the result set. This information is used to provide the
1526 ** azCol[] values in the callback.
1528 static void generateColumnNames(
1529 Parse
*pParse
, /* Parser context */
1530 SrcList
*pTabList
, /* List of tables */
1531 ExprList
*pEList
/* Expressions defining the result set */
1533 Vdbe
*v
= pParse
->pVdbe
;
1535 sqlite3
*db
= pParse
->db
;
1536 int fullNames
, shortNames
;
1538 #ifndef SQLITE_OMIT_EXPLAIN
1539 /* If this is an EXPLAIN, skip this step */
1540 if( pParse
->explain
){
1545 if( pParse
->colNamesSet
|| db
->mallocFailed
) return;
1547 assert( pTabList
!=0 );
1548 pParse
->colNamesSet
= 1;
1549 fullNames
= (db
->flags
& SQLITE_FullColNames
)!=0;
1550 shortNames
= (db
->flags
& SQLITE_ShortColNames
)!=0;
1551 sqlite3VdbeSetNumCols(v
, pEList
->nExpr
);
1552 for(i
=0; i
<pEList
->nExpr
; i
++){
1554 p
= pEList
->a
[i
].pExpr
;
1555 if( NEVER(p
==0) ) continue;
1556 if( pEList
->a
[i
].zName
){
1557 char *zName
= pEList
->a
[i
].zName
;
1558 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zName
, SQLITE_TRANSIENT
);
1559 }else if( p
->op
==TK_COLUMN
|| p
->op
==TK_AGG_COLUMN
){
1562 int iCol
= p
->iColumn
;
1563 for(j
=0; ALWAYS(j
<pTabList
->nSrc
); j
++){
1564 if( pTabList
->a
[j
].iCursor
==p
->iTable
) break;
1566 assert( j
<pTabList
->nSrc
);
1567 pTab
= pTabList
->a
[j
].pTab
;
1568 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1569 assert( iCol
==-1 || (iCol
>=0 && iCol
<pTab
->nCol
) );
1573 zCol
= pTab
->aCol
[iCol
].zName
;
1575 if( !shortNames
&& !fullNames
){
1576 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
,
1577 sqlite3DbStrDup(db
, pEList
->a
[i
].zSpan
), SQLITE_DYNAMIC
);
1578 }else if( fullNames
){
1580 zName
= sqlite3MPrintf(db
, "%s.%s", pTab
->zName
, zCol
);
1581 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zName
, SQLITE_DYNAMIC
);
1583 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zCol
, SQLITE_TRANSIENT
);
1586 const char *z
= pEList
->a
[i
].zSpan
;
1587 z
= z
==0 ? sqlite3MPrintf(db
, "column%d", i
+1) : sqlite3DbStrDup(db
, z
);
1588 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, z
, SQLITE_DYNAMIC
);
1591 generateColumnTypes(pParse
, pTabList
, pEList
);
1595 ** Given an expression list (which is really the list of expressions
1596 ** that form the result set of a SELECT statement) compute appropriate
1597 ** column names for a table that would hold the expression list.
1599 ** All column names will be unique.
1601 ** Only the column names are computed. Column.zType, Column.zColl,
1602 ** and other fields of Column are zeroed.
1604 ** Return SQLITE_OK on success. If a memory allocation error occurs,
1605 ** store NULL in *paCol and 0 in *pnCol and return SQLITE_NOMEM.
1607 int sqlite3ColumnsFromExprList(
1608 Parse
*pParse
, /* Parsing context */
1609 ExprList
*pEList
, /* Expr list from which to derive column names */
1610 i16
*pnCol
, /* Write the number of columns here */
1611 Column
**paCol
/* Write the new column list here */
1613 sqlite3
*db
= pParse
->db
; /* Database connection */
1614 int i
, j
; /* Loop counters */
1615 u32 cnt
; /* Index added to make the name unique */
1616 Column
*aCol
, *pCol
; /* For looping over result columns */
1617 int nCol
; /* Number of columns in the result set */
1618 Expr
*p
; /* Expression for a single result column */
1619 char *zName
; /* Column name */
1620 int nName
; /* Size of name in zName[] */
1621 Hash ht
; /* Hash table of column names */
1623 sqlite3HashInit(&ht
);
1625 nCol
= pEList
->nExpr
;
1626 aCol
= sqlite3DbMallocZero(db
, sizeof(aCol
[0])*nCol
);
1627 testcase( aCol
==0 );
1632 assert( nCol
==(i16
)nCol
);
1636 for(i
=0, pCol
=aCol
; i
<nCol
&& !db
->mallocFailed
; i
++, pCol
++){
1637 /* Get an appropriate name for the column
1639 p
= sqlite3ExprSkipCollate(pEList
->a
[i
].pExpr
);
1640 if( (zName
= pEList
->a
[i
].zName
)!=0 ){
1641 /* If the column contains an "AS <name>" phrase, use <name> as the name */
1643 Expr
*pColExpr
= p
; /* The expression that is the result column name */
1644 Table
*pTab
; /* Table associated with this expression */
1645 while( pColExpr
->op
==TK_DOT
){
1646 pColExpr
= pColExpr
->pRight
;
1647 assert( pColExpr
!=0 );
1649 if( pColExpr
->op
==TK_COLUMN
&& ALWAYS(pColExpr
->pTab
!=0) ){
1650 /* For columns use the column name name */
1651 int iCol
= pColExpr
->iColumn
;
1652 pTab
= pColExpr
->pTab
;
1653 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1654 zName
= iCol
>=0 ? pTab
->aCol
[iCol
].zName
: "rowid";
1655 }else if( pColExpr
->op
==TK_ID
){
1656 assert( !ExprHasProperty(pColExpr
, EP_IntValue
) );
1657 zName
= pColExpr
->u
.zToken
;
1659 /* Use the original text of the column expression as its name */
1660 zName
= pEList
->a
[i
].zSpan
;
1663 zName
= sqlite3MPrintf(db
, "%s", zName
);
1665 /* Make sure the column name is unique. If the name is not unique,
1666 ** append an integer to the name so that it becomes unique.
1669 while( zName
&& sqlite3HashFind(&ht
, zName
)!=0 ){
1670 nName
= sqlite3Strlen30(zName
);
1672 for(j
=nName
-1; j
>0 && sqlite3Isdigit(zName
[j
]); j
--){}
1673 if( zName
[j
]==':' ) nName
= j
;
1675 zName
= sqlite3MPrintf(db
, "%.*z:%u", nName
, zName
, ++cnt
);
1676 if( cnt
>3 ) sqlite3_randomness(sizeof(cnt
), &cnt
);
1678 pCol
->zName
= zName
;
1679 sqlite3ColumnPropertiesFromName(0, pCol
);
1680 if( zName
&& sqlite3HashInsert(&ht
, zName
, pCol
)==pCol
){
1681 sqlite3OomFault(db
);
1684 sqlite3HashClear(&ht
);
1685 if( db
->mallocFailed
){
1687 sqlite3DbFree(db
, aCol
[j
].zName
);
1689 sqlite3DbFree(db
, aCol
);
1692 return SQLITE_NOMEM_BKPT
;
1698 ** Add type and collation information to a column list based on
1699 ** a SELECT statement.
1701 ** The column list presumably came from selectColumnNamesFromExprList().
1702 ** The column list has only names, not types or collations. This
1703 ** routine goes through and adds the types and collations.
1705 ** This routine requires that all identifiers in the SELECT
1706 ** statement be resolved.
1708 void sqlite3SelectAddColumnTypeAndCollation(
1709 Parse
*pParse
, /* Parsing contexts */
1710 Table
*pTab
, /* Add column type information to this table */
1711 Select
*pSelect
/* SELECT used to determine types and collations */
1713 sqlite3
*db
= pParse
->db
;
1719 struct ExprList_item
*a
;
1722 assert( pSelect
!=0 );
1723 assert( (pSelect
->selFlags
& SF_Resolved
)!=0 );
1724 assert( pTab
->nCol
==pSelect
->pEList
->nExpr
|| db
->mallocFailed
);
1725 if( db
->mallocFailed
) return;
1726 memset(&sNC
, 0, sizeof(sNC
));
1727 sNC
.pSrcList
= pSelect
->pSrc
;
1728 a
= pSelect
->pEList
->a
;
1729 for(i
=0, pCol
=pTab
->aCol
; i
<pTab
->nCol
; i
++, pCol
++){
1733 zType
= columnType(&sNC
, p
, 0, 0, 0, &pCol
->szEst
);
1734 szAll
+= pCol
->szEst
;
1735 pCol
->affinity
= sqlite3ExprAffinity(p
);
1736 if( zType
&& (m
= sqlite3Strlen30(zType
))>0 ){
1737 n
= sqlite3Strlen30(pCol
->zName
);
1738 pCol
->zName
= sqlite3DbReallocOrFree(db
, pCol
->zName
, n
+m
+2);
1740 memcpy(&pCol
->zName
[n
+1], zType
, m
+1);
1741 pCol
->colFlags
|= COLFLAG_HASTYPE
;
1744 if( pCol
->affinity
==0 ) pCol
->affinity
= SQLITE_AFF_BLOB
;
1745 pColl
= sqlite3ExprCollSeq(pParse
, p
);
1746 if( pColl
&& pCol
->zColl
==0 ){
1747 pCol
->zColl
= sqlite3DbStrDup(db
, pColl
->zName
);
1750 pTab
->szTabRow
= sqlite3LogEst(szAll
*4);
1754 ** Given a SELECT statement, generate a Table structure that describes
1755 ** the result set of that SELECT.
1757 Table
*sqlite3ResultSetOfSelect(Parse
*pParse
, Select
*pSelect
){
1759 sqlite3
*db
= pParse
->db
;
1762 savedFlags
= db
->flags
;
1763 db
->flags
&= ~SQLITE_FullColNames
;
1764 db
->flags
|= SQLITE_ShortColNames
;
1765 sqlite3SelectPrep(pParse
, pSelect
, 0);
1766 if( pParse
->nErr
) return 0;
1767 while( pSelect
->pPrior
) pSelect
= pSelect
->pPrior
;
1768 db
->flags
= savedFlags
;
1769 pTab
= sqlite3DbMallocZero(db
, sizeof(Table
) );
1773 /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
1775 assert( db
->lookaside
.bDisable
);
1778 pTab
->nRowLogEst
= 200; assert( 200==sqlite3LogEst(1048576) );
1779 sqlite3ColumnsFromExprList(pParse
, pSelect
->pEList
, &pTab
->nCol
, &pTab
->aCol
);
1780 sqlite3SelectAddColumnTypeAndCollation(pParse
, pTab
, pSelect
);
1782 if( db
->mallocFailed
){
1783 sqlite3DeleteTable(db
, pTab
);
1790 ** Get a VDBE for the given parser context. Create a new one if necessary.
1791 ** If an error occurs, return NULL and leave a message in pParse.
1793 static SQLITE_NOINLINE Vdbe
*allocVdbe(Parse
*pParse
){
1794 Vdbe
*v
= pParse
->pVdbe
= sqlite3VdbeCreate(pParse
);
1795 if( v
) sqlite3VdbeAddOp2(v
, OP_Init
, 0, 1);
1796 if( pParse
->pToplevel
==0
1797 && OptimizationEnabled(pParse
->db
,SQLITE_FactorOutConst
)
1799 pParse
->okConstFactor
= 1;
1803 Vdbe
*sqlite3GetVdbe(Parse
*pParse
){
1804 Vdbe
*v
= pParse
->pVdbe
;
1805 return v
? v
: allocVdbe(pParse
);
1810 ** Compute the iLimit and iOffset fields of the SELECT based on the
1811 ** pLimit and pOffset expressions. pLimit and pOffset hold the expressions
1812 ** that appear in the original SQL statement after the LIMIT and OFFSET
1813 ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset
1814 ** are the integer memory register numbers for counters used to compute
1815 ** the limit and offset. If there is no limit and/or offset, then
1816 ** iLimit and iOffset are negative.
1818 ** This routine changes the values of iLimit and iOffset only if
1819 ** a limit or offset is defined by pLimit and pOffset. iLimit and
1820 ** iOffset should have been preset to appropriate default values (zero)
1821 ** prior to calling this routine.
1823 ** The iOffset register (if it exists) is initialized to the value
1824 ** of the OFFSET. The iLimit register is initialized to LIMIT. Register
1825 ** iOffset+1 is initialized to LIMIT+OFFSET.
1827 ** Only if pLimit!=0 or pOffset!=0 do the limit registers get
1828 ** redefined. The UNION ALL operator uses this property to force
1829 ** the reuse of the same limit and offset registers across multiple
1830 ** SELECT statements.
1832 static void computeLimitRegisters(Parse
*pParse
, Select
*p
, int iBreak
){
1837 if( p
->iLimit
) return;
1840 ** "LIMIT -1" always shows all rows. There is some
1841 ** controversy about what the correct behavior should be.
1842 ** The current implementation interprets "LIMIT 0" to mean
1845 sqlite3ExprCacheClear(pParse
);
1846 assert( p
->pOffset
==0 || p
->pLimit
!=0 );
1848 p
->iLimit
= iLimit
= ++pParse
->nMem
;
1849 v
= sqlite3GetVdbe(pParse
);
1851 if( sqlite3ExprIsInteger(p
->pLimit
, &n
) ){
1852 sqlite3VdbeAddOp2(v
, OP_Integer
, n
, iLimit
);
1853 VdbeComment((v
, "LIMIT counter"));
1855 sqlite3VdbeGoto(v
, iBreak
);
1856 }else if( n
>=0 && p
->nSelectRow
>sqlite3LogEst((u64
)n
) ){
1857 p
->nSelectRow
= sqlite3LogEst((u64
)n
);
1858 p
->selFlags
|= SF_FixedLimit
;
1861 sqlite3ExprCode(pParse
, p
->pLimit
, iLimit
);
1862 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, iLimit
); VdbeCoverage(v
);
1863 VdbeComment((v
, "LIMIT counter"));
1864 sqlite3VdbeAddOp2(v
, OP_IfNot
, iLimit
, iBreak
); VdbeCoverage(v
);
1867 p
->iOffset
= iOffset
= ++pParse
->nMem
;
1868 pParse
->nMem
++; /* Allocate an extra register for limit+offset */
1869 sqlite3ExprCode(pParse
, p
->pOffset
, iOffset
);
1870 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, iOffset
); VdbeCoverage(v
);
1871 VdbeComment((v
, "OFFSET counter"));
1872 sqlite3VdbeAddOp3(v
, OP_OffsetLimit
, iLimit
, iOffset
+1, iOffset
);
1873 VdbeComment((v
, "LIMIT+OFFSET"));
1878 #ifndef SQLITE_OMIT_COMPOUND_SELECT
1880 ** Return the appropriate collating sequence for the iCol-th column of
1881 ** the result set for the compound-select statement "p". Return NULL if
1882 ** the column has no default collating sequence.
1884 ** The collating sequence for the compound select is taken from the
1885 ** left-most term of the select that has a collating sequence.
1887 static CollSeq
*multiSelectCollSeq(Parse
*pParse
, Select
*p
, int iCol
){
1890 pRet
= multiSelectCollSeq(pParse
, p
->pPrior
, iCol
);
1895 /* iCol must be less than p->pEList->nExpr. Otherwise an error would
1896 ** have been thrown during name resolution and we would not have gotten
1898 if( pRet
==0 && ALWAYS(iCol
<p
->pEList
->nExpr
) ){
1899 pRet
= sqlite3ExprCollSeq(pParse
, p
->pEList
->a
[iCol
].pExpr
);
1905 ** The select statement passed as the second parameter is a compound SELECT
1906 ** with an ORDER BY clause. This function allocates and returns a KeyInfo
1907 ** structure suitable for implementing the ORDER BY.
1909 ** Space to hold the KeyInfo structure is obtained from malloc. The calling
1910 ** function is responsible for ensuring that this structure is eventually
1913 static KeyInfo
*multiSelectOrderByKeyInfo(Parse
*pParse
, Select
*p
, int nExtra
){
1914 ExprList
*pOrderBy
= p
->pOrderBy
;
1915 int nOrderBy
= p
->pOrderBy
->nExpr
;
1916 sqlite3
*db
= pParse
->db
;
1917 KeyInfo
*pRet
= sqlite3KeyInfoAlloc(db
, nOrderBy
+nExtra
, 1);
1920 for(i
=0; i
<nOrderBy
; i
++){
1921 struct ExprList_item
*pItem
= &pOrderBy
->a
[i
];
1922 Expr
*pTerm
= pItem
->pExpr
;
1925 if( pTerm
->flags
& EP_Collate
){
1926 pColl
= sqlite3ExprCollSeq(pParse
, pTerm
);
1928 pColl
= multiSelectCollSeq(pParse
, p
, pItem
->u
.x
.iOrderByCol
-1);
1929 if( pColl
==0 ) pColl
= db
->pDfltColl
;
1930 pOrderBy
->a
[i
].pExpr
=
1931 sqlite3ExprAddCollateString(pParse
, pTerm
, pColl
->zName
);
1933 assert( sqlite3KeyInfoIsWriteable(pRet
) );
1934 pRet
->aColl
[i
] = pColl
;
1935 pRet
->aSortOrder
[i
] = pOrderBy
->a
[i
].sortOrder
;
1942 #ifndef SQLITE_OMIT_CTE
1944 ** This routine generates VDBE code to compute the content of a WITH RECURSIVE
1945 ** query of the form:
1947 ** <recursive-table> AS (<setup-query> UNION [ALL] <recursive-query>)
1948 ** \___________/ \_______________/
1952 ** There is exactly one reference to the recursive-table in the FROM clause
1953 ** of recursive-query, marked with the SrcList->a[].fg.isRecursive flag.
1955 ** The setup-query runs once to generate an initial set of rows that go
1956 ** into a Queue table. Rows are extracted from the Queue table one by
1957 ** one. Each row extracted from Queue is output to pDest. Then the single
1958 ** extracted row (now in the iCurrent table) becomes the content of the
1959 ** recursive-table for a recursive-query run. The output of the recursive-query
1960 ** is added back into the Queue table. Then another row is extracted from Queue
1961 ** and the iteration continues until the Queue table is empty.
1963 ** If the compound query operator is UNION then no duplicate rows are ever
1964 ** inserted into the Queue table. The iDistinct table keeps a copy of all rows
1965 ** that have ever been inserted into Queue and causes duplicates to be
1966 ** discarded. If the operator is UNION ALL, then duplicates are allowed.
1968 ** If the query has an ORDER BY, then entries in the Queue table are kept in
1969 ** ORDER BY order and the first entry is extracted for each cycle. Without
1970 ** an ORDER BY, the Queue table is just a FIFO.
1972 ** If a LIMIT clause is provided, then the iteration stops after LIMIT rows
1973 ** have been output to pDest. A LIMIT of zero means to output no rows and a
1974 ** negative LIMIT means to output all rows. If there is also an OFFSET clause
1975 ** with a positive value, then the first OFFSET outputs are discarded rather
1976 ** than being sent to pDest. The LIMIT count does not begin until after OFFSET
1977 ** rows have been skipped.
1979 static void generateWithRecursiveQuery(
1980 Parse
*pParse
, /* Parsing context */
1981 Select
*p
, /* The recursive SELECT to be coded */
1982 SelectDest
*pDest
/* What to do with query results */
1984 SrcList
*pSrc
= p
->pSrc
; /* The FROM clause of the recursive query */
1985 int nCol
= p
->pEList
->nExpr
; /* Number of columns in the recursive table */
1986 Vdbe
*v
= pParse
->pVdbe
; /* The prepared statement under construction */
1987 Select
*pSetup
= p
->pPrior
; /* The setup query */
1988 int addrTop
; /* Top of the loop */
1989 int addrCont
, addrBreak
; /* CONTINUE and BREAK addresses */
1990 int iCurrent
= 0; /* The Current table */
1991 int regCurrent
; /* Register holding Current table */
1992 int iQueue
; /* The Queue table */
1993 int iDistinct
= 0; /* To ensure unique results if UNION */
1994 int eDest
= SRT_Fifo
; /* How to write to Queue */
1995 SelectDest destQueue
; /* SelectDest targetting the Queue table */
1996 int i
; /* Loop counter */
1997 int rc
; /* Result code */
1998 ExprList
*pOrderBy
; /* The ORDER BY clause */
1999 Expr
*pLimit
, *pOffset
; /* Saved LIMIT and OFFSET */
2000 int regLimit
, regOffset
; /* Registers used by LIMIT and OFFSET */
2002 /* Obtain authorization to do a recursive query */
2003 if( sqlite3AuthCheck(pParse
, SQLITE_RECURSIVE
, 0, 0, 0) ) return;
2005 /* Process the LIMIT and OFFSET clauses, if they exist */
2006 addrBreak
= sqlite3VdbeMakeLabel(v
);
2007 computeLimitRegisters(pParse
, p
, addrBreak
);
2009 pOffset
= p
->pOffset
;
2010 regLimit
= p
->iLimit
;
2011 regOffset
= p
->iOffset
;
2012 p
->pLimit
= p
->pOffset
= 0;
2013 p
->iLimit
= p
->iOffset
= 0;
2014 pOrderBy
= p
->pOrderBy
;
2016 /* Locate the cursor number of the Current table */
2017 for(i
=0; ALWAYS(i
<pSrc
->nSrc
); i
++){
2018 if( pSrc
->a
[i
].fg
.isRecursive
){
2019 iCurrent
= pSrc
->a
[i
].iCursor
;
2024 /* Allocate cursors numbers for Queue and Distinct. The cursor number for
2025 ** the Distinct table must be exactly one greater than Queue in order
2026 ** for the SRT_DistFifo and SRT_DistQueue destinations to work. */
2027 iQueue
= pParse
->nTab
++;
2028 if( p
->op
==TK_UNION
){
2029 eDest
= pOrderBy
? SRT_DistQueue
: SRT_DistFifo
;
2030 iDistinct
= pParse
->nTab
++;
2032 eDest
= pOrderBy
? SRT_Queue
: SRT_Fifo
;
2034 sqlite3SelectDestInit(&destQueue
, eDest
, iQueue
);
2036 /* Allocate cursors for Current, Queue, and Distinct. */
2037 regCurrent
= ++pParse
->nMem
;
2038 sqlite3VdbeAddOp3(v
, OP_OpenPseudo
, iCurrent
, regCurrent
, nCol
);
2040 KeyInfo
*pKeyInfo
= multiSelectOrderByKeyInfo(pParse
, p
, 1);
2041 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
, iQueue
, pOrderBy
->nExpr
+2, 0,
2042 (char*)pKeyInfo
, P4_KEYINFO
);
2043 destQueue
.pOrderBy
= pOrderBy
;
2045 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, iQueue
, nCol
);
2047 VdbeComment((v
, "Queue table"));
2049 p
->addrOpenEphm
[0] = sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, iDistinct
, 0);
2050 p
->selFlags
|= SF_UsesEphemeral
;
2053 /* Detach the ORDER BY clause from the compound SELECT */
2056 /* Store the results of the setup-query in Queue. */
2058 rc
= sqlite3Select(pParse
, pSetup
, &destQueue
);
2060 if( rc
) goto end_of_recursive_query
;
2062 /* Find the next row in the Queue and output that row */
2063 addrTop
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iQueue
, addrBreak
); VdbeCoverage(v
);
2065 /* Transfer the next row in Queue over to Current */
2066 sqlite3VdbeAddOp1(v
, OP_NullRow
, iCurrent
); /* To reset column cache */
2068 sqlite3VdbeAddOp3(v
, OP_Column
, iQueue
, pOrderBy
->nExpr
+1, regCurrent
);
2070 sqlite3VdbeAddOp2(v
, OP_RowData
, iQueue
, regCurrent
);
2072 sqlite3VdbeAddOp1(v
, OP_Delete
, iQueue
);
2074 /* Output the single row in Current */
2075 addrCont
= sqlite3VdbeMakeLabel(v
);
2076 codeOffset(v
, regOffset
, addrCont
);
2077 selectInnerLoop(pParse
, p
, p
->pEList
, iCurrent
,
2078 0, 0, pDest
, addrCont
, addrBreak
);
2080 sqlite3VdbeAddOp2(v
, OP_DecrJumpZero
, regLimit
, addrBreak
);
2083 sqlite3VdbeResolveLabel(v
, addrCont
);
2085 /* Execute the recursive SELECT taking the single row in Current as
2086 ** the value for the recursive-table. Store the results in the Queue.
2088 if( p
->selFlags
& SF_Aggregate
){
2089 sqlite3ErrorMsg(pParse
, "recursive aggregate queries not supported");
2092 sqlite3Select(pParse
, p
, &destQueue
);
2093 assert( p
->pPrior
==0 );
2097 /* Keep running the loop until the Queue is empty */
2098 sqlite3VdbeGoto(v
, addrTop
);
2099 sqlite3VdbeResolveLabel(v
, addrBreak
);
2101 end_of_recursive_query
:
2102 sqlite3ExprListDelete(pParse
->db
, p
->pOrderBy
);
2103 p
->pOrderBy
= pOrderBy
;
2105 p
->pOffset
= pOffset
;
2108 #endif /* SQLITE_OMIT_CTE */
2110 /* Forward references */
2111 static int multiSelectOrderBy(
2112 Parse
*pParse
, /* Parsing context */
2113 Select
*p
, /* The right-most of SELECTs to be coded */
2114 SelectDest
*pDest
/* What to do with query results */
2118 ** Handle the special case of a compound-select that originates from a
2119 ** VALUES clause. By handling this as a special case, we avoid deep
2120 ** recursion, and thus do not need to enforce the SQLITE_LIMIT_COMPOUND_SELECT
2121 ** on a VALUES clause.
2123 ** Because the Select object originates from a VALUES clause:
2124 ** (1) It has no LIMIT or OFFSET
2125 ** (2) All terms are UNION ALL
2126 ** (3) There is no ORDER BY clause
2128 static int multiSelectValues(
2129 Parse
*pParse
, /* Parsing context */
2130 Select
*p
, /* The right-most of SELECTs to be coded */
2131 SelectDest
*pDest
/* What to do with query results */
2136 assert( p
->selFlags
& SF_MultiValue
);
2138 assert( p
->selFlags
& SF_Values
);
2139 assert( p
->op
==TK_ALL
|| (p
->op
==TK_SELECT
&& p
->pPrior
==0) );
2140 assert( p
->pLimit
==0 );
2141 assert( p
->pOffset
==0 );
2142 assert( p
->pNext
==0 || p
->pEList
->nExpr
==p
->pNext
->pEList
->nExpr
);
2143 if( p
->pPrior
==0 ) break;
2144 assert( p
->pPrior
->pNext
==p
);
2151 rc
= sqlite3Select(pParse
, p
, pDest
);
2154 p
->nSelectRow
= nRow
;
2161 ** This routine is called to process a compound query form from
2162 ** two or more separate queries using UNION, UNION ALL, EXCEPT, or
2165 ** "p" points to the right-most of the two queries. the query on the
2166 ** left is p->pPrior. The left query could also be a compound query
2167 ** in which case this routine will be called recursively.
2169 ** The results of the total query are to be written into a destination
2170 ** of type eDest with parameter iParm.
2172 ** Example 1: Consider a three-way compound SQL statement.
2174 ** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
2176 ** This statement is parsed up as follows:
2180 ** `-----> SELECT b FROM t2
2182 ** `------> SELECT a FROM t1
2184 ** The arrows in the diagram above represent the Select.pPrior pointer.
2185 ** So if this routine is called with p equal to the t3 query, then
2186 ** pPrior will be the t2 query. p->op will be TK_UNION in this case.
2188 ** Notice that because of the way SQLite parses compound SELECTs, the
2189 ** individual selects always group from left to right.
2191 static int multiSelect(
2192 Parse
*pParse
, /* Parsing context */
2193 Select
*p
, /* The right-most of SELECTs to be coded */
2194 SelectDest
*pDest
/* What to do with query results */
2196 int rc
= SQLITE_OK
; /* Success code from a subroutine */
2197 Select
*pPrior
; /* Another SELECT immediately to our left */
2198 Vdbe
*v
; /* Generate code to this VDBE */
2199 SelectDest dest
; /* Alternative data destination */
2200 Select
*pDelete
= 0; /* Chain of simple selects to delete */
2201 sqlite3
*db
; /* Database connection */
2202 #ifndef SQLITE_OMIT_EXPLAIN
2203 int iSub1
= 0; /* EQP id of left-hand query */
2204 int iSub2
= 0; /* EQP id of right-hand query */
2207 /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
2208 ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
2210 assert( p
&& p
->pPrior
); /* Calling function guarantees this much */
2211 assert( (p
->selFlags
& SF_Recursive
)==0 || p
->op
==TK_ALL
|| p
->op
==TK_UNION
);
2215 if( pPrior
->pOrderBy
){
2216 sqlite3ErrorMsg(pParse
,"ORDER BY clause should come after %s not before",
2217 selectOpName(p
->op
));
2219 goto multi_select_end
;
2221 if( pPrior
->pLimit
){
2222 sqlite3ErrorMsg(pParse
,"LIMIT clause should come after %s not before",
2223 selectOpName(p
->op
));
2225 goto multi_select_end
;
2228 v
= sqlite3GetVdbe(pParse
);
2229 assert( v
!=0 ); /* The VDBE already created by calling function */
2231 /* Create the destination temporary table if necessary
2233 if( dest
.eDest
==SRT_EphemTab
){
2234 assert( p
->pEList
);
2235 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, dest
.iSDParm
, p
->pEList
->nExpr
);
2236 dest
.eDest
= SRT_Table
;
2239 /* Special handling for a compound-select that originates as a VALUES clause.
2241 if( p
->selFlags
& SF_MultiValue
){
2242 rc
= multiSelectValues(pParse
, p
, &dest
);
2243 goto multi_select_end
;
2246 /* Make sure all SELECTs in the statement have the same number of elements
2247 ** in their result sets.
2249 assert( p
->pEList
&& pPrior
->pEList
);
2250 assert( p
->pEList
->nExpr
==pPrior
->pEList
->nExpr
);
2252 #ifndef SQLITE_OMIT_CTE
2253 if( p
->selFlags
& SF_Recursive
){
2254 generateWithRecursiveQuery(pParse
, p
, &dest
);
2258 /* Compound SELECTs that have an ORDER BY clause are handled separately.
2261 return multiSelectOrderBy(pParse
, p
, pDest
);
2264 /* Generate code for the left and right SELECT statements.
2270 assert( !pPrior
->pLimit
);
2271 pPrior
->iLimit
= p
->iLimit
;
2272 pPrior
->iOffset
= p
->iOffset
;
2273 pPrior
->pLimit
= p
->pLimit
;
2274 pPrior
->pOffset
= p
->pOffset
;
2275 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2276 rc
= sqlite3Select(pParse
, pPrior
, &dest
);
2280 goto multi_select_end
;
2283 p
->iLimit
= pPrior
->iLimit
;
2284 p
->iOffset
= pPrior
->iOffset
;
2286 addr
= sqlite3VdbeAddOp1(v
, OP_IfNot
, p
->iLimit
); VdbeCoverage(v
);
2287 VdbeComment((v
, "Jump ahead if LIMIT reached"));
2289 sqlite3VdbeAddOp3(v
, OP_OffsetLimit
,
2290 p
->iLimit
, p
->iOffset
+1, p
->iOffset
);
2293 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
2294 rc
= sqlite3Select(pParse
, p
, &dest
);
2295 testcase( rc
!=SQLITE_OK
);
2296 pDelete
= p
->pPrior
;
2298 p
->nSelectRow
= sqlite3LogEstAdd(p
->nSelectRow
, pPrior
->nSelectRow
);
2300 && sqlite3ExprIsInteger(pPrior
->pLimit
, &nLimit
)
2301 && nLimit
>0 && p
->nSelectRow
> sqlite3LogEst((u64
)nLimit
)
2303 p
->nSelectRow
= sqlite3LogEst((u64
)nLimit
);
2306 sqlite3VdbeJumpHere(v
, addr
);
2312 int unionTab
; /* Cursor number of the temporary table holding result */
2313 u8 op
= 0; /* One of the SRT_ operations to apply to self */
2314 int priorOp
; /* The SRT_ operation to apply to prior selects */
2315 Expr
*pLimit
, *pOffset
; /* Saved values of p->nLimit and p->nOffset */
2317 SelectDest uniondest
;
2319 testcase( p
->op
==TK_EXCEPT
);
2320 testcase( p
->op
==TK_UNION
);
2321 priorOp
= SRT_Union
;
2322 if( dest
.eDest
==priorOp
){
2323 /* We can reuse a temporary table generated by a SELECT to our
2326 assert( p
->pLimit
==0 ); /* Not allowed on leftward elements */
2327 assert( p
->pOffset
==0 ); /* Not allowed on leftward elements */
2328 unionTab
= dest
.iSDParm
;
2330 /* We will need to create our own temporary table to hold the
2331 ** intermediate results.
2333 unionTab
= pParse
->nTab
++;
2334 assert( p
->pOrderBy
==0 );
2335 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, unionTab
, 0);
2336 assert( p
->addrOpenEphm
[0] == -1 );
2337 p
->addrOpenEphm
[0] = addr
;
2338 findRightmost(p
)->selFlags
|= SF_UsesEphemeral
;
2339 assert( p
->pEList
);
2342 /* Code the SELECT statements to our left
2344 assert( !pPrior
->pOrderBy
);
2345 sqlite3SelectDestInit(&uniondest
, priorOp
, unionTab
);
2346 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2347 rc
= sqlite3Select(pParse
, pPrior
, &uniondest
);
2349 goto multi_select_end
;
2352 /* Code the current SELECT statement
2354 if( p
->op
==TK_EXCEPT
){
2357 assert( p
->op
==TK_UNION
);
2363 pOffset
= p
->pOffset
;
2365 uniondest
.eDest
= op
;
2366 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
2367 rc
= sqlite3Select(pParse
, p
, &uniondest
);
2368 testcase( rc
!=SQLITE_OK
);
2369 /* Query flattening in sqlite3Select() might refill p->pOrderBy.
2370 ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
2371 sqlite3ExprListDelete(db
, p
->pOrderBy
);
2372 pDelete
= p
->pPrior
;
2375 if( p
->op
==TK_UNION
){
2376 p
->nSelectRow
= sqlite3LogEstAdd(p
->nSelectRow
, pPrior
->nSelectRow
);
2378 sqlite3ExprDelete(db
, p
->pLimit
);
2380 p
->pOffset
= pOffset
;
2384 /* Convert the data in the temporary table into whatever form
2385 ** it is that we currently need.
2387 assert( unionTab
==dest
.iSDParm
|| dest
.eDest
!=priorOp
);
2388 if( dest
.eDest
!=priorOp
){
2389 int iCont
, iBreak
, iStart
;
2390 assert( p
->pEList
);
2391 if( dest
.eDest
==SRT_Output
){
2393 while( pFirst
->pPrior
) pFirst
= pFirst
->pPrior
;
2394 generateColumnNames(pParse
, pFirst
->pSrc
, pFirst
->pEList
);
2396 iBreak
= sqlite3VdbeMakeLabel(v
);
2397 iCont
= sqlite3VdbeMakeLabel(v
);
2398 computeLimitRegisters(pParse
, p
, iBreak
);
2399 sqlite3VdbeAddOp2(v
, OP_Rewind
, unionTab
, iBreak
); VdbeCoverage(v
);
2400 iStart
= sqlite3VdbeCurrentAddr(v
);
2401 selectInnerLoop(pParse
, p
, p
->pEList
, unionTab
,
2402 0, 0, &dest
, iCont
, iBreak
);
2403 sqlite3VdbeResolveLabel(v
, iCont
);
2404 sqlite3VdbeAddOp2(v
, OP_Next
, unionTab
, iStart
); VdbeCoverage(v
);
2405 sqlite3VdbeResolveLabel(v
, iBreak
);
2406 sqlite3VdbeAddOp2(v
, OP_Close
, unionTab
, 0);
2410 default: assert( p
->op
==TK_INTERSECT
); {
2412 int iCont
, iBreak
, iStart
;
2413 Expr
*pLimit
, *pOffset
;
2415 SelectDest intersectdest
;
2418 /* INTERSECT is different from the others since it requires
2419 ** two temporary tables. Hence it has its own case. Begin
2420 ** by allocating the tables we will need.
2422 tab1
= pParse
->nTab
++;
2423 tab2
= pParse
->nTab
++;
2424 assert( p
->pOrderBy
==0 );
2426 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, tab1
, 0);
2427 assert( p
->addrOpenEphm
[0] == -1 );
2428 p
->addrOpenEphm
[0] = addr
;
2429 findRightmost(p
)->selFlags
|= SF_UsesEphemeral
;
2430 assert( p
->pEList
);
2432 /* Code the SELECTs to our left into temporary table "tab1".
2434 sqlite3SelectDestInit(&intersectdest
, SRT_Union
, tab1
);
2435 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2436 rc
= sqlite3Select(pParse
, pPrior
, &intersectdest
);
2438 goto multi_select_end
;
2441 /* Code the current SELECT into temporary table "tab2"
2443 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, tab2
, 0);
2444 assert( p
->addrOpenEphm
[1] == -1 );
2445 p
->addrOpenEphm
[1] = addr
;
2449 pOffset
= p
->pOffset
;
2451 intersectdest
.iSDParm
= tab2
;
2452 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
2453 rc
= sqlite3Select(pParse
, p
, &intersectdest
);
2454 testcase( rc
!=SQLITE_OK
);
2455 pDelete
= p
->pPrior
;
2457 if( p
->nSelectRow
>pPrior
->nSelectRow
) p
->nSelectRow
= pPrior
->nSelectRow
;
2458 sqlite3ExprDelete(db
, p
->pLimit
);
2460 p
->pOffset
= pOffset
;
2462 /* Generate code to take the intersection of the two temporary
2465 assert( p
->pEList
);
2466 if( dest
.eDest
==SRT_Output
){
2468 while( pFirst
->pPrior
) pFirst
= pFirst
->pPrior
;
2469 generateColumnNames(pParse
, pFirst
->pSrc
, pFirst
->pEList
);
2471 iBreak
= sqlite3VdbeMakeLabel(v
);
2472 iCont
= sqlite3VdbeMakeLabel(v
);
2473 computeLimitRegisters(pParse
, p
, iBreak
);
2474 sqlite3VdbeAddOp2(v
, OP_Rewind
, tab1
, iBreak
); VdbeCoverage(v
);
2475 r1
= sqlite3GetTempReg(pParse
);
2476 iStart
= sqlite3VdbeAddOp2(v
, OP_RowKey
, tab1
, r1
);
2477 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, tab2
, iCont
, r1
, 0); VdbeCoverage(v
);
2478 sqlite3ReleaseTempReg(pParse
, r1
);
2479 selectInnerLoop(pParse
, p
, p
->pEList
, tab1
,
2480 0, 0, &dest
, iCont
, iBreak
);
2481 sqlite3VdbeResolveLabel(v
, iCont
);
2482 sqlite3VdbeAddOp2(v
, OP_Next
, tab1
, iStart
); VdbeCoverage(v
);
2483 sqlite3VdbeResolveLabel(v
, iBreak
);
2484 sqlite3VdbeAddOp2(v
, OP_Close
, tab2
, 0);
2485 sqlite3VdbeAddOp2(v
, OP_Close
, tab1
, 0);
2490 explainComposite(pParse
, p
->op
, iSub1
, iSub2
, p
->op
!=TK_ALL
);
2492 /* Compute collating sequences used by
2493 ** temporary tables needed to implement the compound select.
2494 ** Attach the KeyInfo structure to all temporary tables.
2496 ** This section is run by the right-most SELECT statement only.
2497 ** SELECT statements to the left always skip this part. The right-most
2498 ** SELECT might also skip this part if it has no ORDER BY clause and
2499 ** no temp tables are required.
2501 if( p
->selFlags
& SF_UsesEphemeral
){
2502 int i
; /* Loop counter */
2503 KeyInfo
*pKeyInfo
; /* Collating sequence for the result set */
2504 Select
*pLoop
; /* For looping through SELECT statements */
2505 CollSeq
**apColl
; /* For looping through pKeyInfo->aColl[] */
2506 int nCol
; /* Number of columns in result set */
2508 assert( p
->pNext
==0 );
2509 nCol
= p
->pEList
->nExpr
;
2510 pKeyInfo
= sqlite3KeyInfoAlloc(db
, nCol
, 1);
2512 rc
= SQLITE_NOMEM_BKPT
;
2513 goto multi_select_end
;
2515 for(i
=0, apColl
=pKeyInfo
->aColl
; i
<nCol
; i
++, apColl
++){
2516 *apColl
= multiSelectCollSeq(pParse
, p
, i
);
2518 *apColl
= db
->pDfltColl
;
2522 for(pLoop
=p
; pLoop
; pLoop
=pLoop
->pPrior
){
2524 int addr
= pLoop
->addrOpenEphm
[i
];
2526 /* If [0] is unused then [1] is also unused. So we can
2527 ** always safely abort as soon as the first unused slot is found */
2528 assert( pLoop
->addrOpenEphm
[1]<0 );
2531 sqlite3VdbeChangeP2(v
, addr
, nCol
);
2532 sqlite3VdbeChangeP4(v
, addr
, (char*)sqlite3KeyInfoRef(pKeyInfo
),
2534 pLoop
->addrOpenEphm
[i
] = -1;
2537 sqlite3KeyInfoUnref(pKeyInfo
);
2541 pDest
->iSdst
= dest
.iSdst
;
2542 pDest
->nSdst
= dest
.nSdst
;
2543 sqlite3SelectDelete(db
, pDelete
);
2546 #endif /* SQLITE_OMIT_COMPOUND_SELECT */
2549 ** Error message for when two or more terms of a compound select have different
2550 ** size result sets.
2552 void sqlite3SelectWrongNumTermsError(Parse
*pParse
, Select
*p
){
2553 if( p
->selFlags
& SF_Values
){
2554 sqlite3ErrorMsg(pParse
, "all VALUES must have the same number of terms");
2556 sqlite3ErrorMsg(pParse
, "SELECTs to the left and right of %s"
2557 " do not have the same number of result columns", selectOpName(p
->op
));
2562 ** Code an output subroutine for a coroutine implementation of a
2565 ** The data to be output is contained in pIn->iSdst. There are
2566 ** pIn->nSdst columns to be output. pDest is where the output should
2569 ** regReturn is the number of the register holding the subroutine
2572 ** If regPrev>0 then it is the first register in a vector that
2573 ** records the previous output. mem[regPrev] is a flag that is false
2574 ** if there has been no previous output. If regPrev>0 then code is
2575 ** generated to suppress duplicates. pKeyInfo is used for comparing
2578 ** If the LIMIT found in p->iLimit is reached, jump immediately to
2581 static int generateOutputSubroutine(
2582 Parse
*pParse
, /* Parsing context */
2583 Select
*p
, /* The SELECT statement */
2584 SelectDest
*pIn
, /* Coroutine supplying data */
2585 SelectDest
*pDest
, /* Where to send the data */
2586 int regReturn
, /* The return address register */
2587 int regPrev
, /* Previous result register. No uniqueness if 0 */
2588 KeyInfo
*pKeyInfo
, /* For comparing with previous entry */
2589 int iBreak
/* Jump here if we hit the LIMIT */
2591 Vdbe
*v
= pParse
->pVdbe
;
2595 addr
= sqlite3VdbeCurrentAddr(v
);
2596 iContinue
= sqlite3VdbeMakeLabel(v
);
2598 /* Suppress duplicates for UNION, EXCEPT, and INTERSECT
2602 addr1
= sqlite3VdbeAddOp1(v
, OP_IfNot
, regPrev
); VdbeCoverage(v
);
2603 addr2
= sqlite3VdbeAddOp4(v
, OP_Compare
, pIn
->iSdst
, regPrev
+1, pIn
->nSdst
,
2604 (char*)sqlite3KeyInfoRef(pKeyInfo
), P4_KEYINFO
);
2605 sqlite3VdbeAddOp3(v
, OP_Jump
, addr2
+2, iContinue
, addr2
+2); VdbeCoverage(v
);
2606 sqlite3VdbeJumpHere(v
, addr1
);
2607 sqlite3VdbeAddOp3(v
, OP_Copy
, pIn
->iSdst
, regPrev
+1, pIn
->nSdst
-1);
2608 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, regPrev
);
2610 if( pParse
->db
->mallocFailed
) return 0;
2612 /* Suppress the first OFFSET entries if there is an OFFSET clause
2614 codeOffset(v
, p
->iOffset
, iContinue
);
2616 assert( pDest
->eDest
!=SRT_Exists
);
2617 assert( pDest
->eDest
!=SRT_Table
);
2618 switch( pDest
->eDest
){
2619 /* Store the result as data using a unique key.
2621 case SRT_EphemTab
: {
2622 int r1
= sqlite3GetTempReg(pParse
);
2623 int r2
= sqlite3GetTempReg(pParse
);
2624 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, pIn
->iSdst
, pIn
->nSdst
, r1
);
2625 sqlite3VdbeAddOp2(v
, OP_NewRowid
, pDest
->iSDParm
, r2
);
2626 sqlite3VdbeAddOp3(v
, OP_Insert
, pDest
->iSDParm
, r1
, r2
);
2627 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
2628 sqlite3ReleaseTempReg(pParse
, r2
);
2629 sqlite3ReleaseTempReg(pParse
, r1
);
2633 #ifndef SQLITE_OMIT_SUBQUERY
2634 /* If we are creating a set for an "expr IN (SELECT ...)".
2638 testcase( pIn
->nSdst
>1 );
2639 r1
= sqlite3GetTempReg(pParse
);
2640 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, pIn
->iSdst
, pIn
->nSdst
,
2641 r1
, pDest
->zAffSdst
, pIn
->nSdst
);
2642 sqlite3ExprCacheAffinityChange(pParse
, pIn
->iSdst
, pIn
->nSdst
);
2643 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, pDest
->iSDParm
, r1
);
2644 sqlite3ReleaseTempReg(pParse
, r1
);
2648 /* If this is a scalar select that is part of an expression, then
2649 ** store the results in the appropriate memory cell and break out
2650 ** of the scan loop.
2653 assert( pIn
->nSdst
==1 || pParse
->nErr
>0 ); testcase( pIn
->nSdst
!=1 );
2654 sqlite3ExprCodeMove(pParse
, pIn
->iSdst
, pDest
->iSDParm
, 1);
2655 /* The LIMIT clause will jump out of the loop for us */
2658 #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
2660 /* The results are stored in a sequence of registers
2661 ** starting at pDest->iSdst. Then the co-routine yields.
2663 case SRT_Coroutine
: {
2664 if( pDest
->iSdst
==0 ){
2665 pDest
->iSdst
= sqlite3GetTempRange(pParse
, pIn
->nSdst
);
2666 pDest
->nSdst
= pIn
->nSdst
;
2668 sqlite3ExprCodeMove(pParse
, pIn
->iSdst
, pDest
->iSdst
, pIn
->nSdst
);
2669 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iSDParm
);
2673 /* If none of the above, then the result destination must be
2674 ** SRT_Output. This routine is never called with any other
2675 ** destination other than the ones handled above or SRT_Output.
2677 ** For SRT_Output, results are stored in a sequence of registers.
2678 ** Then the OP_ResultRow opcode is used to cause sqlite3_step() to
2679 ** return the next row of result.
2682 assert( pDest
->eDest
==SRT_Output
);
2683 sqlite3VdbeAddOp2(v
, OP_ResultRow
, pIn
->iSdst
, pIn
->nSdst
);
2684 sqlite3ExprCacheAffinityChange(pParse
, pIn
->iSdst
, pIn
->nSdst
);
2689 /* Jump to the end of the loop if the LIMIT is reached.
2692 sqlite3VdbeAddOp2(v
, OP_DecrJumpZero
, p
->iLimit
, iBreak
); VdbeCoverage(v
);
2695 /* Generate the subroutine return
2697 sqlite3VdbeResolveLabel(v
, iContinue
);
2698 sqlite3VdbeAddOp1(v
, OP_Return
, regReturn
);
2704 ** Alternative compound select code generator for cases when there
2705 ** is an ORDER BY clause.
2707 ** We assume a query of the following form:
2709 ** <selectA> <operator> <selectB> ORDER BY <orderbylist>
2711 ** <operator> is one of UNION ALL, UNION, EXCEPT, or INTERSECT. The idea
2712 ** is to code both <selectA> and <selectB> with the ORDER BY clause as
2713 ** co-routines. Then run the co-routines in parallel and merge the results
2714 ** into the output. In addition to the two coroutines (called selectA and
2715 ** selectB) there are 7 subroutines:
2717 ** outA: Move the output of the selectA coroutine into the output
2718 ** of the compound query.
2720 ** outB: Move the output of the selectB coroutine into the output
2721 ** of the compound query. (Only generated for UNION and
2722 ** UNION ALL. EXCEPT and INSERTSECT never output a row that
2723 ** appears only in B.)
2725 ** AltB: Called when there is data from both coroutines and A<B.
2727 ** AeqB: Called when there is data from both coroutines and A==B.
2729 ** AgtB: Called when there is data from both coroutines and A>B.
2731 ** EofA: Called when data is exhausted from selectA.
2733 ** EofB: Called when data is exhausted from selectB.
2735 ** The implementation of the latter five subroutines depend on which
2736 ** <operator> is used:
2739 ** UNION ALL UNION EXCEPT INTERSECT
2740 ** ------------- ----------------- -------------- -----------------
2741 ** AltB: outA, nextA outA, nextA outA, nextA nextA
2743 ** AeqB: outA, nextA nextA nextA outA, nextA
2745 ** AgtB: outB, nextB outB, nextB nextB nextB
2747 ** EofA: outB, nextB outB, nextB halt halt
2749 ** EofB: outA, nextA outA, nextA outA, nextA halt
2751 ** In the AltB, AeqB, and AgtB subroutines, an EOF on A following nextA
2752 ** causes an immediate jump to EofA and an EOF on B following nextB causes
2753 ** an immediate jump to EofB. Within EofA and EofB, and EOF on entry or
2754 ** following nextX causes a jump to the end of the select processing.
2756 ** Duplicate removal in the UNION, EXCEPT, and INTERSECT cases is handled
2757 ** within the output subroutine. The regPrev register set holds the previously
2758 ** output value. A comparison is made against this value and the output
2759 ** is skipped if the next results would be the same as the previous.
2761 ** The implementation plan is to implement the two coroutines and seven
2762 ** subroutines first, then put the control logic at the bottom. Like this:
2765 ** coA: coroutine for left query (A)
2766 ** coB: coroutine for right query (B)
2767 ** outA: output one row of A
2768 ** outB: output one row of B (UNION and UNION ALL only)
2774 ** Init: initialize coroutine registers
2776 ** if eof(A) goto EofA
2778 ** if eof(B) goto EofB
2779 ** Cmpr: Compare A, B
2780 ** Jump AltB, AeqB, AgtB
2783 ** We call AltB, AeqB, AgtB, EofA, and EofB "subroutines" but they are not
2784 ** actually called using Gosub and they do not Return. EofA and EofB loop
2785 ** until all data is exhausted then jump to the "end" labe. AltB, AeqB,
2786 ** and AgtB jump to either L2 or to one of EofA or EofB.
2788 #ifndef SQLITE_OMIT_COMPOUND_SELECT
2789 static int multiSelectOrderBy(
2790 Parse
*pParse
, /* Parsing context */
2791 Select
*p
, /* The right-most of SELECTs to be coded */
2792 SelectDest
*pDest
/* What to do with query results */
2794 int i
, j
; /* Loop counters */
2795 Select
*pPrior
; /* Another SELECT immediately to our left */
2796 Vdbe
*v
; /* Generate code to this VDBE */
2797 SelectDest destA
; /* Destination for coroutine A */
2798 SelectDest destB
; /* Destination for coroutine B */
2799 int regAddrA
; /* Address register for select-A coroutine */
2800 int regAddrB
; /* Address register for select-B coroutine */
2801 int addrSelectA
; /* Address of the select-A coroutine */
2802 int addrSelectB
; /* Address of the select-B coroutine */
2803 int regOutA
; /* Address register for the output-A subroutine */
2804 int regOutB
; /* Address register for the output-B subroutine */
2805 int addrOutA
; /* Address of the output-A subroutine */
2806 int addrOutB
= 0; /* Address of the output-B subroutine */
2807 int addrEofA
; /* Address of the select-A-exhausted subroutine */
2808 int addrEofA_noB
; /* Alternate addrEofA if B is uninitialized */
2809 int addrEofB
; /* Address of the select-B-exhausted subroutine */
2810 int addrAltB
; /* Address of the A<B subroutine */
2811 int addrAeqB
; /* Address of the A==B subroutine */
2812 int addrAgtB
; /* Address of the A>B subroutine */
2813 int regLimitA
; /* Limit register for select-A */
2814 int regLimitB
; /* Limit register for select-A */
2815 int regPrev
; /* A range of registers to hold previous output */
2816 int savedLimit
; /* Saved value of p->iLimit */
2817 int savedOffset
; /* Saved value of p->iOffset */
2818 int labelCmpr
; /* Label for the start of the merge algorithm */
2819 int labelEnd
; /* Label for the end of the overall SELECT stmt */
2820 int addr1
; /* Jump instructions that get retargetted */
2821 int op
; /* One of TK_ALL, TK_UNION, TK_EXCEPT, TK_INTERSECT */
2822 KeyInfo
*pKeyDup
= 0; /* Comparison information for duplicate removal */
2823 KeyInfo
*pKeyMerge
; /* Comparison information for merging rows */
2824 sqlite3
*db
; /* Database connection */
2825 ExprList
*pOrderBy
; /* The ORDER BY clause */
2826 int nOrderBy
; /* Number of terms in the ORDER BY clause */
2827 int *aPermute
; /* Mapping from ORDER BY terms to result set columns */
2828 #ifndef SQLITE_OMIT_EXPLAIN
2829 int iSub1
; /* EQP id of left-hand query */
2830 int iSub2
; /* EQP id of right-hand query */
2833 assert( p
->pOrderBy
!=0 );
2834 assert( pKeyDup
==0 ); /* "Managed" code needs this. Ticket #3382. */
2837 assert( v
!=0 ); /* Already thrown the error if VDBE alloc failed */
2838 labelEnd
= sqlite3VdbeMakeLabel(v
);
2839 labelCmpr
= sqlite3VdbeMakeLabel(v
);
2842 /* Patch up the ORDER BY clause
2846 assert( pPrior
->pOrderBy
==0 );
2847 pOrderBy
= p
->pOrderBy
;
2849 nOrderBy
= pOrderBy
->nExpr
;
2851 /* For operators other than UNION ALL we have to make sure that
2852 ** the ORDER BY clause covers every term of the result set. Add
2853 ** terms to the ORDER BY clause as necessary.
2856 for(i
=1; db
->mallocFailed
==0 && i
<=p
->pEList
->nExpr
; i
++){
2857 struct ExprList_item
*pItem
;
2858 for(j
=0, pItem
=pOrderBy
->a
; j
<nOrderBy
; j
++, pItem
++){
2859 assert( pItem
->u
.x
.iOrderByCol
>0 );
2860 if( pItem
->u
.x
.iOrderByCol
==i
) break;
2863 Expr
*pNew
= sqlite3Expr(db
, TK_INTEGER
, 0);
2864 if( pNew
==0 ) return SQLITE_NOMEM_BKPT
;
2865 pNew
->flags
|= EP_IntValue
;
2867 pOrderBy
= sqlite3ExprListAppend(pParse
, pOrderBy
, pNew
);
2868 if( pOrderBy
) pOrderBy
->a
[nOrderBy
++].u
.x
.iOrderByCol
= (u16
)i
;
2873 /* Compute the comparison permutation and keyinfo that is used with
2874 ** the permutation used to determine if the next
2875 ** row of results comes from selectA or selectB. Also add explicit
2876 ** collations to the ORDER BY clause terms so that when the subqueries
2877 ** to the right and the left are evaluated, they use the correct
2880 aPermute
= sqlite3DbMallocRawNN(db
, sizeof(int)*(nOrderBy
+ 1));
2882 struct ExprList_item
*pItem
;
2883 aPermute
[0] = nOrderBy
;
2884 for(i
=1, pItem
=pOrderBy
->a
; i
<=nOrderBy
; i
++, pItem
++){
2885 assert( pItem
->u
.x
.iOrderByCol
>0 );
2886 assert( pItem
->u
.x
.iOrderByCol
<=p
->pEList
->nExpr
);
2887 aPermute
[i
] = pItem
->u
.x
.iOrderByCol
- 1;
2889 pKeyMerge
= multiSelectOrderByKeyInfo(pParse
, p
, 1);
2894 /* Reattach the ORDER BY clause to the query.
2896 p
->pOrderBy
= pOrderBy
;
2897 pPrior
->pOrderBy
= sqlite3ExprListDup(pParse
->db
, pOrderBy
, 0);
2899 /* Allocate a range of temporary registers and the KeyInfo needed
2900 ** for the logic that removes duplicate result rows when the
2901 ** operator is UNION, EXCEPT, or INTERSECT (but not UNION ALL).
2906 int nExpr
= p
->pEList
->nExpr
;
2907 assert( nOrderBy
>=nExpr
|| db
->mallocFailed
);
2908 regPrev
= pParse
->nMem
+1;
2909 pParse
->nMem
+= nExpr
+1;
2910 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regPrev
);
2911 pKeyDup
= sqlite3KeyInfoAlloc(db
, nExpr
, 1);
2913 assert( sqlite3KeyInfoIsWriteable(pKeyDup
) );
2914 for(i
=0; i
<nExpr
; i
++){
2915 pKeyDup
->aColl
[i
] = multiSelectCollSeq(pParse
, p
, i
);
2916 pKeyDup
->aSortOrder
[i
] = 0;
2921 /* Separate the left and the right query from one another
2925 sqlite3ResolveOrderGroupBy(pParse
, p
, p
->pOrderBy
, "ORDER");
2926 if( pPrior
->pPrior
==0 ){
2927 sqlite3ResolveOrderGroupBy(pParse
, pPrior
, pPrior
->pOrderBy
, "ORDER");
2930 /* Compute the limit registers */
2931 computeLimitRegisters(pParse
, p
, labelEnd
);
2932 if( p
->iLimit
&& op
==TK_ALL
){
2933 regLimitA
= ++pParse
->nMem
;
2934 regLimitB
= ++pParse
->nMem
;
2935 sqlite3VdbeAddOp2(v
, OP_Copy
, p
->iOffset
? p
->iOffset
+1 : p
->iLimit
,
2937 sqlite3VdbeAddOp2(v
, OP_Copy
, regLimitA
, regLimitB
);
2939 regLimitA
= regLimitB
= 0;
2941 sqlite3ExprDelete(db
, p
->pLimit
);
2943 sqlite3ExprDelete(db
, p
->pOffset
);
2946 regAddrA
= ++pParse
->nMem
;
2947 regAddrB
= ++pParse
->nMem
;
2948 regOutA
= ++pParse
->nMem
;
2949 regOutB
= ++pParse
->nMem
;
2950 sqlite3SelectDestInit(&destA
, SRT_Coroutine
, regAddrA
);
2951 sqlite3SelectDestInit(&destB
, SRT_Coroutine
, regAddrB
);
2953 /* Generate a coroutine to evaluate the SELECT statement to the
2954 ** left of the compound operator - the "A" select.
2956 addrSelectA
= sqlite3VdbeCurrentAddr(v
) + 1;
2957 addr1
= sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regAddrA
, 0, addrSelectA
);
2958 VdbeComment((v
, "left SELECT"));
2959 pPrior
->iLimit
= regLimitA
;
2960 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2961 sqlite3Select(pParse
, pPrior
, &destA
);
2962 sqlite3VdbeEndCoroutine(v
, regAddrA
);
2963 sqlite3VdbeJumpHere(v
, addr1
);
2965 /* Generate a coroutine to evaluate the SELECT statement on
2966 ** the right - the "B" select
2968 addrSelectB
= sqlite3VdbeCurrentAddr(v
) + 1;
2969 addr1
= sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regAddrB
, 0, addrSelectB
);
2970 VdbeComment((v
, "right SELECT"));
2971 savedLimit
= p
->iLimit
;
2972 savedOffset
= p
->iOffset
;
2973 p
->iLimit
= regLimitB
;
2975 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
2976 sqlite3Select(pParse
, p
, &destB
);
2977 p
->iLimit
= savedLimit
;
2978 p
->iOffset
= savedOffset
;
2979 sqlite3VdbeEndCoroutine(v
, regAddrB
);
2981 /* Generate a subroutine that outputs the current row of the A
2982 ** select as the next output row of the compound select.
2984 VdbeNoopComment((v
, "Output routine for A"));
2985 addrOutA
= generateOutputSubroutine(pParse
,
2986 p
, &destA
, pDest
, regOutA
,
2987 regPrev
, pKeyDup
, labelEnd
);
2989 /* Generate a subroutine that outputs the current row of the B
2990 ** select as the next output row of the compound select.
2992 if( op
==TK_ALL
|| op
==TK_UNION
){
2993 VdbeNoopComment((v
, "Output routine for B"));
2994 addrOutB
= generateOutputSubroutine(pParse
,
2995 p
, &destB
, pDest
, regOutB
,
2996 regPrev
, pKeyDup
, labelEnd
);
2998 sqlite3KeyInfoUnref(pKeyDup
);
3000 /* Generate a subroutine to run when the results from select A
3001 ** are exhausted and only data in select B remains.
3003 if( op
==TK_EXCEPT
|| op
==TK_INTERSECT
){
3004 addrEofA_noB
= addrEofA
= labelEnd
;
3006 VdbeNoopComment((v
, "eof-A subroutine"));
3007 addrEofA
= sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutB
, addrOutB
);
3008 addrEofA_noB
= sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrB
, labelEnd
);
3010 sqlite3VdbeGoto(v
, addrEofA
);
3011 p
->nSelectRow
= sqlite3LogEstAdd(p
->nSelectRow
, pPrior
->nSelectRow
);
3014 /* Generate a subroutine to run when the results from select B
3015 ** are exhausted and only data in select A remains.
3017 if( op
==TK_INTERSECT
){
3018 addrEofB
= addrEofA
;
3019 if( p
->nSelectRow
> pPrior
->nSelectRow
) p
->nSelectRow
= pPrior
->nSelectRow
;
3021 VdbeNoopComment((v
, "eof-B subroutine"));
3022 addrEofB
= sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutA
, addrOutA
);
3023 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrA
, labelEnd
); VdbeCoverage(v
);
3024 sqlite3VdbeGoto(v
, addrEofB
);
3027 /* Generate code to handle the case of A<B
3029 VdbeNoopComment((v
, "A-lt-B subroutine"));
3030 addrAltB
= sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutA
, addrOutA
);
3031 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrA
, addrEofA
); VdbeCoverage(v
);
3032 sqlite3VdbeGoto(v
, labelCmpr
);
3034 /* Generate code to handle the case of A==B
3037 addrAeqB
= addrAltB
;
3038 }else if( op
==TK_INTERSECT
){
3039 addrAeqB
= addrAltB
;
3042 VdbeNoopComment((v
, "A-eq-B subroutine"));
3044 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrA
, addrEofA
); VdbeCoverage(v
);
3045 sqlite3VdbeGoto(v
, labelCmpr
);
3048 /* Generate code to handle the case of A>B
3050 VdbeNoopComment((v
, "A-gt-B subroutine"));
3051 addrAgtB
= sqlite3VdbeCurrentAddr(v
);
3052 if( op
==TK_ALL
|| op
==TK_UNION
){
3053 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutB
, addrOutB
);
3055 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrB
, addrEofB
); VdbeCoverage(v
);
3056 sqlite3VdbeGoto(v
, labelCmpr
);
3058 /* This code runs once to initialize everything.
3060 sqlite3VdbeJumpHere(v
, addr1
);
3061 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrA
, addrEofA_noB
); VdbeCoverage(v
);
3062 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrB
, addrEofB
); VdbeCoverage(v
);
3064 /* Implement the main merge loop
3066 sqlite3VdbeResolveLabel(v
, labelCmpr
);
3067 sqlite3VdbeAddOp4(v
, OP_Permutation
, 0, 0, 0, (char*)aPermute
, P4_INTARRAY
);
3068 sqlite3VdbeAddOp4(v
, OP_Compare
, destA
.iSdst
, destB
.iSdst
, nOrderBy
,
3069 (char*)pKeyMerge
, P4_KEYINFO
);
3070 sqlite3VdbeChangeP5(v
, OPFLAG_PERMUTE
);
3071 sqlite3VdbeAddOp3(v
, OP_Jump
, addrAltB
, addrAeqB
, addrAgtB
); VdbeCoverage(v
);
3073 /* Jump to the this point in order to terminate the query.
3075 sqlite3VdbeResolveLabel(v
, labelEnd
);
3077 /* Set the number of output columns
3079 if( pDest
->eDest
==SRT_Output
){
3080 Select
*pFirst
= pPrior
;
3081 while( pFirst
->pPrior
) pFirst
= pFirst
->pPrior
;
3082 generateColumnNames(pParse
, pFirst
->pSrc
, pFirst
->pEList
);
3085 /* Reassembly the compound query so that it will be freed correctly
3086 ** by the calling function */
3088 sqlite3SelectDelete(db
, p
->pPrior
);
3093 /*** TBD: Insert subroutine calls to close cursors on incomplete
3094 **** subqueries ****/
3095 explainComposite(pParse
, p
->op
, iSub1
, iSub2
, 0);
3096 return pParse
->nErr
!=0;
3100 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
3101 /* Forward Declarations */
3102 static void substExprList(sqlite3
*, ExprList
*, int, ExprList
*);
3103 static void substSelect(sqlite3
*, Select
*, int, ExprList
*, int);
3106 ** Scan through the expression pExpr. Replace every reference to
3107 ** a column in table number iTable with a copy of the iColumn-th
3108 ** entry in pEList. (But leave references to the ROWID column
3111 ** This routine is part of the flattening procedure. A subquery
3112 ** whose result set is defined by pEList appears as entry in the
3113 ** FROM clause of a SELECT such that the VDBE cursor assigned to that
3114 ** FORM clause entry is iTable. This routine make the necessary
3115 ** changes to pExpr so that it refers directly to the source table
3116 ** of the subquery rather the result set of the subquery.
3118 static Expr
*substExpr(
3119 sqlite3
*db
, /* Report malloc errors to this connection */
3120 Expr
*pExpr
, /* Expr in which substitution occurs */
3121 int iTable
, /* Table to be substituted */
3122 ExprList
*pEList
/* Substitute expressions */
3124 if( pExpr
==0 ) return 0;
3125 if( pExpr
->op
==TK_COLUMN
&& pExpr
->iTable
==iTable
){
3126 if( pExpr
->iColumn
<0 ){
3127 pExpr
->op
= TK_NULL
;
3130 assert( pEList
!=0 && pExpr
->iColumn
<pEList
->nExpr
);
3131 assert( pExpr
->pLeft
==0 && pExpr
->pRight
==0 );
3132 pNew
= sqlite3ExprDup(db
, pEList
->a
[pExpr
->iColumn
].pExpr
, 0);
3133 sqlite3ExprDelete(db
, pExpr
);
3137 pExpr
->pLeft
= substExpr(db
, pExpr
->pLeft
, iTable
, pEList
);
3138 pExpr
->pRight
= substExpr(db
, pExpr
->pRight
, iTable
, pEList
);
3139 if( ExprHasProperty(pExpr
, EP_xIsSelect
) ){
3140 substSelect(db
, pExpr
->x
.pSelect
, iTable
, pEList
, 1);
3142 substExprList(db
, pExpr
->x
.pList
, iTable
, pEList
);
3147 static void substExprList(
3148 sqlite3
*db
, /* Report malloc errors here */
3149 ExprList
*pList
, /* List to scan and in which to make substitutes */
3150 int iTable
, /* Table to be substituted */
3151 ExprList
*pEList
/* Substitute values */
3154 if( pList
==0 ) return;
3155 for(i
=0; i
<pList
->nExpr
; i
++){
3156 pList
->a
[i
].pExpr
= substExpr(db
, pList
->a
[i
].pExpr
, iTable
, pEList
);
3159 static void substSelect(
3160 sqlite3
*db
, /* Report malloc errors here */
3161 Select
*p
, /* SELECT statement in which to make substitutions */
3162 int iTable
, /* Table to be replaced */
3163 ExprList
*pEList
, /* Substitute values */
3164 int doPrior
/* Do substitutes on p->pPrior too */
3167 struct SrcList_item
*pItem
;
3171 substExprList(db
, p
->pEList
, iTable
, pEList
);
3172 substExprList(db
, p
->pGroupBy
, iTable
, pEList
);
3173 substExprList(db
, p
->pOrderBy
, iTable
, pEList
);
3174 p
->pHaving
= substExpr(db
, p
->pHaving
, iTable
, pEList
);
3175 p
->pWhere
= substExpr(db
, p
->pWhere
, iTable
, pEList
);
3178 for(i
=pSrc
->nSrc
, pItem
=pSrc
->a
; i
>0; i
--, pItem
++){
3179 substSelect(db
, pItem
->pSelect
, iTable
, pEList
, 1);
3180 if( pItem
->fg
.isTabFunc
){
3181 substExprList(db
, pItem
->u1
.pFuncArg
, iTable
, pEList
);
3184 }while( doPrior
&& (p
= p
->pPrior
)!=0 );
3186 #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
3188 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
3190 ** This routine attempts to flatten subqueries as a performance optimization.
3191 ** This routine returns 1 if it makes changes and 0 if no flattening occurs.
3193 ** To understand the concept of flattening, consider the following
3196 ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
3198 ** The default way of implementing this query is to execute the
3199 ** subquery first and store the results in a temporary table, then
3200 ** run the outer query on that temporary table. This requires two
3201 ** passes over the data. Furthermore, because the temporary table
3202 ** has no indices, the WHERE clause on the outer query cannot be
3205 ** This routine attempts to rewrite queries such as the above into
3206 ** a single flat select, like this:
3208 ** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
3210 ** The code generated for this simplification gives the same result
3211 ** but only has to scan the data once. And because indices might
3212 ** exist on the table t1, a complete scan of the data might be
3215 ** Flattening is only attempted if all of the following are true:
3217 ** (1) The subquery and the outer query do not both use aggregates.
3219 ** (2) The subquery is not an aggregate or (2a) the outer query is not a join
3220 ** and (2b) the outer query does not use subqueries other than the one
3221 ** FROM-clause subquery that is a candidate for flattening. (2b is
3222 ** due to ticket [2f7170d73bf9abf80] from 2015-02-09.)
3224 ** (3) The subquery is not the right operand of a left outer join
3225 ** (Originally ticket #306. Strengthened by ticket #3300)
3227 ** (4) The subquery is not DISTINCT.
3229 ** (**) At one point restrictions (4) and (5) defined a subset of DISTINCT
3230 ** sub-queries that were excluded from this optimization. Restriction
3231 ** (4) has since been expanded to exclude all DISTINCT subqueries.
3233 ** (6) The subquery does not use aggregates or the outer query is not
3236 ** (7) The subquery has a FROM clause. TODO: For subqueries without
3237 ** A FROM clause, consider adding a FROM close with the special
3238 ** table sqlite_once that consists of a single row containing a
3241 ** (8) The subquery does not use LIMIT or the outer query is not a join.
3243 ** (9) The subquery does not use LIMIT or the outer query does not use
3246 ** (**) Restriction (10) was removed from the code on 2005-02-05 but we
3247 ** accidently carried the comment forward until 2014-09-15. Original
3248 ** text: "The subquery does not use aggregates or the outer query
3249 ** does not use LIMIT."
3251 ** (11) The subquery and the outer query do not both have ORDER BY clauses.
3253 ** (**) Not implemented. Subsumed into restriction (3). Was previously
3254 ** a separate restriction deriving from ticket #350.
3256 ** (13) The subquery and outer query do not both use LIMIT.
3258 ** (14) The subquery does not use OFFSET.
3260 ** (15) The outer query is not part of a compound select or the
3261 ** subquery does not have a LIMIT clause.
3262 ** (See ticket #2339 and ticket [02a8e81d44]).
3264 ** (16) The outer query is not an aggregate or the subquery does
3265 ** not contain ORDER BY. (Ticket #2942) This used to not matter
3266 ** until we introduced the group_concat() function.
3268 ** (17) The sub-query is not a compound select, or it is a UNION ALL
3269 ** compound clause made up entirely of non-aggregate queries, and
3270 ** the parent query:
3272 ** * is not itself part of a compound select,
3273 ** * is not an aggregate or DISTINCT query, and
3276 ** The parent and sub-query may contain WHERE clauses. Subject to
3277 ** rules (11), (13) and (14), they may also contain ORDER BY,
3278 ** LIMIT and OFFSET clauses. The subquery cannot use any compound
3279 ** operator other than UNION ALL because all the other compound
3280 ** operators have an implied DISTINCT which is disallowed by
3283 ** Also, each component of the sub-query must return the same number
3284 ** of result columns. This is actually a requirement for any compound
3285 ** SELECT statement, but all the code here does is make sure that no
3286 ** such (illegal) sub-query is flattened. The caller will detect the
3287 ** syntax error and return a detailed message.
3289 ** (18) If the sub-query is a compound select, then all terms of the
3290 ** ORDER by clause of the parent must be simple references to
3291 ** columns of the sub-query.
3293 ** (19) The subquery does not use LIMIT or the outer query does not
3294 ** have a WHERE clause.
3296 ** (20) If the sub-query is a compound select, then it must not use
3297 ** an ORDER BY clause. Ticket #3773. We could relax this constraint
3298 ** somewhat by saying that the terms of the ORDER BY clause must
3299 ** appear as unmodified result columns in the outer query. But we
3300 ** have other optimizations in mind to deal with that case.
3302 ** (21) The subquery does not use LIMIT or the outer query is not
3303 ** DISTINCT. (See ticket [752e1646fc]).
3305 ** (22) The subquery is not a recursive CTE.
3307 ** (23) The parent is not a recursive CTE, or the sub-query is not a
3308 ** compound query. This restriction is because transforming the
3309 ** parent to a compound query confuses the code that handles
3310 ** recursive queries in multiSelect().
3312 ** (24) The subquery is not an aggregate that uses the built-in min() or
3313 ** or max() functions. (Without this restriction, a query like:
3314 ** "SELECT x FROM (SELECT max(y), x FROM t1)" would not necessarily
3315 ** return the value X for which Y was maximal.)
3318 ** In this routine, the "p" parameter is a pointer to the outer query.
3319 ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
3320 ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
3322 ** If flattening is not attempted, this routine is a no-op and returns 0.
3323 ** If flattening is attempted this routine returns 1.
3325 ** All of the expression analysis must occur on both the outer query and
3326 ** the subquery before this routine runs.
3328 static int flattenSubquery(
3329 Parse
*pParse
, /* Parsing context */
3330 Select
*p
, /* The parent or outer SELECT statement */
3331 int iFrom
, /* Index in p->pSrc->a[] of the inner subquery */
3332 int isAgg
, /* True if outer SELECT uses aggregate functions */
3333 int subqueryIsAgg
/* True if the subquery uses aggregate functions */
3335 const char *zSavedAuthContext
= pParse
->zAuthContext
;
3336 Select
*pParent
; /* Current UNION ALL term of the other query */
3337 Select
*pSub
; /* The inner query or "subquery" */
3338 Select
*pSub1
; /* Pointer to the rightmost select in sub-query */
3339 SrcList
*pSrc
; /* The FROM clause of the outer query */
3340 SrcList
*pSubSrc
; /* The FROM clause of the subquery */
3341 ExprList
*pList
; /* The result set of the outer query */
3342 int iParent
; /* VDBE cursor number of the pSub result set temp table */
3343 int i
; /* Loop counter */
3344 Expr
*pWhere
; /* The WHERE clause */
3345 struct SrcList_item
*pSubitem
; /* The subquery */
3346 sqlite3
*db
= pParse
->db
;
3348 /* Check to see if flattening is permitted. Return 0 if not.
3351 assert( p
->pPrior
==0 ); /* Unable to flatten compound queries */
3352 if( OptimizationDisabled(db
, SQLITE_QueryFlattener
) ) return 0;
3354 assert( pSrc
&& iFrom
>=0 && iFrom
<pSrc
->nSrc
);
3355 pSubitem
= &pSrc
->a
[iFrom
];
3356 iParent
= pSubitem
->iCursor
;
3357 pSub
= pSubitem
->pSelect
;
3359 if( subqueryIsAgg
){
3360 if( isAgg
) return 0; /* Restriction (1) */
3361 if( pSrc
->nSrc
>1 ) return 0; /* Restriction (2a) */
3362 if( (p
->pWhere
&& ExprHasProperty(p
->pWhere
,EP_Subquery
))
3363 || (sqlite3ExprListFlags(p
->pEList
) & EP_Subquery
)!=0
3364 || (sqlite3ExprListFlags(p
->pOrderBy
) & EP_Subquery
)!=0
3366 return 0; /* Restriction (2b) */
3370 pSubSrc
= pSub
->pSrc
;
3372 /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
3373 ** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET
3374 ** because they could be computed at compile-time. But when LIMIT and OFFSET
3375 ** became arbitrary expressions, we were forced to add restrictions (13)
3377 if( pSub
->pLimit
&& p
->pLimit
) return 0; /* Restriction (13) */
3378 if( pSub
->pOffset
) return 0; /* Restriction (14) */
3379 if( (p
->selFlags
& SF_Compound
)!=0 && pSub
->pLimit
){
3380 return 0; /* Restriction (15) */
3382 if( pSubSrc
->nSrc
==0 ) return 0; /* Restriction (7) */
3383 if( pSub
->selFlags
& SF_Distinct
) return 0; /* Restriction (5) */
3384 if( pSub
->pLimit
&& (pSrc
->nSrc
>1 || isAgg
) ){
3385 return 0; /* Restrictions (8)(9) */
3387 if( (p
->selFlags
& SF_Distinct
)!=0 && subqueryIsAgg
){
3388 return 0; /* Restriction (6) */
3390 if( p
->pOrderBy
&& pSub
->pOrderBy
){
3391 return 0; /* Restriction (11) */
3393 if( isAgg
&& pSub
->pOrderBy
) return 0; /* Restriction (16) */
3394 if( pSub
->pLimit
&& p
->pWhere
) return 0; /* Restriction (19) */
3395 if( pSub
->pLimit
&& (p
->selFlags
& SF_Distinct
)!=0 ){
3396 return 0; /* Restriction (21) */
3398 testcase( pSub
->selFlags
& SF_Recursive
);
3399 testcase( pSub
->selFlags
& SF_MinMaxAgg
);
3400 if( pSub
->selFlags
& (SF_Recursive
|SF_MinMaxAgg
) ){
3401 return 0; /* Restrictions (22) and (24) */
3403 if( (p
->selFlags
& SF_Recursive
) && pSub
->pPrior
){
3404 return 0; /* Restriction (23) */
3407 /* OBSOLETE COMMENT 1:
3408 ** Restriction 3: If the subquery is a join, make sure the subquery is
3409 ** not used as the right operand of an outer join. Examples of why this
3412 ** t1 LEFT OUTER JOIN (t2 JOIN t3)
3414 ** If we flatten the above, we would get
3416 ** (t1 LEFT OUTER JOIN t2) JOIN t3
3418 ** which is not at all the same thing.
3420 ** OBSOLETE COMMENT 2:
3421 ** Restriction 12: If the subquery is the right operand of a left outer
3422 ** join, make sure the subquery has no WHERE clause.
3423 ** An examples of why this is not allowed:
3425 ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
3427 ** If we flatten the above, we would get
3429 ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
3431 ** But the t2.x>0 test will always fail on a NULL row of t2, which
3432 ** effectively converts the OUTER JOIN into an INNER JOIN.
3434 ** THIS OVERRIDES OBSOLETE COMMENTS 1 AND 2 ABOVE:
3435 ** Ticket #3300 shows that flattening the right term of a LEFT JOIN
3436 ** is fraught with danger. Best to avoid the whole thing. If the
3437 ** subquery is the right term of a LEFT JOIN, then do not flatten.
3439 if( (pSubitem
->fg
.jointype
& JT_OUTER
)!=0 ){
3443 /* Restriction 17: If the sub-query is a compound SELECT, then it must
3444 ** use only the UNION ALL operator. And none of the simple select queries
3445 ** that make up the compound SELECT are allowed to be aggregate or distinct
3449 if( pSub
->pOrderBy
){
3450 return 0; /* Restriction 20 */
3452 if( isAgg
|| (p
->selFlags
& SF_Distinct
)!=0 || pSrc
->nSrc
!=1 ){
3455 for(pSub1
=pSub
; pSub1
; pSub1
=pSub1
->pPrior
){
3456 testcase( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Distinct
);
3457 testcase( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Aggregate
);
3458 assert( pSub
->pSrc
!=0 );
3459 assert( pSub
->pEList
->nExpr
==pSub1
->pEList
->nExpr
);
3460 if( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))!=0
3461 || (pSub1
->pPrior
&& pSub1
->op
!=TK_ALL
)
3462 || pSub1
->pSrc
->nSrc
<1
3466 testcase( pSub1
->pSrc
->nSrc
>1 );
3469 /* Restriction 18. */
3472 for(ii
=0; ii
<p
->pOrderBy
->nExpr
; ii
++){
3473 if( p
->pOrderBy
->a
[ii
].u
.x
.iOrderByCol
==0 ) return 0;
3478 /***** If we reach this point, flattening is permitted. *****/
3479 SELECTTRACE(1,pParse
,p
,("flatten %s.%p from term %d\n",
3480 pSub
->zSelName
, pSub
, iFrom
));
3482 /* Authorize the subquery */
3483 pParse
->zAuthContext
= pSubitem
->zName
;
3484 TESTONLY(i
=) sqlite3AuthCheck(pParse
, SQLITE_SELECT
, 0, 0, 0);
3485 testcase( i
==SQLITE_DENY
);
3486 pParse
->zAuthContext
= zSavedAuthContext
;
3488 /* If the sub-query is a compound SELECT statement, then (by restrictions
3489 ** 17 and 18 above) it must be a UNION ALL and the parent query must
3492 ** SELECT <expr-list> FROM (<sub-query>) <where-clause>
3494 ** followed by any ORDER BY, LIMIT and/or OFFSET clauses. This block
3495 ** creates N-1 copies of the parent query without any ORDER BY, LIMIT or
3496 ** OFFSET clauses and joins them to the left-hand-side of the original
3497 ** using UNION ALL operators. In this case N is the number of simple
3498 ** select statements in the compound sub-query.
3502 ** SELECT a+1 FROM (
3503 ** SELECT x FROM tab
3505 ** SELECT y FROM tab
3507 ** SELECT abs(z*2) FROM tab2
3508 ** ) WHERE a!=5 ORDER BY 1
3510 ** Transformed into:
3512 ** SELECT x+1 FROM tab WHERE x+1!=5
3514 ** SELECT y+1 FROM tab WHERE y+1!=5
3516 ** SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5
3519 ** We call this the "compound-subquery flattening".
3521 for(pSub
=pSub
->pPrior
; pSub
; pSub
=pSub
->pPrior
){
3523 ExprList
*pOrderBy
= p
->pOrderBy
;
3524 Expr
*pLimit
= p
->pLimit
;
3525 Expr
*pOffset
= p
->pOffset
;
3526 Select
*pPrior
= p
->pPrior
;
3532 pNew
= sqlite3SelectDup(db
, p
, 0);
3533 sqlite3SelectSetName(pNew
, pSub
->zSelName
);
3534 p
->pOffset
= pOffset
;
3536 p
->pOrderBy
= pOrderBy
;
3542 pNew
->pPrior
= pPrior
;
3543 if( pPrior
) pPrior
->pNext
= pNew
;
3546 SELECTTRACE(2,pParse
,p
,
3547 ("compound-subquery flattener creates %s.%p as peer\n",
3548 pNew
->zSelName
, pNew
));
3550 if( db
->mallocFailed
) return 1;
3553 /* Begin flattening the iFrom-th entry of the FROM clause
3554 ** in the outer query.
3556 pSub
= pSub1
= pSubitem
->pSelect
;
3558 /* Delete the transient table structure associated with the
3561 sqlite3DbFree(db
, pSubitem
->zDatabase
);
3562 sqlite3DbFree(db
, pSubitem
->zName
);
3563 sqlite3DbFree(db
, pSubitem
->zAlias
);
3564 pSubitem
->zDatabase
= 0;
3565 pSubitem
->zName
= 0;
3566 pSubitem
->zAlias
= 0;
3567 pSubitem
->pSelect
= 0;
3569 /* Defer deleting the Table object associated with the
3570 ** subquery until code generation is
3571 ** complete, since there may still exist Expr.pTab entries that
3572 ** refer to the subquery even after flattening. Ticket #3346.
3574 ** pSubitem->pTab is always non-NULL by test restrictions and tests above.
3576 if( ALWAYS(pSubitem
->pTab
!=0) ){
3577 Table
*pTabToDel
= pSubitem
->pTab
;
3578 if( pTabToDel
->nRef
==1 ){
3579 Parse
*pToplevel
= sqlite3ParseToplevel(pParse
);
3580 pTabToDel
->pNextZombie
= pToplevel
->pZombieTab
;
3581 pToplevel
->pZombieTab
= pTabToDel
;
3588 /* The following loop runs once for each term in a compound-subquery
3589 ** flattening (as described above). If we are doing a different kind
3590 ** of flattening - a flattening other than a compound-subquery flattening -
3591 ** then this loop only runs once.
3593 ** This loop moves all of the FROM elements of the subquery into the
3594 ** the FROM clause of the outer query. Before doing this, remember
3595 ** the cursor number for the original outer query FROM element in
3596 ** iParent. The iParent cursor will never be used. Subsequent code
3597 ** will scan expressions looking for iParent references and replace
3598 ** those references with expressions that resolve to the subquery FROM
3599 ** elements we are now copying in.
3601 for(pParent
=p
; pParent
; pParent
=pParent
->pPrior
, pSub
=pSub
->pPrior
){
3604 pSubSrc
= pSub
->pSrc
; /* FROM clause of subquery */
3605 nSubSrc
= pSubSrc
->nSrc
; /* Number of terms in subquery FROM clause */
3606 pSrc
= pParent
->pSrc
; /* FROM clause of the outer query */
3609 assert( pParent
==p
); /* First time through the loop */
3610 jointype
= pSubitem
->fg
.jointype
;
3612 assert( pParent
!=p
); /* 2nd and subsequent times through the loop */
3613 pSrc
= pParent
->pSrc
= sqlite3SrcListAppend(db
, 0, 0, 0);
3615 assert( db
->mallocFailed
);
3620 /* The subquery uses a single slot of the FROM clause of the outer
3621 ** query. If the subquery has more than one element in its FROM clause,
3622 ** then expand the outer query to make space for it to hold all elements
3627 ** SELECT * FROM tabA, (SELECT * FROM sub1, sub2), tabB;
3629 ** The outer query has 3 slots in its FROM clause. One slot of the
3630 ** outer query (the middle slot) is used by the subquery. The next
3631 ** block of code will expand the outer query FROM clause to 4 slots.
3632 ** The middle slot is expanded to two slots in order to make space
3633 ** for the two elements in the FROM clause of the subquery.
3636 pParent
->pSrc
= pSrc
= sqlite3SrcListEnlarge(db
, pSrc
, nSubSrc
-1,iFrom
+1);
3637 if( db
->mallocFailed
){
3642 /* Transfer the FROM clause terms from the subquery into the
3645 for(i
=0; i
<nSubSrc
; i
++){
3646 sqlite3IdListDelete(db
, pSrc
->a
[i
+iFrom
].pUsing
);
3647 assert( pSrc
->a
[i
+iFrom
].fg
.isTabFunc
==0 );
3648 pSrc
->a
[i
+iFrom
] = pSubSrc
->a
[i
];
3649 memset(&pSubSrc
->a
[i
], 0, sizeof(pSubSrc
->a
[i
]));
3651 pSrc
->a
[iFrom
].fg
.jointype
= jointype
;
3653 /* Now begin substituting subquery result set expressions for
3654 ** references to the iParent in the outer query.
3658 ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
3659 ** \ \_____________ subquery __________/ /
3660 ** \_____________________ outer query ______________________________/
3662 ** We look at every expression in the outer query and every place we see
3663 ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
3665 pList
= pParent
->pEList
;
3666 for(i
=0; i
<pList
->nExpr
; i
++){
3667 if( pList
->a
[i
].zName
==0 ){
3668 char *zName
= sqlite3DbStrDup(db
, pList
->a
[i
].zSpan
);
3669 sqlite3Dequote(zName
);
3670 pList
->a
[i
].zName
= zName
;
3673 if( pSub
->pOrderBy
){
3674 /* At this point, any non-zero iOrderByCol values indicate that the
3675 ** ORDER BY column expression is identical to the iOrderByCol'th
3676 ** expression returned by SELECT statement pSub. Since these values
3677 ** do not necessarily correspond to columns in SELECT statement pParent,
3678 ** zero them before transfering the ORDER BY clause.
3680 ** Not doing this may cause an error if a subsequent call to this
3681 ** function attempts to flatten a compound sub-query into pParent
3682 ** (the only way this can happen is if the compound sub-query is
3683 ** currently part of pSub->pSrc). See ticket [d11a6e908f]. */
3684 ExprList
*pOrderBy
= pSub
->pOrderBy
;
3685 for(i
=0; i
<pOrderBy
->nExpr
; i
++){
3686 pOrderBy
->a
[i
].u
.x
.iOrderByCol
= 0;
3688 assert( pParent
->pOrderBy
==0 );
3689 assert( pSub
->pPrior
==0 );
3690 pParent
->pOrderBy
= pOrderBy
;
3693 pWhere
= sqlite3ExprDup(db
, pSub
->pWhere
, 0);
3694 if( subqueryIsAgg
){
3695 assert( pParent
->pHaving
==0 );
3696 pParent
->pHaving
= pParent
->pWhere
;
3697 pParent
->pWhere
= pWhere
;
3698 pParent
->pHaving
= sqlite3ExprAnd(db
,
3699 sqlite3ExprDup(db
, pSub
->pHaving
, 0), pParent
->pHaving
3701 assert( pParent
->pGroupBy
==0 );
3702 pParent
->pGroupBy
= sqlite3ExprListDup(db
, pSub
->pGroupBy
, 0);
3704 pParent
->pWhere
= sqlite3ExprAnd(db
, pWhere
, pParent
->pWhere
);
3706 substSelect(db
, pParent
, iParent
, pSub
->pEList
, 0);
3708 /* The flattened query is distinct if either the inner or the
3709 ** outer query is distinct.
3711 pParent
->selFlags
|= pSub
->selFlags
& SF_Distinct
;
3714 ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y;
3716 ** One is tempted to try to add a and b to combine the limits. But this
3717 ** does not work if either limit is negative.
3720 pParent
->pLimit
= pSub
->pLimit
;
3725 /* Finially, delete what is left of the subquery and return
3728 sqlite3SelectDelete(db
, pSub1
);
3730 #if SELECTTRACE_ENABLED
3731 if( sqlite3SelectTrace
& 0x100 ){
3732 SELECTTRACE(0x100,pParse
,p
,("After flattening:\n"));
3733 sqlite3TreeViewSelect(0, p
, 0);
3739 #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
3743 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
3745 ** Make copies of relevant WHERE clause terms of the outer query into
3746 ** the WHERE clause of subquery. Example:
3748 ** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1) WHERE x=5 AND y=10;
3750 ** Transformed into:
3752 ** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1 WHERE a=5 AND c-d=10)
3753 ** WHERE x=5 AND y=10;
3755 ** The hope is that the terms added to the inner query will make it more
3758 ** Do not attempt this optimization if:
3760 ** (1) The inner query is an aggregate. (In that case, we'd really want
3761 ** to copy the outer WHERE-clause terms onto the HAVING clause of the
3762 ** inner query. But they probably won't help there so do not bother.)
3764 ** (2) The inner query is the recursive part of a common table expression.
3766 ** (3) The inner query has a LIMIT clause (since the changes to the WHERE
3767 ** close would change the meaning of the LIMIT).
3769 ** (4) The inner query is the right operand of a LEFT JOIN. (The caller
3770 ** enforces this restriction since this routine does not have enough
3771 ** information to know.)
3773 ** (5) The WHERE clause expression originates in the ON or USING clause
3776 ** Return 0 if no changes are made and non-zero if one or more WHERE clause
3777 ** terms are duplicated into the subquery.
3779 static int pushDownWhereTerms(
3780 sqlite3
*db
, /* The database connection (for malloc()) */
3781 Select
*pSubq
, /* The subquery whose WHERE clause is to be augmented */
3782 Expr
*pWhere
, /* The WHERE clause of the outer query */
3783 int iCursor
/* Cursor number of the subquery */
3787 Select
*pX
; /* For looping over compound SELECTs in pSubq */
3788 if( pWhere
==0 ) return 0;
3789 for(pX
=pSubq
; pX
; pX
=pX
->pPrior
){
3790 if( (pX
->selFlags
& (SF_Aggregate
|SF_Recursive
))!=0 ){
3791 testcase( pX
->selFlags
& SF_Aggregate
);
3792 testcase( pX
->selFlags
& SF_Recursive
);
3793 testcase( pX
!=pSubq
);
3794 return 0; /* restrictions (1) and (2) */
3797 if( pSubq
->pLimit
!=0 ){
3798 return 0; /* restriction (3) */
3800 while( pWhere
->op
==TK_AND
){
3801 nChng
+= pushDownWhereTerms(db
, pSubq
, pWhere
->pRight
, iCursor
);
3802 pWhere
= pWhere
->pLeft
;
3804 if( ExprHasProperty(pWhere
,EP_FromJoin
) ) return 0; /* restriction 5 */
3805 if( sqlite3ExprIsTableConstant(pWhere
, iCursor
) ){
3808 pNew
= sqlite3ExprDup(db
, pWhere
, 0);
3809 pNew
= substExpr(db
, pNew
, iCursor
, pSubq
->pEList
);
3810 pSubq
->pWhere
= sqlite3ExprAnd(db
, pSubq
->pWhere
, pNew
);
3811 pSubq
= pSubq
->pPrior
;
3816 #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
3819 ** Based on the contents of the AggInfo structure indicated by the first
3820 ** argument, this function checks if the following are true:
3822 ** * the query contains just a single aggregate function,
3823 ** * the aggregate function is either min() or max(), and
3824 ** * the argument to the aggregate function is a column value.
3826 ** If all of the above are true, then WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX
3827 ** is returned as appropriate. Also, *ppMinMax is set to point to the
3828 ** list of arguments passed to the aggregate before returning.
3830 ** Or, if the conditions above are not met, *ppMinMax is set to 0 and
3831 ** WHERE_ORDERBY_NORMAL is returned.
3833 static u8
minMaxQuery(AggInfo
*pAggInfo
, ExprList
**ppMinMax
){
3834 int eRet
= WHERE_ORDERBY_NORMAL
; /* Return value */
3837 if( pAggInfo
->nFunc
==1 ){
3838 Expr
*pExpr
= pAggInfo
->aFunc
[0].pExpr
; /* Aggregate function */
3839 ExprList
*pEList
= pExpr
->x
.pList
; /* Arguments to agg function */
3841 assert( pExpr
->op
==TK_AGG_FUNCTION
);
3842 if( pEList
&& pEList
->nExpr
==1 && pEList
->a
[0].pExpr
->op
==TK_AGG_COLUMN
){
3843 const char *zFunc
= pExpr
->u
.zToken
;
3844 if( sqlite3StrICmp(zFunc
, "min")==0 ){
3845 eRet
= WHERE_ORDERBY_MIN
;
3847 }else if( sqlite3StrICmp(zFunc
, "max")==0 ){
3848 eRet
= WHERE_ORDERBY_MAX
;
3854 assert( *ppMinMax
==0 || (*ppMinMax
)->nExpr
==1 );
3859 ** The select statement passed as the first argument is an aggregate query.
3860 ** The second argument is the associated aggregate-info object. This
3861 ** function tests if the SELECT is of the form:
3863 ** SELECT count(*) FROM <tbl>
3865 ** where table is a database table, not a sub-select or view. If the query
3866 ** does match this pattern, then a pointer to the Table object representing
3867 ** <tbl> is returned. Otherwise, 0 is returned.
3869 static Table
*isSimpleCount(Select
*p
, AggInfo
*pAggInfo
){
3873 assert( !p
->pGroupBy
);
3875 if( p
->pWhere
|| p
->pEList
->nExpr
!=1
3876 || p
->pSrc
->nSrc
!=1 || p
->pSrc
->a
[0].pSelect
3880 pTab
= p
->pSrc
->a
[0].pTab
;
3881 pExpr
= p
->pEList
->a
[0].pExpr
;
3882 assert( pTab
&& !pTab
->pSelect
&& pExpr
);
3884 if( IsVirtual(pTab
) ) return 0;
3885 if( pExpr
->op
!=TK_AGG_FUNCTION
) return 0;
3886 if( NEVER(pAggInfo
->nFunc
==0) ) return 0;
3887 if( (pAggInfo
->aFunc
[0].pFunc
->funcFlags
&SQLITE_FUNC_COUNT
)==0 ) return 0;
3888 if( pExpr
->flags
&EP_Distinct
) return 0;
3894 ** If the source-list item passed as an argument was augmented with an
3895 ** INDEXED BY clause, then try to locate the specified index. If there
3896 ** was such a clause and the named index cannot be found, return
3897 ** SQLITE_ERROR and leave an error in pParse. Otherwise, populate
3898 ** pFrom->pIndex and return SQLITE_OK.
3900 int sqlite3IndexedByLookup(Parse
*pParse
, struct SrcList_item
*pFrom
){
3901 if( pFrom
->pTab
&& pFrom
->fg
.isIndexedBy
){
3902 Table
*pTab
= pFrom
->pTab
;
3903 char *zIndexedBy
= pFrom
->u1
.zIndexedBy
;
3905 for(pIdx
=pTab
->pIndex
;
3906 pIdx
&& sqlite3StrICmp(pIdx
->zName
, zIndexedBy
);
3910 sqlite3ErrorMsg(pParse
, "no such index: %s", zIndexedBy
, 0);
3911 pParse
->checkSchema
= 1;
3912 return SQLITE_ERROR
;
3914 pFrom
->pIBIndex
= pIdx
;
3919 ** Detect compound SELECT statements that use an ORDER BY clause with
3920 ** an alternative collating sequence.
3922 ** SELECT ... FROM t1 EXCEPT SELECT ... FROM t2 ORDER BY .. COLLATE ...
3924 ** These are rewritten as a subquery:
3926 ** SELECT * FROM (SELECT ... FROM t1 EXCEPT SELECT ... FROM t2)
3927 ** ORDER BY ... COLLATE ...
3929 ** This transformation is necessary because the multiSelectOrderBy() routine
3930 ** above that generates the code for a compound SELECT with an ORDER BY clause
3931 ** uses a merge algorithm that requires the same collating sequence on the
3932 ** result columns as on the ORDER BY clause. See ticket
3933 ** http://www.sqlite.org/src/info/6709574d2a
3935 ** This transformation is only needed for EXCEPT, INTERSECT, and UNION.
3936 ** The UNION ALL operator works fine with multiSelectOrderBy() even when
3937 ** there are COLLATE terms in the ORDER BY.
3939 static int convertCompoundSelectToSubquery(Walker
*pWalker
, Select
*p
){
3944 struct ExprList_item
*a
;
3949 if( p
->pPrior
==0 ) return WRC_Continue
;
3950 if( p
->pOrderBy
==0 ) return WRC_Continue
;
3951 for(pX
=p
; pX
&& (pX
->op
==TK_ALL
|| pX
->op
==TK_SELECT
); pX
=pX
->pPrior
){}
3952 if( pX
==0 ) return WRC_Continue
;
3954 for(i
=p
->pOrderBy
->nExpr
-1; i
>=0; i
--){
3955 if( a
[i
].pExpr
->flags
& EP_Collate
) break;
3957 if( i
<0 ) return WRC_Continue
;
3959 /* If we reach this point, that means the transformation is required. */
3961 pParse
= pWalker
->pParse
;
3963 pNew
= sqlite3DbMallocZero(db
, sizeof(*pNew
) );
3964 if( pNew
==0 ) return WRC_Abort
;
3965 memset(&dummy
, 0, sizeof(dummy
));
3966 pNewSrc
= sqlite3SrcListAppendFromTerm(pParse
,0,0,0,&dummy
,pNew
,0,0);
3967 if( pNewSrc
==0 ) return WRC_Abort
;
3970 p
->pEList
= sqlite3ExprListAppend(pParse
, 0, sqlite3Expr(db
, TK_ASTERISK
, 0));
3979 p
->selFlags
&= ~SF_Compound
;
3980 assert( (p
->selFlags
& SF_Converted
)==0 );
3981 p
->selFlags
|= SF_Converted
;
3982 assert( pNew
->pPrior
!=0 );
3983 pNew
->pPrior
->pNext
= pNew
;
3986 return WRC_Continue
;
3990 ** Check to see if the FROM clause term pFrom has table-valued function
3991 ** arguments. If it does, leave an error message in pParse and return
3992 ** non-zero, since pFrom is not allowed to be a table-valued function.
3994 static int cannotBeFunction(Parse
*pParse
, struct SrcList_item
*pFrom
){
3995 if( pFrom
->fg
.isTabFunc
){
3996 sqlite3ErrorMsg(pParse
, "'%s' is not a function", pFrom
->zName
);
4002 #ifndef SQLITE_OMIT_CTE
4004 ** Argument pWith (which may be NULL) points to a linked list of nested
4005 ** WITH contexts, from inner to outermost. If the table identified by
4006 ** FROM clause element pItem is really a common-table-expression (CTE)
4007 ** then return a pointer to the CTE definition for that table. Otherwise
4010 ** If a non-NULL value is returned, set *ppContext to point to the With
4011 ** object that the returned CTE belongs to.
4013 static struct Cte
*searchWith(
4014 With
*pWith
, /* Current innermost WITH clause */
4015 struct SrcList_item
*pItem
, /* FROM clause element to resolve */
4016 With
**ppContext
/* OUT: WITH clause return value belongs to */
4019 if( pItem
->zDatabase
==0 && (zName
= pItem
->zName
)!=0 ){
4021 for(p
=pWith
; p
; p
=p
->pOuter
){
4023 for(i
=0; i
<p
->nCte
; i
++){
4024 if( sqlite3StrICmp(zName
, p
->a
[i
].zName
)==0 ){
4034 /* The code generator maintains a stack of active WITH clauses
4035 ** with the inner-most WITH clause being at the top of the stack.
4037 ** This routine pushes the WITH clause passed as the second argument
4038 ** onto the top of the stack. If argument bFree is true, then this
4039 ** WITH clause will never be popped from the stack. In this case it
4040 ** should be freed along with the Parse object. In other cases, when
4041 ** bFree==0, the With object will be freed along with the SELECT
4042 ** statement with which it is associated.
4044 void sqlite3WithPush(Parse
*pParse
, With
*pWith
, u8 bFree
){
4045 assert( bFree
==0 || (pParse
->pWith
==0 && pParse
->pWithToFree
==0) );
4047 assert( pParse
->pWith
!=pWith
);
4048 pWith
->pOuter
= pParse
->pWith
;
4049 pParse
->pWith
= pWith
;
4050 if( bFree
) pParse
->pWithToFree
= pWith
;
4055 ** This function checks if argument pFrom refers to a CTE declared by
4056 ** a WITH clause on the stack currently maintained by the parser. And,
4057 ** if currently processing a CTE expression, if it is a recursive
4058 ** reference to the current CTE.
4060 ** If pFrom falls into either of the two categories above, pFrom->pTab
4061 ** and other fields are populated accordingly. The caller should check
4062 ** (pFrom->pTab!=0) to determine whether or not a successful match
4065 ** Whether or not a match is found, SQLITE_OK is returned if no error
4066 ** occurs. If an error does occur, an error message is stored in the
4067 ** parser and some error code other than SQLITE_OK returned.
4069 static int withExpand(
4071 struct SrcList_item
*pFrom
4073 Parse
*pParse
= pWalker
->pParse
;
4074 sqlite3
*db
= pParse
->db
;
4075 struct Cte
*pCte
; /* Matched CTE (or NULL if no match) */
4076 With
*pWith
; /* WITH clause that pCte belongs to */
4078 assert( pFrom
->pTab
==0 );
4080 pCte
= searchWith(pParse
->pWith
, pFrom
, &pWith
);
4085 Select
*pLeft
; /* Left-most SELECT statement */
4086 int bMayRecursive
; /* True if compound joined by UNION [ALL] */
4087 With
*pSavedWith
; /* Initial value of pParse->pWith */
4089 /* If pCte->zCteErr is non-NULL at this point, then this is an illegal
4090 ** recursive reference to CTE pCte. Leave an error in pParse and return
4091 ** early. If pCte->zCteErr is NULL, then this is not a recursive reference.
4092 ** In this case, proceed. */
4093 if( pCte
->zCteErr
){
4094 sqlite3ErrorMsg(pParse
, pCte
->zCteErr
, pCte
->zName
);
4095 return SQLITE_ERROR
;
4097 if( cannotBeFunction(pParse
, pFrom
) ) return SQLITE_ERROR
;
4099 assert( pFrom
->pTab
==0 );
4100 pFrom
->pTab
= pTab
= sqlite3DbMallocZero(db
, sizeof(Table
));
4101 if( pTab
==0 ) return WRC_Abort
;
4103 pTab
->zName
= sqlite3DbStrDup(db
, pCte
->zName
);
4105 pTab
->nRowLogEst
= 200; assert( 200==sqlite3LogEst(1048576) );
4106 pTab
->tabFlags
|= TF_Ephemeral
| TF_NoVisibleRowid
;
4107 pFrom
->pSelect
= sqlite3SelectDup(db
, pCte
->pSelect
, 0);
4108 if( db
->mallocFailed
) return SQLITE_NOMEM_BKPT
;
4109 assert( pFrom
->pSelect
);
4111 /* Check if this is a recursive CTE. */
4112 pSel
= pFrom
->pSelect
;
4113 bMayRecursive
= ( pSel
->op
==TK_ALL
|| pSel
->op
==TK_UNION
);
4114 if( bMayRecursive
){
4116 SrcList
*pSrc
= pFrom
->pSelect
->pSrc
;
4117 for(i
=0; i
<pSrc
->nSrc
; i
++){
4118 struct SrcList_item
*pItem
= &pSrc
->a
[i
];
4119 if( pItem
->zDatabase
==0
4121 && 0==sqlite3StrICmp(pItem
->zName
, pCte
->zName
)
4124 pItem
->fg
.isRecursive
= 1;
4126 pSel
->selFlags
|= SF_Recursive
;
4131 /* Only one recursive reference is permitted. */
4134 pParse
, "multiple references to recursive table: %s", pCte
->zName
4136 return SQLITE_ERROR
;
4138 assert( pTab
->nRef
==1 || ((pSel
->selFlags
&SF_Recursive
) && pTab
->nRef
==2 ));
4140 pCte
->zCteErr
= "circular reference: %s";
4141 pSavedWith
= pParse
->pWith
;
4142 pParse
->pWith
= pWith
;
4143 sqlite3WalkSelect(pWalker
, bMayRecursive
? pSel
->pPrior
: pSel
);
4144 pParse
->pWith
= pWith
;
4146 for(pLeft
=pSel
; pLeft
->pPrior
; pLeft
=pLeft
->pPrior
);
4147 pEList
= pLeft
->pEList
;
4149 if( pEList
&& pEList
->nExpr
!=pCte
->pCols
->nExpr
){
4150 sqlite3ErrorMsg(pParse
, "table %s has %d values for %d columns",
4151 pCte
->zName
, pEList
->nExpr
, pCte
->pCols
->nExpr
4153 pParse
->pWith
= pSavedWith
;
4154 return SQLITE_ERROR
;
4156 pEList
= pCte
->pCols
;
4159 sqlite3ColumnsFromExprList(pParse
, pEList
, &pTab
->nCol
, &pTab
->aCol
);
4160 if( bMayRecursive
){
4161 if( pSel
->selFlags
& SF_Recursive
){
4162 pCte
->zCteErr
= "multiple recursive references: %s";
4164 pCte
->zCteErr
= "recursive reference in a subquery: %s";
4166 sqlite3WalkSelect(pWalker
, pSel
);
4169 pParse
->pWith
= pSavedWith
;
4176 #ifndef SQLITE_OMIT_CTE
4178 ** If the SELECT passed as the second argument has an associated WITH
4179 ** clause, pop it from the stack stored as part of the Parse object.
4181 ** This function is used as the xSelectCallback2() callback by
4182 ** sqlite3SelectExpand() when walking a SELECT tree to resolve table
4183 ** names and other FROM clause elements.
4185 static void selectPopWith(Walker
*pWalker
, Select
*p
){
4186 Parse
*pParse
= pWalker
->pParse
;
4187 With
*pWith
= findRightmost(p
)->pWith
;
4189 assert( pParse
->pWith
==pWith
);
4190 pParse
->pWith
= pWith
->pOuter
;
4194 #define selectPopWith 0
4198 ** This routine is a Walker callback for "expanding" a SELECT statement.
4199 ** "Expanding" means to do the following:
4201 ** (1) Make sure VDBE cursor numbers have been assigned to every
4202 ** element of the FROM clause.
4204 ** (2) Fill in the pTabList->a[].pTab fields in the SrcList that
4205 ** defines FROM clause. When views appear in the FROM clause,
4206 ** fill pTabList->a[].pSelect with a copy of the SELECT statement
4207 ** that implements the view. A copy is made of the view's SELECT
4208 ** statement so that we can freely modify or delete that statement
4209 ** without worrying about messing up the persistent representation
4212 ** (3) Add terms to the WHERE clause to accommodate the NATURAL keyword
4213 ** on joins and the ON and USING clause of joins.
4215 ** (4) Scan the list of columns in the result set (pEList) looking
4216 ** for instances of the "*" operator or the TABLE.* operator.
4217 ** If found, expand each "*" to be every column in every table
4218 ** and TABLE.* to be every column in TABLE.
4221 static int selectExpander(Walker
*pWalker
, Select
*p
){
4222 Parse
*pParse
= pWalker
->pParse
;
4226 struct SrcList_item
*pFrom
;
4227 sqlite3
*db
= pParse
->db
;
4228 Expr
*pE
, *pRight
, *pExpr
;
4229 u16 selFlags
= p
->selFlags
;
4231 p
->selFlags
|= SF_Expanded
;
4232 if( db
->mallocFailed
){
4235 if( NEVER(p
->pSrc
==0) || (selFlags
& SF_Expanded
)!=0 ){
4240 if( pWalker
->xSelectCallback2
==selectPopWith
){
4241 sqlite3WithPush(pParse
, findRightmost(p
)->pWith
, 0);
4244 /* Make sure cursor numbers have been assigned to all entries in
4245 ** the FROM clause of the SELECT statement.
4247 sqlite3SrcListAssignCursors(pParse
, pTabList
);
4249 /* Look up every table named in the FROM clause of the select. If
4250 ** an entry of the FROM clause is a subquery instead of a table or view,
4251 ** then create a transient table structure to describe the subquery.
4253 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
4255 assert( pFrom
->fg
.isRecursive
==0 || pFrom
->pTab
!=0 );
4256 if( pFrom
->fg
.isRecursive
) continue;
4257 assert( pFrom
->pTab
==0 );
4258 #ifndef SQLITE_OMIT_CTE
4259 if( withExpand(pWalker
, pFrom
) ) return WRC_Abort
;
4260 if( pFrom
->pTab
) {} else
4262 if( pFrom
->zName
==0 ){
4263 #ifndef SQLITE_OMIT_SUBQUERY
4264 Select
*pSel
= pFrom
->pSelect
;
4265 /* A sub-query in the FROM clause of a SELECT */
4267 assert( pFrom
->pTab
==0 );
4268 if( sqlite3WalkSelect(pWalker
, pSel
) ) return WRC_Abort
;
4269 pFrom
->pTab
= pTab
= sqlite3DbMallocZero(db
, sizeof(Table
));
4270 if( pTab
==0 ) return WRC_Abort
;
4272 pTab
->zName
= sqlite3MPrintf(db
, "sqlite_sq_%p", (void*)pTab
);
4273 while( pSel
->pPrior
){ pSel
= pSel
->pPrior
; }
4274 sqlite3ColumnsFromExprList(pParse
, pSel
->pEList
,&pTab
->nCol
,&pTab
->aCol
);
4276 pTab
->nRowLogEst
= 200; assert( 200==sqlite3LogEst(1048576) );
4277 pTab
->tabFlags
|= TF_Ephemeral
;
4280 /* An ordinary table or view name in the FROM clause */
4281 assert( pFrom
->pTab
==0 );
4282 pFrom
->pTab
= pTab
= sqlite3LocateTableItem(pParse
, 0, pFrom
);
4283 if( pTab
==0 ) return WRC_Abort
;
4284 if( pTab
->nRef
==0xffff ){
4285 sqlite3ErrorMsg(pParse
, "too many references to \"%s\": max 65535",
4291 if( !IsVirtual(pTab
) && cannotBeFunction(pParse
, pFrom
) ){
4294 #if !defined(SQLITE_OMIT_VIEW) || !defined (SQLITE_OMIT_VIRTUALTABLE)
4295 if( IsVirtual(pTab
) || pTab
->pSelect
){
4297 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ) return WRC_Abort
;
4298 assert( pFrom
->pSelect
==0 );
4299 pFrom
->pSelect
= sqlite3SelectDup(db
, pTab
->pSelect
, 0);
4300 sqlite3SelectSetName(pFrom
->pSelect
, pTab
->zName
);
4303 sqlite3WalkSelect(pWalker
, pFrom
->pSelect
);
4309 /* Locate the index named by the INDEXED BY clause, if any. */
4310 if( sqlite3IndexedByLookup(pParse
, pFrom
) ){
4315 /* Process NATURAL keywords, and ON and USING clauses of joins.
4317 if( db
->mallocFailed
|| sqliteProcessJoin(pParse
, p
) ){
4321 /* For every "*" that occurs in the column list, insert the names of
4322 ** all columns in all tables. And for every TABLE.* insert the names
4323 ** of all columns in TABLE. The parser inserted a special expression
4324 ** with the TK_ASTERISK operator for each "*" that it found in the column
4325 ** list. The following code just has to locate the TK_ASTERISK
4326 ** expressions and expand each one to the list of all columns in
4329 ** The first loop just checks to see if there are any "*" operators
4330 ** that need expanding.
4332 for(k
=0; k
<pEList
->nExpr
; k
++){
4333 pE
= pEList
->a
[k
].pExpr
;
4334 if( pE
->op
==TK_ASTERISK
) break;
4335 assert( pE
->op
!=TK_DOT
|| pE
->pRight
!=0 );
4336 assert( pE
->op
!=TK_DOT
|| (pE
->pLeft
!=0 && pE
->pLeft
->op
==TK_ID
) );
4337 if( pE
->op
==TK_DOT
&& pE
->pRight
->op
==TK_ASTERISK
) break;
4339 if( k
<pEList
->nExpr
){
4341 ** If we get here it means the result set contains one or more "*"
4342 ** operators that need to be expanded. Loop through each expression
4343 ** in the result set and expand them one by one.
4345 struct ExprList_item
*a
= pEList
->a
;
4347 int flags
= pParse
->db
->flags
;
4348 int longNames
= (flags
& SQLITE_FullColNames
)!=0
4349 && (flags
& SQLITE_ShortColNames
)==0;
4351 for(k
=0; k
<pEList
->nExpr
; k
++){
4353 pRight
= pE
->pRight
;
4354 assert( pE
->op
!=TK_DOT
|| pRight
!=0 );
4355 if( pE
->op
!=TK_ASTERISK
4356 && (pE
->op
!=TK_DOT
|| pRight
->op
!=TK_ASTERISK
)
4358 /* This particular expression does not need to be expanded.
4360 pNew
= sqlite3ExprListAppend(pParse
, pNew
, a
[k
].pExpr
);
4362 pNew
->a
[pNew
->nExpr
-1].zName
= a
[k
].zName
;
4363 pNew
->a
[pNew
->nExpr
-1].zSpan
= a
[k
].zSpan
;
4369 /* This expression is a "*" or a "TABLE.*" and needs to be
4371 int tableSeen
= 0; /* Set to 1 when TABLE matches */
4372 char *zTName
= 0; /* text of name of TABLE */
4373 if( pE
->op
==TK_DOT
){
4374 assert( pE
->pLeft
!=0 );
4375 assert( !ExprHasProperty(pE
->pLeft
, EP_IntValue
) );
4376 zTName
= pE
->pLeft
->u
.zToken
;
4378 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
4379 Table
*pTab
= pFrom
->pTab
;
4380 Select
*pSub
= pFrom
->pSelect
;
4381 char *zTabName
= pFrom
->zAlias
;
4382 const char *zSchemaName
= 0;
4385 zTabName
= pTab
->zName
;
4387 if( db
->mallocFailed
) break;
4388 if( pSub
==0 || (pSub
->selFlags
& SF_NestedFrom
)==0 ){
4390 if( zTName
&& sqlite3StrICmp(zTName
, zTabName
)!=0 ){
4393 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
4394 zSchemaName
= iDb
>=0 ? db
->aDb
[iDb
].zDbSName
: "*";
4396 for(j
=0; j
<pTab
->nCol
; j
++){
4397 char *zName
= pTab
->aCol
[j
].zName
;
4398 char *zColname
; /* The computed column name */
4399 char *zToFree
; /* Malloced string that needs to be freed */
4400 Token sColname
; /* Computed column name as a token */
4404 && sqlite3MatchSpanName(pSub
->pEList
->a
[j
].zSpan
, 0, zTName
, 0)==0
4409 /* If a column is marked as 'hidden', omit it from the expanded
4410 ** result-set list unless the SELECT has the SF_IncludeHidden
4413 if( (p
->selFlags
& SF_IncludeHidden
)==0
4414 && IsHiddenColumn(&pTab
->aCol
[j
])
4420 if( i
>0 && zTName
==0 ){
4421 if( (pFrom
->fg
.jointype
& JT_NATURAL
)!=0
4422 && tableAndColumnIndex(pTabList
, i
, zName
, 0, 0)
4424 /* In a NATURAL join, omit the join columns from the
4425 ** table to the right of the join */
4428 if( sqlite3IdListIndex(pFrom
->pUsing
, zName
)>=0 ){
4429 /* In a join with a USING clause, omit columns in the
4430 ** using clause from the table on the right. */
4434 pRight
= sqlite3Expr(db
, TK_ID
, zName
);
4437 if( longNames
|| pTabList
->nSrc
>1 ){
4439 pLeft
= sqlite3Expr(db
, TK_ID
, zTabName
);
4440 pExpr
= sqlite3PExpr(pParse
, TK_DOT
, pLeft
, pRight
, 0);
4442 pLeft
= sqlite3Expr(db
, TK_ID
, zSchemaName
);
4443 pExpr
= sqlite3PExpr(pParse
, TK_DOT
, pLeft
, pExpr
, 0);
4446 zColname
= sqlite3MPrintf(db
, "%s.%s", zTabName
, zName
);
4452 pNew
= sqlite3ExprListAppend(pParse
, pNew
, pExpr
);
4453 sqlite3TokenInit(&sColname
, zColname
);
4454 sqlite3ExprListSetName(pParse
, pNew
, &sColname
, 0);
4455 if( pNew
&& (p
->selFlags
& SF_NestedFrom
)!=0 ){
4456 struct ExprList_item
*pX
= &pNew
->a
[pNew
->nExpr
-1];
4458 pX
->zSpan
= sqlite3DbStrDup(db
, pSub
->pEList
->a
[j
].zSpan
);
4459 testcase( pX
->zSpan
==0 );
4461 pX
->zSpan
= sqlite3MPrintf(db
, "%s.%s.%s",
4462 zSchemaName
, zTabName
, zColname
);
4463 testcase( pX
->zSpan
==0 );
4467 sqlite3DbFree(db
, zToFree
);
4472 sqlite3ErrorMsg(pParse
, "no such table: %s", zTName
);
4474 sqlite3ErrorMsg(pParse
, "no tables specified");
4479 sqlite3ExprListDelete(db
, pEList
);
4482 #if SQLITE_MAX_COLUMN
4483 if( p
->pEList
&& p
->pEList
->nExpr
>db
->aLimit
[SQLITE_LIMIT_COLUMN
] ){
4484 sqlite3ErrorMsg(pParse
, "too many columns in result set");
4488 return WRC_Continue
;
4492 ** No-op routine for the parse-tree walker.
4494 ** When this routine is the Walker.xExprCallback then expression trees
4495 ** are walked without any actions being taken at each node. Presumably,
4496 ** when this routine is used for Walker.xExprCallback then
4497 ** Walker.xSelectCallback is set to do something useful for every
4498 ** subquery in the parser tree.
4500 int sqlite3ExprWalkNoop(Walker
*NotUsed
, Expr
*NotUsed2
){
4501 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
4502 return WRC_Continue
;
4506 ** This routine "expands" a SELECT statement and all of its subqueries.
4507 ** For additional information on what it means to "expand" a SELECT
4508 ** statement, see the comment on the selectExpand worker callback above.
4510 ** Expanding a SELECT statement is the first step in processing a
4511 ** SELECT statement. The SELECT statement must be expanded before
4512 ** name resolution is performed.
4514 ** If anything goes wrong, an error message is written into pParse.
4515 ** The calling function can detect the problem by looking at pParse->nErr
4516 ** and/or pParse->db->mallocFailed.
4518 static void sqlite3SelectExpand(Parse
*pParse
, Select
*pSelect
){
4520 memset(&w
, 0, sizeof(w
));
4521 w
.xExprCallback
= sqlite3ExprWalkNoop
;
4523 if( pParse
->hasCompound
){
4524 w
.xSelectCallback
= convertCompoundSelectToSubquery
;
4525 sqlite3WalkSelect(&w
, pSelect
);
4527 w
.xSelectCallback
= selectExpander
;
4528 if( (pSelect
->selFlags
& SF_MultiValue
)==0 ){
4529 w
.xSelectCallback2
= selectPopWith
;
4531 sqlite3WalkSelect(&w
, pSelect
);
4535 #ifndef SQLITE_OMIT_SUBQUERY
4537 ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()
4540 ** For each FROM-clause subquery, add Column.zType and Column.zColl
4541 ** information to the Table structure that represents the result set
4542 ** of that subquery.
4544 ** The Table structure that represents the result set was constructed
4545 ** by selectExpander() but the type and collation information was omitted
4546 ** at that point because identifiers had not yet been resolved. This
4547 ** routine is called after identifier resolution.
4549 static void selectAddSubqueryTypeInfo(Walker
*pWalker
, Select
*p
){
4553 struct SrcList_item
*pFrom
;
4555 assert( p
->selFlags
& SF_Resolved
);
4556 assert( (p
->selFlags
& SF_HasTypeInfo
)==0 );
4557 p
->selFlags
|= SF_HasTypeInfo
;
4558 pParse
= pWalker
->pParse
;
4560 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
4561 Table
*pTab
= pFrom
->pTab
;
4563 if( (pTab
->tabFlags
& TF_Ephemeral
)!=0 ){
4564 /* A sub-query in the FROM clause of a SELECT */
4565 Select
*pSel
= pFrom
->pSelect
;
4567 while( pSel
->pPrior
) pSel
= pSel
->pPrior
;
4568 sqlite3SelectAddColumnTypeAndCollation(pParse
, pTab
, pSel
);
4577 ** This routine adds datatype and collating sequence information to
4578 ** the Table structures of all FROM-clause subqueries in a
4579 ** SELECT statement.
4581 ** Use this routine after name resolution.
4583 static void sqlite3SelectAddTypeInfo(Parse
*pParse
, Select
*pSelect
){
4584 #ifndef SQLITE_OMIT_SUBQUERY
4586 memset(&w
, 0, sizeof(w
));
4587 w
.xSelectCallback2
= selectAddSubqueryTypeInfo
;
4588 w
.xExprCallback
= sqlite3ExprWalkNoop
;
4590 sqlite3WalkSelect(&w
, pSelect
);
4596 ** This routine sets up a SELECT statement for processing. The
4597 ** following is accomplished:
4599 ** * VDBE Cursor numbers are assigned to all FROM-clause terms.
4600 ** * Ephemeral Table objects are created for all FROM-clause subqueries.
4601 ** * ON and USING clauses are shifted into WHERE statements
4602 ** * Wildcards "*" and "TABLE.*" in result sets are expanded.
4603 ** * Identifiers in expression are matched to tables.
4605 ** This routine acts recursively on all subqueries within the SELECT.
4607 void sqlite3SelectPrep(
4608 Parse
*pParse
, /* The parser context */
4609 Select
*p
, /* The SELECT statement being coded. */
4610 NameContext
*pOuterNC
/* Name context for container */
4613 if( NEVER(p
==0) ) return;
4615 if( db
->mallocFailed
) return;
4616 if( p
->selFlags
& SF_HasTypeInfo
) return;
4617 sqlite3SelectExpand(pParse
, p
);
4618 if( pParse
->nErr
|| db
->mallocFailed
) return;
4619 sqlite3ResolveSelectNames(pParse
, p
, pOuterNC
);
4620 if( pParse
->nErr
|| db
->mallocFailed
) return;
4621 sqlite3SelectAddTypeInfo(pParse
, p
);
4625 ** Reset the aggregate accumulator.
4627 ** The aggregate accumulator is a set of memory cells that hold
4628 ** intermediate results while calculating an aggregate. This
4629 ** routine generates code that stores NULLs in all of those memory
4632 static void resetAccumulator(Parse
*pParse
, AggInfo
*pAggInfo
){
4633 Vdbe
*v
= pParse
->pVdbe
;
4635 struct AggInfo_func
*pFunc
;
4636 int nReg
= pAggInfo
->nFunc
+ pAggInfo
->nColumn
;
4637 if( nReg
==0 ) return;
4639 /* Verify that all AggInfo registers are within the range specified by
4640 ** AggInfo.mnReg..AggInfo.mxReg */
4641 assert( nReg
==pAggInfo
->mxReg
-pAggInfo
->mnReg
+1 );
4642 for(i
=0; i
<pAggInfo
->nColumn
; i
++){
4643 assert( pAggInfo
->aCol
[i
].iMem
>=pAggInfo
->mnReg
4644 && pAggInfo
->aCol
[i
].iMem
<=pAggInfo
->mxReg
);
4646 for(i
=0; i
<pAggInfo
->nFunc
; i
++){
4647 assert( pAggInfo
->aFunc
[i
].iMem
>=pAggInfo
->mnReg
4648 && pAggInfo
->aFunc
[i
].iMem
<=pAggInfo
->mxReg
);
4651 sqlite3VdbeAddOp3(v
, OP_Null
, 0, pAggInfo
->mnReg
, pAggInfo
->mxReg
);
4652 for(pFunc
=pAggInfo
->aFunc
, i
=0; i
<pAggInfo
->nFunc
; i
++, pFunc
++){
4653 if( pFunc
->iDistinct
>=0 ){
4654 Expr
*pE
= pFunc
->pExpr
;
4655 assert( !ExprHasProperty(pE
, EP_xIsSelect
) );
4656 if( pE
->x
.pList
==0 || pE
->x
.pList
->nExpr
!=1 ){
4657 sqlite3ErrorMsg(pParse
, "DISTINCT aggregates must have exactly one "
4659 pFunc
->iDistinct
= -1;
4661 KeyInfo
*pKeyInfo
= keyInfoFromExprList(pParse
, pE
->x
.pList
, 0, 0);
4662 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
, pFunc
->iDistinct
, 0, 0,
4663 (char*)pKeyInfo
, P4_KEYINFO
);
4670 ** Invoke the OP_AggFinalize opcode for every aggregate function
4671 ** in the AggInfo structure.
4673 static void finalizeAggFunctions(Parse
*pParse
, AggInfo
*pAggInfo
){
4674 Vdbe
*v
= pParse
->pVdbe
;
4676 struct AggInfo_func
*pF
;
4677 for(i
=0, pF
=pAggInfo
->aFunc
; i
<pAggInfo
->nFunc
; i
++, pF
++){
4678 ExprList
*pList
= pF
->pExpr
->x
.pList
;
4679 assert( !ExprHasProperty(pF
->pExpr
, EP_xIsSelect
) );
4680 sqlite3VdbeAddOp4(v
, OP_AggFinal
, pF
->iMem
, pList
? pList
->nExpr
: 0, 0,
4681 (void*)pF
->pFunc
, P4_FUNCDEF
);
4686 ** Update the accumulator memory cells for an aggregate based on
4687 ** the current cursor position.
4689 static void updateAccumulator(Parse
*pParse
, AggInfo
*pAggInfo
){
4690 Vdbe
*v
= pParse
->pVdbe
;
4693 int addrHitTest
= 0;
4694 struct AggInfo_func
*pF
;
4695 struct AggInfo_col
*pC
;
4697 pAggInfo
->directMode
= 1;
4698 for(i
=0, pF
=pAggInfo
->aFunc
; i
<pAggInfo
->nFunc
; i
++, pF
++){
4702 ExprList
*pList
= pF
->pExpr
->x
.pList
;
4703 assert( !ExprHasProperty(pF
->pExpr
, EP_xIsSelect
) );
4705 nArg
= pList
->nExpr
;
4706 regAgg
= sqlite3GetTempRange(pParse
, nArg
);
4707 sqlite3ExprCodeExprList(pParse
, pList
, regAgg
, 0, SQLITE_ECEL_DUP
);
4712 if( pF
->iDistinct
>=0 ){
4713 addrNext
= sqlite3VdbeMakeLabel(v
);
4714 testcase( nArg
==0 ); /* Error condition */
4715 testcase( nArg
>1 ); /* Also an error */
4716 codeDistinct(pParse
, pF
->iDistinct
, addrNext
, 1, regAgg
);
4718 if( pF
->pFunc
->funcFlags
& SQLITE_FUNC_NEEDCOLL
){
4720 struct ExprList_item
*pItem
;
4722 assert( pList
!=0 ); /* pList!=0 if pF->pFunc has NEEDCOLL */
4723 for(j
=0, pItem
=pList
->a
; !pColl
&& j
<nArg
; j
++, pItem
++){
4724 pColl
= sqlite3ExprCollSeq(pParse
, pItem
->pExpr
);
4727 pColl
= pParse
->db
->pDfltColl
;
4729 if( regHit
==0 && pAggInfo
->nAccumulator
) regHit
= ++pParse
->nMem
;
4730 sqlite3VdbeAddOp4(v
, OP_CollSeq
, regHit
, 0, 0, (char *)pColl
, P4_COLLSEQ
);
4732 sqlite3VdbeAddOp4(v
, OP_AggStep0
, 0, regAgg
, pF
->iMem
,
4733 (void*)pF
->pFunc
, P4_FUNCDEF
);
4734 sqlite3VdbeChangeP5(v
, (u8
)nArg
);
4735 sqlite3ExprCacheAffinityChange(pParse
, regAgg
, nArg
);
4736 sqlite3ReleaseTempRange(pParse
, regAgg
, nArg
);
4738 sqlite3VdbeResolveLabel(v
, addrNext
);
4739 sqlite3ExprCacheClear(pParse
);
4743 /* Before populating the accumulator registers, clear the column cache.
4744 ** Otherwise, if any of the required column values are already present
4745 ** in registers, sqlite3ExprCode() may use OP_SCopy to copy the value
4746 ** to pC->iMem. But by the time the value is used, the original register
4747 ** may have been used, invalidating the underlying buffer holding the
4748 ** text or blob value. See ticket [883034dcb5].
4750 ** Another solution would be to change the OP_SCopy used to copy cached
4751 ** values to an OP_Copy.
4754 addrHitTest
= sqlite3VdbeAddOp1(v
, OP_If
, regHit
); VdbeCoverage(v
);
4756 sqlite3ExprCacheClear(pParse
);
4757 for(i
=0, pC
=pAggInfo
->aCol
; i
<pAggInfo
->nAccumulator
; i
++, pC
++){
4758 sqlite3ExprCode(pParse
, pC
->pExpr
, pC
->iMem
);
4760 pAggInfo
->directMode
= 0;
4761 sqlite3ExprCacheClear(pParse
);
4763 sqlite3VdbeJumpHere(v
, addrHitTest
);
4768 ** Add a single OP_Explain instruction to the VDBE to explain a simple
4769 ** count(*) query ("SELECT count(*) FROM pTab").
4771 #ifndef SQLITE_OMIT_EXPLAIN
4772 static void explainSimpleCount(
4773 Parse
*pParse
, /* Parse context */
4774 Table
*pTab
, /* Table being queried */
4775 Index
*pIdx
/* Index used to optimize scan, or NULL */
4777 if( pParse
->explain
==2 ){
4778 int bCover
= (pIdx
!=0 && (HasRowid(pTab
) || !IsPrimaryKeyIndex(pIdx
)));
4779 char *zEqp
= sqlite3MPrintf(pParse
->db
, "SCAN TABLE %s%s%s",
4781 bCover
? " USING COVERING INDEX " : "",
4782 bCover
? pIdx
->zName
: ""
4785 pParse
->pVdbe
, OP_Explain
, pParse
->iSelectId
, 0, 0, zEqp
, P4_DYNAMIC
4790 # define explainSimpleCount(a,b,c)
4794 ** Generate code for the SELECT statement given in the p argument.
4796 ** The results are returned according to the SelectDest structure.
4797 ** See comments in sqliteInt.h for further information.
4799 ** This routine returns the number of errors. If any errors are
4800 ** encountered, then an appropriate error message is left in
4803 ** This routine does NOT free the Select structure passed in. The
4804 ** calling function needs to do that.
4807 Parse
*pParse
, /* The parser context */
4808 Select
*p
, /* The SELECT statement being coded. */
4809 SelectDest
*pDest
/* What to do with the query results */
4811 int i
, j
; /* Loop counters */
4812 WhereInfo
*pWInfo
; /* Return from sqlite3WhereBegin() */
4813 Vdbe
*v
; /* The virtual machine under construction */
4814 int isAgg
; /* True for select lists like "count(*)" */
4815 ExprList
*pEList
= 0; /* List of columns to extract. */
4816 SrcList
*pTabList
; /* List of tables to select from */
4817 Expr
*pWhere
; /* The WHERE clause. May be NULL */
4818 ExprList
*pGroupBy
; /* The GROUP BY clause. May be NULL */
4819 Expr
*pHaving
; /* The HAVING clause. May be NULL */
4820 int rc
= 1; /* Value to return from this function */
4821 DistinctCtx sDistinct
; /* Info on how to code the DISTINCT keyword */
4822 SortCtx sSort
; /* Info on how to code the ORDER BY clause */
4823 AggInfo sAggInfo
; /* Information used by aggregate queries */
4824 int iEnd
; /* Address of the end of the query */
4825 sqlite3
*db
; /* The database connection */
4827 #ifndef SQLITE_OMIT_EXPLAIN
4828 int iRestoreSelectId
= pParse
->iSelectId
;
4829 pParse
->iSelectId
= pParse
->iNextSelectId
++;
4833 if( p
==0 || db
->mallocFailed
|| pParse
->nErr
){
4836 if( sqlite3AuthCheck(pParse
, SQLITE_SELECT
, 0, 0, 0) ) return 1;
4837 memset(&sAggInfo
, 0, sizeof(sAggInfo
));
4838 #if SELECTTRACE_ENABLED
4839 pParse
->nSelectIndent
++;
4840 SELECTTRACE(1,pParse
,p
, ("begin processing:\n"));
4841 if( sqlite3SelectTrace
& 0x100 ){
4842 sqlite3TreeViewSelect(0, p
, 0);
4846 assert( p
->pOrderBy
==0 || pDest
->eDest
!=SRT_DistFifo
);
4847 assert( p
->pOrderBy
==0 || pDest
->eDest
!=SRT_Fifo
);
4848 assert( p
->pOrderBy
==0 || pDest
->eDest
!=SRT_DistQueue
);
4849 assert( p
->pOrderBy
==0 || pDest
->eDest
!=SRT_Queue
);
4850 if( IgnorableOrderby(pDest
) ){
4851 assert(pDest
->eDest
==SRT_Exists
|| pDest
->eDest
==SRT_Union
||
4852 pDest
->eDest
==SRT_Except
|| pDest
->eDest
==SRT_Discard
||
4853 pDest
->eDest
==SRT_Queue
|| pDest
->eDest
==SRT_DistFifo
||
4854 pDest
->eDest
==SRT_DistQueue
|| pDest
->eDest
==SRT_Fifo
);
4855 /* If ORDER BY makes no difference in the output then neither does
4856 ** DISTINCT so it can be removed too. */
4857 sqlite3ExprListDelete(db
, p
->pOrderBy
);
4859 p
->selFlags
&= ~SF_Distinct
;
4861 sqlite3SelectPrep(pParse
, p
, 0);
4862 memset(&sSort
, 0, sizeof(sSort
));
4863 sSort
.pOrderBy
= p
->pOrderBy
;
4865 if( pParse
->nErr
|| db
->mallocFailed
){
4868 assert( p
->pEList
!=0 );
4869 isAgg
= (p
->selFlags
& SF_Aggregate
)!=0;
4870 #if SELECTTRACE_ENABLED
4871 if( sqlite3SelectTrace
& 0x100 ){
4872 SELECTTRACE(0x100,pParse
,p
, ("after name resolution:\n"));
4873 sqlite3TreeViewSelect(0, p
, 0);
4877 /* Try to flatten subqueries in the FROM clause up into the main query
4879 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
4880 for(i
=0; !p
->pPrior
&& i
<pTabList
->nSrc
; i
++){
4881 struct SrcList_item
*pItem
= &pTabList
->a
[i
];
4882 Select
*pSub
= pItem
->pSelect
;
4884 Table
*pTab
= pItem
->pTab
;
4885 if( pSub
==0 ) continue;
4887 /* Catch mismatch in the declared columns of a view and the number of
4888 ** columns in the SELECT on the RHS */
4889 if( pTab
->nCol
!=pSub
->pEList
->nExpr
){
4890 sqlite3ErrorMsg(pParse
, "expected %d columns for '%s' but got %d",
4891 pTab
->nCol
, pTab
->zName
, pSub
->pEList
->nExpr
);
4895 isAggSub
= (pSub
->selFlags
& SF_Aggregate
)!=0;
4896 if( flattenSubquery(pParse
, p
, i
, isAgg
, isAggSub
) ){
4897 /* This subquery can be absorbed into its parent. */
4900 p
->selFlags
|= SF_Aggregate
;
4905 if( db
->mallocFailed
) goto select_end
;
4906 if( !IgnorableOrderby(pDest
) ){
4907 sSort
.pOrderBy
= p
->pOrderBy
;
4912 /* Get a pointer the VDBE under construction, allocating a new VDBE if one
4913 ** does not already exist */
4914 v
= sqlite3GetVdbe(pParse
);
4915 if( v
==0 ) goto select_end
;
4917 #ifndef SQLITE_OMIT_COMPOUND_SELECT
4918 /* Handle compound SELECT statements using the separate multiSelect()
4922 rc
= multiSelect(pParse
, p
, pDest
);
4923 explainSetInteger(pParse
->iSelectId
, iRestoreSelectId
);
4924 #if SELECTTRACE_ENABLED
4925 SELECTTRACE(1,pParse
,p
,("end compound-select processing\n"));
4926 pParse
->nSelectIndent
--;
4932 /* Generate code for all sub-queries in the FROM clause
4934 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
4935 for(i
=0; i
<pTabList
->nSrc
; i
++){
4936 struct SrcList_item
*pItem
= &pTabList
->a
[i
];
4938 Select
*pSub
= pItem
->pSelect
;
4939 if( pSub
==0 ) continue;
4941 /* Sometimes the code for a subquery will be generated more than
4942 ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
4943 ** for example. In that case, do not regenerate the code to manifest
4944 ** a view or the co-routine to implement a view. The first instance
4945 ** is sufficient, though the subroutine to manifest the view does need
4946 ** to be invoked again. */
4947 if( pItem
->addrFillSub
){
4948 if( pItem
->fg
.viaCoroutine
==0 ){
4949 sqlite3VdbeAddOp2(v
, OP_Gosub
, pItem
->regReturn
, pItem
->addrFillSub
);
4954 /* Increment Parse.nHeight by the height of the largest expression
4955 ** tree referred to by this, the parent select. The child select
4956 ** may contain expression trees of at most
4957 ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
4958 ** more conservative than necessary, but much easier than enforcing
4961 pParse
->nHeight
+= sqlite3SelectExprHeight(p
);
4963 /* Make copies of constant WHERE-clause terms in the outer query down
4964 ** inside the subquery. This can help the subquery to run more efficiently.
4966 if( (pItem
->fg
.jointype
& JT_OUTER
)==0
4967 && pushDownWhereTerms(db
, pSub
, p
->pWhere
, pItem
->iCursor
)
4969 #if SELECTTRACE_ENABLED
4970 if( sqlite3SelectTrace
& 0x100 ){
4971 SELECTTRACE(0x100,pParse
,p
,("After WHERE-clause push-down:\n"));
4972 sqlite3TreeViewSelect(0, p
, 0);
4977 /* Generate code to implement the subquery
4979 ** The subquery is implemented as a co-routine if all of these are true:
4980 ** (1) The subquery is guaranteed to be the outer loop (so that it
4981 ** does not need to be computed more than once)
4982 ** (2) The ALL keyword after SELECT is omitted. (Applications are
4983 ** allowed to say "SELECT ALL" instead of just "SELECT" to disable
4984 ** the use of co-routines.)
4985 ** (3) Co-routines are not disabled using sqlite3_test_control()
4986 ** with SQLITE_TESTCTRL_OPTIMIZATIONS.
4988 ** TODO: Are there other reasons beside (1) to use a co-routine
4992 && (pTabList
->nSrc
==1
4993 || (pTabList
->a
[1].fg
.jointype
&(JT_LEFT
|JT_CROSS
))!=0) /* (1) */
4994 && (p
->selFlags
& SF_All
)==0 /* (2) */
4995 && OptimizationEnabled(db
, SQLITE_SubqCoroutine
) /* (3) */
4997 /* Implement a co-routine that will return a single row of the result
4998 ** set on each invocation.
5000 int addrTop
= sqlite3VdbeCurrentAddr(v
)+1;
5001 pItem
->regReturn
= ++pParse
->nMem
;
5002 sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, pItem
->regReturn
, 0, addrTop
);
5003 VdbeComment((v
, "%s", pItem
->pTab
->zName
));
5004 pItem
->addrFillSub
= addrTop
;
5005 sqlite3SelectDestInit(&dest
, SRT_Coroutine
, pItem
->regReturn
);
5006 explainSetInteger(pItem
->iSelectId
, (u8
)pParse
->iNextSelectId
);
5007 sqlite3Select(pParse
, pSub
, &dest
);
5008 pItem
->pTab
->nRowLogEst
= pSub
->nSelectRow
;
5009 pItem
->fg
.viaCoroutine
= 1;
5010 pItem
->regResult
= dest
.iSdst
;
5011 sqlite3VdbeEndCoroutine(v
, pItem
->regReturn
);
5012 sqlite3VdbeJumpHere(v
, addrTop
-1);
5013 sqlite3ClearTempRegCache(pParse
);
5015 /* Generate a subroutine that will fill an ephemeral table with
5016 ** the content of this subquery. pItem->addrFillSub will point
5017 ** to the address of the generated subroutine. pItem->regReturn
5018 ** is a register allocated to hold the subroutine return address
5023 assert( pItem
->addrFillSub
==0 );
5024 pItem
->regReturn
= ++pParse
->nMem
;
5025 topAddr
= sqlite3VdbeAddOp2(v
, OP_Integer
, 0, pItem
->regReturn
);
5026 pItem
->addrFillSub
= topAddr
+1;
5027 if( pItem
->fg
.isCorrelated
==0 ){
5028 /* If the subquery is not correlated and if we are not inside of
5029 ** a trigger, then we only need to compute the value of the subquery
5031 onceAddr
= sqlite3VdbeAddOp0(v
, OP_Once
); VdbeCoverage(v
);
5032 VdbeComment((v
, "materialize \"%s\"", pItem
->pTab
->zName
));
5034 VdbeNoopComment((v
, "materialize \"%s\"", pItem
->pTab
->zName
));
5036 sqlite3SelectDestInit(&dest
, SRT_EphemTab
, pItem
->iCursor
);
5037 explainSetInteger(pItem
->iSelectId
, (u8
)pParse
->iNextSelectId
);
5038 sqlite3Select(pParse
, pSub
, &dest
);
5039 pItem
->pTab
->nRowLogEst
= pSub
->nSelectRow
;
5040 if( onceAddr
) sqlite3VdbeJumpHere(v
, onceAddr
);
5041 retAddr
= sqlite3VdbeAddOp1(v
, OP_Return
, pItem
->regReturn
);
5042 VdbeComment((v
, "end %s", pItem
->pTab
->zName
));
5043 sqlite3VdbeChangeP1(v
, topAddr
, retAddr
);
5044 sqlite3ClearTempRegCache(pParse
);
5046 if( db
->mallocFailed
) goto select_end
;
5047 pParse
->nHeight
-= sqlite3SelectExprHeight(p
);
5051 /* Various elements of the SELECT copied into local variables for
5055 pGroupBy
= p
->pGroupBy
;
5056 pHaving
= p
->pHaving
;
5057 sDistinct
.isTnct
= (p
->selFlags
& SF_Distinct
)!=0;
5059 #if SELECTTRACE_ENABLED
5060 if( sqlite3SelectTrace
& 0x400 ){
5061 SELECTTRACE(0x400,pParse
,p
,("After all FROM-clause analysis:\n"));
5062 sqlite3TreeViewSelect(0, p
, 0);
5066 /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and
5067 ** if the select-list is the same as the ORDER BY list, then this query
5068 ** can be rewritten as a GROUP BY. In other words, this:
5070 ** SELECT DISTINCT xyz FROM ... ORDER BY xyz
5072 ** is transformed to:
5074 ** SELECT xyz FROM ... GROUP BY xyz ORDER BY xyz
5076 ** The second form is preferred as a single index (or temp-table) may be
5077 ** used for both the ORDER BY and DISTINCT processing. As originally
5078 ** written the query must use a temp-table for at least one of the ORDER
5079 ** BY and DISTINCT, and an index or separate temp-table for the other.
5081 if( (p
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Distinct
5082 && sqlite3ExprListCompare(sSort
.pOrderBy
, pEList
, -1)==0
5084 p
->selFlags
&= ~SF_Distinct
;
5085 pGroupBy
= p
->pGroupBy
= sqlite3ExprListDup(db
, pEList
, 0);
5086 /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
5087 ** the sDistinct.isTnct is still set. Hence, isTnct represents the
5088 ** original setting of the SF_Distinct flag, not the current setting */
5089 assert( sDistinct
.isTnct
);
5091 #if SELECTTRACE_ENABLED
5092 if( sqlite3SelectTrace
& 0x400 ){
5093 SELECTTRACE(0x400,pParse
,p
,("Transform DISTINCT into GROUP BY:\n"));
5094 sqlite3TreeViewSelect(0, p
, 0);
5099 /* If there is an ORDER BY clause, then create an ephemeral index to
5100 ** do the sorting. But this sorting ephemeral index might end up
5101 ** being unused if the data can be extracted in pre-sorted order.
5102 ** If that is the case, then the OP_OpenEphemeral instruction will be
5103 ** changed to an OP_Noop once we figure out that the sorting index is
5104 ** not needed. The sSort.addrSortIndex variable is used to facilitate
5107 if( sSort
.pOrderBy
){
5109 pKeyInfo
= keyInfoFromExprList(pParse
, sSort
.pOrderBy
, 0, pEList
->nExpr
);
5110 sSort
.iECursor
= pParse
->nTab
++;
5111 sSort
.addrSortIndex
=
5112 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
,
5113 sSort
.iECursor
, sSort
.pOrderBy
->nExpr
+1+pEList
->nExpr
, 0,
5114 (char*)pKeyInfo
, P4_KEYINFO
5117 sSort
.addrSortIndex
= -1;
5120 /* If the output is destined for a temporary table, open that table.
5122 if( pDest
->eDest
==SRT_EphemTab
){
5123 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, pDest
->iSDParm
, pEList
->nExpr
);
5128 iEnd
= sqlite3VdbeMakeLabel(v
);
5129 p
->nSelectRow
= 320; /* 4 billion rows */
5130 computeLimitRegisters(pParse
, p
, iEnd
);
5131 if( p
->iLimit
==0 && sSort
.addrSortIndex
>=0 ){
5132 sqlite3VdbeChangeOpcode(v
, sSort
.addrSortIndex
, OP_SorterOpen
);
5133 sSort
.sortFlags
|= SORTFLAG_UseSorter
;
5136 /* Open an ephemeral index to use for the distinct set.
5138 if( p
->selFlags
& SF_Distinct
){
5139 sDistinct
.tabTnct
= pParse
->nTab
++;
5140 sDistinct
.addrTnct
= sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
,
5141 sDistinct
.tabTnct
, 0, 0,
5142 (char*)keyInfoFromExprList(pParse
, p
->pEList
,0,0),
5144 sqlite3VdbeChangeP5(v
, BTREE_UNORDERED
);
5145 sDistinct
.eTnctType
= WHERE_DISTINCT_UNORDERED
;
5147 sDistinct
.eTnctType
= WHERE_DISTINCT_NOOP
;
5150 if( !isAgg
&& pGroupBy
==0 ){
5151 /* No aggregate functions and no GROUP BY clause */
5152 u16 wctrlFlags
= (sDistinct
.isTnct
? WHERE_WANT_DISTINCT
: 0);
5153 assert( WHERE_USE_LIMIT
==SF_FixedLimit
);
5154 wctrlFlags
|= p
->selFlags
& SF_FixedLimit
;
5156 /* Begin the database scan. */
5157 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, sSort
.pOrderBy
,
5158 p
->pEList
, wctrlFlags
, p
->nSelectRow
);
5159 if( pWInfo
==0 ) goto select_end
;
5160 if( sqlite3WhereOutputRowCount(pWInfo
) < p
->nSelectRow
){
5161 p
->nSelectRow
= sqlite3WhereOutputRowCount(pWInfo
);
5163 if( sDistinct
.isTnct
&& sqlite3WhereIsDistinct(pWInfo
) ){
5164 sDistinct
.eTnctType
= sqlite3WhereIsDistinct(pWInfo
);
5166 if( sSort
.pOrderBy
){
5167 sSort
.nOBSat
= sqlite3WhereIsOrdered(pWInfo
);
5168 sSort
.bOrderedInnerLoop
= sqlite3WhereOrderedInnerLoop(pWInfo
);
5169 if( sSort
.nOBSat
==sSort
.pOrderBy
->nExpr
){
5174 /* If sorting index that was created by a prior OP_OpenEphemeral
5175 ** instruction ended up not being needed, then change the OP_OpenEphemeral
5178 if( sSort
.addrSortIndex
>=0 && sSort
.pOrderBy
==0 ){
5179 sqlite3VdbeChangeToNoop(v
, sSort
.addrSortIndex
);
5182 /* Use the standard inner loop. */
5183 selectInnerLoop(pParse
, p
, pEList
, -1, &sSort
, &sDistinct
, pDest
,
5184 sqlite3WhereContinueLabel(pWInfo
),
5185 sqlite3WhereBreakLabel(pWInfo
));
5187 /* End the database scan loop.
5189 sqlite3WhereEnd(pWInfo
);
5191 /* This case when there exist aggregate functions or a GROUP BY clause
5193 NameContext sNC
; /* Name context for processing aggregate information */
5194 int iAMem
; /* First Mem address for storing current GROUP BY */
5195 int iBMem
; /* First Mem address for previous GROUP BY */
5196 int iUseFlag
; /* Mem address holding flag indicating that at least
5197 ** one row of the input to the aggregator has been
5199 int iAbortFlag
; /* Mem address which causes query abort if positive */
5200 int groupBySort
; /* Rows come from source in GROUP BY order */
5201 int addrEnd
; /* End of processing for this SELECT */
5202 int sortPTab
= 0; /* Pseudotable used to decode sorting results */
5203 int sortOut
= 0; /* Output register from the sorter */
5204 int orderByGrp
= 0; /* True if the GROUP BY and ORDER BY are the same */
5206 /* Remove any and all aliases between the result set and the
5210 int k
; /* Loop counter */
5211 struct ExprList_item
*pItem
; /* For looping over expression in a list */
5213 for(k
=p
->pEList
->nExpr
, pItem
=p
->pEList
->a
; k
>0; k
--, pItem
++){
5214 pItem
->u
.x
.iAlias
= 0;
5216 for(k
=pGroupBy
->nExpr
, pItem
=pGroupBy
->a
; k
>0; k
--, pItem
++){
5217 pItem
->u
.x
.iAlias
= 0;
5219 assert( 66==sqlite3LogEst(100) );
5220 if( p
->nSelectRow
>66 ) p
->nSelectRow
= 66;
5222 assert( 0==sqlite3LogEst(1) );
5226 /* If there is both a GROUP BY and an ORDER BY clause and they are
5227 ** identical, then it may be possible to disable the ORDER BY clause
5228 ** on the grounds that the GROUP BY will cause elements to come out
5229 ** in the correct order. It also may not - the GROUP BY might use a
5230 ** database index that causes rows to be grouped together as required
5231 ** but not actually sorted. Either way, record the fact that the
5232 ** ORDER BY and GROUP BY clauses are the same by setting the orderByGrp
5234 if( sqlite3ExprListCompare(pGroupBy
, sSort
.pOrderBy
, -1)==0 ){
5238 /* Create a label to jump to when we want to abort the query */
5239 addrEnd
= sqlite3VdbeMakeLabel(v
);
5241 /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
5242 ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
5243 ** SELECT statement.
5245 memset(&sNC
, 0, sizeof(sNC
));
5246 sNC
.pParse
= pParse
;
5247 sNC
.pSrcList
= pTabList
;
5248 sNC
.pAggInfo
= &sAggInfo
;
5249 sAggInfo
.mnReg
= pParse
->nMem
+1;
5250 sAggInfo
.nSortingColumn
= pGroupBy
? pGroupBy
->nExpr
: 0;
5251 sAggInfo
.pGroupBy
= pGroupBy
;
5252 sqlite3ExprAnalyzeAggList(&sNC
, pEList
);
5253 sqlite3ExprAnalyzeAggList(&sNC
, sSort
.pOrderBy
);
5255 sqlite3ExprAnalyzeAggregates(&sNC
, pHaving
);
5257 sAggInfo
.nAccumulator
= sAggInfo
.nColumn
;
5258 for(i
=0; i
<sAggInfo
.nFunc
; i
++){
5259 assert( !ExprHasProperty(sAggInfo
.aFunc
[i
].pExpr
, EP_xIsSelect
) );
5260 sNC
.ncFlags
|= NC_InAggFunc
;
5261 sqlite3ExprAnalyzeAggList(&sNC
, sAggInfo
.aFunc
[i
].pExpr
->x
.pList
);
5262 sNC
.ncFlags
&= ~NC_InAggFunc
;
5264 sAggInfo
.mxReg
= pParse
->nMem
;
5265 if( db
->mallocFailed
) goto select_end
;
5267 /* Processing for aggregates with GROUP BY is very different and
5268 ** much more complex than aggregates without a GROUP BY.
5271 KeyInfo
*pKeyInfo
; /* Keying information for the group by clause */
5272 int addr1
; /* A-vs-B comparision jump */
5273 int addrOutputRow
; /* Start of subroutine that outputs a result row */
5274 int regOutputRow
; /* Return address register for output subroutine */
5275 int addrSetAbort
; /* Set the abort flag and return */
5276 int addrTopOfLoop
; /* Top of the input loop */
5277 int addrSortingIdx
; /* The OP_OpenEphemeral for the sorting index */
5278 int addrReset
; /* Subroutine for resetting the accumulator */
5279 int regReset
; /* Return address register for reset subroutine */
5281 /* If there is a GROUP BY clause we might need a sorting index to
5282 ** implement it. Allocate that sorting index now. If it turns out
5283 ** that we do not need it after all, the OP_SorterOpen instruction
5284 ** will be converted into a Noop.
5286 sAggInfo
.sortingIdx
= pParse
->nTab
++;
5287 pKeyInfo
= keyInfoFromExprList(pParse
, pGroupBy
, 0, sAggInfo
.nColumn
);
5288 addrSortingIdx
= sqlite3VdbeAddOp4(v
, OP_SorterOpen
,
5289 sAggInfo
.sortingIdx
, sAggInfo
.nSortingColumn
,
5290 0, (char*)pKeyInfo
, P4_KEYINFO
);
5292 /* Initialize memory locations used by GROUP BY aggregate processing
5294 iUseFlag
= ++pParse
->nMem
;
5295 iAbortFlag
= ++pParse
->nMem
;
5296 regOutputRow
= ++pParse
->nMem
;
5297 addrOutputRow
= sqlite3VdbeMakeLabel(v
);
5298 regReset
= ++pParse
->nMem
;
5299 addrReset
= sqlite3VdbeMakeLabel(v
);
5300 iAMem
= pParse
->nMem
+ 1;
5301 pParse
->nMem
+= pGroupBy
->nExpr
;
5302 iBMem
= pParse
->nMem
+ 1;
5303 pParse
->nMem
+= pGroupBy
->nExpr
;
5304 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, iAbortFlag
);
5305 VdbeComment((v
, "clear abort flag"));
5306 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, iUseFlag
);
5307 VdbeComment((v
, "indicate accumulator empty"));
5308 sqlite3VdbeAddOp3(v
, OP_Null
, 0, iAMem
, iAMem
+pGroupBy
->nExpr
-1);
5310 /* Begin a loop that will extract all source rows in GROUP BY order.
5311 ** This might involve two separate loops with an OP_Sort in between, or
5312 ** it might be a single loop that uses an index to extract information
5313 ** in the right order to begin with.
5315 sqlite3VdbeAddOp2(v
, OP_Gosub
, regReset
, addrReset
);
5316 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, pGroupBy
, 0,
5317 WHERE_GROUPBY
| (orderByGrp
? WHERE_SORTBYGROUP
: 0), 0
5319 if( pWInfo
==0 ) goto select_end
;
5320 if( sqlite3WhereIsOrdered(pWInfo
)==pGroupBy
->nExpr
){
5321 /* The optimizer is able to deliver rows in group by order so
5322 ** we do not have to sort. The OP_OpenEphemeral table will be
5323 ** cancelled later because we still need to use the pKeyInfo
5327 /* Rows are coming out in undetermined order. We have to push
5328 ** each row into a sorting index, terminate the first loop,
5329 ** then loop over the sorting index in order to get the output
5337 explainTempTable(pParse
,
5338 (sDistinct
.isTnct
&& (p
->selFlags
&SF_Distinct
)==0) ?
5339 "DISTINCT" : "GROUP BY");
5342 nGroupBy
= pGroupBy
->nExpr
;
5345 for(i
=0; i
<sAggInfo
.nColumn
; i
++){
5346 if( sAggInfo
.aCol
[i
].iSorterColumn
>=j
){
5351 regBase
= sqlite3GetTempRange(pParse
, nCol
);
5352 sqlite3ExprCacheClear(pParse
);
5353 sqlite3ExprCodeExprList(pParse
, pGroupBy
, regBase
, 0, 0);
5355 for(i
=0; i
<sAggInfo
.nColumn
; i
++){
5356 struct AggInfo_col
*pCol
= &sAggInfo
.aCol
[i
];
5357 if( pCol
->iSorterColumn
>=j
){
5358 int r1
= j
+ regBase
;
5359 sqlite3ExprCodeGetColumnToReg(pParse
,
5360 pCol
->pTab
, pCol
->iColumn
, pCol
->iTable
, r1
);
5364 regRecord
= sqlite3GetTempReg(pParse
);
5365 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regBase
, nCol
, regRecord
);
5366 sqlite3VdbeAddOp2(v
, OP_SorterInsert
, sAggInfo
.sortingIdx
, regRecord
);
5367 sqlite3ReleaseTempReg(pParse
, regRecord
);
5368 sqlite3ReleaseTempRange(pParse
, regBase
, nCol
);
5369 sqlite3WhereEnd(pWInfo
);
5370 sAggInfo
.sortingIdxPTab
= sortPTab
= pParse
->nTab
++;
5371 sortOut
= sqlite3GetTempReg(pParse
);
5372 sqlite3VdbeAddOp3(v
, OP_OpenPseudo
, sortPTab
, sortOut
, nCol
);
5373 sqlite3VdbeAddOp2(v
, OP_SorterSort
, sAggInfo
.sortingIdx
, addrEnd
);
5374 VdbeComment((v
, "GROUP BY sort")); VdbeCoverage(v
);
5375 sAggInfo
.useSortingIdx
= 1;
5376 sqlite3ExprCacheClear(pParse
);
5380 /* If the index or temporary table used by the GROUP BY sort
5381 ** will naturally deliver rows in the order required by the ORDER BY
5382 ** clause, cancel the ephemeral table open coded earlier.
5384 ** This is an optimization - the correct answer should result regardless.
5385 ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER to
5386 ** disable this optimization for testing purposes. */
5387 if( orderByGrp
&& OptimizationEnabled(db
, SQLITE_GroupByOrder
)
5388 && (groupBySort
|| sqlite3WhereIsSorted(pWInfo
))
5391 sqlite3VdbeChangeToNoop(v
, sSort
.addrSortIndex
);
5394 /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
5395 ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
5396 ** Then compare the current GROUP BY terms against the GROUP BY terms
5397 ** from the previous row currently stored in a0, a1, a2...
5399 addrTopOfLoop
= sqlite3VdbeCurrentAddr(v
);
5400 sqlite3ExprCacheClear(pParse
);
5402 sqlite3VdbeAddOp3(v
, OP_SorterData
, sAggInfo
.sortingIdx
,
5405 for(j
=0; j
<pGroupBy
->nExpr
; j
++){
5407 sqlite3VdbeAddOp3(v
, OP_Column
, sortPTab
, j
, iBMem
+j
);
5409 sAggInfo
.directMode
= 1;
5410 sqlite3ExprCode(pParse
, pGroupBy
->a
[j
].pExpr
, iBMem
+j
);
5413 sqlite3VdbeAddOp4(v
, OP_Compare
, iAMem
, iBMem
, pGroupBy
->nExpr
,
5414 (char*)sqlite3KeyInfoRef(pKeyInfo
), P4_KEYINFO
);
5415 addr1
= sqlite3VdbeCurrentAddr(v
);
5416 sqlite3VdbeAddOp3(v
, OP_Jump
, addr1
+1, 0, addr1
+1); VdbeCoverage(v
);
5418 /* Generate code that runs whenever the GROUP BY changes.
5419 ** Changes in the GROUP BY are detected by the previous code
5420 ** block. If there were no changes, this block is skipped.
5422 ** This code copies current group by terms in b0,b1,b2,...
5423 ** over to a0,a1,a2. It then calls the output subroutine
5424 ** and resets the aggregate accumulator registers in preparation
5425 ** for the next GROUP BY batch.
5427 sqlite3ExprCodeMove(pParse
, iBMem
, iAMem
, pGroupBy
->nExpr
);
5428 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutputRow
, addrOutputRow
);
5429 VdbeComment((v
, "output one row"));
5430 sqlite3VdbeAddOp2(v
, OP_IfPos
, iAbortFlag
, addrEnd
); VdbeCoverage(v
);
5431 VdbeComment((v
, "check abort flag"));
5432 sqlite3VdbeAddOp2(v
, OP_Gosub
, regReset
, addrReset
);
5433 VdbeComment((v
, "reset accumulator"));
5435 /* Update the aggregate accumulators based on the content of
5438 sqlite3VdbeJumpHere(v
, addr1
);
5439 updateAccumulator(pParse
, &sAggInfo
);
5440 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iUseFlag
);
5441 VdbeComment((v
, "indicate data in accumulator"));
5446 sqlite3VdbeAddOp2(v
, OP_SorterNext
, sAggInfo
.sortingIdx
, addrTopOfLoop
);
5449 sqlite3WhereEnd(pWInfo
);
5450 sqlite3VdbeChangeToNoop(v
, addrSortingIdx
);
5453 /* Output the final row of result
5455 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutputRow
, addrOutputRow
);
5456 VdbeComment((v
, "output final row"));
5458 /* Jump over the subroutines
5460 sqlite3VdbeGoto(v
, addrEnd
);
5462 /* Generate a subroutine that outputs a single row of the result
5463 ** set. This subroutine first looks at the iUseFlag. If iUseFlag
5464 ** is less than or equal to zero, the subroutine is a no-op. If
5465 ** the processing calls for the query to abort, this subroutine
5466 ** increments the iAbortFlag memory location before returning in
5467 ** order to signal the caller to abort.
5469 addrSetAbort
= sqlite3VdbeCurrentAddr(v
);
5470 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iAbortFlag
);
5471 VdbeComment((v
, "set abort flag"));
5472 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
5473 sqlite3VdbeResolveLabel(v
, addrOutputRow
);
5474 addrOutputRow
= sqlite3VdbeCurrentAddr(v
);
5475 sqlite3VdbeAddOp2(v
, OP_IfPos
, iUseFlag
, addrOutputRow
+2);
5477 VdbeComment((v
, "Groupby result generator entry point"));
5478 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
5479 finalizeAggFunctions(pParse
, &sAggInfo
);
5480 sqlite3ExprIfFalse(pParse
, pHaving
, addrOutputRow
+1, SQLITE_JUMPIFNULL
);
5481 selectInnerLoop(pParse
, p
, p
->pEList
, -1, &sSort
,
5483 addrOutputRow
+1, addrSetAbort
);
5484 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
5485 VdbeComment((v
, "end groupby result generator"));
5487 /* Generate a subroutine that will reset the group-by accumulator
5489 sqlite3VdbeResolveLabel(v
, addrReset
);
5490 resetAccumulator(pParse
, &sAggInfo
);
5491 sqlite3VdbeAddOp1(v
, OP_Return
, regReset
);
5493 } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */
5496 #ifndef SQLITE_OMIT_BTREECOUNT
5498 if( (pTab
= isSimpleCount(p
, &sAggInfo
))!=0 ){
5499 /* If isSimpleCount() returns a pointer to a Table structure, then
5500 ** the SQL statement is of the form:
5502 ** SELECT count(*) FROM <tbl>
5504 ** where the Table structure returned represents table <tbl>.
5506 ** This statement is so common that it is optimized specially. The
5507 ** OP_Count instruction is executed either on the intkey table that
5508 ** contains the data for table <tbl> or on one of its indexes. It
5509 ** is better to execute the op on an index, as indexes are almost
5510 ** always spread across less pages than their corresponding tables.
5512 const int iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
5513 const int iCsr
= pParse
->nTab
++; /* Cursor to scan b-tree */
5514 Index
*pIdx
; /* Iterator variable */
5515 KeyInfo
*pKeyInfo
= 0; /* Keyinfo for scanned index */
5516 Index
*pBest
= 0; /* Best index found so far */
5517 int iRoot
= pTab
->tnum
; /* Root page of scanned b-tree */
5519 sqlite3CodeVerifySchema(pParse
, iDb
);
5520 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, 0, pTab
->zName
);
5522 /* Search for the index that has the lowest scan cost.
5524 ** (2011-04-15) Do not do a full scan of an unordered index.
5526 ** (2013-10-03) Do not count the entries in a partial index.
5528 ** In practice the KeyInfo structure will not be used. It is only
5529 ** passed to keep OP_OpenRead happy.
5531 if( !HasRowid(pTab
) ) pBest
= sqlite3PrimaryKeyIndex(pTab
);
5532 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
5533 if( pIdx
->bUnordered
==0
5534 && pIdx
->szIdxRow
<pTab
->szTabRow
5535 && pIdx
->pPartIdxWhere
==0
5536 && (!pBest
|| pIdx
->szIdxRow
<pBest
->szIdxRow
)
5542 iRoot
= pBest
->tnum
;
5543 pKeyInfo
= sqlite3KeyInfoOfIndex(pParse
, pBest
);
5546 /* Open a read-only cursor, execute the OP_Count, close the cursor. */
5547 sqlite3VdbeAddOp4Int(v
, OP_OpenRead
, iCsr
, iRoot
, iDb
, 1);
5549 sqlite3VdbeChangeP4(v
, -1, (char *)pKeyInfo
, P4_KEYINFO
);
5551 sqlite3VdbeAddOp2(v
, OP_Count
, iCsr
, sAggInfo
.aFunc
[0].iMem
);
5552 sqlite3VdbeAddOp1(v
, OP_Close
, iCsr
);
5553 explainSimpleCount(pParse
, pTab
, pBest
);
5555 #endif /* SQLITE_OMIT_BTREECOUNT */
5557 /* Check if the query is of one of the following forms:
5559 ** SELECT min(x) FROM ...
5560 ** SELECT max(x) FROM ...
5562 ** If it is, then ask the code in where.c to attempt to sort results
5563 ** as if there was an "ORDER ON x" or "ORDER ON x DESC" clause.
5564 ** If where.c is able to produce results sorted in this order, then
5565 ** add vdbe code to break out of the processing loop after the
5566 ** first iteration (since the first iteration of the loop is
5567 ** guaranteed to operate on the row with the minimum or maximum
5568 ** value of x, the only row required).
5570 ** A special flag must be passed to sqlite3WhereBegin() to slightly
5571 ** modify behavior as follows:
5573 ** + If the query is a "SELECT min(x)", then the loop coded by
5574 ** where.c should not iterate over any values with a NULL value
5577 ** + The optimizer code in where.c (the thing that decides which
5578 ** index or indices to use) should place a different priority on
5579 ** satisfying the 'ORDER BY' clause than it does in other cases.
5580 ** Refer to code and comments in where.c for details.
5582 ExprList
*pMinMax
= 0;
5583 u8 flag
= WHERE_ORDERBY_NORMAL
;
5585 assert( p
->pGroupBy
==0 );
5587 if( p
->pHaving
==0 ){
5588 flag
= minMaxQuery(&sAggInfo
, &pMinMax
);
5590 assert( flag
==0 || (pMinMax
!=0 && pMinMax
->nExpr
==1) );
5593 pMinMax
= sqlite3ExprListDup(db
, pMinMax
, 0);
5595 assert( db
->mallocFailed
|| pMinMax
!=0 );
5596 if( !db
->mallocFailed
){
5597 pMinMax
->a
[0].sortOrder
= flag
!=WHERE_ORDERBY_MIN
?1:0;
5598 pMinMax
->a
[0].pExpr
->op
= TK_COLUMN
;
5602 /* This case runs if the aggregate has no GROUP BY clause. The
5603 ** processing is much simpler since there is only a single row
5606 resetAccumulator(pParse
, &sAggInfo
);
5607 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, pMinMax
,0,flag
,0);
5609 sqlite3ExprListDelete(db
, pDel
);
5612 updateAccumulator(pParse
, &sAggInfo
);
5613 assert( pMinMax
==0 || pMinMax
->nExpr
==1 );
5614 if( sqlite3WhereIsOrdered(pWInfo
)>0 ){
5615 sqlite3VdbeGoto(v
, sqlite3WhereBreakLabel(pWInfo
));
5616 VdbeComment((v
, "%s() by index",
5617 (flag
==WHERE_ORDERBY_MIN
?"min":"max")));
5619 sqlite3WhereEnd(pWInfo
);
5620 finalizeAggFunctions(pParse
, &sAggInfo
);
5624 sqlite3ExprIfFalse(pParse
, pHaving
, addrEnd
, SQLITE_JUMPIFNULL
);
5625 selectInnerLoop(pParse
, p
, p
->pEList
, -1, 0, 0,
5626 pDest
, addrEnd
, addrEnd
);
5627 sqlite3ExprListDelete(db
, pDel
);
5629 sqlite3VdbeResolveLabel(v
, addrEnd
);
5631 } /* endif aggregate query */
5633 if( sDistinct
.eTnctType
==WHERE_DISTINCT_UNORDERED
){
5634 explainTempTable(pParse
, "DISTINCT");
5637 /* If there is an ORDER BY clause, then we need to sort the results
5638 ** and send them to the callback one by one.
5640 if( sSort
.pOrderBy
){
5641 explainTempTable(pParse
,
5642 sSort
.nOBSat
>0 ? "RIGHT PART OF ORDER BY":"ORDER BY");
5643 generateSortTail(pParse
, p
, &sSort
, pEList
->nExpr
, pDest
);
5646 /* Jump here to skip this query
5648 sqlite3VdbeResolveLabel(v
, iEnd
);
5650 /* The SELECT has been coded. If there is an error in the Parse structure,
5651 ** set the return code to 1. Otherwise 0. */
5652 rc
= (pParse
->nErr
>0);
5654 /* Control jumps to here if an error is encountered above, or upon
5655 ** successful coding of the SELECT.
5658 explainSetInteger(pParse
->iSelectId
, iRestoreSelectId
);
5660 /* Identify column names if results of the SELECT are to be output.
5662 if( rc
==SQLITE_OK
&& pDest
->eDest
==SRT_Output
){
5663 generateColumnNames(pParse
, pTabList
, pEList
);
5666 sqlite3DbFree(db
, sAggInfo
.aCol
);
5667 sqlite3DbFree(db
, sAggInfo
.aFunc
);
5668 #if SELECTTRACE_ENABLED
5669 SELECTTRACE(1,pParse
,p
,("end processing\n"));
5670 pParse
->nSelectIndent
--;