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"
19 ** Delete all the content of a Select structure but do not deallocate
20 ** the select structure itself.
22 static void clearSelect(sqlite3
*db
, Select
*p
){
23 sqlite3ExprListDelete(db
, p
->pEList
);
24 sqlite3SrcListDelete(db
, p
->pSrc
);
25 sqlite3ExprDelete(db
, p
->pWhere
);
26 sqlite3ExprListDelete(db
, p
->pGroupBy
);
27 sqlite3ExprDelete(db
, p
->pHaving
);
28 sqlite3ExprListDelete(db
, p
->pOrderBy
);
29 sqlite3SelectDelete(db
, p
->pPrior
);
30 sqlite3ExprDelete(db
, p
->pLimit
);
31 sqlite3ExprDelete(db
, p
->pOffset
);
35 ** Initialize a SelectDest structure.
37 void sqlite3SelectDestInit(SelectDest
*pDest
, int eDest
, int iParm
){
38 pDest
->eDest
= (u8
)eDest
;
47 ** Allocate a new Select structure and return a pointer to that
50 Select
*sqlite3SelectNew(
51 Parse
*pParse
, /* Parsing context */
52 ExprList
*pEList
, /* which columns to include in the result */
53 SrcList
*pSrc
, /* the FROM clause -- which tables to scan */
54 Expr
*pWhere
, /* the WHERE clause */
55 ExprList
*pGroupBy
, /* the GROUP BY clause */
56 Expr
*pHaving
, /* the HAVING clause */
57 ExprList
*pOrderBy
, /* the ORDER BY clause */
58 int isDistinct
, /* true if the DISTINCT keyword is present */
59 Expr
*pLimit
, /* LIMIT value. NULL means not used */
60 Expr
*pOffset
/* OFFSET value. NULL means no offset */
64 sqlite3
*db
= pParse
->db
;
65 pNew
= sqlite3DbMallocZero(db
, sizeof(*pNew
) );
66 assert( db
->mallocFailed
|| !pOffset
|| pLimit
); /* OFFSET implies LIMIT */
69 memset(pNew
, 0, sizeof(*pNew
));
72 pEList
= sqlite3ExprListAppend(pParse
, 0, sqlite3Expr(db
,TK_ALL
,0));
74 pNew
->pEList
= pEList
;
76 pNew
->pWhere
= pWhere
;
77 pNew
->pGroupBy
= pGroupBy
;
78 pNew
->pHaving
= pHaving
;
79 pNew
->pOrderBy
= pOrderBy
;
80 pNew
->selFlags
= isDistinct
? SF_Distinct
: 0;
82 pNew
->pLimit
= pLimit
;
83 pNew
->pOffset
= pOffset
;
84 assert( pOffset
==0 || pLimit
!=0 );
85 pNew
->addrOpenEphm
[0] = -1;
86 pNew
->addrOpenEphm
[1] = -1;
87 pNew
->addrOpenEphm
[2] = -1;
88 if( db
->mallocFailed
) {
89 clearSelect(db
, pNew
);
90 if( pNew
!=&standin
) sqlite3DbFree(db
, pNew
);
97 ** Delete the given Select structure and all of its substructures.
99 void sqlite3SelectDelete(sqlite3
*db
, Select
*p
){
102 sqlite3DbFree(db
, p
);
107 ** Given 1 to 3 identifiers preceeding the JOIN keyword, determine the
108 ** type of join. Return an integer constant that expresses that type
109 ** in terms of the following bit values:
118 ** A full outer join is the combination of JT_LEFT and JT_RIGHT.
120 ** If an illegal or unsupported join type is seen, then still return
121 ** a join type, but put an error in the pParse structure.
123 int sqlite3JoinType(Parse
*pParse
, Token
*pA
, Token
*pB
, Token
*pC
){
127 /* 0123456789 123456789 123456789 123 */
128 static const char zKeyText
[] = "naturaleftouterightfullinnercross";
129 static const struct {
130 u8 i
; /* Beginning of keyword text in zKeyText[] */
131 u8 nChar
; /* Length of the keyword in characters */
132 u8 code
; /* Join type mask */
134 /* natural */ { 0, 7, JT_NATURAL
},
135 /* left */ { 6, 4, JT_LEFT
|JT_OUTER
},
136 /* outer */ { 10, 5, JT_OUTER
},
137 /* right */ { 14, 5, JT_RIGHT
|JT_OUTER
},
138 /* full */ { 19, 4, JT_LEFT
|JT_RIGHT
|JT_OUTER
},
139 /* inner */ { 23, 5, JT_INNER
},
140 /* cross */ { 28, 5, JT_INNER
|JT_CROSS
},
146 for(i
=0; i
<3 && apAll
[i
]; i
++){
148 for(j
=0; j
<ArraySize(aKeyword
); j
++){
149 if( p
->n
==aKeyword
[j
].nChar
150 && sqlite3StrNICmp((char*)p
->z
, &zKeyText
[aKeyword
[j
].i
], p
->n
)==0 ){
151 jointype
|= aKeyword
[j
].code
;
155 testcase( j
==0 || j
==1 || j
==2 || j
==3 || j
==4 || j
==5 || j
==6 );
156 if( j
>=ArraySize(aKeyword
) ){
157 jointype
|= JT_ERROR
;
162 (jointype
& (JT_INNER
|JT_OUTER
))==(JT_INNER
|JT_OUTER
) ||
163 (jointype
& JT_ERROR
)!=0
165 const char *zSp
= " ";
167 if( pC
==0 ){ zSp
++; }
168 sqlite3ErrorMsg(pParse
, "unknown or unsupported join type: "
169 "%T %T%s%T", pA
, pB
, zSp
, pC
);
171 }else if( (jointype
& JT_OUTER
)!=0
172 && (jointype
& (JT_LEFT
|JT_RIGHT
))!=JT_LEFT
){
173 sqlite3ErrorMsg(pParse
,
174 "RIGHT and FULL OUTER JOINs are not currently supported");
181 ** Return the index of a column in a table. Return -1 if the column
182 ** is not contained in the table.
184 static int columnIndex(Table
*pTab
, const char *zCol
){
186 for(i
=0; i
<pTab
->nCol
; i
++){
187 if( sqlite3StrICmp(pTab
->aCol
[i
].zName
, zCol
)==0 ) return i
;
193 ** Search the first N tables in pSrc, from left to right, looking for a
194 ** table that has a column named zCol.
196 ** When found, set *piTab and *piCol to the table index and column index
197 ** of the matching column and return TRUE.
199 ** If not found, return FALSE.
201 static int tableAndColumnIndex(
202 SrcList
*pSrc
, /* Array of tables to search */
203 int N
, /* Number of tables in pSrc->a[] to search */
204 const char *zCol
, /* Name of the column we are looking for */
205 int *piTab
, /* Write index of pSrc->a[] here */
206 int *piCol
/* Write index of pSrc->a[*piTab].pTab->aCol[] here */
208 int i
; /* For looping over tables in pSrc */
209 int iCol
; /* Index of column matching zCol */
211 assert( (piTab
==0)==(piCol
==0) ); /* Both or neither are NULL */
213 iCol
= columnIndex(pSrc
->a
[i
].pTab
, zCol
);
226 ** This function is used to add terms implied by JOIN syntax to the
227 ** WHERE clause expression of a SELECT statement. The new term, which
228 ** is ANDed with the existing WHERE clause, is of the form:
230 ** (tab1.col1 = tab2.col2)
232 ** where tab1 is the iSrc'th table in SrcList pSrc and tab2 is the
233 ** (iSrc+1)'th. Column col1 is column iColLeft of tab1, and col2 is
234 ** column iColRight of tab2.
236 static void addWhereTerm(
237 Parse
*pParse
, /* Parsing context */
238 SrcList
*pSrc
, /* List of tables in FROM clause */
239 int iLeft
, /* Index of first table to join in pSrc */
240 int iColLeft
, /* Index of column in first table */
241 int iRight
, /* Index of second table in pSrc */
242 int iColRight
, /* Index of column in second table */
243 int isOuterJoin
, /* True if this is an OUTER join */
244 Expr
**ppWhere
/* IN/OUT: The WHERE clause to add to */
246 sqlite3
*db
= pParse
->db
;
251 assert( iLeft
<iRight
);
252 assert( pSrc
->nSrc
>iRight
);
253 assert( pSrc
->a
[iLeft
].pTab
);
254 assert( pSrc
->a
[iRight
].pTab
);
256 pE1
= sqlite3CreateColumnExpr(db
, pSrc
, iLeft
, iColLeft
);
257 pE2
= sqlite3CreateColumnExpr(db
, pSrc
, iRight
, iColRight
);
259 pEq
= sqlite3PExpr(pParse
, TK_EQ
, pE1
, pE2
, 0);
260 if( pEq
&& isOuterJoin
){
261 ExprSetProperty(pEq
, EP_FromJoin
);
262 assert( !ExprHasAnyProperty(pEq
, EP_TokenOnly
|EP_Reduced
) );
263 ExprSetIrreducible(pEq
);
264 pEq
->iRightJoinTable
= (i16
)pE2
->iTable
;
266 *ppWhere
= sqlite3ExprAnd(db
, *ppWhere
, pEq
);
270 ** Set the EP_FromJoin property on all terms of the given expression.
271 ** And set the Expr.iRightJoinTable to iTable for every term in the
274 ** The EP_FromJoin property is used on terms of an expression to tell
275 ** the LEFT OUTER JOIN processing logic that this term is part of the
276 ** join restriction specified in the ON or USING clause and not a part
277 ** of the more general WHERE clause. These terms are moved over to the
278 ** WHERE clause during join processing but we need to remember that they
279 ** originated in the ON or USING clause.
281 ** The Expr.iRightJoinTable tells the WHERE clause processing that the
282 ** expression depends on table iRightJoinTable even if that table is not
283 ** explicitly mentioned in the expression. That information is needed
284 ** for cases like this:
286 ** SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
288 ** The where clause needs to defer the handling of the t1.x=5
289 ** term until after the t2 loop of the join. In that way, a
290 ** NULL t2 row will be inserted whenever t1.x!=5. If we do not
291 ** defer the handling of t1.x=5, it will be processed immediately
292 ** after the t1 loop and rows with t1.x!=5 will never appear in
293 ** the output, which is incorrect.
295 static void setJoinExpr(Expr
*p
, int iTable
){
297 ExprSetProperty(p
, EP_FromJoin
);
298 assert( !ExprHasAnyProperty(p
, EP_TokenOnly
|EP_Reduced
) );
299 ExprSetIrreducible(p
);
300 p
->iRightJoinTable
= (i16
)iTable
;
301 setJoinExpr(p
->pLeft
, iTable
);
307 ** This routine processes the join information for a SELECT statement.
308 ** ON and USING clauses are converted into extra terms of the WHERE clause.
309 ** NATURAL joins also create extra WHERE clause terms.
311 ** The terms of a FROM clause are contained in the Select.pSrc structure.
312 ** The left most table is the first entry in Select.pSrc. The right-most
313 ** table is the last entry. The join operator is held in the entry to
314 ** the left. Thus entry 0 contains the join operator for the join between
315 ** entries 0 and 1. Any ON or USING clauses associated with the join are
316 ** also attached to the left entry.
318 ** This routine returns the number of errors encountered.
320 static int sqliteProcessJoin(Parse
*pParse
, Select
*p
){
321 SrcList
*pSrc
; /* All tables in the FROM clause */
322 int i
, j
; /* Loop counters */
323 struct SrcList_item
*pLeft
; /* Left table being joined */
324 struct SrcList_item
*pRight
; /* Right table being joined */
329 for(i
=0; i
<pSrc
->nSrc
-1; i
++, pRight
++, pLeft
++){
330 Table
*pLeftTab
= pLeft
->pTab
;
331 Table
*pRightTab
= pRight
->pTab
;
334 if( NEVER(pLeftTab
==0 || pRightTab
==0) ) continue;
335 isOuter
= (pRight
->jointype
& JT_OUTER
)!=0;
337 /* When the NATURAL keyword is present, add WHERE clause terms for
338 ** every column that the two tables have in common.
340 if( pRight
->jointype
& JT_NATURAL
){
341 if( pRight
->pOn
|| pRight
->pUsing
){
342 sqlite3ErrorMsg(pParse
, "a NATURAL join may not have "
343 "an ON or USING clause", 0);
346 for(j
=0; j
<pRightTab
->nCol
; j
++){
347 char *zName
; /* Name of column in the right table */
348 int iLeft
; /* Matching left table */
349 int iLeftCol
; /* Matching column in the left table */
351 zName
= pRightTab
->aCol
[j
].zName
;
352 if( tableAndColumnIndex(pSrc
, i
+1, zName
, &iLeft
, &iLeftCol
) ){
353 addWhereTerm(pParse
, pSrc
, iLeft
, iLeftCol
, i
+1, j
,
354 isOuter
, &p
->pWhere
);
359 /* Disallow both ON and USING clauses in the same join
361 if( pRight
->pOn
&& pRight
->pUsing
){
362 sqlite3ErrorMsg(pParse
, "cannot have both ON and USING "
363 "clauses in the same join");
367 /* Add the ON clause to the end of the WHERE clause, connected by
371 if( isOuter
) setJoinExpr(pRight
->pOn
, pRight
->iCursor
);
372 p
->pWhere
= sqlite3ExprAnd(pParse
->db
, p
->pWhere
, pRight
->pOn
);
376 /* Create extra terms on the WHERE clause for each column named
377 ** in the USING clause. Example: If the two tables to be joined are
378 ** A and B and the USING clause names X, Y, and Z, then add this
379 ** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
380 ** Report an error if any column mentioned in the USING clause is
381 ** not contained in both tables to be joined.
383 if( pRight
->pUsing
){
384 IdList
*pList
= pRight
->pUsing
;
385 for(j
=0; j
<pList
->nId
; j
++){
386 char *zName
; /* Name of the term in the USING clause */
387 int iLeft
; /* Table on the left with matching column name */
388 int iLeftCol
; /* Column number of matching column on the left */
389 int iRightCol
; /* Column number of matching column on the right */
391 zName
= pList
->a
[j
].zName
;
392 iRightCol
= columnIndex(pRightTab
, zName
);
394 || !tableAndColumnIndex(pSrc
, i
+1, zName
, &iLeft
, &iLeftCol
)
396 sqlite3ErrorMsg(pParse
, "cannot join using column %s - column "
397 "not present in both tables", zName
);
400 addWhereTerm(pParse
, pSrc
, iLeft
, iLeftCol
, i
+1, iRightCol
,
401 isOuter
, &p
->pWhere
);
409 ** Insert code into "v" that will push the record on the top of the
410 ** stack into the sorter.
412 static void pushOntoSorter(
413 Parse
*pParse
, /* Parser context */
414 ExprList
*pOrderBy
, /* The ORDER BY clause */
415 Select
*pSelect
, /* The whole SELECT statement */
416 int regData
/* Register holding data to be sorted */
418 Vdbe
*v
= pParse
->pVdbe
;
419 int nExpr
= pOrderBy
->nExpr
;
420 int regBase
= sqlite3GetTempRange(pParse
, nExpr
+2);
421 int regRecord
= sqlite3GetTempReg(pParse
);
422 sqlite3ExprCacheClear(pParse
);
423 sqlite3ExprCodeExprList(pParse
, pOrderBy
, regBase
, 0);
424 sqlite3VdbeAddOp2(v
, OP_Sequence
, pOrderBy
->iECursor
, regBase
+nExpr
);
425 sqlite3ExprCodeMove(pParse
, regData
, regBase
+nExpr
+1, 1);
426 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regBase
, nExpr
+ 2, regRecord
);
427 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, pOrderBy
->iECursor
, regRecord
);
428 sqlite3ReleaseTempReg(pParse
, regRecord
);
429 sqlite3ReleaseTempRange(pParse
, regBase
, nExpr
+2);
430 if( pSelect
->iLimit
){
433 if( pSelect
->iOffset
){
434 iLimit
= pSelect
->iOffset
+1;
436 iLimit
= pSelect
->iLimit
;
438 addr1
= sqlite3VdbeAddOp1(v
, OP_IfZero
, iLimit
);
439 sqlite3VdbeAddOp2(v
, OP_AddImm
, iLimit
, -1);
440 addr2
= sqlite3VdbeAddOp0(v
, OP_Goto
);
441 sqlite3VdbeJumpHere(v
, addr1
);
442 sqlite3VdbeAddOp1(v
, OP_Last
, pOrderBy
->iECursor
);
443 sqlite3VdbeAddOp1(v
, OP_Delete
, pOrderBy
->iECursor
);
444 sqlite3VdbeJumpHere(v
, addr2
);
449 ** Add code to implement the OFFSET
451 static void codeOffset(
452 Vdbe
*v
, /* Generate code into this VM */
453 Select
*p
, /* The SELECT statement being coded */
454 int iContinue
/* Jump here to skip the current record */
456 if( p
->iOffset
&& iContinue
!=0 ){
458 sqlite3VdbeAddOp2(v
, OP_AddImm
, p
->iOffset
, -1);
459 addr
= sqlite3VdbeAddOp1(v
, OP_IfNeg
, p
->iOffset
);
460 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, iContinue
);
461 VdbeComment((v
, "skip OFFSET records"));
462 sqlite3VdbeJumpHere(v
, addr
);
467 ** Add code that will check to make sure the N registers starting at iMem
468 ** form a distinct entry. iTab is a sorting index that holds previously
469 ** seen combinations of the N values. A new entry is made in iTab
470 ** if the current N values are new.
472 ** A jump to addrRepeat is made and the N+1 values are popped from the
473 ** stack if the top N elements are not distinct.
475 static void codeDistinct(
476 Parse
*pParse
, /* Parsing and code generating context */
477 int iTab
, /* A sorting index used to test for distinctness */
478 int addrRepeat
, /* Jump to here if not distinct */
479 int N
, /* Number of elements */
480 int iMem
/* First element */
486 r1
= sqlite3GetTempReg(pParse
);
487 sqlite3VdbeAddOp4Int(v
, OP_Found
, iTab
, addrRepeat
, iMem
, N
);
488 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, iMem
, N
, r1
);
489 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iTab
, r1
);
490 sqlite3ReleaseTempReg(pParse
, r1
);
493 #ifndef SQLITE_OMIT_SUBQUERY
495 ** Generate an error message when a SELECT is used within a subexpression
496 ** (example: "a IN (SELECT * FROM table)") but it has more than 1 result
497 ** column. We do this in a subroutine because the error used to occur
498 ** in multiple places. (The error only occurs in one place now, but we
499 ** retain the subroutine to minimize code disruption.)
501 static int checkForMultiColumnSelectError(
502 Parse
*pParse
, /* Parse context. */
503 SelectDest
*pDest
, /* Destination of SELECT results */
504 int nExpr
/* Number of result columns returned by SELECT */
506 int eDest
= pDest
->eDest
;
507 if( nExpr
>1 && (eDest
==SRT_Mem
|| eDest
==SRT_Set
) ){
508 sqlite3ErrorMsg(pParse
, "only a single result allowed for "
509 "a SELECT that is part of an expression");
518 ** This routine generates the code for the inside of the inner loop
521 ** If srcTab and nColumn are both zero, then the pEList expressions
522 ** are evaluated in order to get the data for this row. If nColumn>0
523 ** then data is pulled from srcTab and pEList is used only to get the
524 ** datatypes for each column.
526 static void selectInnerLoop(
527 Parse
*pParse
, /* The parser context */
528 Select
*p
, /* The complete select statement being coded */
529 ExprList
*pEList
, /* List of values being extracted */
530 int srcTab
, /* Pull data from this table */
531 int nColumn
, /* Number of columns in the source table */
532 ExprList
*pOrderBy
, /* If not NULL, sort results using this key */
533 int distinct
, /* If >=0, make sure results are distinct */
534 SelectDest
*pDest
, /* How to dispose of the results */
535 int iContinue
, /* Jump here to continue with next row */
536 int iBreak
/* Jump here to break out of the inner loop */
538 Vdbe
*v
= pParse
->pVdbe
;
540 int hasDistinct
; /* True if the DISTINCT keyword is present */
541 int regResult
; /* Start of memory holding result set */
542 int eDest
= pDest
->eDest
; /* How to dispose of results */
543 int iParm
= pDest
->iParm
; /* First argument to disposal method */
544 int nResultCol
; /* Number of result columns */
547 if( NEVER(v
==0) ) return;
549 hasDistinct
= distinct
>=0;
550 if( pOrderBy
==0 && !hasDistinct
){
551 codeOffset(v
, p
, iContinue
);
554 /* Pull the requested columns.
557 nResultCol
= nColumn
;
559 nResultCol
= pEList
->nExpr
;
561 if( pDest
->iMem
==0 ){
562 pDest
->iMem
= pParse
->nMem
+1;
563 pDest
->nMem
= nResultCol
;
564 pParse
->nMem
+= nResultCol
;
566 assert( pDest
->nMem
==nResultCol
);
568 regResult
= pDest
->iMem
;
570 for(i
=0; i
<nColumn
; i
++){
571 sqlite3VdbeAddOp3(v
, OP_Column
, srcTab
, i
, regResult
+i
);
573 }else if( eDest
!=SRT_Exists
){
574 /* If the destination is an EXISTS(...) expression, the actual
575 ** values returned by the SELECT are not required.
577 sqlite3ExprCacheClear(pParse
);
578 sqlite3ExprCodeExprList(pParse
, pEList
, regResult
, eDest
==SRT_Output
);
580 nColumn
= nResultCol
;
582 /* If the DISTINCT keyword was present on the SELECT statement
583 ** and this row has been seen before, then do not make this row
584 ** part of the result.
588 assert( pEList
->nExpr
==nColumn
);
589 codeDistinct(pParse
, distinct
, iContinue
, nColumn
, regResult
);
591 codeOffset(v
, p
, iContinue
);
596 /* In this mode, write each query result to the key of the temporary
599 #ifndef SQLITE_OMIT_COMPOUND_SELECT
602 r1
= sqlite3GetTempReg(pParse
);
603 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nColumn
, r1
);
604 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
, r1
);
605 sqlite3ReleaseTempReg(pParse
, r1
);
609 /* Construct a record from the query result, but instead of
610 ** saving that record, use it as a key to delete elements from
611 ** the temporary table iParm.
614 sqlite3VdbeAddOp3(v
, OP_IdxDelete
, iParm
, regResult
, nColumn
);
619 /* Store the result as data using a unique key.
623 int r1
= sqlite3GetTempReg(pParse
);
624 testcase( eDest
==SRT_Table
);
625 testcase( eDest
==SRT_EphemTab
);
626 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nColumn
, r1
);
628 pushOntoSorter(pParse
, pOrderBy
, p
, r1
);
630 int r2
= sqlite3GetTempReg(pParse
);
631 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iParm
, r2
);
632 sqlite3VdbeAddOp3(v
, OP_Insert
, iParm
, r1
, r2
);
633 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
634 sqlite3ReleaseTempReg(pParse
, r2
);
636 sqlite3ReleaseTempReg(pParse
, r1
);
640 #ifndef SQLITE_OMIT_SUBQUERY
641 /* If we are creating a set for an "expr IN (SELECT ...)" construct,
642 ** then there should be a single item on the stack. Write this
643 ** item into the set table with bogus data.
646 assert( nColumn
==1 );
647 p
->affinity
= sqlite3CompareAffinity(pEList
->a
[0].pExpr
, pDest
->affinity
);
649 /* At first glance you would think we could optimize out the
650 ** ORDER BY in this case since the order of entries in the set
651 ** does not matter. But there might be a LIMIT clause, in which
652 ** case the order does matter */
653 pushOntoSorter(pParse
, pOrderBy
, p
, regResult
);
655 int r1
= sqlite3GetTempReg(pParse
);
656 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regResult
, 1, r1
, &p
->affinity
, 1);
657 sqlite3ExprCacheAffinityChange(pParse
, regResult
, 1);
658 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
, r1
);
659 sqlite3ReleaseTempReg(pParse
, r1
);
664 /* If any row exist in the result set, record that fact and abort.
667 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iParm
);
668 /* The LIMIT clause will terminate the loop for us */
672 /* If this is a scalar select that is part of an expression, then
673 ** store the results in the appropriate memory cell and break out
677 assert( nColumn
==1 );
679 pushOntoSorter(pParse
, pOrderBy
, p
, regResult
);
681 sqlite3ExprCodeMove(pParse
, regResult
, iParm
, 1);
682 /* The LIMIT clause will jump out of the loop for us */
686 #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
688 /* Send the data to the callback function or to a subroutine. In the
689 ** case of a subroutine, the subroutine itself is responsible for
690 ** popping the data from the stack.
694 testcase( eDest
==SRT_Coroutine
);
695 testcase( eDest
==SRT_Output
);
697 int r1
= sqlite3GetTempReg(pParse
);
698 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regResult
, nColumn
, r1
);
699 pushOntoSorter(pParse
, pOrderBy
, p
, r1
);
700 sqlite3ReleaseTempReg(pParse
, r1
);
701 }else if( eDest
==SRT_Coroutine
){
702 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iParm
);
704 sqlite3VdbeAddOp2(v
, OP_ResultRow
, regResult
, nColumn
);
705 sqlite3ExprCacheAffinityChange(pParse
, regResult
, nColumn
);
710 #if !defined(SQLITE_OMIT_TRIGGER)
711 /* Discard the results. This is used for SELECT statements inside
712 ** the body of a TRIGGER. The purpose of such selects is to call
713 ** user-defined functions that have side effects. We do not care
714 ** about the actual results of the select.
717 assert( eDest
==SRT_Discard
);
723 /* Jump to the end of the loop if the LIMIT is reached. Except, if
724 ** there is a sorter, in which case the sorter has already limited
725 ** the output for us.
727 if( pOrderBy
==0 && p
->iLimit
){
728 sqlite3VdbeAddOp3(v
, OP_IfZero
, p
->iLimit
, iBreak
, -1);
733 ** Given an expression list, generate a KeyInfo structure that records
734 ** the collating sequence for each expression in that expression list.
736 ** If the ExprList is an ORDER BY or GROUP BY clause then the resulting
737 ** KeyInfo structure is appropriate for initializing a virtual index to
738 ** implement that clause. If the ExprList is the result set of a SELECT
739 ** then the KeyInfo structure is appropriate for initializing a virtual
740 ** index to implement a DISTINCT test.
742 ** Space to hold the KeyInfo structure is obtain from malloc. The calling
743 ** function is responsible for seeing that this structure is eventually
744 ** freed. Add the KeyInfo structure to the P4 field of an opcode using
745 ** P4_KEYINFO_HANDOFF is the usual way of dealing with this.
747 static KeyInfo
*keyInfoFromExprList(Parse
*pParse
, ExprList
*pList
){
748 sqlite3
*db
= pParse
->db
;
751 struct ExprList_item
*pItem
;
754 nExpr
= pList
->nExpr
;
755 pInfo
= sqlite3DbMallocZero(db
, sizeof(*pInfo
) + nExpr
*(sizeof(CollSeq
*)+1) );
757 pInfo
->aSortOrder
= (u8
*)&pInfo
->aColl
[nExpr
];
758 pInfo
->nField
= (u16
)nExpr
;
759 pInfo
->enc
= ENC(db
);
761 for(i
=0, pItem
=pList
->a
; i
<nExpr
; i
++, pItem
++){
763 pColl
= sqlite3ExprCollSeq(pParse
, pItem
->pExpr
);
765 pColl
= db
->pDfltColl
;
767 pInfo
->aColl
[i
] = pColl
;
768 pInfo
->aSortOrder
[i
] = pItem
->sortOrder
;
774 #ifndef SQLITE_OMIT_COMPOUND_SELECT
776 ** Name of the connection operator, used for error messages.
778 static const char *selectOpName(int id
){
781 case TK_ALL
: z
= "UNION ALL"; break;
782 case TK_INTERSECT
: z
= "INTERSECT"; break;
783 case TK_EXCEPT
: z
= "EXCEPT"; break;
784 default: z
= "UNION"; break;
788 #endif /* SQLITE_OMIT_COMPOUND_SELECT */
790 #ifndef SQLITE_OMIT_EXPLAIN
792 ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
793 ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
794 ** where the caption is of the form:
796 ** "USE TEMP B-TREE FOR xxx"
798 ** where xxx is one of "DISTINCT", "ORDER BY" or "GROUP BY". Exactly which
799 ** is determined by the zUsage argument.
801 static void explainTempTable(Parse
*pParse
, const char *zUsage
){
802 if( pParse
->explain
==2 ){
803 Vdbe
*v
= pParse
->pVdbe
;
804 char *zMsg
= sqlite3MPrintf(pParse
->db
, "USE TEMP B-TREE FOR %s", zUsage
);
805 sqlite3VdbeAddOp4(v
, OP_Explain
, pParse
->iSelectId
, 0, 0, zMsg
, P4_DYNAMIC
);
810 ** Assign expression b to lvalue a. A second, no-op, version of this macro
811 ** is provided when SQLITE_OMIT_EXPLAIN is defined. This allows the code
812 ** in sqlite3Select() to assign values to structure member variables that
813 ** only exist if SQLITE_OMIT_EXPLAIN is not defined without polluting the
814 ** code with #ifndef directives.
816 # define explainSetInteger(a, b) a = b
819 /* No-op versions of the explainXXX() functions and macros. */
820 # define explainTempTable(y,z)
821 # define explainSetInteger(y,z)
824 #if !defined(SQLITE_OMIT_EXPLAIN) && !defined(SQLITE_OMIT_COMPOUND_SELECT)
826 ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
827 ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
828 ** where the caption is of one of the two forms:
830 ** "COMPOSITE SUBQUERIES iSub1 and iSub2 (op)"
831 ** "COMPOSITE SUBQUERIES iSub1 and iSub2 USING TEMP B-TREE (op)"
833 ** where iSub1 and iSub2 are the integers passed as the corresponding
834 ** function parameters, and op is the text representation of the parameter
835 ** of the same name. The parameter "op" must be one of TK_UNION, TK_EXCEPT,
836 ** TK_INTERSECT or TK_ALL. The first form is used if argument bUseTmp is
837 ** false, or the second form if it is true.
839 static void explainComposite(
840 Parse
*pParse
, /* Parse context */
841 int op
, /* One of TK_UNION, TK_EXCEPT etc. */
842 int iSub1
, /* Subquery id 1 */
843 int iSub2
, /* Subquery id 2 */
844 int bUseTmp
/* True if a temp table was used */
846 assert( op
==TK_UNION
|| op
==TK_EXCEPT
|| op
==TK_INTERSECT
|| op
==TK_ALL
);
847 if( pParse
->explain
==2 ){
848 Vdbe
*v
= pParse
->pVdbe
;
849 char *zMsg
= sqlite3MPrintf(
850 pParse
->db
, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1
, iSub2
,
851 bUseTmp
?"USING TEMP B-TREE ":"", selectOpName(op
)
853 sqlite3VdbeAddOp4(v
, OP_Explain
, pParse
->iSelectId
, 0, 0, zMsg
, P4_DYNAMIC
);
857 /* No-op versions of the explainXXX() functions and macros. */
858 # define explainComposite(v,w,x,y,z)
862 ** If the inner loop was generated using a non-null pOrderBy argument,
863 ** then the results were placed in a sorter. After the loop is terminated
864 ** we need to run the sorter and output the results. The following
865 ** routine generates the code needed to do that.
867 static void generateSortTail(
868 Parse
*pParse
, /* Parsing context */
869 Select
*p
, /* The SELECT statement */
870 Vdbe
*v
, /* Generate code into this VDBE */
871 int nColumn
, /* Number of columns of data */
872 SelectDest
*pDest
/* Write the sorted results here */
874 int addrBreak
= sqlite3VdbeMakeLabel(v
); /* Jump here to exit loop */
875 int addrContinue
= sqlite3VdbeMakeLabel(v
); /* Jump here for next cycle */
879 ExprList
*pOrderBy
= p
->pOrderBy
;
881 int eDest
= pDest
->eDest
;
882 int iParm
= pDest
->iParm
;
887 iTab
= pOrderBy
->iECursor
;
888 regRow
= sqlite3GetTempReg(pParse
);
889 if( eDest
==SRT_Output
|| eDest
==SRT_Coroutine
){
890 pseudoTab
= pParse
->nTab
++;
891 sqlite3VdbeAddOp3(v
, OP_OpenPseudo
, pseudoTab
, regRow
, nColumn
);
894 regRowid
= sqlite3GetTempReg(pParse
);
896 addr
= 1 + sqlite3VdbeAddOp2(v
, OP_Sort
, iTab
, addrBreak
);
897 codeOffset(v
, p
, addrContinue
);
898 sqlite3VdbeAddOp3(v
, OP_Column
, iTab
, pOrderBy
->nExpr
+ 1, regRow
);
902 testcase( eDest
==SRT_Table
);
903 testcase( eDest
==SRT_EphemTab
);
904 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iParm
, regRowid
);
905 sqlite3VdbeAddOp3(v
, OP_Insert
, iParm
, regRow
, regRowid
);
906 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
909 #ifndef SQLITE_OMIT_SUBQUERY
911 assert( nColumn
==1 );
912 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regRow
, 1, regRowid
, &p
->affinity
, 1);
913 sqlite3ExprCacheAffinityChange(pParse
, regRow
, 1);
914 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, iParm
, regRowid
);
918 assert( nColumn
==1 );
919 sqlite3ExprCodeMove(pParse
, regRow
, iParm
, 1);
920 /* The LIMIT clause will terminate the loop for us */
926 assert( eDest
==SRT_Output
|| eDest
==SRT_Coroutine
);
927 testcase( eDest
==SRT_Output
);
928 testcase( eDest
==SRT_Coroutine
);
929 for(i
=0; i
<nColumn
; i
++){
930 assert( regRow
!=pDest
->iMem
+i
);
931 sqlite3VdbeAddOp3(v
, OP_Column
, pseudoTab
, i
, pDest
->iMem
+i
);
933 sqlite3VdbeChangeP5(v
, OPFLAG_CLEARCACHE
);
936 if( eDest
==SRT_Output
){
937 sqlite3VdbeAddOp2(v
, OP_ResultRow
, pDest
->iMem
, nColumn
);
938 sqlite3ExprCacheAffinityChange(pParse
, pDest
->iMem
, nColumn
);
940 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iParm
);
945 sqlite3ReleaseTempReg(pParse
, regRow
);
946 sqlite3ReleaseTempReg(pParse
, regRowid
);
948 /* The bottom of the loop
950 sqlite3VdbeResolveLabel(v
, addrContinue
);
951 sqlite3VdbeAddOp2(v
, OP_Next
, iTab
, addr
);
952 sqlite3VdbeResolveLabel(v
, addrBreak
);
953 if( eDest
==SRT_Output
|| eDest
==SRT_Coroutine
){
954 sqlite3VdbeAddOp2(v
, OP_Close
, pseudoTab
, 0);
959 ** Return a pointer to a string containing the 'declaration type' of the
960 ** expression pExpr. The string may be treated as static by the caller.
962 ** The declaration type is the exact datatype definition extracted from the
963 ** original CREATE TABLE statement if the expression is a column. The
964 ** declaration type for a ROWID field is INTEGER. Exactly when an expression
965 ** is considered a column can be complex in the presence of subqueries. The
966 ** result-set expression in all of the following SELECT statements is
967 ** considered a column by this function.
969 ** SELECT col FROM tbl;
970 ** SELECT (SELECT col FROM tbl;
971 ** SELECT (SELECT col FROM tbl);
972 ** SELECT abc FROM (SELECT col AS abc FROM tbl);
974 ** The declaration type for any expression other than a column is NULL.
976 static const char *columnType(
979 const char **pzOriginDb
,
980 const char **pzOriginTab
,
981 const char **pzOriginCol
983 char const *zType
= 0;
984 char const *zOriginDb
= 0;
985 char const *zOriginTab
= 0;
986 char const *zOriginCol
= 0;
988 if( NEVER(pExpr
==0) || pNC
->pSrcList
==0 ) return 0;
993 /* The expression is a column. Locate the table the column is being
994 ** extracted from in NameContext.pSrcList. This table may be real
995 ** database table or a subquery.
997 Table
*pTab
= 0; /* Table structure column is extracted from */
998 Select
*pS
= 0; /* Select the column is extracted from */
999 int iCol
= pExpr
->iColumn
; /* Index of column in pTab */
1000 testcase( pExpr
->op
==TK_AGG_COLUMN
);
1001 testcase( pExpr
->op
==TK_COLUMN
);
1002 while( pNC
&& !pTab
){
1003 SrcList
*pTabList
= pNC
->pSrcList
;
1004 for(j
=0;j
<pTabList
->nSrc
&& pTabList
->a
[j
].iCursor
!=pExpr
->iTable
;j
++);
1005 if( j
<pTabList
->nSrc
){
1006 pTab
= pTabList
->a
[j
].pTab
;
1007 pS
= pTabList
->a
[j
].pSelect
;
1014 /* At one time, code such as "SELECT new.x" within a trigger would
1015 ** cause this condition to run. Since then, we have restructured how
1016 ** trigger code is generated and so this condition is no longer
1017 ** possible. However, it can still be true for statements like
1020 ** CREATE TABLE t1(col INTEGER);
1021 ** SELECT (SELECT t1.col) FROM FROM t1;
1023 ** when columnType() is called on the expression "t1.col" in the
1024 ** sub-select. In this case, set the column type to NULL, even
1025 ** though it should really be "INTEGER".
1027 ** This is not a problem, as the column type of "t1.col" is never
1028 ** used. When columnType() is called on the expression
1029 ** "(SELECT t1.col)", the correct type is returned (see the TK_SELECT
1034 assert( pTab
&& pExpr
->pTab
==pTab
);
1036 /* The "table" is actually a sub-select or a view in the FROM clause
1037 ** of the SELECT statement. Return the declaration type and origin
1038 ** data for the result-set column of the sub-select.
1040 if( iCol
>=0 && ALWAYS(iCol
<pS
->pEList
->nExpr
) ){
1041 /* If iCol is less than zero, then the expression requests the
1042 ** rowid of the sub-select or view. This expression is legal (see
1043 ** test case misc2.2.2) - it always evaluates to NULL.
1046 Expr
*p
= pS
->pEList
->a
[iCol
].pExpr
;
1047 sNC
.pSrcList
= pS
->pSrc
;
1049 sNC
.pParse
= pNC
->pParse
;
1050 zType
= columnType(&sNC
, p
, &zOriginDb
, &zOriginTab
, &zOriginCol
);
1052 }else if( ALWAYS(pTab
->pSchema
) ){
1055 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1056 assert( iCol
==-1 || (iCol
>=0 && iCol
<pTab
->nCol
) );
1059 zOriginCol
= "rowid";
1061 zType
= pTab
->aCol
[iCol
].zType
;
1062 zOriginCol
= pTab
->aCol
[iCol
].zName
;
1064 zOriginTab
= pTab
->zName
;
1066 int iDb
= sqlite3SchemaToIndex(pNC
->pParse
->db
, pTab
->pSchema
);
1067 zOriginDb
= pNC
->pParse
->db
->aDb
[iDb
].zName
;
1072 #ifndef SQLITE_OMIT_SUBQUERY
1074 /* The expression is a sub-select. Return the declaration type and
1075 ** origin info for the single column in the result set of the SELECT
1079 Select
*pS
= pExpr
->x
.pSelect
;
1080 Expr
*p
= pS
->pEList
->a
[0].pExpr
;
1081 assert( ExprHasProperty(pExpr
, EP_xIsSelect
) );
1082 sNC
.pSrcList
= pS
->pSrc
;
1084 sNC
.pParse
= pNC
->pParse
;
1085 zType
= columnType(&sNC
, p
, &zOriginDb
, &zOriginTab
, &zOriginCol
);
1092 assert( pzOriginTab
&& pzOriginCol
);
1093 *pzOriginDb
= zOriginDb
;
1094 *pzOriginTab
= zOriginTab
;
1095 *pzOriginCol
= zOriginCol
;
1101 ** Generate code that will tell the VDBE the declaration types of columns
1102 ** in the result set.
1104 static void generateColumnTypes(
1105 Parse
*pParse
, /* Parser context */
1106 SrcList
*pTabList
, /* List of tables */
1107 ExprList
*pEList
/* Expressions defining the result set */
1109 #ifndef SQLITE_OMIT_DECLTYPE
1110 Vdbe
*v
= pParse
->pVdbe
;
1113 sNC
.pSrcList
= pTabList
;
1114 sNC
.pParse
= pParse
;
1115 for(i
=0; i
<pEList
->nExpr
; i
++){
1116 Expr
*p
= pEList
->a
[i
].pExpr
;
1118 #ifdef SQLITE_ENABLE_COLUMN_METADATA
1119 const char *zOrigDb
= 0;
1120 const char *zOrigTab
= 0;
1121 const char *zOrigCol
= 0;
1122 zType
= columnType(&sNC
, p
, &zOrigDb
, &zOrigTab
, &zOrigCol
);
1124 /* The vdbe must make its own copy of the column-type and other
1125 ** column specific strings, in case the schema is reset before this
1126 ** virtual machine is deleted.
1128 sqlite3VdbeSetColName(v
, i
, COLNAME_DATABASE
, zOrigDb
, SQLITE_TRANSIENT
);
1129 sqlite3VdbeSetColName(v
, i
, COLNAME_TABLE
, zOrigTab
, SQLITE_TRANSIENT
);
1130 sqlite3VdbeSetColName(v
, i
, COLNAME_COLUMN
, zOrigCol
, SQLITE_TRANSIENT
);
1132 zType
= columnType(&sNC
, p
, 0, 0, 0);
1134 sqlite3VdbeSetColName(v
, i
, COLNAME_DECLTYPE
, zType
, SQLITE_TRANSIENT
);
1136 #endif /* SQLITE_OMIT_DECLTYPE */
1140 ** Generate code that will tell the VDBE the names of columns
1141 ** in the result set. This information is used to provide the
1142 ** azCol[] values in the callback.
1144 static void generateColumnNames(
1145 Parse
*pParse
, /* Parser context */
1146 SrcList
*pTabList
, /* List of tables */
1147 ExprList
*pEList
/* Expressions defining the result set */
1149 Vdbe
*v
= pParse
->pVdbe
;
1151 sqlite3
*db
= pParse
->db
;
1152 int fullNames
, shortNames
;
1154 #ifndef SQLITE_OMIT_EXPLAIN
1155 /* If this is an EXPLAIN, skip this step */
1156 if( pParse
->explain
){
1161 if( pParse
->colNamesSet
|| NEVER(v
==0) || db
->mallocFailed
) return;
1162 pParse
->colNamesSet
= 1;
1163 fullNames
= (db
->flags
& SQLITE_FullColNames
)!=0;
1164 shortNames
= (db
->flags
& SQLITE_ShortColNames
)!=0;
1165 sqlite3VdbeSetNumCols(v
, pEList
->nExpr
);
1166 for(i
=0; i
<pEList
->nExpr
; i
++){
1168 p
= pEList
->a
[i
].pExpr
;
1169 if( NEVER(p
==0) ) continue;
1170 if( pEList
->a
[i
].zName
){
1171 char *zName
= pEList
->a
[i
].zName
;
1172 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zName
, SQLITE_TRANSIENT
);
1173 }else if( (p
->op
==TK_COLUMN
|| p
->op
==TK_AGG_COLUMN
) && pTabList
){
1176 int iCol
= p
->iColumn
;
1177 for(j
=0; ALWAYS(j
<pTabList
->nSrc
); j
++){
1178 if( pTabList
->a
[j
].iCursor
==p
->iTable
) break;
1180 assert( j
<pTabList
->nSrc
);
1181 pTab
= pTabList
->a
[j
].pTab
;
1182 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1183 assert( iCol
==-1 || (iCol
>=0 && iCol
<pTab
->nCol
) );
1187 zCol
= pTab
->aCol
[iCol
].zName
;
1189 if( !shortNames
&& !fullNames
){
1190 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
,
1191 sqlite3DbStrDup(db
, pEList
->a
[i
].zSpan
), SQLITE_DYNAMIC
);
1192 }else if( fullNames
){
1194 zName
= sqlite3MPrintf(db
, "%s.%s", pTab
->zName
, zCol
);
1195 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zName
, SQLITE_DYNAMIC
);
1197 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
, zCol
, SQLITE_TRANSIENT
);
1200 sqlite3VdbeSetColName(v
, i
, COLNAME_NAME
,
1201 sqlite3DbStrDup(db
, pEList
->a
[i
].zSpan
), SQLITE_DYNAMIC
);
1204 generateColumnTypes(pParse
, pTabList
, pEList
);
1208 ** Given a an expression list (which is really the list of expressions
1209 ** that form the result set of a SELECT statement) compute appropriate
1210 ** column names for a table that would hold the expression list.
1212 ** All column names will be unique.
1214 ** Only the column names are computed. Column.zType, Column.zColl,
1215 ** and other fields of Column are zeroed.
1217 ** Return SQLITE_OK on success. If a memory allocation error occurs,
1218 ** store NULL in *paCol and 0 in *pnCol and return SQLITE_NOMEM.
1220 static int selectColumnsFromExprList(
1221 Parse
*pParse
, /* Parsing context */
1222 ExprList
*pEList
, /* Expr list from which to derive column names */
1223 int *pnCol
, /* Write the number of columns here */
1224 Column
**paCol
/* Write the new column list here */
1226 sqlite3
*db
= pParse
->db
; /* Database connection */
1227 int i
, j
; /* Loop counters */
1228 int cnt
; /* Index added to make the name unique */
1229 Column
*aCol
, *pCol
; /* For looping over result columns */
1230 int nCol
; /* Number of columns in the result set */
1231 Expr
*p
; /* Expression for a single result column */
1232 char *zName
; /* Column name */
1233 int nName
; /* Size of name in zName[] */
1235 *pnCol
= nCol
= pEList
->nExpr
;
1236 aCol
= *paCol
= sqlite3DbMallocZero(db
, sizeof(aCol
[0])*nCol
);
1237 if( aCol
==0 ) return SQLITE_NOMEM
;
1238 for(i
=0, pCol
=aCol
; i
<nCol
; i
++, pCol
++){
1239 /* Get an appropriate name for the column
1241 p
= pEList
->a
[i
].pExpr
;
1242 assert( p
->pRight
==0 || ExprHasProperty(p
->pRight
, EP_IntValue
)
1243 || p
->pRight
->u
.zToken
==0 || p
->pRight
->u
.zToken
[0]!=0 );
1244 if( (zName
= pEList
->a
[i
].zName
)!=0 ){
1245 /* If the column contains an "AS <name>" phrase, use <name> as the name */
1246 zName
= sqlite3DbStrDup(db
, zName
);
1248 Expr
*pColExpr
= p
; /* The expression that is the result column name */
1249 Table
*pTab
; /* Table associated with this expression */
1250 while( pColExpr
->op
==TK_DOT
) pColExpr
= pColExpr
->pRight
;
1251 if( pColExpr
->op
==TK_COLUMN
&& ALWAYS(pColExpr
->pTab
!=0) ){
1252 /* For columns use the column name name */
1253 int iCol
= pColExpr
->iColumn
;
1254 pTab
= pColExpr
->pTab
;
1255 if( iCol
<0 ) iCol
= pTab
->iPKey
;
1256 zName
= sqlite3MPrintf(db
, "%s",
1257 iCol
>=0 ? pTab
->aCol
[iCol
].zName
: "rowid");
1258 }else if( pColExpr
->op
==TK_ID
){
1259 assert( !ExprHasProperty(pColExpr
, EP_IntValue
) );
1260 zName
= sqlite3MPrintf(db
, "%s", pColExpr
->u
.zToken
);
1262 /* Use the original text of the column expression as its name */
1263 zName
= sqlite3MPrintf(db
, "%s", pEList
->a
[i
].zSpan
);
1266 if( db
->mallocFailed
){
1267 sqlite3DbFree(db
, zName
);
1271 /* Make sure the column name is unique. If the name is not unique,
1272 ** append a integer to the name so that it becomes unique.
1274 nName
= sqlite3Strlen30(zName
);
1275 for(j
=cnt
=0; j
<i
; j
++){
1276 if( sqlite3StrICmp(aCol
[j
].zName
, zName
)==0 ){
1279 zNewName
= sqlite3MPrintf(db
, "%s:%d", zName
, ++cnt
);
1280 sqlite3DbFree(db
, zName
);
1283 if( zName
==0 ) break;
1286 pCol
->zName
= zName
;
1288 if( db
->mallocFailed
){
1290 sqlite3DbFree(db
, aCol
[j
].zName
);
1292 sqlite3DbFree(db
, aCol
);
1295 return SQLITE_NOMEM
;
1301 ** Add type and collation information to a column list based on
1302 ** a SELECT statement.
1304 ** The column list presumably came from selectColumnNamesFromExprList().
1305 ** The column list has only names, not types or collations. This
1306 ** routine goes through and adds the types and collations.
1308 ** This routine requires that all identifiers in the SELECT
1309 ** statement be resolved.
1311 static void selectAddColumnTypeAndCollation(
1312 Parse
*pParse
, /* Parsing contexts */
1313 int nCol
, /* Number of columns */
1314 Column
*aCol
, /* List of columns */
1315 Select
*pSelect
/* SELECT used to determine types and collations */
1317 sqlite3
*db
= pParse
->db
;
1323 struct ExprList_item
*a
;
1325 assert( pSelect
!=0 );
1326 assert( (pSelect
->selFlags
& SF_Resolved
)!=0 );
1327 assert( nCol
==pSelect
->pEList
->nExpr
|| db
->mallocFailed
);
1328 if( db
->mallocFailed
) return;
1329 memset(&sNC
, 0, sizeof(sNC
));
1330 sNC
.pSrcList
= pSelect
->pSrc
;
1331 a
= pSelect
->pEList
->a
;
1332 for(i
=0, pCol
=aCol
; i
<nCol
; i
++, pCol
++){
1334 pCol
->zType
= sqlite3DbStrDup(db
, columnType(&sNC
, p
, 0, 0, 0));
1335 pCol
->affinity
= sqlite3ExprAffinity(p
);
1336 if( pCol
->affinity
==0 ) pCol
->affinity
= SQLITE_AFF_NONE
;
1337 pColl
= sqlite3ExprCollSeq(pParse
, p
);
1339 pCol
->zColl
= sqlite3DbStrDup(db
, pColl
->zName
);
1345 ** Given a SELECT statement, generate a Table structure that describes
1346 ** the result set of that SELECT.
1348 Table
*sqlite3ResultSetOfSelect(Parse
*pParse
, Select
*pSelect
){
1350 sqlite3
*db
= pParse
->db
;
1353 savedFlags
= db
->flags
;
1354 db
->flags
&= ~SQLITE_FullColNames
;
1355 db
->flags
|= SQLITE_ShortColNames
;
1356 sqlite3SelectPrep(pParse
, pSelect
, 0);
1357 if( pParse
->nErr
) return 0;
1358 while( pSelect
->pPrior
) pSelect
= pSelect
->pPrior
;
1359 db
->flags
= savedFlags
;
1360 pTab
= sqlite3DbMallocZero(db
, sizeof(Table
) );
1364 /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
1366 assert( db
->lookaside
.bEnabled
==0 );
1369 pTab
->nRowEst
= 1000000;
1370 selectColumnsFromExprList(pParse
, pSelect
->pEList
, &pTab
->nCol
, &pTab
->aCol
);
1371 selectAddColumnTypeAndCollation(pParse
, pTab
->nCol
, pTab
->aCol
, pSelect
);
1373 if( db
->mallocFailed
){
1374 sqlite3DeleteTable(db
, pTab
);
1381 ** Get a VDBE for the given parser context. Create a new one if necessary.
1382 ** If an error occurs, return NULL and leave a message in pParse.
1384 Vdbe
*sqlite3GetVdbe(Parse
*pParse
){
1385 Vdbe
*v
= pParse
->pVdbe
;
1387 v
= pParse
->pVdbe
= sqlite3VdbeCreate(pParse
->db
);
1388 #ifndef SQLITE_OMIT_TRACE
1390 sqlite3VdbeAddOp0(v
, OP_Trace
);
1399 ** Compute the iLimit and iOffset fields of the SELECT based on the
1400 ** pLimit and pOffset expressions. pLimit and pOffset hold the expressions
1401 ** that appear in the original SQL statement after the LIMIT and OFFSET
1402 ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset
1403 ** are the integer memory register numbers for counters used to compute
1404 ** the limit and offset. If there is no limit and/or offset, then
1405 ** iLimit and iOffset are negative.
1407 ** This routine changes the values of iLimit and iOffset only if
1408 ** a limit or offset is defined by pLimit and pOffset. iLimit and
1409 ** iOffset should have been preset to appropriate default values
1410 ** (usually but not always -1) prior to calling this routine.
1411 ** Only if pLimit!=0 or pOffset!=0 do the limit registers get
1412 ** redefined. The UNION ALL operator uses this property to force
1413 ** the reuse of the same limit and offset registers across multiple
1414 ** SELECT statements.
1416 static void computeLimitRegisters(Parse
*pParse
, Select
*p
, int iBreak
){
1421 if( p
->iLimit
) return;
1424 ** "LIMIT -1" always shows all rows. There is some
1425 ** contraversy about what the correct behavior should be.
1426 ** The current implementation interprets "LIMIT 0" to mean
1429 sqlite3ExprCacheClear(pParse
);
1430 assert( p
->pOffset
==0 || p
->pLimit
!=0 );
1432 p
->iLimit
= iLimit
= ++pParse
->nMem
;
1433 v
= sqlite3GetVdbe(pParse
);
1434 if( NEVER(v
==0) ) return; /* VDBE should have already been allocated */
1435 if( sqlite3ExprIsInteger(p
->pLimit
, &n
) ){
1436 sqlite3VdbeAddOp2(v
, OP_Integer
, n
, iLimit
);
1437 VdbeComment((v
, "LIMIT counter"));
1439 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, iBreak
);
1441 if( p
->nSelectRow
> (double)n
) p
->nSelectRow
= (double)n
;
1444 sqlite3ExprCode(pParse
, p
->pLimit
, iLimit
);
1445 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, iLimit
);
1446 VdbeComment((v
, "LIMIT counter"));
1447 sqlite3VdbeAddOp2(v
, OP_IfZero
, iLimit
, iBreak
);
1450 p
->iOffset
= iOffset
= ++pParse
->nMem
;
1451 pParse
->nMem
++; /* Allocate an extra register for limit+offset */
1452 sqlite3ExprCode(pParse
, p
->pOffset
, iOffset
);
1453 sqlite3VdbeAddOp1(v
, OP_MustBeInt
, iOffset
);
1454 VdbeComment((v
, "OFFSET counter"));
1455 addr1
= sqlite3VdbeAddOp1(v
, OP_IfPos
, iOffset
);
1456 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, iOffset
);
1457 sqlite3VdbeJumpHere(v
, addr1
);
1458 sqlite3VdbeAddOp3(v
, OP_Add
, iLimit
, iOffset
, iOffset
+1);
1459 VdbeComment((v
, "LIMIT+OFFSET"));
1460 addr1
= sqlite3VdbeAddOp1(v
, OP_IfPos
, iLimit
);
1461 sqlite3VdbeAddOp2(v
, OP_Integer
, -1, iOffset
+1);
1462 sqlite3VdbeJumpHere(v
, addr1
);
1467 #ifndef SQLITE_OMIT_COMPOUND_SELECT
1469 ** Return the appropriate collating sequence for the iCol-th column of
1470 ** the result set for the compound-select statement "p". Return NULL if
1471 ** the column has no default collating sequence.
1473 ** The collating sequence for the compound select is taken from the
1474 ** left-most term of the select that has a collating sequence.
1476 static CollSeq
*multiSelectCollSeq(Parse
*pParse
, Select
*p
, int iCol
){
1479 pRet
= multiSelectCollSeq(pParse
, p
->pPrior
, iCol
);
1484 if( pRet
==0 && iCol
<p
->pEList
->nExpr
){
1485 pRet
= sqlite3ExprCollSeq(pParse
, p
->pEList
->a
[iCol
].pExpr
);
1489 #endif /* SQLITE_OMIT_COMPOUND_SELECT */
1491 /* Forward reference */
1492 static int multiSelectOrderBy(
1493 Parse
*pParse
, /* Parsing context */
1494 Select
*p
, /* The right-most of SELECTs to be coded */
1495 SelectDest
*pDest
/* What to do with query results */
1499 #ifndef SQLITE_OMIT_COMPOUND_SELECT
1501 ** This routine is called to process a compound query form from
1502 ** two or more separate queries using UNION, UNION ALL, EXCEPT, or
1505 ** "p" points to the right-most of the two queries. the query on the
1506 ** left is p->pPrior. The left query could also be a compound query
1507 ** in which case this routine will be called recursively.
1509 ** The results of the total query are to be written into a destination
1510 ** of type eDest with parameter iParm.
1512 ** Example 1: Consider a three-way compound SQL statement.
1514 ** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
1516 ** This statement is parsed up as follows:
1520 ** `-----> SELECT b FROM t2
1522 ** `------> SELECT a FROM t1
1524 ** The arrows in the diagram above represent the Select.pPrior pointer.
1525 ** So if this routine is called with p equal to the t3 query, then
1526 ** pPrior will be the t2 query. p->op will be TK_UNION in this case.
1528 ** Notice that because of the way SQLite parses compound SELECTs, the
1529 ** individual selects always group from left to right.
1531 static int multiSelect(
1532 Parse
*pParse
, /* Parsing context */
1533 Select
*p
, /* The right-most of SELECTs to be coded */
1534 SelectDest
*pDest
/* What to do with query results */
1536 int rc
= SQLITE_OK
; /* Success code from a subroutine */
1537 Select
*pPrior
; /* Another SELECT immediately to our left */
1538 Vdbe
*v
; /* Generate code to this VDBE */
1539 SelectDest dest
; /* Alternative data destination */
1540 Select
*pDelete
= 0; /* Chain of simple selects to delete */
1541 sqlite3
*db
; /* Database connection */
1542 #ifndef SQLITE_OMIT_EXPLAIN
1543 int iSub1
; /* EQP id of left-hand query */
1544 int iSub2
; /* EQP id of right-hand query */
1547 /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
1548 ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
1550 assert( p
&& p
->pPrior
); /* Calling function guarantees this much */
1553 assert( pPrior
->pRightmost
!=pPrior
);
1554 assert( pPrior
->pRightmost
==p
->pRightmost
);
1556 if( pPrior
->pOrderBy
){
1557 sqlite3ErrorMsg(pParse
,"ORDER BY clause should come after %s not before",
1558 selectOpName(p
->op
));
1560 goto multi_select_end
;
1562 if( pPrior
->pLimit
){
1563 sqlite3ErrorMsg(pParse
,"LIMIT clause should come after %s not before",
1564 selectOpName(p
->op
));
1566 goto multi_select_end
;
1569 v
= sqlite3GetVdbe(pParse
);
1570 assert( v
!=0 ); /* The VDBE already created by calling function */
1572 /* Create the destination temporary table if necessary
1574 if( dest
.eDest
==SRT_EphemTab
){
1575 assert( p
->pEList
);
1576 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, dest
.iParm
, p
->pEList
->nExpr
);
1577 sqlite3VdbeChangeP5(v
, BTREE_UNORDERED
);
1578 dest
.eDest
= SRT_Table
;
1581 /* Make sure all SELECTs in the statement have the same number of elements
1582 ** in their result sets.
1584 assert( p
->pEList
&& pPrior
->pEList
);
1585 if( p
->pEList
->nExpr
!=pPrior
->pEList
->nExpr
){
1586 sqlite3ErrorMsg(pParse
, "SELECTs to the left and right of %s"
1587 " do not have the same number of result columns", selectOpName(p
->op
));
1589 goto multi_select_end
;
1592 /* Compound SELECTs that have an ORDER BY clause are handled separately.
1595 return multiSelectOrderBy(pParse
, p
, pDest
);
1598 /* Generate code for the left and right SELECT statements.
1604 assert( !pPrior
->pLimit
);
1605 pPrior
->pLimit
= p
->pLimit
;
1606 pPrior
->pOffset
= p
->pOffset
;
1607 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
1608 rc
= sqlite3Select(pParse
, pPrior
, &dest
);
1612 goto multi_select_end
;
1615 p
->iLimit
= pPrior
->iLimit
;
1616 p
->iOffset
= pPrior
->iOffset
;
1618 addr
= sqlite3VdbeAddOp1(v
, OP_IfZero
, p
->iLimit
);
1619 VdbeComment((v
, "Jump ahead if LIMIT reached"));
1621 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
1622 rc
= sqlite3Select(pParse
, p
, &dest
);
1623 testcase( rc
!=SQLITE_OK
);
1624 pDelete
= p
->pPrior
;
1626 p
->nSelectRow
+= pPrior
->nSelectRow
;
1628 && sqlite3ExprIsInteger(pPrior
->pLimit
, &nLimit
)
1629 && p
->nSelectRow
> (double)nLimit
1631 p
->nSelectRow
= (double)nLimit
;
1634 sqlite3VdbeJumpHere(v
, addr
);
1640 int unionTab
; /* Cursor number of the temporary table holding result */
1641 u8 op
= 0; /* One of the SRT_ operations to apply to self */
1642 int priorOp
; /* The SRT_ operation to apply to prior selects */
1643 Expr
*pLimit
, *pOffset
; /* Saved values of p->nLimit and p->nOffset */
1645 SelectDest uniondest
;
1647 testcase( p
->op
==TK_EXCEPT
);
1648 testcase( p
->op
==TK_UNION
);
1649 priorOp
= SRT_Union
;
1650 if( dest
.eDest
==priorOp
&& ALWAYS(!p
->pLimit
&&!p
->pOffset
) ){
1651 /* We can reuse a temporary table generated by a SELECT to our
1654 assert( p
->pRightmost
!=p
); /* Can only happen for leftward elements
1655 ** of a 3-way or more compound */
1656 assert( p
->pLimit
==0 ); /* Not allowed on leftward elements */
1657 assert( p
->pOffset
==0 ); /* Not allowed on leftward elements */
1658 unionTab
= dest
.iParm
;
1660 /* We will need to create our own temporary table to hold the
1661 ** intermediate results.
1663 unionTab
= pParse
->nTab
++;
1664 assert( p
->pOrderBy
==0 );
1665 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, unionTab
, 0);
1666 assert( p
->addrOpenEphm
[0] == -1 );
1667 p
->addrOpenEphm
[0] = addr
;
1668 p
->pRightmost
->selFlags
|= SF_UsesEphemeral
;
1669 assert( p
->pEList
);
1672 /* Code the SELECT statements to our left
1674 assert( !pPrior
->pOrderBy
);
1675 sqlite3SelectDestInit(&uniondest
, priorOp
, unionTab
);
1676 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
1677 rc
= sqlite3Select(pParse
, pPrior
, &uniondest
);
1679 goto multi_select_end
;
1682 /* Code the current SELECT statement
1684 if( p
->op
==TK_EXCEPT
){
1687 assert( p
->op
==TK_UNION
);
1693 pOffset
= p
->pOffset
;
1695 uniondest
.eDest
= op
;
1696 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
1697 rc
= sqlite3Select(pParse
, p
, &uniondest
);
1698 testcase( rc
!=SQLITE_OK
);
1699 /* Query flattening in sqlite3Select() might refill p->pOrderBy.
1700 ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
1701 sqlite3ExprListDelete(db
, p
->pOrderBy
);
1702 pDelete
= p
->pPrior
;
1705 if( p
->op
==TK_UNION
) p
->nSelectRow
+= pPrior
->nSelectRow
;
1706 sqlite3ExprDelete(db
, p
->pLimit
);
1708 p
->pOffset
= pOffset
;
1712 /* Convert the data in the temporary table into whatever form
1713 ** it is that we currently need.
1715 assert( unionTab
==dest
.iParm
|| dest
.eDest
!=priorOp
);
1716 if( dest
.eDest
!=priorOp
){
1717 int iCont
, iBreak
, iStart
;
1718 assert( p
->pEList
);
1719 if( dest
.eDest
==SRT_Output
){
1721 while( pFirst
->pPrior
) pFirst
= pFirst
->pPrior
;
1722 generateColumnNames(pParse
, 0, pFirst
->pEList
);
1724 iBreak
= sqlite3VdbeMakeLabel(v
);
1725 iCont
= sqlite3VdbeMakeLabel(v
);
1726 computeLimitRegisters(pParse
, p
, iBreak
);
1727 sqlite3VdbeAddOp2(v
, OP_Rewind
, unionTab
, iBreak
);
1728 iStart
= sqlite3VdbeCurrentAddr(v
);
1729 selectInnerLoop(pParse
, p
, p
->pEList
, unionTab
, p
->pEList
->nExpr
,
1730 0, -1, &dest
, iCont
, iBreak
);
1731 sqlite3VdbeResolveLabel(v
, iCont
);
1732 sqlite3VdbeAddOp2(v
, OP_Next
, unionTab
, iStart
);
1733 sqlite3VdbeResolveLabel(v
, iBreak
);
1734 sqlite3VdbeAddOp2(v
, OP_Close
, unionTab
, 0);
1738 default: assert( p
->op
==TK_INTERSECT
); {
1740 int iCont
, iBreak
, iStart
;
1741 Expr
*pLimit
, *pOffset
;
1743 SelectDest intersectdest
;
1746 /* INTERSECT is different from the others since it requires
1747 ** two temporary tables. Hence it has its own case. Begin
1748 ** by allocating the tables we will need.
1750 tab1
= pParse
->nTab
++;
1751 tab2
= pParse
->nTab
++;
1752 assert( p
->pOrderBy
==0 );
1754 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, tab1
, 0);
1755 assert( p
->addrOpenEphm
[0] == -1 );
1756 p
->addrOpenEphm
[0] = addr
;
1757 p
->pRightmost
->selFlags
|= SF_UsesEphemeral
;
1758 assert( p
->pEList
);
1760 /* Code the SELECTs to our left into temporary table "tab1".
1762 sqlite3SelectDestInit(&intersectdest
, SRT_Union
, tab1
);
1763 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
1764 rc
= sqlite3Select(pParse
, pPrior
, &intersectdest
);
1766 goto multi_select_end
;
1769 /* Code the current SELECT into temporary table "tab2"
1771 addr
= sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, tab2
, 0);
1772 assert( p
->addrOpenEphm
[1] == -1 );
1773 p
->addrOpenEphm
[1] = addr
;
1777 pOffset
= p
->pOffset
;
1779 intersectdest
.iParm
= tab2
;
1780 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
1781 rc
= sqlite3Select(pParse
, p
, &intersectdest
);
1782 testcase( rc
!=SQLITE_OK
);
1783 pDelete
= p
->pPrior
;
1785 if( p
->nSelectRow
>pPrior
->nSelectRow
) p
->nSelectRow
= pPrior
->nSelectRow
;
1786 sqlite3ExprDelete(db
, p
->pLimit
);
1788 p
->pOffset
= pOffset
;
1790 /* Generate code to take the intersection of the two temporary
1793 assert( p
->pEList
);
1794 if( dest
.eDest
==SRT_Output
){
1796 while( pFirst
->pPrior
) pFirst
= pFirst
->pPrior
;
1797 generateColumnNames(pParse
, 0, pFirst
->pEList
);
1799 iBreak
= sqlite3VdbeMakeLabel(v
);
1800 iCont
= sqlite3VdbeMakeLabel(v
);
1801 computeLimitRegisters(pParse
, p
, iBreak
);
1802 sqlite3VdbeAddOp2(v
, OP_Rewind
, tab1
, iBreak
);
1803 r1
= sqlite3GetTempReg(pParse
);
1804 iStart
= sqlite3VdbeAddOp2(v
, OP_RowKey
, tab1
, r1
);
1805 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, tab2
, iCont
, r1
, 0);
1806 sqlite3ReleaseTempReg(pParse
, r1
);
1807 selectInnerLoop(pParse
, p
, p
->pEList
, tab1
, p
->pEList
->nExpr
,
1808 0, -1, &dest
, iCont
, iBreak
);
1809 sqlite3VdbeResolveLabel(v
, iCont
);
1810 sqlite3VdbeAddOp2(v
, OP_Next
, tab1
, iStart
);
1811 sqlite3VdbeResolveLabel(v
, iBreak
);
1812 sqlite3VdbeAddOp2(v
, OP_Close
, tab2
, 0);
1813 sqlite3VdbeAddOp2(v
, OP_Close
, tab1
, 0);
1818 explainComposite(pParse
, p
->op
, iSub1
, iSub2
, p
->op
!=TK_ALL
);
1820 /* Compute collating sequences used by
1821 ** temporary tables needed to implement the compound select.
1822 ** Attach the KeyInfo structure to all temporary tables.
1824 ** This section is run by the right-most SELECT statement only.
1825 ** SELECT statements to the left always skip this part. The right-most
1826 ** SELECT might also skip this part if it has no ORDER BY clause and
1827 ** no temp tables are required.
1829 if( p
->selFlags
& SF_UsesEphemeral
){
1830 int i
; /* Loop counter */
1831 KeyInfo
*pKeyInfo
; /* Collating sequence for the result set */
1832 Select
*pLoop
; /* For looping through SELECT statements */
1833 CollSeq
**apColl
; /* For looping through pKeyInfo->aColl[] */
1834 int nCol
; /* Number of columns in result set */
1836 assert( p
->pRightmost
==p
);
1837 nCol
= p
->pEList
->nExpr
;
1838 pKeyInfo
= sqlite3DbMallocZero(db
,
1839 sizeof(*pKeyInfo
)+nCol
*(sizeof(CollSeq
*) + 1));
1842 goto multi_select_end
;
1845 pKeyInfo
->enc
= ENC(db
);
1846 pKeyInfo
->nField
= (u16
)nCol
;
1848 for(i
=0, apColl
=pKeyInfo
->aColl
; i
<nCol
; i
++, apColl
++){
1849 *apColl
= multiSelectCollSeq(pParse
, p
, i
);
1851 *apColl
= db
->pDfltColl
;
1855 for(pLoop
=p
; pLoop
; pLoop
=pLoop
->pPrior
){
1857 int addr
= pLoop
->addrOpenEphm
[i
];
1859 /* If [0] is unused then [1] is also unused. So we can
1860 ** always safely abort as soon as the first unused slot is found */
1861 assert( pLoop
->addrOpenEphm
[1]<0 );
1864 sqlite3VdbeChangeP2(v
, addr
, nCol
);
1865 sqlite3VdbeChangeP4(v
, addr
, (char*)pKeyInfo
, P4_KEYINFO
);
1866 pLoop
->addrOpenEphm
[i
] = -1;
1869 sqlite3DbFree(db
, pKeyInfo
);
1873 pDest
->iMem
= dest
.iMem
;
1874 pDest
->nMem
= dest
.nMem
;
1875 sqlite3SelectDelete(db
, pDelete
);
1878 #endif /* SQLITE_OMIT_COMPOUND_SELECT */
1881 ** Code an output subroutine for a coroutine implementation of a
1884 ** The data to be output is contained in pIn->iMem. There are
1885 ** pIn->nMem columns to be output. pDest is where the output should
1888 ** regReturn is the number of the register holding the subroutine
1891 ** If regPrev>0 then it is the first register in a vector that
1892 ** records the previous output. mem[regPrev] is a flag that is false
1893 ** if there has been no previous output. If regPrev>0 then code is
1894 ** generated to suppress duplicates. pKeyInfo is used for comparing
1897 ** If the LIMIT found in p->iLimit is reached, jump immediately to
1900 static int generateOutputSubroutine(
1901 Parse
*pParse
, /* Parsing context */
1902 Select
*p
, /* The SELECT statement */
1903 SelectDest
*pIn
, /* Coroutine supplying data */
1904 SelectDest
*pDest
, /* Where to send the data */
1905 int regReturn
, /* The return address register */
1906 int regPrev
, /* Previous result register. No uniqueness if 0 */
1907 KeyInfo
*pKeyInfo
, /* For comparing with previous entry */
1908 int p4type
, /* The p4 type for pKeyInfo */
1909 int iBreak
/* Jump here if we hit the LIMIT */
1911 Vdbe
*v
= pParse
->pVdbe
;
1915 addr
= sqlite3VdbeCurrentAddr(v
);
1916 iContinue
= sqlite3VdbeMakeLabel(v
);
1918 /* Suppress duplicates for UNION, EXCEPT, and INTERSECT
1922 j1
= sqlite3VdbeAddOp1(v
, OP_IfNot
, regPrev
);
1923 j2
= sqlite3VdbeAddOp4(v
, OP_Compare
, pIn
->iMem
, regPrev
+1, pIn
->nMem
,
1924 (char*)pKeyInfo
, p4type
);
1925 sqlite3VdbeAddOp3(v
, OP_Jump
, j2
+2, iContinue
, j2
+2);
1926 sqlite3VdbeJumpHere(v
, j1
);
1927 sqlite3ExprCodeCopy(pParse
, pIn
->iMem
, regPrev
+1, pIn
->nMem
);
1928 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, regPrev
);
1930 if( pParse
->db
->mallocFailed
) return 0;
1932 /* Suppress the the first OFFSET entries if there is an OFFSET clause
1934 codeOffset(v
, p
, iContinue
);
1936 switch( pDest
->eDest
){
1937 /* Store the result as data using a unique key.
1940 case SRT_EphemTab
: {
1941 int r1
= sqlite3GetTempReg(pParse
);
1942 int r2
= sqlite3GetTempReg(pParse
);
1943 testcase( pDest
->eDest
==SRT_Table
);
1944 testcase( pDest
->eDest
==SRT_EphemTab
);
1945 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, pIn
->iMem
, pIn
->nMem
, r1
);
1946 sqlite3VdbeAddOp2(v
, OP_NewRowid
, pDest
->iParm
, r2
);
1947 sqlite3VdbeAddOp3(v
, OP_Insert
, pDest
->iParm
, r1
, r2
);
1948 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
1949 sqlite3ReleaseTempReg(pParse
, r2
);
1950 sqlite3ReleaseTempReg(pParse
, r1
);
1954 #ifndef SQLITE_OMIT_SUBQUERY
1955 /* If we are creating a set for an "expr IN (SELECT ...)" construct,
1956 ** then there should be a single item on the stack. Write this
1957 ** item into the set table with bogus data.
1961 assert( pIn
->nMem
==1 );
1963 sqlite3CompareAffinity(p
->pEList
->a
[0].pExpr
, pDest
->affinity
);
1964 r1
= sqlite3GetTempReg(pParse
);
1965 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, pIn
->iMem
, 1, r1
, &p
->affinity
, 1);
1966 sqlite3ExprCacheAffinityChange(pParse
, pIn
->iMem
, 1);
1967 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, pDest
->iParm
, r1
);
1968 sqlite3ReleaseTempReg(pParse
, r1
);
1972 #if 0 /* Never occurs on an ORDER BY query */
1973 /* If any row exist in the result set, record that fact and abort.
1976 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, pDest
->iParm
);
1977 /* The LIMIT clause will terminate the loop for us */
1982 /* If this is a scalar select that is part of an expression, then
1983 ** store the results in the appropriate memory cell and break out
1984 ** of the scan loop.
1987 assert( pIn
->nMem
==1 );
1988 sqlite3ExprCodeMove(pParse
, pIn
->iMem
, pDest
->iParm
, 1);
1989 /* The LIMIT clause will jump out of the loop for us */
1992 #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
1994 /* The results are stored in a sequence of registers
1995 ** starting at pDest->iMem. Then the co-routine yields.
1997 case SRT_Coroutine
: {
1998 if( pDest
->iMem
==0 ){
1999 pDest
->iMem
= sqlite3GetTempRange(pParse
, pIn
->nMem
);
2000 pDest
->nMem
= pIn
->nMem
;
2002 sqlite3ExprCodeMove(pParse
, pIn
->iMem
, pDest
->iMem
, pDest
->nMem
);
2003 sqlite3VdbeAddOp1(v
, OP_Yield
, pDest
->iParm
);
2007 /* If none of the above, then the result destination must be
2008 ** SRT_Output. This routine is never called with any other
2009 ** destination other than the ones handled above or SRT_Output.
2011 ** For SRT_Output, results are stored in a sequence of registers.
2012 ** Then the OP_ResultRow opcode is used to cause sqlite3_step() to
2013 ** return the next row of result.
2016 assert( pDest
->eDest
==SRT_Output
);
2017 sqlite3VdbeAddOp2(v
, OP_ResultRow
, pIn
->iMem
, pIn
->nMem
);
2018 sqlite3ExprCacheAffinityChange(pParse
, pIn
->iMem
, pIn
->nMem
);
2023 /* Jump to the end of the loop if the LIMIT is reached.
2026 sqlite3VdbeAddOp3(v
, OP_IfZero
, p
->iLimit
, iBreak
, -1);
2029 /* Generate the subroutine return
2031 sqlite3VdbeResolveLabel(v
, iContinue
);
2032 sqlite3VdbeAddOp1(v
, OP_Return
, regReturn
);
2038 ** Alternative compound select code generator for cases when there
2039 ** is an ORDER BY clause.
2041 ** We assume a query of the following form:
2043 ** <selectA> <operator> <selectB> ORDER BY <orderbylist>
2045 ** <operator> is one of UNION ALL, UNION, EXCEPT, or INTERSECT. The idea
2046 ** is to code both <selectA> and <selectB> with the ORDER BY clause as
2047 ** co-routines. Then run the co-routines in parallel and merge the results
2048 ** into the output. In addition to the two coroutines (called selectA and
2049 ** selectB) there are 7 subroutines:
2051 ** outA: Move the output of the selectA coroutine into the output
2052 ** of the compound query.
2054 ** outB: Move the output of the selectB coroutine into the output
2055 ** of the compound query. (Only generated for UNION and
2056 ** UNION ALL. EXCEPT and INSERTSECT never output a row that
2057 ** appears only in B.)
2059 ** AltB: Called when there is data from both coroutines and A<B.
2061 ** AeqB: Called when there is data from both coroutines and A==B.
2063 ** AgtB: Called when there is data from both coroutines and A>B.
2065 ** EofA: Called when data is exhausted from selectA.
2067 ** EofB: Called when data is exhausted from selectB.
2069 ** The implementation of the latter five subroutines depend on which
2070 ** <operator> is used:
2073 ** UNION ALL UNION EXCEPT INTERSECT
2074 ** ------------- ----------------- -------------- -----------------
2075 ** AltB: outA, nextA outA, nextA outA, nextA nextA
2077 ** AeqB: outA, nextA nextA nextA outA, nextA
2079 ** AgtB: outB, nextB outB, nextB nextB nextB
2081 ** EofA: outB, nextB outB, nextB halt halt
2083 ** EofB: outA, nextA outA, nextA outA, nextA halt
2085 ** In the AltB, AeqB, and AgtB subroutines, an EOF on A following nextA
2086 ** causes an immediate jump to EofA and an EOF on B following nextB causes
2087 ** an immediate jump to EofB. Within EofA and EofB, and EOF on entry or
2088 ** following nextX causes a jump to the end of the select processing.
2090 ** Duplicate removal in the UNION, EXCEPT, and INTERSECT cases is handled
2091 ** within the output subroutine. The regPrev register set holds the previously
2092 ** output value. A comparison is made against this value and the output
2093 ** is skipped if the next results would be the same as the previous.
2095 ** The implementation plan is to implement the two coroutines and seven
2096 ** subroutines first, then put the control logic at the bottom. Like this:
2099 ** coA: coroutine for left query (A)
2100 ** coB: coroutine for right query (B)
2101 ** outA: output one row of A
2102 ** outB: output one row of B (UNION and UNION ALL only)
2108 ** Init: initialize coroutine registers
2110 ** if eof(A) goto EofA
2112 ** if eof(B) goto EofB
2113 ** Cmpr: Compare A, B
2114 ** Jump AltB, AeqB, AgtB
2117 ** We call AltB, AeqB, AgtB, EofA, and EofB "subroutines" but they are not
2118 ** actually called using Gosub and they do not Return. EofA and EofB loop
2119 ** until all data is exhausted then jump to the "end" labe. AltB, AeqB,
2120 ** and AgtB jump to either L2 or to one of EofA or EofB.
2122 #ifndef SQLITE_OMIT_COMPOUND_SELECT
2123 static int multiSelectOrderBy(
2124 Parse
*pParse
, /* Parsing context */
2125 Select
*p
, /* The right-most of SELECTs to be coded */
2126 SelectDest
*pDest
/* What to do with query results */
2128 int i
, j
; /* Loop counters */
2129 Select
*pPrior
; /* Another SELECT immediately to our left */
2130 Vdbe
*v
; /* Generate code to this VDBE */
2131 SelectDest destA
; /* Destination for coroutine A */
2132 SelectDest destB
; /* Destination for coroutine B */
2133 int regAddrA
; /* Address register for select-A coroutine */
2134 int regEofA
; /* Flag to indicate when select-A is complete */
2135 int regAddrB
; /* Address register for select-B coroutine */
2136 int regEofB
; /* Flag to indicate when select-B is complete */
2137 int addrSelectA
; /* Address of the select-A coroutine */
2138 int addrSelectB
; /* Address of the select-B coroutine */
2139 int regOutA
; /* Address register for the output-A subroutine */
2140 int regOutB
; /* Address register for the output-B subroutine */
2141 int addrOutA
; /* Address of the output-A subroutine */
2142 int addrOutB
= 0; /* Address of the output-B subroutine */
2143 int addrEofA
; /* Address of the select-A-exhausted subroutine */
2144 int addrEofB
; /* Address of the select-B-exhausted subroutine */
2145 int addrAltB
; /* Address of the A<B subroutine */
2146 int addrAeqB
; /* Address of the A==B subroutine */
2147 int addrAgtB
; /* Address of the A>B subroutine */
2148 int regLimitA
; /* Limit register for select-A */
2149 int regLimitB
; /* Limit register for select-A */
2150 int regPrev
; /* A range of registers to hold previous output */
2151 int savedLimit
; /* Saved value of p->iLimit */
2152 int savedOffset
; /* Saved value of p->iOffset */
2153 int labelCmpr
; /* Label for the start of the merge algorithm */
2154 int labelEnd
; /* Label for the end of the overall SELECT stmt */
2155 int j1
; /* Jump instructions that get retargetted */
2156 int op
; /* One of TK_ALL, TK_UNION, TK_EXCEPT, TK_INTERSECT */
2157 KeyInfo
*pKeyDup
= 0; /* Comparison information for duplicate removal */
2158 KeyInfo
*pKeyMerge
; /* Comparison information for merging rows */
2159 sqlite3
*db
; /* Database connection */
2160 ExprList
*pOrderBy
; /* The ORDER BY clause */
2161 int nOrderBy
; /* Number of terms in the ORDER BY clause */
2162 int *aPermute
; /* Mapping from ORDER BY terms to result set columns */
2163 #ifndef SQLITE_OMIT_EXPLAIN
2164 int iSub1
; /* EQP id of left-hand query */
2165 int iSub2
; /* EQP id of right-hand query */
2168 assert( p
->pOrderBy
!=0 );
2169 assert( pKeyDup
==0 ); /* "Managed" code needs this. Ticket #3382. */
2172 assert( v
!=0 ); /* Already thrown the error if VDBE alloc failed */
2173 labelEnd
= sqlite3VdbeMakeLabel(v
);
2174 labelCmpr
= sqlite3VdbeMakeLabel(v
);
2177 /* Patch up the ORDER BY clause
2181 assert( pPrior
->pOrderBy
==0 );
2182 pOrderBy
= p
->pOrderBy
;
2184 nOrderBy
= pOrderBy
->nExpr
;
2186 /* For operators other than UNION ALL we have to make sure that
2187 ** the ORDER BY clause covers every term of the result set. Add
2188 ** terms to the ORDER BY clause as necessary.
2191 for(i
=1; db
->mallocFailed
==0 && i
<=p
->pEList
->nExpr
; i
++){
2192 struct ExprList_item
*pItem
;
2193 for(j
=0, pItem
=pOrderBy
->a
; j
<nOrderBy
; j
++, pItem
++){
2194 assert( pItem
->iCol
>0 );
2195 if( pItem
->iCol
==i
) break;
2198 Expr
*pNew
= sqlite3Expr(db
, TK_INTEGER
, 0);
2199 if( pNew
==0 ) return SQLITE_NOMEM
;
2200 pNew
->flags
|= EP_IntValue
;
2202 pOrderBy
= sqlite3ExprListAppend(pParse
, pOrderBy
, pNew
);
2203 pOrderBy
->a
[nOrderBy
++].iCol
= (u16
)i
;
2208 /* Compute the comparison permutation and keyinfo that is used with
2209 ** the permutation used to determine if the next
2210 ** row of results comes from selectA or selectB. Also add explicit
2211 ** collations to the ORDER BY clause terms so that when the subqueries
2212 ** to the right and the left are evaluated, they use the correct
2215 aPermute
= sqlite3DbMallocRaw(db
, sizeof(int)*nOrderBy
);
2217 struct ExprList_item
*pItem
;
2218 for(i
=0, pItem
=pOrderBy
->a
; i
<nOrderBy
; i
++, pItem
++){
2219 assert( pItem
->iCol
>0 && pItem
->iCol
<=p
->pEList
->nExpr
);
2220 aPermute
[i
] = pItem
->iCol
- 1;
2223 sqlite3DbMallocRaw(db
, sizeof(*pKeyMerge
)+nOrderBy
*(sizeof(CollSeq
*)+1));
2225 pKeyMerge
->aSortOrder
= (u8
*)&pKeyMerge
->aColl
[nOrderBy
];
2226 pKeyMerge
->nField
= (u16
)nOrderBy
;
2227 pKeyMerge
->enc
= ENC(db
);
2228 for(i
=0; i
<nOrderBy
; i
++){
2230 Expr
*pTerm
= pOrderBy
->a
[i
].pExpr
;
2231 if( pTerm
->flags
& EP_ExpCollate
){
2232 pColl
= pTerm
->pColl
;
2234 pColl
= multiSelectCollSeq(pParse
, p
, aPermute
[i
]);
2235 pTerm
->flags
|= EP_ExpCollate
;
2236 pTerm
->pColl
= pColl
;
2238 pKeyMerge
->aColl
[i
] = pColl
;
2239 pKeyMerge
->aSortOrder
[i
] = pOrderBy
->a
[i
].sortOrder
;
2246 /* Reattach the ORDER BY clause to the query.
2248 p
->pOrderBy
= pOrderBy
;
2249 pPrior
->pOrderBy
= sqlite3ExprListDup(pParse
->db
, pOrderBy
, 0);
2251 /* Allocate a range of temporary registers and the KeyInfo needed
2252 ** for the logic that removes duplicate result rows when the
2253 ** operator is UNION, EXCEPT, or INTERSECT (but not UNION ALL).
2258 int nExpr
= p
->pEList
->nExpr
;
2259 assert( nOrderBy
>=nExpr
|| db
->mallocFailed
);
2260 regPrev
= sqlite3GetTempRange(pParse
, nExpr
+1);
2261 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regPrev
);
2262 pKeyDup
= sqlite3DbMallocZero(db
,
2263 sizeof(*pKeyDup
) + nExpr
*(sizeof(CollSeq
*)+1) );
2265 pKeyDup
->aSortOrder
= (u8
*)&pKeyDup
->aColl
[nExpr
];
2266 pKeyDup
->nField
= (u16
)nExpr
;
2267 pKeyDup
->enc
= ENC(db
);
2268 for(i
=0; i
<nExpr
; i
++){
2269 pKeyDup
->aColl
[i
] = multiSelectCollSeq(pParse
, p
, i
);
2270 pKeyDup
->aSortOrder
[i
] = 0;
2275 /* Separate the left and the right query from one another
2278 sqlite3ResolveOrderGroupBy(pParse
, p
, p
->pOrderBy
, "ORDER");
2279 if( pPrior
->pPrior
==0 ){
2280 sqlite3ResolveOrderGroupBy(pParse
, pPrior
, pPrior
->pOrderBy
, "ORDER");
2283 /* Compute the limit registers */
2284 computeLimitRegisters(pParse
, p
, labelEnd
);
2285 if( p
->iLimit
&& op
==TK_ALL
){
2286 regLimitA
= ++pParse
->nMem
;
2287 regLimitB
= ++pParse
->nMem
;
2288 sqlite3VdbeAddOp2(v
, OP_Copy
, p
->iOffset
? p
->iOffset
+1 : p
->iLimit
,
2290 sqlite3VdbeAddOp2(v
, OP_Copy
, regLimitA
, regLimitB
);
2292 regLimitA
= regLimitB
= 0;
2294 sqlite3ExprDelete(db
, p
->pLimit
);
2296 sqlite3ExprDelete(db
, p
->pOffset
);
2299 regAddrA
= ++pParse
->nMem
;
2300 regEofA
= ++pParse
->nMem
;
2301 regAddrB
= ++pParse
->nMem
;
2302 regEofB
= ++pParse
->nMem
;
2303 regOutA
= ++pParse
->nMem
;
2304 regOutB
= ++pParse
->nMem
;
2305 sqlite3SelectDestInit(&destA
, SRT_Coroutine
, regAddrA
);
2306 sqlite3SelectDestInit(&destB
, SRT_Coroutine
, regAddrB
);
2308 /* Jump past the various subroutines and coroutines to the main
2311 j1
= sqlite3VdbeAddOp0(v
, OP_Goto
);
2312 addrSelectA
= sqlite3VdbeCurrentAddr(v
);
2315 /* Generate a coroutine to evaluate the SELECT statement to the
2316 ** left of the compound operator - the "A" select.
2318 VdbeNoopComment((v
, "Begin coroutine for left SELECT"));
2319 pPrior
->iLimit
= regLimitA
;
2320 explainSetInteger(iSub1
, pParse
->iNextSelectId
);
2321 sqlite3Select(pParse
, pPrior
, &destA
);
2322 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, regEofA
);
2323 sqlite3VdbeAddOp1(v
, OP_Yield
, regAddrA
);
2324 VdbeNoopComment((v
, "End coroutine for left SELECT"));
2326 /* Generate a coroutine to evaluate the SELECT statement on
2327 ** the right - the "B" select
2329 addrSelectB
= sqlite3VdbeCurrentAddr(v
);
2330 VdbeNoopComment((v
, "Begin coroutine for right SELECT"));
2331 savedLimit
= p
->iLimit
;
2332 savedOffset
= p
->iOffset
;
2333 p
->iLimit
= regLimitB
;
2335 explainSetInteger(iSub2
, pParse
->iNextSelectId
);
2336 sqlite3Select(pParse
, p
, &destB
);
2337 p
->iLimit
= savedLimit
;
2338 p
->iOffset
= savedOffset
;
2339 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, regEofB
);
2340 sqlite3VdbeAddOp1(v
, OP_Yield
, regAddrB
);
2341 VdbeNoopComment((v
, "End coroutine for right SELECT"));
2343 /* Generate a subroutine that outputs the current row of the A
2344 ** select as the next output row of the compound select.
2346 VdbeNoopComment((v
, "Output routine for A"));
2347 addrOutA
= generateOutputSubroutine(pParse
,
2348 p
, &destA
, pDest
, regOutA
,
2349 regPrev
, pKeyDup
, P4_KEYINFO_HANDOFF
, labelEnd
);
2351 /* Generate a subroutine that outputs the current row of the B
2352 ** select as the next output row of the compound select.
2354 if( op
==TK_ALL
|| op
==TK_UNION
){
2355 VdbeNoopComment((v
, "Output routine for B"));
2356 addrOutB
= generateOutputSubroutine(pParse
,
2357 p
, &destB
, pDest
, regOutB
,
2358 regPrev
, pKeyDup
, P4_KEYINFO_STATIC
, labelEnd
);
2361 /* Generate a subroutine to run when the results from select A
2362 ** are exhausted and only data in select B remains.
2364 VdbeNoopComment((v
, "eof-A subroutine"));
2365 if( op
==TK_EXCEPT
|| op
==TK_INTERSECT
){
2366 addrEofA
= sqlite3VdbeAddOp2(v
, OP_Goto
, 0, labelEnd
);
2368 addrEofA
= sqlite3VdbeAddOp2(v
, OP_If
, regEofB
, labelEnd
);
2369 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutB
, addrOutB
);
2370 sqlite3VdbeAddOp1(v
, OP_Yield
, regAddrB
);
2371 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, addrEofA
);
2372 p
->nSelectRow
+= pPrior
->nSelectRow
;
2375 /* Generate a subroutine to run when the results from select B
2376 ** are exhausted and only data in select A remains.
2378 if( op
==TK_INTERSECT
){
2379 addrEofB
= addrEofA
;
2380 if( p
->nSelectRow
> pPrior
->nSelectRow
) p
->nSelectRow
= pPrior
->nSelectRow
;
2382 VdbeNoopComment((v
, "eof-B subroutine"));
2383 addrEofB
= sqlite3VdbeAddOp2(v
, OP_If
, regEofA
, labelEnd
);
2384 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutA
, addrOutA
);
2385 sqlite3VdbeAddOp1(v
, OP_Yield
, regAddrA
);
2386 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, addrEofB
);
2389 /* Generate code to handle the case of A<B
2391 VdbeNoopComment((v
, "A-lt-B subroutine"));
2392 addrAltB
= sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutA
, addrOutA
);
2393 sqlite3VdbeAddOp1(v
, OP_Yield
, regAddrA
);
2394 sqlite3VdbeAddOp2(v
, OP_If
, regEofA
, addrEofA
);
2395 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, labelCmpr
);
2397 /* Generate code to handle the case of A==B
2400 addrAeqB
= addrAltB
;
2401 }else if( op
==TK_INTERSECT
){
2402 addrAeqB
= addrAltB
;
2405 VdbeNoopComment((v
, "A-eq-B subroutine"));
2407 sqlite3VdbeAddOp1(v
, OP_Yield
, regAddrA
);
2408 sqlite3VdbeAddOp2(v
, OP_If
, regEofA
, addrEofA
);
2409 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, labelCmpr
);
2412 /* Generate code to handle the case of A>B
2414 VdbeNoopComment((v
, "A-gt-B subroutine"));
2415 addrAgtB
= sqlite3VdbeCurrentAddr(v
);
2416 if( op
==TK_ALL
|| op
==TK_UNION
){
2417 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutB
, addrOutB
);
2419 sqlite3VdbeAddOp1(v
, OP_Yield
, regAddrB
);
2420 sqlite3VdbeAddOp2(v
, OP_If
, regEofB
, addrEofB
);
2421 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, labelCmpr
);
2423 /* This code runs once to initialize everything.
2425 sqlite3VdbeJumpHere(v
, j1
);
2426 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regEofA
);
2427 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regEofB
);
2428 sqlite3VdbeAddOp2(v
, OP_Gosub
, regAddrA
, addrSelectA
);
2429 sqlite3VdbeAddOp2(v
, OP_Gosub
, regAddrB
, addrSelectB
);
2430 sqlite3VdbeAddOp2(v
, OP_If
, regEofA
, addrEofA
);
2431 sqlite3VdbeAddOp2(v
, OP_If
, regEofB
, addrEofB
);
2433 /* Implement the main merge loop
2435 sqlite3VdbeResolveLabel(v
, labelCmpr
);
2436 sqlite3VdbeAddOp4(v
, OP_Permutation
, 0, 0, 0, (char*)aPermute
, P4_INTARRAY
);
2437 sqlite3VdbeAddOp4(v
, OP_Compare
, destA
.iMem
, destB
.iMem
, nOrderBy
,
2438 (char*)pKeyMerge
, P4_KEYINFO_HANDOFF
);
2439 sqlite3VdbeAddOp3(v
, OP_Jump
, addrAltB
, addrAeqB
, addrAgtB
);
2441 /* Release temporary registers
2444 sqlite3ReleaseTempRange(pParse
, regPrev
, nOrderBy
+1);
2447 /* Jump to the this point in order to terminate the query.
2449 sqlite3VdbeResolveLabel(v
, labelEnd
);
2451 /* Set the number of output columns
2453 if( pDest
->eDest
==SRT_Output
){
2454 Select
*pFirst
= pPrior
;
2455 while( pFirst
->pPrior
) pFirst
= pFirst
->pPrior
;
2456 generateColumnNames(pParse
, 0, pFirst
->pEList
);
2459 /* Reassembly the compound query so that it will be freed correctly
2460 ** by the calling function */
2462 sqlite3SelectDelete(db
, p
->pPrior
);
2466 /*** TBD: Insert subroutine calls to close cursors on incomplete
2467 **** subqueries ****/
2468 explainComposite(pParse
, p
->op
, iSub1
, iSub2
, 0);
2473 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
2474 /* Forward Declarations */
2475 static void substExprList(sqlite3
*, ExprList
*, int, ExprList
*);
2476 static void substSelect(sqlite3
*, Select
*, int, ExprList
*);
2479 ** Scan through the expression pExpr. Replace every reference to
2480 ** a column in table number iTable with a copy of the iColumn-th
2481 ** entry in pEList. (But leave references to the ROWID column
2484 ** This routine is part of the flattening procedure. A subquery
2485 ** whose result set is defined by pEList appears as entry in the
2486 ** FROM clause of a SELECT such that the VDBE cursor assigned to that
2487 ** FORM clause entry is iTable. This routine make the necessary
2488 ** changes to pExpr so that it refers directly to the source table
2489 ** of the subquery rather the result set of the subquery.
2491 static Expr
*substExpr(
2492 sqlite3
*db
, /* Report malloc errors to this connection */
2493 Expr
*pExpr
, /* Expr in which substitution occurs */
2494 int iTable
, /* Table to be substituted */
2495 ExprList
*pEList
/* Substitute expressions */
2497 if( pExpr
==0 ) return 0;
2498 if( pExpr
->op
==TK_COLUMN
&& pExpr
->iTable
==iTable
){
2499 if( pExpr
->iColumn
<0 ){
2500 pExpr
->op
= TK_NULL
;
2503 assert( pEList
!=0 && pExpr
->iColumn
<pEList
->nExpr
);
2504 assert( pExpr
->pLeft
==0 && pExpr
->pRight
==0 );
2505 pNew
= sqlite3ExprDup(db
, pEList
->a
[pExpr
->iColumn
].pExpr
, 0);
2506 if( pNew
&& pExpr
->pColl
){
2507 pNew
->pColl
= pExpr
->pColl
;
2509 sqlite3ExprDelete(db
, pExpr
);
2513 pExpr
->pLeft
= substExpr(db
, pExpr
->pLeft
, iTable
, pEList
);
2514 pExpr
->pRight
= substExpr(db
, pExpr
->pRight
, iTable
, pEList
);
2515 if( ExprHasProperty(pExpr
, EP_xIsSelect
) ){
2516 substSelect(db
, pExpr
->x
.pSelect
, iTable
, pEList
);
2518 substExprList(db
, pExpr
->x
.pList
, iTable
, pEList
);
2523 static void substExprList(
2524 sqlite3
*db
, /* Report malloc errors here */
2525 ExprList
*pList
, /* List to scan and in which to make substitutes */
2526 int iTable
, /* Table to be substituted */
2527 ExprList
*pEList
/* Substitute values */
2530 if( pList
==0 ) return;
2531 for(i
=0; i
<pList
->nExpr
; i
++){
2532 pList
->a
[i
].pExpr
= substExpr(db
, pList
->a
[i
].pExpr
, iTable
, pEList
);
2535 static void substSelect(
2536 sqlite3
*db
, /* Report malloc errors here */
2537 Select
*p
, /* SELECT statement in which to make substitutions */
2538 int iTable
, /* Table to be replaced */
2539 ExprList
*pEList
/* Substitute values */
2542 struct SrcList_item
*pItem
;
2545 substExprList(db
, p
->pEList
, iTable
, pEList
);
2546 substExprList(db
, p
->pGroupBy
, iTable
, pEList
);
2547 substExprList(db
, p
->pOrderBy
, iTable
, pEList
);
2548 p
->pHaving
= substExpr(db
, p
->pHaving
, iTable
, pEList
);
2549 p
->pWhere
= substExpr(db
, p
->pWhere
, iTable
, pEList
);
2550 substSelect(db
, p
->pPrior
, iTable
, pEList
);
2552 assert( pSrc
); /* Even for (SELECT 1) we have: pSrc!=0 but pSrc->nSrc==0 */
2554 for(i
=pSrc
->nSrc
, pItem
=pSrc
->a
; i
>0; i
--, pItem
++){
2555 substSelect(db
, pItem
->pSelect
, iTable
, pEList
);
2559 #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
2561 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
2563 ** This routine attempts to flatten subqueries in order to speed
2564 ** execution. It returns 1 if it makes changes and 0 if no flattening
2567 ** To understand the concept of flattening, consider the following
2570 ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
2572 ** The default way of implementing this query is to execute the
2573 ** subquery first and store the results in a temporary table, then
2574 ** run the outer query on that temporary table. This requires two
2575 ** passes over the data. Furthermore, because the temporary table
2576 ** has no indices, the WHERE clause on the outer query cannot be
2579 ** This routine attempts to rewrite queries such as the above into
2580 ** a single flat select, like this:
2582 ** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
2584 ** The code generated for this simpification gives the same result
2585 ** but only has to scan the data once. And because indices might
2586 ** exist on the table t1, a complete scan of the data might be
2589 ** Flattening is only attempted if all of the following are true:
2591 ** (1) The subquery and the outer query do not both use aggregates.
2593 ** (2) The subquery is not an aggregate or the outer query is not a join.
2595 ** (3) The subquery is not the right operand of a left outer join
2596 ** (Originally ticket #306. Strengthened by ticket #3300)
2598 ** (4) The subquery is not DISTINCT.
2600 ** (**) At one point restrictions (4) and (5) defined a subset of DISTINCT
2601 ** sub-queries that were excluded from this optimization. Restriction
2602 ** (4) has since been expanded to exclude all DISTINCT subqueries.
2604 ** (6) The subquery does not use aggregates or the outer query is not
2607 ** (7) The subquery has a FROM clause.
2609 ** (8) The subquery does not use LIMIT or the outer query is not a join.
2611 ** (9) The subquery does not use LIMIT or the outer query does not use
2614 ** (10) The subquery does not use aggregates or the outer query does not
2617 ** (11) The subquery and the outer query do not both have ORDER BY clauses.
2619 ** (**) Not implemented. Subsumed into restriction (3). Was previously
2620 ** a separate restriction deriving from ticket #350.
2622 ** (13) The subquery and outer query do not both use LIMIT.
2624 ** (14) The subquery does not use OFFSET.
2626 ** (15) The outer query is not part of a compound select or the
2627 ** subquery does not have a LIMIT clause.
2628 ** (See ticket #2339 and ticket [02a8e81d44]).
2630 ** (16) The outer query is not an aggregate or the subquery does
2631 ** not contain ORDER BY. (Ticket #2942) This used to not matter
2632 ** until we introduced the group_concat() function.
2634 ** (17) The sub-query is not a compound select, or it is a UNION ALL
2635 ** compound clause made up entirely of non-aggregate queries, and
2636 ** the parent query:
2638 ** * is not itself part of a compound select,
2639 ** * is not an aggregate or DISTINCT query, and
2640 ** * has no other tables or sub-selects in the FROM clause.
2642 ** The parent and sub-query may contain WHERE clauses. Subject to
2643 ** rules (11), (13) and (14), they may also contain ORDER BY,
2644 ** LIMIT and OFFSET clauses.
2646 ** (18) If the sub-query is a compound select, then all terms of the
2647 ** ORDER by clause of the parent must be simple references to
2648 ** columns of the sub-query.
2650 ** (19) The subquery does not use LIMIT or the outer query does not
2651 ** have a WHERE clause.
2653 ** (20) If the sub-query is a compound select, then it must not use
2654 ** an ORDER BY clause. Ticket #3773. We could relax this constraint
2655 ** somewhat by saying that the terms of the ORDER BY clause must
2656 ** appear as unmodified result columns in the outer query. But
2657 ** have other optimizations in mind to deal with that case.
2659 ** (21) The subquery does not use LIMIT or the outer query is not
2660 ** DISTINCT. (See ticket [752e1646fc]).
2662 ** In this routine, the "p" parameter is a pointer to the outer query.
2663 ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
2664 ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
2666 ** If flattening is not attempted, this routine is a no-op and returns 0.
2667 ** If flattening is attempted this routine returns 1.
2669 ** All of the expression analysis must occur on both the outer query and
2670 ** the subquery before this routine runs.
2672 static int flattenSubquery(
2673 Parse
*pParse
, /* Parsing context */
2674 Select
*p
, /* The parent or outer SELECT statement */
2675 int iFrom
, /* Index in p->pSrc->a[] of the inner subquery */
2676 int isAgg
, /* True if outer SELECT uses aggregate functions */
2677 int subqueryIsAgg
/* True if the subquery uses aggregate functions */
2679 const char *zSavedAuthContext
= pParse
->zAuthContext
;
2681 Select
*pSub
; /* The inner query or "subquery" */
2682 Select
*pSub1
; /* Pointer to the rightmost select in sub-query */
2683 SrcList
*pSrc
; /* The FROM clause of the outer query */
2684 SrcList
*pSubSrc
; /* The FROM clause of the subquery */
2685 ExprList
*pList
; /* The result set of the outer query */
2686 int iParent
; /* VDBE cursor number of the pSub result set temp table */
2687 int i
; /* Loop counter */
2688 Expr
*pWhere
; /* The WHERE clause */
2689 struct SrcList_item
*pSubitem
; /* The subquery */
2690 sqlite3
*db
= pParse
->db
;
2692 /* Check to see if flattening is permitted. Return 0 if not.
2695 assert( p
->pPrior
==0 ); /* Unable to flatten compound queries */
2696 if( db
->flags
& SQLITE_QueryFlattener
) return 0;
2698 assert( pSrc
&& iFrom
>=0 && iFrom
<pSrc
->nSrc
);
2699 pSubitem
= &pSrc
->a
[iFrom
];
2700 iParent
= pSubitem
->iCursor
;
2701 pSub
= pSubitem
->pSelect
;
2703 if( isAgg
&& subqueryIsAgg
) return 0; /* Restriction (1) */
2704 if( subqueryIsAgg
&& pSrc
->nSrc
>1 ) return 0; /* Restriction (2) */
2705 pSubSrc
= pSub
->pSrc
;
2707 /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
2708 ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET
2709 ** because they could be computed at compile-time. But when LIMIT and OFFSET
2710 ** became arbitrary expressions, we were forced to add restrictions (13)
2712 if( pSub
->pLimit
&& p
->pLimit
) return 0; /* Restriction (13) */
2713 if( pSub
->pOffset
) return 0; /* Restriction (14) */
2714 if( p
->pRightmost
&& pSub
->pLimit
){
2715 return 0; /* Restriction (15) */
2717 if( pSubSrc
->nSrc
==0 ) return 0; /* Restriction (7) */
2718 if( pSub
->selFlags
& SF_Distinct
) return 0; /* Restriction (5) */
2719 if( pSub
->pLimit
&& (pSrc
->nSrc
>1 || isAgg
) ){
2720 return 0; /* Restrictions (8)(9) */
2722 if( (p
->selFlags
& SF_Distinct
)!=0 && subqueryIsAgg
){
2723 return 0; /* Restriction (6) */
2725 if( p
->pOrderBy
&& pSub
->pOrderBy
){
2726 return 0; /* Restriction (11) */
2728 if( isAgg
&& pSub
->pOrderBy
) return 0; /* Restriction (16) */
2729 if( pSub
->pLimit
&& p
->pWhere
) return 0; /* Restriction (19) */
2730 if( pSub
->pLimit
&& (p
->selFlags
& SF_Distinct
)!=0 ){
2731 return 0; /* Restriction (21) */
2734 /* OBSOLETE COMMENT 1:
2735 ** Restriction 3: If the subquery is a join, make sure the subquery is
2736 ** not used as the right operand of an outer join. Examples of why this
2739 ** t1 LEFT OUTER JOIN (t2 JOIN t3)
2741 ** If we flatten the above, we would get
2743 ** (t1 LEFT OUTER JOIN t2) JOIN t3
2745 ** which is not at all the same thing.
2747 ** OBSOLETE COMMENT 2:
2748 ** Restriction 12: If the subquery is the right operand of a left outer
2749 ** join, make sure the subquery has no WHERE clause.
2750 ** An examples of why this is not allowed:
2752 ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
2754 ** If we flatten the above, we would get
2756 ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
2758 ** But the t2.x>0 test will always fail on a NULL row of t2, which
2759 ** effectively converts the OUTER JOIN into an INNER JOIN.
2761 ** THIS OVERRIDES OBSOLETE COMMENTS 1 AND 2 ABOVE:
2762 ** Ticket #3300 shows that flattening the right term of a LEFT JOIN
2763 ** is fraught with danger. Best to avoid the whole thing. If the
2764 ** subquery is the right term of a LEFT JOIN, then do not flatten.
2766 if( (pSubitem
->jointype
& JT_OUTER
)!=0 ){
2770 /* Restriction 17: If the sub-query is a compound SELECT, then it must
2771 ** use only the UNION ALL operator. And none of the simple select queries
2772 ** that make up the compound SELECT are allowed to be aggregate or distinct
2776 if( pSub
->pOrderBy
){
2777 return 0; /* Restriction 20 */
2779 if( isAgg
|| (p
->selFlags
& SF_Distinct
)!=0 || pSrc
->nSrc
!=1 ){
2782 for(pSub1
=pSub
; pSub1
; pSub1
=pSub1
->pPrior
){
2783 testcase( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Distinct
);
2784 testcase( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Aggregate
);
2785 if( (pSub1
->selFlags
& (SF_Distinct
|SF_Aggregate
))!=0
2786 || (pSub1
->pPrior
&& pSub1
->op
!=TK_ALL
)
2787 || NEVER(pSub1
->pSrc
==0) || pSub1
->pSrc
->nSrc
!=1
2793 /* Restriction 18. */
2796 for(ii
=0; ii
<p
->pOrderBy
->nExpr
; ii
++){
2797 if( p
->pOrderBy
->a
[ii
].iCol
==0 ) return 0;
2802 /***** If we reach this point, flattening is permitted. *****/
2804 /* Authorize the subquery */
2805 pParse
->zAuthContext
= pSubitem
->zName
;
2806 sqlite3AuthCheck(pParse
, SQLITE_SELECT
, 0, 0, 0);
2807 pParse
->zAuthContext
= zSavedAuthContext
;
2809 /* If the sub-query is a compound SELECT statement, then (by restrictions
2810 ** 17 and 18 above) it must be a UNION ALL and the parent query must
2813 ** SELECT <expr-list> FROM (<sub-query>) <where-clause>
2815 ** followed by any ORDER BY, LIMIT and/or OFFSET clauses. This block
2816 ** creates N-1 copies of the parent query without any ORDER BY, LIMIT or
2817 ** OFFSET clauses and joins them to the left-hand-side of the original
2818 ** using UNION ALL operators. In this case N is the number of simple
2819 ** select statements in the compound sub-query.
2823 ** SELECT a+1 FROM (
2824 ** SELECT x FROM tab
2826 ** SELECT y FROM tab
2828 ** SELECT abs(z*2) FROM tab2
2829 ** ) WHERE a!=5 ORDER BY 1
2831 ** Transformed into:
2833 ** SELECT x+1 FROM tab WHERE x+1!=5
2835 ** SELECT y+1 FROM tab WHERE y+1!=5
2837 ** SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5
2840 ** We call this the "compound-subquery flattening".
2842 for(pSub
=pSub
->pPrior
; pSub
; pSub
=pSub
->pPrior
){
2844 ExprList
*pOrderBy
= p
->pOrderBy
;
2845 Expr
*pLimit
= p
->pLimit
;
2846 Select
*pPrior
= p
->pPrior
;
2851 pNew
= sqlite3SelectDup(db
, p
, 0);
2853 p
->pOrderBy
= pOrderBy
;
2860 pNew
->pPrior
= pPrior
;
2861 pNew
->pRightmost
= 0;
2864 if( db
->mallocFailed
) return 1;
2867 /* Begin flattening the iFrom-th entry of the FROM clause
2868 ** in the outer query.
2870 pSub
= pSub1
= pSubitem
->pSelect
;
2872 /* Delete the transient table structure associated with the
2875 sqlite3DbFree(db
, pSubitem
->zDatabase
);
2876 sqlite3DbFree(db
, pSubitem
->zName
);
2877 sqlite3DbFree(db
, pSubitem
->zAlias
);
2878 pSubitem
->zDatabase
= 0;
2879 pSubitem
->zName
= 0;
2880 pSubitem
->zAlias
= 0;
2881 pSubitem
->pSelect
= 0;
2883 /* Defer deleting the Table object associated with the
2884 ** subquery until code generation is
2885 ** complete, since there may still exist Expr.pTab entries that
2886 ** refer to the subquery even after flattening. Ticket #3346.
2888 ** pSubitem->pTab is always non-NULL by test restrictions and tests above.
2890 if( ALWAYS(pSubitem
->pTab
!=0) ){
2891 Table
*pTabToDel
= pSubitem
->pTab
;
2892 if( pTabToDel
->nRef
==1 ){
2893 Parse
*pToplevel
= sqlite3ParseToplevel(pParse
);
2894 pTabToDel
->pNextZombie
= pToplevel
->pZombieTab
;
2895 pToplevel
->pZombieTab
= pTabToDel
;
2902 /* The following loop runs once for each term in a compound-subquery
2903 ** flattening (as described above). If we are doing a different kind
2904 ** of flattening - a flattening other than a compound-subquery flattening -
2905 ** then this loop only runs once.
2907 ** This loop moves all of the FROM elements of the subquery into the
2908 ** the FROM clause of the outer query. Before doing this, remember
2909 ** the cursor number for the original outer query FROM element in
2910 ** iParent. The iParent cursor will never be used. Subsequent code
2911 ** will scan expressions looking for iParent references and replace
2912 ** those references with expressions that resolve to the subquery FROM
2913 ** elements we are now copying in.
2915 for(pParent
=p
; pParent
; pParent
=pParent
->pPrior
, pSub
=pSub
->pPrior
){
2918 pSubSrc
= pSub
->pSrc
; /* FROM clause of subquery */
2919 nSubSrc
= pSubSrc
->nSrc
; /* Number of terms in subquery FROM clause */
2920 pSrc
= pParent
->pSrc
; /* FROM clause of the outer query */
2923 assert( pParent
==p
); /* First time through the loop */
2924 jointype
= pSubitem
->jointype
;
2926 assert( pParent
!=p
); /* 2nd and subsequent times through the loop */
2927 pSrc
= pParent
->pSrc
= sqlite3SrcListAppend(db
, 0, 0, 0);
2929 assert( db
->mallocFailed
);
2934 /* The subquery uses a single slot of the FROM clause of the outer
2935 ** query. If the subquery has more than one element in its FROM clause,
2936 ** then expand the outer query to make space for it to hold all elements
2941 ** SELECT * FROM tabA, (SELECT * FROM sub1, sub2), tabB;
2943 ** The outer query has 3 slots in its FROM clause. One slot of the
2944 ** outer query (the middle slot) is used by the subquery. The next
2945 ** block of code will expand the out query to 4 slots. The middle
2946 ** slot is expanded to two slots in order to make space for the
2947 ** two elements in the FROM clause of the subquery.
2950 pParent
->pSrc
= pSrc
= sqlite3SrcListEnlarge(db
, pSrc
, nSubSrc
-1,iFrom
+1);
2951 if( db
->mallocFailed
){
2956 /* Transfer the FROM clause terms from the subquery into the
2959 for(i
=0; i
<nSubSrc
; i
++){
2960 sqlite3IdListDelete(db
, pSrc
->a
[i
+iFrom
].pUsing
);
2961 pSrc
->a
[i
+iFrom
] = pSubSrc
->a
[i
];
2962 memset(&pSubSrc
->a
[i
], 0, sizeof(pSubSrc
->a
[i
]));
2964 pSrc
->a
[iFrom
].jointype
= jointype
;
2966 /* Now begin substituting subquery result set expressions for
2967 ** references to the iParent in the outer query.
2971 ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
2972 ** \ \_____________ subquery __________/ /
2973 ** \_____________________ outer query ______________________________/
2975 ** We look at every expression in the outer query and every place we see
2976 ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
2978 pList
= pParent
->pEList
;
2979 for(i
=0; i
<pList
->nExpr
; i
++){
2980 if( pList
->a
[i
].zName
==0 ){
2981 const char *zSpan
= pList
->a
[i
].zSpan
;
2982 if( ALWAYS(zSpan
) ){
2983 pList
->a
[i
].zName
= sqlite3DbStrDup(db
, zSpan
);
2987 substExprList(db
, pParent
->pEList
, iParent
, pSub
->pEList
);
2989 substExprList(db
, pParent
->pGroupBy
, iParent
, pSub
->pEList
);
2990 pParent
->pHaving
= substExpr(db
, pParent
->pHaving
, iParent
, pSub
->pEList
);
2992 if( pSub
->pOrderBy
){
2993 assert( pParent
->pOrderBy
==0 );
2994 pParent
->pOrderBy
= pSub
->pOrderBy
;
2996 }else if( pParent
->pOrderBy
){
2997 substExprList(db
, pParent
->pOrderBy
, iParent
, pSub
->pEList
);
3000 pWhere
= sqlite3ExprDup(db
, pSub
->pWhere
, 0);
3004 if( subqueryIsAgg
){
3005 assert( pParent
->pHaving
==0 );
3006 pParent
->pHaving
= pParent
->pWhere
;
3007 pParent
->pWhere
= pWhere
;
3008 pParent
->pHaving
= substExpr(db
, pParent
->pHaving
, iParent
, pSub
->pEList
);
3009 pParent
->pHaving
= sqlite3ExprAnd(db
, pParent
->pHaving
,
3010 sqlite3ExprDup(db
, pSub
->pHaving
, 0));
3011 assert( pParent
->pGroupBy
==0 );
3012 pParent
->pGroupBy
= sqlite3ExprListDup(db
, pSub
->pGroupBy
, 0);
3014 pParent
->pWhere
= substExpr(db
, pParent
->pWhere
, iParent
, pSub
->pEList
);
3015 pParent
->pWhere
= sqlite3ExprAnd(db
, pParent
->pWhere
, pWhere
);
3018 /* The flattened query is distinct if either the inner or the
3019 ** outer query is distinct.
3021 pParent
->selFlags
|= pSub
->selFlags
& SF_Distinct
;
3024 ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y;
3026 ** One is tempted to try to add a and b to combine the limits. But this
3027 ** does not work if either limit is negative.
3030 pParent
->pLimit
= pSub
->pLimit
;
3035 /* Finially, delete what is left of the subquery and return
3038 sqlite3SelectDelete(db
, pSub1
);
3042 #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
3045 ** Analyze the SELECT statement passed as an argument to see if it
3046 ** is a min() or max() query. Return WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX if
3047 ** it is, or 0 otherwise. At present, a query is considered to be
3048 ** a min()/max() query if:
3050 ** 1. There is a single object in the FROM clause.
3052 ** 2. There is a single expression in the result set, and it is
3053 ** either min(x) or max(x), where x is a column reference.
3055 static u8
minMaxQuery(Select
*p
){
3057 ExprList
*pEList
= p
->pEList
;
3059 if( pEList
->nExpr
!=1 ) return WHERE_ORDERBY_NORMAL
;
3060 pExpr
= pEList
->a
[0].pExpr
;
3061 if( pExpr
->op
!=TK_AGG_FUNCTION
) return 0;
3062 if( NEVER(ExprHasProperty(pExpr
, EP_xIsSelect
)) ) return 0;
3063 pEList
= pExpr
->x
.pList
;
3064 if( pEList
==0 || pEList
->nExpr
!=1 ) return 0;
3065 if( pEList
->a
[0].pExpr
->op
!=TK_AGG_COLUMN
) return WHERE_ORDERBY_NORMAL
;
3066 assert( !ExprHasProperty(pExpr
, EP_IntValue
) );
3067 if( sqlite3StrICmp(pExpr
->u
.zToken
,"min")==0 ){
3068 return WHERE_ORDERBY_MIN
;
3069 }else if( sqlite3StrICmp(pExpr
->u
.zToken
,"max")==0 ){
3070 return WHERE_ORDERBY_MAX
;
3072 return WHERE_ORDERBY_NORMAL
;
3076 ** The select statement passed as the first argument is an aggregate query.
3077 ** The second argment is the associated aggregate-info object. This
3078 ** function tests if the SELECT is of the form:
3080 ** SELECT count(*) FROM <tbl>
3082 ** where table is a database table, not a sub-select or view. If the query
3083 ** does match this pattern, then a pointer to the Table object representing
3084 ** <tbl> is returned. Otherwise, 0 is returned.
3086 static Table
*isSimpleCount(Select
*p
, AggInfo
*pAggInfo
){
3090 assert( !p
->pGroupBy
);
3092 if( p
->pWhere
|| p
->pEList
->nExpr
!=1
3093 || p
->pSrc
->nSrc
!=1 || p
->pSrc
->a
[0].pSelect
3097 pTab
= p
->pSrc
->a
[0].pTab
;
3098 pExpr
= p
->pEList
->a
[0].pExpr
;
3099 assert( pTab
&& !pTab
->pSelect
&& pExpr
);
3101 if( IsVirtual(pTab
) ) return 0;
3102 if( pExpr
->op
!=TK_AGG_FUNCTION
) return 0;
3103 if( (pAggInfo
->aFunc
[0].pFunc
->flags
&SQLITE_FUNC_COUNT
)==0 ) return 0;
3104 if( pExpr
->flags
&EP_Distinct
) return 0;
3110 ** If the source-list item passed as an argument was augmented with an
3111 ** INDEXED BY clause, then try to locate the specified index. If there
3112 ** was such a clause and the named index cannot be found, return
3113 ** SQLITE_ERROR and leave an error in pParse. Otherwise, populate
3114 ** pFrom->pIndex and return SQLITE_OK.
3116 int sqlite3IndexedByLookup(Parse
*pParse
, struct SrcList_item
*pFrom
){
3117 if( pFrom
->pTab
&& pFrom
->zIndex
){
3118 Table
*pTab
= pFrom
->pTab
;
3119 char *zIndex
= pFrom
->zIndex
;
3121 for(pIdx
=pTab
->pIndex
;
3122 pIdx
&& sqlite3StrICmp(pIdx
->zName
, zIndex
);
3126 sqlite3ErrorMsg(pParse
, "no such index: %s", zIndex
, 0);
3127 pParse
->checkSchema
= 1;
3128 return SQLITE_ERROR
;
3130 pFrom
->pIndex
= pIdx
;
3136 ** This routine is a Walker callback for "expanding" a SELECT statement.
3137 ** "Expanding" means to do the following:
3139 ** (1) Make sure VDBE cursor numbers have been assigned to every
3140 ** element of the FROM clause.
3142 ** (2) Fill in the pTabList->a[].pTab fields in the SrcList that
3143 ** defines FROM clause. When views appear in the FROM clause,
3144 ** fill pTabList->a[].pSelect with a copy of the SELECT statement
3145 ** that implements the view. A copy is made of the view's SELECT
3146 ** statement so that we can freely modify or delete that statement
3147 ** without worrying about messing up the presistent representation
3150 ** (3) Add terms to the WHERE clause to accomodate the NATURAL keyword
3151 ** on joins and the ON and USING clause of joins.
3153 ** (4) Scan the list of columns in the result set (pEList) looking
3154 ** for instances of the "*" operator or the TABLE.* operator.
3155 ** If found, expand each "*" to be every column in every table
3156 ** and TABLE.* to be every column in TABLE.
3159 static int selectExpander(Walker
*pWalker
, Select
*p
){
3160 Parse
*pParse
= pWalker
->pParse
;
3164 struct SrcList_item
*pFrom
;
3165 sqlite3
*db
= pParse
->db
;
3167 if( db
->mallocFailed
){
3170 if( NEVER(p
->pSrc
==0) || (p
->selFlags
& SF_Expanded
)!=0 ){
3173 p
->selFlags
|= SF_Expanded
;
3177 /* Make sure cursor numbers have been assigned to all entries in
3178 ** the FROM clause of the SELECT statement.
3180 sqlite3SrcListAssignCursors(pParse
, pTabList
);
3182 /* Look up every table named in the FROM clause of the select. If
3183 ** an entry of the FROM clause is a subquery instead of a table or view,
3184 ** then create a transient table structure to describe the subquery.
3186 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
3188 if( pFrom
->pTab
!=0 ){
3189 /* This statement has already been prepared. There is no need
3190 ** to go further. */
3194 if( pFrom
->zName
==0 ){
3195 #ifndef SQLITE_OMIT_SUBQUERY
3196 Select
*pSel
= pFrom
->pSelect
;
3197 /* A sub-query in the FROM clause of a SELECT */
3199 assert( pFrom
->pTab
==0 );
3200 sqlite3WalkSelect(pWalker
, pSel
);
3201 pFrom
->pTab
= pTab
= sqlite3DbMallocZero(db
, sizeof(Table
));
3202 if( pTab
==0 ) return WRC_Abort
;
3204 pTab
->zName
= sqlite3MPrintf(db
, "sqlite_subquery_%p_", (void*)pTab
);
3205 while( pSel
->pPrior
){ pSel
= pSel
->pPrior
; }
3206 selectColumnsFromExprList(pParse
, pSel
->pEList
, &pTab
->nCol
, &pTab
->aCol
);
3208 pTab
->nRowEst
= 1000000;
3209 pTab
->tabFlags
|= TF_Ephemeral
;
3212 /* An ordinary table or view name in the FROM clause */
3213 assert( pFrom
->pTab
==0 );
3214 pFrom
->pTab
= pTab
=
3215 sqlite3LocateTable(pParse
,0,pFrom
->zName
,pFrom
->zDatabase
);
3216 if( pTab
==0 ) return WRC_Abort
;
3218 #if !defined(SQLITE_OMIT_VIEW) || !defined (SQLITE_OMIT_VIRTUALTABLE)
3219 if( pTab
->pSelect
|| IsVirtual(pTab
) ){
3220 /* We reach here if the named table is a really a view */
3221 if( sqlite3ViewGetColumnNames(pParse
, pTab
) ) return WRC_Abort
;
3222 assert( pFrom
->pSelect
==0 );
3223 pFrom
->pSelect
= sqlite3SelectDup(db
, pTab
->pSelect
, 0);
3224 sqlite3WalkSelect(pWalker
, pFrom
->pSelect
);
3229 /* Locate the index named by the INDEXED BY clause, if any. */
3230 if( sqlite3IndexedByLookup(pParse
, pFrom
) ){
3235 /* Process NATURAL keywords, and ON and USING clauses of joins.
3237 if( db
->mallocFailed
|| sqliteProcessJoin(pParse
, p
) ){
3241 /* For every "*" that occurs in the column list, insert the names of
3242 ** all columns in all tables. And for every TABLE.* insert the names
3243 ** of all columns in TABLE. The parser inserted a special expression
3244 ** with the TK_ALL operator for each "*" that it found in the column list.
3245 ** The following code just has to locate the TK_ALL expressions and expand
3246 ** each one to the list of all columns in all tables.
3248 ** The first loop just checks to see if there are any "*" operators
3249 ** that need expanding.
3251 for(k
=0; k
<pEList
->nExpr
; k
++){
3252 Expr
*pE
= pEList
->a
[k
].pExpr
;
3253 if( pE
->op
==TK_ALL
) break;
3254 assert( pE
->op
!=TK_DOT
|| pE
->pRight
!=0 );
3255 assert( pE
->op
!=TK_DOT
|| (pE
->pLeft
!=0 && pE
->pLeft
->op
==TK_ID
) );
3256 if( pE
->op
==TK_DOT
&& pE
->pRight
->op
==TK_ALL
) break;
3258 if( k
<pEList
->nExpr
){
3260 ** If we get here it means the result set contains one or more "*"
3261 ** operators that need to be expanded. Loop through each expression
3262 ** in the result set and expand them one by one.
3264 struct ExprList_item
*a
= pEList
->a
;
3266 int flags
= pParse
->db
->flags
;
3267 int longNames
= (flags
& SQLITE_FullColNames
)!=0
3268 && (flags
& SQLITE_ShortColNames
)==0;
3270 for(k
=0; k
<pEList
->nExpr
; k
++){
3271 Expr
*pE
= a
[k
].pExpr
;
3272 assert( pE
->op
!=TK_DOT
|| pE
->pRight
!=0 );
3273 if( pE
->op
!=TK_ALL
&& (pE
->op
!=TK_DOT
|| pE
->pRight
->op
!=TK_ALL
) ){
3274 /* This particular expression does not need to be expanded.
3276 pNew
= sqlite3ExprListAppend(pParse
, pNew
, a
[k
].pExpr
);
3278 pNew
->a
[pNew
->nExpr
-1].zName
= a
[k
].zName
;
3279 pNew
->a
[pNew
->nExpr
-1].zSpan
= a
[k
].zSpan
;
3285 /* This expression is a "*" or a "TABLE.*" and needs to be
3287 int tableSeen
= 0; /* Set to 1 when TABLE matches */
3288 char *zTName
; /* text of name of TABLE */
3289 if( pE
->op
==TK_DOT
){
3290 assert( pE
->pLeft
!=0 );
3291 assert( !ExprHasProperty(pE
->pLeft
, EP_IntValue
) );
3292 zTName
= pE
->pLeft
->u
.zToken
;
3296 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
3297 Table
*pTab
= pFrom
->pTab
;
3298 char *zTabName
= pFrom
->zAlias
;
3300 zTabName
= pTab
->zName
;
3302 if( db
->mallocFailed
) break;
3303 if( zTName
&& sqlite3StrICmp(zTName
, zTabName
)!=0 ){
3307 for(j
=0; j
<pTab
->nCol
; j
++){
3308 Expr
*pExpr
, *pRight
;
3309 char *zName
= pTab
->aCol
[j
].zName
;
3310 char *zColname
; /* The computed column name */
3311 char *zToFree
; /* Malloced string that needs to be freed */
3312 Token sColname
; /* Computed column name as a token */
3314 /* If a column is marked as 'hidden' (currently only possible
3315 ** for virtual tables), do not include it in the expanded
3318 if( IsHiddenColumn(&pTab
->aCol
[j
]) ){
3319 assert(IsVirtual(pTab
));
3323 if( i
>0 && zTName
==0 ){
3324 if( (pFrom
->jointype
& JT_NATURAL
)!=0
3325 && tableAndColumnIndex(pTabList
, i
, zName
, 0, 0)
3327 /* In a NATURAL join, omit the join columns from the
3328 ** table to the right of the join */
3331 if( sqlite3IdListIndex(pFrom
->pUsing
, zName
)>=0 ){
3332 /* In a join with a USING clause, omit columns in the
3333 ** using clause from the table on the right. */
3337 pRight
= sqlite3Expr(db
, TK_ID
, zName
);
3340 if( longNames
|| pTabList
->nSrc
>1 ){
3342 pLeft
= sqlite3Expr(db
, TK_ID
, zTabName
);
3343 pExpr
= sqlite3PExpr(pParse
, TK_DOT
, pLeft
, pRight
, 0);
3345 zColname
= sqlite3MPrintf(db
, "%s.%s", zTabName
, zName
);
3351 pNew
= sqlite3ExprListAppend(pParse
, pNew
, pExpr
);
3352 sColname
.z
= zColname
;
3353 sColname
.n
= sqlite3Strlen30(zColname
);
3354 sqlite3ExprListSetName(pParse
, pNew
, &sColname
, 0);
3355 sqlite3DbFree(db
, zToFree
);
3360 sqlite3ErrorMsg(pParse
, "no such table: %s", zTName
);
3362 sqlite3ErrorMsg(pParse
, "no tables specified");
3367 sqlite3ExprListDelete(db
, pEList
);
3370 #if SQLITE_MAX_COLUMN
3371 if( p
->pEList
&& p
->pEList
->nExpr
>db
->aLimit
[SQLITE_LIMIT_COLUMN
] ){
3372 sqlite3ErrorMsg(pParse
, "too many columns in result set");
3375 return WRC_Continue
;
3379 ** No-op routine for the parse-tree walker.
3381 ** When this routine is the Walker.xExprCallback then expression trees
3382 ** are walked without any actions being taken at each node. Presumably,
3383 ** when this routine is used for Walker.xExprCallback then
3384 ** Walker.xSelectCallback is set to do something useful for every
3385 ** subquery in the parser tree.
3387 static int exprWalkNoop(Walker
*NotUsed
, Expr
*NotUsed2
){
3388 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
3389 return WRC_Continue
;
3393 ** This routine "expands" a SELECT statement and all of its subqueries.
3394 ** For additional information on what it means to "expand" a SELECT
3395 ** statement, see the comment on the selectExpand worker callback above.
3397 ** Expanding a SELECT statement is the first step in processing a
3398 ** SELECT statement. The SELECT statement must be expanded before
3399 ** name resolution is performed.
3401 ** If anything goes wrong, an error message is written into pParse.
3402 ** The calling function can detect the problem by looking at pParse->nErr
3403 ** and/or pParse->db->mallocFailed.
3405 static void sqlite3SelectExpand(Parse
*pParse
, Select
*pSelect
){
3407 w
.xSelectCallback
= selectExpander
;
3408 w
.xExprCallback
= exprWalkNoop
;
3410 sqlite3WalkSelect(&w
, pSelect
);
3414 #ifndef SQLITE_OMIT_SUBQUERY
3416 ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()
3419 ** For each FROM-clause subquery, add Column.zType and Column.zColl
3420 ** information to the Table structure that represents the result set
3421 ** of that subquery.
3423 ** The Table structure that represents the result set was constructed
3424 ** by selectExpander() but the type and collation information was omitted
3425 ** at that point because identifiers had not yet been resolved. This
3426 ** routine is called after identifier resolution.
3428 static int selectAddSubqueryTypeInfo(Walker
*pWalker
, Select
*p
){
3432 struct SrcList_item
*pFrom
;
3434 assert( p
->selFlags
& SF_Resolved
);
3435 if( (p
->selFlags
& SF_HasTypeInfo
)==0 ){
3436 p
->selFlags
|= SF_HasTypeInfo
;
3437 pParse
= pWalker
->pParse
;
3439 for(i
=0, pFrom
=pTabList
->a
; i
<pTabList
->nSrc
; i
++, pFrom
++){
3440 Table
*pTab
= pFrom
->pTab
;
3441 if( ALWAYS(pTab
!=0) && (pTab
->tabFlags
& TF_Ephemeral
)!=0 ){
3442 /* A sub-query in the FROM clause of a SELECT */
3443 Select
*pSel
= pFrom
->pSelect
;
3445 while( pSel
->pPrior
) pSel
= pSel
->pPrior
;
3446 selectAddColumnTypeAndCollation(pParse
, pTab
->nCol
, pTab
->aCol
, pSel
);
3450 return WRC_Continue
;
3456 ** This routine adds datatype and collating sequence information to
3457 ** the Table structures of all FROM-clause subqueries in a
3458 ** SELECT statement.
3460 ** Use this routine after name resolution.
3462 static void sqlite3SelectAddTypeInfo(Parse
*pParse
, Select
*pSelect
){
3463 #ifndef SQLITE_OMIT_SUBQUERY
3465 w
.xSelectCallback
= selectAddSubqueryTypeInfo
;
3466 w
.xExprCallback
= exprWalkNoop
;
3468 sqlite3WalkSelect(&w
, pSelect
);
3474 ** This routine sets of a SELECT statement for processing. The
3475 ** following is accomplished:
3477 ** * VDBE Cursor numbers are assigned to all FROM-clause terms.
3478 ** * Ephemeral Table objects are created for all FROM-clause subqueries.
3479 ** * ON and USING clauses are shifted into WHERE statements
3480 ** * Wildcards "*" and "TABLE.*" in result sets are expanded.
3481 ** * Identifiers in expression are matched to tables.
3483 ** This routine acts recursively on all subqueries within the SELECT.
3485 void sqlite3SelectPrep(
3486 Parse
*pParse
, /* The parser context */
3487 Select
*p
, /* The SELECT statement being coded. */
3488 NameContext
*pOuterNC
/* Name context for container */
3491 if( NEVER(p
==0) ) return;
3493 if( p
->selFlags
& SF_HasTypeInfo
) return;
3494 sqlite3SelectExpand(pParse
, p
);
3495 if( pParse
->nErr
|| db
->mallocFailed
) return;
3496 sqlite3ResolveSelectNames(pParse
, p
, pOuterNC
);
3497 if( pParse
->nErr
|| db
->mallocFailed
) return;
3498 sqlite3SelectAddTypeInfo(pParse
, p
);
3502 ** Reset the aggregate accumulator.
3504 ** The aggregate accumulator is a set of memory cells that hold
3505 ** intermediate results while calculating an aggregate. This
3506 ** routine simply stores NULLs in all of those memory cells.
3508 static void resetAccumulator(Parse
*pParse
, AggInfo
*pAggInfo
){
3509 Vdbe
*v
= pParse
->pVdbe
;
3511 struct AggInfo_func
*pFunc
;
3512 if( pAggInfo
->nFunc
+pAggInfo
->nColumn
==0 ){
3515 for(i
=0; i
<pAggInfo
->nColumn
; i
++){
3516 sqlite3VdbeAddOp2(v
, OP_Null
, 0, pAggInfo
->aCol
[i
].iMem
);
3518 for(pFunc
=pAggInfo
->aFunc
, i
=0; i
<pAggInfo
->nFunc
; i
++, pFunc
++){
3519 sqlite3VdbeAddOp2(v
, OP_Null
, 0, pFunc
->iMem
);
3520 if( pFunc
->iDistinct
>=0 ){
3521 Expr
*pE
= pFunc
->pExpr
;
3522 assert( !ExprHasProperty(pE
, EP_xIsSelect
) );
3523 if( pE
->x
.pList
==0 || pE
->x
.pList
->nExpr
!=1 ){
3524 sqlite3ErrorMsg(pParse
, "DISTINCT aggregates must have exactly one "
3526 pFunc
->iDistinct
= -1;
3528 KeyInfo
*pKeyInfo
= keyInfoFromExprList(pParse
, pE
->x
.pList
);
3529 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
, pFunc
->iDistinct
, 0, 0,
3530 (char*)pKeyInfo
, P4_KEYINFO_HANDOFF
);
3537 ** Invoke the OP_AggFinalize opcode for every aggregate function
3538 ** in the AggInfo structure.
3540 static void finalizeAggFunctions(Parse
*pParse
, AggInfo
*pAggInfo
){
3541 Vdbe
*v
= pParse
->pVdbe
;
3543 struct AggInfo_func
*pF
;
3544 for(i
=0, pF
=pAggInfo
->aFunc
; i
<pAggInfo
->nFunc
; i
++, pF
++){
3545 ExprList
*pList
= pF
->pExpr
->x
.pList
;
3546 assert( !ExprHasProperty(pF
->pExpr
, EP_xIsSelect
) );
3547 sqlite3VdbeAddOp4(v
, OP_AggFinal
, pF
->iMem
, pList
? pList
->nExpr
: 0, 0,
3548 (void*)pF
->pFunc
, P4_FUNCDEF
);
3553 ** Update the accumulator memory cells for an aggregate based on
3554 ** the current cursor position.
3556 static void updateAccumulator(Parse
*pParse
, AggInfo
*pAggInfo
){
3557 Vdbe
*v
= pParse
->pVdbe
;
3559 struct AggInfo_func
*pF
;
3560 struct AggInfo_col
*pC
;
3562 pAggInfo
->directMode
= 1;
3563 sqlite3ExprCacheClear(pParse
);
3564 for(i
=0, pF
=pAggInfo
->aFunc
; i
<pAggInfo
->nFunc
; i
++, pF
++){
3568 ExprList
*pList
= pF
->pExpr
->x
.pList
;
3569 assert( !ExprHasProperty(pF
->pExpr
, EP_xIsSelect
) );
3571 nArg
= pList
->nExpr
;
3572 regAgg
= sqlite3GetTempRange(pParse
, nArg
);
3573 sqlite3ExprCodeExprList(pParse
, pList
, regAgg
, 1);
3578 if( pF
->iDistinct
>=0 ){
3579 addrNext
= sqlite3VdbeMakeLabel(v
);
3581 codeDistinct(pParse
, pF
->iDistinct
, addrNext
, 1, regAgg
);
3583 if( pF
->pFunc
->flags
& SQLITE_FUNC_NEEDCOLL
){
3585 struct ExprList_item
*pItem
;
3587 assert( pList
!=0 ); /* pList!=0 if pF->pFunc has NEEDCOLL */
3588 for(j
=0, pItem
=pList
->a
; !pColl
&& j
<nArg
; j
++, pItem
++){
3589 pColl
= sqlite3ExprCollSeq(pParse
, pItem
->pExpr
);
3592 pColl
= pParse
->db
->pDfltColl
;
3594 sqlite3VdbeAddOp4(v
, OP_CollSeq
, 0, 0, 0, (char *)pColl
, P4_COLLSEQ
);
3596 sqlite3VdbeAddOp4(v
, OP_AggStep
, 0, regAgg
, pF
->iMem
,
3597 (void*)pF
->pFunc
, P4_FUNCDEF
);
3598 sqlite3VdbeChangeP5(v
, (u8
)nArg
);
3599 sqlite3ExprCacheAffinityChange(pParse
, regAgg
, nArg
);
3600 sqlite3ReleaseTempRange(pParse
, regAgg
, nArg
);
3602 sqlite3VdbeResolveLabel(v
, addrNext
);
3603 sqlite3ExprCacheClear(pParse
);
3607 /* Before populating the accumulator registers, clear the column cache.
3608 ** Otherwise, if any of the required column values are already present
3609 ** in registers, sqlite3ExprCode() may use OP_SCopy to copy the value
3610 ** to pC->iMem. But by the time the value is used, the original register
3611 ** may have been used, invalidating the underlying buffer holding the
3612 ** text or blob value. See ticket [883034dcb5].
3614 ** Another solution would be to change the OP_SCopy used to copy cached
3615 ** values to an OP_Copy.
3617 sqlite3ExprCacheClear(pParse
);
3618 for(i
=0, pC
=pAggInfo
->aCol
; i
<pAggInfo
->nAccumulator
; i
++, pC
++){
3619 sqlite3ExprCode(pParse
, pC
->pExpr
, pC
->iMem
);
3621 pAggInfo
->directMode
= 0;
3622 sqlite3ExprCacheClear(pParse
);
3626 ** Add a single OP_Explain instruction to the VDBE to explain a simple
3627 ** count(*) query ("SELECT count(*) FROM pTab").
3629 #ifndef SQLITE_OMIT_EXPLAIN
3630 static void explainSimpleCount(
3631 Parse
*pParse
, /* Parse context */
3632 Table
*pTab
, /* Table being queried */
3633 Index
*pIdx
/* Index used to optimize scan, or NULL */
3635 if( pParse
->explain
==2 ){
3636 char *zEqp
= sqlite3MPrintf(pParse
->db
, "SCAN TABLE %s %s%s(~%d rows)",
3638 pIdx
? "USING COVERING INDEX " : "",
3639 pIdx
? pIdx
->zName
: "",
3643 pParse
->pVdbe
, OP_Explain
, pParse
->iSelectId
, 0, 0, zEqp
, P4_DYNAMIC
3648 # define explainSimpleCount(a,b,c)
3652 ** Generate code for the SELECT statement given in the p argument.
3654 ** The results are distributed in various ways depending on the
3655 ** contents of the SelectDest structure pointed to by argument pDest
3658 ** pDest->eDest Result
3659 ** ------------ -------------------------------------------
3660 ** SRT_Output Generate a row of output (using the OP_ResultRow
3661 ** opcode) for each row in the result set.
3663 ** SRT_Mem Only valid if the result is a single column.
3664 ** Store the first column of the first result row
3665 ** in register pDest->iParm then abandon the rest
3666 ** of the query. This destination implies "LIMIT 1".
3668 ** SRT_Set The result must be a single column. Store each
3669 ** row of result as the key in table pDest->iParm.
3670 ** Apply the affinity pDest->affinity before storing
3671 ** results. Used to implement "IN (SELECT ...)".
3673 ** SRT_Union Store results as a key in a temporary table pDest->iParm.
3675 ** SRT_Except Remove results from the temporary table pDest->iParm.
3677 ** SRT_Table Store results in temporary table pDest->iParm.
3678 ** This is like SRT_EphemTab except that the table
3679 ** is assumed to already be open.
3681 ** SRT_EphemTab Create an temporary table pDest->iParm and store
3682 ** the result there. The cursor is left open after
3683 ** returning. This is like SRT_Table except that
3684 ** this destination uses OP_OpenEphemeral to create
3687 ** SRT_Coroutine Generate a co-routine that returns a new row of
3688 ** results each time it is invoked. The entry point
3689 ** of the co-routine is stored in register pDest->iParm.
3691 ** SRT_Exists Store a 1 in memory cell pDest->iParm if the result
3692 ** set is not empty.
3694 ** SRT_Discard Throw the results away. This is used by SELECT
3695 ** statements within triggers whose only purpose is
3696 ** the side-effects of functions.
3698 ** This routine returns the number of errors. If any errors are
3699 ** encountered, then an appropriate error message is left in
3702 ** This routine does NOT free the Select structure passed in. The
3703 ** calling function needs to do that.
3706 Parse
*pParse
, /* The parser context */
3707 Select
*p
, /* The SELECT statement being coded. */
3708 SelectDest
*pDest
/* What to do with the query results */
3710 int i
, j
; /* Loop counters */
3711 WhereInfo
*pWInfo
; /* Return from sqlite3WhereBegin() */
3712 Vdbe
*v
; /* The virtual machine under construction */
3713 int isAgg
; /* True for select lists like "count(*)" */
3714 ExprList
*pEList
; /* List of columns to extract. */
3715 SrcList
*pTabList
; /* List of tables to select from */
3716 Expr
*pWhere
; /* The WHERE clause. May be NULL */
3717 ExprList
*pOrderBy
; /* The ORDER BY clause. May be NULL */
3718 ExprList
*pGroupBy
; /* The GROUP BY clause. May be NULL */
3719 Expr
*pHaving
; /* The HAVING clause. May be NULL */
3720 int isDistinct
; /* True if the DISTINCT keyword is present */
3721 int distinct
; /* Table to use for the distinct set */
3722 int rc
= 1; /* Value to return from this function */
3723 int addrSortIndex
; /* Address of an OP_OpenEphemeral instruction */
3724 AggInfo sAggInfo
; /* Information used by aggregate queries */
3725 int iEnd
; /* Address of the end of the query */
3726 sqlite3
*db
; /* The database connection */
3728 #ifndef SQLITE_OMIT_EXPLAIN
3729 int iRestoreSelectId
= pParse
->iSelectId
;
3730 pParse
->iSelectId
= pParse
->iNextSelectId
++;
3734 if( p
==0 || db
->mallocFailed
|| pParse
->nErr
){
3737 if( sqlite3AuthCheck(pParse
, SQLITE_SELECT
, 0, 0, 0) ) return 1;
3738 memset(&sAggInfo
, 0, sizeof(sAggInfo
));
3740 if( IgnorableOrderby(pDest
) ){
3741 assert(pDest
->eDest
==SRT_Exists
|| pDest
->eDest
==SRT_Union
||
3742 pDest
->eDest
==SRT_Except
|| pDest
->eDest
==SRT_Discard
);
3743 /* If ORDER BY makes no difference in the output then neither does
3744 ** DISTINCT so it can be removed too. */
3745 sqlite3ExprListDelete(db
, p
->pOrderBy
);
3747 p
->selFlags
&= ~SF_Distinct
;
3749 sqlite3SelectPrep(pParse
, p
, 0);
3750 pOrderBy
= p
->pOrderBy
;
3753 if( pParse
->nErr
|| db
->mallocFailed
){
3756 isAgg
= (p
->selFlags
& SF_Aggregate
)!=0;
3757 assert( pEList
!=0 );
3759 /* Begin generating code.
3761 v
= sqlite3GetVdbe(pParse
);
3762 if( v
==0 ) goto select_end
;
3764 /* If writing to memory or generating a set
3765 ** only a single column may be output.
3767 #ifndef SQLITE_OMIT_SUBQUERY
3768 if( checkForMultiColumnSelectError(pParse
, pDest
, pEList
->nExpr
) ){
3773 /* Generate code for all sub-queries in the FROM clause
3775 #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
3776 for(i
=0; !p
->pPrior
&& i
<pTabList
->nSrc
; i
++){
3777 struct SrcList_item
*pItem
= &pTabList
->a
[i
];
3779 Select
*pSub
= pItem
->pSelect
;
3782 if( pSub
==0 || pItem
->isPopulated
) continue;
3784 /* Increment Parse.nHeight by the height of the largest expression
3785 ** tree refered to by this, the parent select. The child select
3786 ** may contain expression trees of at most
3787 ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
3788 ** more conservative than necessary, but much easier than enforcing
3791 pParse
->nHeight
+= sqlite3SelectExprHeight(p
);
3793 /* Check to see if the subquery can be absorbed into the parent. */
3794 isAggSub
= (pSub
->selFlags
& SF_Aggregate
)!=0;
3795 if( flattenSubquery(pParse
, p
, i
, isAgg
, isAggSub
) ){
3798 p
->selFlags
|= SF_Aggregate
;
3802 sqlite3SelectDestInit(&dest
, SRT_EphemTab
, pItem
->iCursor
);
3803 assert( pItem
->isPopulated
==0 );
3804 explainSetInteger(pItem
->iSelectId
, (u8
)pParse
->iNextSelectId
);
3805 sqlite3Select(pParse
, pSub
, &dest
);
3806 pItem
->isPopulated
= 1;
3807 pItem
->pTab
->nRowEst
= (unsigned)pSub
->nSelectRow
;
3809 if( /*pParse->nErr ||*/ db
->mallocFailed
){
3812 pParse
->nHeight
-= sqlite3SelectExprHeight(p
);
3814 if( !IgnorableOrderby(pDest
) ){
3815 pOrderBy
= p
->pOrderBy
;
3821 pGroupBy
= p
->pGroupBy
;
3822 pHaving
= p
->pHaving
;
3823 isDistinct
= (p
->selFlags
& SF_Distinct
)!=0;
3825 #ifndef SQLITE_OMIT_COMPOUND_SELECT
3826 /* If there is are a sequence of queries, do the earlier ones first.
3829 if( p
->pRightmost
==0 ){
3830 Select
*pLoop
, *pRight
= 0;
3833 for(pLoop
=p
; pLoop
; pLoop
=pLoop
->pPrior
, cnt
++){
3834 pLoop
->pRightmost
= p
;
3835 pLoop
->pNext
= pRight
;
3838 mxSelect
= db
->aLimit
[SQLITE_LIMIT_COMPOUND_SELECT
];
3839 if( mxSelect
&& cnt
>mxSelect
){
3840 sqlite3ErrorMsg(pParse
, "too many terms in compound SELECT");
3844 rc
= multiSelect(pParse
, p
, pDest
);
3845 explainSetInteger(pParse
->iSelectId
, iRestoreSelectId
);
3850 /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
3851 ** GROUP BY might use an index, DISTINCT never does.
3853 assert( p
->pGroupBy
==0 || (p
->selFlags
& SF_Aggregate
)!=0 );
3854 if( (p
->selFlags
& (SF_Distinct
|SF_Aggregate
))==SF_Distinct
){
3855 p
->pGroupBy
= sqlite3ExprListDup(db
, p
->pEList
, 0);
3856 pGroupBy
= p
->pGroupBy
;
3857 p
->selFlags
&= ~SF_Distinct
;
3860 /* If there is both a GROUP BY and an ORDER BY clause and they are
3861 ** identical, then disable the ORDER BY clause since the GROUP BY
3862 ** will cause elements to come out in the correct order. This is
3863 ** an optimization - the correct answer should result regardless.
3864 ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
3865 ** to disable this optimization for testing purposes.
3867 if( sqlite3ExprListCompare(p
->pGroupBy
, pOrderBy
)==0
3868 && (db
->flags
& SQLITE_GroupByOrder
)==0 ){
3872 /* If there is an ORDER BY clause, then this sorting
3873 ** index might end up being unused if the data can be
3874 ** extracted in pre-sorted order. If that is the case, then the
3875 ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
3876 ** we figure out that the sorting index is not needed. The addrSortIndex
3877 ** variable is used to facilitate that change.
3881 pKeyInfo
= keyInfoFromExprList(pParse
, pOrderBy
);
3882 pOrderBy
->iECursor
= pParse
->nTab
++;
3883 p
->addrOpenEphm
[2] = addrSortIndex
=
3884 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
,
3885 pOrderBy
->iECursor
, pOrderBy
->nExpr
+2, 0,
3886 (char*)pKeyInfo
, P4_KEYINFO_HANDOFF
);
3891 /* If the output is destined for a temporary table, open that table.
3893 if( pDest
->eDest
==SRT_EphemTab
){
3894 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, pDest
->iParm
, pEList
->nExpr
);
3899 iEnd
= sqlite3VdbeMakeLabel(v
);
3900 p
->nSelectRow
= (double)LARGEST_INT64
;
3901 computeLimitRegisters(pParse
, p
, iEnd
);
3903 /* Open a virtual index to use for the distinct set.
3905 if( p
->selFlags
& SF_Distinct
){
3907 assert( isAgg
|| pGroupBy
);
3908 distinct
= pParse
->nTab
++;
3909 pKeyInfo
= keyInfoFromExprList(pParse
, p
->pEList
);
3910 sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
, distinct
, 0, 0,
3911 (char*)pKeyInfo
, P4_KEYINFO_HANDOFF
);
3912 sqlite3VdbeChangeP5(v
, BTREE_UNORDERED
);
3917 /* Aggregate and non-aggregate queries are handled differently */
3918 if( !isAgg
&& pGroupBy
==0 ){
3919 /* This case is for non-aggregate queries
3920 ** Begin the database scan
3922 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, &pOrderBy
, 0);
3923 if( pWInfo
==0 ) goto select_end
;
3924 if( pWInfo
->nRowOut
< p
->nSelectRow
) p
->nSelectRow
= pWInfo
->nRowOut
;
3926 /* If sorting index that was created by a prior OP_OpenEphemeral
3927 ** instruction ended up not being needed, then change the OP_OpenEphemeral
3930 if( addrSortIndex
>=0 && pOrderBy
==0 ){
3931 sqlite3VdbeChangeToNoop(v
, addrSortIndex
, 1);
3932 p
->addrOpenEphm
[2] = -1;
3935 /* Use the standard inner loop
3937 assert(!isDistinct
);
3938 selectInnerLoop(pParse
, p
, pEList
, 0, 0, pOrderBy
, -1, pDest
,
3939 pWInfo
->iContinue
, pWInfo
->iBreak
);
3941 /* End the database scan loop.
3943 sqlite3WhereEnd(pWInfo
);
3945 /* This is the processing for aggregate queries */
3946 NameContext sNC
; /* Name context for processing aggregate information */
3947 int iAMem
; /* First Mem address for storing current GROUP BY */
3948 int iBMem
; /* First Mem address for previous GROUP BY */
3949 int iUseFlag
; /* Mem address holding flag indicating that at least
3950 ** one row of the input to the aggregator has been
3952 int iAbortFlag
; /* Mem address which causes query abort if positive */
3953 int groupBySort
; /* Rows come from source in GROUP BY order */
3954 int addrEnd
; /* End of processing for this SELECT */
3956 /* Remove any and all aliases between the result set and the
3960 int k
; /* Loop counter */
3961 struct ExprList_item
*pItem
; /* For looping over expression in a list */
3963 for(k
=p
->pEList
->nExpr
, pItem
=p
->pEList
->a
; k
>0; k
--, pItem
++){
3966 for(k
=pGroupBy
->nExpr
, pItem
=pGroupBy
->a
; k
>0; k
--, pItem
++){
3969 if( p
->nSelectRow
>(double)100 ) p
->nSelectRow
= (double)100;
3971 p
->nSelectRow
= (double)1;
3975 /* Create a label to jump to when we want to abort the query */
3976 addrEnd
= sqlite3VdbeMakeLabel(v
);
3978 /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
3979 ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
3980 ** SELECT statement.
3982 memset(&sNC
, 0, sizeof(sNC
));
3983 sNC
.pParse
= pParse
;
3984 sNC
.pSrcList
= pTabList
;
3985 sNC
.pAggInfo
= &sAggInfo
;
3986 sAggInfo
.nSortingColumn
= pGroupBy
? pGroupBy
->nExpr
+1 : 0;
3987 sAggInfo
.pGroupBy
= pGroupBy
;
3988 sqlite3ExprAnalyzeAggList(&sNC
, pEList
);
3989 sqlite3ExprAnalyzeAggList(&sNC
, pOrderBy
);
3991 sqlite3ExprAnalyzeAggregates(&sNC
, pHaving
);
3993 sAggInfo
.nAccumulator
= sAggInfo
.nColumn
;
3994 for(i
=0; i
<sAggInfo
.nFunc
; i
++){
3995 assert( !ExprHasProperty(sAggInfo
.aFunc
[i
].pExpr
, EP_xIsSelect
) );
3996 sqlite3ExprAnalyzeAggList(&sNC
, sAggInfo
.aFunc
[i
].pExpr
->x
.pList
);
3998 if( db
->mallocFailed
) goto select_end
;
4000 /* Processing for aggregates with GROUP BY is very different and
4001 ** much more complex than aggregates without a GROUP BY.
4004 KeyInfo
*pKeyInfo
; /* Keying information for the group by clause */
4005 int j1
; /* A-vs-B comparision jump */
4006 int addrOutputRow
; /* Start of subroutine that outputs a result row */
4007 int regOutputRow
; /* Return address register for output subroutine */
4008 int addrSetAbort
; /* Set the abort flag and return */
4009 int addrTopOfLoop
; /* Top of the input loop */
4010 int addrSortingIdx
; /* The OP_OpenEphemeral for the sorting index */
4011 int addrReset
; /* Subroutine for resetting the accumulator */
4012 int regReset
; /* Return address register for reset subroutine */
4014 /* If there is a GROUP BY clause we might need a sorting index to
4015 ** implement it. Allocate that sorting index now. If it turns out
4016 ** that we do not need it after all, the OpenEphemeral instruction
4017 ** will be converted into a Noop.
4019 sAggInfo
.sortingIdx
= pParse
->nTab
++;
4020 pKeyInfo
= keyInfoFromExprList(pParse
, pGroupBy
);
4021 addrSortingIdx
= sqlite3VdbeAddOp4(v
, OP_OpenEphemeral
,
4022 sAggInfo
.sortingIdx
, sAggInfo
.nSortingColumn
,
4023 0, (char*)pKeyInfo
, P4_KEYINFO_HANDOFF
);
4025 /* Initialize memory locations used by GROUP BY aggregate processing
4027 iUseFlag
= ++pParse
->nMem
;
4028 iAbortFlag
= ++pParse
->nMem
;
4029 regOutputRow
= ++pParse
->nMem
;
4030 addrOutputRow
= sqlite3VdbeMakeLabel(v
);
4031 regReset
= ++pParse
->nMem
;
4032 addrReset
= sqlite3VdbeMakeLabel(v
);
4033 iAMem
= pParse
->nMem
+ 1;
4034 pParse
->nMem
+= pGroupBy
->nExpr
;
4035 iBMem
= pParse
->nMem
+ 1;
4036 pParse
->nMem
+= pGroupBy
->nExpr
;
4037 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, iAbortFlag
);
4038 VdbeComment((v
, "clear abort flag"));
4039 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, iUseFlag
);
4040 VdbeComment((v
, "indicate accumulator empty"));
4042 /* Begin a loop that will extract all source rows in GROUP BY order.
4043 ** This might involve two separate loops with an OP_Sort in between, or
4044 ** it might be a single loop that uses an index to extract information
4045 ** in the right order to begin with.
4047 sqlite3VdbeAddOp2(v
, OP_Gosub
, regReset
, addrReset
);
4048 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, &pGroupBy
, 0);
4049 if( pWInfo
==0 ) goto select_end
;
4051 /* The optimizer is able to deliver rows in group by order so
4052 ** we do not have to sort. The OP_OpenEphemeral table will be
4053 ** cancelled later because we still need to use the pKeyInfo
4055 pGroupBy
= p
->pGroupBy
;
4058 /* Rows are coming out in undetermined order. We have to push
4059 ** each row into a sorting index, terminate the first loop,
4060 ** then loop over the sorting index in order to get the output
4068 explainTempTable(pParse
,
4069 isDistinct
&& !(p
->selFlags
&SF_Distinct
)?"DISTINCT":"GROUP BY");
4072 nGroupBy
= pGroupBy
->nExpr
;
4073 nCol
= nGroupBy
+ 1;
4075 for(i
=0; i
<sAggInfo
.nColumn
; i
++){
4076 if( sAggInfo
.aCol
[i
].iSorterColumn
>=j
){
4081 regBase
= sqlite3GetTempRange(pParse
, nCol
);
4082 sqlite3ExprCacheClear(pParse
);
4083 sqlite3ExprCodeExprList(pParse
, pGroupBy
, regBase
, 0);
4084 sqlite3VdbeAddOp2(v
, OP_Sequence
, sAggInfo
.sortingIdx
,regBase
+nGroupBy
);
4086 for(i
=0; i
<sAggInfo
.nColumn
; i
++){
4087 struct AggInfo_col
*pCol
= &sAggInfo
.aCol
[i
];
4088 if( pCol
->iSorterColumn
>=j
){
4089 int r1
= j
+ regBase
;
4092 r2
= sqlite3ExprCodeGetColumn(pParse
,
4093 pCol
->pTab
, pCol
->iColumn
, pCol
->iTable
, r1
);
4095 sqlite3VdbeAddOp2(v
, OP_SCopy
, r2
, r1
);
4100 regRecord
= sqlite3GetTempReg(pParse
);
4101 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regBase
, nCol
, regRecord
);
4102 sqlite3VdbeAddOp2(v
, OP_IdxInsert
, sAggInfo
.sortingIdx
, regRecord
);
4103 sqlite3ReleaseTempReg(pParse
, regRecord
);
4104 sqlite3ReleaseTempRange(pParse
, regBase
, nCol
);
4105 sqlite3WhereEnd(pWInfo
);
4106 sqlite3VdbeAddOp2(v
, OP_Sort
, sAggInfo
.sortingIdx
, addrEnd
);
4107 VdbeComment((v
, "GROUP BY sort"));
4108 sAggInfo
.useSortingIdx
= 1;
4109 sqlite3ExprCacheClear(pParse
);
4112 /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
4113 ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
4114 ** Then compare the current GROUP BY terms against the GROUP BY terms
4115 ** from the previous row currently stored in a0, a1, a2...
4117 addrTopOfLoop
= sqlite3VdbeCurrentAddr(v
);
4118 sqlite3ExprCacheClear(pParse
);
4119 for(j
=0; j
<pGroupBy
->nExpr
; j
++){
4121 sqlite3VdbeAddOp3(v
, OP_Column
, sAggInfo
.sortingIdx
, j
, iBMem
+j
);
4123 sAggInfo
.directMode
= 1;
4124 sqlite3ExprCode(pParse
, pGroupBy
->a
[j
].pExpr
, iBMem
+j
);
4127 sqlite3VdbeAddOp4(v
, OP_Compare
, iAMem
, iBMem
, pGroupBy
->nExpr
,
4128 (char*)pKeyInfo
, P4_KEYINFO
);
4129 j1
= sqlite3VdbeCurrentAddr(v
);
4130 sqlite3VdbeAddOp3(v
, OP_Jump
, j1
+1, 0, j1
+1);
4132 /* Generate code that runs whenever the GROUP BY changes.
4133 ** Changes in the GROUP BY are detected by the previous code
4134 ** block. If there were no changes, this block is skipped.
4136 ** This code copies current group by terms in b0,b1,b2,...
4137 ** over to a0,a1,a2. It then calls the output subroutine
4138 ** and resets the aggregate accumulator registers in preparation
4139 ** for the next GROUP BY batch.
4141 sqlite3ExprCodeMove(pParse
, iBMem
, iAMem
, pGroupBy
->nExpr
);
4142 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutputRow
, addrOutputRow
);
4143 VdbeComment((v
, "output one row"));
4144 sqlite3VdbeAddOp2(v
, OP_IfPos
, iAbortFlag
, addrEnd
);
4145 VdbeComment((v
, "check abort flag"));
4146 sqlite3VdbeAddOp2(v
, OP_Gosub
, regReset
, addrReset
);
4147 VdbeComment((v
, "reset accumulator"));
4149 /* Update the aggregate accumulators based on the content of
4152 sqlite3VdbeJumpHere(v
, j1
);
4153 updateAccumulator(pParse
, &sAggInfo
);
4154 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iUseFlag
);
4155 VdbeComment((v
, "indicate data in accumulator"));
4160 sqlite3VdbeAddOp2(v
, OP_Next
, sAggInfo
.sortingIdx
, addrTopOfLoop
);
4162 sqlite3WhereEnd(pWInfo
);
4163 sqlite3VdbeChangeToNoop(v
, addrSortingIdx
, 1);
4166 /* Output the final row of result
4168 sqlite3VdbeAddOp2(v
, OP_Gosub
, regOutputRow
, addrOutputRow
);
4169 VdbeComment((v
, "output final row"));
4171 /* Jump over the subroutines
4173 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, addrEnd
);
4175 /* Generate a subroutine that outputs a single row of the result
4176 ** set. This subroutine first looks at the iUseFlag. If iUseFlag
4177 ** is less than or equal to zero, the subroutine is a no-op. If
4178 ** the processing calls for the query to abort, this subroutine
4179 ** increments the iAbortFlag memory location before returning in
4180 ** order to signal the caller to abort.
4182 addrSetAbort
= sqlite3VdbeCurrentAddr(v
);
4183 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, iAbortFlag
);
4184 VdbeComment((v
, "set abort flag"));
4185 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
4186 sqlite3VdbeResolveLabel(v
, addrOutputRow
);
4187 addrOutputRow
= sqlite3VdbeCurrentAddr(v
);
4188 sqlite3VdbeAddOp2(v
, OP_IfPos
, iUseFlag
, addrOutputRow
+2);
4189 VdbeComment((v
, "Groupby result generator entry point"));
4190 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
4191 finalizeAggFunctions(pParse
, &sAggInfo
);
4192 sqlite3ExprIfFalse(pParse
, pHaving
, addrOutputRow
+1, SQLITE_JUMPIFNULL
);
4193 selectInnerLoop(pParse
, p
, p
->pEList
, 0, 0, pOrderBy
,
4195 addrOutputRow
+1, addrSetAbort
);
4196 sqlite3VdbeAddOp1(v
, OP_Return
, regOutputRow
);
4197 VdbeComment((v
, "end groupby result generator"));
4199 /* Generate a subroutine that will reset the group-by accumulator
4201 sqlite3VdbeResolveLabel(v
, addrReset
);
4202 resetAccumulator(pParse
, &sAggInfo
);
4203 sqlite3VdbeAddOp1(v
, OP_Return
, regReset
);
4205 } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */
4208 #ifndef SQLITE_OMIT_BTREECOUNT
4210 if( (pTab
= isSimpleCount(p
, &sAggInfo
))!=0 ){
4211 /* If isSimpleCount() returns a pointer to a Table structure, then
4212 ** the SQL statement is of the form:
4214 ** SELECT count(*) FROM <tbl>
4216 ** where the Table structure returned represents table <tbl>.
4218 ** This statement is so common that it is optimized specially. The
4219 ** OP_Count instruction is executed either on the intkey table that
4220 ** contains the data for table <tbl> or on one of its indexes. It
4221 ** is better to execute the op on an index, as indexes are almost
4222 ** always spread across less pages than their corresponding tables.
4224 const int iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
4225 const int iCsr
= pParse
->nTab
++; /* Cursor to scan b-tree */
4226 Index
*pIdx
; /* Iterator variable */
4227 KeyInfo
*pKeyInfo
= 0; /* Keyinfo for scanned index */
4228 Index
*pBest
= 0; /* Best index found so far */
4229 int iRoot
= pTab
->tnum
; /* Root page of scanned b-tree */
4231 sqlite3CodeVerifySchema(pParse
, iDb
);
4232 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, 0, pTab
->zName
);
4234 /* Search for the index that has the least amount of columns. If
4235 ** there is such an index, and it has less columns than the table
4236 ** does, then we can assume that it consumes less space on disk and
4237 ** will therefore be cheaper to scan to determine the query result.
4238 ** In this case set iRoot to the root page number of the index b-tree
4239 ** and pKeyInfo to the KeyInfo structure required to navigate the
4242 ** In practice the KeyInfo structure will not be used. It is only
4243 ** passed to keep OP_OpenRead happy.
4245 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
4246 if( !pBest
|| pIdx
->nColumn
<pBest
->nColumn
){
4250 if( pBest
&& pBest
->nColumn
<pTab
->nCol
){
4251 iRoot
= pBest
->tnum
;
4252 pKeyInfo
= sqlite3IndexKeyinfo(pParse
, pBest
);
4255 /* Open a read-only cursor, execute the OP_Count, close the cursor. */
4256 sqlite3VdbeAddOp3(v
, OP_OpenRead
, iCsr
, iRoot
, iDb
);
4258 sqlite3VdbeChangeP4(v
, -1, (char *)pKeyInfo
, P4_KEYINFO_HANDOFF
);
4260 sqlite3VdbeAddOp2(v
, OP_Count
, iCsr
, sAggInfo
.aFunc
[0].iMem
);
4261 sqlite3VdbeAddOp1(v
, OP_Close
, iCsr
);
4262 explainSimpleCount(pParse
, pTab
, pBest
);
4264 #endif /* SQLITE_OMIT_BTREECOUNT */
4266 /* Check if the query is of one of the following forms:
4268 ** SELECT min(x) FROM ...
4269 ** SELECT max(x) FROM ...
4271 ** If it is, then ask the code in where.c to attempt to sort results
4272 ** as if there was an "ORDER ON x" or "ORDER ON x DESC" clause.
4273 ** If where.c is able to produce results sorted in this order, then
4274 ** add vdbe code to break out of the processing loop after the
4275 ** first iteration (since the first iteration of the loop is
4276 ** guaranteed to operate on the row with the minimum or maximum
4277 ** value of x, the only row required).
4279 ** A special flag must be passed to sqlite3WhereBegin() to slightly
4280 ** modify behaviour as follows:
4282 ** + If the query is a "SELECT min(x)", then the loop coded by
4283 ** where.c should not iterate over any values with a NULL value
4286 ** + The optimizer code in where.c (the thing that decides which
4287 ** index or indices to use) should place a different priority on
4288 ** satisfying the 'ORDER BY' clause than it does in other cases.
4289 ** Refer to code and comments in where.c for details.
4291 ExprList
*pMinMax
= 0;
4292 u8 flag
= minMaxQuery(p
);
4294 assert( !ExprHasProperty(p
->pEList
->a
[0].pExpr
, EP_xIsSelect
) );
4295 pMinMax
= sqlite3ExprListDup(db
, p
->pEList
->a
[0].pExpr
->x
.pList
,0);
4297 if( pMinMax
&& !db
->mallocFailed
){
4298 pMinMax
->a
[0].sortOrder
= flag
!=WHERE_ORDERBY_MIN
?1:0;
4299 pMinMax
->a
[0].pExpr
->op
= TK_COLUMN
;
4303 /* This case runs if the aggregate has no GROUP BY clause. The
4304 ** processing is much simpler since there is only a single row
4307 resetAccumulator(pParse
, &sAggInfo
);
4308 pWInfo
= sqlite3WhereBegin(pParse
, pTabList
, pWhere
, &pMinMax
, flag
);
4310 sqlite3ExprListDelete(db
, pDel
);
4313 updateAccumulator(pParse
, &sAggInfo
);
4314 if( !pMinMax
&& flag
){
4315 sqlite3VdbeAddOp2(v
, OP_Goto
, 0, pWInfo
->iBreak
);
4316 VdbeComment((v
, "%s() by index",
4317 (flag
==WHERE_ORDERBY_MIN
?"min":"max")));
4319 sqlite3WhereEnd(pWInfo
);
4320 finalizeAggFunctions(pParse
, &sAggInfo
);
4324 sqlite3ExprIfFalse(pParse
, pHaving
, addrEnd
, SQLITE_JUMPIFNULL
);
4325 selectInnerLoop(pParse
, p
, p
->pEList
, 0, 0, 0, -1,
4326 pDest
, addrEnd
, addrEnd
);
4327 sqlite3ExprListDelete(db
, pDel
);
4329 sqlite3VdbeResolveLabel(v
, addrEnd
);
4331 } /* endif aggregate query */
4334 explainTempTable(pParse
, "DISTINCT");
4337 /* If there is an ORDER BY clause, then we need to sort the results
4338 ** and send them to the callback one by one.
4341 explainTempTable(pParse
, "ORDER BY");
4342 generateSortTail(pParse
, p
, v
, pEList
->nExpr
, pDest
);
4345 /* Jump here to skip this query
4347 sqlite3VdbeResolveLabel(v
, iEnd
);
4349 /* The SELECT was successfully coded. Set the return code to 0
4350 ** to indicate no errors.
4354 /* Control jumps to here if an error is encountered above, or upon
4355 ** successful coding of the SELECT.
4358 explainSetInteger(pParse
->iSelectId
, iRestoreSelectId
);
4360 /* Identify column names if results of the SELECT are to be output.
4362 if( rc
==SQLITE_OK
&& pDest
->eDest
==SRT_Output
){
4363 generateColumnNames(pParse
, pTabList
, pEList
);
4366 sqlite3DbFree(db
, sAggInfo
.aCol
);
4367 sqlite3DbFree(db
, sAggInfo
.aFunc
);
4371 #if defined(SQLITE_DEBUG)
4373 *******************************************************************************
4374 ** The following code is used for testing and debugging only. The code
4375 ** that follows does not appear in normal builds.
4377 ** These routines are used to print out the content of all or part of a
4378 ** parse structures such as Select or Expr. Such printouts are useful
4379 ** for helping to understand what is happening inside the code generator
4380 ** during the execution of complex SELECT statements.
4382 ** These routine are not called anywhere from within the normal
4383 ** code base. Then are intended to be called from within the debugger
4384 ** or from temporary "printf" statements inserted for debugging.
4386 void sqlite3PrintExpr(Expr
*p
){
4387 if( !ExprHasProperty(p
, EP_IntValue
) && p
->u
.zToken
){
4388 sqlite3DebugPrintf("(%s", p
->u
.zToken
);
4390 sqlite3DebugPrintf("(%d", p
->op
);
4393 sqlite3DebugPrintf(" ");
4394 sqlite3PrintExpr(p
->pLeft
);
4397 sqlite3DebugPrintf(" ");
4398 sqlite3PrintExpr(p
->pRight
);
4400 sqlite3DebugPrintf(")");
4402 void sqlite3PrintExprList(ExprList
*pList
){
4404 for(i
=0; i
<pList
->nExpr
; i
++){
4405 sqlite3PrintExpr(pList
->a
[i
].pExpr
);
4406 if( i
<pList
->nExpr
-1 ){
4407 sqlite3DebugPrintf(", ");
4411 void sqlite3PrintSelect(Select
*p
, int indent
){
4412 sqlite3DebugPrintf("%*sSELECT(%p) ", indent
, "", p
);
4413 sqlite3PrintExprList(p
->pEList
);
4414 sqlite3DebugPrintf("\n");
4419 for(i
=0; i
<p
->pSrc
->nSrc
; i
++){
4420 struct SrcList_item
*pItem
= &p
->pSrc
->a
[i
];
4421 sqlite3DebugPrintf("%*s ", indent
+6, zPrefix
);
4423 if( pItem
->pSelect
){
4424 sqlite3DebugPrintf("(\n");
4425 sqlite3PrintSelect(pItem
->pSelect
, indent
+10);
4426 sqlite3DebugPrintf("%*s)", indent
+8, "");
4427 }else if( pItem
->zName
){
4428 sqlite3DebugPrintf("%s", pItem
->zName
);
4431 sqlite3DebugPrintf("(table: %s)", pItem
->pTab
->zName
);
4433 if( pItem
->zAlias
){
4434 sqlite3DebugPrintf(" AS %s", pItem
->zAlias
);
4436 if( i
<p
->pSrc
->nSrc
-1 ){
4437 sqlite3DebugPrintf(",");
4439 sqlite3DebugPrintf("\n");
4443 sqlite3DebugPrintf("%*s WHERE ", indent
, "");
4444 sqlite3PrintExpr(p
->pWhere
);
4445 sqlite3DebugPrintf("\n");
4448 sqlite3DebugPrintf("%*s GROUP BY ", indent
, "");
4449 sqlite3PrintExprList(p
->pGroupBy
);
4450 sqlite3DebugPrintf("\n");
4453 sqlite3DebugPrintf("%*s HAVING ", indent
, "");
4454 sqlite3PrintExpr(p
->pHaving
);
4455 sqlite3DebugPrintf("\n");
4458 sqlite3DebugPrintf("%*s ORDER BY ", indent
, "");
4459 sqlite3PrintExprList(p
->pOrderBy
);
4460 sqlite3DebugPrintf("\n");
4463 /* End of the structure debug printing code
4464 *****************************************************************************/
4465 #endif /* defined(SQLITE_TEST) || defined(SQLITE_DEBUG) */