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.
17
#include "sqliteInt.h"
21
** Allocate a new Select structure and return a pointer to that
24
Select *sqliteSelectNew(
25
ExprList *pEList, /* which columns to include in the result */
26
SrcList *pSrc, /* the FROM clause -- which tables to scan */
27
Expr *pWhere, /* the WHERE clause */
28
ExprList *pGroupBy, /* the GROUP BY clause */
29
Expr *pHaving, /* the HAVING clause */
30
ExprList *pOrderBy, /* the ORDER BY clause */
31
int isDistinct, /* true if the DISTINCT keyword is present */
32
int nLimit, /* LIMIT value. -1 means not used */
33
int nOffset /* OFFSET value. 0 means no offset */
36
pNew = sqliteMalloc( sizeof(*pNew) );
38
sqliteExprListDelete(pEList);
39
sqliteSrcListDelete(pSrc);
40
sqliteExprDelete(pWhere);
41
sqliteExprListDelete(pGroupBy);
42
sqliteExprDelete(pHaving);
43
sqliteExprListDelete(pOrderBy);
46
pEList = sqliteExprListAppend(0, sqliteExpr(TK_ALL,0,0,0), 0);
48
pNew->pEList = pEList;
50
pNew->pWhere = pWhere;
51
pNew->pGroupBy = pGroupBy;
52
pNew->pHaving = pHaving;
53
pNew->pOrderBy = pOrderBy;
54
pNew->isDistinct = isDistinct;
56
pNew->nLimit = nLimit;
57
pNew->nOffset = nOffset;
65
** Given 1 to 3 identifiers preceeding the JOIN keyword, determine the
66
** type of join. Return an integer constant that expresses that type
67
** in terms of the following bit values:
75
** A full outer join is the combination of JT_LEFT and JT_RIGHT.
77
** If an illegal or unsupported join type is seen, then still return
78
** a join type, but put an error in the pParse structure.
80
int sqliteJoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
89
{ "natural", 7, JT_NATURAL },
90
{ "left", 4, JT_LEFT|JT_OUTER },
91
{ "right", 5, JT_RIGHT|JT_OUTER },
92
{ "full", 4, JT_LEFT|JT_RIGHT|JT_OUTER },
93
{ "outer", 5, JT_OUTER },
94
{ "inner", 5, JT_INNER },
95
{ "cross", 5, JT_INNER },
101
for(i=0; i<3 && apAll[i]; i++){
103
for(j=0; j<sizeof(keywords)/sizeof(keywords[0]); j++){
104
if( p->n==keywords[j].nChar
105
&& sqliteStrNICmp(p->z, keywords[j].zKeyword, p->n)==0 ){
106
jointype |= keywords[j].code;
110
if( j>=sizeof(keywords)/sizeof(keywords[0]) ){
111
jointype |= JT_ERROR;
116
(jointype & (JT_INNER|JT_OUTER))==(JT_INNER|JT_OUTER) ||
117
(jointype & JT_ERROR)!=0
119
static Token dummy = { 0, 0 };
120
char *zSp1 = " ", *zSp2 = " ";
121
if( pB==0 ){ pB = &dummy; zSp1 = 0; }
122
if( pC==0 ){ pC = &dummy; zSp2 = 0; }
123
sqliteSetNString(&pParse->zErrMsg, "unknown or unsupported join type: ", 0,
124
pA->z, pA->n, zSp1, 1, pB->z, pB->n, zSp2, 1, pC->z, pC->n, 0);
127
}else if( jointype & JT_RIGHT ){
128
sqliteErrorMsg(pParse,
129
"RIGHT and FULL OUTER JOINs are not currently supported");
136
** Return the index of a column in a table. Return -1 if the column
137
** is not contained in the table.
139
static int columnIndex(Table *pTab, const char *zCol){
141
for(i=0; i<pTab->nCol; i++){
142
if( sqliteStrICmp(pTab->aCol[i].zName, zCol)==0 ) return i;
148
** Add a term to the WHERE expression in *ppExpr that requires the
149
** zCol column to be equal in the two tables pTab1 and pTab2.
151
static void addWhereTerm(
152
const char *zCol, /* Name of the column */
153
const Table *pTab1, /* First table */
154
const Table *pTab2, /* Second table */
155
Expr **ppExpr /* Add the equality term to this expression */
158
Expr *pE1a, *pE1b, *pE1c;
159
Expr *pE2a, *pE2b, *pE2c;
163
dummy.n = strlen(zCol);
165
pE1a = sqliteExpr(TK_ID, 0, 0, &dummy);
166
pE2a = sqliteExpr(TK_ID, 0, 0, &dummy);
167
dummy.z = pTab1->zName;
168
dummy.n = strlen(dummy.z);
169
pE1b = sqliteExpr(TK_ID, 0, 0, &dummy);
170
dummy.z = pTab2->zName;
171
dummy.n = strlen(dummy.z);
172
pE2b = sqliteExpr(TK_ID, 0, 0, &dummy);
173
pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0);
174
pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0);
175
pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0);
176
ExprSetProperty(pE, EP_FromJoin);
178
*ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0);
185
** Set the EP_FromJoin property on all terms of the given expression.
187
** The EP_FromJoin property is used on terms of an expression to tell
188
** the LEFT OUTER JOIN processing logic that this term is part of the
189
** join restriction specified in the ON or USING clause and not a part
190
** of the more general WHERE clause. These terms are moved over to the
191
** WHERE clause during join processing but we need to remember that they
192
** originated in the ON or USING clause.
194
static void setJoinExpr(Expr *p){
196
ExprSetProperty(p, EP_FromJoin);
197
setJoinExpr(p->pLeft);
203
** This routine processes the join information for a SELECT statement.
204
** ON and USING clauses are converted into extra terms of the WHERE clause.
205
** NATURAL joins also create extra WHERE clause terms.
207
** This routine returns the number of errors encountered.
209
static int sqliteProcessJoin(Parse *pParse, Select *p){
213
for(i=0; i<pSrc->nSrc-1; i++){
214
struct SrcList_item *pTerm = &pSrc->a[i];
215
struct SrcList_item *pOther = &pSrc->a[i+1];
217
if( pTerm->pTab==0 || pOther->pTab==0 ) continue;
219
/* When the NATURAL keyword is present, add WHERE clause terms for
220
** every column that the two tables have in common.
222
if( pTerm->jointype & JT_NATURAL ){
224
if( pTerm->pOn || pTerm->pUsing ){
225
sqliteErrorMsg(pParse, "a NATURAL join may not have "
226
"an ON or USING clause", 0);
230
for(j=0; j<pTab->nCol; j++){
231
if( columnIndex(pOther->pTab, pTab->aCol[j].zName)>=0 ){
232
addWhereTerm(pTab->aCol[j].zName, pTab, pOther->pTab, &p->pWhere);
237
/* Disallow both ON and USING clauses in the same join
239
if( pTerm->pOn && pTerm->pUsing ){
240
sqliteErrorMsg(pParse, "cannot have both ON and USING "
241
"clauses in the same join");
245
/* Add the ON clause to the end of the WHERE clause, connected by
249
setJoinExpr(pTerm->pOn);
251
p->pWhere = pTerm->pOn;
253
p->pWhere = sqliteExpr(TK_AND, p->pWhere, pTerm->pOn, 0);
258
/* Create extra terms on the WHERE clause for each column named
259
** in the USING clause. Example: If the two tables to be joined are
260
** A and B and the USING clause names X, Y, and Z, then add this
261
** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
262
** Report an error if any column mentioned in the USING clause is
263
** not contained in both tables to be joined.
268
assert( i<pSrc->nSrc-1 );
269
pList = pTerm->pUsing;
270
for(j=0; j<pList->nId; j++){
271
if( columnIndex(pTerm->pTab, pList->a[j].zName)<0 ||
272
columnIndex(pOther->pTab, pList->a[j].zName)<0 ){
273
sqliteErrorMsg(pParse, "cannot join using column %s - column "
274
"not present in both tables", pList->a[j].zName);
277
addWhereTerm(pList->a[j].zName, pTerm->pTab, pOther->pTab, &p->pWhere);
285
** Delete the given Select structure and all of its substructures.
287
void sqliteSelectDelete(Select *p){
289
sqliteExprListDelete(p->pEList);
290
sqliteSrcListDelete(p->pSrc);
291
sqliteExprDelete(p->pWhere);
292
sqliteExprListDelete(p->pGroupBy);
293
sqliteExprDelete(p->pHaving);
294
sqliteExprListDelete(p->pOrderBy);
295
sqliteSelectDelete(p->pPrior);
296
sqliteFree(p->zSelect);
301
** Delete the aggregate information from the parse structure.
303
static void sqliteAggregateInfoReset(Parse *pParse){
304
sqliteFree(pParse->aAgg);
311
** Insert code into "v" that will push the record on the top of the
312
** stack into the sorter.
314
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
317
zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
318
if( zSortOrder==0 ) return;
319
for(i=0; i<pOrderBy->nExpr; i++){
320
int order = pOrderBy->a[i].sortOrder;
323
if( (order & SQLITE_SO_TYPEMASK)==SQLITE_SO_TEXT ){
324
type = SQLITE_SO_TEXT;
325
}else if( (order & SQLITE_SO_TYPEMASK)==SQLITE_SO_NUM ){
326
type = SQLITE_SO_NUM;
327
}else if( pParse->db->file_format>=4 ){
328
type = sqliteExprType(pOrderBy->a[i].pExpr);
330
type = SQLITE_SO_NUM;
332
if( (order & SQLITE_SO_DIRMASK)==SQLITE_SO_ASC ){
333
c = type==SQLITE_SO_TEXT ? 'A' : '+';
335
c = type==SQLITE_SO_TEXT ? 'D' : '-';
338
sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
340
zSortOrder[pOrderBy->nExpr] = 0;
341
sqliteVdbeOp3(v, OP_SortMakeKey, pOrderBy->nExpr, 0, zSortOrder, P3_DYNAMIC);
342
sqliteVdbeAddOp(v, OP_SortPut, 0, 0);
346
** This routine adds a P3 argument to the last VDBE opcode that was
347
** inserted. The P3 argument added is a string suitable for the
348
** OP_MakeKey or OP_MakeIdxKey opcodes. The string consists of
349
** characters 't' or 'n' depending on whether or not the various
350
** fields of the key to be generated should be treated as numeric
351
** or as text. See the OP_MakeKey and OP_MakeIdxKey opcode
352
** documentation for additional information about the P3 string.
353
** See also the sqliteAddIdxKeyType() routine.
355
void sqliteAddKeyType(Vdbe *v, ExprList *pEList){
356
int nColumn = pEList->nExpr;
357
char *zType = sqliteMalloc( nColumn+1 );
359
if( zType==0 ) return;
360
for(i=0; i<nColumn; i++){
361
zType[i] = sqliteExprType(pEList->a[i].pExpr)==SQLITE_SO_NUM ? 'n' : 't';
364
sqliteVdbeChangeP3(v, -1, zType, P3_DYNAMIC);
368
** Add code to implement the OFFSET and LIMIT
370
static void codeLimiter(
371
Vdbe *v, /* Generate code into this VM */
372
Select *p, /* The SELECT statement being coded */
373
int iContinue, /* Jump here to skip the current record */
374
int iBreak, /* Jump here to end the loop */
375
int nPop /* Number of times to pop stack when jumping */
378
int addr = sqliteVdbeCurrentAddr(v) + 2;
380
sqliteVdbeAddOp(v, OP_MemIncr, p->iOffset, addr);
382
sqliteVdbeAddOp(v, OP_Pop, nPop, 0);
384
sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
387
sqliteVdbeAddOp(v, OP_MemIncr, p->iLimit, iBreak);
392
** This routine generates the code for the inside of the inner loop
395
** If srcTab and nColumn are both zero, then the pEList expressions
396
** are evaluated in order to get the data for this row. If nColumn>0
397
** then data is pulled from srcTab and pEList is used only to get the
398
** datatypes for each column.
400
static int selectInnerLoop(
401
Parse *pParse, /* The parser context */
402
Select *p, /* The complete select statement being coded */
403
ExprList *pEList, /* List of values being extracted */
404
int srcTab, /* Pull data from this table */
405
int nColumn, /* Number of columns in the source table */
406
ExprList *pOrderBy, /* If not NULL, sort results using this key */
407
int distinct, /* If >=0, make sure results are distinct */
408
int eDest, /* How to dispose of the results */
409
int iParm, /* An argument to the disposal method */
410
int iContinue, /* Jump here to continue with next row */
411
int iBreak /* Jump here to break out of the inner loop */
413
Vdbe *v = pParse->pVdbe;
415
int hasDistinct; /* True if the DISTINCT keyword is present */
420
/* If there was a LIMIT clause on the SELECT statement, then do the check
421
** to see if this row should be output.
423
hasDistinct = distinct>=0 && pEList && pEList->nExpr>0;
424
if( pOrderBy==0 && !hasDistinct ){
425
codeLimiter(v, p, iContinue, iBreak, 0);
428
/* Pull the requested columns.
431
for(i=0; i<nColumn; i++){
432
sqliteVdbeAddOp(v, OP_Column, srcTab, i);
435
nColumn = pEList->nExpr;
436
for(i=0; i<pEList->nExpr; i++){
437
sqliteExprCode(pParse, pEList->a[i].pExpr);
441
/* If the DISTINCT keyword was present on the SELECT statement
442
** and this row has been seen before, then do not make this row
443
** part of the result.
446
#if NULL_ALWAYS_DISTINCT
447
sqliteVdbeAddOp(v, OP_IsNull, -pEList->nExpr, sqliteVdbeCurrentAddr(v)+7);
449
sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1);
450
if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pEList);
451
sqliteVdbeAddOp(v, OP_Distinct, distinct, sqliteVdbeCurrentAddr(v)+3);
452
sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
453
sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
454
sqliteVdbeAddOp(v, OP_String, 0, 0);
455
sqliteVdbeAddOp(v, OP_PutStrKey, distinct, 0);
457
codeLimiter(v, p, iContinue, iBreak, nColumn);
462
/* In this mode, write each query result to the key of the temporary
466
sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
467
sqliteVdbeAddOp(v, OP_String, 0, 0);
468
sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
472
/* Store the result as data using a unique key.
475
case SRT_TempTable: {
476
sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
478
pushOntoSorter(pParse, v, pOrderBy);
480
sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
481
sqliteVdbeAddOp(v, OP_Pull, 1, 0);
482
sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
487
/* Construct a record from the query result, but instead of
488
** saving that record, use it as a key to delete elements from
489
** the temporary table iParm.
493
addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, NULL_ALWAYS_DISTINCT);
494
sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3);
495
sqliteVdbeAddOp(v, OP_Delete, iParm, 0);
499
/* If we are creating a set for an "expr IN (SELECT ...)" construct,
500
** then there should be a single item on the stack. Write this
501
** item into the set table with bogus data.
504
int addr1 = sqliteVdbeCurrentAddr(v);
506
assert( nColumn==1 );
507
sqliteVdbeAddOp(v, OP_NotNull, -1, addr1+3);
508
sqliteVdbeAddOp(v, OP_Pop, 1, 0);
509
addr2 = sqliteVdbeAddOp(v, OP_Goto, 0, 0);
511
pushOntoSorter(pParse, v, pOrderBy);
513
sqliteVdbeAddOp(v, OP_String, 0, 0);
514
sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
516
sqliteVdbeChangeP2(v, addr2, sqliteVdbeCurrentAddr(v));
520
/* If this is a scalar select that is part of an expression, then
521
** store the results in the appropriate memory cell and break out
525
assert( nColumn==1 );
527
pushOntoSorter(pParse, v, pOrderBy);
529
sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
530
sqliteVdbeAddOp(v, OP_Goto, 0, iBreak);
535
/* Send the data to the callback function.
540
sqliteVdbeAddOp(v, OP_SortMakeRec, nColumn, 0);
541
pushOntoSorter(pParse, v, pOrderBy);
543
assert( eDest==SRT_Callback );
544
sqliteVdbeAddOp(v, OP_Callback, nColumn, 0);
549
/* Invoke a subroutine to handle the results. The subroutine itself
550
** is responsible for popping the results off of the stack.
552
case SRT_Subroutine: {
554
sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
555
pushOntoSorter(pParse, v, pOrderBy);
557
sqliteVdbeAddOp(v, OP_Gosub, 0, iParm);
562
/* Discard the results. This is used for SELECT statements inside
563
** the body of a TRIGGER. The purpose of such selects is to call
564
** user-defined functions that have side effects. We do not care
565
** about the actual results of the select.
568
assert( eDest==SRT_Discard );
569
sqliteVdbeAddOp(v, OP_Pop, nColumn, 0);
577
** If the inner loop was generated using a non-null pOrderBy argument,
578
** then the results were placed in a sorter. After the loop is terminated
579
** we need to run the sorter and output the results. The following
580
** routine generates the code needed to do that.
582
static void generateSortTail(
583
Select *p, /* The SELECT statement */
584
Vdbe *v, /* Generate code into this VDBE */
585
int nColumn, /* Number of columns of data */
586
int eDest, /* Write the sorted results here */
587
int iParm /* Optional parameter associated with eDest */
589
int end1 = sqliteVdbeMakeLabel(v);
590
int end2 = sqliteVdbeMakeLabel(v);
592
if( eDest==SRT_Sorter ) return;
593
sqliteVdbeAddOp(v, OP_Sort, 0, 0);
594
addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end1);
595
codeLimiter(v, p, addr, end2, 1);
598
sqliteVdbeAddOp(v, OP_SortCallback, nColumn, 0);
602
case SRT_TempTable: {
603
sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
604
sqliteVdbeAddOp(v, OP_Pull, 1, 0);
605
sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
609
assert( nColumn==1 );
610
sqliteVdbeAddOp(v, OP_NotNull, -1, sqliteVdbeCurrentAddr(v)+3);
611
sqliteVdbeAddOp(v, OP_Pop, 1, 0);
612
sqliteVdbeAddOp(v, OP_Goto, 0, sqliteVdbeCurrentAddr(v)+3);
613
sqliteVdbeAddOp(v, OP_String, 0, 0);
614
sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
618
assert( nColumn==1 );
619
sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
620
sqliteVdbeAddOp(v, OP_Goto, 0, end1);
623
case SRT_Subroutine: {
625
for(i=0; i<nColumn; i++){
626
sqliteVdbeAddOp(v, OP_Column, -1-i, i);
628
sqliteVdbeAddOp(v, OP_Gosub, 0, iParm);
629
sqliteVdbeAddOp(v, OP_Pop, 1, 0);
637
sqliteVdbeAddOp(v, OP_Goto, 0, addr);
638
sqliteVdbeResolveLabel(v, end2);
639
sqliteVdbeAddOp(v, OP_Pop, 1, 0);
640
sqliteVdbeResolveLabel(v, end1);
641
sqliteVdbeAddOp(v, OP_SortReset, 0, 0);
645
** Generate code that will tell the VDBE the datatypes of
646
** columns in the result set.
648
** This routine only generates code if the "PRAGMA show_datatypes=on"
649
** has been executed. The datatypes are reported out in the azCol
650
** parameter to the callback function. The first N azCol[] entries
651
** are the names of the columns, and the second N entries are the
652
** datatypes for the columns.
654
** The "datatype" for a result that is a column of a type is the
655
** datatype definition extracted from the CREATE TABLE statement.
656
** The datatype for an expression is either TEXT or NUMERIC. The
657
** datatype for a ROWID field is INTEGER.
659
static void generateColumnTypes(
660
Parse *pParse, /* Parser context */
661
SrcList *pTabList, /* List of tables */
662
ExprList *pEList /* Expressions defining the result set */
664
Vdbe *v = pParse->pVdbe;
666
for(i=0; i<pEList->nExpr; i++){
667
Expr *p = pEList->a[i].pExpr;
670
if( p->op==TK_COLUMN && pTabList ){
672
int iCol = p->iColumn;
673
for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable; j++){}
674
assert( j<pTabList->nSrc );
675
pTab = pTabList->a[j].pTab;
676
if( iCol<0 ) iCol = pTab->iPKey;
677
assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
681
zType = pTab->aCol[iCol].zType;
684
if( sqliteExprType(p)==SQLITE_SO_TEXT ){
690
sqliteVdbeOp3(v, OP_ColumnName, i + pEList->nExpr, 0, zType, 0);
695
** Generate code that will tell the VDBE the names of columns
696
** in the result set. This information is used to provide the
697
** azCol[] values in the callback.
699
static void generateColumnNames(
700
Parse *pParse, /* Parser context */
701
SrcList *pTabList, /* List of tables */
702
ExprList *pEList /* Expressions defining the result set */
704
Vdbe *v = pParse->pVdbe;
706
sqlite *db = pParse->db;
707
int fullNames, shortNames;
710
if( pParse->colNamesSet || v==0 || sqlite_malloc_failed ) return;
711
pParse->colNamesSet = 1;
712
fullNames = (db->flags & SQLITE_FullColNames)!=0;
713
shortNames = (db->flags & SQLITE_ShortColNames)!=0;
714
for(i=0; i<pEList->nExpr; i++){
716
int p2 = i==pEList->nExpr-1;
717
p = pEList->a[i].pExpr;
719
if( pEList->a[i].zName ){
720
char *zName = pEList->a[i].zName;
721
sqliteVdbeOp3(v, OP_ColumnName, i, p2, zName, 0);
724
if( p->op==TK_COLUMN && pTabList ){
727
int iCol = p->iColumn;
728
for(j=0; j<pTabList->nSrc && pTabList->a[j].iCursor!=p->iTable; j++){}
729
assert( j<pTabList->nSrc );
730
pTab = pTabList->a[j].pTab;
731
if( iCol<0 ) iCol = pTab->iPKey;
732
assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
736
zCol = pTab->aCol[iCol].zName;
738
if( !shortNames && !fullNames && p->span.z && p->span.z[0] ){
739
int addr = sqliteVdbeOp3(v,OP_ColumnName, i, p2, p->span.z, p->span.n);
740
sqliteVdbeCompressSpace(v, addr);
741
}else if( fullNames || (!shortNames && pTabList->nSrc>1) ){
745
zTab = pTabList->a[j].zAlias;
746
if( fullNames || zTab==0 ) zTab = pTab->zName;
747
sqliteSetString(&zName, zTab, ".", zCol, 0);
748
sqliteVdbeOp3(v, OP_ColumnName, i, p2, zName, P3_DYNAMIC);
750
sqliteVdbeOp3(v, OP_ColumnName, i, p2, zCol, 0);
752
}else if( p->span.z && p->span.z[0] ){
753
int addr = sqliteVdbeOp3(v,OP_ColumnName, i, p2, p->span.z, p->span.n);
754
sqliteVdbeCompressSpace(v, addr);
757
assert( p->op!=TK_COLUMN || pTabList==0 );
758
sprintf(zName, "column%d", i+1);
759
sqliteVdbeOp3(v, OP_ColumnName, i, p2, zName, 0);
765
** Name of the connection operator, used for error messages.
767
static const char *selectOpName(int id){
770
case TK_ALL: z = "UNION ALL"; break;
771
case TK_INTERSECT: z = "INTERSECT"; break;
772
case TK_EXCEPT: z = "EXCEPT"; break;
773
default: z = "UNION"; break;
779
** Forward declaration
781
static int fillInColumnList(Parse*, Select*);
784
** Given a SELECT statement, generate a Table structure that describes
785
** the result set of that SELECT.
787
Table *sqliteResultSetOfSelect(Parse *pParse, char *zTabName, Select *pSelect){
793
if( fillInColumnList(pParse, pSelect) ){
796
pTab = sqliteMalloc( sizeof(Table) );
800
pTab->zName = zTabName ? sqliteStrDup(zTabName) : 0;
801
pEList = pSelect->pEList;
802
pTab->nCol = pEList->nExpr;
803
assert( pTab->nCol>0 );
804
pTab->aCol = aCol = sqliteMalloc( sizeof(pTab->aCol[0])*pTab->nCol );
805
for(i=0; i<pTab->nCol; i++){
807
if( pEList->a[i].zName ){
808
aCol[i].zName = sqliteStrDup(pEList->a[i].zName);
809
}else if( (p=pEList->a[i].pExpr)->op==TK_DOT
810
&& (pR=p->pRight)!=0 && pR->token.z && pR->token.z[0] ){
812
sqliteSetNString(&aCol[i].zName, pR->token.z, pR->token.n, 0);
813
for(j=cnt=0; j<i; j++){
814
if( sqliteStrICmp(aCol[j].zName, aCol[i].zName)==0 ){
817
sprintf(zBuf,"_%d",++cnt);
819
sqliteSetNString(&aCol[i].zName, pR->token.z, pR->token.n, zBuf, n,0);
823
}else if( p->span.z && p->span.z[0] ){
824
sqliteSetNString(&pTab->aCol[i].zName, p->span.z, p->span.n, 0);
827
sprintf(zBuf, "column%d", i+1);
828
aCol[i].zName = sqliteStrDup(zBuf);
830
sqliteDequote(aCol[i].zName);
837
** For the given SELECT statement, do three things.
839
** (1) Fill in the pTabList->a[].pTab fields in the SrcList that
840
** defines the set of tables that should be scanned. For views,
841
** fill pTabList->a[].pSelect with a copy of the SELECT statement
842
** that implements the view. A copy is made of the view's SELECT
843
** statement so that we can freely modify or delete that statement
844
** without worrying about messing up the presistent representation
847
** (2) Add terms to the WHERE clause to accomodate the NATURAL keyword
848
** on joins and the ON and USING clause of joins.
850
** (3) Scan the list of columns in the result set (pEList) looking
851
** for instances of the "*" operator or the TABLE.* operator.
852
** If found, expand each "*" to be every column in every table
853
** and TABLE.* to be every column in TABLE.
855
** Return 0 on success. If there are problems, leave an error message
856
** in pParse and return non-zero.
858
static int fillInColumnList(Parse *pParse, Select *p){
864
if( p==0 || p->pSrc==0 ) return 1;
868
/* Look up every table in the table list.
870
for(i=0; i<pTabList->nSrc; i++){
871
if( pTabList->a[i].pTab ){
872
/* This routine has run before! No need to continue */
875
if( pTabList->a[i].zName==0 ){
876
/* A sub-query in the FROM clause of a SELECT */
877
assert( pTabList->a[i].pSelect!=0 );
878
if( pTabList->a[i].zAlias==0 ){
880
sprintf(zFakeName, "sqlite_subquery_%p_",
881
(void*)pTabList->a[i].pSelect);
882
sqliteSetString(&pTabList->a[i].zAlias, zFakeName, 0);
884
pTabList->a[i].pTab = pTab =
885
sqliteResultSetOfSelect(pParse, pTabList->a[i].zAlias,
886
pTabList->a[i].pSelect);
890
/* The isTransient flag indicates that the Table structure has been
891
** dynamically allocated and may be freed at any time. In other words,
892
** pTab is not pointing to a persistent table structure that defines
893
** part of the schema. */
894
pTab->isTransient = 1;
896
/* An ordinary table or view name in the FROM clause */
897
pTabList->a[i].pTab = pTab =
898
sqliteLocateTable(pParse,pTabList->a[i].zName,pTabList->a[i].zDatabase);
903
/* We reach here if the named table is a really a view */
904
if( sqliteViewGetColumnNames(pParse, pTab) ){
907
/* If pTabList->a[i].pSelect!=0 it means we are dealing with a
908
** view within a view. The SELECT structure has already been
909
** copied by the outer view so we can skip the copy step here
910
** in the inner view.
912
if( pTabList->a[i].pSelect==0 ){
913
pTabList->a[i].pSelect = sqliteSelectDup(pTab->pSelect);
919
/* Process NATURAL keywords, and ON and USING clauses of joins.
921
if( sqliteProcessJoin(pParse, p) ) return 1;
923
/* For every "*" that occurs in the column list, insert the names of
924
** all columns in all tables. And for every TABLE.* insert the names
925
** of all columns in TABLE. The parser inserted a special expression
926
** with the TK_ALL operator for each "*" that it found in the column list.
927
** The following code just has to locate the TK_ALL expressions and expand
928
** each one to the list of all columns in all tables.
930
** The first loop just checks to see if there are any "*" operators
931
** that need expanding.
933
for(k=0; k<pEList->nExpr; k++){
934
Expr *pE = pEList->a[k].pExpr;
935
if( pE->op==TK_ALL ) break;
936
if( pE->op==TK_DOT && pE->pRight && pE->pRight->op==TK_ALL
937
&& pE->pLeft && pE->pLeft->op==TK_ID ) break;
940
if( k<pEList->nExpr ){
942
** If we get here it means the result set contains one or more "*"
943
** operators that need to be expanded. Loop through each expression
944
** in the result set and expand them one by one.
946
struct ExprList_item *a = pEList->a;
948
for(k=0; k<pEList->nExpr; k++){
949
Expr *pE = a[k].pExpr;
950
if( pE->op!=TK_ALL &&
951
(pE->op!=TK_DOT || pE->pRight==0 || pE->pRight->op!=TK_ALL) ){
952
/* This particular expression does not need to be expanded.
954
pNew = sqliteExprListAppend(pNew, a[k].pExpr, 0);
955
pNew->a[pNew->nExpr-1].zName = a[k].zName;
959
/* This expression is a "*" or a "TABLE.*" and needs to be
961
int tableSeen = 0; /* Set to 1 when TABLE matches */
962
char *zTName; /* text of name of TABLE */
963
if( pE->op==TK_DOT && pE->pLeft ){
964
zTName = sqliteTableNameFromToken(&pE->pLeft->token);
968
for(i=0; i<pTabList->nSrc; i++){
969
Table *pTab = pTabList->a[i].pTab;
970
char *zTabName = pTabList->a[i].zAlias;
971
if( zTabName==0 || zTabName[0]==0 ){
972
zTabName = pTab->zName;
974
if( zTName && (zTabName==0 || zTabName[0]==0 ||
975
sqliteStrICmp(zTName, zTabName)!=0) ){
979
for(j=0; j<pTab->nCol; j++){
980
Expr *pExpr, *pLeft, *pRight;
981
char *zName = pTab->aCol[j].zName;
983
if( i>0 && (pTabList->a[i-1].jointype & JT_NATURAL)!=0 &&
984
columnIndex(pTabList->a[i-1].pTab, zName)>=0 ){
985
/* In a NATURAL join, omit the join columns from the
986
** table on the right */
989
if( i>0 && sqliteIdListIndex(pTabList->a[i-1].pUsing, zName)>=0 ){
990
/* In a join with a USING clause, omit columns in the
991
** using clause from the table on the right. */
994
pRight = sqliteExpr(TK_ID, 0, 0, 0);
995
if( pRight==0 ) break;
996
pRight->token.z = zName;
997
pRight->token.n = strlen(zName);
998
pRight->token.dyn = 0;
999
if( zTabName && pTabList->nSrc>1 ){
1000
pLeft = sqliteExpr(TK_ID, 0, 0, 0);
1001
pExpr = sqliteExpr(TK_DOT, pLeft, pRight, 0);
1002
if( pExpr==0 ) break;
1003
pLeft->token.z = zTabName;
1004
pLeft->token.n = strlen(zTabName);
1005
pLeft->token.dyn = 0;
1006
sqliteSetString((char**)&pExpr->span.z, zTabName, ".", zName, 0);
1007
pExpr->span.n = strlen(pExpr->span.z);
1008
pExpr->span.dyn = 1;
1011
pExpr->token.dyn = 0;
1014
pExpr->span = pExpr->token;
1016
pNew = sqliteExprListAppend(pNew, pExpr, 0);
1021
sqliteErrorMsg(pParse, "no such table: %s", zTName);
1023
sqliteErrorMsg(pParse, "no tables specified");
1030
sqliteExprListDelete(pEList);
1037
** This routine recursively unlinks the Select.pSrc.a[].pTab pointers
1038
** in a select structure. It just sets the pointers to NULL. This
1039
** routine is recursive in the sense that if the Select.pSrc.a[].pSelect
1040
** pointer is not NULL, this routine is called recursively on that pointer.
1042
** This routine is called on the Select structure that defines a
1043
** VIEW in order to undo any bindings to tables. This is necessary
1044
** because those tables might be DROPed by a subsequent SQL command.
1045
** If the bindings are not removed, then the Select.pSrc->a[].pTab field
1046
** will be left pointing to a deallocated Table structure after the
1047
** DROP and a coredump will occur the next time the VIEW is used.
1049
void sqliteSelectUnbind(Select *p){
1051
SrcList *pSrc = p->pSrc;
1054
for(i=0; i<pSrc->nSrc; i++){
1055
if( (pTab = pSrc->a[i].pTab)!=0 ){
1056
if( pTab->isTransient ){
1057
sqliteDeleteTable(0, pTab);
1059
pSrc->a[i].pTab = 0;
1060
if( pSrc->a[i].pSelect ){
1061
sqliteSelectUnbind(pSrc->a[i].pSelect);
1068
** This routine associates entries in an ORDER BY expression list with
1069
** columns in a result. For each ORDER BY expression, the opcode of
1070
** the top-level node is changed to TK_COLUMN and the iColumn value of
1071
** the top-level node is filled in with column number and the iTable
1072
** value of the top-level node is filled with iTable parameter.
1074
** If there are prior SELECT clauses, they are processed first. A match
1075
** in an earlier SELECT takes precedence over a later SELECT.
1077
** Any entry that does not match is flagged as an error. The number
1078
** of errors is returned.
1080
** This routine does NOT correctly initialize the Expr.dataType field
1081
** of the ORDER BY expressions. The multiSelectSortOrder() routine
1082
** must be called to do that after the individual select statements
1083
** have all been analyzed. This routine is unable to compute Expr.dataType
1084
** because it must be called before the individual select statements
1085
** have been analyzed.
1087
static int matchOrderbyToColumn(
1088
Parse *pParse, /* A place to leave error messages */
1089
Select *pSelect, /* Match to result columns of this SELECT */
1090
ExprList *pOrderBy, /* The ORDER BY values to match against columns */
1091
int iTable, /* Insert this value in iTable */
1092
int mustComplete /* If TRUE all ORDER BYs must match */
1098
if( pSelect==0 || pOrderBy==0 ) return 1;
1100
for(i=0; i<pOrderBy->nExpr; i++){ pOrderBy->a[i].done = 0; }
1102
if( fillInColumnList(pParse, pSelect) ){
1105
if( pSelect->pPrior ){
1106
if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
1110
pEList = pSelect->pEList;
1111
for(i=0; i<pOrderBy->nExpr; i++){
1112
Expr *pE = pOrderBy->a[i].pExpr;
1114
if( pOrderBy->a[i].done ) continue;
1115
if( sqliteExprIsInteger(pE, &iCol) ){
1116
if( iCol<=0 || iCol>pEList->nExpr ){
1117
sqliteErrorMsg(pParse,
1118
"ORDER BY position %d should be between 1 and %d",
1119
iCol, pEList->nExpr);
1123
if( !mustComplete ) continue;
1126
for(j=0; iCol<0 && j<pEList->nExpr; j++){
1127
if( pEList->a[j].zName && (pE->op==TK_ID || pE->op==TK_STRING) ){
1128
char *zName, *zLabel;
1129
zName = pEList->a[j].zName;
1130
assert( pE->token.z );
1131
zLabel = sqliteStrNDup(pE->token.z, pE->token.n);
1132
sqliteDequote(zLabel);
1133
if( sqliteStrICmp(zName, zLabel)==0 ){
1138
if( iCol<0 && sqliteExprCompare(pE, pEList->a[j].pExpr) ){
1145
pE->iTable = iTable;
1146
pOrderBy->a[i].done = 1;
1148
if( iCol<0 && mustComplete ){
1149
sqliteErrorMsg(pParse,
1150
"ORDER BY term number %d does not match any result column", i+1);
1159
** Get a VDBE for the given parser context. Create a new one if necessary.
1160
** If an error occurs, return NULL and leave a message in pParse.
1162
Vdbe *sqliteGetVdbe(Parse *pParse){
1163
Vdbe *v = pParse->pVdbe;
1165
v = pParse->pVdbe = sqliteVdbeCreate(pParse->db);
1171
** This routine sets the Expr.dataType field on all elements of
1172
** the pOrderBy expression list. The pOrderBy list will have been
1173
** set up by matchOrderbyToColumn(). Hence each expression has
1174
** a TK_COLUMN as its root node. The Expr.iColumn refers to a
1175
** column in the result set. The datatype is set to SQLITE_SO_TEXT
1176
** if the corresponding column in p and every SELECT to the left of
1177
** p has a datatype of SQLITE_SO_TEXT. If the cooressponding column
1178
** in p or any of the left SELECTs is SQLITE_SO_NUM, then the datatype
1179
** of the order-by expression is set to SQLITE_SO_NUM.
1183
** CREATE TABLE one(a INTEGER, b TEXT);
1184
** CREATE TABLE two(c VARCHAR(5), d FLOAT);
1186
** SELECT b, b FROM one UNION SELECT d, c FROM two ORDER BY 1, 2;
1188
** The primary sort key will use SQLITE_SO_NUM because the "d" in
1189
** the second SELECT is numeric. The 1st column of the first SELECT
1190
** is text but that does not matter because a numeric always overrides
1193
** The secondary key will use the SQLITE_SO_TEXT sort order because
1194
** both the (second) "b" in the first SELECT and the "c" in the second
1195
** SELECT have a datatype of text.
1197
static void multiSelectSortOrder(Select *p, ExprList *pOrderBy){
1200
if( pOrderBy==0 ) return;
1202
for(i=0; i<pOrderBy->nExpr; i++){
1203
pOrderBy->a[i].pExpr->dataType = SQLITE_SO_TEXT;
1207
multiSelectSortOrder(p->pPrior, pOrderBy);
1209
for(i=0; i<pOrderBy->nExpr; i++){
1210
Expr *pE = pOrderBy->a[i].pExpr;
1211
if( pE->dataType==SQLITE_SO_NUM ) continue;
1212
assert( pE->iColumn>=0 );
1213
if( pEList->nExpr>pE->iColumn ){
1214
pE->dataType = sqliteExprType(pEList->a[pE->iColumn].pExpr);
1220
** Compute the iLimit and iOffset fields of the SELECT based on the
1221
** nLimit and nOffset fields. nLimit and nOffset hold the integers
1222
** that appear in the original SQL statement after the LIMIT and OFFSET
1223
** keywords. Or that hold -1 and 0 if those keywords are omitted.
1224
** iLimit and iOffset are the integer memory register numbers for
1225
** counters used to compute the limit and offset. If there is no
1226
** limit and/or offset, then iLimit and iOffset are negative.
1228
** This routine changes the values if iLimit and iOffset only if
1229
** a limit or offset is defined by nLimit and nOffset. iLimit and
1230
** iOffset should have been preset to appropriate default values
1231
** (usually but not always -1) prior to calling this routine.
1232
** Only if nLimit>=0 or nOffset>0 do the limit registers get
1233
** redefined. The UNION ALL operator uses this property to force
1234
** the reuse of the same limit and offset registers across multiple
1235
** SELECT statements.
1237
static void computeLimitRegisters(Parse *pParse, Select *p){
1239
** If the comparison is p->nLimit>0 then "LIMIT 0" shows
1240
** all rows. It is the same as no limit. If the comparision is
1241
** p->nLimit>=0 then "LIMIT 0" show no rows at all.
1242
** "LIMIT -1" always shows all rows. There is some
1243
** contraversy about what the correct behavior should be.
1244
** The current implementation interprets "LIMIT 0" to mean
1248
int iMem = pParse->nMem++;
1249
Vdbe *v = sqliteGetVdbe(pParse);
1251
sqliteVdbeAddOp(v, OP_Integer, -p->nLimit, 0);
1252
sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
1256
int iMem = pParse->nMem++;
1257
Vdbe *v = sqliteGetVdbe(pParse);
1259
sqliteVdbeAddOp(v, OP_Integer, -p->nOffset, 0);
1260
sqliteVdbeAddOp(v, OP_MemStore, iMem, 1);
1266
** This routine is called to process a query that is really the union
1267
** or intersection of two or more separate queries.
1269
** "p" points to the right-most of the two queries. the query on the
1270
** left is p->pPrior. The left query could also be a compound query
1271
** in which case this routine will be called recursively.
1273
** The results of the total query are to be written into a destination
1274
** of type eDest with parameter iParm.
1276
** Example 1: Consider a three-way compound SQL statement.
1278
** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
1280
** This statement is parsed up as follows:
1284
** `-----> SELECT b FROM t2
1286
** `------> SELECT a FROM t1
1288
** The arrows in the diagram above represent the Select.pPrior pointer.
1289
** So if this routine is called with p equal to the t3 query, then
1290
** pPrior will be the t2 query. p->op will be TK_UNION in this case.
1292
** Notice that because of the way SQLite parses compound SELECTs, the
1293
** individual selects always group from left to right.
1295
static int multiSelect(Parse *pParse, Select *p, int eDest, int iParm){
1296
int rc; /* Success code from a subroutine */
1297
Select *pPrior; /* Another SELECT immediately to our left */
1298
Vdbe *v; /* Generate code to this VDBE */
1300
/* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
1301
** the last SELECT in the series may have an ORDER BY or LIMIT.
1303
if( p==0 || p->pPrior==0 ) return 1;
1305
if( pPrior->pOrderBy ){
1306
sqliteErrorMsg(pParse,"ORDER BY clause should come after %s not before",
1307
selectOpName(p->op));
1310
if( pPrior->nLimit>=0 || pPrior->nOffset>0 ){
1311
sqliteErrorMsg(pParse,"LIMIT clause should come after %s not before",
1312
selectOpName(p->op));
1316
/* Make sure we have a valid query engine. If not, create a new one.
1318
v = sqliteGetVdbe(pParse);
1319
if( v==0 ) return 1;
1321
/* Create the destination temporary table if necessary
1323
if( eDest==SRT_TempTable ){
1324
sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
1328
/* Generate code for the left and right SELECT statements.
1332
if( p->pOrderBy==0 ){
1333
pPrior->nLimit = p->nLimit;
1334
pPrior->nOffset = p->nOffset;
1335
rc = sqliteSelect(pParse, pPrior, eDest, iParm, 0, 0, 0);
1338
p->iLimit = pPrior->iLimit;
1339
p->iOffset = pPrior->iOffset;
1342
rc = sqliteSelect(pParse, p, eDest, iParm, 0, 0, 0);
1347
/* For UNION ALL ... ORDER BY fall through to the next case */
1351
int unionTab; /* Cursor number of the temporary table holding result */
1352
int op; /* One of the SRT_ operations to apply to self */
1353
int priorOp; /* The SRT_ operation to apply to prior selects */
1354
int nLimit, nOffset; /* Saved values of p->nLimit and p->nOffset */
1355
ExprList *pOrderBy; /* The ORDER BY clause for the right SELECT */
1357
priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union;
1358
if( eDest==priorOp && p->pOrderBy==0 && p->nLimit<0 && p->nOffset==0 ){
1359
/* We can reuse a temporary table generated by a SELECT to our
1364
/* We will need to create our own temporary table to hold the
1365
** intermediate results.
1367
unionTab = pParse->nTab++;
1369
&& matchOrderbyToColumn(pParse, p, p->pOrderBy, unionTab, 1) ){
1372
if( p->op!=TK_ALL ){
1373
sqliteVdbeAddOp(v, OP_OpenTemp, unionTab, 1);
1374
sqliteVdbeAddOp(v, OP_KeyAsData, unionTab, 1);
1376
sqliteVdbeAddOp(v, OP_OpenTemp, unionTab, 0);
1380
/* Code the SELECT statements to our left
1382
rc = sqliteSelect(pParse, pPrior, priorOp, unionTab, 0, 0, 0);
1385
/* Code the current SELECT statement
1388
case TK_EXCEPT: op = SRT_Except; break;
1389
case TK_UNION: op = SRT_Union; break;
1390
case TK_ALL: op = SRT_Table; break;
1393
pOrderBy = p->pOrderBy;
1397
nOffset = p->nOffset;
1399
rc = sqliteSelect(pParse, p, op, unionTab, 0, 0, 0);
1401
p->pOrderBy = pOrderBy;
1403
p->nOffset = nOffset;
1406
/* Convert the data in the temporary table into whatever form
1407
** it is that we currently need.
1409
if( eDest!=priorOp || unionTab!=iParm ){
1410
int iCont, iBreak, iStart;
1411
assert( p->pEList );
1412
if( eDest==SRT_Callback ){
1413
generateColumnNames(pParse, 0, p->pEList);
1414
generateColumnTypes(pParse, p->pSrc, p->pEList);
1416
iBreak = sqliteVdbeMakeLabel(v);
1417
iCont = sqliteVdbeMakeLabel(v);
1418
sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak);
1419
computeLimitRegisters(pParse, p);
1420
iStart = sqliteVdbeCurrentAddr(v);
1421
multiSelectSortOrder(p, p->pOrderBy);
1422
rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
1423
p->pOrderBy, -1, eDest, iParm,
1426
sqliteVdbeResolveLabel(v, iCont);
1427
sqliteVdbeAddOp(v, OP_Next, unionTab, iStart);
1428
sqliteVdbeResolveLabel(v, iBreak);
1429
sqliteVdbeAddOp(v, OP_Close, unionTab, 0);
1431
generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
1436
case TK_INTERSECT: {
1438
int iCont, iBreak, iStart;
1439
int nLimit, nOffset;
1441
/* INTERSECT is different from the others since it requires
1442
** two temporary tables. Hence it has its own case. Begin
1443
** by allocating the tables we will need.
1445
tab1 = pParse->nTab++;
1446
tab2 = pParse->nTab++;
1447
if( p->pOrderBy && matchOrderbyToColumn(pParse,p,p->pOrderBy,tab1,1) ){
1450
sqliteVdbeAddOp(v, OP_OpenTemp, tab1, 1);
1451
sqliteVdbeAddOp(v, OP_KeyAsData, tab1, 1);
1453
/* Code the SELECTs to our left into temporary table "tab1".
1455
rc = sqliteSelect(pParse, pPrior, SRT_Union, tab1, 0, 0, 0);
1458
/* Code the current SELECT into temporary table "tab2"
1460
sqliteVdbeAddOp(v, OP_OpenTemp, tab2, 1);
1461
sqliteVdbeAddOp(v, OP_KeyAsData, tab2, 1);
1465
nOffset = p->nOffset;
1467
rc = sqliteSelect(pParse, p, SRT_Union, tab2, 0, 0, 0);
1470
p->nOffset = nOffset;
1473
/* Generate code to take the intersection of the two temporary
1476
assert( p->pEList );
1477
if( eDest==SRT_Callback ){
1478
generateColumnNames(pParse, 0, p->pEList);
1479
generateColumnTypes(pParse, p->pSrc, p->pEList);
1481
iBreak = sqliteVdbeMakeLabel(v);
1482
iCont = sqliteVdbeMakeLabel(v);
1483
sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
1484
computeLimitRegisters(pParse, p);
1485
iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
1486
sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);
1487
multiSelectSortOrder(p, p->pOrderBy);
1488
rc = selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
1489
p->pOrderBy, -1, eDest, iParm,
1492
sqliteVdbeResolveLabel(v, iCont);
1493
sqliteVdbeAddOp(v, OP_Next, tab1, iStart);
1494
sqliteVdbeResolveLabel(v, iBreak);
1495
sqliteVdbeAddOp(v, OP_Close, tab2, 0);
1496
sqliteVdbeAddOp(v, OP_Close, tab1, 0);
1498
generateSortTail(p, v, p->pEList->nExpr, eDest, iParm);
1503
assert( p->pEList && pPrior->pEList );
1504
if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
1505
sqliteErrorMsg(pParse, "SELECTs to the left and right of %s"
1506
" do not have the same number of result columns", selectOpName(p->op));
1513
** Scan through the expression pExpr. Replace every reference to
1514
** a column in table number iTable with a copy of the iColumn-th
1515
** entry in pEList. (But leave references to the ROWID column
1518
** This routine is part of the flattening procedure. A subquery
1519
** whose result set is defined by pEList appears as entry in the
1520
** FROM clause of a SELECT such that the VDBE cursor assigned to that
1521
** FORM clause entry is iTable. This routine make the necessary
1522
** changes to pExpr so that it refers directly to the source table
1523
** of the subquery rather the result set of the subquery.
1525
static void substExprList(ExprList*,int,ExprList*); /* Forward Decl */
1526
static void substExpr(Expr *pExpr, int iTable, ExprList *pEList){
1527
if( pExpr==0 ) return;
1528
if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable ){
1529
if( pExpr->iColumn<0 ){
1530
pExpr->op = TK_NULL;
1533
assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
1534
assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
1535
pNew = pEList->a[pExpr->iColumn].pExpr;
1537
pExpr->op = pNew->op;
1538
pExpr->dataType = pNew->dataType;
1539
assert( pExpr->pLeft==0 );
1540
pExpr->pLeft = sqliteExprDup(pNew->pLeft);
1541
assert( pExpr->pRight==0 );
1542
pExpr->pRight = sqliteExprDup(pNew->pRight);
1543
assert( pExpr->pList==0 );
1544
pExpr->pList = sqliteExprListDup(pNew->pList);
1545
pExpr->iTable = pNew->iTable;
1546
pExpr->iColumn = pNew->iColumn;
1547
pExpr->iAgg = pNew->iAgg;
1548
sqliteTokenCopy(&pExpr->token, &pNew->token);
1549
sqliteTokenCopy(&pExpr->span, &pNew->span);
1552
substExpr(pExpr->pLeft, iTable, pEList);
1553
substExpr(pExpr->pRight, iTable, pEList);
1554
substExprList(pExpr->pList, iTable, pEList);
1558
substExprList(ExprList *pList, int iTable, ExprList *pEList){
1560
if( pList==0 ) return;
1561
for(i=0; i<pList->nExpr; i++){
1562
substExpr(pList->a[i].pExpr, iTable, pEList);
1567
** This routine attempts to flatten subqueries in order to speed
1568
** execution. It returns 1 if it makes changes and 0 if no flattening
1571
** To understand the concept of flattening, consider the following
1574
** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
1576
** The default way of implementing this query is to execute the
1577
** subquery first and store the results in a temporary table, then
1578
** run the outer query on that temporary table. This requires two
1579
** passes over the data. Furthermore, because the temporary table
1580
** has no indices, the WHERE clause on the outer query cannot be
1583
** This routine attempts to rewrite queries such as the above into
1584
** a single flat select, like this:
1586
** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
1588
** The code generated for this simpification gives the same result
1589
** but only has to scan the data once. And because indices might
1590
** exist on the table t1, a complete scan of the data might be
1593
** Flattening is only attempted if all of the following are true:
1595
** (1) The subquery and the outer query do not both use aggregates.
1597
** (2) The subquery is not an aggregate or the outer query is not a join.
1599
** (3) The subquery is not the right operand of a left outer join, or
1600
** the subquery is not itself a join. (Ticket #306)
1602
** (4) The subquery is not DISTINCT or the outer query is not a join.
1604
** (5) The subquery is not DISTINCT or the outer query does not use
1607
** (6) The subquery does not use aggregates or the outer query is not
1610
** (7) The subquery has a FROM clause.
1612
** (8) The subquery does not use LIMIT or the outer query is not a join.
1614
** (9) The subquery does not use LIMIT or the outer query does not use
1617
** (10) The subquery does not use aggregates or the outer query does not
1620
** (11) The subquery and the outer query do not both have ORDER BY clauses.
1622
** (12) The subquery is not the right term of a LEFT OUTER JOIN or the
1623
** subquery has no WHERE clause. (added by ticket #350)
1625
** In this routine, the "p" parameter is a pointer to the outer query.
1626
** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
1627
** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
1629
** If flattening is not attempted, this routine is a no-op and returns 0.
1630
** If flattening is attempted this routine returns 1.
1632
** All of the expression analysis must occur on both the outer query and
1633
** the subquery before this routine runs.
1635
static int flattenSubquery(
1636
Parse *pParse, /* The parsing context */
1637
Select *p, /* The parent or outer SELECT statement */
1638
int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
1639
int isAgg, /* True if outer SELECT uses aggregate functions */
1640
int subqueryIsAgg /* True if the subquery uses aggregate functions */
1642
Select *pSub; /* The inner query or "subquery" */
1643
SrcList *pSrc; /* The FROM clause of the outer query */
1644
SrcList *pSubSrc; /* The FROM clause of the subquery */
1645
ExprList *pList; /* The result set of the outer query */
1646
int iParent; /* VDBE cursor number of the pSub result set temp table */
1650
/* Check to see if flattening is permitted. Return 0 if not.
1652
if( p==0 ) return 0;
1654
assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
1655
pSub = pSrc->a[iFrom].pSelect;
1657
if( isAgg && subqueryIsAgg ) return 0;
1658
if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;
1659
pSubSrc = pSub->pSrc;
1661
if( pSubSrc->nSrc==0 ) return 0;
1662
if( (pSub->isDistinct || pSub->nLimit>=0) && (pSrc->nSrc>1 || isAgg) ){
1665
if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0;
1666
if( p->pOrderBy && pSub->pOrderBy ) return 0;
1668
/* Restriction 3: If the subquery is a join, make sure the subquery is
1669
** not used as the right operand of an outer join. Examples of why this
1672
** t1 LEFT OUTER JOIN (t2 JOIN t3)
1674
** If we flatten the above, we would get
1676
** (t1 LEFT OUTER JOIN t2) JOIN t3
1678
** which is not at all the same thing.
1680
if( pSubSrc->nSrc>1 && iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0 ){
1684
/* Restriction 12: If the subquery is the right operand of a left outer
1685
** join, make sure the subquery has no WHERE clause.
1686
** An examples of why this is not allowed:
1688
** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
1690
** If we flatten the above, we would get
1692
** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
1694
** But the t2.x>0 test will always fail on a NULL row of t2, which
1695
** effectively converts the OUTER JOIN into an INNER JOIN.
1697
if( iFrom>0 && (pSrc->a[iFrom-1].jointype & JT_OUTER)!=0
1698
&& pSub->pWhere!=0 ){
1702
/* If we reach this point, it means flattening is permitted for the
1703
** iFrom-th entry of the FROM clause in the outer query.
1706
/* Move all of the FROM elements of the subquery into the
1707
** the FROM clause of the outer query. Before doing this, remember
1708
** the cursor number for the original outer query FROM element in
1709
** iParent. The iParent cursor will never be used. Subsequent code
1710
** will scan expressions looking for iParent references and replace
1711
** those references with expressions that resolve to the subquery FROM
1712
** elements we are now copying in.
1714
iParent = pSrc->a[iFrom].iCursor;
1716
int nSubSrc = pSubSrc->nSrc;
1717
int jointype = pSrc->a[iFrom].jointype;
1719
if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){
1720
sqliteDeleteTable(0, pSrc->a[iFrom].pTab);
1722
sqliteFree(pSrc->a[iFrom].zDatabase);
1723
sqliteFree(pSrc->a[iFrom].zName);
1724
sqliteFree(pSrc->a[iFrom].zAlias);
1726
int extra = nSubSrc - 1;
1727
for(i=1; i<nSubSrc; i++){
1728
pSrc = sqliteSrcListAppend(pSrc, 0, 0);
1731
for(i=pSrc->nSrc-1; i-extra>=iFrom; i--){
1732
pSrc->a[i] = pSrc->a[i-extra];
1735
for(i=0; i<nSubSrc; i++){
1736
pSrc->a[i+iFrom] = pSubSrc->a[i];
1737
memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
1739
pSrc->a[iFrom+nSubSrc-1].jointype = jointype;
1742
/* Now begin substituting subquery result set expressions for
1743
** references to the iParent in the outer query.
1747
** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
1748
** \ \_____________ subquery __________/ /
1749
** \_____________________ outer query ______________________________/
1751
** We look at every expression in the outer query and every place we see
1752
** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
1754
substExprList(p->pEList, iParent, pSub->pEList);
1756
for(i=0; i<pList->nExpr; i++){
1758
if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){
1759
pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n);
1763
substExprList(p->pGroupBy, iParent, pSub->pEList);
1764
substExpr(p->pHaving, iParent, pSub->pEList);
1766
if( pSub->pOrderBy ){
1767
assert( p->pOrderBy==0 );
1768
p->pOrderBy = pSub->pOrderBy;
1770
}else if( p->pOrderBy ){
1771
substExprList(p->pOrderBy, iParent, pSub->pEList);
1774
pWhere = sqliteExprDup(pSub->pWhere);
1778
if( subqueryIsAgg ){
1779
assert( p->pHaving==0 );
1780
p->pHaving = p->pWhere;
1782
substExpr(p->pHaving, iParent, pSub->pEList);
1783
if( pSub->pHaving ){
1784
Expr *pHaving = sqliteExprDup(pSub->pHaving);
1786
p->pHaving = sqliteExpr(TK_AND, p->pHaving, pHaving, 0);
1788
p->pHaving = pHaving;
1791
assert( p->pGroupBy==0 );
1792
p->pGroupBy = sqliteExprListDup(pSub->pGroupBy);
1793
}else if( p->pWhere==0 ){
1796
substExpr(p->pWhere, iParent, pSub->pEList);
1798
p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0);
1802
/* The flattened query is distinct if either the inner or the
1803
** outer query is distinct.
1805
p->isDistinct = p->isDistinct || pSub->isDistinct;
1807
/* Transfer the limit expression from the subquery to the outer
1810
if( pSub->nLimit>=0 ){
1812
p->nLimit = pSub->nLimit;
1813
}else if( p->nLimit+p->nOffset > pSub->nLimit+pSub->nOffset ){
1814
p->nLimit = pSub->nLimit + pSub->nOffset - p->nOffset;
1817
p->nOffset += pSub->nOffset;
1819
/* Finially, delete what is left of the subquery and return
1822
sqliteSelectDelete(pSub);
1827
** Analyze the SELECT statement passed in as an argument to see if it
1828
** is a simple min() or max() query. If it is and this query can be
1829
** satisfied using a single seek to the beginning or end of an index,
1830
** then generate the code for this SELECT and return 1. If this is not a
1831
** simple min() or max() query, then return 0;
1833
** A simply min() or max() query looks like this:
1835
** SELECT min(a) FROM table;
1836
** SELECT max(a) FROM table;
1838
** The query may have only a single table in its FROM argument. There
1839
** can be no GROUP BY or HAVING or WHERE clauses. The result set must
1840
** be the min() or max() of a single column of the table. The column
1841
** in the min() or max() function must be indexed.
1843
** The parameters to this routine are the same as for sqliteSelect().
1844
** See the header comment on that routine for additional information.
1846
static int simpleMinMaxQuery(Parse *pParse, Select *p, int eDest, int iParm){
1855
ExprList *pEList, *pList, eList;
1856
struct ExprList_item eListItem;
1860
/* Check to see if this query is a simple min() or max() query. Return
1861
** zero if it is not.
1863
if( p->pGroupBy || p->pHaving || p->pWhere ) return 0;
1865
if( pSrc->nSrc!=1 ) return 0;
1867
if( pEList->nExpr!=1 ) return 0;
1868
pExpr = pEList->a[0].pExpr;
1869
if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
1870
pList = pExpr->pList;
1871
if( pList==0 || pList->nExpr!=1 ) return 0;
1872
if( pExpr->token.n!=3 ) return 0;
1873
if( sqliteStrNICmp(pExpr->token.z,"min",3)==0 ){
1875
}else if( sqliteStrNICmp(pExpr->token.z,"max",3)==0 ){
1880
pExpr = pList->a[0].pExpr;
1881
if( pExpr->op!=TK_COLUMN ) return 0;
1882
iCol = pExpr->iColumn;
1883
pTab = pSrc->a[0].pTab;
1885
/* If we get to here, it means the query is of the correct form.
1886
** Check to make sure we have an index and make pIdx point to the
1887
** appropriate index. If the min() or max() is on an INTEGER PRIMARY
1888
** key column, no index is necessary so set pIdx to NULL. If no
1889
** usable index is found, return 0.
1894
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
1895
assert( pIdx->nColumn>=1 );
1896
if( pIdx->aiColumn[0]==iCol ) break;
1898
if( pIdx==0 ) return 0;
1901
/* Identify column types if we will be using the callback. This
1902
** step is skipped if the output is going to a table or a memory cell.
1903
** The column names have already been generated in the calling function.
1905
v = sqliteGetVdbe(pParse);
1906
if( v==0 ) return 0;
1907
if( eDest==SRT_Callback ){
1908
generateColumnTypes(pParse, p->pSrc, p->pEList);
1911
/* If the output is destined for a temporary table, open that table.
1913
if( eDest==SRT_TempTable ){
1914
sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
1917
/* Generating code to find the min or the max. Basically all we have
1918
** to do is find the first or the last entry in the chosen index. If
1919
** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
1920
** or last entry in the main table.
1922
sqliteCodeVerifySchema(pParse, pTab->iDb);
1923
base = pSrc->a[0].iCursor;
1924
computeLimitRegisters(pParse, p);
1925
if( pSrc->a[0].pSelect==0 ){
1926
sqliteVdbeAddOp(v, OP_Integer, pTab->iDb, 0);
1927
sqliteVdbeOp3(v, OP_OpenRead, base, pTab->tnum, pTab->zName, 0);
1929
cont = sqliteVdbeMakeLabel(v);
1931
sqliteVdbeAddOp(v, seekOp, base, 0);
1933
sqliteVdbeAddOp(v, OP_Integer, pIdx->iDb, 0);
1934
sqliteVdbeOp3(v, OP_OpenRead, base+1, pIdx->tnum, pIdx->zName, P3_STATIC);
1935
if( seekOp==OP_Rewind ){
1936
sqliteVdbeAddOp(v, OP_String, 0, 0);
1937
sqliteVdbeAddOp(v, OP_MakeKey, 1, 0);
1938
sqliteVdbeAddOp(v, OP_IncrKey, 0, 0);
1941
sqliteVdbeAddOp(v, seekOp, base+1, 0);
1942
sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
1943
sqliteVdbeAddOp(v, OP_Close, base+1, 0);
1944
sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
1947
memset(&eListItem, 0, sizeof(eListItem));
1948
eList.a = &eListItem;
1949
eList.a[0].pExpr = pExpr;
1950
selectInnerLoop(pParse, p, &eList, 0, 0, 0, -1, eDest, iParm, cont, cont);
1951
sqliteVdbeResolveLabel(v, cont);
1952
sqliteVdbeAddOp(v, OP_Close, base, 0);
1958
** Generate code for the given SELECT statement.
1960
** The results are distributed in various ways depending on the
1961
** value of eDest and iParm.
1963
** eDest Value Result
1964
** ------------ -------------------------------------------
1965
** SRT_Callback Invoke the callback for each row of the result.
1967
** SRT_Mem Store first result in memory cell iParm
1969
** SRT_Set Store results as keys of a table with cursor iParm
1971
** SRT_Union Store results as a key in a temporary table iParm
1973
** SRT_Except Remove results from the temporary table iParm.
1975
** SRT_Table Store results in temporary table iParm
1977
** The table above is incomplete. Additional eDist value have be added
1978
** since this comment was written. See the selectInnerLoop() function for
1979
** a complete listing of the allowed values of eDest and their meanings.
1981
** This routine returns the number of errors. If any errors are
1982
** encountered, then an appropriate error message is left in
1985
** This routine does NOT free the Select structure passed in. The
1986
** calling function needs to do that.
1988
** The pParent, parentTab, and *pParentAgg fields are filled in if this
1989
** SELECT is a subquery. This routine may try to combine this SELECT
1990
** with its parent to form a single flat query. In so doing, it might
1991
** change the parent query from a non-aggregate to an aggregate query.
1992
** For that reason, the pParentAgg flag is passed as a pointer, so it
1995
** Example 1: The meaning of the pParent parameter.
1997
** SELECT * FROM t1 JOIN (SELECT x, count(*) FROM t2) JOIN t3;
1998
** \ \_______ subquery _______/ /
2000
** \____________________ outer query ___________________/
2002
** This routine is called for the outer query first. For that call,
2003
** pParent will be NULL. During the processing of the outer query, this
2004
** routine is called recursively to handle the subquery. For the recursive
2005
** call, pParent will point to the outer query. Because the subquery is
2006
** the second element in a three-way join, the parentTab parameter will
2007
** be 1 (the 2nd value of a 0-indexed array.)
2010
Parse *pParse, /* The parser context */
2011
Select *p, /* The SELECT statement being coded. */
2012
int eDest, /* How to dispose of the results */
2013
int iParm, /* A parameter used by the eDest disposal method */
2014
Select *pParent, /* Another SELECT for which this is a sub-query */
2015
int parentTab, /* Index in pParent->pSrc of this query */
2016
int *pParentAgg /* True if pParent uses aggregate functions */
2021
int isAgg = 0; /* True for select lists like "count(*)" */
2022
ExprList *pEList; /* List of columns to extract. */
2023
SrcList *pTabList; /* List of tables to select from */
2024
Expr *pWhere; /* The WHERE clause. May be NULL */
2025
ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */
2026
ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */
2027
Expr *pHaving; /* The HAVING clause. May be NULL */
2028
int isDistinct; /* True if the DISTINCT keyword is present */
2029
int distinct; /* Table to use for the distinct set */
2030
int rc = 1; /* Value to return from this function */
2032
if( sqlite_malloc_failed || pParse->nErr || p==0 ) return 1;
2033
if( sqliteAuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
2035
/* If there is are a sequence of queries, do the earlier ones first.
2038
return multiSelect(pParse, p, eDest, iParm);
2041
/* Make local copies of the parameters for this query.
2045
pOrderBy = p->pOrderBy;
2046
pGroupBy = p->pGroupBy;
2047
pHaving = p->pHaving;
2048
isDistinct = p->isDistinct;
2050
/* Allocate VDBE cursors for each table in the FROM clause
2052
sqliteSrcListAssignCursors(pParse, pTabList);
2055
** Do not even attempt to generate any code if we have already seen
2056
** errors before this routine starts.
2058
if( pParse->nErr>0 ) goto select_end;
2060
/* Expand any "*" terms in the result set. (For example the "*" in
2061
** "SELECT * FROM t1") The fillInColumnlist() routine also does some
2062
** other housekeeping - see the header comment for details.
2064
if( fillInColumnList(pParse, p) ){
2069
if( pEList==0 ) goto select_end;
2071
/* If writing to memory or generating a set
2072
** only a single column may be output.
2074
if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
2075
sqliteErrorMsg(pParse, "only a single result allowed for "
2076
"a SELECT that is part of an expression");
2080
/* ORDER BY is ignored for some destinations.
2092
/* At this point, we should have allocated all the cursors that we
2093
** need to handle subquerys and temporary tables.
2095
** Resolve the column names and do a semantics check on all the expressions.
2097
for(i=0; i<pEList->nExpr; i++){
2098
if( sqliteExprResolveIds(pParse, pTabList, 0, pEList->a[i].pExpr) ){
2101
if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
2106
if( sqliteExprResolveIds(pParse, pTabList, pEList, pWhere) ){
2109
if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
2115
sqliteErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
2118
if( sqliteExprResolveIds(pParse, pTabList, pEList, pHaving) ){
2121
if( sqliteExprCheck(pParse, pHaving, 1, &isAgg) ){
2126
for(i=0; i<pOrderBy->nExpr; i++){
2128
Expr *pE = pOrderBy->a[i].pExpr;
2129
if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
2130
sqliteExprDelete(pE);
2131
pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
2133
if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
2136
if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
2139
if( sqliteExprIsConstant(pE) ){
2140
if( sqliteExprIsInteger(pE, &iCol)==0 ){
2141
sqliteErrorMsg(pParse,
2142
"ORDER BY terms must not be non-integer constants");
2144
}else if( iCol<=0 || iCol>pEList->nExpr ){
2145
sqliteErrorMsg(pParse,
2146
"ORDER BY column number %d out of range - should be "
2147
"between 1 and %d", iCol, pEList->nExpr);
2154
for(i=0; i<pGroupBy->nExpr; i++){
2156
Expr *pE = pGroupBy->a[i].pExpr;
2157
if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
2158
sqliteExprDelete(pE);
2159
pE = pGroupBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
2161
if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
2164
if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
2167
if( sqliteExprIsConstant(pE) ){
2168
if( sqliteExprIsInteger(pE, &iCol)==0 ){
2169
sqliteErrorMsg(pParse,
2170
"GROUP BY terms must not be non-integer constants");
2172
}else if( iCol<=0 || iCol>pEList->nExpr ){
2173
sqliteErrorMsg(pParse,
2174
"GROUP BY column number %d out of range - should be "
2175
"between 1 and %d", iCol, pEList->nExpr);
2182
/* Begin generating code.
2184
v = sqliteGetVdbe(pParse);
2185
if( v==0 ) goto select_end;
2187
/* Identify column names if we will be using them in a callback. This
2188
** step is skipped if the output is going to some other destination.
2190
if( eDest==SRT_Callback ){
2191
generateColumnNames(pParse, pTabList, pEList);
2194
/* Generate code for all sub-queries in the FROM clause
2196
for(i=0; i<pTabList->nSrc; i++){
2197
const char *zSavedAuthContext;
2198
int needRestoreContext;
2200
if( pTabList->a[i].pSelect==0 ) continue;
2201
if( pTabList->a[i].zName!=0 ){
2202
zSavedAuthContext = pParse->zAuthContext;
2203
pParse->zAuthContext = pTabList->a[i].zName;
2204
needRestoreContext = 1;
2206
needRestoreContext = 0;
2208
sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_TempTable,
2209
pTabList->a[i].iCursor, p, i, &isAgg);
2210
if( needRestoreContext ){
2211
pParse->zAuthContext = zSavedAuthContext;
2215
if( eDest!=SRT_Union && eDest!=SRT_Except && eDest!=SRT_Discard ){
2216
pOrderBy = p->pOrderBy;
2218
pGroupBy = p->pGroupBy;
2219
pHaving = p->pHaving;
2220
isDistinct = p->isDistinct;
2223
/* Check for the special case of a min() or max() function by itself
2224
** in the result set.
2226
if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){
2231
/* Check to see if this is a subquery that can be "flattened" into its parent.
2232
** If flattening is a possiblity, do so and return immediately.
2234
if( pParent && pParentAgg &&
2235
flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){
2236
if( isAgg ) *pParentAgg = 1;
2242
computeLimitRegisters(pParse, p);
2244
/* Identify column types if we will be using a callback. This
2245
** step is skipped if the output is going to a destination other
2248
** We have to do this separately from the creation of column names
2249
** above because if the pTabList contains views then they will not
2250
** have been resolved and we will not know the column types until
2253
if( eDest==SRT_Callback ){
2254
generateColumnTypes(pParse, pTabList, pEList);
2257
/* If the output is destined for a temporary table, open that table.
2259
if( eDest==SRT_TempTable ){
2260
sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
2263
/* Do an analysis of aggregate expressions.
2265
sqliteAggregateInfoReset(pParse);
2266
if( isAgg || pGroupBy ){
2267
assert( pParse->nAgg==0 );
2269
for(i=0; i<pEList->nExpr; i++){
2270
if( sqliteExprAnalyzeAggregates(pParse, pEList->a[i].pExpr) ){
2275
for(i=0; i<pGroupBy->nExpr; i++){
2276
if( sqliteExprAnalyzeAggregates(pParse, pGroupBy->a[i].pExpr) ){
2281
if( pHaving && sqliteExprAnalyzeAggregates(pParse, pHaving) ){
2285
for(i=0; i<pOrderBy->nExpr; i++){
2286
if( sqliteExprAnalyzeAggregates(pParse, pOrderBy->a[i].pExpr) ){
2293
/* Reset the aggregator
2296
sqliteVdbeAddOp(v, OP_AggReset, 0, pParse->nAgg);
2297
for(i=0; i<pParse->nAgg; i++){
2299
if( (pFunc = pParse->aAgg[i].pFunc)!=0 && pFunc->xFinalize!=0 ){
2300
sqliteVdbeOp3(v, OP_AggInit, 0, i, (char*)pFunc, P3_POINTER);
2304
sqliteVdbeAddOp(v, OP_String, 0, 0);
2305
sqliteVdbeAddOp(v, OP_AggFocus, 0, 0);
2309
/* Initialize the memory cell to NULL
2311
if( eDest==SRT_Mem ){
2312
sqliteVdbeAddOp(v, OP_String, 0, 0);
2313
sqliteVdbeAddOp(v, OP_MemStore, iParm, 1);
2316
/* Open a temporary table to use for the distinct set.
2319
distinct = pParse->nTab++;
2320
sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1);
2325
/* Begin the database scan
2327
pWInfo = sqliteWhereBegin(pParse, pTabList, pWhere, 0,
2328
pGroupBy ? 0 : &pOrderBy);
2329
if( pWInfo==0 ) goto select_end;
2331
/* Use the standard inner loop if we are not dealing with
2335
if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
2336
iParm, pWInfo->iContinue, pWInfo->iBreak) ){
2341
/* If we are dealing with aggregates, then do the special aggregate
2348
for(i=0; i<pGroupBy->nExpr; i++){
2349
sqliteExprCode(pParse, pGroupBy->a[i].pExpr);
2351
sqliteVdbeAddOp(v, OP_MakeKey, pGroupBy->nExpr, 0);
2352
if( pParse->db->file_format>=4 ) sqliteAddKeyType(v, pGroupBy);
2353
lbl1 = sqliteVdbeMakeLabel(v);
2354
sqliteVdbeAddOp(v, OP_AggFocus, 0, lbl1);
2355
for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
2356
if( pAgg->isAgg ) continue;
2357
sqliteExprCode(pParse, pAgg->pExpr);
2358
sqliteVdbeAddOp(v, OP_AggSet, 0, i);
2360
sqliteVdbeResolveLabel(v, lbl1);
2362
for(i=0, pAgg=pParse->aAgg; i<pParse->nAgg; i++, pAgg++){
2366
if( !pAgg->isAgg ) continue;
2367
assert( pAgg->pFunc!=0 );
2368
assert( pAgg->pFunc->xStep!=0 );
2372
assert( pE->op==TK_AGG_FUNCTION );
2373
nExpr = sqliteExprCodeExprList(pParse, pE->pList, pDef->includeTypes);
2374
sqliteVdbeAddOp(v, OP_Integer, i, 0);
2375
sqliteVdbeOp3(v, OP_AggFunc, 0, nExpr, (char*)pDef, P3_POINTER);
2379
/* End the database scan loop.
2381
sqliteWhereEnd(pWInfo);
2383
/* If we are processing aggregates, we need to set up a second loop
2384
** over all of the aggregate values and process them.
2387
int endagg = sqliteVdbeMakeLabel(v);
2389
startagg = sqliteVdbeAddOp(v, OP_AggNext, 0, endagg);
2392
sqliteExprIfFalse(pParse, pHaving, startagg, 1);
2394
if( selectInnerLoop(pParse, p, pEList, 0, 0, pOrderBy, distinct, eDest,
2395
iParm, startagg, endagg) ){
2398
sqliteVdbeAddOp(v, OP_Goto, 0, startagg);
2399
sqliteVdbeResolveLabel(v, endagg);
2400
sqliteVdbeAddOp(v, OP_Noop, 0, 0);
2404
/* If there is an ORDER BY clause, then we need to sort the results
2405
** and send them to the callback one by one.
2408
generateSortTail(p, v, pEList->nExpr, eDest, iParm);
2411
/* If this was a subquery, we have now converted the subquery into a
2412
** temporary table. So delete the subquery structure from the parent
2413
** to prevent this subquery from being evaluated again and to force the
2414
** the use of the temporary table.
2417
assert( pParent->pSrc->nSrc>parentTab );
2418
assert( pParent->pSrc->a[parentTab].pSelect==p );
2419
sqliteSelectDelete(p);
2420
pParent->pSrc->a[parentTab].pSelect = 0;
2423
/* The SELECT was successfully coded. Set the return code to 0
2424
** to indicate no errors.
2428
/* Control jumps to here if an error is encountered above, or upon
2429
** successful coding of the SELECT.
2432
sqliteAggregateInfoReset(pParse);
added
removed
core/libs/3rdparty/sqlite2/select.c
