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 if( OK_IF_ALWAYS_TRUE(p
->pWith
) ) sqlite3WithDelete(db
, p
->pWith
);
78 if( bFree
) sqlite3DbFreeNN(db
, p
);
85 ** Initialize a SelectDest structure.
87 void sqlite3SelectDestInit(SelectDest
*pDest
, int eDest
, int iParm
){
88 pDest
->eDest
= (u8
)eDest
;
89 pDest
->iSDParm
= iParm
;
97 ** Allocate a new Select structure and return a pointer to that
100 Select
*sqlite3SelectNew(
101 Parse
*pParse
, /* Parsing context */
102 ExprList
*pEList
, /* which columns to include in the result */
103 SrcList
*pSrc
, /* the FROM clause -- which tables to scan */
104 Expr
*pWhere
, /* the WHERE clause */
105 ExprList
*pGroupBy
, /* the GROUP BY clause */
106 Expr
*pHaving
, /* the HAVING clause */
107 ExprList
*pOrderBy
, /* the ORDER BY clause */
108 u32 selFlags
, /* Flag parameters, such as SF_Distinct */
109 Expr
*pLimit
/* LIMIT value. NULL means not used */
113 pNew
= sqlite3DbMallocRawNN(pParse
->db
, sizeof(*pNew
) );
115 assert( pParse
->db
->mallocFailed
);
119 pEList
= sqlite3ExprListAppend(pParse
, 0,
120 sqlite3Expr(pParse
->db
,TK_ASTERISK
,0));
122 pNew
->pEList
= pEList
;
123 pNew
->op
= TK_SELECT
;
124 pNew
->selFlags
= selFlags
;
127 #if SELECTTRACE_ENABLED
128 pNew
->zSelName
[0] = 0;
130 pNew
->addrOpenEphm
[0] = -1;
131 pNew
->addrOpenEphm
[1] = -1;
132 pNew
->nSelectRow
= 0;
133 if( pSrc
==0 ) pSrc
= sqlite3DbMallocZero(pParse
->db
, sizeof(*pSrc
));
135 pNew
->pWhere
= pWhere
;
136 pNew
->pGroupBy
= pGroupBy
;
137 pNew
->pHaving
= pHaving
;
138 pNew
->pOrderBy
= pOrderBy
;
141 pNew
->pLimit
= pLimit
;
143 if( pParse
->db
->mallocFailed
) {
144 clearSelect(pParse
->db
, pNew
, pNew
!=&standin
);
147 assert( pNew
->pSrc
!=0 || pParse
->nErr
>0 );
149 assert( pNew
!=&standin
);
153 #if SELECTTRACE_ENABLED
155 ** Set the name of a Select object
157 void sqlite3SelectSetName(Select
*p
, const char *zName
){
159 sqlite3_snprintf(sizeof(p
->zSelName
), p
->zSelName
, "%s", zName
);
166 ** Delete the given Select structure and all of its substructures.
168 void sqlite3SelectDelete(sqlite3
*db
, Select
*p
){
169 if( OK_IF_ALWAYS_TRUE(p
) ) clearSelect(db
, p
, 1);
173 ** Return a pointer to the right-most SELECT statement in a compound.
175 static Select
*findRightmost(Select
*p
){
176 while( p
->pNext
) p
= p
->pNext
;
181 ** Given 1 to 3 identifiers preceding the JOIN keyword, determine the
182 ** type of join. Return an integer constant that expresses that type
183 ** in terms of the following bit values:
192 ** A full outer join is the combination of JT_LEFT and JT_RIGHT.
194 ** If an illegal or unsupported join type is seen, then still return
195 ** a join type, but put an error in the pParse structure.
197 int sqlite3JoinType(Parse
*pParse
, Token
*pA
, Token
*pB
, Token
*pC
){
201 /* 0123456789 123456789 123456789 123 */
202 static const char zKeyText
[] = "naturaleftouterightfullinnercross";
203 static const struct {
204 u8 i
; /* Beginning of keyword text in zKeyText[] */
205 u8 nChar
; /* Length of the keyword in characters */
206 u8 code
; /* Join type mask */
208 /* natural */ { 0, 7, JT_NATURAL
},
209 /* left */ { 6, 4, JT_LEFT
|JT_OUTER
},
210 /* outer */ { 10, 5, JT_OUTER
},
211 /* right */ { 14, 5, JT_RIGHT
|JT_OUTER
},
212 /* full */ { 19, 4, JT_LEFT
|JT_RIGHT
|JT_OUTER
},
213 /* inner */ { 23, 5, JT_INNER
},
214 /* cross */ { 28, 5, JT_INNER
|JT_CROSS
},
220 for(i
=0; i
<3 && apAll
[i
]; i
++){
222 for(j
=0; j
<ArraySize(aKeyword
); j
++){
223 if( p
->n
==aKeyword
[j
].nChar
224 && sqlite3StrNICmp((char*)p
->z
, &zKeyText
[aKeyword
[j
].i
], p
->n
)==0 ){
225 jointype
|= aKeyword
[j
].code
;
229 testcase( j
==0 || j
==1 || j
==2 || j
==3 || j
==4 || j
==5 || j
==6 );
230 if( j
>=ArraySize(aKeyword
) ){
231 jointype
|= JT_ERROR
;
236 (jointype
& (JT_INNER
|JT_OUTER
))==(JT_INNER
|JT_OUTER
) ||
237 (jointype
& JT_ERROR
)!=0
239 const char *zSp
= " ";
241 if( pC
==0 ){ zSp
++; }
242 sqlite3ErrorMsg(pParse
, "unknown or unsupported join type: "
243 "%T %T%s%T", pA
, pB
, zSp
, pC
);
245 }else if( (jointype
& JT_OUTER
)!=0
246 && (jointype
& (JT_LEFT
|JT_RIGHT
))!=JT_LEFT
){
247 sqlite3ErrorMsg(pParse
,
248 "RIGHT and FULL OUTER JOINs are not currently supported");
255 ** Return the index of a column in a table. Return -1 if the column
256 ** is not contained in the table.
258 static int columnIndex(Table
*pTab
, const char *zCol
){
260 for(i
=0; i
<pTab
->nCol
; i
++){
261 if( sqlite3StrICmp(pTab
->aCol
[i
].zName
, zCol
)==0 ) return i
;
267 ** Search the first N tables in pSrc, from left to right, looking for a
268 ** table that has a column named zCol.
270 ** When found, set *piTab and *piCol to the table index and column index
271 ** of the matching column and return TRUE.
273 ** If not found, return FALSE.
275 static int tableAndColumnIndex(
276 SrcList
*pSrc
, /* Array of tables to search */
277 int N
, /* Number of tables in pSrc->a[] to search */
278 const char *zCol
, /* Name of the column we are looking for */
279 int *piTab
, /* Write index of pSrc->a[] here */
280 int *piCol
/* Write index of pSrc->a[*piTab].pTab->aCol[] here */
282 int i
; /* For looping over tables in pSrc */
283 int iCol
; /* Index of column matching zCol */
285 assert( (piTab
==0)==(piCol
==0) ); /* Both or neither are NULL */
287 iCol
= columnIndex(pSrc
->a
[i
].pTab
, zCol
);
300 ** This function is used to add terms implied by JOIN syntax to the
301 ** WHERE clause expression of a SELECT statement. The new term, which
302 ** is ANDed with the existing WHERE clause, is of the form:
304 ** (tab1.col1 = tab2.col2)
306 ** where tab1 is the iSrc'th table in SrcList pSrc and tab2 is the
307 ** (iSrc+1)'th. Column col1 is column iColLeft of tab1, and col2 is
308 ** column iColRight of tab2.
310 static void addWhereTerm(
311 Parse
*pParse
, /* Parsing context */
312 SrcList
*pSrc
, /* List of tables in FROM clause */
313 int iLeft
, /* Index of first table to join in pSrc */
314 int iColLeft
, /* Index of column in first table */
315 int iRight
, /* Index of second table in pSrc */
316 int iColRight
, /* Index of column in second table */
317 int isOuterJoin
, /* True if this is an OUTER join */
318 Expr
**ppWhere
/* IN/OUT: The WHERE clause to add to */
320 sqlite3
*db
= pParse
->db
;
325 assert( iLeft
<iRight
);
326 assert( pSrc
->nSrc
>iRight
);
327 assert( pSrc
->a
[iLeft
].pTab
);
328 assert( pSrc
->a
[iRight
].pTab
);
330 pE1
= sqlite3CreateColumnExpr(db
, pSrc
, iLeft
, iColLeft
);
331 pE2
= sqlite3CreateColumnExpr(db
, pSrc
, iRight
, iColRight
);
333 pEq
= sqlite3PExpr(pParse
, TK_EQ
, pE1
, pE2
);
334 if( pEq
&& isOuterJoin
){
335 ExprSetProperty(pEq
, EP_FromJoin
);
336 assert( !ExprHasProperty(pEq
, EP_TokenOnly
|EP_Reduced
) );
337 ExprSetVVAProperty(pEq
, EP_NoReduce
);
338 pEq
->iRightJoinTable
= (i16
)pE2
->iTable
;
340 *ppWhere
= sqlite3ExprAnd(db
, *ppWhere
, pEq
);
344 ** Set the EP_FromJoin property on all terms of the given expression.
345 ** And set the Expr.iRightJoinTable to iTable for every term in the
348 ** The EP_FromJoin property is used on terms of an expression to tell
349 ** the LEFT OUTER JOIN processing logic that this term is part of the
350 ** join restriction specified in the ON or USING clause and not a part
351 ** of the more general WHERE clause. These terms are moved over to the
352 ** WHERE clause during join processing but we need to remember that they
353 ** originated in the ON or USING clause.
355 ** The Expr.iRightJoinTable tells the WHERE clause processing that the
356 ** expression depends on table iRightJoinTable even if that table is not
357 ** explicitly mentioned in the expression. That information is needed
358 ** for cases like this:
360 ** SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
362 ** The where clause needs to defer the handling of the t1.x=5
363 ** term until after the t2 loop of the join. In that way, a
364 ** NULL t2 row will be inserted whenever t1.x!=5. If we do not
365 ** defer the handling of t1.x=5, it will be processed immediately
366 ** after the t1 loop and rows with t1.x!=5 will never appear in
367 ** the output, which is incorrect.
369 static void setJoinExpr(Expr
*p
, int iTable
){
371 ExprSetProperty(p
, EP_FromJoin
);
372 assert( !ExprHasProperty(p
, EP_TokenOnly
|EP_Reduced
) );
373 ExprSetVVAProperty(p
, EP_NoReduce
);
374 p
->iRightJoinTable
= (i16
)iTable
;
375 if( p
->op
==TK_FUNCTION
&& p
->x
.pList
){
377 for(i
=0; i
<p
->x
.pList
->nExpr
; i
++){
378 setJoinExpr(p
->x
.pList
->a
[i
].pExpr
, iTable
);
381 setJoinExpr(p
->pLeft
, iTable
);
387 ** This routine processes the join information for a SELECT statement.
388 ** ON and USING clauses are converted into extra terms of the WHERE clause.
389 ** NATURAL joins also create extra WHERE clause terms.
391 ** The terms of a FROM clause are contained in the Select.pSrc structure.
392 ** The left most table is the first entry in Select.pSrc. The right-most
393 ** table is the last entry. The join operator is held in the entry to
394 ** the left. Thus entry 0 contains the join operator for the join between
395 ** entries 0 and 1. Any ON or USING clauses associated with the join are
396 ** also attached to the left entry.
398 ** This routine returns the number of errors encountered.
400 static int sqliteProcessJoin(Parse
*pParse
, Select
*p
){
401 SrcList
*pSrc
; /* All tables in the FROM clause */
402 int i
, j
; /* Loop counters */
403 struct SrcList_item
*pLeft
; /* Left table being joined */
404 struct SrcList_item
*pRight
; /* Right table being joined */
409 for(i
=0; i
<pSrc
->nSrc
-1; i
++, pRight
++, pLeft
++){
410 Table
*pRightTab
= pRight
->pTab
;
413 if( NEVER(pLeft
->pTab
==0 || pRightTab
==0) ) continue;
414 isOuter
= (pRight
->fg
.jointype
& JT_OUTER
)!=0;
416 /* When the NATURAL keyword is present, add WHERE clause terms for
417 ** every column that the two tables have in common.
419 if( pRight
->fg
.jointype
& JT_NATURAL
){
420 if( pRight
->pOn
|| pRight
->pUsing
){
421 sqlite3ErrorMsg(pParse
, "a NATURAL join may not have "
422 "an ON or USING clause", 0);
425 for(j
=0; j
<pRightTab
->nCol
; j
++){
426 char *zName
; /* Name of column in the right table */
427 int iLeft
; /* Matching left table */
428 int iLeftCol
; /* Matching column in the left table */
430 zName
= pRightTab
->aCol
[j
].zName
;
431 if( tableAndColumnIndex(pSrc
, i
+1, zName
, &iLeft
, &iLeftCol
) ){
432 addWhereTerm(pParse
, pSrc
, iLeft
, iLeftCol
, i
+1, j
,
433 isOuter
, &p
->pWhere
);
438 /* Disallow both ON and USING clauses in the same join
440 if( pRight
->pOn
&& pRight
->pUsing
){
441 sqlite3ErrorMsg(pParse
, "cannot have both ON and USING "
442 "clauses in the same join");
446 /* Add the ON clause to the end of the WHERE clause, connected by
450 if( isOuter
) setJoinExpr(pRight
->pOn
, pRight
->iCursor
);
451 p
->pWhere
= sqlite3ExprAnd(pParse
->db
, p
->pWhere
, pRight
->pOn
);
455 /* Create extra terms on the WHERE clause for each column named
456 ** in the USING clause. Example: If the two tables to be joined are
457 ** A and B and the USING clause names X, Y, and Z, then add this
458 ** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
459 ** Report an error if any column mentioned in the USING clause is
460 ** not contained in both tables to be joined.
462 if( pRight
->pUsing
){
463 IdList
*pList
= pRight
->pUsing
;
464 for(j
=0; j
<pList
->nId
; j
++){
465 char *zName
; /* Name of the term in the USING clause */
466 int iLeft
; /* Table on the left with matching column name */
467 int iLeftCol
; /* Column number of matching column on the left */
468 int iRightCol
; /* Column number of matching column on the right */
470 zName
= pList
->a
[j
].zName
;
471 iRightCol
= columnIndex(pRightTab
, zName
);
473 || !tableAndColumnIndex(pSrc
, i
+1, zName
, &iLeft
, &iLeftCol
)
475 sqlite3ErrorMsg(pParse
, "cannot join using column %s - column "
476 "not present in both tables", zName
);
479 addWhereTerm(pParse
, pSrc
, iLeft
, iLeftCol
, i
+1, iRightCol
,
480 isOuter
, &p
->pWhere
);
487 /* Forward reference */
488 static KeyInfo
*keyInfoFromExprList(
489 Parse
*pParse
, /* Parsing context */
490 ExprList
*pList
, /* Form the KeyInfo object from this ExprList */
491 int iStart
, /* Begin with this column of pList */
492 int nExtra
/* Add this many extra columns to the end */
496 ** Generate code that will push the record in registers regData
497 ** through regData+nData-1 onto the sorter.
499 static void pushOntoSorter(
500 Parse
*pParse
, /* Parser context */
501 SortCtx
*pSort
, /* Information about the ORDER BY clause */
502 Select
*pSelect
, /* The whole SELECT statement */
503 int regData
, /* First register holding data to be sorted */
504 int regOrigData
, /* First register holding data before packing */
505 int nData
, /* Number of elements in the data array */
506 int nPrefixReg
/* No. of reg prior to regData available for use */
508 Vdbe
*v
= pParse
->pVdbe
; /* Stmt under construction */
509 int bSeq
= ((pSort
->sortFlags
& SORTFLAG_UseSorter
)==0);
510 int nExpr
= pSort
->pOrderBy
->nExpr
; /* No. of ORDER BY terms */
511 int nBase
= nExpr
+ bSeq
+ nData
; /* Fields in sorter record */
512 int regBase
; /* Regs for sorter record */
513 int regRecord
= ++pParse
->nMem
; /* Assembled sorter record */
514 int nOBSat
= pSort
->nOBSat
; /* ORDER BY terms to skip */
515 int op
; /* Opcode to add sorter record to sorter */
516 int iLimit
; /* LIMIT counter */
518 assert( bSeq
==0 || bSeq
==1 );
519 assert( nData
==1 || regData
==regOrigData
|| regOrigData
==0 );
521 assert( nPrefixReg
==nExpr
+bSeq
);
522 regBase
= regData
- nExpr
- bSeq
;
524 regBase
= pParse
->nMem
+ 1;
525 pParse
->nMem
+= nBase
;
527 assert( pSelect
->iOffset
==0 || pSelect
->iLimit
!=0 );
528 iLimit
= pSelect
->iOffset
? pSelect
->iOffset
+1 : pSelect
->iLimit
;
529 pSort
->labelDone
= sqlite3VdbeMakeLabel(v
);
530 sqlite3ExprCodeExprList(pParse
, pSort
->pOrderBy
, regBase
, regOrigData
,
531 SQLITE_ECEL_DUP
| (regOrigData
? SQLITE_ECEL_REF
: 0));
533 sqlite3VdbeAddOp2(v
, OP_Sequence
, pSort
->iECursor
, regBase
+nExpr
);
535 if( nPrefixReg
==0 && nData
>0 ){
536 sqlite3ExprCodeMove(pParse
, regData
, regBase
+nExpr
+bSeq
, nData
);
538 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regBase
+nOBSat
, nBase
-nOBSat
, regRecord
);
540 int regPrevKey
; /* The first nOBSat columns of the previous row */
541 int addrFirst
; /* Address of the OP_IfNot opcode */
542 int addrJmp
; /* Address of the OP_Jump opcode */
543 VdbeOp
*pOp
; /* Opcode that opens the sorter */
544 int nKey
; /* Number of sorting key columns, including OP_Sequence */
545 KeyInfo
*pKI
; /* Original KeyInfo on the sorter table */
547 regPrevKey
= pParse
->nMem
+1;
548 pParse
->nMem
+= pSort
->nOBSat
;
549 nKey
= nExpr
- pSort
->nOBSat
+ bSeq
;
551 addrFirst
= sqlite3VdbeAddOp1(v
, OP_IfNot
, regBase
+nExpr
);
553 addrFirst
= sqlite3VdbeAddOp1(v
, OP_SequenceTest
, pSort
->iECursor
);
556 sqlite3VdbeAddOp3(v
, OP_Compare
, regPrevKey
, regBase
, pSort
->nOBSat
);
557 pOp
= sqlite3VdbeGetOp(v
, pSort
->addrSortIndex
);
558 if( pParse
->db
->mallocFailed
) return;
559 pOp
->p2
= nKey
+ nData
;
560 pKI
= pOp
->p4
.pKeyInfo
;
561 memset(pKI
->aSortOrder
, 0, pKI
->nKeyField
); /* Makes OP_Jump testable */
562 sqlite3VdbeChangeP4(v
, -1, (char*)pKI
, P4_KEYINFO
);
563 testcase( pKI
->nAllField
> pKI
->nKeyField
+2 );
564 pOp
->p4
.pKeyInfo
= keyInfoFromExprList(pParse
, pSort
->pOrderBy
, nOBSat
,
565 pKI
->nAllField
-pKI
->nKeyField
-1);
566 addrJmp
= sqlite3VdbeCurrentAddr(v
);
567 sqlite3VdbeAddOp3(v
, OP_Jump
, addrJmp
+1, 0, addrJmp
+1); VdbeCoverage(v
);
568 pSort
->labelBkOut
= sqlite3VdbeMakeLabel(v
);
569 pSort
->regReturn
= ++pParse
->nMem
;
570 sqlite3VdbeAddOp2(v
, OP_Gosub
, pSort
->regReturn
, pSort
->labelBkOut
);
571 sqlite3VdbeAddOp1(v
, OP_ResetSorter
, pSort
->iECursor
);
573 sqlite3VdbeAddOp2(v
, OP_IfNot
, iLimit
, pSort
->labelDone
);
576 sqlite3VdbeJumpHere(v
, addrFirst
);
577 sqlite3ExprCodeMove(pParse
, regBase
, regPrevKey
, pSort
->nOBSat
);
578 sqlite3VdbeJumpHere(v
, addrJmp
);
580 if( pSort
->sortFlags
& SORTFLAG_UseSorter
){
581 op
= OP_SorterInsert
;
585 sqlite3VdbeAddOp4Int(v
, op
, pSort
->iECursor
, regRecord
,
586 regBase
+nOBSat
, nBase
-nOBSat
);
590 /* Fill the sorter until it contains LIMIT+OFFSET entries. (The iLimit
591 ** register is initialized with value of LIMIT+OFFSET.) After the sorter
592 ** fills up, delete the least entry in the sorter after each insert.
593 ** Thus we never hold more than the LIMIT+OFFSET rows in memory at once */
594 addr
= sqlite3VdbeAddOp1(v
, OP_IfNotZero
, iLimit
); VdbeCoverage(v
);
595 sqlite3VdbeAddOp1(v
, OP_Last
, pSort
->iECursor
);
596 if( pSort
->bOrderedInnerLoop
){
598 sqlite3VdbeAddOp3(v
, OP_Column
, pSort
->iECursor
, nExpr
, r1
);
599 VdbeComment((v
, "seq"));
601 sqlite3VdbeAddOp1(v
, OP_Delete
, pSort
->iECursor
);
602 if( pSort
->bOrderedInnerLoop
){
603 /* If the inner loop is driven by an index such that values from
604 ** the same iteration of the inner loop are in sorted order, then
605 ** immediately jump to the next iteration of an inner loop if the
606 ** entry from the current iteration does not fit into the top
607 ** LIMIT+OFFSET entries of the sorter. */
608 int iBrk
= sqlite3VdbeCurrentAddr(v
) + 2;
609 sqlite3VdbeAddOp3(v
, OP_Eq
, regBase
+nExpr
, iBrk
, r1
);
610 sqlite3VdbeChangeP5(v
, SQLITE_NULLEQ
);
613 sqlite3VdbeJumpHere(v
, addr
);
618 ** Add code to implement the OFFSET
620 static void codeOffset(
621 Vdbe
*v
, /* Generate code into this VM */
622 int iOffset
, /* Register holding the offset counter */
623 int iContinue
/* Jump here to skip the current record */
626 sqlite3VdbeAddOp3(v
, OP_IfPos
, iOffset
, iContinue
, 1); VdbeCoverage(v
);
627 VdbeComment((v
, "OFFSET"));
632 ** Add code that will check to make sure the N registers starting at iMem
633 ** form a distinct entry. iTab is a sorting index that holds previously
634 ** seen combinations of the N values. A new entry is made in iTab
635 ** if the current N values are new.
637 ** A jump to addrRepeat is made and the N+1 values are popped from the
638 ** stack if the top N elements are not distinct.
640 static void codeDistinct(
641 Parse
*pParse
, /* Parsing and code generating context */
642 int iTab
, /* A sorting index used to test for distinctness */
643 int addrRepeat
, /* Jump to here if not distinct */
644 int N
, /* Number of elements */
645 int iMem
/* First element */
651 r1
= sqlite3GetTempReg(pParse
);
652 sqlite3VdbeAddOp4Int(v
, OP_Found
, iTab
, addrRepeat
, iMem
, N
); VdbeCoverage(v
);
653 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, iMem
, N
, r1
);
654 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iTab
, r1
, iMem
, N
);
655 sqlite3VdbeChangeP5(v
, OPFLAG_USESEEKRESULT
);
656 sqlite3ReleaseTempReg(pParse
, r1
);
660 ** This routine generates the code for the inside of the inner loop
663 ** If srcTab is negative, then the p->pEList expressions
664 ** are evaluated in order to get the data for this row. If srcTab is
665 ** zero or more, then data is pulled from srcTab and p->pEList is used only
666 ** to get the number of columns and the collation sequence for each column.
668 static void selectInnerLoop(
669 Parse
*pParse
, /* The parser context */
670 Select
*p
, /* The complete select statement being coded */
671 int srcTab
, /* Pull data from this table if non-negative */
672 SortCtx
*pSort
, /* If not NULL, info on how to process ORDER BY */
673 DistinctCtx
*pDistinct
, /* If not NULL, info on how to process DISTINCT */
674 SelectDest
*pDest
, /* How to dispose of the results */
675 int iContinue
, /* Jump here to continue with next row */
676 int iBreak
/* Jump here to break out of the inner loop */
678 Vdbe
*v
= pParse
->pVdbe
;
680 int hasDistinct
; /* True if the DISTINCT keyword is present */
681 int eDest
= pDest
->eDest
; /* How to dispose of results */
682 int iParm
= pDest
->iSDParm
; /* First argument to disposal method */
683 int nResultCol
; /* Number of result columns */
684 int nPrefixReg
= 0; /* Number of extra registers before regResult */
686 /* Usually, regResult is the first cell in an array of memory cells
687 ** containing the current result row. In this case regOrig is set to the
688 ** same value. However, if the results are being sent to the sorter, the
689 ** values for any expressions that are also part of the sort-key are omitted
690 ** from this array. In this case regOrig is set to zero. */
691 int regResult
; /* Start of memory holding current results */
692 int regOrig
; /* Start of memory holding full result (or 0) */
695 assert( p
->pEList
!=0 );
696 hasDistinct
= pDistinct
? pDistinct
->eTnctType
: WHERE_DISTINCT_NOOP
;
697 if( pSort
&& pSort
->pOrderBy
==0 ) pSort
= 0;
698 if( pSort
==0 && !hasDistinct
){
699 assert( iContinue
!=0 );
700 codeOffset(v
, p
->iOffset
, iContinue
);
703 /* Pull the requested columns.
705 nResultCol
= p
->pEList
->nExpr
;
707 if( pDest
->iSdst
==0 ){
709 nPrefixReg
= pSort
->pOrderBy
->nExpr
;
710 if( !(pSort
->sortFlags
& SORTFLAG_UseSorter
) ) nPrefixReg
++;
711 pParse
->nMem
+= nPrefixReg
;
713 pDest
->iSdst
= pParse
->nMem
+1;
714 pParse
->nMem
+= nResultCol
;
715 }else if( pDest
->iSdst
+nResultCol
> pParse
->nMem
){
716 /* This is an error condition that can result, for example, when a SELECT
717 ** on the right-hand side of an INSERT contains more result columns than
718 ** there are columns in the table on the left. The error will be caught
719 ** and reported later. But we need to make sure enough memory is allocated
720 ** to avoid other spurious errors in the meantime. */
721 pParse
->nMem
+= nResultCol
;
723 pDest
->nSdst
= nResultCol
;
724 regOrig
= regResult
= pDest
->iSdst
;
726 for(i
=0; i
<nResultCol
; i
++){
727 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, i
, regResult
+i
);
728 VdbeComment((v
, "%s", p
->pEList
->a
[i
].zName
));
730 }else if( eDest
!=SRT_Exists
){
731 /* If the destination is an EXISTS(...) expression, the actual
732 ** values returned by the SELECT are not required.
735 if( eDest
==SRT_Mem
|| eDest
==SRT_Output
|| eDest
==SRT_Coroutine
){
736 ecelFlags
= SQLITE_ECEL_DUP
;
740 if( pSort
&& hasDistinct
==0 && eDest
!=SRT_EphemTab
&& eDest
!=SRT_Table
){
741 /* For each expression in p->pEList that is a copy of an expression in
742 ** the ORDER BY clause (pSort->pOrderBy), set the associated
743 ** iOrderByCol value to one more than the index of the ORDER BY
744 ** expression within the sort-key that pushOntoSorter() will generate.
745 ** This allows the p->pEList field to be omitted from the sorted record,
746 ** saving space and CPU cycles. */
747 ecelFlags
|= (SQLITE_ECEL_OMITREF
|SQLITE_ECEL_REF
);
748 for(i
=pSort
->nOBSat
; i
<pSort
->pOrderBy
->nExpr
; i
++){
750 if( (j
= pSort
->pOrderBy
->a
[i
].u
.x
.iOrderByCol
)>0 ){
751 p
->pEList
->a
[j
-1].u
.x
.iOrderByCol
= i
+1-pSort
->nOBSat
;
755 assert( eDest
==SRT_Set
|| eDest
==SRT_Mem
756 || eDest
==SRT_Coroutine
|| eDest
==SRT_Output
);
758 nResultCol
= sqlite3ExprCodeExprList(pParse
,p
->pEList
,regResult
,
762 /* If the DISTINCT keyword was present on the SELECT statement
763 ** and this row has been seen before, then do not make this row
764 ** part of the result.
767 switch( pDistinct
->eTnctType
){
768 case WHERE_DISTINCT_ORDERED
: {
769 VdbeOp
*pOp
; /* No longer required OpenEphemeral instr. */
770 int iJump
; /* Jump destination */
771 int regPrev
; /* Previous row content */
773 /* Allocate space for the previous row */
774 regPrev
= pParse
->nMem
+1;
775 pParse
->nMem
+= nResultCol
;
777 /* Change the OP_OpenEphemeral coded earlier to an OP_Null
778 ** sets the MEM_Cleared bit on the first register of the
779 ** previous value. This will cause the OP_Ne below to always
780 ** fail on the first iteration of the loop even if the first
783 sqlite3VdbeChangeToNoop(v
, pDistinct
->addrTnct
);
784 pOp
= sqlite3VdbeGetOp(v
, pDistinct
->addrTnct
);
785 pOp
->opcode
= OP_Null
;
789 iJump
= sqlite3VdbeCurrentAddr(v
) + nResultCol
;
790 for(i
=0; i
<nResultCol
; i
++){
791 CollSeq
*pColl
= sqlite3ExprCollSeq(pParse
, p
->pEList
->a
[i
].pExpr
);
792 if( i
<nResultCol
-1 ){
793 sqlite3VdbeAddOp3(v
, OP_Ne
, regResult
+i
, iJump
, regPrev
+i
);
796 sqlite3VdbeAddOp3(v
, OP_Eq
, regResult
+i
, iContinue
, regPrev
+i
);
799 sqlite3VdbeChangeP4(v
, -1, (const char *)pColl
, P4_COLLSEQ
);
800 sqlite3VdbeChangeP5(v
, SQLITE_NULLEQ
);
802 assert( sqlite3VdbeCurrentAddr(v
)==iJump
|| pParse
->db
->mallocFailed
);
803 sqlite3VdbeAddOp3(v
, OP_Copy
, regResult
, regPrev
, nResultCol
-1);
807 case WHERE_DISTINCT_UNIQUE
: {
808 sqlite3VdbeChangeToNoop(v
, pDistinct
->addrTnct
);
813 assert( pDistinct
->eTnctType
==WHERE_DISTINCT_UNORDERED
);
814 codeDistinct(pParse
, pDistinct
->tabTnct
, iContinue
, nResultCol
,
820 codeOffset(v
, p
->iOffset
, iContinue
);
825 /* In this mode, write each query result to the key of the temporary
828 #ifndef SQLITE_OMIT_COMPOUND_SELECT
831 r1
= sqlite3GetTempReg(pParse
);
832 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nResultCol
, r1
);
833 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iParm
, r1
, regResult
, nResultCol
);
834 sqlite3ReleaseTempReg(pParse
, r1
);
838 /* Construct a record from the query result, but instead of
839 ** saving that record, use it as a key to delete elements from
840 ** the temporary table iParm.
843 sqlite3VdbeAddOp3(v
, OP_IdxDelete
, iParm
, regResult
, nResultCol
);
846 #endif /* SQLITE_OMIT_COMPOUND_SELECT */
848 /* Store the result as data using a unique key.
854 int r1
= sqlite3GetTempRange(pParse
, nPrefixReg
+1);
855 testcase( eDest
==SRT_Table
);
856 testcase( eDest
==SRT_EphemTab
);
857 testcase( eDest
==SRT_Fifo
);
858 testcase( eDest
==SRT_DistFifo
);
859 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nResultCol
, r1
+nPrefixReg
);
860 #ifndef SQLITE_OMIT_CTE
861 if( eDest
==SRT_DistFifo
){
862 /* If the destination is DistFifo, then cursor (iParm+1) is open
863 ** on an ephemeral index. If the current row is already present
864 ** in the index, do not write it to the output. If not, add the
865 ** current row to the index and proceed with writing it to the
866 ** output table as well. */
867 int addr
= sqlite3VdbeCurrentAddr(v
) + 4;
868 sqlite3VdbeAddOp4Int(v
, OP_Found
, iParm
+1, addr
, r1
, 0);
870 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iParm
+1, r1
,regResult
,nResultCol
);
875 pushOntoSorter(pParse
, pSort
, p
, r1
+nPrefixReg
,regResult
,1,nPrefixReg
);
877 int r2
= sqlite3GetTempReg(pParse
);
878 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iParm
, r2
);
879 sqlite3VdbeAddOp3(v
, OP_Insert
, iParm
, r1
, r2
);
880 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
881 sqlite3ReleaseTempReg(pParse
, r2
);
883 sqlite3ReleaseTempRange(pParse
, r1
, nPrefixReg
+1);
887 #ifndef SQLITE_OMIT_SUBQUERY
888 /* If we are creating a set for an "expr IN (SELECT ...)" construct,
889 ** then there should be a single item on the stack. Write this
890 ** item into the set table with bogus data.
894 /* At first glance you would think we could optimize out the
895 ** ORDER BY in this case since the order of entries in the set
896 ** does not matter. But there might be a LIMIT clause, in which
897 ** case the order does matter */
899 pParse
, pSort
, p
, regResult
, regOrig
, nResultCol
, nPrefixReg
);
901 int r1
= sqlite3GetTempReg(pParse
);
902 assert( sqlite3Strlen30(pDest
->zAffSdst
)==nResultCol
);
903 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regResult
, nResultCol
,
904 r1
, pDest
->zAffSdst
, nResultCol
);
905 sqlite3ExprCacheAffinityChange(pParse
, regResult
, nResultCol
);
906 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iParm
, r1
, regResult
, nResultCol
);
907 sqlite3ReleaseTempReg(pParse
, r1
);
912 /* If any row exist in the result set, record that fact and abort.
915 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iParm
);
916 /* The LIMIT clause will terminate the loop for us */
920 /* If this is a scalar select that is part of an expression, then
921 ** store the results in the appropriate memory cell or array of
922 ** memory cells and break out of the scan loop.
926 assert( nResultCol
<=pDest
->nSdst
);
928 pParse
, pSort
, p
, regResult
, regOrig
, nResultCol
, nPrefixReg
);
930 assert( nResultCol
==pDest
->nSdst
);
931 assert( regResult
==iParm
);
932 /* The LIMIT clause will jump out of the loop for us */
936 #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
938 case SRT_Coroutine
: /* Send data to a co-routine */
939 case SRT_Output
: { /* Return the results */
940 testcase( eDest
==SRT_Coroutine
);
941 testcase( eDest
==SRT_Output
);
943 pushOntoSorter(pParse
, pSort
, p
, regResult
, regOrig
, nResultCol
,
945 }else if( eDest
==SRT_Coroutine
){
946 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iSDParm
);
948 sqlite3VdbeAddOp2(v
, OP_ResultRow
, regResult
, nResultCol
);
949 sqlite3ExprCacheAffinityChange(pParse
, regResult
, nResultCol
);
954 #ifndef SQLITE_OMIT_CTE
955 /* Write the results into a priority queue that is order according to
956 ** pDest->pOrderBy (in pSO). pDest->iSDParm (in iParm) is the cursor for an
957 ** index with pSO->nExpr+2 columns. Build a key using pSO for the first
958 ** pSO->nExpr columns, then make sure all keys are unique by adding a
959 ** final OP_Sequence column. The last column is the record as a blob.
967 pSO
= pDest
->pOrderBy
;
970 r1
= sqlite3GetTempReg(pParse
);
971 r2
= sqlite3GetTempRange(pParse
, nKey
+2);
973 if( eDest
==SRT_DistQueue
){
974 /* If the destination is DistQueue, then cursor (iParm+1) is open
975 ** on a second ephemeral index that holds all values every previously
976 ** added to the queue. */
977 addrTest
= sqlite3VdbeAddOp4Int(v
, OP_Found
, iParm
+1, 0,
978 regResult
, nResultCol
);
981 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nResultCol
, r3
);
982 if( eDest
==SRT_DistQueue
){
983 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
+1, r3
);
984 sqlite3VdbeChangeP5(v
, OPFLAG_USESEEKRESULT
);
986 for(i
=0; i
<nKey
; i
++){
987 sqlite3VdbeAddOp2(v
, OP_SCopy
,
988 regResult
+ pSO
->a
[i
].u
.x
.iOrderByCol
- 1,
991 sqlite3VdbeAddOp2(v
, OP_Sequence
, iParm
, r2
+nKey
);
992 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, r2
, nKey
+2, r1
);
993 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iParm
, r1
, r2
, nKey
+2);
994 if( addrTest
) sqlite3VdbeJumpHere(v
, addrTest
);
995 sqlite3ReleaseTempReg(pParse
, r1
);
996 sqlite3ReleaseTempRange(pParse
, r2
, nKey
+2);
999 #endif /* SQLITE_OMIT_CTE */
1003 #if !defined(SQLITE_OMIT_TRIGGER)
1004 /* Discard the results. This is used for SELECT statements inside
1005 ** the body of a TRIGGER. The purpose of such selects is to call
1006 ** user-defined functions that have side effects. We do not care
1007 ** about the actual results of the select.
1010 assert( eDest
==SRT_Discard
);
1016 /* Jump to the end of the loop if the LIMIT is reached. Except, if
1017 ** there is a sorter, in which case the sorter has already limited
1018 ** the output for us.
1020 if( pSort
==0 && p
->iLimit
){
1021 sqlite3VdbeAddOp2(v
, OP_DecrJumpZero
, p
->iLimit
, iBreak
); VdbeCoverage(v
);
1026 ** Allocate a KeyInfo object sufficient for an index of N key columns and
1029 KeyInfo
*sqlite3KeyInfoAlloc(sqlite3
*db
, int N
, int X
){
1030 int nExtra
= (N
+X
)*(sizeof(CollSeq
*)+1) - sizeof(CollSeq
*);
1031 KeyInfo
*p
= sqlite3DbMallocRawNN(db
, sizeof(KeyInfo
) + nExtra
);
1033 p
->aSortOrder
= (u8
*)&p
->aColl
[N
+X
];
1034 p
->nKeyField
= (u16
)N
;
1035 p
->nAllField
= (u16
)(N
+X
);
1039 memset(&p
[1], 0, nExtra
);
1041 sqlite3OomFault(db
);
1047 ** Deallocate a KeyInfo object
1049 void sqlite3KeyInfoUnref(KeyInfo
*p
){
1051 assert( p
->nRef
>0 );
1053 if( p
->nRef
==0 ) sqlite3DbFreeNN(p
->db
, p
);
1058 ** Make a new pointer to a KeyInfo object
1060 KeyInfo
*sqlite3KeyInfoRef(KeyInfo
*p
){
1062 assert( p
->nRef
>0 );
1070 ** Return TRUE if a KeyInfo object can be change. The KeyInfo object
1071 ** can only be changed if this is just a single reference to the object.
1073 ** This routine is used only inside of assert() statements.
1075 int sqlite3KeyInfoIsWriteable(KeyInfo
*p
){ return p
->nRef
==1; }
1076 #endif /* SQLITE_DEBUG */
1079 ** Given an expression list, generate a KeyInfo structure that records
1080 ** the collating sequence for each expression in that expression list.
1082 ** If the ExprList is an ORDER BY or GROUP BY clause then the resulting
1083 ** KeyInfo structure is appropriate for initializing a virtual index to
1084 ** implement that clause. If the ExprList is the result set of a SELECT
1085 ** then the KeyInfo structure is appropriate for initializing a virtual
1086 ** index to implement a DISTINCT test.
1088 ** Space to hold the KeyInfo structure is obtained from malloc. The calling
1089 ** function is responsible for seeing that this structure is eventually
1092 static KeyInfo
*keyInfoFromExprList(
1093 Parse
*pParse
, /* Parsing context */
1094 ExprList
*pList
, /* Form the KeyInfo object from this ExprList */
1095 int iStart
, /* Begin with this column of pList */
1096 int nExtra
/* Add this many extra columns to the end */
1100 struct ExprList_item
*pItem
;
1101 sqlite3
*db
= pParse
->db
;
1104 nExpr
= pList
->nExpr
;
1105 pInfo
= sqlite3KeyInfoAlloc(db
, nExpr
-iStart
, nExtra
+1);
1107 assert( sqlite3KeyInfoIsWriteable(pInfo
) );
1108 for(i
=iStart
, pItem
=pList
->a
+iStart
; i
<nExpr
; i
++, pItem
++){
1109 pInfo
->aColl
[i
-iStart
] = sqlite3ExprNNCollSeq(pParse
, pItem
->pExpr
);
1110 pInfo
->aSortOrder
[i
-iStart
] = pItem
->sortOrder
;
1117 ** Name of the connection operator, used for error messages.
1119 static const char *selectOpName(int id
){
1122 case TK_ALL
: z
= "UNION ALL"; break;
1123 case TK_INTERSECT
: z
= "INTERSECT"; break;
1124 case TK_EXCEPT
: z
= "EXCEPT"; break;
1125 default: z
= "UNION"; break;
1130 #ifndef SQLITE_OMIT_EXPLAIN
1132 ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
1133 ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
1134 ** where the caption is of the form:
1136 ** "USE TEMP B-TREE FOR xxx"
1138 ** where xxx is one of "DISTINCT", "ORDER BY" or "GROUP BY". Exactly which
1139 ** is determined by the zUsage argument.
1141 static void explainTempTable(Parse
*pParse
, const char *zUsage
){
1142 if( pParse
->explain
==2 ){
1143 Vdbe
*v
= pParse
->pVdbe
;
1144 char *zMsg
= sqlite3MPrintf(pParse
->db
, "USE TEMP B-TREE FOR %s", zUsage
);
1145 sqlite3VdbeAddOp4(v
, OP_Explain
, pParse
->iSelectId
, 0, 0, zMsg
, P4_DYNAMIC
);
1150 ** Assign expression b to lvalue a. A second, no-op, version of this macro
1151 ** is provided when SQLITE_OMIT_EXPLAIN is defined. This allows the code
1152 ** in sqlite3Select() to assign values to structure member variables that
1153 ** only exist if SQLITE_OMIT_EXPLAIN is not defined without polluting the
1154 ** code with #ifndef directives.
1156 # define explainSetInteger(a, b) a = b
1159 /* No-op versions of the explainXXX() functions and macros. */
1160 # define explainTempTable(y,z)
1161 # define explainSetInteger(y,z)
1164 #if !defined(SQLITE_OMIT_EXPLAIN) && !defined(SQLITE_OMIT_COMPOUND_SELECT)
1166 ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
1167 ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
1168 ** where the caption is of one of the two forms:
1170 ** "COMPOSITE SUBQUERIES iSub1 and iSub2 (op)"
1171 ** "COMPOSITE SUBQUERIES iSub1 and iSub2 USING TEMP B-TREE (op)"
1173 ** where iSub1 and iSub2 are the integers passed as the corresponding
1174 ** function parameters, and op is the text representation of the parameter
1175 ** of the same name. The parameter "op" must be one of TK_UNION, TK_EXCEPT,
1176 ** TK_INTERSECT or TK_ALL. The first form is used if argument bUseTmp is
1177 ** false, or the second form if it is true.
1179 static void explainComposite(
1180 Parse
*pParse
, /* Parse context */
1181 int op
, /* One of TK_UNION, TK_EXCEPT etc. */
1182 int iSub1
, /* Subquery id 1 */
1183 int iSub2
, /* Subquery id 2 */
1184 int bUseTmp
/* True if a temp table was used */
1186 assert( op
==TK_UNION
|| op
==TK_EXCEPT
|| op
==TK_INTERSECT
|| op
==TK_ALL
);
1187 if( pParse
->explain
==2 ){
1188 Vdbe
*v
= pParse
->pVdbe
;
1189 char *zMsg
= sqlite3MPrintf(
1190 pParse
->db
, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1
, iSub2
,
1191 bUseTmp
?"USING TEMP B-TREE ":"", selectOpName(op
)
1193 sqlite3VdbeAddOp4(v
, OP_Explain
, pParse
->iSelectId
, 0, 0, zMsg
, P4_DYNAMIC
);
1197 /* No-op versions of the explainXXX() functions and macros. */
1198 # define explainComposite(v,w,x,y,z)
1202 ** If the inner loop was generated using a non-null pOrderBy argument,
1203 ** then the results were placed in a sorter. After the loop is terminated
1204 ** we need to run the sorter and output the results. The following
1205 ** routine generates the code needed to do that.
1207 static void generateSortTail(
1208 Parse
*pParse
, /* Parsing context */
1209 Select
*p
, /* The SELECT statement */
1210 SortCtx
*pSort
, /* Information on the ORDER BY clause */
1211 int nColumn
, /* Number of columns of data */
1212 SelectDest
*pDest
/* Write the sorted results here */
1214 Vdbe
*v
= pParse
->pVdbe
; /* The prepared statement */
1215 int addrBreak
= pSort
->labelDone
; /* Jump here to exit loop */
1216 int addrContinue
= sqlite3VdbeMakeLabel(v
); /* Jump here for next cycle */
1220 ExprList
*pOrderBy
= pSort
->pOrderBy
;
1221 int eDest
= pDest
->eDest
;
1222 int iParm
= pDest
->iSDParm
;
1227 int iSortTab
; /* Sorter cursor to read from */
1228 int nSortData
; /* Trailing values to read from sorter */
1230 int bSeq
; /* True if sorter record includes seq. no. */
1231 struct ExprList_item
*aOutEx
= p
->pEList
->a
;
1233 assert( addrBreak
<0 );
1234 if( pSort
->labelBkOut
){
1235 sqlite3VdbeAddOp2(v
, OP_Gosub
, pSort
->regReturn
, pSort
->labelBkOut
);
1236 sqlite3VdbeGoto(v
, addrBreak
);
1237 sqlite3VdbeResolveLabel(v
, pSort
->labelBkOut
);
1239 iTab
= pSort
->iECursor
;
1240 if( eDest
==SRT_Output
|| eDest
==SRT_Coroutine
|| eDest
==SRT_Mem
){
1242 regRow
= pDest
->iSdst
;
1243 nSortData
= nColumn
;
1245 regRowid
= sqlite3GetTempReg(pParse
);
1246 regRow
= sqlite3GetTempRange(pParse
, nColumn
);
1247 nSortData
= nColumn
;
1249 nKey
= pOrderBy
->nExpr
- pSort
->nOBSat
;
1250 if( pSort
->sortFlags
& SORTFLAG_UseSorter
){
1251 int regSortOut
= ++pParse
->nMem
;
1252 iSortTab
= pParse
->nTab
++;
1253 if( pSort
->labelBkOut
){
1254 addrOnce
= sqlite3VdbeAddOp0(v
, OP_Once
); VdbeCoverage(v
);
1256 sqlite3VdbeAddOp3(v
, OP_OpenPseudo
, iSortTab
, regSortOut
, nKey
+1+nSortData
);
1257 if( addrOnce
) sqlite3VdbeJumpHere(v
, addrOnce
);
1258 addr
= 1 + sqlite3VdbeAddOp2(v
, OP_SorterSort
, iTab
, addrBreak
);
1260 codeOffset(v
, p
->iOffset
, addrContinue
);
1261 sqlite3VdbeAddOp3(v
, OP_SorterData
, iTab
, regSortOut
, iSortTab
);
1264 addr
= 1 + sqlite3VdbeAddOp2(v
, OP_Sort
, iTab
, addrBreak
); VdbeCoverage(v
);
1265 codeOffset(v
, p
->iOffset
, addrContinue
);
1269 for(i
=0, iCol
=nKey
+bSeq
; i
<nSortData
; i
++){
1271 if( aOutEx
[i
].u
.x
.iOrderByCol
){
1272 iRead
= aOutEx
[i
].u
.x
.iOrderByCol
-1;
1276 sqlite3VdbeAddOp3(v
, OP_Column
, iSortTab
, iRead
, regRow
+i
);
1277 VdbeComment((v
, "%s", aOutEx
[i
].zName
? aOutEx
[i
].zName
: aOutEx
[i
].zSpan
));
1281 case SRT_EphemTab
: {
1282 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iParm
, regRowid
);
1283 sqlite3VdbeAddOp3(v
, OP_Insert
, iParm
, regRow
, regRowid
);
1284 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
1287 #ifndef SQLITE_OMIT_SUBQUERY
1289 assert( nColumn
==sqlite3Strlen30(pDest
->zAffSdst
) );
1290 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regRow
, nColumn
, regRowid
,
1291 pDest
->zAffSdst
, nColumn
);
1292 sqlite3ExprCacheAffinityChange(pParse
, regRow
, nColumn
);
1293 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, iParm
, regRowid
, regRow
, nColumn
);
1297 /* The LIMIT clause will terminate the loop for us */
1302 assert( eDest
==SRT_Output
|| eDest
==SRT_Coroutine
);
1303 testcase( eDest
==SRT_Output
);
1304 testcase( eDest
==SRT_Coroutine
);
1305 if( eDest
==SRT_Output
){
1306 sqlite3VdbeAddOp2(v
, OP_ResultRow
, pDest
->iSdst
, nColumn
);
1307 sqlite3ExprCacheAffinityChange(pParse
, pDest
->iSdst
, nColumn
);
1309 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iSDParm
);
1315 if( eDest
==SRT_Set
){
1316 sqlite3ReleaseTempRange(pParse
, regRow
, nColumn
);
1318 sqlite3ReleaseTempReg(pParse
, regRow
);
1320 sqlite3ReleaseTempReg(pParse
, regRowid
);
1322 /* The bottom of the loop
1324 sqlite3VdbeResolveLabel(v
, addrContinue
);
1325 if( pSort
->sortFlags
& SORTFLAG_UseSorter
){
1326 sqlite3VdbeAddOp2(v
, OP_SorterNext
, iTab
, addr
); VdbeCoverage(v
);
1328 sqlite3VdbeAddOp2(v
, OP_Next
, iTab
, addr
); VdbeCoverage(v
);
1330 if( pSort
->regReturn
) sqlite3VdbeAddOp1(v
, OP_Return
, pSort
->regReturn
);
1331 sqlite3VdbeResolveLabel(v
, addrBreak
);
1335 ** Return a pointer to a string containing the 'declaration type' of the
1336 ** expression pExpr. The string may be treated as static by the caller.
1338 ** Also try to estimate the size of the returned value and return that
1339 ** result in *pEstWidth.
1341 ** The declaration type is the exact datatype definition extracted from the
1342 ** original CREATE TABLE statement if the expression is a column. The
1343 ** declaration type for a ROWID field is INTEGER. Exactly when an expression
1344 ** is considered a column can be complex in the presence of subqueries. The
1345 ** result-set expression in all of the following SELECT statements is
1346 ** considered a column by this function.
1348 ** SELECT col FROM tbl;
1349 ** SELECT (SELECT col FROM tbl;
1350 ** SELECT (SELECT col FROM tbl);
1351 ** SELECT abc FROM (SELECT col AS abc FROM tbl);
1353 ** The declaration type for any expression other than a column is NULL.
1355 ** This routine has either 3 or 6 parameters depending on whether or not
1356 ** the SQLITE_ENABLE_COLUMN_METADATA compile-time option is used.
1358 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1359 # define columnType(A,B,C,D,E) columnTypeImpl(A,B,C,D,E)
1360 #else /* if !defined(SQLITE_ENABLE_COLUMN_METADATA) */
1361 # define columnType(A,B,C,D,E) columnTypeImpl(A,B)
1363 static const char *columnTypeImpl(
1365 #ifndef SQLITE_ENABLE_COLUMN_METADATA
1369 const char **pzOrigDb
,
1370 const char **pzOrigTab
,
1371 const char **pzOrigCol
1374 char const *zType
= 0;
1376 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1377 char const *zOrigDb
= 0;
1378 char const *zOrigTab
= 0;
1379 char const *zOrigCol
= 0;
1383 assert( pNC
->pSrcList
!=0 );
1384 switch( pExpr
->op
){
1387 /* The expression is a column. Locate the table the column is being
1388 ** extracted from in NameContext.pSrcList. This table may be real
1389 ** database table or a subquery.
1391 Table
*pTab
= 0; /* Table structure column is extracted from */
1392 Select
*pS
= 0; /* Select the column is extracted from */
1393 int iCol
= pExpr
->iColumn
; /* Index of column in pTab */
1394 testcase( pExpr
->op
==TK_AGG_COLUMN
);
1395 testcase( pExpr
->op
==TK_COLUMN
);
1396 while( pNC
&& !pTab
){
1397 SrcList
*pTabList
= pNC
->pSrcList
;
1398 for(j
=0;j
<pTabList
->nSrc
&& pTabList
->a
[j
].iCursor
!=pExpr
->iTable
;j
++);
1399 if( j
<pTabList
->nSrc
){
1400 pTab
= pTabList
->a
[j
].pTab
;
1401 pS
= pTabList
->a
[j
].pSelect
;
1408 /* At one time, code such as "SELECT new.x" within a trigger would
1409 ** cause this condition to run. Since then, we have restructured how
1410 ** trigger code is generated and so this condition is no longer
1411 ** possible. However, it can still be true for statements like
1414 ** CREATE TABLE t1(col INTEGER);
1415 ** SELECT (SELECT t1.col) FROM FROM t1;
1417 ** when columnType() is called on the expression "t1.col" in the
1418 ** sub-select. In this case, set the column type to NULL, even
1419 ** though it should really be "INTEGER".
1421 ** This is not a problem, as the column type of "t1.col" is never
1422 ** used. When columnType() is called on the expression
1423 ** "(SELECT t1.col)", the correct type is returned (see the TK_SELECT
1428 assert( pTab
&& pExpr
->pTab
==pTab
);
1430 /* The "table" is actually a sub-select or a view in the FROM clause
1431 ** of the SELECT statement. Return the declaration type and origin
1432 ** data for the result-set column of the sub-select.
1434 if( iCol
>=0 && iCol
<pS
->pEList
->nExpr
){
1435 /* If iCol is less than zero, then the expression requests the
1436 ** rowid of the sub-select or view. This expression is legal (see
1437 ** test case misc2.2.2) - it always evaluates to NULL.
1440 Expr
*p
= pS
->pEList
->a
[iCol
].pExpr
;
1441 sNC
.pSrcList
= pS
->pSrc
;
1443 sNC
.pParse
= pNC
->pParse
;
1444 zType
= columnType(&sNC
, p
,&zOrigDb
,&zOrigTab
,&zOrigCol
);
1447 /* A real table or a CTE table */
1449 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1450 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1451 assert( iCol
==XN_ROWID
|| (iCol
>=0 && iCol
<pTab
->nCol
) );
1456 zOrigCol
= pTab
->aCol
[iCol
].zName
;
1457 zType
= sqlite3ColumnType(&pTab
->aCol
[iCol
],0);
1459 zOrigTab
= pTab
->zName
;
1460 if( pNC
->pParse
&& pTab
->pSchema
){
1461 int iDb
= sqlite3SchemaToIndex(pNC
->pParse
->db
, pTab
->pSchema
);
1462 zOrigDb
= pNC
->pParse
->db
->aDb
[iDb
].zDbSName
;
1465 assert( iCol
==XN_ROWID
|| (iCol
>=0 && iCol
<pTab
->nCol
) );
1469 zType
= sqlite3ColumnType(&pTab
->aCol
[iCol
],0);
1475 #ifndef SQLITE_OMIT_SUBQUERY
1477 /* The expression is a sub-select. Return the declaration type and
1478 ** origin info for the single column in the result set of the SELECT
1482 Select
*pS
= pExpr
->x
.pSelect
;
1483 Expr
*p
= pS
->pEList
->a
[0].pExpr
;
1484 assert( ExprHasProperty(pExpr
, EP_xIsSelect
) );
1485 sNC
.pSrcList
= pS
->pSrc
;
1487 sNC
.pParse
= pNC
->pParse
;
1488 zType
= columnType(&sNC
, p
, &zOrigDb
, &zOrigTab
, &zOrigCol
);
1494 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1496 assert( pzOrigTab
&& pzOrigCol
);
1497 *pzOrigDb
= zOrigDb
;
1498 *pzOrigTab
= zOrigTab
;
1499 *pzOrigCol
= zOrigCol
;
1506 ** Generate code that will tell the VDBE the declaration types of columns
1507 ** in the result set.
1509 static void generateColumnTypes(
1510 Parse
*pParse
, /* Parser context */
1511 SrcList
*pTabList
, /* List of tables */
1512 ExprList
*pEList
/* Expressions defining the result set */
1514 #ifndef SQLITE_OMIT_DECLTYPE
1515 Vdbe
*v
= pParse
->pVdbe
;
1518 sNC
.pSrcList
= pTabList
;
1519 sNC
.pParse
= pParse
;
1521 for(i
=0; i
<pEList
->nExpr
; i
++){
1522 Expr
*p
= pEList
->a
[i
].pExpr
;
1524 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1525 const char *zOrigDb
= 0;
1526 const char *zOrigTab
= 0;
1527 const char *zOrigCol
= 0;
1528 zType
= columnType(&sNC
, p
, &zOrigDb
, &zOrigTab
, &zOrigCol
);
1530 /* The vdbe must make its own copy of the column-type and other
1531 ** column specific strings, in case the schema is reset before this
1532 ** virtual machine is deleted.
1534 sqlite3VdbeSetColName(v
, i
, COLNAME_DATABASE
, zOrigDb
, SQLITE_TRANSIENT
);
1535 sqlite3VdbeSetColName(v
, i
, COLNAME_TABLE
, zOrigTab
, SQLITE_TRANSIENT
);
1536 sqlite3VdbeSetColName(v
, i
, COLNAME_COLUMN
, zOrigCol
, SQLITE_TRANSIENT
);
1538 zType
= columnType(&sNC
, p
, 0, 0, 0);
1540 sqlite3VdbeSetColName(v
, i
, COLNAME_DECLTYPE
, zType
, SQLITE_TRANSIENT
);
1542 #endif /* !defined(SQLITE_OMIT_DECLTYPE) */
1547 ** Compute the column names for a SELECT statement.
1549 ** The only guarantee that SQLite makes about column names is that if the
1550 ** column has an AS clause assigning it a name, that will be the name used.
1551 ** That is the only documented guarantee. However, countless applications
1552 ** developed over the years have made baseless assumptions about column names
1553 ** and will break if those assumptions changes. Hence, use extreme caution
1554 ** when modifying this routine to avoid breaking legacy.
1556 ** See Also: sqlite3ColumnsFromExprList()
1558 ** The PRAGMA short_column_names and PRAGMA full_column_names settings are
1559 ** deprecated. The default setting is short=ON, full=OFF. 99.9% of all
1560 ** applications should operate this way. Nevertheless, we need to support the
1561 ** other modes for legacy:
1563 ** short=OFF, full=OFF: Column name is the text of the expression has it
1564 ** originally appears in the SELECT statement. In
1565 ** other words, the zSpan of the result expression.
1567 ** short=ON, full=OFF: (This is the default setting). If the result
1568 ** refers directly to a table column, then the
1569 ** result column name is just the table column
1570 ** name: COLUMN. Otherwise use zSpan.
1572 ** full=ON, short=ANY: If the result refers directly to a table column,
1573 ** then the result column name with the table name
1574 ** prefix, ex: TABLE.COLUMN. Otherwise use zSpan.
1576 static void generateColumnNames(
1577 Parse
*pParse
, /* Parser context */
1578 Select
*pSelect
/* Generate column names for this SELECT statement */
1580 Vdbe
*v
= pParse
->pVdbe
;
1585 sqlite3
*db
= pParse
->db
;
1586 int fullName
; /* TABLE.COLUMN if no AS clause and is a direct table ref */
1587 int srcName
; /* COLUMN or TABLE.COLUMN if no AS clause and is direct */
1589 #ifndef SQLITE_OMIT_EXPLAIN
1590 /* If this is an EXPLAIN, skip this step */
1591 if( pParse
->explain
){
1596 if( pParse
->colNamesSet
|| db
->mallocFailed
) return;
1597 /* Column names are determined by the left-most term of a compound select */
1598 while( pSelect
->pPrior
) pSelect
= pSelect
->pPrior
;
1599 pTabList
= pSelect
->pSrc
;
1600 pEList
= pSelect
->pEList
;
1602 assert( pTabList
!=0 );
1603 pParse
->colNamesSet
= 1;
1604 fullName
= (db
->flags
& SQLITE_FullColNames
)!=0;
1605 srcName
= (db
->flags
& SQLITE_ShortColNames
)!=0 || fullName
;
1606 sqlite3VdbeSetNumCols(v
, pEList
->nExpr
);
1607 for(i
=0; i
<pEList
->nExpr
; i
++){
1608 Expr
*p
= pEList
->a
[i
].pExpr
;
1611 assert( p
->op
!=TK_AGG_COLUMN
); /* Agg processing has not run yet */
1612 assert( p
->op
!=TK_COLUMN
|| p
->pTab
!=0 ); /* Covering idx not yet coded */
1613 if( pEList
->a
[i
].zName
){
1614 /* An AS clause always takes first priority */
1615 char *zName
= pEList
->a
[i
].zName
;
1616 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zName
, SQLITE_TRANSIENT
);
1617 }else if( srcName
&& p
->op
==TK_COLUMN
){
1619 int iCol
= p
->iColumn
;
1622 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1623 assert( iCol
==-1 || (iCol
>=0 && iCol
<pTab
->nCol
) );
1627 zCol
= pTab
->aCol
[iCol
].zName
;
1631 zName
= sqlite3MPrintf(db
, "%s.%s", pTab
->zName
, zCol
);
1632 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zName
, SQLITE_DYNAMIC
);
1634 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zCol
, SQLITE_TRANSIENT
);
1637 const char *z
= pEList
->a
[i
].zSpan
;
1638 z
= z
==0 ? sqlite3MPrintf(db
, "column%d", i
+1) : sqlite3DbStrDup(db
, z
);
1639 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, z
, SQLITE_DYNAMIC
);
1642 generateColumnTypes(pParse
, pTabList
, pEList
);
1646 ** Given an expression list (which is really the list of expressions
1647 ** that form the result set of a SELECT statement) compute appropriate
1648 ** column names for a table that would hold the expression list.
1650 ** All column names will be unique.
1652 ** Only the column names are computed. Column.zType, Column.zColl,
1653 ** and other fields of Column are zeroed.
1655 ** Return SQLITE_OK on success. If a memory allocation error occurs,
1656 ** store NULL in *paCol and 0 in *pnCol and return SQLITE_NOMEM.
1658 ** The only guarantee that SQLite makes about column names is that if the
1659 ** column has an AS clause assigning it a name, that will be the name used.
1660 ** That is the only documented guarantee. However, countless applications
1661 ** developed over the years have made baseless assumptions about column names
1662 ** and will break if those assumptions changes. Hence, use extreme caution
1663 ** when modifying this routine to avoid breaking legacy.
1665 ** See Also: generateColumnNames()
1667 int sqlite3ColumnsFromExprList(
1668 Parse
*pParse
, /* Parsing context */
1669 ExprList
*pEList
, /* Expr list from which to derive column names */
1670 i16
*pnCol
, /* Write the number of columns here */
1671 Column
**paCol
/* Write the new column list here */
1673 sqlite3
*db
= pParse
->db
; /* Database connection */
1674 int i
, j
; /* Loop counters */
1675 u32 cnt
; /* Index added to make the name unique */
1676 Column
*aCol
, *pCol
; /* For looping over result columns */
1677 int nCol
; /* Number of columns in the result set */
1678 char *zName
; /* Column name */
1679 int nName
; /* Size of name in zName[] */
1680 Hash ht
; /* Hash table of column names */
1682 sqlite3HashInit(&ht
);
1684 nCol
= pEList
->nExpr
;
1685 aCol
= sqlite3DbMallocZero(db
, sizeof(aCol
[0])*nCol
);
1686 testcase( aCol
==0 );
1687 if( nCol
>32767 ) nCol
= 32767;
1692 assert( nCol
==(i16
)nCol
);
1696 for(i
=0, pCol
=aCol
; i
<nCol
&& !db
->mallocFailed
; i
++, pCol
++){
1697 /* Get an appropriate name for the column
1699 if( (zName
= pEList
->a
[i
].zName
)!=0 ){
1700 /* If the column contains an "AS <name>" phrase, use <name> as the name */
1702 Expr
*pColExpr
= sqlite3ExprSkipCollate(pEList
->a
[i
].pExpr
);
1703 while( pColExpr
->op
==TK_DOT
){
1704 pColExpr
= pColExpr
->pRight
;
1705 assert( pColExpr
!=0 );
1707 if( (pColExpr
->op
==TK_COLUMN
|| pColExpr
->op
==TK_AGG_COLUMN
)
1708 && pColExpr
->pTab
!=0
1710 /* For columns use the column name name */
1711 int iCol
= pColExpr
->iColumn
;
1712 Table
*pTab
= pColExpr
->pTab
;
1713 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1714 zName
= iCol
>=0 ? pTab
->aCol
[iCol
].zName
: "rowid";
1715 }else if( pColExpr
->op
==TK_ID
){
1716 assert( !ExprHasProperty(pColExpr
, EP_IntValue
) );
1717 zName
= pColExpr
->u
.zToken
;
1719 /* Use the original text of the column expression as its name */
1720 zName
= pEList
->a
[i
].zSpan
;
1724 zName
= sqlite3DbStrDup(db
, zName
);
1726 zName
= sqlite3MPrintf(db
,"column%d",i
+1);
1729 /* Make sure the column name is unique. If the name is not unique,
1730 ** append an integer to the name so that it becomes unique.
1733 while( zName
&& sqlite3HashFind(&ht
, zName
)!=0 ){
1734 nName
= sqlite3Strlen30(zName
);
1736 for(j
=nName
-1; j
>0 && sqlite3Isdigit(zName
[j
]); j
--){}
1737 if( zName
[j
]==':' ) nName
= j
;
1739 zName
= sqlite3MPrintf(db
, "%.*z:%u", nName
, zName
, ++cnt
);
1740 if( cnt
>3 ) sqlite3_randomness(sizeof(cnt
), &cnt
);
1742 pCol
->zName
= zName
;
1743 sqlite3ColumnPropertiesFromName(0, pCol
);
1744 if( zName
&& sqlite3HashInsert(&ht
, zName
, pCol
)==pCol
){
1745 sqlite3OomFault(db
);
1748 sqlite3HashClear(&ht
);
1749 if( db
->mallocFailed
){
1751 sqlite3DbFree(db
, aCol
[j
].zName
);
1753 sqlite3DbFree(db
, aCol
);
1756 return SQLITE_NOMEM_BKPT
;
1762 ** Add type and collation information to a column list based on
1763 ** a SELECT statement.
1765 ** The column list presumably came from selectColumnNamesFromExprList().
1766 ** The column list has only names, not types or collations. This
1767 ** routine goes through and adds the types and collations.
1769 ** This routine requires that all identifiers in the SELECT
1770 ** statement be resolved.
1772 void sqlite3SelectAddColumnTypeAndCollation(
1773 Parse
*pParse
, /* Parsing contexts */
1774 Table
*pTab
, /* Add column type information to this table */
1775 Select
*pSelect
/* SELECT used to determine types and collations */
1777 sqlite3
*db
= pParse
->db
;
1783 struct ExprList_item
*a
;
1785 assert( pSelect
!=0 );
1786 assert( (pSelect
->selFlags
& SF_Resolved
)!=0 );
1787 assert( pTab
->nCol
==pSelect
->pEList
->nExpr
|| db
->mallocFailed
);
1788 if( db
->mallocFailed
) return;
1789 memset(&sNC
, 0, sizeof(sNC
));
1790 sNC
.pSrcList
= pSelect
->pSrc
;
1791 a
= pSelect
->pEList
->a
;
1792 for(i
=0, pCol
=pTab
->aCol
; i
<pTab
->nCol
; i
++, pCol
++){
1796 zType
= columnType(&sNC
, p
, 0, 0, 0);
1797 /* pCol->szEst = ... // Column size est for SELECT tables never used */
1798 pCol
->affinity
= sqlite3ExprAffinity(p
);
1800 m
= sqlite3Strlen30(zType
);
1801 n
= sqlite3Strlen30(pCol
->zName
);
1802 pCol
->zName
= sqlite3DbReallocOrFree(db
, pCol
->zName
, n
+m
+2);
1804 memcpy(&pCol
->zName
[n
+1], zType
, m
+1);
1805 pCol
->colFlags
|= COLFLAG_HASTYPE
;
1808 if( pCol
->affinity
==0 ) pCol
->affinity
= SQLITE_AFF_BLOB
;
1809 pColl
= sqlite3ExprCollSeq(pParse
, p
);
1810 if( pColl
&& pCol
->zColl
==0 ){
1811 pCol
->zColl
= sqlite3DbStrDup(db
, pColl
->zName
);
1814 pTab
->szTabRow
= 1; /* Any non-zero value works */
1818 ** Given a SELECT statement, generate a Table structure that describes
1819 ** the result set of that SELECT.
1821 Table
*sqlite3ResultSetOfSelect(Parse
*pParse
, Select
*pSelect
){
1823 sqlite3
*db
= pParse
->db
;
1826 savedFlags
= db
->flags
;
1827 db
->flags
&= ~SQLITE_FullColNames
;
1828 db
->flags
|= SQLITE_ShortColNames
;
1829 sqlite3SelectPrep(pParse
, pSelect
, 0);
1830 if( pParse
->nErr
) return 0;
1831 while( pSelect
->pPrior
) pSelect
= pSelect
->pPrior
;
1832 db
->flags
= savedFlags
;
1833 pTab
= sqlite3DbMallocZero(db
, sizeof(Table
) );
1837 /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
1839 assert( db
->lookaside
.bDisable
);
1842 pTab
->nRowLogEst
= 200; assert( 200==sqlite3LogEst(1048576) );
1843 sqlite3ColumnsFromExprList(pParse
, pSelect
->pEList
, &pTab
->nCol
, &pTab
->aCol
);
1844 sqlite3SelectAddColumnTypeAndCollation(pParse
, pTab
, pSelect
);
1846 if( db
->mallocFailed
){
1847 sqlite3DeleteTable(db
, pTab
);
1854 ** Get a VDBE for the given parser context. Create a new one if necessary.
1855 ** If an error occurs, return NULL and leave a message in pParse.
1857 Vdbe
*sqlite3GetVdbe(Parse
*pParse
){
1858 if( pParse
->pVdbe
){
1859 return pParse
->pVdbe
;
1861 if( pParse
->pToplevel
==0
1862 && OptimizationEnabled(pParse
->db
,SQLITE_FactorOutConst
)
1864 pParse
->okConstFactor
= 1;
1866 return sqlite3VdbeCreate(pParse
);
1871 ** Compute the iLimit and iOffset fields of the SELECT based on the
1872 ** pLimit expressions. pLimit->pLeft and pLimit->pRight hold the expressions
1873 ** that appear in the original SQL statement after the LIMIT and OFFSET
1874 ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset
1875 ** are the integer memory register numbers for counters used to compute
1876 ** the limit and offset. If there is no limit and/or offset, then
1877 ** iLimit and iOffset are negative.
1879 ** This routine changes the values of iLimit and iOffset only if
1880 ** a limit or offset is defined by pLimit->pLeft and pLimit->pRight. iLimit
1881 ** and iOffset should have been preset to appropriate default values (zero)
1882 ** prior to calling this routine.
1884 ** The iOffset register (if it exists) is initialized to the value
1885 ** of the OFFSET. The iLimit register is initialized to LIMIT. Register
1886 ** iOffset+1 is initialized to LIMIT+OFFSET.
1888 ** Only if pLimit->pLeft!=0 do the limit registers get
1889 ** redefined. The UNION ALL operator uses this property to force
1890 ** the reuse of the same limit and offset registers across multiple
1891 ** SELECT statements.
1893 static void computeLimitRegisters(Parse
*pParse
, Select
*p
, int iBreak
){
1898 Expr
*pLimit
= p
->pLimit
;
1900 if( p
->iLimit
) return;
1903 ** "LIMIT -1" always shows all rows. There is some
1904 ** controversy about what the correct behavior should be.
1905 ** The current implementation interprets "LIMIT 0" to mean
1908 sqlite3ExprCacheClear(pParse
);
1910 assert( pLimit
->op
==TK_LIMIT
);
1911 assert( pLimit
->pLeft
!=0 );
1912 p
->iLimit
= iLimit
= ++pParse
->nMem
;
1913 v
= sqlite3GetVdbe(pParse
);
1915 if( sqlite3ExprIsInteger(pLimit
->pLeft
, &n
) ){
1916 sqlite3VdbeAddOp2(v
, OP_Integer
, n
, iLimit
);
1917 VdbeComment((v
, "LIMIT counter"));
1919 sqlite3VdbeGoto(v
, iBreak
);
1920 }else if( n
>=0 && p
->nSelectRow
>sqlite3LogEst((u64
)n
) ){
1921 p
->nSelectRow
= sqlite3LogEst((u64
)n
);
1922 p
->selFlags
|= SF_FixedLimit
;
1925 sqlite3ExprCode(pParse
, pLimit
->pLeft
, iLimit
);
1926 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, iLimit
); VdbeCoverage(v
);
1927 VdbeComment((v
, "LIMIT counter"));
1928 sqlite3VdbeAddOp2(v
, OP_IfNot
, iLimit
, iBreak
); VdbeCoverage(v
);
1930 if( pLimit
->pRight
){
1931 p
->iOffset
= iOffset
= ++pParse
->nMem
;
1932 pParse
->nMem
++; /* Allocate an extra register for limit+offset */
1933 sqlite3ExprCode(pParse
, pLimit
->pRight
, iOffset
);
1934 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, iOffset
); VdbeCoverage(v
);
1935 VdbeComment((v
, "OFFSET counter"));
1936 sqlite3VdbeAddOp3(v
, OP_OffsetLimit
, iLimit
, iOffset
+1, iOffset
);
1937 VdbeComment((v
, "LIMIT+OFFSET"));
1942 #ifndef SQLITE_OMIT_COMPOUND_SELECT
1944 ** Return the appropriate collating sequence for the iCol-th column of
1945 ** the result set for the compound-select statement "p". Return NULL if
1946 ** the column has no default collating sequence.
1948 ** The collating sequence for the compound select is taken from the
1949 ** left-most term of the select that has a collating sequence.
1951 static CollSeq
*multiSelectCollSeq(Parse
*pParse
, Select
*p
, int iCol
){
1954 pRet
= multiSelectCollSeq(pParse
, p
->pPrior
, iCol
);
1959 /* iCol must be less than p->pEList->nExpr. Otherwise an error would
1960 ** have been thrown during name resolution and we would not have gotten
1962 if( pRet
==0 && ALWAYS(iCol
<p
->pEList
->nExpr
) ){
1963 pRet
= sqlite3ExprCollSeq(pParse
, p
->pEList
->a
[iCol
].pExpr
);
1969 ** The select statement passed as the second parameter is a compound SELECT
1970 ** with an ORDER BY clause. This function allocates and returns a KeyInfo
1971 ** structure suitable for implementing the ORDER BY.
1973 ** Space to hold the KeyInfo structure is obtained from malloc. The calling
1974 ** function is responsible for ensuring that this structure is eventually
1977 static KeyInfo
*multiSelectOrderByKeyInfo(Parse
*pParse
, Select
*p
, int nExtra
){
1978 ExprList
*pOrderBy
= p
->pOrderBy
;
1979 int nOrderBy
= p
->pOrderBy
->nExpr
;
1980 sqlite3
*db
= pParse
->db
;
1981 KeyInfo
*pRet
= sqlite3KeyInfoAlloc(db
, nOrderBy
+nExtra
, 1);
1984 for(i
=0; i
<nOrderBy
; i
++){
1985 struct ExprList_item
*pItem
= &pOrderBy
->a
[i
];
1986 Expr
*pTerm
= pItem
->pExpr
;
1989 if( pTerm
->flags
& EP_Collate
){
1990 pColl
= sqlite3ExprCollSeq(pParse
, pTerm
);
1992 pColl
= multiSelectCollSeq(pParse
, p
, pItem
->u
.x
.iOrderByCol
-1);
1993 if( pColl
==0 ) pColl
= db
->pDfltColl
;
1994 pOrderBy
->a
[i
].pExpr
=
1995 sqlite3ExprAddCollateString(pParse
, pTerm
, pColl
->zName
);
1997 assert( sqlite3KeyInfoIsWriteable(pRet
) );
1998 pRet
->aColl
[i
] = pColl
;
1999 pRet
->aSortOrder
[i
] = pOrderBy
->a
[i
].sortOrder
;
2006 #ifndef SQLITE_OMIT_CTE
2008 ** This routine generates VDBE code to compute the content of a WITH RECURSIVE
2009 ** query of the form:
2011 ** <recursive-table> AS (<setup-query> UNION [ALL] <recursive-query>)
2012 ** \___________/ \_______________/
2016 ** There is exactly one reference to the recursive-table in the FROM clause
2017 ** of recursive-query, marked with the SrcList->a[].fg.isRecursive flag.
2019 ** The setup-query runs once to generate an initial set of rows that go
2020 ** into a Queue table. Rows are extracted from the Queue table one by
2021 ** one. Each row extracted from Queue is output to pDest. Then the single
2022 ** extracted row (now in the iCurrent table) becomes the content of the
2023 ** recursive-table for a recursive-query run. The output of the recursive-query
2024 ** is added back into the Queue table. Then another row is extracted from Queue
2025 ** and the iteration continues until the Queue table is empty.
2027 ** If the compound query operator is UNION then no duplicate rows are ever
2028 ** inserted into the Queue table. The iDistinct table keeps a copy of all rows
2029 ** that have ever been inserted into Queue and causes duplicates to be
2030 ** discarded. If the operator is UNION ALL, then duplicates are allowed.
2032 ** If the query has an ORDER BY, then entries in the Queue table are kept in
2033 ** ORDER BY order and the first entry is extracted for each cycle. Without
2034 ** an ORDER BY, the Queue table is just a FIFO.
2036 ** If a LIMIT clause is provided, then the iteration stops after LIMIT rows
2037 ** have been output to pDest. A LIMIT of zero means to output no rows and a
2038 ** negative LIMIT means to output all rows. If there is also an OFFSET clause
2039 ** with a positive value, then the first OFFSET outputs are discarded rather
2040 ** than being sent to pDest. The LIMIT count does not begin until after OFFSET
2041 ** rows have been skipped.
2043 static void generateWithRecursiveQuery(
2044 Parse
*pParse
, /* Parsing context */
2045 Select
*p
, /* The recursive SELECT to be coded */
2046 SelectDest
*pDest
/* What to do with query results */
2048 SrcList
*pSrc
= p
->pSrc
; /* The FROM clause of the recursive query */
2049 int nCol
= p
->pEList
->nExpr
; /* Number of columns in the recursive table */
2050 Vdbe
*v
= pParse
->pVdbe
; /* The prepared statement under construction */
2051 Select
*pSetup
= p
->pPrior
; /* The setup query */
2052 int addrTop
; /* Top of the loop */
2053 int addrCont
, addrBreak
; /* CONTINUE and BREAK addresses */
2054 int iCurrent
= 0; /* The Current table */
2055 int regCurrent
; /* Register holding Current table */
2056 int iQueue
; /* The Queue table */
2057 int iDistinct
= 0; /* To ensure unique results if UNION */
2058 int eDest
= SRT_Fifo
; /* How to write to Queue */
2059 SelectDest destQueue
; /* SelectDest targetting the Queue table */
2060 int i
; /* Loop counter */
2061 int rc
; /* Result code */
2062 ExprList
*pOrderBy
; /* The ORDER BY clause */
2063 Expr
*pLimit
; /* Saved LIMIT and OFFSET */
2064 int regLimit
, regOffset
; /* Registers used by LIMIT and OFFSET */
2066 /* Obtain authorization to do a recursive query */
2067 if( sqlite3AuthCheck(pParse
, SQLITE_RECURSIVE
, 0, 0, 0) ) return;
2069 /* Process the LIMIT and OFFSET clauses, if they exist */
2070 addrBreak
= sqlite3VdbeMakeLabel(v
);
2071 p
->nSelectRow
= 320; /* 4 billion rows */
2072 computeLimitRegisters(pParse
, p
, addrBreak
);
2074 regLimit
= p
->iLimit
;
2075 regOffset
= p
->iOffset
;
2077 p
->iLimit
= p
->iOffset
= 0;
2078 pOrderBy
= p
->pOrderBy
;
2080 /* Locate the cursor number of the Current table */
2081 for(i
=0; ALWAYS(i
<pSrc
->nSrc
); i
++){
2082 if( pSrc
->a
[i
].fg
.isRecursive
){
2083 iCurrent
= pSrc
->a
[i
].iCursor
;
2088 /* Allocate cursors numbers for Queue and Distinct. The cursor number for
2089 ** the Distinct table must be exactly one greater than Queue in order
2090 ** for the SRT_DistFifo and SRT_DistQueue destinations to work. */
2091 iQueue
= pParse
->nTab
++;
2092 if( p
->op
==TK_UNION
){
2093 eDest
= pOrderBy
? SRT_DistQueue
: SRT_DistFifo
;
2094 iDistinct
= pParse
->nTab
++;
2096 eDest
= pOrderBy
? SRT_Queue
: SRT_Fifo
;
2098 sqlite3SelectDestInit(&destQueue
, eDest
, iQueue
);
2100 /* Allocate cursors for Current, Queue, and Distinct. */
2101 regCurrent
= ++pParse
->nMem
;
2102 sqlite3VdbeAddOp3(v
, OP_OpenPseudo
, iCurrent
, regCurrent
, nCol
);
2104 KeyInfo
*pKeyInfo
= multiSelectOrderByKeyInfo(pParse
, p
, 1);
2105 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
, iQueue
, pOrderBy
->nExpr
+2, 0,
2106 (char*)pKeyInfo
, P4_KEYINFO
);
2107 destQueue
.pOrderBy
= pOrderBy
;
2109 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, iQueue
, nCol
);
2111 VdbeComment((v
, "Queue table"));
2113 p
->addrOpenEphm
[0] = sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, iDistinct
, 0);
2114 p
->selFlags
|= SF_UsesEphemeral
;
2117 /* Detach the ORDER BY clause from the compound SELECT */
2120 /* Store the results of the setup-query in Queue. */
2122 rc
= sqlite3Select(pParse
, pSetup
, &destQueue
);
2124 if( rc
) goto end_of_recursive_query
;
2126 /* Find the next row in the Queue and output that row */
2127 addrTop
= sqlite3VdbeAddOp2(v
, OP_Rewind
, iQueue
, addrBreak
); VdbeCoverage(v
);
2129 /* Transfer the next row in Queue over to Current */
2130 sqlite3VdbeAddOp1(v
, OP_NullRow
, iCurrent
); /* To reset column cache */
2132 sqlite3VdbeAddOp3(v
, OP_Column
, iQueue
, pOrderBy
->nExpr
+1, regCurrent
);
2134 sqlite3VdbeAddOp2(v
, OP_RowData
, iQueue
, regCurrent
);
2136 sqlite3VdbeAddOp1(v
, OP_Delete
, iQueue
);
2138 /* Output the single row in Current */
2139 addrCont
= sqlite3VdbeMakeLabel(v
);
2140 codeOffset(v
, regOffset
, addrCont
);
2141 selectInnerLoop(pParse
, p
, iCurrent
,
2142 0, 0, pDest
, addrCont
, addrBreak
);
2144 sqlite3VdbeAddOp2(v
, OP_DecrJumpZero
, regLimit
, addrBreak
);
2147 sqlite3VdbeResolveLabel(v
, addrCont
);
2149 /* Execute the recursive SELECT taking the single row in Current as
2150 ** the value for the recursive-table. Store the results in the Queue.
2152 if( p
->selFlags
& SF_Aggregate
){
2153 sqlite3ErrorMsg(pParse
, "recursive aggregate queries not supported");
2156 sqlite3Select(pParse
, p
, &destQueue
);
2157 assert( p
->pPrior
==0 );
2161 /* Keep running the loop until the Queue is empty */
2162 sqlite3VdbeGoto(v
, addrTop
);
2163 sqlite3VdbeResolveLabel(v
, addrBreak
);
2165 end_of_recursive_query
:
2166 sqlite3ExprListDelete(pParse
->db
, p
->pOrderBy
);
2167 p
->pOrderBy
= pOrderBy
;
2171 #endif /* SQLITE_OMIT_CTE */
2173 /* Forward references */
2174 static int multiSelectOrderBy(
2175 Parse
*pParse
, /* Parsing context */
2176 Select
*p
, /* The right-most of SELECTs to be coded */
2177 SelectDest
*pDest
/* What to do with query results */
2181 ** Handle the special case of a compound-select that originates from a
2182 ** VALUES clause. By handling this as a special case, we avoid deep
2183 ** recursion, and thus do not need to enforce the SQLITE_LIMIT_COMPOUND_SELECT
2184 ** on a VALUES clause.
2186 ** Because the Select object originates from a VALUES clause:
2187 ** (1) It has no LIMIT or OFFSET
2188 ** (2) All terms are UNION ALL
2189 ** (3) There is no ORDER BY clause
2191 static int multiSelectValues(
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 */
2199 assert( p
->selFlags
& SF_MultiValue
);
2201 assert( p
->selFlags
& SF_Values
);
2202 assert( p
->op
==TK_ALL
|| (p
->op
==TK_SELECT
&& p
->pPrior
==0) );
2203 assert( p
->pLimit
==0 );
2204 assert( p
->pNext
==0 || p
->pEList
->nExpr
==p
->pNext
->pEList
->nExpr
);
2205 if( p
->pPrior
==0 ) break;
2206 assert( p
->pPrior
->pNext
==p
);
2213 rc
= sqlite3Select(pParse
, p
, pDest
);
2216 p
->nSelectRow
= nRow
;
2223 ** This routine is called to process a compound query form from
2224 ** two or more separate queries using UNION, UNION ALL, EXCEPT, or
2227 ** "p" points to the right-most of the two queries. the query on the
2228 ** left is p->pPrior. The left query could also be a compound query
2229 ** in which case this routine will be called recursively.
2231 ** The results of the total query are to be written into a destination
2232 ** of type eDest with parameter iParm.
2234 ** Example 1: Consider a three-way compound SQL statement.
2236 ** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
2238 ** This statement is parsed up as follows:
2242 ** `-----> SELECT b FROM t2
2244 ** `------> SELECT a FROM t1
2246 ** The arrows in the diagram above represent the Select.pPrior pointer.
2247 ** So if this routine is called with p equal to the t3 query, then
2248 ** pPrior will be the t2 query. p->op will be TK_UNION in this case.
2250 ** Notice that because of the way SQLite parses compound SELECTs, the
2251 ** individual selects always group from left to right.
2253 static int multiSelect(
2254 Parse
*pParse
, /* Parsing context */
2255 Select
*p
, /* The right-most of SELECTs to be coded */
2256 SelectDest
*pDest
/* What to do with query results */
2258 int rc
= SQLITE_OK
; /* Success code from a subroutine */
2259 Select
*pPrior
; /* Another SELECT immediately to our left */
2260 Vdbe
*v
; /* Generate code to this VDBE */
2261 SelectDest dest
; /* Alternative data destination */
2262 Select
*pDelete
= 0; /* Chain of simple selects to delete */
2263 sqlite3
*db
; /* Database connection */
2264 #ifndef SQLITE_OMIT_EXPLAIN
2265 int iSub1
= 0; /* EQP id of left-hand query */
2266 int iSub2
= 0; /* EQP id of right-hand query */
2269 /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
2270 ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
2272 assert( p
&& p
->pPrior
); /* Calling function guarantees this much */
2273 assert( (p
->selFlags
& SF_Recursive
)==0 || p
->op
==TK_ALL
|| p
->op
==TK_UNION
);
2277 if( pPrior
->pOrderBy
|| pPrior
->pLimit
){
2278 sqlite3ErrorMsg(pParse
,"%s clause should come after %s not before",
2279 pPrior
->pOrderBy
!=0 ? "ORDER BY" : "LIMIT", selectOpName(p
->op
));
2281 goto multi_select_end
;
2284 v
= sqlite3GetVdbe(pParse
);
2285 assert( v
!=0 ); /* The VDBE already created by calling function */
2287 /* Create the destination temporary table if necessary
2289 if( dest
.eDest
==SRT_EphemTab
){
2290 assert( p
->pEList
);
2291 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, dest
.iSDParm
, p
->pEList
->nExpr
);
2292 dest
.eDest
= SRT_Table
;
2295 /* Special handling for a compound-select that originates as a VALUES clause.
2297 if( p
->selFlags
& SF_MultiValue
){
2298 rc
= multiSelectValues(pParse
, p
, &dest
);
2299 goto multi_select_end
;
2302 /* Make sure all SELECTs in the statement have the same number of elements
2303 ** in their result sets.
2305 assert( p
->pEList
&& pPrior
->pEList
);
2306 assert( p
->pEList
->nExpr
==pPrior
->pEList
->nExpr
);
2308 #ifndef SQLITE_OMIT_CTE
2309 if( p
->selFlags
& SF_Recursive
){
2310 generateWithRecursiveQuery(pParse
, p
, &dest
);
2314 /* Compound SELECTs that have an ORDER BY clause are handled separately.
2317 return multiSelectOrderBy(pParse
, p
, pDest
);
2320 /* Generate code for the left and right SELECT statements.
2326 assert( !pPrior
->pLimit
);
2327 pPrior
->iLimit
= p
->iLimit
;
2328 pPrior
->iOffset
= p
->iOffset
;
2329 pPrior
->pLimit
= p
->pLimit
;
2330 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2331 rc
= sqlite3Select(pParse
, pPrior
, &dest
);
2334 goto multi_select_end
;
2337 p
->iLimit
= pPrior
->iLimit
;
2338 p
->iOffset
= pPrior
->iOffset
;
2340 addr
= sqlite3VdbeAddOp1(v
, OP_IfNot
, p
->iLimit
); VdbeCoverage(v
);
2341 VdbeComment((v
, "Jump ahead if LIMIT reached"));
2343 sqlite3VdbeAddOp3(v
, OP_OffsetLimit
,
2344 p
->iLimit
, p
->iOffset
+1, p
->iOffset
);
2347 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
2348 rc
= sqlite3Select(pParse
, p
, &dest
);
2349 testcase( rc
!=SQLITE_OK
);
2350 pDelete
= p
->pPrior
;
2352 p
->nSelectRow
= sqlite3LogEstAdd(p
->nSelectRow
, pPrior
->nSelectRow
);
2354 && sqlite3ExprIsInteger(pPrior
->pLimit
->pLeft
, &nLimit
)
2355 && nLimit
>0 && p
->nSelectRow
> sqlite3LogEst((u64
)nLimit
)
2357 p
->nSelectRow
= sqlite3LogEst((u64
)nLimit
);
2360 sqlite3VdbeJumpHere(v
, addr
);
2366 int unionTab
; /* Cursor number of the temporary table holding result */
2367 u8 op
= 0; /* One of the SRT_ operations to apply to self */
2368 int priorOp
; /* The SRT_ operation to apply to prior selects */
2369 Expr
*pLimit
; /* Saved values of p->nLimit */
2371 SelectDest uniondest
;
2373 testcase( p
->op
==TK_EXCEPT
);
2374 testcase( p
->op
==TK_UNION
);
2375 priorOp
= SRT_Union
;
2376 if( dest
.eDest
==priorOp
){
2377 /* We can reuse a temporary table generated by a SELECT to our
2380 assert( p
->pLimit
==0 ); /* Not allowed on leftward elements */
2381 unionTab
= dest
.iSDParm
;
2383 /* We will need to create our own temporary table to hold the
2384 ** intermediate results.
2386 unionTab
= pParse
->nTab
++;
2387 assert( p
->pOrderBy
==0 );
2388 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, unionTab
, 0);
2389 assert( p
->addrOpenEphm
[0] == -1 );
2390 p
->addrOpenEphm
[0] = addr
;
2391 findRightmost(p
)->selFlags
|= SF_UsesEphemeral
;
2392 assert( p
->pEList
);
2395 /* Code the SELECT statements to our left
2397 assert( !pPrior
->pOrderBy
);
2398 sqlite3SelectDestInit(&uniondest
, priorOp
, unionTab
);
2399 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2400 rc
= sqlite3Select(pParse
, pPrior
, &uniondest
);
2402 goto multi_select_end
;
2405 /* Code the current SELECT statement
2407 if( p
->op
==TK_EXCEPT
){
2410 assert( p
->op
==TK_UNION
);
2416 uniondest
.eDest
= op
;
2417 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
2418 rc
= sqlite3Select(pParse
, p
, &uniondest
);
2419 testcase( rc
!=SQLITE_OK
);
2420 /* Query flattening in sqlite3Select() might refill p->pOrderBy.
2421 ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
2422 sqlite3ExprListDelete(db
, p
->pOrderBy
);
2423 pDelete
= p
->pPrior
;
2426 if( p
->op
==TK_UNION
){
2427 p
->nSelectRow
= sqlite3LogEstAdd(p
->nSelectRow
, pPrior
->nSelectRow
);
2429 sqlite3ExprDelete(db
, p
->pLimit
);
2434 /* Convert the data in the temporary table into whatever form
2435 ** it is that we currently need.
2437 assert( unionTab
==dest
.iSDParm
|| dest
.eDest
!=priorOp
);
2438 if( dest
.eDest
!=priorOp
){
2439 int iCont
, iBreak
, iStart
;
2440 assert( p
->pEList
);
2441 iBreak
= sqlite3VdbeMakeLabel(v
);
2442 iCont
= sqlite3VdbeMakeLabel(v
);
2443 computeLimitRegisters(pParse
, p
, iBreak
);
2444 sqlite3VdbeAddOp2(v
, OP_Rewind
, unionTab
, iBreak
); VdbeCoverage(v
);
2445 iStart
= sqlite3VdbeCurrentAddr(v
);
2446 selectInnerLoop(pParse
, p
, unionTab
,
2447 0, 0, &dest
, iCont
, iBreak
);
2448 sqlite3VdbeResolveLabel(v
, iCont
);
2449 sqlite3VdbeAddOp2(v
, OP_Next
, unionTab
, iStart
); VdbeCoverage(v
);
2450 sqlite3VdbeResolveLabel(v
, iBreak
);
2451 sqlite3VdbeAddOp2(v
, OP_Close
, unionTab
, 0);
2455 default: assert( p
->op
==TK_INTERSECT
); {
2457 int iCont
, iBreak
, iStart
;
2460 SelectDest intersectdest
;
2463 /* INTERSECT is different from the others since it requires
2464 ** two temporary tables. Hence it has its own case. Begin
2465 ** by allocating the tables we will need.
2467 tab1
= pParse
->nTab
++;
2468 tab2
= pParse
->nTab
++;
2469 assert( p
->pOrderBy
==0 );
2471 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, tab1
, 0);
2472 assert( p
->addrOpenEphm
[0] == -1 );
2473 p
->addrOpenEphm
[0] = addr
;
2474 findRightmost(p
)->selFlags
|= SF_UsesEphemeral
;
2475 assert( p
->pEList
);
2477 /* Code the SELECTs to our left into temporary table "tab1".
2479 sqlite3SelectDestInit(&intersectdest
, SRT_Union
, tab1
);
2480 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2481 rc
= sqlite3Select(pParse
, pPrior
, &intersectdest
);
2483 goto multi_select_end
;
2486 /* Code the current SELECT into temporary table "tab2"
2488 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, tab2
, 0);
2489 assert( p
->addrOpenEphm
[1] == -1 );
2490 p
->addrOpenEphm
[1] = addr
;
2494 intersectdest
.iSDParm
= tab2
;
2495 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
2496 rc
= sqlite3Select(pParse
, p
, &intersectdest
);
2497 testcase( rc
!=SQLITE_OK
);
2498 pDelete
= p
->pPrior
;
2500 if( p
->nSelectRow
>pPrior
->nSelectRow
) p
->nSelectRow
= pPrior
->nSelectRow
;
2501 sqlite3ExprDelete(db
, p
->pLimit
);
2504 /* Generate code to take the intersection of the two temporary
2507 assert( p
->pEList
);
2508 iBreak
= sqlite3VdbeMakeLabel(v
);
2509 iCont
= sqlite3VdbeMakeLabel(v
);
2510 computeLimitRegisters(pParse
, p
, iBreak
);
2511 sqlite3VdbeAddOp2(v
, OP_Rewind
, tab1
, iBreak
); VdbeCoverage(v
);
2512 r1
= sqlite3GetTempReg(pParse
);
2513 iStart
= sqlite3VdbeAddOp2(v
, OP_RowData
, tab1
, r1
);
2514 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, tab2
, iCont
, r1
, 0); VdbeCoverage(v
);
2515 sqlite3ReleaseTempReg(pParse
, r1
);
2516 selectInnerLoop(pParse
, p
, tab1
,
2517 0, 0, &dest
, iCont
, iBreak
);
2518 sqlite3VdbeResolveLabel(v
, iCont
);
2519 sqlite3VdbeAddOp2(v
, OP_Next
, tab1
, iStart
); VdbeCoverage(v
);
2520 sqlite3VdbeResolveLabel(v
, iBreak
);
2521 sqlite3VdbeAddOp2(v
, OP_Close
, tab2
, 0);
2522 sqlite3VdbeAddOp2(v
, OP_Close
, tab1
, 0);
2527 explainComposite(pParse
, p
->op
, iSub1
, iSub2
, p
->op
!=TK_ALL
);
2529 /* Compute collating sequences used by
2530 ** temporary tables needed to implement the compound select.
2531 ** Attach the KeyInfo structure to all temporary tables.
2533 ** This section is run by the right-most SELECT statement only.
2534 ** SELECT statements to the left always skip this part. The right-most
2535 ** SELECT might also skip this part if it has no ORDER BY clause and
2536 ** no temp tables are required.
2538 if( p
->selFlags
& SF_UsesEphemeral
){
2539 int i
; /* Loop counter */
2540 KeyInfo
*pKeyInfo
; /* Collating sequence for the result set */
2541 Select
*pLoop
; /* For looping through SELECT statements */
2542 CollSeq
**apColl
; /* For looping through pKeyInfo->aColl[] */
2543 int nCol
; /* Number of columns in result set */
2545 assert( p
->pNext
==0 );
2546 nCol
= p
->pEList
->nExpr
;
2547 pKeyInfo
= sqlite3KeyInfoAlloc(db
, nCol
, 1);
2549 rc
= SQLITE_NOMEM_BKPT
;
2550 goto multi_select_end
;
2552 for(i
=0, apColl
=pKeyInfo
->aColl
; i
<nCol
; i
++, apColl
++){
2553 *apColl
= multiSelectCollSeq(pParse
, p
, i
);
2555 *apColl
= db
->pDfltColl
;
2559 for(pLoop
=p
; pLoop
; pLoop
=pLoop
->pPrior
){
2561 int addr
= pLoop
->addrOpenEphm
[i
];
2563 /* If [0] is unused then [1] is also unused. So we can
2564 ** always safely abort as soon as the first unused slot is found */
2565 assert( pLoop
->addrOpenEphm
[1]<0 );
2568 sqlite3VdbeChangeP2(v
, addr
, nCol
);
2569 sqlite3VdbeChangeP4(v
, addr
, (char*)sqlite3KeyInfoRef(pKeyInfo
),
2571 pLoop
->addrOpenEphm
[i
] = -1;
2574 sqlite3KeyInfoUnref(pKeyInfo
);
2578 pDest
->iSdst
= dest
.iSdst
;
2579 pDest
->nSdst
= dest
.nSdst
;
2580 sqlite3SelectDelete(db
, pDelete
);
2583 #endif /* SQLITE_OMIT_COMPOUND_SELECT */
2586 ** Error message for when two or more terms of a compound select have different
2587 ** size result sets.
2589 void sqlite3SelectWrongNumTermsError(Parse
*pParse
, Select
*p
){
2590 if( p
->selFlags
& SF_Values
){
2591 sqlite3ErrorMsg(pParse
, "all VALUES must have the same number of terms");
2593 sqlite3ErrorMsg(pParse
, "SELECTs to the left and right of %s"
2594 " do not have the same number of result columns", selectOpName(p
->op
));
2599 ** Code an output subroutine for a coroutine implementation of a
2602 ** The data to be output is contained in pIn->iSdst. There are
2603 ** pIn->nSdst columns to be output. pDest is where the output should
2606 ** regReturn is the number of the register holding the subroutine
2609 ** If regPrev>0 then it is the first register in a vector that
2610 ** records the previous output. mem[regPrev] is a flag that is false
2611 ** if there has been no previous output. If regPrev>0 then code is
2612 ** generated to suppress duplicates. pKeyInfo is used for comparing
2615 ** If the LIMIT found in p->iLimit is reached, jump immediately to
2618 static int generateOutputSubroutine(
2619 Parse
*pParse
, /* Parsing context */
2620 Select
*p
, /* The SELECT statement */
2621 SelectDest
*pIn
, /* Coroutine supplying data */
2622 SelectDest
*pDest
, /* Where to send the data */
2623 int regReturn
, /* The return address register */
2624 int regPrev
, /* Previous result register. No uniqueness if 0 */
2625 KeyInfo
*pKeyInfo
, /* For comparing with previous entry */
2626 int iBreak
/* Jump here if we hit the LIMIT */
2628 Vdbe
*v
= pParse
->pVdbe
;
2632 addr
= sqlite3VdbeCurrentAddr(v
);
2633 iContinue
= sqlite3VdbeMakeLabel(v
);
2635 /* Suppress duplicates for UNION, EXCEPT, and INTERSECT
2639 addr1
= sqlite3VdbeAddOp1(v
, OP_IfNot
, regPrev
); VdbeCoverage(v
);
2640 addr2
= sqlite3VdbeAddOp4(v
, OP_Compare
, pIn
->iSdst
, regPrev
+1, pIn
->nSdst
,
2641 (char*)sqlite3KeyInfoRef(pKeyInfo
), P4_KEYINFO
);
2642 sqlite3VdbeAddOp3(v
, OP_Jump
, addr2
+2, iContinue
, addr2
+2); VdbeCoverage(v
);
2643 sqlite3VdbeJumpHere(v
, addr1
);
2644 sqlite3VdbeAddOp3(v
, OP_Copy
, pIn
->iSdst
, regPrev
+1, pIn
->nSdst
-1);
2645 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, regPrev
);
2647 if( pParse
->db
->mallocFailed
) return 0;
2649 /* Suppress the first OFFSET entries if there is an OFFSET clause
2651 codeOffset(v
, p
->iOffset
, iContinue
);
2653 assert( pDest
->eDest
!=SRT_Exists
);
2654 assert( pDest
->eDest
!=SRT_Table
);
2655 switch( pDest
->eDest
){
2656 /* Store the result as data using a unique key.
2658 case SRT_EphemTab
: {
2659 int r1
= sqlite3GetTempReg(pParse
);
2660 int r2
= sqlite3GetTempReg(pParse
);
2661 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, pIn
->iSdst
, pIn
->nSdst
, r1
);
2662 sqlite3VdbeAddOp2(v
, OP_NewRowid
, pDest
->iSDParm
, r2
);
2663 sqlite3VdbeAddOp3(v
, OP_Insert
, pDest
->iSDParm
, r1
, r2
);
2664 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
2665 sqlite3ReleaseTempReg(pParse
, r2
);
2666 sqlite3ReleaseTempReg(pParse
, r1
);
2670 #ifndef SQLITE_OMIT_SUBQUERY
2671 /* If we are creating a set for an "expr IN (SELECT ...)".
2675 testcase( pIn
->nSdst
>1 );
2676 r1
= sqlite3GetTempReg(pParse
);
2677 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, pIn
->iSdst
, pIn
->nSdst
,
2678 r1
, pDest
->zAffSdst
, pIn
->nSdst
);
2679 sqlite3ExprCacheAffinityChange(pParse
, pIn
->iSdst
, pIn
->nSdst
);
2680 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, pDest
->iSDParm
, r1
,
2681 pIn
->iSdst
, pIn
->nSdst
);
2682 sqlite3ReleaseTempReg(pParse
, r1
);
2686 /* If this is a scalar select that is part of an expression, then
2687 ** store the results in the appropriate memory cell and break out
2688 ** of the scan loop.
2691 assert( pIn
->nSdst
==1 || pParse
->nErr
>0 ); testcase( pIn
->nSdst
!=1 );
2692 sqlite3ExprCodeMove(pParse
, pIn
->iSdst
, pDest
->iSDParm
, 1);
2693 /* The LIMIT clause will jump out of the loop for us */
2696 #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
2698 /* The results are stored in a sequence of registers
2699 ** starting at pDest->iSdst. Then the co-routine yields.
2701 case SRT_Coroutine
: {
2702 if( pDest
->iSdst
==0 ){
2703 pDest
->iSdst
= sqlite3GetTempRange(pParse
, pIn
->nSdst
);
2704 pDest
->nSdst
= pIn
->nSdst
;
2706 sqlite3ExprCodeMove(pParse
, pIn
->iSdst
, pDest
->iSdst
, pIn
->nSdst
);
2707 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iSDParm
);
2711 /* If none of the above, then the result destination must be
2712 ** SRT_Output. This routine is never called with any other
2713 ** destination other than the ones handled above or SRT_Output.
2715 ** For SRT_Output, results are stored in a sequence of registers.
2716 ** Then the OP_ResultRow opcode is used to cause sqlite3_step() to
2717 ** return the next row of result.
2720 assert( pDest
->eDest
==SRT_Output
);
2721 sqlite3VdbeAddOp2(v
, OP_ResultRow
, pIn
->iSdst
, pIn
->nSdst
);
2722 sqlite3ExprCacheAffinityChange(pParse
, pIn
->iSdst
, pIn
->nSdst
);
2727 /* Jump to the end of the loop if the LIMIT is reached.
2730 sqlite3VdbeAddOp2(v
, OP_DecrJumpZero
, p
->iLimit
, iBreak
); VdbeCoverage(v
);
2733 /* Generate the subroutine return
2735 sqlite3VdbeResolveLabel(v
, iContinue
);
2736 sqlite3VdbeAddOp1(v
, OP_Return
, regReturn
);
2742 ** Alternative compound select code generator for cases when there
2743 ** is an ORDER BY clause.
2745 ** We assume a query of the following form:
2747 ** <selectA> <operator> <selectB> ORDER BY <orderbylist>
2749 ** <operator> is one of UNION ALL, UNION, EXCEPT, or INTERSECT. The idea
2750 ** is to code both <selectA> and <selectB> with the ORDER BY clause as
2751 ** co-routines. Then run the co-routines in parallel and merge the results
2752 ** into the output. In addition to the two coroutines (called selectA and
2753 ** selectB) there are 7 subroutines:
2755 ** outA: Move the output of the selectA coroutine into the output
2756 ** of the compound query.
2758 ** outB: Move the output of the selectB coroutine into the output
2759 ** of the compound query. (Only generated for UNION and
2760 ** UNION ALL. EXCEPT and INSERTSECT never output a row that
2761 ** appears only in B.)
2763 ** AltB: Called when there is data from both coroutines and A<B.
2765 ** AeqB: Called when there is data from both coroutines and A==B.
2767 ** AgtB: Called when there is data from both coroutines and A>B.
2769 ** EofA: Called when data is exhausted from selectA.
2771 ** EofB: Called when data is exhausted from selectB.
2773 ** The implementation of the latter five subroutines depend on which
2774 ** <operator> is used:
2777 ** UNION ALL UNION EXCEPT INTERSECT
2778 ** ------------- ----------------- -------------- -----------------
2779 ** AltB: outA, nextA outA, nextA outA, nextA nextA
2781 ** AeqB: outA, nextA nextA nextA outA, nextA
2783 ** AgtB: outB, nextB outB, nextB nextB nextB
2785 ** EofA: outB, nextB outB, nextB halt halt
2787 ** EofB: outA, nextA outA, nextA outA, nextA halt
2789 ** In the AltB, AeqB, and AgtB subroutines, an EOF on A following nextA
2790 ** causes an immediate jump to EofA and an EOF on B following nextB causes
2791 ** an immediate jump to EofB. Within EofA and EofB, and EOF on entry or
2792 ** following nextX causes a jump to the end of the select processing.
2794 ** Duplicate removal in the UNION, EXCEPT, and INTERSECT cases is handled
2795 ** within the output subroutine. The regPrev register set holds the previously
2796 ** output value. A comparison is made against this value and the output
2797 ** is skipped if the next results would be the same as the previous.
2799 ** The implementation plan is to implement the two coroutines and seven
2800 ** subroutines first, then put the control logic at the bottom. Like this:
2803 ** coA: coroutine for left query (A)
2804 ** coB: coroutine for right query (B)
2805 ** outA: output one row of A
2806 ** outB: output one row of B (UNION and UNION ALL only)
2812 ** Init: initialize coroutine registers
2814 ** if eof(A) goto EofA
2816 ** if eof(B) goto EofB
2817 ** Cmpr: Compare A, B
2818 ** Jump AltB, AeqB, AgtB
2821 ** We call AltB, AeqB, AgtB, EofA, and EofB "subroutines" but they are not
2822 ** actually called using Gosub and they do not Return. EofA and EofB loop
2823 ** until all data is exhausted then jump to the "end" labe. AltB, AeqB,
2824 ** and AgtB jump to either L2 or to one of EofA or EofB.
2826 #ifndef SQLITE_OMIT_COMPOUND_SELECT
2827 static int multiSelectOrderBy(
2828 Parse
*pParse
, /* Parsing context */
2829 Select
*p
, /* The right-most of SELECTs to be coded */
2830 SelectDest
*pDest
/* What to do with query results */
2832 int i
, j
; /* Loop counters */
2833 Select
*pPrior
; /* Another SELECT immediately to our left */
2834 Vdbe
*v
; /* Generate code to this VDBE */
2835 SelectDest destA
; /* Destination for coroutine A */
2836 SelectDest destB
; /* Destination for coroutine B */
2837 int regAddrA
; /* Address register for select-A coroutine */
2838 int regAddrB
; /* Address register for select-B coroutine */
2839 int addrSelectA
; /* Address of the select-A coroutine */
2840 int addrSelectB
; /* Address of the select-B coroutine */
2841 int regOutA
; /* Address register for the output-A subroutine */
2842 int regOutB
; /* Address register for the output-B subroutine */
2843 int addrOutA
; /* Address of the output-A subroutine */
2844 int addrOutB
= 0; /* Address of the output-B subroutine */
2845 int addrEofA
; /* Address of the select-A-exhausted subroutine */
2846 int addrEofA_noB
; /* Alternate addrEofA if B is uninitialized */
2847 int addrEofB
; /* Address of the select-B-exhausted subroutine */
2848 int addrAltB
; /* Address of the A<B subroutine */
2849 int addrAeqB
; /* Address of the A==B subroutine */
2850 int addrAgtB
; /* Address of the A>B subroutine */
2851 int regLimitA
; /* Limit register for select-A */
2852 int regLimitB
; /* Limit register for select-A */
2853 int regPrev
; /* A range of registers to hold previous output */
2854 int savedLimit
; /* Saved value of p->iLimit */
2855 int savedOffset
; /* Saved value of p->iOffset */
2856 int labelCmpr
; /* Label for the start of the merge algorithm */
2857 int labelEnd
; /* Label for the end of the overall SELECT stmt */
2858 int addr1
; /* Jump instructions that get retargetted */
2859 int op
; /* One of TK_ALL, TK_UNION, TK_EXCEPT, TK_INTERSECT */
2860 KeyInfo
*pKeyDup
= 0; /* Comparison information for duplicate removal */
2861 KeyInfo
*pKeyMerge
; /* Comparison information for merging rows */
2862 sqlite3
*db
; /* Database connection */
2863 ExprList
*pOrderBy
; /* The ORDER BY clause */
2864 int nOrderBy
; /* Number of terms in the ORDER BY clause */
2865 int *aPermute
; /* Mapping from ORDER BY terms to result set columns */
2866 #ifndef SQLITE_OMIT_EXPLAIN
2867 int iSub1
; /* EQP id of left-hand query */
2868 int iSub2
; /* EQP id of right-hand query */
2871 assert( p
->pOrderBy
!=0 );
2872 assert( pKeyDup
==0 ); /* "Managed" code needs this. Ticket #3382. */
2875 assert( v
!=0 ); /* Already thrown the error if VDBE alloc failed */
2876 labelEnd
= sqlite3VdbeMakeLabel(v
);
2877 labelCmpr
= sqlite3VdbeMakeLabel(v
);
2880 /* Patch up the ORDER BY clause
2884 assert( pPrior
->pOrderBy
==0 );
2885 pOrderBy
= p
->pOrderBy
;
2887 nOrderBy
= pOrderBy
->nExpr
;
2889 /* For operators other than UNION ALL we have to make sure that
2890 ** the ORDER BY clause covers every term of the result set. Add
2891 ** terms to the ORDER BY clause as necessary.
2894 for(i
=1; db
->mallocFailed
==0 && i
<=p
->pEList
->nExpr
; i
++){
2895 struct ExprList_item
*pItem
;
2896 for(j
=0, pItem
=pOrderBy
->a
; j
<nOrderBy
; j
++, pItem
++){
2897 assert( pItem
->u
.x
.iOrderByCol
>0 );
2898 if( pItem
->u
.x
.iOrderByCol
==i
) break;
2901 Expr
*pNew
= sqlite3Expr(db
, TK_INTEGER
, 0);
2902 if( pNew
==0 ) return SQLITE_NOMEM_BKPT
;
2903 pNew
->flags
|= EP_IntValue
;
2905 p
->pOrderBy
= pOrderBy
= sqlite3ExprListAppend(pParse
, pOrderBy
, pNew
);
2906 if( pOrderBy
) pOrderBy
->a
[nOrderBy
++].u
.x
.iOrderByCol
= (u16
)i
;
2911 /* Compute the comparison permutation and keyinfo that is used with
2912 ** the permutation used to determine if the next
2913 ** row of results comes from selectA or selectB. Also add explicit
2914 ** collations to the ORDER BY clause terms so that when the subqueries
2915 ** to the right and the left are evaluated, they use the correct
2918 aPermute
= sqlite3DbMallocRawNN(db
, sizeof(int)*(nOrderBy
+ 1));
2920 struct ExprList_item
*pItem
;
2921 aPermute
[0] = nOrderBy
;
2922 for(i
=1, pItem
=pOrderBy
->a
; i
<=nOrderBy
; i
++, pItem
++){
2923 assert( pItem
->u
.x
.iOrderByCol
>0 );
2924 assert( pItem
->u
.x
.iOrderByCol
<=p
->pEList
->nExpr
);
2925 aPermute
[i
] = pItem
->u
.x
.iOrderByCol
- 1;
2927 pKeyMerge
= multiSelectOrderByKeyInfo(pParse
, p
, 1);
2932 /* Reattach the ORDER BY clause to the query.
2934 p
->pOrderBy
= pOrderBy
;
2935 pPrior
->pOrderBy
= sqlite3ExprListDup(pParse
->db
, pOrderBy
, 0);
2937 /* Allocate a range of temporary registers and the KeyInfo needed
2938 ** for the logic that removes duplicate result rows when the
2939 ** operator is UNION, EXCEPT, or INTERSECT (but not UNION ALL).
2944 int nExpr
= p
->pEList
->nExpr
;
2945 assert( nOrderBy
>=nExpr
|| db
->mallocFailed
);
2946 regPrev
= pParse
->nMem
+1;
2947 pParse
->nMem
+= nExpr
+1;
2948 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regPrev
);
2949 pKeyDup
= sqlite3KeyInfoAlloc(db
, nExpr
, 1);
2951 assert( sqlite3KeyInfoIsWriteable(pKeyDup
) );
2952 for(i
=0; i
<nExpr
; i
++){
2953 pKeyDup
->aColl
[i
] = multiSelectCollSeq(pParse
, p
, i
);
2954 pKeyDup
->aSortOrder
[i
] = 0;
2959 /* Separate the left and the right query from one another
2963 sqlite3ResolveOrderGroupBy(pParse
, p
, p
->pOrderBy
, "ORDER");
2964 if( pPrior
->pPrior
==0 ){
2965 sqlite3ResolveOrderGroupBy(pParse
, pPrior
, pPrior
->pOrderBy
, "ORDER");
2968 /* Compute the limit registers */
2969 computeLimitRegisters(pParse
, p
, labelEnd
);
2970 if( p
->iLimit
&& op
==TK_ALL
){
2971 regLimitA
= ++pParse
->nMem
;
2972 regLimitB
= ++pParse
->nMem
;
2973 sqlite3VdbeAddOp2(v
, OP_Copy
, p
->iOffset
? p
->iOffset
+1 : p
->iLimit
,
2975 sqlite3VdbeAddOp2(v
, OP_Copy
, regLimitA
, regLimitB
);
2977 regLimitA
= regLimitB
= 0;
2979 sqlite3ExprDelete(db
, p
->pLimit
);
2982 regAddrA
= ++pParse
->nMem
;
2983 regAddrB
= ++pParse
->nMem
;
2984 regOutA
= ++pParse
->nMem
;
2985 regOutB
= ++pParse
->nMem
;
2986 sqlite3SelectDestInit(&destA
, SRT_Coroutine
, regAddrA
);
2987 sqlite3SelectDestInit(&destB
, SRT_Coroutine
, regAddrB
);
2989 /* Generate a coroutine to evaluate the SELECT statement to the
2990 ** left of the compound operator - the "A" select.
2992 addrSelectA
= sqlite3VdbeCurrentAddr(v
) + 1;
2993 addr1
= sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regAddrA
, 0, addrSelectA
);
2994 VdbeComment((v
, "left SELECT"));
2995 pPrior
->iLimit
= regLimitA
;
2996 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2997 sqlite3Select(pParse
, pPrior
, &destA
);
2998 sqlite3VdbeEndCoroutine(v
, regAddrA
);
2999 sqlite3VdbeJumpHere(v
, addr1
);
3001 /* Generate a coroutine to evaluate the SELECT statement on
3002 ** the right - the "B" select
3004 addrSelectB
= sqlite3VdbeCurrentAddr(v
) + 1;
3005 addr1
= sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regAddrB
, 0, addrSelectB
);
3006 VdbeComment((v
, "right SELECT"));
3007 savedLimit
= p
->iLimit
;
3008 savedOffset
= p
->iOffset
;
3009 p
->iLimit
= regLimitB
;
3011 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
3012 sqlite3Select(pParse
, p
, &destB
);
3013 p
->iLimit
= savedLimit
;
3014 p
->iOffset
= savedOffset
;
3015 sqlite3VdbeEndCoroutine(v
, regAddrB
);
3017 /* Generate a subroutine that outputs the current row of the A
3018 ** select as the next output row of the compound select.
3020 VdbeNoopComment((v
, "Output routine for A"));
3021 addrOutA
= generateOutputSubroutine(pParse
,
3022 p
, &destA
, pDest
, regOutA
,
3023 regPrev
, pKeyDup
, labelEnd
);
3025 /* Generate a subroutine that outputs the current row of the B
3026 ** select as the next output row of the compound select.
3028 if( op
==TK_ALL
|| op
==TK_UNION
){
3029 VdbeNoopComment((v
, "Output routine for B"));
3030 addrOutB
= generateOutputSubroutine(pParse
,
3031 p
, &destB
, pDest
, regOutB
,
3032 regPrev
, pKeyDup
, labelEnd
);
3034 sqlite3KeyInfoUnref(pKeyDup
);
3036 /* Generate a subroutine to run when the results from select A
3037 ** are exhausted and only data in select B remains.
3039 if( op
==TK_EXCEPT
|| op
==TK_INTERSECT
){
3040 addrEofA_noB
= addrEofA
= labelEnd
;
3042 VdbeNoopComment((v
, "eof-A subroutine"));
3043 addrEofA
= sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutB
, addrOutB
);
3044 addrEofA_noB
= sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrB
, labelEnd
);
3046 sqlite3VdbeGoto(v
, addrEofA
);
3047 p
->nSelectRow
= sqlite3LogEstAdd(p
->nSelectRow
, pPrior
->nSelectRow
);
3050 /* Generate a subroutine to run when the results from select B
3051 ** are exhausted and only data in select A remains.
3053 if( op
==TK_INTERSECT
){
3054 addrEofB
= addrEofA
;
3055 if( p
->nSelectRow
> pPrior
->nSelectRow
) p
->nSelectRow
= pPrior
->nSelectRow
;
3057 VdbeNoopComment((v
, "eof-B subroutine"));
3058 addrEofB
= sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutA
, addrOutA
);
3059 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrA
, labelEnd
); VdbeCoverage(v
);
3060 sqlite3VdbeGoto(v
, addrEofB
);
3063 /* Generate code to handle the case of A<B
3065 VdbeNoopComment((v
, "A-lt-B subroutine"));
3066 addrAltB
= sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutA
, addrOutA
);
3067 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrA
, addrEofA
); VdbeCoverage(v
);
3068 sqlite3VdbeGoto(v
, labelCmpr
);
3070 /* Generate code to handle the case of A==B
3073 addrAeqB
= addrAltB
;
3074 }else if( op
==TK_INTERSECT
){
3075 addrAeqB
= addrAltB
;
3078 VdbeNoopComment((v
, "A-eq-B subroutine"));
3080 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrA
, addrEofA
); VdbeCoverage(v
);
3081 sqlite3VdbeGoto(v
, labelCmpr
);
3084 /* Generate code to handle the case of A>B
3086 VdbeNoopComment((v
, "A-gt-B subroutine"));
3087 addrAgtB
= sqlite3VdbeCurrentAddr(v
);
3088 if( op
==TK_ALL
|| op
==TK_UNION
){
3089 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutB
, addrOutB
);
3091 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrB
, addrEofB
); VdbeCoverage(v
);
3092 sqlite3VdbeGoto(v
, labelCmpr
);
3094 /* This code runs once to initialize everything.
3096 sqlite3VdbeJumpHere(v
, addr1
);
3097 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrA
, addrEofA_noB
); VdbeCoverage(v
);
3098 sqlite3VdbeAddOp2(v
, OP_Yield
, regAddrB
, addrEofB
); VdbeCoverage(v
);
3100 /* Implement the main merge loop
3102 sqlite3VdbeResolveLabel(v
, labelCmpr
);
3103 sqlite3VdbeAddOp4(v
, OP_Permutation
, 0, 0, 0, (char*)aPermute
, P4_INTARRAY
);
3104 sqlite3VdbeAddOp4(v
, OP_Compare
, destA
.iSdst
, destB
.iSdst
, nOrderBy
,
3105 (char*)pKeyMerge
, P4_KEYINFO
);
3106 sqlite3VdbeChangeP5(v
, OPFLAG_PERMUTE
);
3107 sqlite3VdbeAddOp3(v
, OP_Jump
, addrAltB
, addrAeqB
, addrAgtB
); VdbeCoverage(v
);
3109 /* Jump to the this point in order to terminate the query.
3111 sqlite3VdbeResolveLabel(v
, labelEnd
);
3113 /* Reassembly the compound query so that it will be freed correctly
3114 ** by the calling function */
3116 sqlite3SelectDelete(db
, p
->pPrior
);
3121 /*** TBD: Insert subroutine calls to close cursors on incomplete
3122 **** subqueries ****/
3123 explainComposite(pParse
, p
->op
, iSub1
, iSub2
, 0);
3124 return pParse
->nErr
!=0;
3128 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
3130 /* An instance of the SubstContext object describes an substitution edit
3131 ** to be performed on a parse tree.
3133 ** All references to columns in table iTable are to be replaced by corresponding
3134 ** expressions in pEList.
3136 typedef struct SubstContext
{
3137 Parse
*pParse
; /* The parsing context */
3138 int iTable
; /* Replace references to this table */
3139 int iNewTable
; /* New table number */
3140 int isLeftJoin
; /* Add TK_IF_NULL_ROW opcodes on each replacement */
3141 ExprList
*pEList
; /* Replacement expressions */
3144 /* Forward Declarations */
3145 static void substExprList(SubstContext
*, ExprList
*);
3146 static void substSelect(SubstContext
*, Select
*, int);
3149 ** Scan through the expression pExpr. Replace every reference to
3150 ** a column in table number iTable with a copy of the iColumn-th
3151 ** entry in pEList. (But leave references to the ROWID column
3154 ** This routine is part of the flattening procedure. A subquery
3155 ** whose result set is defined by pEList appears as entry in the
3156 ** FROM clause of a SELECT such that the VDBE cursor assigned to that
3157 ** FORM clause entry is iTable. This routine makes the necessary
3158 ** changes to pExpr so that it refers directly to the source table
3159 ** of the subquery rather the result set of the subquery.
3161 static Expr
*substExpr(
3162 SubstContext
*pSubst
, /* Description of the substitution */
3163 Expr
*pExpr
/* Expr in which substitution occurs */
3165 if( pExpr
==0 ) return 0;
3166 if( ExprHasProperty(pExpr
, EP_FromJoin
)
3167 && pExpr
->iRightJoinTable
==pSubst
->iTable
3169 pExpr
->iRightJoinTable
= pSubst
->iNewTable
;
3171 if( pExpr
->op
==TK_COLUMN
&& pExpr
->iTable
==pSubst
->iTable
){
3172 if( pExpr
->iColumn
<0 ){
3173 pExpr
->op
= TK_NULL
;
3176 Expr
*pCopy
= pSubst
->pEList
->a
[pExpr
->iColumn
].pExpr
;
3178 assert( pSubst
->pEList
!=0 && pExpr
->iColumn
<pSubst
->pEList
->nExpr
);
3179 assert( pExpr
->pLeft
==0 && pExpr
->pRight
==0 );
3180 if( sqlite3ExprIsVector(pCopy
) ){
3181 sqlite3VectorErrorMsg(pSubst
->pParse
, pCopy
);
3183 sqlite3
*db
= pSubst
->pParse
->db
;
3184 if( pSubst
->isLeftJoin
&& pCopy
->op
!=TK_COLUMN
){
3185 memset(&ifNullRow
, 0, sizeof(ifNullRow
));
3186 ifNullRow
.op
= TK_IF_NULL_ROW
;
3187 ifNullRow
.pLeft
= pCopy
;
3188 ifNullRow
.iTable
= pSubst
->iNewTable
;
3191 pNew
= sqlite3ExprDup(db
, pCopy
, 0);
3192 if( pNew
&& pSubst
->isLeftJoin
){
3193 ExprSetProperty(pNew
, EP_CanBeNull
);
3195 if( pNew
&& ExprHasProperty(pExpr
,EP_FromJoin
) ){
3196 pNew
->iRightJoinTable
= pExpr
->iRightJoinTable
;
3197 ExprSetProperty(pNew
, EP_FromJoin
);
3199 sqlite3ExprDelete(db
, pExpr
);
3204 if( pExpr
->op
==TK_IF_NULL_ROW
&& pExpr
->iTable
==pSubst
->iTable
){
3205 pExpr
->iTable
= pSubst
->iNewTable
;
3207 pExpr
->pLeft
= substExpr(pSubst
, pExpr
->pLeft
);
3208 pExpr
->pRight
= substExpr(pSubst
, pExpr
->pRight
);
3209 if( ExprHasProperty(pExpr
, EP_xIsSelect
) ){
3210 substSelect(pSubst
, pExpr
->x
.pSelect
, 1);
3212 substExprList(pSubst
, pExpr
->x
.pList
);
3217 static void substExprList(
3218 SubstContext
*pSubst
, /* Description of the substitution */
3219 ExprList
*pList
/* List to scan and in which to make substitutes */
3222 if( pList
==0 ) return;
3223 for(i
=0; i
<pList
->nExpr
; i
++){
3224 pList
->a
[i
].pExpr
= substExpr(pSubst
, pList
->a
[i
].pExpr
);
3227 static void substSelect(
3228 SubstContext
*pSubst
, /* Description of the substitution */
3229 Select
*p
, /* SELECT statement in which to make substitutions */
3230 int doPrior
/* Do substitutes on p->pPrior too */
3233 struct SrcList_item
*pItem
;
3237 substExprList(pSubst
, p
->pEList
);
3238 substExprList(pSubst
, p
->pGroupBy
);
3239 substExprList(pSubst
, p
->pOrderBy
);
3240 p
->pHaving
= substExpr(pSubst
, p
->pHaving
);
3241 p
->pWhere
= substExpr(pSubst
, p
->pWhere
);
3244 for(i
=pSrc
->nSrc
, pItem
=pSrc
->a
; i
>0; i
--, pItem
++){
3245 substSelect(pSubst
, pItem
->pSelect
, 1);
3246 if( pItem
->fg
.isTabFunc
){
3247 substExprList(pSubst
, pItem
->u1
.pFuncArg
);
3250 }while( doPrior
&& (p
= p
->pPrior
)!=0 );
3252 #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
3254 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
3256 ** This routine attempts to flatten subqueries as a performance optimization.
3257 ** This routine returns 1 if it makes changes and 0 if no flattening occurs.
3259 ** To understand the concept of flattening, consider the following
3262 ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
3264 ** The default way of implementing this query is to execute the
3265 ** subquery first and store the results in a temporary table, then
3266 ** run the outer query on that temporary table. This requires two
3267 ** passes over the data. Furthermore, because the temporary table
3268 ** has no indices, the WHERE clause on the outer query cannot be
3271 ** This routine attempts to rewrite queries such as the above into
3272 ** a single flat select, like this:
3274 ** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
3276 ** The code generated for this simplification gives the same result
3277 ** but only has to scan the data once. And because indices might
3278 ** exist on the table t1, a complete scan of the data might be
3281 ** Flattening is subject to the following constraints:
3283 ** (**) We no longer attempt to flatten aggregate subqueries. Was:
3284 ** The subquery and the outer query cannot both be aggregates.
3286 ** (**) We no longer attempt to flatten aggregate subqueries. Was:
3287 ** (2) If the subquery is an aggregate then
3288 ** (2a) the outer query must not be a join and
3289 ** (2b) the outer query must not use subqueries
3290 ** other than the one FROM-clause subquery that is a candidate
3291 ** for flattening. (This is due to ticket [2f7170d73bf9abf80]
3292 ** from 2015-02-09.)
3294 ** (3) If the subquery is the right operand of a LEFT JOIN then
3295 ** (3a) the subquery may not be a join and
3296 ** (3b) the FROM clause of the subquery may not contain a virtual
3298 ** (3c) the outer query may not be an aggregate.
3300 ** (4) The subquery can not be DISTINCT.
3302 ** (**) At one point restrictions (4) and (5) defined a subset of DISTINCT
3303 ** sub-queries that were excluded from this optimization. Restriction
3304 ** (4) has since been expanded to exclude all DISTINCT subqueries.
3306 ** (**) We no longer attempt to flatten aggregate subqueries. Was:
3307 ** If the subquery is aggregate, the outer query may not be DISTINCT.
3309 ** (7) The subquery must have a FROM clause. TODO: For subqueries without
3310 ** A FROM clause, consider adding a FROM clause with the special
3311 ** table sqlite_once that consists of a single row containing a
3314 ** (8) If the subquery uses LIMIT then the outer query may not be a join.
3316 ** (9) If the subquery uses LIMIT then the outer query may not be aggregate.
3318 ** (**) Restriction (10) was removed from the code on 2005-02-05 but we
3319 ** accidently carried the comment forward until 2014-09-15. Original
3320 ** constraint: "If the subquery is aggregate then the outer query
3321 ** may not use LIMIT."
3323 ** (11) The subquery and the outer query may not both have ORDER BY clauses.
3325 ** (**) Not implemented. Subsumed into restriction (3). Was previously
3326 ** a separate restriction deriving from ticket #350.
3328 ** (13) The subquery and outer query may not both use LIMIT.
3330 ** (14) The subquery may not use OFFSET.
3332 ** (15) If the outer query is part of a compound select, then the
3333 ** subquery may not use LIMIT.
3334 ** (See ticket #2339 and ticket [02a8e81d44]).
3336 ** (16) If the outer query is aggregate, then the subquery may not
3337 ** use ORDER BY. (Ticket #2942) This used to not matter
3338 ** until we introduced the group_concat() function.
3340 ** (17) If the subquery is a compound select, then
3341 ** (17a) all compound operators must be a UNION ALL, and
3342 ** (17b) no terms within the subquery compound may be aggregate
3344 ** (17c) every term within the subquery compound must have a FROM clause
3345 ** (17d) the outer query may not be
3346 ** (17d1) aggregate, or
3347 ** (17d2) DISTINCT, or
3350 ** The parent and sub-query may contain WHERE clauses. Subject to
3351 ** rules (11), (13) and (14), they may also contain ORDER BY,
3352 ** LIMIT and OFFSET clauses. The subquery cannot use any compound
3353 ** operator other than UNION ALL because all the other compound
3354 ** operators have an implied DISTINCT which is disallowed by
3357 ** Also, each component of the sub-query must return the same number
3358 ** of result columns. This is actually a requirement for any compound
3359 ** SELECT statement, but all the code here does is make sure that no
3360 ** such (illegal) sub-query is flattened. The caller will detect the
3361 ** syntax error and return a detailed message.
3363 ** (18) If the sub-query is a compound select, then all terms of the
3364 ** ORDER BY clause of the parent must be simple references to
3365 ** columns of the sub-query.
3367 ** (19) If the subquery uses LIMIT then the outer query may not
3368 ** have a WHERE clause.
3370 ** (20) If the sub-query is a compound select, then it must not use
3371 ** an ORDER BY clause. Ticket #3773. We could relax this constraint
3372 ** somewhat by saying that the terms of the ORDER BY clause must
3373 ** appear as unmodified result columns in the outer query. But we
3374 ** have other optimizations in mind to deal with that case.
3376 ** (21) If the subquery uses LIMIT then the outer query may not be
3377 ** DISTINCT. (See ticket [752e1646fc]).
3379 ** (22) The subquery may not be a recursive CTE.
3381 ** (**) Subsumed into restriction (17d3). Was: If the outer query is
3382 ** a recursive CTE, then the sub-query may not be a compound query.
3383 ** This restriction is because transforming the
3384 ** parent to a compound query confuses the code that handles
3385 ** recursive queries in multiSelect().
3387 ** (**) We no longer attempt to flatten aggregate subqueries. Was:
3388 ** The subquery may not be an aggregate that uses the built-in min() or
3389 ** or max() functions. (Without this restriction, a query like:
3390 ** "SELECT x FROM (SELECT max(y), x FROM t1)" would not necessarily
3391 ** return the value X for which Y was maximal.)
3394 ** In this routine, the "p" parameter is a pointer to the outer query.
3395 ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
3398 ** If flattening is not attempted, this routine is a no-op and returns 0.
3399 ** If flattening is attempted this routine returns 1.
3401 ** All of the expression analysis must occur on both the outer query and
3402 ** the subquery before this routine runs.
3404 static int flattenSubquery(
3405 Parse
*pParse
, /* Parsing context */
3406 Select
*p
, /* The parent or outer SELECT statement */
3407 int iFrom
, /* Index in p->pSrc->a[] of the inner subquery */
3408 int isAgg
/* True if outer SELECT uses aggregate functions */
3410 const char *zSavedAuthContext
= pParse
->zAuthContext
;
3411 Select
*pParent
; /* Current UNION ALL term of the other query */
3412 Select
*pSub
; /* The inner query or "subquery" */
3413 Select
*pSub1
; /* Pointer to the rightmost select in sub-query */
3414 SrcList
*pSrc
; /* The FROM clause of the outer query */
3415 SrcList
*pSubSrc
; /* The FROM clause of the subquery */
3416 int iParent
; /* VDBE cursor number of the pSub result set temp table */
3417 int iNewParent
= -1;/* Replacement table for iParent */
3418 int isLeftJoin
= 0; /* True if pSub is the right side of a LEFT JOIN */
3419 int i
; /* Loop counter */
3420 Expr
*pWhere
; /* The WHERE clause */
3421 struct SrcList_item
*pSubitem
; /* The subquery */
3422 sqlite3
*db
= pParse
->db
;
3424 /* Check to see if flattening is permitted. Return 0 if not.
3427 assert( p
->pPrior
==0 );
3428 if( OptimizationDisabled(db
, SQLITE_QueryFlattener
) ) return 0;
3430 assert( pSrc
&& iFrom
>=0 && iFrom
<pSrc
->nSrc
);
3431 pSubitem
= &pSrc
->a
[iFrom
];
3432 iParent
= pSubitem
->iCursor
;
3433 pSub
= pSubitem
->pSelect
;
3436 pSubSrc
= pSub
->pSrc
;
3438 /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
3439 ** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET
3440 ** because they could be computed at compile-time. But when LIMIT and OFFSET
3441 ** became arbitrary expressions, we were forced to add restrictions (13)
3443 if( pSub
->pLimit
&& p
->pLimit
) return 0; /* Restriction (13) */
3444 if( pSub
->pLimit
&& pSub
->pLimit
->pRight
) return 0; /* Restriction (14) */
3445 if( (p
->selFlags
& SF_Compound
)!=0 && pSub
->pLimit
){
3446 return 0; /* Restriction (15) */
3448 if( pSubSrc
->nSrc
==0 ) return 0; /* Restriction (7) */
3449 if( pSub
->selFlags
& SF_Distinct
) return 0; /* Restriction (4) */
3450 if( pSub
->pLimit
&& (pSrc
->nSrc
>1 || isAgg
) ){
3451 return 0; /* Restrictions (8)(9) */
3453 if( p
->pOrderBy
&& pSub
->pOrderBy
){
3454 return 0; /* Restriction (11) */
3456 if( isAgg
&& pSub
->pOrderBy
) return 0; /* Restriction (16) */
3457 if( pSub
->pLimit
&& p
->pWhere
) return 0; /* Restriction (19) */
3458 if( pSub
->pLimit
&& (p
->selFlags
& SF_Distinct
)!=0 ){
3459 return 0; /* Restriction (21) */
3461 if( pSub
->selFlags
& (SF_Recursive
) ){
3462 return 0; /* Restrictions (22) */
3466 ** If the subquery is the right operand of a LEFT JOIN, then the
3467 ** subquery may not be a join itself (3a). Example of why this is not
3470 ** t1 LEFT OUTER JOIN (t2 JOIN t3)
3472 ** If we flatten the above, we would get
3474 ** (t1 LEFT OUTER JOIN t2) JOIN t3
3476 ** which is not at all the same thing.
3478 ** If the subquery is the right operand of a LEFT JOIN, then the outer
3479 ** query cannot be an aggregate. (3c) This is an artifact of the way
3480 ** aggregates are processed - there is no mechanism to determine if
3481 ** the LEFT JOIN table should be all-NULL.
3483 ** See also tickets #306, #350, and #3300.
3485 if( (pSubitem
->fg
.jointype
& JT_OUTER
)!=0 ){
3487 if( pSubSrc
->nSrc
>1 || isAgg
|| IsVirtual(pSubSrc
->a
[0].pTab
) ){
3488 /* (3a) (3c) (3b) */
3492 #ifdef SQLITE_EXTRA_IFNULLROW
3493 else if( iFrom
>0 && !isAgg
){
3494 /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
3495 ** every reference to any result column from subquery in a join, even
3496 ** though they are not necessary. This will stress-test the OP_IfNullRow
3502 /* Restriction (17): If the sub-query is a compound SELECT, then it must
3503 ** use only the UNION ALL operator. And none of the simple select queries
3504 ** that make up the compound SELECT are allowed to be aggregate or distinct
3508 if( pSub
->pOrderBy
){
3509 return 0; /* Restriction (20) */
3511 if( isAgg
|| (p
->selFlags
& SF_Distinct
)!=0 || pSrc
->nSrc
!=1 ){
3512 return 0; /* (17d1), (17d2), or (17d3) */
3514 for(pSub1
=pSub
; pSub1
; pSub1
=pSub1
->pPrior
){
3515 testcase( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Distinct
);
3516 testcase( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Aggregate
);
3517 assert( pSub
->pSrc
!=0 );
3518 assert( pSub
->pEList
->nExpr
==pSub1
->pEList
->nExpr
);
3519 if( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))!=0 /* (17b) */
3520 || (pSub1
->pPrior
&& pSub1
->op
!=TK_ALL
) /* (17a) */
3521 || pSub1
->pSrc
->nSrc
<1 /* (17c) */
3525 testcase( pSub1
->pSrc
->nSrc
>1 );
3528 /* Restriction (18). */
3531 for(ii
=0; ii
<p
->pOrderBy
->nExpr
; ii
++){
3532 if( p
->pOrderBy
->a
[ii
].u
.x
.iOrderByCol
==0 ) return 0;
3537 /* Ex-restriction (23):
3538 ** The only way that the recursive part of a CTE can contain a compound
3539 ** subquery is for the subquery to be one term of a join. But if the
3540 ** subquery is a join, then the flattening has already been stopped by
3541 ** restriction (17d3)
3543 assert( (p
->selFlags
& SF_Recursive
)==0 || pSub
->pPrior
==0 );
3545 /***** If we reach this point, flattening is permitted. *****/
3546 SELECTTRACE(1,pParse
,p
,("flatten %s.%p from term %d\n",
3547 pSub
->zSelName
, pSub
, iFrom
));
3549 /* Authorize the subquery */
3550 pParse
->zAuthContext
= pSubitem
->zName
;
3551 TESTONLY(i
=) sqlite3AuthCheck(pParse
, SQLITE_SELECT
, 0, 0, 0);
3552 testcase( i
==SQLITE_DENY
);
3553 pParse
->zAuthContext
= zSavedAuthContext
;
3555 /* If the sub-query is a compound SELECT statement, then (by restrictions
3556 ** 17 and 18 above) it must be a UNION ALL and the parent query must
3559 ** SELECT <expr-list> FROM (<sub-query>) <where-clause>
3561 ** followed by any ORDER BY, LIMIT and/or OFFSET clauses. This block
3562 ** creates N-1 copies of the parent query without any ORDER BY, LIMIT or
3563 ** OFFSET clauses and joins them to the left-hand-side of the original
3564 ** using UNION ALL operators. In this case N is the number of simple
3565 ** select statements in the compound sub-query.
3569 ** SELECT a+1 FROM (
3570 ** SELECT x FROM tab
3572 ** SELECT y FROM tab
3574 ** SELECT abs(z*2) FROM tab2
3575 ** ) WHERE a!=5 ORDER BY 1
3577 ** Transformed into:
3579 ** SELECT x+1 FROM tab WHERE x+1!=5
3581 ** SELECT y+1 FROM tab WHERE y+1!=5
3583 ** SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5
3586 ** We call this the "compound-subquery flattening".
3588 for(pSub
=pSub
->pPrior
; pSub
; pSub
=pSub
->pPrior
){
3590 ExprList
*pOrderBy
= p
->pOrderBy
;
3591 Expr
*pLimit
= p
->pLimit
;
3592 Select
*pPrior
= p
->pPrior
;
3597 pNew
= sqlite3SelectDup(db
, p
, 0);
3598 sqlite3SelectSetName(pNew
, pSub
->zSelName
);
3600 p
->pOrderBy
= pOrderBy
;
3606 pNew
->pPrior
= pPrior
;
3607 if( pPrior
) pPrior
->pNext
= pNew
;
3610 SELECTTRACE(2,pParse
,p
,
3611 ("compound-subquery flattener creates %s.%p as peer\n",
3612 pNew
->zSelName
, pNew
));
3614 if( db
->mallocFailed
) return 1;
3617 /* Begin flattening the iFrom-th entry of the FROM clause
3618 ** in the outer query.
3620 pSub
= pSub1
= pSubitem
->pSelect
;
3622 /* Delete the transient table structure associated with the
3625 sqlite3DbFree(db
, pSubitem
->zDatabase
);
3626 sqlite3DbFree(db
, pSubitem
->zName
);
3627 sqlite3DbFree(db
, pSubitem
->zAlias
);
3628 pSubitem
->zDatabase
= 0;
3629 pSubitem
->zName
= 0;
3630 pSubitem
->zAlias
= 0;
3631 pSubitem
->pSelect
= 0;
3633 /* Defer deleting the Table object associated with the
3634 ** subquery until code generation is
3635 ** complete, since there may still exist Expr.pTab entries that
3636 ** refer to the subquery even after flattening. Ticket #3346.
3638 ** pSubitem->pTab is always non-NULL by test restrictions and tests above.
3640 if( ALWAYS(pSubitem
->pTab
!=0) ){
3641 Table
*pTabToDel
= pSubitem
->pTab
;
3642 if( pTabToDel
->nTabRef
==1 ){
3643 Parse
*pToplevel
= sqlite3ParseToplevel(pParse
);
3644 pTabToDel
->pNextZombie
= pToplevel
->pZombieTab
;
3645 pToplevel
->pZombieTab
= pTabToDel
;
3647 pTabToDel
->nTabRef
--;
3652 /* The following loop runs once for each term in a compound-subquery
3653 ** flattening (as described above). If we are doing a different kind
3654 ** of flattening - a flattening other than a compound-subquery flattening -
3655 ** then this loop only runs once.
3657 ** This loop moves all of the FROM elements of the subquery into the
3658 ** the FROM clause of the outer query. Before doing this, remember
3659 ** the cursor number for the original outer query FROM element in
3660 ** iParent. The iParent cursor will never be used. Subsequent code
3661 ** will scan expressions looking for iParent references and replace
3662 ** those references with expressions that resolve to the subquery FROM
3663 ** elements we are now copying in.
3665 for(pParent
=p
; pParent
; pParent
=pParent
->pPrior
, pSub
=pSub
->pPrior
){
3668 pSubSrc
= pSub
->pSrc
; /* FROM clause of subquery */
3669 nSubSrc
= pSubSrc
->nSrc
; /* Number of terms in subquery FROM clause */
3670 pSrc
= pParent
->pSrc
; /* FROM clause of the outer query */
3673 assert( pParent
==p
); /* First time through the loop */
3674 jointype
= pSubitem
->fg
.jointype
;
3676 assert( pParent
!=p
); /* 2nd and subsequent times through the loop */
3677 pSrc
= pParent
->pSrc
= sqlite3SrcListAppend(db
, 0, 0, 0);
3679 assert( db
->mallocFailed
);
3684 /* The subquery uses a single slot of the FROM clause of the outer
3685 ** query. If the subquery has more than one element in its FROM clause,
3686 ** then expand the outer query to make space for it to hold all elements
3691 ** SELECT * FROM tabA, (SELECT * FROM sub1, sub2), tabB;
3693 ** The outer query has 3 slots in its FROM clause. One slot of the
3694 ** outer query (the middle slot) is used by the subquery. The next
3695 ** block of code will expand the outer query FROM clause to 4 slots.
3696 ** The middle slot is expanded to two slots in order to make space
3697 ** for the two elements in the FROM clause of the subquery.
3700 pParent
->pSrc
= pSrc
= sqlite3SrcListEnlarge(db
, pSrc
, nSubSrc
-1,iFrom
+1);
3701 if( db
->mallocFailed
){
3706 /* Transfer the FROM clause terms from the subquery into the
3709 for(i
=0; i
<nSubSrc
; i
++){
3710 sqlite3IdListDelete(db
, pSrc
->a
[i
+iFrom
].pUsing
);
3711 assert( pSrc
->a
[i
+iFrom
].fg
.isTabFunc
==0 );
3712 pSrc
->a
[i
+iFrom
] = pSubSrc
->a
[i
];
3713 iNewParent
= pSubSrc
->a
[i
].iCursor
;
3714 memset(&pSubSrc
->a
[i
], 0, sizeof(pSubSrc
->a
[i
]));
3716 pSrc
->a
[iFrom
].fg
.jointype
= jointype
;
3718 /* Now begin substituting subquery result set expressions for
3719 ** references to the iParent in the outer query.
3723 ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
3724 ** \ \_____________ subquery __________/ /
3725 ** \_____________________ outer query ______________________________/
3727 ** We look at every expression in the outer query and every place we see
3728 ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
3730 if( pSub
->pOrderBy
){
3731 /* At this point, any non-zero iOrderByCol values indicate that the
3732 ** ORDER BY column expression is identical to the iOrderByCol'th
3733 ** expression returned by SELECT statement pSub. Since these values
3734 ** do not necessarily correspond to columns in SELECT statement pParent,
3735 ** zero them before transfering the ORDER BY clause.
3737 ** Not doing this may cause an error if a subsequent call to this
3738 ** function attempts to flatten a compound sub-query into pParent
3739 ** (the only way this can happen is if the compound sub-query is
3740 ** currently part of pSub->pSrc). See ticket [d11a6e908f]. */
3741 ExprList
*pOrderBy
= pSub
->pOrderBy
;
3742 for(i
=0; i
<pOrderBy
->nExpr
; i
++){
3743 pOrderBy
->a
[i
].u
.x
.iOrderByCol
= 0;
3745 assert( pParent
->pOrderBy
==0 );
3746 assert( pSub
->pPrior
==0 );
3747 pParent
->pOrderBy
= pOrderBy
;
3750 pWhere
= sqlite3ExprDup(db
, pSub
->pWhere
, 0);
3752 setJoinExpr(pWhere
, iNewParent
);
3754 pParent
->pWhere
= sqlite3ExprAnd(db
, pWhere
, pParent
->pWhere
);
3755 if( db
->mallocFailed
==0 ){
3759 x
.iNewTable
= iNewParent
;
3760 x
.isLeftJoin
= isLeftJoin
;
3761 x
.pEList
= pSub
->pEList
;
3762 substSelect(&x
, pParent
, 0);
3765 /* The flattened query is distinct if either the inner or the
3766 ** outer query is distinct.
3768 pParent
->selFlags
|= pSub
->selFlags
& SF_Distinct
;
3771 ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y;
3773 ** One is tempted to try to add a and b to combine the limits. But this
3774 ** does not work if either limit is negative.
3777 pParent
->pLimit
= pSub
->pLimit
;
3782 /* Finially, delete what is left of the subquery and return
3785 sqlite3SelectDelete(db
, pSub1
);
3787 #if SELECTTRACE_ENABLED
3788 if( sqlite3SelectTrace
& 0x100 ){
3789 SELECTTRACE(0x100,pParse
,p
,("After flattening:\n"));
3790 sqlite3TreeViewSelect(0, p
, 0);
3796 #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
3800 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
3802 ** Make copies of relevant WHERE clause terms of the outer query into
3803 ** the WHERE clause of subquery. Example:
3805 ** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1) WHERE x=5 AND y=10;
3807 ** Transformed into:
3809 ** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1 WHERE a=5 AND c-d=10)
3810 ** WHERE x=5 AND y=10;
3812 ** The hope is that the terms added to the inner query will make it more
3815 ** Do not attempt this optimization if:
3817 ** (1) (** This restriction was removed on 2017-09-29. We used to
3818 ** disallow this optimization for aggregate subqueries, but now
3819 ** it is allowed by putting the extra terms on the HAVING clause.
3820 ** The added HAVING clause is pointless if the subquery lacks
3821 ** a GROUP BY clause. But such a HAVING clause is also harmless
3822 ** so there does not appear to be any reason to add extra logic
3823 ** to suppress it. **)
3825 ** (2) The inner query is the recursive part of a common table expression.
3827 ** (3) The inner query has a LIMIT clause (since the changes to the WHERE
3828 ** close would change the meaning of the LIMIT).
3830 ** (4) The inner query is the right operand of a LEFT JOIN. (The caller
3831 ** enforces this restriction since this routine does not have enough
3832 ** information to know.)
3834 ** (5) The WHERE clause expression originates in the ON or USING clause
3837 ** Return 0 if no changes are made and non-zero if one or more WHERE clause
3838 ** terms are duplicated into the subquery.
3840 static int pushDownWhereTerms(
3841 Parse
*pParse
, /* Parse context (for malloc() and error reporting) */
3842 Select
*pSubq
, /* The subquery whose WHERE clause is to be augmented */
3843 Expr
*pWhere
, /* The WHERE clause of the outer query */
3844 int iCursor
/* Cursor number of the subquery */
3848 if( pWhere
==0 ) return 0;
3849 if( pSubq
->selFlags
& SF_Recursive
) return 0; /* restriction (2) */
3852 /* Only the first term of a compound can have a WITH clause. But make
3853 ** sure no other terms are marked SF_Recursive in case something changes
3858 for(pX
=pSubq
; pX
; pX
=pX
->pPrior
){
3859 assert( (pX
->selFlags
& (SF_Recursive
))==0 );
3864 if( pSubq
->pLimit
!=0 ){
3865 return 0; /* restriction (3) */
3867 while( pWhere
->op
==TK_AND
){
3868 nChng
+= pushDownWhereTerms(pParse
, pSubq
, pWhere
->pRight
, iCursor
);
3869 pWhere
= pWhere
->pLeft
;
3871 if( ExprHasProperty(pWhere
,EP_FromJoin
) ) return 0; /* restriction (5) */
3872 if( sqlite3ExprIsTableConstant(pWhere
, iCursor
) ){
3876 pNew
= sqlite3ExprDup(pParse
->db
, pWhere
, 0);
3879 x
.iNewTable
= iCursor
;
3881 x
.pEList
= pSubq
->pEList
;
3882 pNew
= substExpr(&x
, pNew
);
3883 if( pSubq
->selFlags
& SF_Aggregate
){
3884 pSubq
->pHaving
= sqlite3ExprAnd(pParse
->db
, pSubq
->pHaving
, pNew
);
3886 pSubq
->pWhere
= sqlite3ExprAnd(pParse
->db
, pSubq
->pWhere
, pNew
);
3888 pSubq
= pSubq
->pPrior
;
3893 #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
3896 ** The pFunc is the only aggregate function in the query. Check to see
3897 ** if the query is a candidate for the min/max optimization.
3899 ** If the query is a candidate for the min/max optimization, then set
3900 ** *ppMinMax to be an ORDER BY clause to be used for the optimization
3901 ** and return either WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX depending on
3902 ** whether pFunc is a min() or max() function.
3904 ** If the query is not a candidate for the min/max optimization, return
3905 ** WHERE_ORDERBY_NORMAL (which must be zero).
3907 ** This routine must be called after aggregate functions have been
3908 ** located but before their arguments have been subjected to aggregate
3911 static u8
minMaxQuery(sqlite3
*db
, Expr
*pFunc
, ExprList
**ppMinMax
){
3912 int eRet
= WHERE_ORDERBY_NORMAL
; /* Return value */
3913 ExprList
*pEList
= pFunc
->x
.pList
; /* Arguments to agg function */
3914 const char *zFunc
; /* Name of aggregate function pFunc */
3918 assert( *ppMinMax
==0 );
3919 assert( pFunc
->op
==TK_AGG_FUNCTION
);
3920 if( pEList
==0 || pEList
->nExpr
!=1 ) return eRet
;
3921 zFunc
= pFunc
->u
.zToken
;
3922 if( sqlite3StrICmp(zFunc
, "min")==0 ){
3923 eRet
= WHERE_ORDERBY_MIN
;
3924 sortOrder
= SQLITE_SO_ASC
;
3925 }else if( sqlite3StrICmp(zFunc
, "max")==0 ){
3926 eRet
= WHERE_ORDERBY_MAX
;
3927 sortOrder
= SQLITE_SO_DESC
;
3931 *ppMinMax
= pOrderBy
= sqlite3ExprListDup(db
, pEList
, 0);
3932 assert( pOrderBy
!=0 || db
->mallocFailed
);
3933 if( pOrderBy
) pOrderBy
->a
[0].sortOrder
= sortOrder
;
3938 ** The select statement passed as the first argument is an aggregate query.
3939 ** The second argument is the associated aggregate-info object. This
3940 ** function tests if the SELECT is of the form:
3942 ** SELECT count(*) FROM <tbl>
3944 ** where table is a database table, not a sub-select or view. If the query
3945 ** does match this pattern, then a pointer to the Table object representing
3946 ** <tbl> is returned. Otherwise, 0 is returned.
3948 static Table
*isSimpleCount(Select
*p
, AggInfo
*pAggInfo
){
3952 assert( !p
->pGroupBy
);
3954 if( p
->pWhere
|| p
->pEList
->nExpr
!=1
3955 || p
->pSrc
->nSrc
!=1 || p
->pSrc
->a
[0].pSelect
3959 pTab
= p
->pSrc
->a
[0].pTab
;
3960 pExpr
= p
->pEList
->a
[0].pExpr
;
3961 assert( pTab
&& !pTab
->pSelect
&& pExpr
);
3963 if( IsVirtual(pTab
) ) return 0;
3964 if( pExpr
->op
!=TK_AGG_FUNCTION
) return 0;
3965 if( NEVER(pAggInfo
->nFunc
==0) ) return 0;
3966 if( (pAggInfo
->aFunc
[0].pFunc
->funcFlags
&SQLITE_FUNC_COUNT
)==0 ) return 0;
3967 if( pExpr
->flags
&EP_Distinct
) return 0;
3973 ** If the source-list item passed as an argument was augmented with an
3974 ** INDEXED BY clause, then try to locate the specified index. If there
3975 ** was such a clause and the named index cannot be found, return
3976 ** SQLITE_ERROR and leave an error in pParse. Otherwise, populate
3977 ** pFrom->pIndex and return SQLITE_OK.
3979 int sqlite3IndexedByLookup(Parse
*pParse
, struct SrcList_item
*pFrom
){
3980 if( pFrom
->pTab
&& pFrom
->fg
.isIndexedBy
){
3981 Table
*pTab
= pFrom
->pTab
;
3982 char *zIndexedBy
= pFrom
->u1
.zIndexedBy
;
3984 for(pIdx
=pTab
->pIndex
;
3985 pIdx
&& sqlite3StrICmp(pIdx
->zName
, zIndexedBy
);
3989 sqlite3ErrorMsg(pParse
, "no such index: %s", zIndexedBy
, 0);
3990 pParse
->checkSchema
= 1;
3991 return SQLITE_ERROR
;
3993 pFrom
->pIBIndex
= pIdx
;
3998 ** Detect compound SELECT statements that use an ORDER BY clause with
3999 ** an alternative collating sequence.
4001 ** SELECT ... FROM t1 EXCEPT SELECT ... FROM t2 ORDER BY .. COLLATE ...
4003 ** These are rewritten as a subquery:
4005 ** SELECT * FROM (SELECT ... FROM t1 EXCEPT SELECT ... FROM t2)
4006 ** ORDER BY ... COLLATE ...
4008 ** This transformation is necessary because the multiSelectOrderBy() routine
4009 ** above that generates the code for a compound SELECT with an ORDER BY clause
4010 ** uses a merge algorithm that requires the same collating sequence on the
4011 ** result columns as on the ORDER BY clause. See ticket
4012 ** http://www.sqlite.org/src/info/6709574d2a
4014 ** This transformation is only needed for EXCEPT, INTERSECT, and UNION.
4015 ** The UNION ALL operator works fine with multiSelectOrderBy() even when
4016 ** there are COLLATE terms in the ORDER BY.
4018 static int convertCompoundSelectToSubquery(Walker
*pWalker
, Select
*p
){
4023 struct ExprList_item
*a
;
4028 if( p
->pPrior
==0 ) return WRC_Continue
;
4029 if( p
->pOrderBy
==0 ) return WRC_Continue
;
4030 for(pX
=p
; pX
&& (pX
->op
==TK_ALL
|| pX
->op
==TK_SELECT
); pX
=pX
->pPrior
){}
4031 if( pX
==0 ) return WRC_Continue
;
4033 for(i
=p
->pOrderBy
->nExpr
-1; i
>=0; i
--){
4034 if( a
[i
].pExpr
->flags
& EP_Collate
) break;
4036 if( i
<0 ) return WRC_Continue
;
4038 /* If we reach this point, that means the transformation is required. */
4040 pParse
= pWalker
->pParse
;
4042 pNew
= sqlite3DbMallocZero(db
, sizeof(*pNew
) );
4043 if( pNew
==0 ) return WRC_Abort
;
4044 memset(&dummy
, 0, sizeof(dummy
));
4045 pNewSrc
= sqlite3SrcListAppendFromTerm(pParse
,0,0,0,&dummy
,pNew
,0,0);
4046 if( pNewSrc
==0 ) return WRC_Abort
;
4049 p
->pEList
= sqlite3ExprListAppend(pParse
, 0, sqlite3Expr(db
, TK_ASTERISK
, 0));
4058 p
->selFlags
&= ~SF_Compound
;
4059 assert( (p
->selFlags
& SF_Converted
)==0 );
4060 p
->selFlags
|= SF_Converted
;
4061 assert( pNew
->pPrior
!=0 );
4062 pNew
->pPrior
->pNext
= pNew
;
4064 return WRC_Continue
;
4068 ** Check to see if the FROM clause term pFrom has table-valued function
4069 ** arguments. If it does, leave an error message in pParse and return
4070 ** non-zero, since pFrom is not allowed to be a table-valued function.
4072 static int cannotBeFunction(Parse
*pParse
, struct SrcList_item
*pFrom
){
4073 if( pFrom
->fg
.isTabFunc
){
4074 sqlite3ErrorMsg(pParse
, "'%s' is not a function", pFrom
->zName
);
4080 #ifndef SQLITE_OMIT_CTE
4082 ** Argument pWith (which may be NULL) points to a linked list of nested
4083 ** WITH contexts, from inner to outermost. If the table identified by
4084 ** FROM clause element pItem is really a common-table-expression (CTE)
4085 ** then return a pointer to the CTE definition for that table. Otherwise
4088 ** If a non-NULL value is returned, set *ppContext to point to the With
4089 ** object that the returned CTE belongs to.
4091 static struct Cte
*searchWith(
4092 With
*pWith
, /* Current innermost WITH clause */
4093 struct SrcList_item
*pItem
, /* FROM clause element to resolve */
4094 With
**ppContext
/* OUT: WITH clause return value belongs to */
4097 if( pItem
->zDatabase
==0 && (zName
= pItem
->zName
)!=0 ){
4099 for(p
=pWith
; p
; p
=p
->pOuter
){
4101 for(i
=0; i
<p
->nCte
; i
++){
4102 if( sqlite3StrICmp(zName
, p
->a
[i
].zName
)==0 ){
4112 /* The code generator maintains a stack of active WITH clauses
4113 ** with the inner-most WITH clause being at the top of the stack.
4115 ** This routine pushes the WITH clause passed as the second argument
4116 ** onto the top of the stack. If argument bFree is true, then this
4117 ** WITH clause will never be popped from the stack. In this case it
4118 ** should be freed along with the Parse object. In other cases, when
4119 ** bFree==0, the With object will be freed along with the SELECT
4120 ** statement with which it is associated.
4122 void sqlite3WithPush(Parse
*pParse
, With
*pWith
, u8 bFree
){
4123 assert( bFree
==0 || (pParse
->pWith
==0 && pParse
->pWithToFree
==0) );
4125 assert( pParse
->pWith
!=pWith
);
4126 pWith
->pOuter
= pParse
->pWith
;
4127 pParse
->pWith
= pWith
;
4128 if( bFree
) pParse
->pWithToFree
= pWith
;
4133 ** This function checks if argument pFrom refers to a CTE declared by
4134 ** a WITH clause on the stack currently maintained by the parser. And,
4135 ** if currently processing a CTE expression, if it is a recursive
4136 ** reference to the current CTE.
4138 ** If pFrom falls into either of the two categories above, pFrom->pTab
4139 ** and other fields are populated accordingly. The caller should check
4140 ** (pFrom->pTab!=0) to determine whether or not a successful match
4143 ** Whether or not a match is found, SQLITE_OK is returned if no error
4144 ** occurs. If an error does occur, an error message is stored in the
4145 ** parser and some error code other than SQLITE_OK returned.
4147 static int withExpand(
4149 struct SrcList_item
*pFrom
4151 Parse
*pParse
= pWalker
->pParse
;
4152 sqlite3
*db
= pParse
->db
;
4153 struct Cte
*pCte
; /* Matched CTE (or NULL if no match) */
4154 With
*pWith
; /* WITH clause that pCte belongs to */
4156 assert( pFrom
->pTab
==0 );
4158 pCte
= searchWith(pParse
->pWith
, pFrom
, &pWith
);
4163 Select
*pLeft
; /* Left-most SELECT statement */
4164 int bMayRecursive
; /* True if compound joined by UNION [ALL] */
4165 With
*pSavedWith
; /* Initial value of pParse->pWith */
4167 /* If pCte->zCteErr is non-NULL at this point, then this is an illegal
4168 ** recursive reference to CTE pCte. Leave an error in pParse and return
4169 ** early. If pCte->zCteErr is NULL, then this is not a recursive reference.
4170 ** In this case, proceed. */
4171 if( pCte
->zCteErr
){
4172 sqlite3ErrorMsg(pParse
, pCte
->zCteErr
, pCte
->zName
);
4173 return SQLITE_ERROR
;
4175 if( cannotBeFunction(pParse
, pFrom
) ) return SQLITE_ERROR
;
4177 assert( pFrom
->pTab
==0 );
4178 pFrom
->pTab
= pTab
= sqlite3DbMallocZero(db
, sizeof(Table
));
4179 if( pTab
==0 ) return WRC_Abort
;
4181 pTab
->zName
= sqlite3DbStrDup(db
, pCte
->zName
);
4183 pTab
->nRowLogEst
= 200; assert( 200==sqlite3LogEst(1048576) );
4184 pTab
->tabFlags
|= TF_Ephemeral
| TF_NoVisibleRowid
;
4185 pFrom
->pSelect
= sqlite3SelectDup(db
, pCte
->pSelect
, 0);
4186 if( db
->mallocFailed
) return SQLITE_NOMEM_BKPT
;
4187 assert( pFrom
->pSelect
);
4189 /* Check if this is a recursive CTE. */
4190 pSel
= pFrom
->pSelect
;
4191 bMayRecursive
= ( pSel
->op
==TK_ALL
|| pSel
->op
==TK_UNION
);
4192 if( bMayRecursive
){
4194 SrcList
*pSrc
= pFrom
->pSelect
->pSrc
;
4195 for(i
=0; i
<pSrc
->nSrc
; i
++){
4196 struct SrcList_item
*pItem
= &pSrc
->a
[i
];
4197 if( pItem
->zDatabase
==0
4199 && 0==sqlite3StrICmp(pItem
->zName
, pCte
->zName
)
4202 pItem
->fg
.isRecursive
= 1;
4204 pSel
->selFlags
|= SF_Recursive
;
4209 /* Only one recursive reference is permitted. */
4210 if( pTab
->nTabRef
>2 ){
4212 pParse
, "multiple references to recursive table: %s", pCte
->zName
4214 return SQLITE_ERROR
;
4216 assert( pTab
->nTabRef
==1 ||
4217 ((pSel
->selFlags
&SF_Recursive
) && pTab
->nTabRef
==2 ));
4219 pCte
->zCteErr
= "circular reference: %s";
4220 pSavedWith
= pParse
->pWith
;
4221 pParse
->pWith
= pWith
;
4222 if( bMayRecursive
){
4223 Select
*pPrior
= pSel
->pPrior
;
4224 assert( pPrior
->pWith
==0 );
4225 pPrior
->pWith
= pSel
->pWith
;
4226 sqlite3WalkSelect(pWalker
, pPrior
);
4229 sqlite3WalkSelect(pWalker
, pSel
);
4231 pParse
->pWith
= pWith
;
4233 for(pLeft
=pSel
; pLeft
->pPrior
; pLeft
=pLeft
->pPrior
);
4234 pEList
= pLeft
->pEList
;
4236 if( pEList
&& pEList
->nExpr
!=pCte
->pCols
->nExpr
){
4237 sqlite3ErrorMsg(pParse
, "table %s has %d values for %d columns",
4238 pCte
->zName
, pEList
->nExpr
, pCte
->pCols
->nExpr
4240 pParse
->pWith
= pSavedWith
;
4241 return SQLITE_ERROR
;
4243 pEList
= pCte
->pCols
;
4246 sqlite3ColumnsFromExprList(pParse
, pEList
, &pTab
->nCol
, &pTab
->aCol
);
4247 if( bMayRecursive
){
4248 if( pSel
->selFlags
& SF_Recursive
){
4249 pCte
->zCteErr
= "multiple recursive references: %s";
4251 pCte
->zCteErr
= "recursive reference in a subquery: %s";
4253 sqlite3WalkSelect(pWalker
, pSel
);
4256 pParse
->pWith
= pSavedWith
;
4263 #ifndef SQLITE_OMIT_CTE
4265 ** If the SELECT passed as the second argument has an associated WITH
4266 ** clause, pop it from the stack stored as part of the Parse object.
4268 ** This function is used as the xSelectCallback2() callback by
4269 ** sqlite3SelectExpand() when walking a SELECT tree to resolve table
4270 ** names and other FROM clause elements.
4272 static void selectPopWith(Walker
*pWalker
, Select
*p
){
4273 Parse
*pParse
= pWalker
->pParse
;
4274 if( OK_IF_ALWAYS_TRUE(pParse
->pWith
) && p
->pPrior
==0 ){
4275 With
*pWith
= findRightmost(p
)->pWith
;
4277 assert( pParse
->pWith
==pWith
);
4278 pParse
->pWith
= pWith
->pOuter
;
4283 #define selectPopWith 0
4287 ** This routine is a Walker callback for "expanding" a SELECT statement.
4288 ** "Expanding" means to do the following:
4290 ** (1) Make sure VDBE cursor numbers have been assigned to every
4291 ** element of the FROM clause.
4293 ** (2) Fill in the pTabList->a[].pTab fields in the SrcList that
4294 ** defines FROM clause. When views appear in the FROM clause,
4295 ** fill pTabList->a[].pSelect with a copy of the SELECT statement
4296 ** that implements the view. A copy is made of the view's SELECT
4297 ** statement so that we can freely modify or delete that statement
4298 ** without worrying about messing up the persistent representation
4301 ** (3) Add terms to the WHERE clause to accommodate the NATURAL keyword
4302 ** on joins and the ON and USING clause of joins.
4304 ** (4) Scan the list of columns in the result set (pEList) looking
4305 ** for instances of the "*" operator or the TABLE.* operator.
4306 ** If found, expand each "*" to be every column in every table
4307 ** and TABLE.* to be every column in TABLE.
4310 static int selectExpander(Walker
*pWalker
, Select
*p
){
4311 Parse
*pParse
= pWalker
->pParse
;
4315 struct SrcList_item
*pFrom
;
4316 sqlite3
*db
= pParse
->db
;
4317 Expr
*pE
, *pRight
, *pExpr
;
4318 u16 selFlags
= p
->selFlags
;
4321 p
->selFlags
|= SF_Expanded
;
4322 if( db
->mallocFailed
){
4325 assert( p
->pSrc
!=0 );
4326 if( (selFlags
& SF_Expanded
)!=0 ){
4331 if( OK_IF_ALWAYS_TRUE(p
->pWith
) ){
4332 sqlite3WithPush(pParse
, p
->pWith
, 0);
4335 /* Make sure cursor numbers have been assigned to all entries in
4336 ** the FROM clause of the SELECT statement.
4338 sqlite3SrcListAssignCursors(pParse
, pTabList
);
4340 /* Look up every table named in the FROM clause of the select. If
4341 ** an entry of the FROM clause is a subquery instead of a table or view,
4342 ** then create a transient table structure to describe the subquery.
4344 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
4346 assert( pFrom
->fg
.isRecursive
==0 || pFrom
->pTab
!=0 );
4347 if( pFrom
->fg
.isRecursive
) continue;
4348 assert( pFrom
->pTab
==0 );
4349 #ifndef SQLITE_OMIT_CTE
4350 if( withExpand(pWalker
, pFrom
) ) return WRC_Abort
;
4351 if( pFrom
->pTab
) {} else
4353 if( pFrom
->zName
==0 ){
4354 #ifndef SQLITE_OMIT_SUBQUERY
4355 Select
*pSel
= pFrom
->pSelect
;
4356 /* A sub-query in the FROM clause of a SELECT */
4358 assert( pFrom
->pTab
==0 );
4359 if( sqlite3WalkSelect(pWalker
, pSel
) ) return WRC_Abort
;
4360 pFrom
->pTab
= pTab
= sqlite3DbMallocZero(db
, sizeof(Table
));
4361 if( pTab
==0 ) return WRC_Abort
;
4363 if( pFrom
->zAlias
){
4364 pTab
->zName
= sqlite3DbStrDup(db
, pFrom
->zAlias
);
4366 pTab
->zName
= sqlite3MPrintf(db
, "subquery_%p", (void*)pTab
);
4368 while( pSel
->pPrior
){ pSel
= pSel
->pPrior
; }
4369 sqlite3ColumnsFromExprList(pParse
, pSel
->pEList
,&pTab
->nCol
,&pTab
->aCol
);
4371 pTab
->nRowLogEst
= 200; assert( 200==sqlite3LogEst(1048576) );
4372 pTab
->tabFlags
|= TF_Ephemeral
;
4375 /* An ordinary table or view name in the FROM clause */
4376 assert( pFrom
->pTab
==0 );
4377 pFrom
->pTab
= pTab
= sqlite3LocateTableItem(pParse
, 0, pFrom
);
4378 if( pTab
==0 ) return WRC_Abort
;
4379 if( pTab
->nTabRef
>=0xffff ){
4380 sqlite3ErrorMsg(pParse
, "too many references to \"%s\": max 65535",
4386 if( !IsVirtual(pTab
) && cannotBeFunction(pParse
, pFrom
) ){
4389 #if !defined(SQLITE_OMIT_VIEW) || !defined (SQLITE_OMIT_VIRTUALTABLE)
4390 if( IsVirtual(pTab
) || pTab
->pSelect
){
4392 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ) return WRC_Abort
;
4393 assert( pFrom
->pSelect
==0 );
4394 pFrom
->pSelect
= sqlite3SelectDup(db
, pTab
->pSelect
, 0);
4395 sqlite3SelectSetName(pFrom
->pSelect
, pTab
->zName
);
4398 sqlite3WalkSelect(pWalker
, pFrom
->pSelect
);
4404 /* Locate the index named by the INDEXED BY clause, if any. */
4405 if( sqlite3IndexedByLookup(pParse
, pFrom
) ){
4410 /* Process NATURAL keywords, and ON and USING clauses of joins.
4412 if( db
->mallocFailed
|| sqliteProcessJoin(pParse
, p
) ){
4416 /* For every "*" that occurs in the column list, insert the names of
4417 ** all columns in all tables. And for every TABLE.* insert the names
4418 ** of all columns in TABLE. The parser inserted a special expression
4419 ** with the TK_ASTERISK operator for each "*" that it found in the column
4420 ** list. The following code just has to locate the TK_ASTERISK
4421 ** expressions and expand each one to the list of all columns in
4424 ** The first loop just checks to see if there are any "*" operators
4425 ** that need expanding.
4427 for(k
=0; k
<pEList
->nExpr
; k
++){
4428 pE
= pEList
->a
[k
].pExpr
;
4429 if( pE
->op
==TK_ASTERISK
) break;
4430 assert( pE
->op
!=TK_DOT
|| pE
->pRight
!=0 );
4431 assert( pE
->op
!=TK_DOT
|| (pE
->pLeft
!=0 && pE
->pLeft
->op
==TK_ID
) );
4432 if( pE
->op
==TK_DOT
&& pE
->pRight
->op
==TK_ASTERISK
) break;
4433 elistFlags
|= pE
->flags
;
4435 if( k
<pEList
->nExpr
){
4437 ** If we get here it means the result set contains one or more "*"
4438 ** operators that need to be expanded. Loop through each expression
4439 ** in the result set and expand them one by one.
4441 struct ExprList_item
*a
= pEList
->a
;
4443 int flags
= pParse
->db
->flags
;
4444 int longNames
= (flags
& SQLITE_FullColNames
)!=0
4445 && (flags
& SQLITE_ShortColNames
)==0;
4447 for(k
=0; k
<pEList
->nExpr
; k
++){
4449 elistFlags
|= pE
->flags
;
4450 pRight
= pE
->pRight
;
4451 assert( pE
->op
!=TK_DOT
|| pRight
!=0 );
4452 if( pE
->op
!=TK_ASTERISK
4453 && (pE
->op
!=TK_DOT
|| pRight
->op
!=TK_ASTERISK
)
4455 /* This particular expression does not need to be expanded.
4457 pNew
= sqlite3ExprListAppend(pParse
, pNew
, a
[k
].pExpr
);
4459 pNew
->a
[pNew
->nExpr
-1].zName
= a
[k
].zName
;
4460 pNew
->a
[pNew
->nExpr
-1].zSpan
= a
[k
].zSpan
;
4466 /* This expression is a "*" or a "TABLE.*" and needs to be
4468 int tableSeen
= 0; /* Set to 1 when TABLE matches */
4469 char *zTName
= 0; /* text of name of TABLE */
4470 if( pE
->op
==TK_DOT
){
4471 assert( pE
->pLeft
!=0 );
4472 assert( !ExprHasProperty(pE
->pLeft
, EP_IntValue
) );
4473 zTName
= pE
->pLeft
->u
.zToken
;
4475 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
4476 Table
*pTab
= pFrom
->pTab
;
4477 Select
*pSub
= pFrom
->pSelect
;
4478 char *zTabName
= pFrom
->zAlias
;
4479 const char *zSchemaName
= 0;
4482 zTabName
= pTab
->zName
;
4484 if( db
->mallocFailed
) break;
4485 if( pSub
==0 || (pSub
->selFlags
& SF_NestedFrom
)==0 ){
4487 if( zTName
&& sqlite3StrICmp(zTName
, zTabName
)!=0 ){
4490 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
4491 zSchemaName
= iDb
>=0 ? db
->aDb
[iDb
].zDbSName
: "*";
4493 for(j
=0; j
<pTab
->nCol
; j
++){
4494 char *zName
= pTab
->aCol
[j
].zName
;
4495 char *zColname
; /* The computed column name */
4496 char *zToFree
; /* Malloced string that needs to be freed */
4497 Token sColname
; /* Computed column name as a token */
4501 && sqlite3MatchSpanName(pSub
->pEList
->a
[j
].zSpan
, 0, zTName
, 0)==0
4506 /* If a column is marked as 'hidden', omit it from the expanded
4507 ** result-set list unless the SELECT has the SF_IncludeHidden
4510 if( (p
->selFlags
& SF_IncludeHidden
)==0
4511 && IsHiddenColumn(&pTab
->aCol
[j
])
4517 if( i
>0 && zTName
==0 ){
4518 if( (pFrom
->fg
.jointype
& JT_NATURAL
)!=0
4519 && tableAndColumnIndex(pTabList
, i
, zName
, 0, 0)
4521 /* In a NATURAL join, omit the join columns from the
4522 ** table to the right of the join */
4525 if( sqlite3IdListIndex(pFrom
->pUsing
, zName
)>=0 ){
4526 /* In a join with a USING clause, omit columns in the
4527 ** using clause from the table on the right. */
4531 pRight
= sqlite3Expr(db
, TK_ID
, zName
);
4534 if( longNames
|| pTabList
->nSrc
>1 ){
4536 pLeft
= sqlite3Expr(db
, TK_ID
, zTabName
);
4537 pExpr
= sqlite3PExpr(pParse
, TK_DOT
, pLeft
, pRight
);
4539 pLeft
= sqlite3Expr(db
, TK_ID
, zSchemaName
);
4540 pExpr
= sqlite3PExpr(pParse
, TK_DOT
, pLeft
, pExpr
);
4543 zColname
= sqlite3MPrintf(db
, "%s.%s", zTabName
, zName
);
4549 pNew
= sqlite3ExprListAppend(pParse
, pNew
, pExpr
);
4550 sqlite3TokenInit(&sColname
, zColname
);
4551 sqlite3ExprListSetName(pParse
, pNew
, &sColname
, 0);
4552 if( pNew
&& (p
->selFlags
& SF_NestedFrom
)!=0 ){
4553 struct ExprList_item
*pX
= &pNew
->a
[pNew
->nExpr
-1];
4555 pX
->zSpan
= sqlite3DbStrDup(db
, pSub
->pEList
->a
[j
].zSpan
);
4556 testcase( pX
->zSpan
==0 );
4558 pX
->zSpan
= sqlite3MPrintf(db
, "%s.%s.%s",
4559 zSchemaName
, zTabName
, zColname
);
4560 testcase( pX
->zSpan
==0 );
4564 sqlite3DbFree(db
, zToFree
);
4569 sqlite3ErrorMsg(pParse
, "no such table: %s", zTName
);
4571 sqlite3ErrorMsg(pParse
, "no tables specified");
4576 sqlite3ExprListDelete(db
, pEList
);
4580 if( p
->pEList
->nExpr
>db
->aLimit
[SQLITE_LIMIT_COLUMN
] ){
4581 sqlite3ErrorMsg(pParse
, "too many columns in result set");
4584 if( (elistFlags
& (EP_HasFunc
|EP_Subquery
))!=0 ){
4585 p
->selFlags
|= SF_ComplexResult
;
4588 return WRC_Continue
;
4592 ** No-op routine for the parse-tree walker.
4594 ** When this routine is the Walker.xExprCallback then expression trees
4595 ** are walked without any actions being taken at each node. Presumably,
4596 ** when this routine is used for Walker.xExprCallback then
4597 ** Walker.xSelectCallback is set to do something useful for every
4598 ** subquery in the parser tree.
4600 int sqlite3ExprWalkNoop(Walker
*NotUsed
, Expr
*NotUsed2
){
4601 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
4602 return WRC_Continue
;
4606 ** No-op routine for the parse-tree walker for SELECT statements.
4607 ** subquery in the parser tree.
4609 int sqlite3SelectWalkNoop(Walker
*NotUsed
, Select
*NotUsed2
){
4610 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
4611 return WRC_Continue
;
4616 ** Always assert. This xSelectCallback2 implementation proves that the
4617 ** xSelectCallback2 is never invoked.
4619 void sqlite3SelectWalkAssert2(Walker
*NotUsed
, Select
*NotUsed2
){
4620 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
4625 ** This routine "expands" a SELECT statement and all of its subqueries.
4626 ** For additional information on what it means to "expand" a SELECT
4627 ** statement, see the comment on the selectExpand worker callback above.
4629 ** Expanding a SELECT statement is the first step in processing a
4630 ** SELECT statement. The SELECT statement must be expanded before
4631 ** name resolution is performed.
4633 ** If anything goes wrong, an error message is written into pParse.
4634 ** The calling function can detect the problem by looking at pParse->nErr
4635 ** and/or pParse->db->mallocFailed.
4637 static void sqlite3SelectExpand(Parse
*pParse
, Select
*pSelect
){
4639 w
.xExprCallback
= sqlite3ExprWalkNoop
;
4641 if( OK_IF_ALWAYS_TRUE(pParse
->hasCompound
) ){
4642 w
.xSelectCallback
= convertCompoundSelectToSubquery
;
4643 w
.xSelectCallback2
= 0;
4644 sqlite3WalkSelect(&w
, pSelect
);
4646 w
.xSelectCallback
= selectExpander
;
4647 w
.xSelectCallback2
= selectPopWith
;
4648 sqlite3WalkSelect(&w
, pSelect
);
4652 #ifndef SQLITE_OMIT_SUBQUERY
4654 ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()
4657 ** For each FROM-clause subquery, add Column.zType and Column.zColl
4658 ** information to the Table structure that represents the result set
4659 ** of that subquery.
4661 ** The Table structure that represents the result set was constructed
4662 ** by selectExpander() but the type and collation information was omitted
4663 ** at that point because identifiers had not yet been resolved. This
4664 ** routine is called after identifier resolution.
4666 static void selectAddSubqueryTypeInfo(Walker
*pWalker
, Select
*p
){
4670 struct SrcList_item
*pFrom
;
4672 assert( p
->selFlags
& SF_Resolved
);
4673 assert( (p
->selFlags
& SF_HasTypeInfo
)==0 );
4674 p
->selFlags
|= SF_HasTypeInfo
;
4675 pParse
= pWalker
->pParse
;
4677 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
4678 Table
*pTab
= pFrom
->pTab
;
4680 if( (pTab
->tabFlags
& TF_Ephemeral
)!=0 ){
4681 /* A sub-query in the FROM clause of a SELECT */
4682 Select
*pSel
= pFrom
->pSelect
;
4684 while( pSel
->pPrior
) pSel
= pSel
->pPrior
;
4685 sqlite3SelectAddColumnTypeAndCollation(pParse
, pTab
, pSel
);
4694 ** This routine adds datatype and collating sequence information to
4695 ** the Table structures of all FROM-clause subqueries in a
4696 ** SELECT statement.
4698 ** Use this routine after name resolution.
4700 static void sqlite3SelectAddTypeInfo(Parse
*pParse
, Select
*pSelect
){
4701 #ifndef SQLITE_OMIT_SUBQUERY
4703 w
.xSelectCallback
= sqlite3SelectWalkNoop
;
4704 w
.xSelectCallback2
= selectAddSubqueryTypeInfo
;
4705 w
.xExprCallback
= sqlite3ExprWalkNoop
;
4707 sqlite3WalkSelect(&w
, pSelect
);
4713 ** This routine sets up a SELECT statement for processing. The
4714 ** following is accomplished:
4716 ** * VDBE Cursor numbers are assigned to all FROM-clause terms.
4717 ** * Ephemeral Table objects are created for all FROM-clause subqueries.
4718 ** * ON and USING clauses are shifted into WHERE statements
4719 ** * Wildcards "*" and "TABLE.*" in result sets are expanded.
4720 ** * Identifiers in expression are matched to tables.
4722 ** This routine acts recursively on all subqueries within the SELECT.
4724 void sqlite3SelectPrep(
4725 Parse
*pParse
, /* The parser context */
4726 Select
*p
, /* The SELECT statement being coded. */
4727 NameContext
*pOuterNC
/* Name context for container */
4729 assert( p
!=0 || pParse
->db
->mallocFailed
);
4730 if( pParse
->db
->mallocFailed
) return;
4731 if( p
->selFlags
& SF_HasTypeInfo
) return;
4732 sqlite3SelectExpand(pParse
, p
);
4733 if( pParse
->nErr
|| pParse
->db
->mallocFailed
) return;
4734 sqlite3ResolveSelectNames(pParse
, p
, pOuterNC
);
4735 if( pParse
->nErr
|| pParse
->db
->mallocFailed
) return;
4736 sqlite3SelectAddTypeInfo(pParse
, p
);
4740 ** Reset the aggregate accumulator.
4742 ** The aggregate accumulator is a set of memory cells that hold
4743 ** intermediate results while calculating an aggregate. This
4744 ** routine generates code that stores NULLs in all of those memory
4747 static void resetAccumulator(Parse
*pParse
, AggInfo
*pAggInfo
){
4748 Vdbe
*v
= pParse
->pVdbe
;
4750 struct AggInfo_func
*pFunc
;
4751 int nReg
= pAggInfo
->nFunc
+ pAggInfo
->nColumn
;
4752 if( nReg
==0 ) return;
4754 /* Verify that all AggInfo registers are within the range specified by
4755 ** AggInfo.mnReg..AggInfo.mxReg */
4756 assert( nReg
==pAggInfo
->mxReg
-pAggInfo
->mnReg
+1 );
4757 for(i
=0; i
<pAggInfo
->nColumn
; i
++){
4758 assert( pAggInfo
->aCol
[i
].iMem
>=pAggInfo
->mnReg
4759 && pAggInfo
->aCol
[i
].iMem
<=pAggInfo
->mxReg
);
4761 for(i
=0; i
<pAggInfo
->nFunc
; i
++){
4762 assert( pAggInfo
->aFunc
[i
].iMem
>=pAggInfo
->mnReg
4763 && pAggInfo
->aFunc
[i
].iMem
<=pAggInfo
->mxReg
);
4766 sqlite3VdbeAddOp3(v
, OP_Null
, 0, pAggInfo
->mnReg
, pAggInfo
->mxReg
);
4767 for(pFunc
=pAggInfo
->aFunc
, i
=0; i
<pAggInfo
->nFunc
; i
++, pFunc
++){
4768 if( pFunc
->iDistinct
>=0 ){
4769 Expr
*pE
= pFunc
->pExpr
;
4770 assert( !ExprHasProperty(pE
, EP_xIsSelect
) );
4771 if( pE
->x
.pList
==0 || pE
->x
.pList
->nExpr
!=1 ){
4772 sqlite3ErrorMsg(pParse
, "DISTINCT aggregates must have exactly one "
4774 pFunc
->iDistinct
= -1;
4776 KeyInfo
*pKeyInfo
= keyInfoFromExprList(pParse
, pE
->x
.pList
, 0, 0);
4777 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
, pFunc
->iDistinct
, 0, 0,
4778 (char*)pKeyInfo
, P4_KEYINFO
);
4785 ** Invoke the OP_AggFinalize opcode for every aggregate function
4786 ** in the AggInfo structure.
4788 static void finalizeAggFunctions(Parse
*pParse
, AggInfo
*pAggInfo
){
4789 Vdbe
*v
= pParse
->pVdbe
;
4791 struct AggInfo_func
*pF
;
4792 for(i
=0, pF
=pAggInfo
->aFunc
; i
<pAggInfo
->nFunc
; i
++, pF
++){
4793 ExprList
*pList
= pF
->pExpr
->x
.pList
;
4794 assert( !ExprHasProperty(pF
->pExpr
, EP_xIsSelect
) );
4795 sqlite3VdbeAddOp2(v
, OP_AggFinal
, pF
->iMem
, pList
? pList
->nExpr
: 0);
4796 sqlite3VdbeAppendP4(v
, pF
->pFunc
, P4_FUNCDEF
);
4801 ** Update the accumulator memory cells for an aggregate based on
4802 ** the current cursor position.
4804 static void updateAccumulator(Parse
*pParse
, AggInfo
*pAggInfo
){
4805 Vdbe
*v
= pParse
->pVdbe
;
4808 int addrHitTest
= 0;
4809 struct AggInfo_func
*pF
;
4810 struct AggInfo_col
*pC
;
4812 pAggInfo
->directMode
= 1;
4813 for(i
=0, pF
=pAggInfo
->aFunc
; i
<pAggInfo
->nFunc
; i
++, pF
++){
4817 ExprList
*pList
= pF
->pExpr
->x
.pList
;
4818 assert( !ExprHasProperty(pF
->pExpr
, EP_xIsSelect
) );
4820 nArg
= pList
->nExpr
;
4821 regAgg
= sqlite3GetTempRange(pParse
, nArg
);
4822 sqlite3ExprCodeExprList(pParse
, pList
, regAgg
, 0, SQLITE_ECEL_DUP
);
4827 if( pF
->iDistinct
>=0 ){
4828 addrNext
= sqlite3VdbeMakeLabel(v
);
4829 testcase( nArg
==0 ); /* Error condition */
4830 testcase( nArg
>1 ); /* Also an error */
4831 codeDistinct(pParse
, pF
->iDistinct
, addrNext
, 1, regAgg
);
4833 if( pF
->pFunc
->funcFlags
& SQLITE_FUNC_NEEDCOLL
){
4835 struct ExprList_item
*pItem
;
4837 assert( pList
!=0 ); /* pList!=0 if pF->pFunc has NEEDCOLL */
4838 for(j
=0, pItem
=pList
->a
; !pColl
&& j
<nArg
; j
++, pItem
++){
4839 pColl
= sqlite3ExprCollSeq(pParse
, pItem
->pExpr
);
4842 pColl
= pParse
->db
->pDfltColl
;
4844 if( regHit
==0 && pAggInfo
->nAccumulator
) regHit
= ++pParse
->nMem
;
4845 sqlite3VdbeAddOp4(v
, OP_CollSeq
, regHit
, 0, 0, (char *)pColl
, P4_COLLSEQ
);
4847 sqlite3VdbeAddOp3(v
, OP_AggStep0
, 0, regAgg
, pF
->iMem
);
4848 sqlite3VdbeAppendP4(v
, pF
->pFunc
, P4_FUNCDEF
);
4849 sqlite3VdbeChangeP5(v
, (u8
)nArg
);
4850 sqlite3ExprCacheAffinityChange(pParse
, regAgg
, nArg
);
4851 sqlite3ReleaseTempRange(pParse
, regAgg
, nArg
);
4853 sqlite3VdbeResolveLabel(v
, addrNext
);
4854 sqlite3ExprCacheClear(pParse
);
4858 /* Before populating the accumulator registers, clear the column cache.
4859 ** Otherwise, if any of the required column values are already present
4860 ** in registers, sqlite3ExprCode() may use OP_SCopy to copy the value
4861 ** to pC->iMem. But by the time the value is used, the original register
4862 ** may have been used, invalidating the underlying buffer holding the
4863 ** text or blob value. See ticket [883034dcb5].
4865 ** Another solution would be to change the OP_SCopy used to copy cached
4866 ** values to an OP_Copy.
4869 addrHitTest
= sqlite3VdbeAddOp1(v
, OP_If
, regHit
); VdbeCoverage(v
);
4871 sqlite3ExprCacheClear(pParse
);
4872 for(i
=0, pC
=pAggInfo
->aCol
; i
<pAggInfo
->nAccumulator
; i
++, pC
++){
4873 sqlite3ExprCode(pParse
, pC
->pExpr
, pC
->iMem
);
4875 pAggInfo
->directMode
= 0;
4876 sqlite3ExprCacheClear(pParse
);
4878 sqlite3VdbeJumpHere(v
, addrHitTest
);
4883 ** Add a single OP_Explain instruction to the VDBE to explain a simple
4884 ** count(*) query ("SELECT count(*) FROM pTab").
4886 #ifndef SQLITE_OMIT_EXPLAIN
4887 static void explainSimpleCount(
4888 Parse
*pParse
, /* Parse context */
4889 Table
*pTab
, /* Table being queried */
4890 Index
*pIdx
/* Index used to optimize scan, or NULL */
4892 if( pParse
->explain
==2 ){
4893 int bCover
= (pIdx
!=0 && (HasRowid(pTab
) || !IsPrimaryKeyIndex(pIdx
)));
4894 char *zEqp
= sqlite3MPrintf(pParse
->db
, "SCAN TABLE %s%s%s",
4896 bCover
? " USING COVERING INDEX " : "",
4897 bCover
? pIdx
->zName
: ""
4900 pParse
->pVdbe
, OP_Explain
, pParse
->iSelectId
, 0, 0, zEqp
, P4_DYNAMIC
4905 # define explainSimpleCount(a,b,c)
4909 ** Context object for havingToWhereExprCb().
4911 struct HavingToWhereCtx
{
4917 ** sqlite3WalkExpr() callback used by havingToWhere().
4919 ** If the node passed to the callback is a TK_AND node, return
4920 ** WRC_Continue to tell sqlite3WalkExpr() to iterate through child nodes.
4922 ** Otherwise, return WRC_Prune. In this case, also check if the
4923 ** sub-expression matches the criteria for being moved to the WHERE
4924 ** clause. If so, add it to the WHERE clause and replace the sub-expression
4925 ** within the HAVING expression with a constant "1".
4927 static int havingToWhereExprCb(Walker
*pWalker
, Expr
*pExpr
){
4928 if( pExpr
->op
!=TK_AND
){
4929 struct HavingToWhereCtx
*p
= pWalker
->u
.pHavingCtx
;
4930 if( sqlite3ExprIsConstantOrGroupBy(pWalker
->pParse
, pExpr
, p
->pGroupBy
) ){
4931 sqlite3
*db
= pWalker
->pParse
->db
;
4932 Expr
*pNew
= sqlite3ExprAlloc(db
, TK_INTEGER
, &sqlite3IntTokens
[1], 0);
4934 Expr
*pWhere
= *(p
->ppWhere
);
4935 SWAP(Expr
, *pNew
, *pExpr
);
4936 pNew
= sqlite3ExprAnd(db
, pWhere
, pNew
);
4937 *(p
->ppWhere
) = pNew
;
4942 return WRC_Continue
;
4946 ** Transfer eligible terms from the HAVING clause of a query, which is
4947 ** processed after grouping, to the WHERE clause, which is processed before
4948 ** grouping. For example, the query:
4950 ** SELECT * FROM <tables> WHERE a=? GROUP BY b HAVING b=? AND c=?
4952 ** can be rewritten as:
4954 ** SELECT * FROM <tables> WHERE a=? AND b=? GROUP BY b HAVING c=?
4956 ** A term of the HAVING expression is eligible for transfer if it consists
4957 ** entirely of constants and expressions that are also GROUP BY terms that
4958 ** use the "BINARY" collation sequence.
4960 static void havingToWhere(
4966 struct HavingToWhereCtx sCtx
;
4969 sCtx
.ppWhere
= ppWhere
;
4970 sCtx
.pGroupBy
= pGroupBy
;
4972 memset(&sWalker
, 0, sizeof(sWalker
));
4973 sWalker
.pParse
= pParse
;
4974 sWalker
.xExprCallback
= havingToWhereExprCb
;
4975 sWalker
.u
.pHavingCtx
= &sCtx
;
4976 sqlite3WalkExpr(&sWalker
, pHaving
);
4980 ** Check to see if the pThis entry of pTabList is a self-join of a prior view.
4981 ** If it is, then return the SrcList_item for the prior view. If it is not,
4984 static struct SrcList_item
*isSelfJoinView(
4985 SrcList
*pTabList
, /* Search for self-joins in this FROM clause */
4986 struct SrcList_item
*pThis
/* Search for prior reference to this subquery */
4988 struct SrcList_item
*pItem
;
4989 for(pItem
= pTabList
->a
; pItem
<pThis
; pItem
++){
4990 if( pItem
->pSelect
==0 ) continue;
4991 if( pItem
->fg
.viaCoroutine
) continue;
4992 if( pItem
->zName
==0 ) continue;
4993 if( sqlite3_stricmp(pItem
->zDatabase
, pThis
->zDatabase
)!=0 ) continue;
4994 if( sqlite3_stricmp(pItem
->zName
, pThis
->zName
)!=0 ) continue;
4995 if( sqlite3ExprCompare(0,
4996 pThis
->pSelect
->pWhere
, pItem
->pSelect
->pWhere
, -1)
4998 /* The view was modified by some other optimization such as
4999 ** pushDownWhereTerms() */
5007 #ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION
5009 ** Attempt to transform a query of the form
5011 ** SELECT count(*) FROM (SELECT x FROM t1 UNION ALL SELECT y FROM t2)
5015 ** SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)
5017 ** The transformation only works if all of the following are true:
5019 ** * The subquery is a UNION ALL of two or more terms
5020 ** * There is no WHERE or GROUP BY or HAVING clauses on the subqueries
5021 ** * The outer query is a simple count(*)
5023 ** Return TRUE if the optimization is undertaken.
5025 static int countOfViewOptimization(Parse
*pParse
, Select
*p
){
5026 Select
*pSub
, *pPrior
;
5030 if( (p
->selFlags
& SF_Aggregate
)==0 ) return 0; /* This is an aggregate */
5031 if( p
->pEList
->nExpr
!=1 ) return 0; /* Single result column */
5032 pExpr
= p
->pEList
->a
[0].pExpr
;
5033 if( pExpr
->op
!=TK_AGG_FUNCTION
) return 0; /* Result is an aggregate */
5034 if( sqlite3_stricmp(pExpr
->u
.zToken
,"count") ) return 0; /* Is count() */
5035 if( pExpr
->x
.pList
!=0 ) return 0; /* Must be count(*) */
5036 if( p
->pSrc
->nSrc
!=1 ) return 0; /* One table in FROM */
5037 pSub
= p
->pSrc
->a
[0].pSelect
;
5038 if( pSub
==0 ) return 0; /* The FROM is a subquery */
5039 if( pSub
->pPrior
==0 ) return 0; /* Must be a compound ry */
5041 if( pSub
->op
!=TK_ALL
&& pSub
->pPrior
) return 0; /* Must be UNION ALL */
5042 if( pSub
->pWhere
) return 0; /* No WHERE clause */
5043 if( pSub
->selFlags
& SF_Aggregate
) return 0; /* Not an aggregate */
5044 pSub
= pSub
->pPrior
; /* Repeat over compound */
5047 /* If we reach this point then it is OK to perform the transformation */
5052 pSub
= p
->pSrc
->a
[0].pSelect
;
5053 p
->pSrc
->a
[0].pSelect
= 0;
5054 sqlite3SrcListDelete(db
, p
->pSrc
);
5055 p
->pSrc
= sqlite3DbMallocZero(pParse
->db
, sizeof(*p
->pSrc
));
5058 pPrior
= pSub
->pPrior
;
5061 pSub
->selFlags
|= SF_Aggregate
;
5062 pSub
->selFlags
&= ~SF_Compound
;
5063 pSub
->nSelectRow
= 0;
5064 sqlite3ExprListDelete(db
, pSub
->pEList
);
5065 pTerm
= pPrior
? sqlite3ExprDup(db
, pCount
, 0) : pCount
;
5066 pSub
->pEList
= sqlite3ExprListAppend(pParse
, 0, pTerm
);
5067 pTerm
= sqlite3PExpr(pParse
, TK_SELECT
, 0, 0);
5068 sqlite3PExprAddSelect(pParse
, pTerm
, pSub
);
5072 pExpr
= sqlite3PExpr(pParse
, TK_PLUS
, pTerm
, pExpr
);
5076 p
->pEList
->a
[0].pExpr
= pExpr
;
5077 p
->selFlags
&= ~SF_Aggregate
;
5079 #if SELECTTRACE_ENABLED
5080 if( sqlite3SelectTrace
& 0x400 ){
5081 SELECTTRACE(0x400,pParse
,p
,("After count-of-view optimization:\n"));
5082 sqlite3TreeViewSelect(0, p
, 0);
5087 #endif /* SQLITE_COUNTOFVIEW_OPTIMIZATION */
5090 ** Generate code for the SELECT statement given in the p argument.
5092 ** The results are returned according to the SelectDest structure.
5093 ** See comments in sqliteInt.h for further information.
5095 ** This routine returns the number of errors. If any errors are
5096 ** encountered, then an appropriate error message is left in
5099 ** This routine does NOT free the Select structure passed in. The
5100 ** calling function needs to do that.
5103 Parse
*pParse
, /* The parser context */
5104 Select
*p
, /* The SELECT statement being coded. */
5105 SelectDest
*pDest
/* What to do with the query results */
5107 int i
, j
; /* Loop counters */
5108 WhereInfo
*pWInfo
; /* Return from sqlite3WhereBegin() */
5109 Vdbe
*v
; /* The virtual machine under construction */
5110 int isAgg
; /* True for select lists like "count(*)" */
5111 ExprList
*pEList
= 0; /* List of columns to extract. */
5112 SrcList
*pTabList
; /* List of tables to select from */
5113 Expr
*pWhere
; /* The WHERE clause. May be NULL */
5114 ExprList
*pGroupBy
; /* The GROUP BY clause. May be NULL */
5115 Expr
*pHaving
; /* The HAVING clause. May be NULL */
5116 int rc
= 1; /* Value to return from this function */
5117 DistinctCtx sDistinct
; /* Info on how to code the DISTINCT keyword */
5118 SortCtx sSort
; /* Info on how to code the ORDER BY clause */
5119 AggInfo sAggInfo
; /* Information used by aggregate queries */
5120 int iEnd
; /* Address of the end of the query */
5121 sqlite3
*db
; /* The database connection */
5122 ExprList
*pMinMaxOrderBy
= 0; /* Added ORDER BY for min/max queries */
5123 u8 minMaxFlag
; /* Flag for min/max queries */
5125 #ifndef SQLITE_OMIT_EXPLAIN
5126 int iRestoreSelectId
= pParse
->iSelectId
;
5127 pParse
->iSelectId
= pParse
->iNextSelectId
++;
5131 if( p
==0 || db
->mallocFailed
|| pParse
->nErr
){
5134 if( sqlite3AuthCheck(pParse
, SQLITE_SELECT
, 0, 0, 0) ) return 1;
5135 memset(&sAggInfo
, 0, sizeof(sAggInfo
));
5136 #if SELECTTRACE_ENABLED
5137 pParse
->nSelectIndent
++;
5138 SELECTTRACE(1,pParse
,p
, ("begin processing:\n"));
5139 if( sqlite3SelectTrace
& 0x100 ){
5140 sqlite3TreeViewSelect(0, p
, 0);
5144 assert( p
->pOrderBy
==0 || pDest
->eDest
!=SRT_DistFifo
);
5145 assert( p
->pOrderBy
==0 || pDest
->eDest
!=SRT_Fifo
);
5146 assert( p
->pOrderBy
==0 || pDest
->eDest
!=SRT_DistQueue
);
5147 assert( p
->pOrderBy
==0 || pDest
->eDest
!=SRT_Queue
);
5148 if( IgnorableOrderby(pDest
) ){
5149 assert(pDest
->eDest
==SRT_Exists
|| pDest
->eDest
==SRT_Union
||
5150 pDest
->eDest
==SRT_Except
|| pDest
->eDest
==SRT_Discard
||
5151 pDest
->eDest
==SRT_Queue
|| pDest
->eDest
==SRT_DistFifo
||
5152 pDest
->eDest
==SRT_DistQueue
|| pDest
->eDest
==SRT_Fifo
);
5153 /* If ORDER BY makes no difference in the output then neither does
5154 ** DISTINCT so it can be removed too. */
5155 sqlite3ExprListDelete(db
, p
->pOrderBy
);
5157 p
->selFlags
&= ~SF_Distinct
;
5159 sqlite3SelectPrep(pParse
, p
, 0);
5160 memset(&sSort
, 0, sizeof(sSort
));
5161 sSort
.pOrderBy
= p
->pOrderBy
;
5163 if( pParse
->nErr
|| db
->mallocFailed
){
5166 assert( p
->pEList
!=0 );
5167 isAgg
= (p
->selFlags
& SF_Aggregate
)!=0;
5168 #if SELECTTRACE_ENABLED
5169 if( sqlite3SelectTrace
& 0x100 ){
5170 SELECTTRACE(0x100,pParse
,p
, ("after name resolution:\n"));
5171 sqlite3TreeViewSelect(0, p
, 0);
5175 /* Get a pointer the VDBE under construction, allocating a new VDBE if one
5176 ** does not already exist */
5177 v
= sqlite3GetVdbe(pParse
);
5178 if( v
==0 ) goto select_end
;
5179 if( pDest
->eDest
==SRT_Output
){
5180 generateColumnNames(pParse
, p
);
5183 /* Try to flatten subqueries in the FROM clause up into the main query
5185 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
5186 for(i
=0; !p
->pPrior
&& i
<pTabList
->nSrc
; i
++){
5187 struct SrcList_item
*pItem
= &pTabList
->a
[i
];
5188 Select
*pSub
= pItem
->pSelect
;
5189 Table
*pTab
= pItem
->pTab
;
5190 if( pSub
==0 ) continue;
5192 /* Catch mismatch in the declared columns of a view and the number of
5193 ** columns in the SELECT on the RHS */
5194 if( pTab
->nCol
!=pSub
->pEList
->nExpr
){
5195 sqlite3ErrorMsg(pParse
, "expected %d columns for '%s' but got %d",
5196 pTab
->nCol
, pTab
->zName
, pSub
->pEList
->nExpr
);
5200 /* Do not try to flatten an aggregate subquery.
5202 ** Flattening an aggregate subquery is only possible if the outer query
5203 ** is not a join. But if the outer query is not a join, then the subquery
5204 ** will be implemented as a co-routine and there is no advantage to
5205 ** flattening in that case.
5207 if( (pSub
->selFlags
& SF_Aggregate
)!=0 ) continue;
5208 assert( pSub
->pGroupBy
==0 );
5210 /* If the outer query contains a "complex" result set (that is,
5211 ** if the result set of the outer query uses functions or subqueries)
5212 ** and if the subquery contains an ORDER BY clause and if
5213 ** it will be implemented as a co-routine, then do not flatten. This
5214 ** restriction allows SQL constructs like this:
5216 ** SELECT expensive_function(x)
5217 ** FROM (SELECT x FROM tab ORDER BY y LIMIT 10);
5219 ** The expensive_function() is only computed on the 10 rows that
5220 ** are output, rather than every row of the table.
5222 ** The requirement that the outer query have a complex result set
5223 ** means that flattening does occur on simpler SQL constraints without
5224 ** the expensive_function() like:
5226 ** SELECT x FROM (SELECT x FROM tab ORDER BY y LIMIT 10);
5228 if( pSub
->pOrderBy
!=0
5230 && (p
->selFlags
& SF_ComplexResult
)!=0
5231 && (pTabList
->nSrc
==1
5232 || (pTabList
->a
[1].fg
.jointype
&(JT_LEFT
|JT_CROSS
))!=0)
5237 if( flattenSubquery(pParse
, p
, i
, isAgg
) ){
5238 /* This subquery can be absorbed into its parent. */
5242 if( db
->mallocFailed
) goto select_end
;
5243 if( !IgnorableOrderby(pDest
) ){
5244 sSort
.pOrderBy
= p
->pOrderBy
;
5249 #ifndef SQLITE_OMIT_COMPOUND_SELECT
5250 /* Handle compound SELECT statements using the separate multiSelect()
5254 rc
= multiSelect(pParse
, p
, pDest
);
5255 explainSetInteger(pParse
->iSelectId
, iRestoreSelectId
);
5256 #if SELECTTRACE_ENABLED
5257 SELECTTRACE(1,pParse
,p
,("end compound-select processing\n"));
5258 pParse
->nSelectIndent
--;
5264 /* For each term in the FROM clause, do two things:
5265 ** (1) Authorized unreferenced tables
5266 ** (2) Generate code for all sub-queries
5268 for(i
=0; i
<pTabList
->nSrc
; i
++){
5269 struct SrcList_item
*pItem
= &pTabList
->a
[i
];
5272 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
5273 const char *zSavedAuthContext
;
5276 /* Issue SQLITE_READ authorizations with a fake column name for any
5277 ** tables that are referenced but from which no values are extracted.
5278 ** Examples of where these kinds of null SQLITE_READ authorizations
5281 ** SELECT count(*) FROM t1; -- SQLITE_READ t1.""
5282 ** SELECT t1.* FROM t1, t2; -- SQLITE_READ t2.""
5284 ** The fake column name is an empty string. It is possible for a table to
5285 ** have a column named by the empty string, in which case there is no way to
5286 ** distinguish between an unreferenced table and an actual reference to the
5287 ** "" column. The original design was for the fake column name to be a NULL,
5288 ** which would be unambiguous. But legacy authorization callbacks might
5289 ** assume the column name is non-NULL and segfault. The use of an empty
5290 ** string for the fake column name seems safer.
5292 if( pItem
->colUsed
==0 ){
5293 sqlite3AuthCheck(pParse
, SQLITE_READ
, pItem
->zName
, "", pItem
->zDatabase
);
5296 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
5297 /* Generate code for all sub-queries in the FROM clause
5299 pSub
= pItem
->pSelect
;
5300 if( pSub
==0 ) continue;
5302 /* Sometimes the code for a subquery will be generated more than
5303 ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
5304 ** for example. In that case, do not regenerate the code to manifest
5305 ** a view or the co-routine to implement a view. The first instance
5306 ** is sufficient, though the subroutine to manifest the view does need
5307 ** to be invoked again. */
5308 if( pItem
->addrFillSub
){
5309 if( pItem
->fg
.viaCoroutine
==0 ){
5310 /* The subroutine that manifests the view might be a one-time routine,
5311 ** or it might need to be rerun on each iteration because it
5312 ** encodes a correlated subquery. */
5313 testcase( sqlite3VdbeGetOp(v
, pItem
->addrFillSub
)->opcode
==OP_Once
);
5314 sqlite3VdbeAddOp2(v
, OP_Gosub
, pItem
->regReturn
, pItem
->addrFillSub
);
5319 /* Increment Parse.nHeight by the height of the largest expression
5320 ** tree referred to by this, the parent select. The child select
5321 ** may contain expression trees of at most
5322 ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
5323 ** more conservative than necessary, but much easier than enforcing
5326 pParse
->nHeight
+= sqlite3SelectExprHeight(p
);
5328 /* Make copies of constant WHERE-clause terms in the outer query down
5329 ** inside the subquery. This can help the subquery to run more efficiently.
5331 if( (pItem
->fg
.jointype
& JT_OUTER
)==0
5332 && pushDownWhereTerms(pParse
, pSub
, p
->pWhere
, pItem
->iCursor
)
5334 #if SELECTTRACE_ENABLED
5335 if( sqlite3SelectTrace
& 0x100 ){
5336 SELECTTRACE(0x100,pParse
,p
,("After WHERE-clause push-down:\n"));
5337 sqlite3TreeViewSelect(0, p
, 0);
5342 zSavedAuthContext
= pParse
->zAuthContext
;
5343 pParse
->zAuthContext
= pItem
->zName
;
5345 /* Generate code to implement the subquery
5347 ** The subquery is implemented as a co-routine if the subquery is
5348 ** guaranteed to be the outer loop (so that it does not need to be
5349 ** computed more than once)
5351 ** TODO: Are there other reasons beside (1) to use a co-routine
5355 && (pTabList
->nSrc
==1
5356 || (pTabList
->a
[1].fg
.jointype
&(JT_LEFT
|JT_CROSS
))!=0) /* (1) */
5358 /* Implement a co-routine that will return a single row of the result
5359 ** set on each invocation.
5361 int addrTop
= sqlite3VdbeCurrentAddr(v
)+1;
5363 pItem
->regReturn
= ++pParse
->nMem
;
5364 sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, pItem
->regReturn
, 0, addrTop
);
5365 VdbeComment((v
, "%s", pItem
->pTab
->zName
));
5366 pItem
->addrFillSub
= addrTop
;
5367 sqlite3SelectDestInit(&dest
, SRT_Coroutine
, pItem
->regReturn
);
5368 explainSetInteger(pItem
->iSelectId
, (u8
)pParse
->iNextSelectId
);
5369 sqlite3Select(pParse
, pSub
, &dest
);
5370 pItem
->pTab
->nRowLogEst
= pSub
->nSelectRow
;
5371 pItem
->fg
.viaCoroutine
= 1;
5372 pItem
->regResult
= dest
.iSdst
;
5373 sqlite3VdbeEndCoroutine(v
, pItem
->regReturn
);
5374 sqlite3VdbeJumpHere(v
, addrTop
-1);
5375 sqlite3ClearTempRegCache(pParse
);
5377 /* Generate a subroutine that will fill an ephemeral table with
5378 ** the content of this subquery. pItem->addrFillSub will point
5379 ** to the address of the generated subroutine. pItem->regReturn
5380 ** is a register allocated to hold the subroutine return address
5385 struct SrcList_item
*pPrior
;
5387 assert( pItem
->addrFillSub
==0 );
5388 pItem
->regReturn
= ++pParse
->nMem
;
5389 topAddr
= sqlite3VdbeAddOp2(v
, OP_Integer
, 0, pItem
->regReturn
);
5390 pItem
->addrFillSub
= topAddr
+1;
5391 if( pItem
->fg
.isCorrelated
==0 ){
5392 /* If the subquery is not correlated and if we are not inside of
5393 ** a trigger, then we only need to compute the value of the subquery
5395 onceAddr
= sqlite3VdbeAddOp0(v
, OP_Once
); VdbeCoverage(v
);
5396 VdbeComment((v
, "materialize \"%s\"", pItem
->pTab
->zName
));
5398 VdbeNoopComment((v
, "materialize \"%s\"", pItem
->pTab
->zName
));
5400 pPrior
= isSelfJoinView(pTabList
, pItem
);
5402 sqlite3VdbeAddOp2(v
, OP_OpenDup
, pItem
->iCursor
, pPrior
->iCursor
);
5403 explainSetInteger(pItem
->iSelectId
, pPrior
->iSelectId
);
5404 assert( pPrior
->pSelect
!=0 );
5405 pSub
->nSelectRow
= pPrior
->pSelect
->nSelectRow
;
5407 sqlite3SelectDestInit(&dest
, SRT_EphemTab
, pItem
->iCursor
);
5408 explainSetInteger(pItem
->iSelectId
, (u8
)pParse
->iNextSelectId
);
5409 sqlite3Select(pParse
, pSub
, &dest
);
5411 pItem
->pTab
->nRowLogEst
= pSub
->nSelectRow
;
5412 if( onceAddr
) sqlite3VdbeJumpHere(v
, onceAddr
);
5413 retAddr
= sqlite3VdbeAddOp1(v
, OP_Return
, pItem
->regReturn
);
5414 VdbeComment((v
, "end %s", pItem
->pTab
->zName
));
5415 sqlite3VdbeChangeP1(v
, topAddr
, retAddr
);
5416 sqlite3ClearTempRegCache(pParse
);
5418 if( db
->mallocFailed
) goto select_end
;
5419 pParse
->nHeight
-= sqlite3SelectExprHeight(p
);
5420 pParse
->zAuthContext
= zSavedAuthContext
;
5424 /* Various elements of the SELECT copied into local variables for
5428 pGroupBy
= p
->pGroupBy
;
5429 pHaving
= p
->pHaving
;
5430 sDistinct
.isTnct
= (p
->selFlags
& SF_Distinct
)!=0;
5432 #if SELECTTRACE_ENABLED
5433 if( sqlite3SelectTrace
& 0x400 ){
5434 SELECTTRACE(0x400,pParse
,p
,("After all FROM-clause analysis:\n"));
5435 sqlite3TreeViewSelect(0, p
, 0);
5439 #ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION
5440 if( OptimizationEnabled(db
, SQLITE_QueryFlattener
|SQLITE_CountOfView
)
5441 && countOfViewOptimization(pParse
, p
)
5443 if( db
->mallocFailed
) goto select_end
;
5449 /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and
5450 ** if the select-list is the same as the ORDER BY list, then this query
5451 ** can be rewritten as a GROUP BY. In other words, this:
5453 ** SELECT DISTINCT xyz FROM ... ORDER BY xyz
5455 ** is transformed to:
5457 ** SELECT xyz FROM ... GROUP BY xyz ORDER BY xyz
5459 ** The second form is preferred as a single index (or temp-table) may be
5460 ** used for both the ORDER BY and DISTINCT processing. As originally
5461 ** written the query must use a temp-table for at least one of the ORDER
5462 ** BY and DISTINCT, and an index or separate temp-table for the other.
5464 if( (p
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Distinct
5465 && sqlite3ExprListCompare(sSort
.pOrderBy
, pEList
, -1)==0
5467 p
->selFlags
&= ~SF_Distinct
;
5468 pGroupBy
= p
->pGroupBy
= sqlite3ExprListDup(db
, pEList
, 0);
5469 /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
5470 ** the sDistinct.isTnct is still set. Hence, isTnct represents the
5471 ** original setting of the SF_Distinct flag, not the current setting */
5472 assert( sDistinct
.isTnct
);
5474 #if SELECTTRACE_ENABLED
5475 if( sqlite3SelectTrace
& 0x400 ){
5476 SELECTTRACE(0x400,pParse
,p
,("Transform DISTINCT into GROUP BY:\n"));
5477 sqlite3TreeViewSelect(0, p
, 0);
5482 /* If there is an ORDER BY clause, then create an ephemeral index to
5483 ** do the sorting. But this sorting ephemeral index might end up
5484 ** being unused if the data can be extracted in pre-sorted order.
5485 ** If that is the case, then the OP_OpenEphemeral instruction will be
5486 ** changed to an OP_Noop once we figure out that the sorting index is
5487 ** not needed. The sSort.addrSortIndex variable is used to facilitate
5490 if( sSort
.pOrderBy
){
5492 pKeyInfo
= keyInfoFromExprList(pParse
, sSort
.pOrderBy
, 0, pEList
->nExpr
);
5493 sSort
.iECursor
= pParse
->nTab
++;
5494 sSort
.addrSortIndex
=
5495 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
,
5496 sSort
.iECursor
, sSort
.pOrderBy
->nExpr
+1+pEList
->nExpr
, 0,
5497 (char*)pKeyInfo
, P4_KEYINFO
5500 sSort
.addrSortIndex
= -1;
5503 /* If the output is destined for a temporary table, open that table.
5505 if( pDest
->eDest
==SRT_EphemTab
){
5506 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, pDest
->iSDParm
, pEList
->nExpr
);
5511 iEnd
= sqlite3VdbeMakeLabel(v
);
5512 if( (p
->selFlags
& SF_FixedLimit
)==0 ){
5513 p
->nSelectRow
= 320; /* 4 billion rows */
5515 computeLimitRegisters(pParse
, p
, iEnd
);
5516 if( p
->iLimit
==0 && sSort
.addrSortIndex
>=0 ){
5517 sqlite3VdbeChangeOpcode(v
, sSort
.addrSortIndex
, OP_SorterOpen
);
5518 sSort
.sortFlags
|= SORTFLAG_UseSorter
;
5521 /* Open an ephemeral index to use for the distinct set.
5523 if( p
->selFlags
& SF_Distinct
){
5524 sDistinct
.tabTnct
= pParse
->nTab
++;
5525 sDistinct
.addrTnct
= sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
,
5526 sDistinct
.tabTnct
, 0, 0,
5527 (char*)keyInfoFromExprList(pParse
, p
->pEList
,0,0),
5529 sqlite3VdbeChangeP5(v
, BTREE_UNORDERED
);
5530 sDistinct
.eTnctType
= WHERE_DISTINCT_UNORDERED
;
5532 sDistinct
.eTnctType
= WHERE_DISTINCT_NOOP
;
5535 if( !isAgg
&& pGroupBy
==0 ){
5536 /* No aggregate functions and no GROUP BY clause */
5537 u16 wctrlFlags
= (sDistinct
.isTnct
? WHERE_WANT_DISTINCT
: 0);
5538 assert( WHERE_USE_LIMIT
==SF_FixedLimit
);
5539 wctrlFlags
|= p
->selFlags
& SF_FixedLimit
;
5541 /* Begin the database scan. */
5542 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, sSort
.pOrderBy
,
5543 p
->pEList
, wctrlFlags
, p
->nSelectRow
);
5544 if( pWInfo
==0 ) goto select_end
;
5545 if( sqlite3WhereOutputRowCount(pWInfo
) < p
->nSelectRow
){
5546 p
->nSelectRow
= sqlite3WhereOutputRowCount(pWInfo
);
5548 if( sDistinct
.isTnct
&& sqlite3WhereIsDistinct(pWInfo
) ){
5549 sDistinct
.eTnctType
= sqlite3WhereIsDistinct(pWInfo
);
5551 if( sSort
.pOrderBy
){
5552 sSort
.nOBSat
= sqlite3WhereIsOrdered(pWInfo
);
5553 sSort
.bOrderedInnerLoop
= sqlite3WhereOrderedInnerLoop(pWInfo
);
5554 if( sSort
.nOBSat
==sSort
.pOrderBy
->nExpr
){
5559 /* If sorting index that was created by a prior OP_OpenEphemeral
5560 ** instruction ended up not being needed, then change the OP_OpenEphemeral
5563 if( sSort
.addrSortIndex
>=0 && sSort
.pOrderBy
==0 ){
5564 sqlite3VdbeChangeToNoop(v
, sSort
.addrSortIndex
);
5567 /* Use the standard inner loop. */
5568 assert( p
->pEList
==pEList
);
5569 selectInnerLoop(pParse
, p
, -1, &sSort
, &sDistinct
, pDest
,
5570 sqlite3WhereContinueLabel(pWInfo
),
5571 sqlite3WhereBreakLabel(pWInfo
));
5573 /* End the database scan loop.
5575 sqlite3WhereEnd(pWInfo
);
5577 /* This case when there exist aggregate functions or a GROUP BY clause
5579 NameContext sNC
; /* Name context for processing aggregate information */
5580 int iAMem
; /* First Mem address for storing current GROUP BY */
5581 int iBMem
; /* First Mem address for previous GROUP BY */
5582 int iUseFlag
; /* Mem address holding flag indicating that at least
5583 ** one row of the input to the aggregator has been
5585 int iAbortFlag
; /* Mem address which causes query abort if positive */
5586 int groupBySort
; /* Rows come from source in GROUP BY order */
5587 int addrEnd
; /* End of processing for this SELECT */
5588 int sortPTab
= 0; /* Pseudotable used to decode sorting results */
5589 int sortOut
= 0; /* Output register from the sorter */
5590 int orderByGrp
= 0; /* True if the GROUP BY and ORDER BY are the same */
5592 /* Remove any and all aliases between the result set and the
5596 int k
; /* Loop counter */
5597 struct ExprList_item
*pItem
; /* For looping over expression in a list */
5599 for(k
=p
->pEList
->nExpr
, pItem
=p
->pEList
->a
; k
>0; k
--, pItem
++){
5600 pItem
->u
.x
.iAlias
= 0;
5602 for(k
=pGroupBy
->nExpr
, pItem
=pGroupBy
->a
; k
>0; k
--, pItem
++){
5603 pItem
->u
.x
.iAlias
= 0;
5605 assert( 66==sqlite3LogEst(100) );
5606 if( p
->nSelectRow
>66 ) p
->nSelectRow
= 66;
5608 assert( 0==sqlite3LogEst(1) );
5612 /* If there is both a GROUP BY and an ORDER BY clause and they are
5613 ** identical, then it may be possible to disable the ORDER BY clause
5614 ** on the grounds that the GROUP BY will cause elements to come out
5615 ** in the correct order. It also may not - the GROUP BY might use a
5616 ** database index that causes rows to be grouped together as required
5617 ** but not actually sorted. Either way, record the fact that the
5618 ** ORDER BY and GROUP BY clauses are the same by setting the orderByGrp
5620 if( sqlite3ExprListCompare(pGroupBy
, sSort
.pOrderBy
, -1)==0 ){
5624 /* Create a label to jump to when we want to abort the query */
5625 addrEnd
= sqlite3VdbeMakeLabel(v
);
5627 /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
5628 ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
5629 ** SELECT statement.
5631 memset(&sNC
, 0, sizeof(sNC
));
5632 sNC
.pParse
= pParse
;
5633 sNC
.pSrcList
= pTabList
;
5634 sNC
.pAggInfo
= &sAggInfo
;
5635 sAggInfo
.mnReg
= pParse
->nMem
+1;
5636 sAggInfo
.nSortingColumn
= pGroupBy
? pGroupBy
->nExpr
: 0;
5637 sAggInfo
.pGroupBy
= pGroupBy
;
5638 sqlite3ExprAnalyzeAggList(&sNC
, pEList
);
5639 sqlite3ExprAnalyzeAggList(&sNC
, sSort
.pOrderBy
);
5642 assert( pWhere
==p
->pWhere
);
5643 havingToWhere(pParse
, pGroupBy
, pHaving
, &p
->pWhere
);
5646 sqlite3ExprAnalyzeAggregates(&sNC
, pHaving
);
5648 sAggInfo
.nAccumulator
= sAggInfo
.nColumn
;
5649 if( p
->pGroupBy
==0 && p
->pHaving
==0 && sAggInfo
.nFunc
==1 ){
5650 minMaxFlag
= minMaxQuery(db
, sAggInfo
.aFunc
[0].pExpr
, &pMinMaxOrderBy
);
5652 minMaxFlag
= WHERE_ORDERBY_NORMAL
;
5654 for(i
=0; i
<sAggInfo
.nFunc
; i
++){
5655 assert( !ExprHasProperty(sAggInfo
.aFunc
[i
].pExpr
, EP_xIsSelect
) );
5656 sNC
.ncFlags
|= NC_InAggFunc
;
5657 sqlite3ExprAnalyzeAggList(&sNC
, sAggInfo
.aFunc
[i
].pExpr
->x
.pList
);
5658 sNC
.ncFlags
&= ~NC_InAggFunc
;
5660 sAggInfo
.mxReg
= pParse
->nMem
;
5661 if( db
->mallocFailed
) goto select_end
;
5662 #if SELECTTRACE_ENABLED
5663 if( sqlite3SelectTrace
& 0x400 ){
5665 SELECTTRACE(0x400,pParse
,p
,("After aggregate analysis:\n"));
5666 sqlite3TreeViewSelect(0, p
, 0);
5667 for(ii
=0; ii
<sAggInfo
.nColumn
; ii
++){
5668 sqlite3DebugPrintf("agg-column[%d] iMem=%d\n",
5669 ii
, sAggInfo
.aCol
[ii
].iMem
);
5670 sqlite3TreeViewExpr(0, sAggInfo
.aCol
[ii
].pExpr
, 0);
5672 for(ii
=0; ii
<sAggInfo
.nFunc
; ii
++){
5673 sqlite3DebugPrintf("agg-func[%d]: iMem=%d\n",
5674 ii
, sAggInfo
.aFunc
[ii
].iMem
);
5675 sqlite3TreeViewExpr(0, sAggInfo
.aFunc
[ii
].pExpr
, 0);
5681 /* Processing for aggregates with GROUP BY is very different and
5682 ** much more complex than aggregates without a GROUP BY.
5685 KeyInfo
*pKeyInfo
; /* Keying information for the group by clause */
5686 int addr1
; /* A-vs-B comparision jump */
5687 int addrOutputRow
; /* Start of subroutine that outputs a result row */
5688 int regOutputRow
; /* Return address register for output subroutine */
5689 int addrSetAbort
; /* Set the abort flag and return */
5690 int addrTopOfLoop
; /* Top of the input loop */
5691 int addrSortingIdx
; /* The OP_OpenEphemeral for the sorting index */
5692 int addrReset
; /* Subroutine for resetting the accumulator */
5693 int regReset
; /* Return address register for reset subroutine */
5695 /* If there is a GROUP BY clause we might need a sorting index to
5696 ** implement it. Allocate that sorting index now. If it turns out
5697 ** that we do not need it after all, the OP_SorterOpen instruction
5698 ** will be converted into a Noop.
5700 sAggInfo
.sortingIdx
= pParse
->nTab
++;
5701 pKeyInfo
= keyInfoFromExprList(pParse
, pGroupBy
, 0, sAggInfo
.nColumn
);
5702 addrSortingIdx
= sqlite3VdbeAddOp4(v
, OP_SorterOpen
,
5703 sAggInfo
.sortingIdx
, sAggInfo
.nSortingColumn
,
5704 0, (char*)pKeyInfo
, P4_KEYINFO
);
5706 /* Initialize memory locations used by GROUP BY aggregate processing
5708 iUseFlag
= ++pParse
->nMem
;
5709 iAbortFlag
= ++pParse
->nMem
;
5710 regOutputRow
= ++pParse
->nMem
;
5711 addrOutputRow
= sqlite3VdbeMakeLabel(v
);
5712 regReset
= ++pParse
->nMem
;
5713 addrReset
= sqlite3VdbeMakeLabel(v
);
5714 iAMem
= pParse
->nMem
+ 1;
5715 pParse
->nMem
+= pGroupBy
->nExpr
;
5716 iBMem
= pParse
->nMem
+ 1;
5717 pParse
->nMem
+= pGroupBy
->nExpr
;
5718 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, iAbortFlag
);
5719 VdbeComment((v
, "clear abort flag"));
5720 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, iUseFlag
);
5721 VdbeComment((v
, "indicate accumulator empty"));
5722 sqlite3VdbeAddOp3(v
, OP_Null
, 0, iAMem
, iAMem
+pGroupBy
->nExpr
-1);
5724 /* Begin a loop that will extract all source rows in GROUP BY order.
5725 ** This might involve two separate loops with an OP_Sort in between, or
5726 ** it might be a single loop that uses an index to extract information
5727 ** in the right order to begin with.
5729 sqlite3VdbeAddOp2(v
, OP_Gosub
, regReset
, addrReset
);
5730 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, pGroupBy
, 0,
5731 WHERE_GROUPBY
| (orderByGrp
? WHERE_SORTBYGROUP
: 0), 0
5733 if( pWInfo
==0 ) goto select_end
;
5734 if( sqlite3WhereIsOrdered(pWInfo
)==pGroupBy
->nExpr
){
5735 /* The optimizer is able to deliver rows in group by order so
5736 ** we do not have to sort. The OP_OpenEphemeral table will be
5737 ** cancelled later because we still need to use the pKeyInfo
5741 /* Rows are coming out in undetermined order. We have to push
5742 ** each row into a sorting index, terminate the first loop,
5743 ** then loop over the sorting index in order to get the output
5751 explainTempTable(pParse
,
5752 (sDistinct
.isTnct
&& (p
->selFlags
&SF_Distinct
)==0) ?
5753 "DISTINCT" : "GROUP BY");
5756 nGroupBy
= pGroupBy
->nExpr
;
5759 for(i
=0; i
<sAggInfo
.nColumn
; i
++){
5760 if( sAggInfo
.aCol
[i
].iSorterColumn
>=j
){
5765 regBase
= sqlite3GetTempRange(pParse
, nCol
);
5766 sqlite3ExprCacheClear(pParse
);
5767 sqlite3ExprCodeExprList(pParse
, pGroupBy
, regBase
, 0, 0);
5769 for(i
=0; i
<sAggInfo
.nColumn
; i
++){
5770 struct AggInfo_col
*pCol
= &sAggInfo
.aCol
[i
];
5771 if( pCol
->iSorterColumn
>=j
){
5772 int r1
= j
+ regBase
;
5773 sqlite3ExprCodeGetColumnToReg(pParse
,
5774 pCol
->pTab
, pCol
->iColumn
, pCol
->iTable
, r1
);
5778 regRecord
= sqlite3GetTempReg(pParse
);
5779 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regBase
, nCol
, regRecord
);
5780 sqlite3VdbeAddOp2(v
, OP_SorterInsert
, sAggInfo
.sortingIdx
, regRecord
);
5781 sqlite3ReleaseTempReg(pParse
, regRecord
);
5782 sqlite3ReleaseTempRange(pParse
, regBase
, nCol
);
5783 sqlite3WhereEnd(pWInfo
);
5784 sAggInfo
.sortingIdxPTab
= sortPTab
= pParse
->nTab
++;
5785 sortOut
= sqlite3GetTempReg(pParse
);
5786 sqlite3VdbeAddOp3(v
, OP_OpenPseudo
, sortPTab
, sortOut
, nCol
);
5787 sqlite3VdbeAddOp2(v
, OP_SorterSort
, sAggInfo
.sortingIdx
, addrEnd
);
5788 VdbeComment((v
, "GROUP BY sort")); VdbeCoverage(v
);
5789 sAggInfo
.useSortingIdx
= 1;
5790 sqlite3ExprCacheClear(pParse
);
5794 /* If the index or temporary table used by the GROUP BY sort
5795 ** will naturally deliver rows in the order required by the ORDER BY
5796 ** clause, cancel the ephemeral table open coded earlier.
5798 ** This is an optimization - the correct answer should result regardless.
5799 ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER to
5800 ** disable this optimization for testing purposes. */
5801 if( orderByGrp
&& OptimizationEnabled(db
, SQLITE_GroupByOrder
)
5802 && (groupBySort
|| sqlite3WhereIsSorted(pWInfo
))
5805 sqlite3VdbeChangeToNoop(v
, sSort
.addrSortIndex
);
5808 /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
5809 ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
5810 ** Then compare the current GROUP BY terms against the GROUP BY terms
5811 ** from the previous row currently stored in a0, a1, a2...
5813 addrTopOfLoop
= sqlite3VdbeCurrentAddr(v
);
5814 sqlite3ExprCacheClear(pParse
);
5816 sqlite3VdbeAddOp3(v
, OP_SorterData
, sAggInfo
.sortingIdx
,
5819 for(j
=0; j
<pGroupBy
->nExpr
; j
++){
5821 sqlite3VdbeAddOp3(v
, OP_Column
, sortPTab
, j
, iBMem
+j
);
5823 sAggInfo
.directMode
= 1;
5824 sqlite3ExprCode(pParse
, pGroupBy
->a
[j
].pExpr
, iBMem
+j
);
5827 sqlite3VdbeAddOp4(v
, OP_Compare
, iAMem
, iBMem
, pGroupBy
->nExpr
,
5828 (char*)sqlite3KeyInfoRef(pKeyInfo
), P4_KEYINFO
);
5829 addr1
= sqlite3VdbeCurrentAddr(v
);
5830 sqlite3VdbeAddOp3(v
, OP_Jump
, addr1
+1, 0, addr1
+1); VdbeCoverage(v
);
5832 /* Generate code that runs whenever the GROUP BY changes.
5833 ** Changes in the GROUP BY are detected by the previous code
5834 ** block. If there were no changes, this block is skipped.
5836 ** This code copies current group by terms in b0,b1,b2,...
5837 ** over to a0,a1,a2. It then calls the output subroutine
5838 ** and resets the aggregate accumulator registers in preparation
5839 ** for the next GROUP BY batch.
5841 sqlite3ExprCodeMove(pParse
, iBMem
, iAMem
, pGroupBy
->nExpr
);
5842 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutputRow
, addrOutputRow
);
5843 VdbeComment((v
, "output one row"));
5844 sqlite3VdbeAddOp2(v
, OP_IfPos
, iAbortFlag
, addrEnd
); VdbeCoverage(v
);
5845 VdbeComment((v
, "check abort flag"));
5846 sqlite3VdbeAddOp2(v
, OP_Gosub
, regReset
, addrReset
);
5847 VdbeComment((v
, "reset accumulator"));
5849 /* Update the aggregate accumulators based on the content of
5852 sqlite3VdbeJumpHere(v
, addr1
);
5853 updateAccumulator(pParse
, &sAggInfo
);
5854 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iUseFlag
);
5855 VdbeComment((v
, "indicate data in accumulator"));
5860 sqlite3VdbeAddOp2(v
, OP_SorterNext
, sAggInfo
.sortingIdx
, addrTopOfLoop
);
5863 sqlite3WhereEnd(pWInfo
);
5864 sqlite3VdbeChangeToNoop(v
, addrSortingIdx
);
5867 /* Output the final row of result
5869 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutputRow
, addrOutputRow
);
5870 VdbeComment((v
, "output final row"));
5872 /* Jump over the subroutines
5874 sqlite3VdbeGoto(v
, addrEnd
);
5876 /* Generate a subroutine that outputs a single row of the result
5877 ** set. This subroutine first looks at the iUseFlag. If iUseFlag
5878 ** is less than or equal to zero, the subroutine is a no-op. If
5879 ** the processing calls for the query to abort, this subroutine
5880 ** increments the iAbortFlag memory location before returning in
5881 ** order to signal the caller to abort.
5883 addrSetAbort
= sqlite3VdbeCurrentAddr(v
);
5884 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iAbortFlag
);
5885 VdbeComment((v
, "set abort flag"));
5886 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
5887 sqlite3VdbeResolveLabel(v
, addrOutputRow
);
5888 addrOutputRow
= sqlite3VdbeCurrentAddr(v
);
5889 sqlite3VdbeAddOp2(v
, OP_IfPos
, iUseFlag
, addrOutputRow
+2);
5891 VdbeComment((v
, "Groupby result generator entry point"));
5892 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
5893 finalizeAggFunctions(pParse
, &sAggInfo
);
5894 sqlite3ExprIfFalse(pParse
, pHaving
, addrOutputRow
+1, SQLITE_JUMPIFNULL
);
5895 selectInnerLoop(pParse
, p
, -1, &sSort
,
5897 addrOutputRow
+1, addrSetAbort
);
5898 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
5899 VdbeComment((v
, "end groupby result generator"));
5901 /* Generate a subroutine that will reset the group-by accumulator
5903 sqlite3VdbeResolveLabel(v
, addrReset
);
5904 resetAccumulator(pParse
, &sAggInfo
);
5905 sqlite3VdbeAddOp1(v
, OP_Return
, regReset
);
5907 } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */
5909 #ifndef SQLITE_OMIT_BTREECOUNT
5911 if( (pTab
= isSimpleCount(p
, &sAggInfo
))!=0 ){
5912 /* If isSimpleCount() returns a pointer to a Table structure, then
5913 ** the SQL statement is of the form:
5915 ** SELECT count(*) FROM <tbl>
5917 ** where the Table structure returned represents table <tbl>.
5919 ** This statement is so common that it is optimized specially. The
5920 ** OP_Count instruction is executed either on the intkey table that
5921 ** contains the data for table <tbl> or on one of its indexes. It
5922 ** is better to execute the op on an index, as indexes are almost
5923 ** always spread across less pages than their corresponding tables.
5925 const int iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
5926 const int iCsr
= pParse
->nTab
++; /* Cursor to scan b-tree */
5927 Index
*pIdx
; /* Iterator variable */
5928 KeyInfo
*pKeyInfo
= 0; /* Keyinfo for scanned index */
5929 Index
*pBest
= 0; /* Best index found so far */
5930 int iRoot
= pTab
->tnum
; /* Root page of scanned b-tree */
5932 sqlite3CodeVerifySchema(pParse
, iDb
);
5933 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, 0, pTab
->zName
);
5935 /* Search for the index that has the lowest scan cost.
5937 ** (2011-04-15) Do not do a full scan of an unordered index.
5939 ** (2013-10-03) Do not count the entries in a partial index.
5941 ** In practice the KeyInfo structure will not be used. It is only
5942 ** passed to keep OP_OpenRead happy.
5944 if( !HasRowid(pTab
) ) pBest
= sqlite3PrimaryKeyIndex(pTab
);
5945 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
5946 if( pIdx
->bUnordered
==0
5947 && pIdx
->szIdxRow
<pTab
->szTabRow
5948 && pIdx
->pPartIdxWhere
==0
5949 && (!pBest
|| pIdx
->szIdxRow
<pBest
->szIdxRow
)
5955 iRoot
= pBest
->tnum
;
5956 pKeyInfo
= sqlite3KeyInfoOfIndex(pParse
, pBest
);
5959 /* Open a read-only cursor, execute the OP_Count, close the cursor. */
5960 sqlite3VdbeAddOp4Int(v
, OP_OpenRead
, iCsr
, iRoot
, iDb
, 1);
5962 sqlite3VdbeChangeP4(v
, -1, (char *)pKeyInfo
, P4_KEYINFO
);
5964 sqlite3VdbeAddOp2(v
, OP_Count
, iCsr
, sAggInfo
.aFunc
[0].iMem
);
5965 sqlite3VdbeAddOp1(v
, OP_Close
, iCsr
);
5966 explainSimpleCount(pParse
, pTab
, pBest
);
5968 #endif /* SQLITE_OMIT_BTREECOUNT */
5970 /* This case runs if the aggregate has no GROUP BY clause. The
5971 ** processing is much simpler since there is only a single row
5974 assert( p
->pGroupBy
==0 );
5975 resetAccumulator(pParse
, &sAggInfo
);
5977 /* If this query is a candidate for the min/max optimization, then
5978 ** minMaxFlag will have been previously set to either
5979 ** WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX and pMinMaxOrderBy will
5980 ** be an appropriate ORDER BY expression for the optimization.
5982 assert( minMaxFlag
==WHERE_ORDERBY_NORMAL
|| pMinMaxOrderBy
!=0 );
5983 assert( pMinMaxOrderBy
==0 || pMinMaxOrderBy
->nExpr
==1 );
5985 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, pMinMaxOrderBy
,
5990 updateAccumulator(pParse
, &sAggInfo
);
5991 if( sqlite3WhereIsOrdered(pWInfo
)>0 ){
5992 sqlite3VdbeGoto(v
, sqlite3WhereBreakLabel(pWInfo
));
5993 VdbeComment((v
, "%s() by index",
5994 (minMaxFlag
==WHERE_ORDERBY_MIN
?"min":"max")));
5996 sqlite3WhereEnd(pWInfo
);
5997 finalizeAggFunctions(pParse
, &sAggInfo
);
6001 sqlite3ExprIfFalse(pParse
, pHaving
, addrEnd
, SQLITE_JUMPIFNULL
);
6002 selectInnerLoop(pParse
, p
, -1, 0, 0,
6003 pDest
, addrEnd
, addrEnd
);
6005 sqlite3VdbeResolveLabel(v
, addrEnd
);
6007 } /* endif aggregate query */
6009 if( sDistinct
.eTnctType
==WHERE_DISTINCT_UNORDERED
){
6010 explainTempTable(pParse
, "DISTINCT");
6013 /* If there is an ORDER BY clause, then we need to sort the results
6014 ** and send them to the callback one by one.
6016 if( sSort
.pOrderBy
){
6017 explainTempTable(pParse
,
6018 sSort
.nOBSat
>0 ? "RIGHT PART OF ORDER BY":"ORDER BY");
6019 generateSortTail(pParse
, p
, &sSort
, pEList
->nExpr
, pDest
);
6022 /* Jump here to skip this query
6024 sqlite3VdbeResolveLabel(v
, iEnd
);
6026 /* The SELECT has been coded. If there is an error in the Parse structure,
6027 ** set the return code to 1. Otherwise 0. */
6028 rc
= (pParse
->nErr
>0);
6030 /* Control jumps to here if an error is encountered above, or upon
6031 ** successful coding of the SELECT.
6034 explainSetInteger(pParse
->iSelectId
, iRestoreSelectId
);
6035 sqlite3ExprListDelete(db
, pMinMaxOrderBy
);
6036 sqlite3DbFree(db
, sAggInfo
.aCol
);
6037 sqlite3DbFree(db
, sAggInfo
.aFunc
);
6038 #if SELECTTRACE_ENABLED
6039 SELECTTRACE(1,pParse
,p
,("end processing\n"));
6040 pParse
->nSelectIndent
--;