wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 #define SQLITE_MAX_EXPR_DEPTH
2 using System;
3 using System.Diagnostics;
4 using System.Text;
5  
6 using i16 = System.Int16;
7 using u8 = System.Byte;
8 using u16 = System.UInt16;
9 using u32 = System.UInt32;
10  
11 using Pgno = System.UInt32;
12  
13 namespace Community.CsharpSqlite
14 {
15 public partial class Sqlite3
16 {
17 /*
18 ** 2001 September 15
19 **
20 ** The author disclaims copyright to this source code. In place of
21 ** a legal notice, here is a blessing:
22 **
23 ** May you do good and not evil.
24 ** May you find forgiveness for yourself and forgive others.
25 ** May you share freely, never taking more than you give.
26 **
27 *************************************************************************
28 ** This file contains C code routines that are called by the parser
29 ** to handle SELECT statements in SQLite.
30 *************************************************************************
31 ** Included in SQLite3 port to C#-SQLite; 2008 Noah B Hart
32 ** C#-SQLite is an independent reimplementation of the SQLite software library
33 **
34 ** SQLITE_SOURCE_ID: 2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c936272862f32f2
35 **
36 *************************************************************************
37 */
38 //#include "sqliteInt.h"
39  
40  
41 /*
42 ** Delete all the content of a Select structure but do not deallocate
43 ** the select structure itself.
44 */
45 static void clearSelect( sqlite3 db, Select p )
46 {
47 sqlite3ExprListDelete( db, ref p.pEList );
48 sqlite3SrcListDelete( db, ref p.pSrc );
49 sqlite3ExprDelete( db, ref p.pWhere );
50 sqlite3ExprListDelete( db, ref p.pGroupBy );
51 sqlite3ExprDelete( db, ref p.pHaving );
52 sqlite3ExprListDelete( db, ref p.pOrderBy );
53 sqlite3SelectDelete( db, ref p.pPrior );
54 sqlite3ExprDelete( db, ref p.pLimit );
55 sqlite3ExprDelete( db, ref p.pOffset );
56 }
57  
58 /*
59 ** Initialize a SelectDest structure.
60 */
61 static void sqlite3SelectDestInit( SelectDest pDest, int eDest, int iParm )
62 {
63 pDest.eDest = (u8)eDest;
64 pDest.iParm = iParm;
65 pDest.affinity = '\0';
66 pDest.iMem = 0;
67 pDest.nMem = 0;
68 }
69  
70  
71 /*
72 ** Allocate a new Select structure and return a pointer to that
73 ** structure.
74 */
75 // OVERLOADS, so I don't need to rewrite parse.c
76 static Select sqlite3SelectNew( Parse pParse, int null_2, SrcList pSrc, int null_4, int null_5, int null_6, int null_7, int isDistinct, int null_9, int null_10 )
77 {
78 return sqlite3SelectNew( pParse, null, pSrc, null, null, null, null, isDistinct, null, null );
79 }
80 static Select sqlite3SelectNew(
81 Parse pParse, /* Parsing context */
82 ExprList pEList, /* which columns to include in the result */
83 SrcList pSrc, /* the FROM clause -- which tables to scan */
84 Expr pWhere, /* the WHERE clause */
85 ExprList pGroupBy, /* the GROUP BY clause */
86 Expr pHaving, /* the HAVING clause */
87 ExprList pOrderBy, /* the ORDER BY clause */
88 int isDistinct, /* true if the DISTINCT keyword is present */
89 Expr pLimit, /* LIMIT value. NULL means not used */
90 Expr pOffset /* OFFSET value. NULL means no offset */
91 )
92 {
93 Select pNew;
94 // Select standin;
95 sqlite3 db = pParse.db;
96 pNew = new Select();//sqlite3DbMallocZero(db, sizeof(*pNew) );
97 Debug.Assert( //db.mallocFailed != 0 ||
98 null == pOffset || pLimit != null ); /* OFFSET implies LIMIT */
99 //if( pNew==null ){
100 // pNew = standin;
101 // memset(pNew, 0, sizeof(*pNew));
102 //}
103 if ( pEList == null )
104 {
105 pEList = sqlite3ExprListAppend( pParse, null, sqlite3Expr( db, TK_ALL, null ) );
106 }
107 pNew.pEList = pEList;
108 pNew.pSrc = pSrc;
109 pNew.pWhere = pWhere;
110 pNew.pGroupBy = pGroupBy;
111 pNew.pHaving = pHaving;
112 pNew.pOrderBy = pOrderBy;
113 pNew.selFlags = (u16)( isDistinct != 0 ? SF_Distinct : 0 );
114 pNew.op = TK_SELECT;
115 pNew.pLimit = pLimit;
116 pNew.pOffset = pOffset;
117 Debug.Assert( pOffset == null || pLimit != null );
118 pNew.addrOpenEphm[0] = -1;
119 pNew.addrOpenEphm[1] = -1;
120 pNew.addrOpenEphm[2] = -1;
121 //if ( db.mallocFailed != 0 )
122 //{
123 // clearSelect( db, pNew );
124 // //if ( pNew != standin ) sqlite3DbFree( db, ref pNew );
125 // pNew = null;
126 //}
127 return pNew;
128 }
129  
130 /*
131 ** Delete the given Select structure and all of its substructures.
132 */
133 static void sqlite3SelectDelete( sqlite3 db, ref Select p )
134 {
135 if ( p != null )
136 {
137 clearSelect( db, p );
138 sqlite3DbFree( db, ref p );
139 }
140 }
141  
142 /*
143 ** Given 1 to 3 identifiers preceeding the JOIN keyword, determine the
144 ** type of join. Return an integer constant that expresses that type
145 ** in terms of the following bit values:
146 **
147 ** JT_INNER
148 ** JT_CROSS
149 ** JT_OUTER
150 ** JT_NATURAL
151 ** JT_LEFT
152 ** JT_RIGHT
153 **
154 ** A full outer join is the combination of JT_LEFT and JT_RIGHT.
155 **
156 ** If an illegal or unsupported join type is seen, then still return
157 ** a join type, but put an error in the pParse structure.
158 */
159  
160 class Keyword
161 {
162 public u8 i; /* Beginning of keyword text in zKeyText[] */
163 public u8 nChar; /* Length of the keyword in characters */
164 public u8 code; /* Join type mask */
165 public Keyword( u8 i, u8 nChar, u8 code )
166 {
167 this.i = i;
168 this.nChar = nChar;
169 this.code = code;
170 }
171 }
172  
173 // OVERLOADS, so I don't need to rewrite parse.c
174 static int sqlite3JoinType( Parse pParse, Token pA, int null_3, int null_4 )
175 {
176 return sqlite3JoinType( pParse, pA, null, null );
177 }
178 static int sqlite3JoinType( Parse pParse, Token pA, Token pB, int null_4 )
179 {
180 return sqlite3JoinType( pParse, pA, pB, null );
181 }
182 static int sqlite3JoinType( Parse pParse, Token pA, Token pB, Token pC )
183 {
184 int jointype = 0;
185 Token[] apAll = new Token[3];
186 Token p;
187  
188 /* 0123456789 123456789 123456789 123 */
189 string zKeyText = "naturaleftouterightfullinnercross";
190  
191 Keyword[] aKeyword = new Keyword[]{
192 /* natural */ new Keyword( 0, 7, JT_NATURAL ),
193 /* left */ new Keyword( 6, 4, JT_LEFT|JT_OUTER ),
194 /* outer */ new Keyword( 10, 5, JT_OUTER ),
195 /* right */ new Keyword( 14, 5, JT_RIGHT|JT_OUTER ),
196 /* full */ new Keyword( 19, 4, JT_LEFT|JT_RIGHT|JT_OUTER ),
197 /* inner */ new Keyword( 23, 5, JT_INNER ),
198 /* cross */ new Keyword( 28, 5, JT_INNER|JT_CROSS ),
199 };
200 int i, j;
201 apAll[0] = pA;
202 apAll[1] = pB;
203 apAll[2] = pC;
204 for ( i = 0; i < 3 && apAll[i] != null; i++ )
205 {
206 p = apAll[i];
207 for ( j = 0; j < ArraySize( aKeyword ); j++ )
208 {
209 if ( p.n == aKeyword[j].nChar
210 && p.z.StartsWith( zKeyText.Substring( aKeyword[j].i, aKeyword[j].nChar ), StringComparison.OrdinalIgnoreCase ) )
211 {
212 jointype |= aKeyword[j].code;
213 break;
214 }
215 }
216 testcase( j == 0 || j == 1 || j == 2 || j == 3 || j == 4 || j == 5 || j == 6 );
217 if ( j >= ArraySize( aKeyword ) )
218 {
219 jointype |= JT_ERROR;
220 break;
221 }
222 }
223 if (
224 ( jointype & ( JT_INNER | JT_OUTER ) ) == ( JT_INNER | JT_OUTER ) ||
225 ( jointype & JT_ERROR ) != 0
226 )
227 {
228 string zSp = pC == null ? string.Empty : " ";
229 Debug.Assert( pB != null );
230 sqlite3ErrorMsg( pParse, "unknown or unsupported join type: " +
231 "%T %T%s%T", pA, pB, zSp, pC );
232 jointype = JT_INNER;
233 }
234 else if ( ( jointype & JT_OUTER ) != 0
235 && ( jointype & ( JT_LEFT | JT_RIGHT ) ) != JT_LEFT )
236 {
237 sqlite3ErrorMsg( pParse,
238 "RIGHT and FULL OUTER JOINs are not currently supported" );
239 jointype = JT_INNER;
240 }
241 return jointype;
242 }
243  
244 /*
245 ** Return the index of a column in a table. Return -1 if the column
246 ** is not contained in the table.
247 */
248 static int columnIndex( Table pTab, string zCol )
249 {
250 int i;
251 for ( i = 0; i < pTab.nCol; i++ )
252 {
253 if ( pTab.aCol[i].zName.Equals( zCol, StringComparison.OrdinalIgnoreCase ) )
254 return i;
255 }
256 return -1;
257 }
258  
259  
260 /*
261 ** Search the first N tables in pSrc, from left to right, looking for a
262 ** table that has a column named zCol.
263 **
264 ** When found, set *piTab and *piCol to the table index and column index
265 ** of the matching column and return TRUE.
266 **
267 ** If not found, return FALSE.
268 */
269 static int tableAndColumnIndex(
270 SrcList pSrc, /* Array of tables to search */
271 int N, /* Number of tables in pSrc.a[] to search */
272 string zCol, /* Name of the column we are looking for */
273 ref int piTab, /* Write index of pSrc.a[] here */
274 ref int piCol /* Write index of pSrc.a[*piTab].pTab.aCol[] here */
275 )
276 {
277 int i; /* For looping over tables in pSrc */
278 int iCol; /* Index of column matching zCol */
279  
280 Debug.Assert( ( piTab == 0 ) == ( piCol == 0 ) ); /* Both or neither are NULL */
281 for ( i = 0; i < N; i++ )
282 {
283 iCol = columnIndex( pSrc.a[i].pTab, zCol );
284 if ( iCol >= 0 )
285 {
286 //if( piTab )
287 {
288 piTab = i;
289 piCol = iCol;
290 }
291 return 1;
292 }
293 }
294 return 0;
295 }
296  
297 /*
298 ** This function is used to add terms implied by JOIN syntax to the
299 ** WHERE clause expression of a SELECT statement. The new term, which
300 ** is ANDed with the existing WHERE clause, is of the form:
301 **
302 ** (vtab1.col1 = tab2.col2)
303 **
304 ** where tab1 is the iSrc'th table in SrcList pSrc and tab2 is the
305 ** (iSrc+1)'th. Column col1 is column iColLeft of tab1, and col2 is
306 ** column iColRight of tab2.
307 */
308 static void addWhereTerm(
309 Parse pParse, /* Parsing context */
310 SrcList pSrc, /* List of tables in FROM clause */
311 int iLeft, /* Index of first table to join in pSrc */
312 int iColLeft, /* Index of column in first table */
313 int iRight, /* Index of second table in pSrc */
314 int iColRight, /* Index of column in second table */
315 int isOuterJoin, /* True if this is an OUTER join */
316 ref Expr ppWhere /* IN/OUT: The WHERE clause to add to */
317 )
318 {
319 sqlite3 db = pParse.db;
320 Expr pE1;
321 Expr pE2;
322 Expr pEq;
323  
324 Debug.Assert( iLeft < iRight );
325 Debug.Assert( pSrc.nSrc > iRight );
326 Debug.Assert( pSrc.a[iLeft].pTab != null );
327 Debug.Assert( pSrc.a[iRight].pTab != null );
328  
329 pE1 = sqlite3CreateColumnExpr( db, pSrc, iLeft, iColLeft );
330 pE2 = sqlite3CreateColumnExpr( db, pSrc, iRight, iColRight );
331  
332 pEq = sqlite3PExpr( pParse, TK_EQ, pE1, pE2, 0 );
333 if ( pEq != null && isOuterJoin != 0 )
334 {
335 ExprSetProperty( pEq, EP_FromJoin );
336 Debug.Assert( !ExprHasAnyProperty( pEq, EP_TokenOnly | EP_Reduced ) );
337 ExprSetIrreducible( pEq );
338 pEq.iRightJoinTable = (i16)pE2.iTable;
339 }
340 ppWhere = sqlite3ExprAnd( db, ppWhere, pEq );
341 }
342  
343 /*
344 ** Set the EP_FromJoin property on all terms of the given expression.
345 ** And set the Expr.iRightJoinTable to iTable for every term in the
346 ** expression.
347 **
348 ** The EP_FromJoin property is used on terms of an expression to tell
349 ** the LEFT OUTER JOIN processing logic that this term is part of the
350 ** join restriction specified in the ON or USING clause and not a part
351 ** of the more general WHERE clause. These terms are moved over to the
352 ** WHERE clause during join processing but we need to remember that they
353 ** originated in the ON or USING clause.
354 **
355 ** The Expr.iRightJoinTable tells the WHERE clause processing that the
356 ** expression depends on table iRightJoinTable even if that table is not
357 ** explicitly mentioned in the expression. That information is needed
358 ** for cases like this:
359 **
360 ** SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
361 **
362 ** The where clause needs to defer the handling of the t1.x=5
363 ** term until after the t2 loop of the join. In that way, a
364 ** NULL t2 row will be inserted whenever t1.x!=5. If we do not
365 ** defer the handling of t1.x=5, it will be processed immediately
366 ** after the t1 loop and rows with t1.x!=5 will never appear in
367 ** the output, which is incorrect.
368 */
369 static void setJoinExpr( Expr p, int iTable )
370 {
371 while ( p != null )
372 {
373 ExprSetProperty( p, EP_FromJoin );
374 Debug.Assert( !ExprHasAnyProperty( p, EP_TokenOnly | EP_Reduced ) );
375 ExprSetIrreducible( p );
376 p.iRightJoinTable = (i16)iTable;
377 setJoinExpr( p.pLeft, iTable );
378 p = p.pRight;
379 }
380 }
381  
382 /*
383 ** This routine processes the join information for a SELECT statement.
384 ** ON and USING clauses are converted into extra terms of the WHERE clause.
385 ** NATURAL joins also create extra WHERE clause terms.
386 **
387 ** The terms of a FROM clause are contained in the Select.pSrc structure.
388 ** The left most table is the first entry in Select.pSrc. The right-most
389 ** table is the last entry. The join operator is held in the entry to
390 ** the left. Thus entry 0 contains the join operator for the join between
391 ** entries 0 and 1. Any ON or USING clauses associated with the join are
392 ** also attached to the left entry.
393 **
394 ** This routine returns the number of errors encountered.
395 */
396 static int sqliteProcessJoin( Parse pParse, Select p )
397 {
398 SrcList pSrc; /* All tables in the FROM clause */
399 int i;
400 int j; /* Loop counters */
401 SrcList_item pLeft; /* Left table being joined */
402 SrcList_item pRight; /* Right table being joined */
403  
404 pSrc = p.pSrc;
405 //pLeft = pSrc.a[0];
406 //pRight = pLeft[1];
407 for ( i = 0; i < pSrc.nSrc - 1; i++ )
408 {
409 pLeft = pSrc.a[i]; // pLeft ++
410 pRight = pSrc.a[i + 1];//Right++,
411 Table pLeftTab = pLeft.pTab;
412 Table pRightTab = pRight.pTab;
413 bool isOuter;
414  
415 if ( NEVER( pLeftTab == null || pRightTab == null ) )
416 continue;
417 isOuter = ( pRight.jointype & JT_OUTER ) != 0;
418  
419 /* When the NATURAL keyword is present, add WHERE clause terms for
420 ** every column that the two tables have in common.
421 */
422 if ( ( pRight.jointype & JT_NATURAL ) != 0 )
423 {
424 if ( pRight.pOn != null || pRight.pUsing != null )
425 {
426 sqlite3ErrorMsg( pParse, "a NATURAL join may not have " +
427 "an ON or USING clause", string.Empty );
428 return 1;
429 }
430 for ( j = 0; j < pRightTab.nCol; j++ )
431 {
432 string zName; /* Name of column in the right table */
433 int iLeft = 0; /* Matching left table */
434 int iLeftCol = 0; /* Matching column in the left table */
435  
436 zName = pRightTab.aCol[j].zName;
437 ////int iRightCol = columnIndex( pRightTab, zName );
438 if ( tableAndColumnIndex( pSrc, i + 1, zName, ref iLeft, ref iLeftCol ) != 0 )
439 {
440 addWhereTerm( pParse, pSrc, iLeft, iLeftCol, i + 1, j,
441 isOuter ? 1 : 0, ref p.pWhere );
442 }
443 }
444 }
445  
446 /* Disallow both ON and USING clauses in the same join
447 */
448 if ( pRight.pOn != null && pRight.pUsing != null )
449 {
450 sqlite3ErrorMsg( pParse, "cannot have both ON and USING " +
451 "clauses in the same join" );
452 return 1;
453 }
454  
455 /* Add the ON clause to the end of the WHERE clause, connected by
456 ** an AND operator.
457 */
458 if ( pRight.pOn != null )
459 {
460 if ( isOuter )
461 setJoinExpr( pRight.pOn, pRight.iCursor );
462 p.pWhere = sqlite3ExprAnd( pParse.db, p.pWhere, pRight.pOn );
463 pRight.pOn = null;
464 }
465  
466 /* Create extra terms on the WHERE clause for each column named
467 ** in the USING clause. Example: If the two tables to be joined are
468 ** A and B and the USING clause names X, Y, and Z, then add this
469 ** to the WHERE clause: A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
470 ** Report an error if any column mentioned in the USING clause is
471 ** not contained in both tables to be joined.
472 */
473 if ( pRight.pUsing != null )
474 {
475 IdList pList = pRight.pUsing;
476 for ( j = 0; j < pList.nId; j++ )
477 {
478 string zName; /* Name of the term in the USING clause */
479 int iLeft = 0; /* Table on the left with matching column name */
480 int iLeftCol = 0; /* Column number of matching column on the left */
481 int iRightCol; /* Column number of matching column on the right */
482  
483 zName = pList.a[j].zName;
484 iRightCol = columnIndex( pRightTab, zName );
485 if ( iRightCol < 0
486 || 0 == tableAndColumnIndex( pSrc, i + 1, zName, ref iLeft, ref iLeftCol )
487 )
488 {
489 sqlite3ErrorMsg( pParse, "cannot join using column %s - column " +
490 "not present in both tables", zName );
491 return 1;
492 }
493 addWhereTerm( pParse, pSrc, iLeft, iLeftCol, i + 1, iRightCol,
494 isOuter ? 1 : 0, ref p.pWhere );
495 }
496 }
497 }
498 return 0;
499 }
500  
501 /*
502 ** Insert code into "v" that will push the record on the top of the
503 ** stack into the sorter.
504 */
505 static void pushOntoSorter(
506 Parse pParse, /* Parser context */
507 ExprList pOrderBy, /* The ORDER BY clause */
508 Select pSelect, /* The whole SELECT statement */
509 int regData /* Register holding data to be sorted */
510 )
511 {
512 Vdbe v = pParse.pVdbe;
513 int nExpr = pOrderBy.nExpr;
514 int regBase = sqlite3GetTempRange( pParse, nExpr + 2 );
515 int regRecord = sqlite3GetTempReg( pParse );
516 sqlite3ExprCacheClear( pParse );
517 sqlite3ExprCodeExprList( pParse, pOrderBy, regBase, false );
518 sqlite3VdbeAddOp2( v, OP_Sequence, pOrderBy.iECursor, regBase + nExpr );
519 sqlite3ExprCodeMove( pParse, regData, regBase + nExpr + 1, 1 );
520 sqlite3VdbeAddOp3( v, OP_MakeRecord, regBase, nExpr + 2, regRecord );
521 sqlite3VdbeAddOp2( v, OP_IdxInsert, pOrderBy.iECursor, regRecord );
522 sqlite3ReleaseTempReg( pParse, regRecord );
523 sqlite3ReleaseTempRange( pParse, regBase, nExpr + 2 );
524 if ( pSelect.iLimit != 0 )
525 {
526 int addr1, addr2;
527 int iLimit;
528 if ( pSelect.iOffset != 0 )
529 {
530 iLimit = pSelect.iOffset + 1;
531 }
532 else
533 {
534 iLimit = pSelect.iLimit;
535 }
536 addr1 = sqlite3VdbeAddOp1( v, OP_IfZero, iLimit );
537 sqlite3VdbeAddOp2( v, OP_AddImm, iLimit, -1 );
538 addr2 = sqlite3VdbeAddOp0( v, OP_Goto );
539 sqlite3VdbeJumpHere( v, addr1 );
540 sqlite3VdbeAddOp1( v, OP_Last, pOrderBy.iECursor );
541 sqlite3VdbeAddOp1( v, OP_Delete, pOrderBy.iECursor );
542 sqlite3VdbeJumpHere( v, addr2 );
543 }
544 }
545  
546 /*
547 ** Add code to implement the OFFSET
548 */
549 static void codeOffset(
550 Vdbe v, /* Generate code into this VM */
551 Select p, /* The SELECT statement being coded */
552 int iContinue /* Jump here to skip the current record */
553 )
554 {
555 if ( p.iOffset != 0 && iContinue != 0 )
556 {
557 int addr;
558 sqlite3VdbeAddOp2( v, OP_AddImm, p.iOffset, -1 );
559 addr = sqlite3VdbeAddOp1( v, OP_IfNeg, p.iOffset );
560 sqlite3VdbeAddOp2( v, OP_Goto, 0, iContinue );
561 #if SQLITE_DEBUG
562 VdbeComment( v, "skip OFFSET records" );
563 #endif
564 sqlite3VdbeJumpHere( v, addr );
565 }
566 }
567  
568 /*
569 ** Add code that will check to make sure the N registers starting at iMem
570 ** form a distinct entry. iTab is a sorting index that holds previously
571 ** seen combinations of the N values. A new entry is made in iTab
572 ** if the current N values are new.
573 **
574 ** A jump to addrRepeat is made and the N+1 values are popped from the
575 ** stack if the top N elements are not distinct.
576 */
577 static void codeDistinct(
578 Parse pParse, /* Parsing and code generating context */
579 int iTab, /* A sorting index used to test for distinctness */
580 int addrRepeat, /* Jump to here if not distinct */
581 int N, /* Number of elements */
582 int iMem /* First element */
583 )
584 {
585 Vdbe v;
586 int r1;
587  
588 v = pParse.pVdbe;
589 r1 = sqlite3GetTempReg( pParse );
590 sqlite3VdbeAddOp4Int( v, OP_Found, iTab, addrRepeat, iMem, N );
591 sqlite3VdbeAddOp3( v, OP_MakeRecord, iMem, N, r1 );
592 sqlite3VdbeAddOp2( v, OP_IdxInsert, iTab, r1 );
593 sqlite3ReleaseTempReg( pParse, r1 );
594 }
595  
596 #if !SQLITE_OMIT_SUBQUERY
597 /*
598 ** Generate an error message when a SELECT is used within a subexpression
599 ** (example: "a IN (SELECT * FROM table)") but it has more than 1 result
600 ** column. We do this in a subroutine because the error used to occur
601 ** in multiple places. (The error only occurs in one place now, but we
602 ** retain the subroutine to minimize code disruption.)
603 */
604 static bool checkForMultiColumnSelectError(
605 Parse pParse, /* Parse context. */
606 SelectDest pDest, /* Destination of SELECT results */
607 int nExpr /* Number of result columns returned by SELECT */
608 )
609 {
610 int eDest = pDest.eDest;
611 if ( nExpr > 1 && ( eDest == SRT_Mem || eDest == SRT_Set ) )
612 {
613 sqlite3ErrorMsg( pParse, "only a single result allowed for " +
614 "a SELECT that is part of an expression" );
615 return true;
616 }
617 else
618 {
619 return false;
620 }
621 }
622 #endif
623  
624 /*
625 ** This routine generates the code for the inside of the inner loop
626 ** of a SELECT.
627 **
628 ** If srcTab and nColumn are both zero, then the pEList expressions
629 ** are evaluated in order to get the data for this row. If nColumn>0
630 ** then data is pulled from srcTab and pEList is used only to get the
631 ** datatypes for each column.
632 */
633 static void selectInnerLoop(
634 Parse pParse, /* The parser context */
635 Select p, /* The complete select statement being coded */
636 ExprList pEList, /* List of values being extracted */
637 int srcTab, /* Pull data from this table */
638 int nColumn, /* Number of columns in the source table */
639 ExprList pOrderBy, /* If not NULL, sort results using this key */
640 int distinct, /* If >=0, make sure results are distinct */
641 SelectDest pDest, /* How to dispose of the results */
642 int iContinue, /* Jump here to continue with next row */
643 int iBreak /* Jump here to break out of the inner loop */
644 )
645 {
646 Vdbe v = pParse.pVdbe;
647 int i;
648 bool hasDistinct; /* True if the DISTINCT keyword is present */
649 int regResult; /* Start of memory holding result set */
650 int eDest = pDest.eDest; /* How to dispose of results */
651 int iParm = pDest.iParm; /* First argument to disposal method */
652 int nResultCol; /* Number of result columns */
653  
654 Debug.Assert( v != null );
655 if ( NEVER( v == null ) )
656 return;
657 Debug.Assert( pEList != null );
658 hasDistinct = distinct >= 0;
659 if ( pOrderBy == null && !hasDistinct )
660 {
661 codeOffset( v, p, iContinue );
662 }
663  
664 /* Pull the requested columns.
665 */
666 if ( nColumn > 0 )
667 {
668 nResultCol = nColumn;
669 }
670 else
671 {
672 nResultCol = pEList.nExpr;
673 }
674 if ( pDest.iMem == 0 )
675 {
676 pDest.iMem = pParse.nMem + 1;
677 pDest.nMem = nResultCol;
678 pParse.nMem += nResultCol;
679 }
680 else
681 {
682 Debug.Assert( pDest.nMem == nResultCol );
683 }
684 regResult = pDest.iMem;
685 if ( nColumn > 0 )
686 {
687 for ( i = 0; i < nColumn; i++ )
688 {
689 sqlite3VdbeAddOp3( v, OP_Column, srcTab, i, regResult + i );
690 }
691 }
692 else if ( eDest != SRT_Exists )
693 {
694 /* If the destination is an EXISTS(...) expression, the actual
695 ** values returned by the SELECT are not required.
696 */
697 sqlite3ExprCacheClear( pParse );
698 sqlite3ExprCodeExprList( pParse, pEList, regResult, eDest == SRT_Output );
699 }
700 nColumn = nResultCol;
701  
702 /* If the DISTINCT keyword was present on the SELECT statement
703 ** and this row has been seen before, then do not make this row
704 ** part of the result.
705 */
706 if ( hasDistinct )
707 {
708 Debug.Assert( pEList != null );
709 Debug.Assert( pEList.nExpr == nColumn );
710 codeDistinct( pParse, distinct, iContinue, nColumn, regResult );
711 if ( pOrderBy == null )
712 {
713 codeOffset( v, p, iContinue );
714 }
715 }
716  
717 switch ( eDest )
718 {
719 /* In this mode, write each query result to the key of the temporary
720 ** table iParm.
721 */
722 #if !SQLITE_OMIT_COMPOUND_SELECT
723 case SRT_Union:
724 {
725 int r1;
726 r1 = sqlite3GetTempReg( pParse );
727 sqlite3VdbeAddOp3( v, OP_MakeRecord, regResult, nColumn, r1 );
728 sqlite3VdbeAddOp2( v, OP_IdxInsert, iParm, r1 );
729 sqlite3ReleaseTempReg( pParse, r1 );
730 break;
731 }
732  
733 /* Construct a record from the query result, but instead of
734 ** saving that record, use it as a key to delete elements from
735 ** the temporary table iParm.
736 */
737 case SRT_Except:
738 {
739 sqlite3VdbeAddOp3( v, OP_IdxDelete, iParm, regResult, nColumn );
740 break;
741 }
742 #endif
743  
744 /* Store the result as data using a unique key.
745 */
746 case SRT_Table:
747 case SRT_EphemTab:
748 {
749 int r1 = sqlite3GetTempReg( pParse );
750 testcase( eDest == SRT_Table );
751 testcase( eDest == SRT_EphemTab );
752 sqlite3VdbeAddOp3( v, OP_MakeRecord, regResult, nColumn, r1 );
753 if ( pOrderBy != null )
754 {
755 pushOntoSorter( pParse, pOrderBy, p, r1 );
756 }
757 else
758 {
759 int r2 = sqlite3GetTempReg( pParse );
760 sqlite3VdbeAddOp2( v, OP_NewRowid, iParm, r2 );
761 sqlite3VdbeAddOp3( v, OP_Insert, iParm, r1, r2 );
762 sqlite3VdbeChangeP5( v, OPFLAG_APPEND );
763 sqlite3ReleaseTempReg( pParse, r2 );
764 }
765 sqlite3ReleaseTempReg( pParse, r1 );
766 break;
767 }
768  
769 #if !SQLITE_OMIT_SUBQUERY
770 /* If we are creating a set for an "expr IN (SELECT ...)" construct,
771 ** then there should be a single item on the stack. Write this
772 ** item into the set table with bogus data.
773 */
774 case SRT_Set:
775 {
776 Debug.Assert( nColumn == 1 );
777 p.affinity = sqlite3CompareAffinity( pEList.a[0].pExpr, pDest.affinity );
778 if ( pOrderBy != null )
779 {
780 /* At first glance you would think we could optimize out the
781 ** ORDER BY in this case since the order of entries in the set
782 ** does not matter. But there might be a LIMIT clause, in which
783 ** case the order does matter */
784 pushOntoSorter( pParse, pOrderBy, p, regResult );
785 }
786 else
787 {
788 int r1 = sqlite3GetTempReg( pParse );
789 sqlite3VdbeAddOp4( v, OP_MakeRecord, regResult, 1, r1, p.affinity, 1 );
790 sqlite3ExprCacheAffinityChange( pParse, regResult, 1 );
791 sqlite3VdbeAddOp2( v, OP_IdxInsert, iParm, r1 );
792 sqlite3ReleaseTempReg( pParse, r1 );
793 }
794 break;
795 }
796  
797 /* If any row exist in the result set, record that fact and abort.
798 */
799 case SRT_Exists:
800 {
801 sqlite3VdbeAddOp2( v, OP_Integer, 1, iParm );
802 /* The LIMIT clause will terminate the loop for us */
803 break;
804 }
805  
806 /* If this is a scalar select that is part of an expression, then
807 ** store the results in the appropriate memory cell and break out
808 ** of the scan loop.
809 */
810 case SRT_Mem:
811 {
812 Debug.Assert( nColumn == 1 );
813 if ( pOrderBy != null )
814 {
815 pushOntoSorter( pParse, pOrderBy, p, regResult );
816 }
817 else
818 {
819 sqlite3ExprCodeMove( pParse, regResult, iParm, 1 );
820 /* The LIMIT clause will jump out of the loop for us */
821 }
822 break;
823 }
824 #endif // * #if !SQLITE_OMIT_SUBQUERY */
825  
826 /* Send the data to the callback function or to a subroutine. In the
827 ** case of a subroutine, the subroutine itself is responsible for
828 ** popping the data from the stack.
829 */
830 case SRT_Coroutine:
831 case SRT_Output:
832 {
833 testcase( eDest == SRT_Coroutine );
834 testcase( eDest == SRT_Output );
835 if ( pOrderBy != null )
836 {
837 int r1 = sqlite3GetTempReg( pParse );
838 sqlite3VdbeAddOp3( v, OP_MakeRecord, regResult, nColumn, r1 );
839 pushOntoSorter( pParse, pOrderBy, p, r1 );
840 sqlite3ReleaseTempReg( pParse, r1 );
841 }
842 else if ( eDest == SRT_Coroutine )
843 {
844 sqlite3VdbeAddOp1( v, OP_Yield, pDest.iParm );
845 }
846 else
847 {
848 sqlite3VdbeAddOp2( v, OP_ResultRow, regResult, nColumn );
849 sqlite3ExprCacheAffinityChange( pParse, regResult, nColumn );
850 }
851 break;
852 }
853  
854 #if !SQLITE_OMIT_TRIGGER
855 /* Discard the results. This is used for SELECT statements inside
856 ** the body of a TRIGGER. The purpose of such selects is to call
857 ** user-defined functions that have side effects. We do not care
858 ** about the actual results of the select.
859 */
860 default:
861 {
862 Debug.Assert( eDest == SRT_Discard );
863 break;
864 }
865 #endif
866 }
867  
868 /* Jump to the end of the loop if the LIMIT is reached. Except, if
869 ** there is a sorter, in which case the sorter has already limited
870 ** the output for us.
871 */
872 if ( pOrderBy == null && p.iLimit != 0 )
873 {
874 sqlite3VdbeAddOp3( v, OP_IfZero, p.iLimit, iBreak, -1 );
875 }
876 }
877  
878 /*
879 ** Given an expression list, generate a KeyInfo structure that records
880 ** the collating sequence for each expression in that expression list.
881 **
882 ** If the ExprList is an ORDER BY or GROUP BY clause then the resulting
883 ** KeyInfo structure is appropriate for initializing a virtual index to
884 ** implement that clause. If the ExprList is the result set of a SELECT
885 ** then the KeyInfo structure is appropriate for initializing a virtual
886 ** index to implement a DISTINCT test.
887 **
888 ** Space to hold the KeyInfo structure is obtain from malloc. The calling
889 ** function is responsible for seeing that this structure is eventually
890 ** freed. Add the KeyInfo structure to the P4 field of an opcode using
891 ** P4_KEYINFO_HANDOFF is the usual way of dealing with this.
892 */
893 static KeyInfo keyInfoFromExprList( Parse pParse, ExprList pList )
894 {
895 sqlite3 db = pParse.db;
896 int nExpr;
897 KeyInfo pInfo;
898 ExprList_item pItem;
899 int i;
900  
901 nExpr = pList.nExpr;
902 pInfo = new KeyInfo();//sqlite3DbMallocZero(db, sizeof(*pInfo) + nExpr*(CollSeq*.Length+1) );
903 if ( pInfo != null )
904 {
905 pInfo.aSortOrder = new byte[nExpr];// pInfo.aColl[nExpr];
906 pInfo.aColl = new CollSeq[nExpr];
907 pInfo.nField = (u16)nExpr;
908 pInfo.enc = db.aDbStatic[0].pSchema.enc;// ENC(db);
909 pInfo.db = db;
910 for ( i = 0; i < nExpr; i++ )
911 {//, pItem++){
912 pItem = pList.a[i];
913 CollSeq pColl;
914 pColl = sqlite3ExprCollSeq( pParse, pItem.pExpr );
915 if ( pColl == null )
916 {
917 pColl = db.pDfltColl;
918 }
919 pInfo.aColl[i] = pColl;
920 pInfo.aSortOrder[i] = (byte)pItem.sortOrder;
921 }
922 }
923 return pInfo;
924 }
925  
926 #if !SQLITE_OMIT_COMPOUND_SELECT
927 /*
928 ** Name of the connection operator, used for error messages.
929 */
930 static string selectOpName( int id )
931 {
932 string z;
933 switch ( id )
934 {
935 case TK_ALL:
936 z = "UNION ALL";
937 break;
938 case TK_INTERSECT:
939 z = "INTERSECT";
940 break;
941 case TK_EXCEPT:
942 z = "EXCEPT";
943 break;
944 default:
945 z = "UNION";
946 break;
947 }
948 return z;
949 }
950 #endif //* SQLITE_OMIT_COMPOUND_SELECT */
951  
952 #if !SQLITE_OMIT_EXPLAIN
953 /*
954 ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
955 ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
956 ** where the caption is of the form:
957 **
958 ** "USE TEMP B-TREE FOR xxx"
959 **
960 ** where xxx is one of "DISTINCT", "ORDER BY" or "GROUP BY". Exactly which
961 ** is determined by the zUsage argument.
962 */
963 static void explainTempTable( Parse pParse, string zUsage )
964 {
965 if ( pParse.explain == 2 )
966 {
967 Vdbe v = pParse.pVdbe;
968 string zMsg = sqlite3MPrintf( pParse.db, "USE TEMP B-TREE FOR %s", zUsage );
969 sqlite3VdbeAddOp4( v, OP_Explain, pParse.iSelectId, 0, 0, zMsg, P4_DYNAMIC );
970 }
971 }
972  
973 /*
974 ** Assign expression b to lvalue a. A second, no-op, version of this macro
975 ** is provided when SQLITE_OMIT_EXPLAIN is defined. This allows the code
976 ** in sqlite3Select() to assign values to structure member variables that
977 ** only exist if SQLITE_OMIT_EXPLAIN is not defined without polluting the
978 ** code with #if !directives.
979 */
980 //# define explainSetInteger(a, b) a = b
981 static void explainSetInteger( ref int a, int b )
982 {
983 a = b;
984 }
985 static void explainSetInteger( ref byte a, int b )
986 {
987 a = (byte)b;
988 }
989 #else
990 /* No-op versions of the explainXXX() functions and macros. */
991 //# define explainTempTable(y,z)
992 static void explainTempTable(ref int a, int b){ a = b;}
993  
994 //# define explainSetInteger(y,z)
995 static void explainSetInteger(ref int a, int b){ a = b;}
996 #endif
997  
998 #if !(SQLITE_OMIT_EXPLAIN) && !(SQLITE_OMIT_COMPOUND_SELECT)
999 /*
1000 ** Unless an "EXPLAIN QUERY PLAN" command is being processed, this function
1001 ** is a no-op. Otherwise, it adds a single row of output to the EQP result,
1002 ** where the caption is of one of the two forms:
1003 **
1004 ** "COMPOSITE SUBQUERIES iSub1 and iSub2 (op)"
1005 ** "COMPOSITE SUBQUERIES iSub1 and iSub2 USING TEMP B-TREE (op)"
1006 **
1007 ** where iSub1 and iSub2 are the integers passed as the corresponding
1008 ** function parameters, and op is the text representation of the parameter
1009 ** of the same name. The parameter "op" must be one of TK_UNION, TK_EXCEPT,
1010 ** TK_INTERSECT or TK_ALL. The first form is used if argument bUseTmp is
1011 ** false, or the second form if it is true.
1012 */
1013 static void explainComposite(
1014 Parse pParse, /* Parse context */
1015 int op, /* One of TK_UNION, TK_EXCEPT etc. */
1016 int iSub1, /* Subquery id 1 */
1017 int iSub2, /* Subquery id 2 */
1018 bool bUseTmp /* True if a temp table was used */
1019 )
1020 {
1021 Debug.Assert( op == TK_UNION || op == TK_EXCEPT || op == TK_INTERSECT || op == TK_ALL );
1022 if ( pParse.explain == 2 )
1023 {
1024 Vdbe v = pParse.pVdbe;
1025 string zMsg = sqlite3MPrintf(
1026 pParse.db, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2,
1027 bUseTmp ? "USING TEMP B-TREE " : string.Empty, selectOpName( op )
1028 );
1029 sqlite3VdbeAddOp4( v, OP_Explain, pParse.iSelectId, 0, 0, zMsg, P4_DYNAMIC );
1030 }
1031 }
1032  
1033 #else
1034 /* No-op versions of the explainXXX() functions and macros. */
1035 //# define explainComposite(v,w,x,y,z)
1036 static void explainComposite(Parse v, int w,int x,int y,bool z) {}
1037 #endif
1038  
1039  
1040 /*
1041 ** If the inner loop was generated using a non-null pOrderBy argument,
1042 ** then the results were placed in a sorter. After the loop is terminated
1043 ** we need to run the sorter and output the results. The following
1044 ** routine generates the code needed to do that.
1045 */
1046 static void generateSortTail(
1047 Parse pParse, /* Parsing context */
1048 Select p, /* The SELECT statement */
1049 Vdbe v, /* Generate code into this VDBE */
1050 int nColumn, /* Number of columns of data */
1051 SelectDest pDest /* Write the sorted results here */
1052 )
1053 {
1054 int addrBreak = sqlite3VdbeMakeLabel( v ); /* Jump here to exit loop */
1055 int addrContinue = sqlite3VdbeMakeLabel( v ); /* Jump here for next cycle */
1056 int addr;
1057 int iTab;
1058 int pseudoTab = 0;
1059 ExprList pOrderBy = p.pOrderBy;
1060  
1061 int eDest = pDest.eDest;
1062 int iParm = pDest.iParm;
1063  
1064 int regRow;
1065 int regRowid;
1066  
1067 iTab = pOrderBy.iECursor;
1068 regRow = sqlite3GetTempReg( pParse );
1069 if ( eDest == SRT_Output || eDest == SRT_Coroutine )
1070 {
1071 pseudoTab = pParse.nTab++;
1072 sqlite3VdbeAddOp3( v, OP_OpenPseudo, pseudoTab, regRow, nColumn );
1073 regRowid = 0;
1074 }
1075 else
1076 {
1077 regRowid = sqlite3GetTempReg( pParse );
1078 }
1079 addr = 1 + sqlite3VdbeAddOp2( v, OP_Sort, iTab, addrBreak );
1080 codeOffset( v, p, addrContinue );
1081 sqlite3VdbeAddOp3( v, OP_Column, iTab, pOrderBy.nExpr + 1, regRow );
1082 switch ( eDest )
1083 {
1084 case SRT_Table:
1085 case SRT_EphemTab:
1086 {
1087 testcase( eDest == SRT_Table );
1088 testcase( eDest == SRT_EphemTab );
1089 sqlite3VdbeAddOp2( v, OP_NewRowid, iParm, regRowid );
1090 sqlite3VdbeAddOp3( v, OP_Insert, iParm, regRow, regRowid );
1091 sqlite3VdbeChangeP5( v, OPFLAG_APPEND );
1092 break;
1093 }
1094 #if !SQLITE_OMIT_SUBQUERY
1095 case SRT_Set:
1096 {
1097 Debug.Assert( nColumn == 1 );
1098 sqlite3VdbeAddOp4( v, OP_MakeRecord, regRow, 1, regRowid, p.affinity, 1 );
1099 sqlite3ExprCacheAffinityChange( pParse, regRow, 1 );
1100 sqlite3VdbeAddOp2( v, OP_IdxInsert, iParm, regRowid );
1101 break;
1102 }
1103 case SRT_Mem:
1104 {
1105 Debug.Assert( nColumn == 1 );
1106 sqlite3ExprCodeMove( pParse, regRow, iParm, 1 );
1107 /* The LIMIT clause will terminate the loop for us */
1108 break;
1109 }
1110 #endif
1111 default:
1112 {
1113 int i;
1114 Debug.Assert( eDest == SRT_Output || eDest == SRT_Coroutine );
1115 testcase( eDest == SRT_Output );
1116 testcase( eDest == SRT_Coroutine );
1117 for ( i = 0; i < nColumn; i++ )
1118 {
1119 Debug.Assert( regRow != pDest.iMem + i );
1120 sqlite3VdbeAddOp3( v, OP_Column, pseudoTab, i, pDest.iMem + i );
1121 if ( i == 0 )
1122 {
1123 sqlite3VdbeChangeP5( v, OPFLAG_CLEARCACHE );
1124 }
1125 }
1126 if ( eDest == SRT_Output )
1127 {
1128 sqlite3VdbeAddOp2( v, OP_ResultRow, pDest.iMem, nColumn );
1129 sqlite3ExprCacheAffinityChange( pParse, pDest.iMem, nColumn );
1130 }
1131 else
1132 {
1133 sqlite3VdbeAddOp1( v, OP_Yield, pDest.iParm );
1134 }
1135 break;
1136 }
1137 }
1138 sqlite3ReleaseTempReg( pParse, regRow );
1139 sqlite3ReleaseTempReg( pParse, regRowid );
1140  
1141 /* The bottom of the loop
1142 */
1143 sqlite3VdbeResolveLabel( v, addrContinue );
1144 sqlite3VdbeAddOp2( v, OP_Next, iTab, addr );
1145 sqlite3VdbeResolveLabel( v, addrBreak );
1146 if ( eDest == SRT_Output || eDest == SRT_Coroutine )
1147 {
1148 sqlite3VdbeAddOp2( v, OP_Close, pseudoTab, 0 );
1149 }
1150  
1151 }
1152  
1153 /*
1154 ** Return a pointer to a string containing the 'declaration type' of the
1155 ** expression pExpr. The string may be treated as static by the caller.
1156 **
1157 ** The declaration type is the exact datatype definition extracted from the
1158 ** original CREATE TABLE statement if the expression is a column. The
1159 ** declaration type for a ROWID field is INTEGER. Exactly when an expression
1160 ** is considered a column can be complex in the presence of subqueries. The
1161 ** result-set expression in all of the following SELECT statements is
1162 ** considered a column by this function.
1163 **
1164 ** SELECT col FROM tbl;
1165 ** SELECT (SELECT col FROM tbl;
1166 ** SELECT (SELECT col FROM tbl);
1167 ** SELECT abc FROM (SELECT col AS abc FROM tbl);
1168 **
1169 ** The declaration type for any expression other than a column is NULL.
1170 */
1171 static string columnType(
1172 NameContext pNC,
1173 Expr pExpr,
1174 ref string pzOriginDb,
1175 ref string pzOriginTab,
1176 ref string pzOriginCol
1177 )
1178 {
1179 string zType = null;
1180 string zOriginDb = null;
1181 string zOriginTab = null;
1182 string zOriginCol = null;
1183 int j;
1184 if ( NEVER( pExpr == null ) || pNC.pSrcList == null )
1185 return null;
1186  
1187 switch ( pExpr.op )
1188 {
1189 case TK_AGG_COLUMN:
1190 case TK_COLUMN:
1191 {
1192 /* The expression is a column. Locate the table the column is being
1193 ** extracted from in NameContext.pSrcList. This table may be real
1194 ** database table or a subquery.
1195 */
1196 Table pTab = null; /* Table structure column is extracted from */
1197 Select pS = null; /* Select the column is extracted from */
1198 int iCol = pExpr.iColumn; /* Index of column in pTab */
1199 testcase( pExpr.op == TK_AGG_COLUMN );
1200 testcase( pExpr.op == TK_COLUMN );
1201 while ( pNC != null && pTab == null )
1202 {
1203 SrcList pTabList = pNC.pSrcList;
1204 for ( j = 0; j < pTabList.nSrc && pTabList.a[j].iCursor != pExpr.iTable; j++ )
1205 ;
1206 if ( j < pTabList.nSrc )
1207 {
1208 pTab = pTabList.a[j].pTab;
1209 pS = pTabList.a[j].pSelect;
1210 }
1211 else
1212 {
1213 pNC = pNC.pNext;
1214 }
1215 }
1216  
1217 if ( pTab == null )
1218 {
1219 /* At one time, code such as "SELECT new.x" within a trigger would
1220 ** cause this condition to run. Since then, we have restructured how
1221 ** trigger code is generated and so this condition is no longer
1222 ** possible. However, it can still be true for statements like
1223 ** the following:
1224 **
1225 ** CREATE TABLE t1(col INTEGER);
1226 ** SELECT (SELECT t1.col) FROM FROM t1;
1227 **
1228 ** when columnType() is called on the expression "t1.col" in the
1229 ** sub-select. In this case, set the column type to NULL, even
1230 ** though it should really be "INTEGER".
1231 **
1232 ** This is not a problem, as the column type of "t1.col" is never
1233 ** used. When columnType() is called on the expression
1234 ** "(SELECT t1.col)", the correct type is returned (see the TK_SELECT
1235 ** branch below. */
1236 break;
1237 }
1238  
1239 //Debug.Assert( pTab != null && pExpr.pTab == pTab );
1240 if ( pS != null )
1241 {
1242 /* The "table" is actually a sub-select or a view in the FROM clause
1243 ** of the SELECT statement. Return the declaration type and origin
1244 ** data for the result-set column of the sub-select.
1245 */
1246 if ( iCol >= 0 && ALWAYS( iCol < pS.pEList.nExpr ) )
1247 {
1248 /* If iCol is less than zero, then the expression requests the
1249 ** rowid of the sub-select or view. This expression is legal (see
1250 ** test case misc2.2.2) - it always evaluates to NULL.
1251 */
1252 NameContext sNC = new NameContext();
1253 Expr p = pS.pEList.a[iCol].pExpr;
1254 sNC.pSrcList = pS.pSrc;
1255 sNC.pNext = pNC;
1256 sNC.pParse = pNC.pParse;
1257 zType = columnType( sNC, p, ref zOriginDb, ref zOriginTab, ref zOriginCol );
1258 }
1259 }
1260 else if ( ALWAYS( pTab.pSchema ) )
1261 {
1262 /* A real table */
1263 Debug.Assert( pS == null );
1264 if ( iCol < 0 )
1265 iCol = pTab.iPKey;
1266 Debug.Assert( iCol == -1 || ( iCol >= 0 && iCol < pTab.nCol ) );
1267 if ( iCol < 0 )
1268 {
1269 zType = "INTEGER";
1270 zOriginCol = "rowid";
1271 }
1272 else
1273 {
1274 zType = pTab.aCol[iCol].zType;
1275 zOriginCol = pTab.aCol[iCol].zName;
1276 }
1277 zOriginTab = pTab.zName;
1278 if ( pNC.pParse != null )
1279 {
1280 int iDb = sqlite3SchemaToIndex( pNC.pParse.db, pTab.pSchema );
1281 zOriginDb = pNC.pParse.db.aDb[iDb].zName;
1282 }
1283 }
1284 break;
1285 }
1286 #if !SQLITE_OMIT_SUBQUERY
1287 case TK_SELECT:
1288 {
1289 /* The expression is a sub-select. Return the declaration type and
1290 ** origin info for the single column in the result set of the SELECT
1291 ** statement.
1292 */
1293 NameContext sNC = new NameContext();
1294 Select pS = pExpr.x.pSelect;
1295 Expr p = pS.pEList.a[0].pExpr;
1296 Debug.Assert( ExprHasProperty( pExpr, EP_xIsSelect ) );
1297 sNC.pSrcList = pS.pSrc;
1298 sNC.pNext = pNC;
1299 sNC.pParse = pNC.pParse;
1300 zType = columnType( sNC, p, ref zOriginDb, ref zOriginTab, ref zOriginCol );
1301 break;
1302 }
1303 #endif
1304 }
1305  
1306 //if ( pzOriginDb != null )
1307 {
1308 //Debug.Assert( pzOriginTab != null && pzOriginCol != null );
1309 pzOriginDb = zOriginDb;
1310 pzOriginTab = zOriginTab;
1311 pzOriginCol = zOriginCol;
1312 }
1313 return zType;
1314 }
1315  
1316 /*
1317 ** Generate code that will tell the VDBE the declaration types of columns
1318 ** in the result set.
1319 */
1320 static void generateColumnTypes(
1321 Parse pParse, /* Parser context */
1322 SrcList pTabList, /* List of tables */
1323 ExprList pEList /* Expressions defining the result set */
1324 )
1325 {
1326 #if !SQLITE_OMIT_DECLTYPE
1327 Vdbe v = pParse.pVdbe;
1328 int i;
1329 NameContext sNC = new NameContext();
1330 sNC.pSrcList = pTabList;
1331 sNC.pParse = pParse;
1332 for ( i = 0; i < pEList.nExpr; i++ )
1333 {
1334 Expr p = pEList.a[i].pExpr;
1335 string zType;
1336 #if SQLITE_ENABLE_COLUMN_METADATA
1337 string zOrigDb = null;
1338 string zOrigTab = null;
1339 string zOrigCol = null;
1340 zType = columnType( sNC, p, ref zOrigDb, ref zOrigTab, ref zOrigCol );
1341  
1342 /* The vdbe must make its own copy of the column-type and other
1343 ** column specific strings, in case the schema is reset before this
1344 ** virtual machine is deleted.
1345 */
1346 sqlite3VdbeSetColName( v, i, COLNAME_DATABASE, zOrigDb, SQLITE_TRANSIENT );
1347 sqlite3VdbeSetColName( v, i, COLNAME_TABLE, zOrigTab, SQLITE_TRANSIENT );
1348 sqlite3VdbeSetColName( v, i, COLNAME_COLUMN, zOrigCol, SQLITE_TRANSIENT );
1349 #else
1350 string sDummy = null;
1351 zType = columnType( sNC, p, ref sDummy, ref sDummy, ref sDummy );
1352 #endif
1353 sqlite3VdbeSetColName( v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT );
1354 }
1355 #endif //* SQLITE_OMIT_DECLTYPE */
1356 }
1357  
1358 /*
1359 ** Generate code that will tell the VDBE the names of columns
1360 ** in the result set. This information is used to provide the
1361 ** azCol[] values in the callback.
1362 */
1363 static void generateColumnNames(
1364 Parse pParse, /* Parser context */
1365 SrcList pTabList, /* List of tables */
1366 ExprList pEList /* Expressions defining the result set */
1367 )
1368 {
1369 Vdbe v = pParse.pVdbe;
1370 int i, j;
1371 sqlite3 db = pParse.db;
1372 bool fullNames;
1373 bool shortNames;
1374  
1375 #if !SQLITE_OMIT_EXPLAIN
1376 /* If this is an EXPLAIN, skip this step */
1377 if ( pParse.explain != 0 )
1378 {
1379 return;
1380 }
1381 #endif
1382  
1383 if ( pParse.colNamesSet != 0 || NEVER( v == null ) /*|| db.mallocFailed != 0 */ )
1384 return;
1385 pParse.colNamesSet = 1;
1386 fullNames = ( db.flags & SQLITE_FullColNames ) != 0;
1387 shortNames = ( db.flags & SQLITE_ShortColNames ) != 0;
1388 sqlite3VdbeSetNumCols( v, pEList.nExpr );
1389 for ( i = 0; i < pEList.nExpr; i++ )
1390 {
1391 Expr p;
1392 p = pEList.a[i].pExpr;
1393 if ( NEVER( p == null ) )
1394 continue;
1395 if ( pEList.a[i].zName != null )
1396 {
1397 string zName = pEList.a[i].zName;
1398 sqlite3VdbeSetColName( v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT );
1399 }
1400 else if ( ( p.op == TK_COLUMN || p.op == TK_AGG_COLUMN ) && pTabList != null )
1401 {
1402 Table pTab;
1403 string zCol;
1404 int iCol = p.iColumn;
1405 for ( j = 0; ALWAYS( j < pTabList.nSrc ); j++ )
1406 {
1407 if ( pTabList.a[j].iCursor == p.iTable )
1408 break;
1409 }
1410 Debug.Assert( j < pTabList.nSrc );
1411 pTab = pTabList.a[j].pTab;
1412 if ( iCol < 0 )
1413 iCol = pTab.iPKey;
1414 Debug.Assert( iCol == -1 || ( iCol >= 0 && iCol < pTab.nCol ) );
1415 if ( iCol < 0 )
1416 {
1417 zCol = "rowid";
1418 }
1419 else
1420 {
1421 zCol = pTab.aCol[iCol].zName;
1422 }
1423 if ( !shortNames && !fullNames )
1424 {
1425 sqlite3VdbeSetColName( v, i, COLNAME_NAME,
1426 pEList.a[i].zSpan, SQLITE_DYNAMIC );//sqlite3DbStrDup(db, pEList->a[i].zSpan), SQLITE_DYNAMIC);
1427 }
1428 else if ( fullNames )
1429 {
1430 string zName;
1431 zName = sqlite3MPrintf( db, "%s.%s", pTab.zName, zCol );
1432 sqlite3VdbeSetColName( v, i, COLNAME_NAME, zName, SQLITE_DYNAMIC );
1433 }
1434 else
1435 {
1436 sqlite3VdbeSetColName( v, i, COLNAME_NAME, zCol, SQLITE_TRANSIENT );
1437 }
1438 }
1439 else
1440 {
1441 sqlite3VdbeSetColName( v, i, COLNAME_NAME,
1442 pEList.a[i].zSpan, SQLITE_DYNAMIC );//sqlite3DbStrDup(db, pEList->a[i].zSpan), SQLITE_DYNAMIC);
1443 }
1444 }
1445 generateColumnTypes( pParse, pTabList, pEList );
1446 }
1447  
1448 /*
1449 ** Given a an expression list (which is really the list of expressions
1450 ** that form the result set of a SELECT statement) compute appropriate
1451 ** column names for a table that would hold the expression list.
1452 **
1453 ** All column names will be unique.
1454 **
1455 ** Only the column names are computed. Column.zType, Column.zColl,
1456 ** and other fields of Column are zeroed.
1457 **
1458 ** Return SQLITE_OK on success. If a memory allocation error occurs,
1459 ** store NULL in paCol and 0 in pnCol and return SQLITE_NOMEM.
1460 */
1461 static int selectColumnsFromExprList(
1462 Parse pParse, /* Parsing context */
1463 ExprList pEList, /* Expr list from which to derive column names */
1464 ref int pnCol, /* Write the number of columns here */
1465 ref Column[] paCol /* Write the new column list here */
1466 )
1467 {
1468 sqlite3 db = pParse.db; /* Database connection */
1469 int i, j; /* Loop counters */
1470 int cnt; /* Index added to make the name unique */
1471 Column[] aCol;
1472 Column pCol; /* For looping over result columns */
1473 int nCol; /* Number of columns in the result set */
1474 Expr p; /* Expression for a single result column */
1475 string zName; /* Column name */
1476 int nName; /* Size of name in zName[] */
1477  
1478  
1479 pnCol = nCol = pEList.nExpr;
1480 aCol = paCol = new Column[nCol];//sqlite3DbMallocZero(db, sizeof(aCol[0])*nCol);
1481 //if ( aCol == null )
1482 // return SQLITE_NOMEM;
1483 for ( i = 0; i < nCol; i++ )//, pCol++)
1484 {
1485 if ( aCol[i] == null )
1486 aCol[i] = new Column();
1487 pCol = aCol[i];
1488 /* Get an appropriate name for the column
1489 */
1490 p = pEList.a[i].pExpr;
1491 Debug.Assert( p.pRight == null || ExprHasProperty( p.pRight, EP_IntValue )
1492 || p.pRight.u.zToken == null || p.pRight.u.zToken.Length > 0 );
1493 if ( pEList.a[i].zName != null && ( zName = pEList.a[i].zName ) != string.Empty )
1494 {
1495 /* If the column contains an "AS <name>" phrase, use <name> as the name */
1496 //zName = sqlite3DbStrDup(db, zName);
1497 }
1498 else
1499 {
1500 Expr pColExpr = p; /* The expression that is the result column name */
1501 Table pTab; /* Table associated with this expression */
1502 while ( pColExpr.op == TK_DOT )
1503 pColExpr = pColExpr.pRight;
1504 if ( pColExpr.op == TK_COLUMN && ALWAYS( pColExpr.pTab != null ) )
1505 {
1506 /* For columns use the column name name */
1507 int iCol = pColExpr.iColumn;
1508 pTab = pColExpr.pTab;
1509 if ( iCol < 0 )
1510 iCol = pTab.iPKey;
1511 zName = sqlite3MPrintf( db, "%s",
1512 iCol >= 0 ? pTab.aCol[iCol].zName : "rowid" );
1513 }
1514 else if ( pColExpr.op == TK_ID )
1515 {
1516 Debug.Assert( !ExprHasProperty( pColExpr, EP_IntValue ) );
1517 zName = sqlite3MPrintf( db, "%s", pColExpr.u.zToken );
1518 }
1519 else
1520 {
1521 /* Use the original text of the column expression as its name */
1522 zName = sqlite3MPrintf( db, "%s", pEList.a[i].zSpan );
1523 }
1524 }
1525 //if ( db.mallocFailed != 0 )
1526 //{
1527 // sqlite3DbFree( db, ref zName );
1528 // break;
1529 //}
1530  
1531 /* Make sure the column name is unique. If the name is not unique,
1532 ** append a integer to the name so that it becomes unique.
1533 */
1534 nName = sqlite3Strlen30( zName );
1535 for ( j = cnt = 0; j < i; j++ )
1536 {
1537 if ( aCol[j].zName.Equals( zName, StringComparison.OrdinalIgnoreCase ) )
1538 {
1539 string zNewName;
1540 //zName[nName] = 0;
1541 zNewName = sqlite3MPrintf( db, "%s:%d", zName.Substring( 0, nName ), ++cnt );
1542 sqlite3DbFree( db, ref zName );
1543 zName = zNewName;
1544 j = -1;
1545 if ( zName.Length == 0 )
1546 break;
1547 }
1548 }
1549 pCol.zName = zName;
1550 }
1551 //if ( db.mallocFailed != 0 )
1552 //{
1553 // for ( j = 0 ; j < i ; j++ )
1554 // {
1555 // sqlite3DbFree( db, aCol[j].zName );
1556 // }
1557 // sqlite3DbFree( db, aCol );
1558 // paCol = null;
1559 // pnCol = 0;
1560 // return SQLITE_NOMEM;
1561 //}
1562 return SQLITE_OK;
1563 }
1564  
1565 /*
1566 ** Add type and collation information to a column list based on
1567 ** a SELECT statement.
1568 **
1569 ** The column list presumably came from selectColumnNamesFromExprList().
1570 ** The column list has only names, not types or collations. This
1571 ** routine goes through and adds the types and collations.
1572 **
1573 ** This routine requires that all identifiers in the SELECT
1574 ** statement be resolved.
1575 */
1576 static void selectAddColumnTypeAndCollation(
1577 Parse pParse, /* Parsing contexts */
1578 int nCol, /* Number of columns */
1579 Column[] aCol, /* List of columns */
1580 Select pSelect /* SELECT used to determine types and collations */
1581 )
1582 {
1583 ////sqlite3 db = pParse.db;
1584 NameContext sNC;
1585 Column pCol;
1586 CollSeq pColl;
1587 int i;
1588 Expr p;
1589 ExprList_item[] a;
1590  
1591 Debug.Assert( pSelect != null );
1592 Debug.Assert( ( pSelect.selFlags & SF_Resolved ) != 0 );
1593 Debug.Assert( nCol == pSelect.pEList.nExpr /*|| db.mallocFailed != 0 */ );
1594 // if ( db.mallocFailed != 0 ) return;
1595 sNC = new NameContext();// memset( &sNC, 0, sizeof( sNC ) );
1596 sNC.pSrcList = pSelect.pSrc;
1597 a = pSelect.pEList.a;
1598 for ( i = 0; i < nCol; i++ )//, pCol++ )
1599 {
1600 pCol = aCol[i];
1601 p = a[i].pExpr;
1602 string bDummy = null;
1603 pCol.zType = columnType( sNC, p, ref bDummy, ref bDummy, ref bDummy );// sqlite3DbStrDup( db, columnType( sNC, p, 0, 0, 0 ) );
1604 pCol.affinity = sqlite3ExprAffinity( p );
1605 if ( pCol.affinity == 0 )
1606 pCol.affinity = SQLITE_AFF_NONE;
1607 pColl = sqlite3ExprCollSeq( pParse, p );
1608 if ( pColl != null )
1609 {
1610 pCol.zColl = pColl.zName;// sqlite3DbStrDup( db, pColl.zName );
1611 }
1612 }
1613 }
1614  
1615 /*
1616 ** Given a SELECT statement, generate a Table structure that describes
1617 ** the result set of that SELECT.
1618 */
1619 static Table sqlite3ResultSetOfSelect( Parse pParse, Select pSelect )
1620 {
1621 Table pTab;
1622 sqlite3 db = pParse.db;
1623 int savedFlags;
1624  
1625 savedFlags = db.flags;
1626 db.flags &= ~SQLITE_FullColNames;
1627 db.flags |= SQLITE_ShortColNames;
1628 sqlite3SelectPrep( pParse, pSelect, null );
1629 if ( pParse.nErr != 0 )
1630 return null;
1631 while ( pSelect.pPrior != null )
1632 pSelect = pSelect.pPrior;
1633 db.flags = savedFlags;
1634 pTab = new Table();// sqlite3DbMallocZero( db, sizeof( Table ) );
1635 if ( pTab == null )
1636 {
1637 return null;
1638 }
1639 /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
1640 ** is disabled */
1641 Debug.Assert( db.lookaside.bEnabled == 0 );
1642 pTab.nRef = 1;
1643 pTab.zName = null;
1644 pTab.nRowEst = 1000000;
1645 selectColumnsFromExprList( pParse, pSelect.pEList, ref pTab.nCol, ref pTab.aCol );
1646 selectAddColumnTypeAndCollation( pParse, pTab.nCol, pTab.aCol, pSelect );
1647 pTab.iPKey = -1;
1648 //if ( db.mallocFailed != 0 )
1649 //{
1650 // sqlite3DeleteTable(db, ref pTab );
1651 // return null;
1652 //}
1653 return pTab;
1654 }
1655  
1656 /*
1657 ** Get a VDBE for the given parser context. Create a new one if necessary.
1658 ** If an error occurs, return NULL and leave a message in pParse.
1659 */
1660 static Vdbe sqlite3GetVdbe( Parse pParse )
1661 {
1662 Vdbe v = pParse.pVdbe;
1663 if ( v == null )
1664 {
1665 v = pParse.pVdbe = sqlite3VdbeCreate( pParse.db );
1666 #if !SQLITE_OMIT_TRACE
1667 if ( v != null )
1668 {
1669 sqlite3VdbeAddOp0( v, OP_Trace );
1670 }
1671 #endif
1672 }
1673 return v;
1674 }
1675  
1676  
1677 /*
1678 ** Compute the iLimit and iOffset fields of the SELECT based on the
1679 ** pLimit and pOffset expressions. pLimit and pOffset hold the expressions
1680 ** that appear in the original SQL statement after the LIMIT and OFFSET
1681 ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset
1682 ** are the integer memory register numbers for counters used to compute
1683 ** the limit and offset. If there is no limit and/or offset, then
1684 ** iLimit and iOffset are negative.
1685 **
1686 ** This routine changes the values of iLimit and iOffset only if
1687 ** a limit or offset is defined by pLimit and pOffset. iLimit and
1688 ** iOffset should have been preset to appropriate default values
1689 ** (usually but not always -1) prior to calling this routine.
1690 ** Only if pLimit!=0 or pOffset!=0 do the limit registers get
1691 ** redefined. The UNION ALL operator uses this property to force
1692 ** the reuse of the same limit and offset registers across multiple
1693 ** SELECT statements.
1694 */
1695 static void computeLimitRegisters( Parse pParse, Select p, int iBreak )
1696 {
1697 Vdbe v = null;
1698 int iLimit = 0;
1699 int iOffset;
1700 int addr1, n = 0;
1701 if ( p.iLimit != 0 )
1702 return;
1703  
1704 /*
1705 ** "LIMIT -1" always shows all rows. There is some
1706 ** contraversy about what the correct behavior should be.
1707 ** The current implementation interprets "LIMIT 0" to mean
1708 ** no rows.
1709 */
1710 sqlite3ExprCacheClear( pParse );
1711 Debug.Assert( p.pOffset == null || p.pLimit != null );
1712 if ( p.pLimit != null )
1713 {
1714 p.iLimit = iLimit = ++pParse.nMem;
1715 v = sqlite3GetVdbe( pParse );
1716 if ( NEVER( v == null ) )
1717 return; /* VDBE should have already been allocated */
1718 if ( sqlite3ExprIsInteger( p.pLimit, ref n ) != 0 )
1719 {
1720 sqlite3VdbeAddOp2( v, OP_Integer, n, iLimit );
1721 VdbeComment( v, "LIMIT counter" );
1722 if ( n == 0 )
1723 {
1724 sqlite3VdbeAddOp2( v, OP_Goto, 0, iBreak );
1725 }
1726 else
1727 {
1728 if ( p.nSelectRow > (double)n )
1729 p.nSelectRow = (double)n;
1730 }
1731 }
1732 else
1733 {
1734 sqlite3ExprCode( pParse, p.pLimit, iLimit );
1735 sqlite3VdbeAddOp1( v, OP_MustBeInt, iLimit );
1736 #if SQLITE_DEBUG
1737 VdbeComment( v, "LIMIT counter" );
1738 #endif
1739 sqlite3VdbeAddOp2( v, OP_IfZero, iLimit, iBreak );
1740 }
1741 if ( p.pOffset != null )
1742 {
1743 p.iOffset = iOffset = ++pParse.nMem;
1744 pParse.nMem++; /* Allocate an extra register for limit+offset */
1745 sqlite3ExprCode( pParse, p.pOffset, iOffset );
1746 sqlite3VdbeAddOp1( v, OP_MustBeInt, iOffset );
1747 #if SQLITE_DEBUG
1748 VdbeComment( v, "OFFSET counter" );
1749 #endif
1750 addr1 = sqlite3VdbeAddOp1( v, OP_IfPos, iOffset );
1751 sqlite3VdbeAddOp2( v, OP_Integer, 0, iOffset );
1752 sqlite3VdbeJumpHere( v, addr1 );
1753 sqlite3VdbeAddOp3( v, OP_Add, iLimit, iOffset, iOffset + 1 );
1754 #if SQLITE_DEBUG
1755 VdbeComment( v, "LIMIT+OFFSET" );
1756 #endif
1757 addr1 = sqlite3VdbeAddOp1( v, OP_IfPos, iLimit );
1758 sqlite3VdbeAddOp2( v, OP_Integer, -1, iOffset + 1 );
1759 sqlite3VdbeJumpHere( v, addr1 );
1760 }
1761 }
1762 }
1763  
1764 #if !SQLITE_OMIT_COMPOUND_SELECT
1765 /*
1766 ** Return the appropriate collating sequence for the iCol-th column of
1767 ** the result set for the compound-select statement "p". Return NULL if
1768 ** the column has no default collating sequence.
1769 **
1770 ** The collating sequence for the compound select is taken from the
1771 ** left-most term of the select that has a collating sequence.
1772 */
1773 static CollSeq multiSelectCollSeq( Parse pParse, Select p, int iCol )
1774 {
1775 CollSeq pRet;
1776 if ( p.pPrior != null )
1777 {
1778 pRet = multiSelectCollSeq( pParse, p.pPrior, iCol );
1779 }
1780 else
1781 {
1782 pRet = null;
1783 }
1784 Debug.Assert( iCol >= 0 );
1785 if ( pRet == null && iCol < p.pEList.nExpr )
1786 {
1787 pRet = sqlite3ExprCollSeq( pParse, p.pEList.a[iCol].pExpr );
1788 }
1789 return pRet;
1790 }
1791 #endif // * SQLITE_OMIT_COMPOUND_SELECT */
1792  
1793 /* Forward reference */
1794 //static int multiSelectOrderBy(
1795 // Parse* pParse, /* Parsing context */
1796 // Select* p, /* The right-most of SELECTs to be coded */
1797 // SelectDest* pDest /* What to do with query results */
1798 //);
1799  
1800 #if !SQLITE_OMIT_COMPOUND_SELECT
1801 /*
1802 ** This routine is called to process a compound query form from
1803 ** two or more separate queries using UNION, UNION ALL, EXCEPT, or
1804 ** INTERSECT
1805 **
1806 ** "p" points to the right-most of the two queries. the query on the
1807 ** left is p.pPrior. The left query could also be a compound query
1808 ** in which case this routine will be called recursively.
1809 **
1810 ** The results of the total query are to be written into a destination
1811 ** of type eDest with parameter iParm.
1812 **
1813 ** Example 1: Consider a three-way compound SQL statement.
1814 **
1815 ** SELECT a FROM t1 UNION SELECT b FROM t2 UNION SELECT c FROM t3
1816 **
1817 ** This statement is parsed up as follows:
1818 **
1819 ** SELECT c FROM t3
1820 ** |
1821 ** `----. SELECT b FROM t2
1822 ** |
1823 ** `-----. SELECT a FROM t1
1824 **
1825 ** The arrows in the diagram above represent the Select.pPrior pointer.
1826 ** So if this routine is called with p equal to the t3 query, then
1827 ** pPrior will be the t2 query. p.op will be TK_UNION in this case.
1828 **
1829 ** Notice that because of the way SQLite parses compound SELECTs, the
1830 ** individual selects always group from left to right.
1831 */
1832 static int multiSelect(
1833 Parse pParse, /* Parsing context */
1834 Select p, /* The right-most of SELECTs to be coded */
1835 SelectDest pDest /* What to do with query results */
1836 )
1837 {
1838 int rc = SQLITE_OK; /* Success code from a subroutine */
1839 Select pPrior; /* Another SELECT immediately to our left */
1840 Vdbe v; /* Generate code to this VDBE */
1841 SelectDest dest = new SelectDest(); /* Alternative data destination */
1842 Select pDelete = null; /* Chain of simple selects to delete */
1843 sqlite3 db; /* Database connection */
1844 #if !SQLITE_OMIT_EXPLAIN
1845 int iSub1 = 0; /* EQP id of left-hand query */
1846 int iSub2 = 0; /* EQP id of right-hand query */
1847 #endif
1848  
1849 /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only
1850 ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
1851 */
1852 Debug.Assert( p != null && p.pPrior != null ); /* Calling function guarantees this much */
1853 db = pParse.db;
1854 pPrior = p.pPrior;
1855 Debug.Assert( pPrior.pRightmost != pPrior );
1856 Debug.Assert( pPrior.pRightmost == p.pRightmost );
1857 dest = pDest;
1858 if ( pPrior.pOrderBy != null )
1859 {
1860 sqlite3ErrorMsg( pParse, "ORDER BY clause should come after %s not before",
1861 selectOpName( p.op ) );
1862 rc = 1;
1863 goto multi_select_end;
1864 }
1865 if ( pPrior.pLimit != null )
1866 {
1867 sqlite3ErrorMsg( pParse, "LIMIT clause should come after %s not before",
1868 selectOpName( p.op ) );
1869 rc = 1;
1870 goto multi_select_end;
1871 }
1872  
1873 v = sqlite3GetVdbe( pParse );
1874 Debug.Assert( v != null ); /* The VDBE already created by calling function */
1875  
1876 /* Create the destination temporary table if necessary
1877 */
1878 if ( dest.eDest == SRT_EphemTab )
1879 {
1880 Debug.Assert( p.pEList != null );
1881 sqlite3VdbeAddOp2( v, OP_OpenEphemeral, dest.iParm, p.pEList.nExpr );
1882 sqlite3VdbeChangeP5( v, BTREE_UNORDERED );
1883 dest.eDest = SRT_Table;
1884 }
1885  
1886 /* Make sure all SELECTs in the statement have the same number of elements
1887 ** in their result sets.
1888 */
1889 Debug.Assert( p.pEList != null && pPrior.pEList != null );
1890 if ( p.pEList.nExpr != pPrior.pEList.nExpr )
1891 {
1892 sqlite3ErrorMsg( pParse, "SELECTs to the left and right of %s" +
1893 " do not have the same number of result columns", selectOpName( p.op ) );
1894 rc = 1;
1895 goto multi_select_end;
1896 }
1897  
1898 /* Compound SELECTs that have an ORDER BY clause are handled separately.
1899 */
1900 if ( p.pOrderBy != null )
1901 {
1902 return multiSelectOrderBy( pParse, p, pDest );
1903 }
1904  
1905 /* Generate code for the left and right SELECT statements.
1906 */
1907 switch ( p.op )
1908 {
1909 case TK_ALL:
1910 {
1911 int addr = 0;
1912 int nLimit = 0;
1913 Debug.Assert( pPrior.pLimit == null );
1914 pPrior.pLimit = p.pLimit;
1915 pPrior.pOffset = p.pOffset;
1916 explainSetInteger( ref iSub1, pParse.iNextSelectId );
1917 rc = sqlite3Select( pParse, pPrior, ref dest );
1918 p.pLimit = null;
1919 p.pOffset = null;
1920 if ( rc != 0 )
1921 {
1922 goto multi_select_end;
1923 }
1924 p.pPrior = null;
1925 p.iLimit = pPrior.iLimit;
1926 p.iOffset = pPrior.iOffset;
1927 if ( p.iLimit != 0 )
1928 {
1929 addr = sqlite3VdbeAddOp1( v, OP_IfZero, p.iLimit );
1930 #if SQLITE_DEBUG
1931 VdbeComment( v, "Jump ahead if LIMIT reached" );
1932 #endif
1933 }
1934 explainSetInteger( ref iSub2, pParse.iNextSelectId );
1935 rc = sqlite3Select( pParse, p, ref dest );
1936 testcase( rc != SQLITE_OK );
1937 pDelete = p.pPrior;
1938 p.pPrior = pPrior;
1939 p.nSelectRow += pPrior.nSelectRow;
1940 if ( pPrior.pLimit != null
1941 && sqlite3ExprIsInteger( pPrior.pLimit, ref nLimit ) != 0
1942 && p.nSelectRow > (double)nLimit
1943 )
1944 {
1945 p.nSelectRow = (double)nLimit;
1946 }
1947 if ( addr != 0 )
1948 {
1949 sqlite3VdbeJumpHere( v, addr );
1950 }
1951 break;
1952 }
1953 case TK_EXCEPT:
1954 case TK_UNION:
1955 {
1956 int unionTab; /* VdbeCursor number of the temporary table holding result */
1957 u8 op = 0; /* One of the SRT_ operations to apply to self */
1958 int priorOp; /* The SRT_ operation to apply to prior selects */
1959 Expr pLimit, pOffset; /* Saved values of p.nLimit and p.nOffset */
1960 int addr;
1961 SelectDest uniondest = new SelectDest();
1962  
1963 testcase( p.op == TK_EXCEPT );
1964 testcase( p.op == TK_UNION );
1965 priorOp = SRT_Union;
1966 if ( dest.eDest == priorOp && ALWAYS( null == p.pLimit && null == p.pOffset ) )
1967 {
1968 /* We can reuse a temporary table generated by a SELECT to our
1969 ** right.
1970 */
1971 Debug.Assert( p.pRightmost != p ); /* Can only happen for leftward elements
1972 ** of a 3-way or more compound */
1973 Debug.Assert( p.pLimit == null ); /* Not allowed on leftward elements */
1974 Debug.Assert( p.pOffset == null ); /* Not allowed on leftward elements */
1975 unionTab = dest.iParm;
1976 }
1977 else
1978 {
1979 /* We will need to create our own temporary table to hold the
1980 ** intermediate results.
1981 */
1982 unionTab = pParse.nTab++;
1983 Debug.Assert( p.pOrderBy == null );
1984 addr = sqlite3VdbeAddOp2( v, OP_OpenEphemeral, unionTab, 0 );
1985 Debug.Assert( p.addrOpenEphm[0] == -1 );
1986 p.addrOpenEphm[0] = addr;
1987 p.pRightmost.selFlags |= SF_UsesEphemeral;
1988 Debug.Assert( p.pEList != null );
1989 }
1990  
1991 /* Code the SELECT statements to our left
1992 */
1993 Debug.Assert( pPrior.pOrderBy == null );
1994 sqlite3SelectDestInit( uniondest, priorOp, unionTab );
1995 explainSetInteger( ref iSub1, pParse.iNextSelectId );
1996 rc = sqlite3Select( pParse, pPrior, ref uniondest );
1997 if ( rc != 0 )
1998 {
1999 goto multi_select_end;
2000 }
2001  
2002 /* Code the current SELECT statement
2003 */
2004 if ( p.op == TK_EXCEPT )
2005 {
2006 op = SRT_Except;
2007 }
2008 else
2009 {
2010 Debug.Assert( p.op == TK_UNION );
2011 op = SRT_Union;
2012 }
2013 p.pPrior = null;
2014 pLimit = p.pLimit;
2015 p.pLimit = null;
2016 pOffset = p.pOffset;
2017 p.pOffset = null;
2018 uniondest.eDest = op;
2019 explainSetInteger( ref iSub2, pParse.iNextSelectId );
2020 rc = sqlite3Select( pParse, p, ref uniondest );
2021 testcase( rc != SQLITE_OK );
2022 /* Query flattening in sqlite3Select() might refill p.pOrderBy.
2023 ** Be sure to delete p.pOrderBy, therefore, to avoid a memory leak. */
2024 sqlite3ExprListDelete( db, ref p.pOrderBy );
2025 pDelete = p.pPrior;
2026 p.pPrior = pPrior;
2027 p.pOrderBy = null;
2028 if ( p.op == TK_UNION )
2029 p.nSelectRow += pPrior.nSelectRow;
2030 sqlite3ExprDelete( db, ref p.pLimit );
2031 p.pLimit = pLimit;
2032 p.pOffset = pOffset;
2033 p.iLimit = 0;
2034 p.iOffset = 0;
2035  
2036 /* Convert the data in the temporary table into whatever form
2037 ** it is that we currently need.
2038 */
2039 Debug.Assert( unionTab == dest.iParm || dest.eDest != priorOp );
2040 if ( dest.eDest != priorOp )
2041 {
2042 int iCont, iBreak, iStart;
2043 Debug.Assert( p.pEList != null );
2044 if ( dest.eDest == SRT_Output )
2045 {
2046 Select pFirst = p;
2047 while ( pFirst.pPrior != null )
2048 pFirst = pFirst.pPrior;
2049 generateColumnNames( pParse, null, pFirst.pEList );
2050 }
2051 iBreak = sqlite3VdbeMakeLabel( v );
2052 iCont = sqlite3VdbeMakeLabel( v );
2053 computeLimitRegisters( pParse, p, iBreak );
2054 sqlite3VdbeAddOp2( v, OP_Rewind, unionTab, iBreak );
2055 iStart = sqlite3VdbeCurrentAddr( v );
2056 selectInnerLoop( pParse, p, p.pEList, unionTab, p.pEList.nExpr,
2057 null, -1, dest, iCont, iBreak );
2058 sqlite3VdbeResolveLabel( v, iCont );
2059 sqlite3VdbeAddOp2( v, OP_Next, unionTab, iStart );
2060 sqlite3VdbeResolveLabel( v, iBreak );
2061 sqlite3VdbeAddOp2( v, OP_Close, unionTab, 0 );
2062 }
2063 break;
2064 }
2065 default:
2066 Debug.Assert( p.op == TK_INTERSECT );
2067 {
2068 int tab1, tab2;
2069 int iCont, iBreak, iStart;
2070 Expr pLimit, pOffset;
2071 int addr;
2072 SelectDest intersectdest = new SelectDest();
2073 int r1;
2074  
2075 /* INTERSECT is different from the others since it requires
2076 ** two temporary tables. Hence it has its own case. Begin
2077 ** by allocating the tables we will need.
2078 */
2079 tab1 = pParse.nTab++;
2080 tab2 = pParse.nTab++;
2081 Debug.Assert( p.pOrderBy == null );
2082  
2083 addr = sqlite3VdbeAddOp2( v, OP_OpenEphemeral, tab1, 0 );
2084 Debug.Assert( p.addrOpenEphm[0] == -1 );
2085 p.addrOpenEphm[0] = addr;
2086 p.pRightmost.selFlags |= SF_UsesEphemeral;
2087 Debug.Assert( p.pEList != null );
2088  
2089 /* Code the SELECTs to our left into temporary table "tab1".
2090 */
2091 sqlite3SelectDestInit( intersectdest, SRT_Union, tab1 );
2092 explainSetInteger( ref iSub1, pParse.iNextSelectId );
2093 rc = sqlite3Select( pParse, pPrior, ref intersectdest );
2094 if ( rc != 0 )
2095 {
2096 goto multi_select_end;
2097 }
2098  
2099 /* Code the current SELECT into temporary table "tab2"
2100 */
2101 addr = sqlite3VdbeAddOp2( v, OP_OpenEphemeral, tab2, 0 );
2102 Debug.Assert( p.addrOpenEphm[1] == -1 );
2103 p.addrOpenEphm[1] = addr;
2104 p.pPrior = null;
2105 pLimit = p.pLimit;
2106 p.pLimit = null;
2107 pOffset = p.pOffset;
2108 p.pOffset = null;
2109 intersectdest.iParm = tab2;
2110 explainSetInteger( ref iSub2, pParse.iNextSelectId );
2111 rc = sqlite3Select( pParse, p, ref intersectdest );
2112 testcase( rc != SQLITE_OK );
2113 p.pPrior = pPrior;
2114 if ( p.nSelectRow > pPrior.nSelectRow )
2115 p.nSelectRow = pPrior.nSelectRow;
2116 sqlite3ExprDelete( db, ref p.pLimit );
2117 p.pLimit = pLimit;
2118 p.pOffset = pOffset;
2119  
2120 /* Generate code to take the intersection of the two temporary
2121 ** tables.
2122 */
2123 Debug.Assert( p.pEList != null );
2124 if ( dest.eDest == SRT_Output )
2125 {
2126 Select pFirst = p;
2127 while ( pFirst.pPrior != null )
2128 pFirst = pFirst.pPrior;
2129 generateColumnNames( pParse, null, pFirst.pEList );
2130 }
2131 iBreak = sqlite3VdbeMakeLabel( v );
2132 iCont = sqlite3VdbeMakeLabel( v );
2133 computeLimitRegisters( pParse, p, iBreak );
2134 sqlite3VdbeAddOp2( v, OP_Rewind, tab1, iBreak );
2135 r1 = sqlite3GetTempReg( pParse );
2136 iStart = sqlite3VdbeAddOp2( v, OP_RowKey, tab1, r1 );
2137 sqlite3VdbeAddOp4Int( v, OP_NotFound, tab2, iCont, r1, 0 );
2138 sqlite3ReleaseTempReg( pParse, r1 );
2139 selectInnerLoop( pParse, p, p.pEList, tab1, p.pEList.nExpr,
2140 null, -1, dest, iCont, iBreak );
2141 sqlite3VdbeResolveLabel( v, iCont );
2142 sqlite3VdbeAddOp2( v, OP_Next, tab1, iStart );
2143 sqlite3VdbeResolveLabel( v, iBreak );
2144 sqlite3VdbeAddOp2( v, OP_Close, tab2, 0 );
2145 sqlite3VdbeAddOp2( v, OP_Close, tab1, 0 );
2146 break;
2147 }
2148 }
2149  
2150 explainComposite( pParse, p.op, iSub1, iSub2, p.op != TK_ALL );
2151  
2152 /* Compute collating sequences used by
2153 ** temporary tables needed to implement the compound select.
2154 ** Attach the KeyInfo structure to all temporary tables.
2155 **
2156 ** This section is run by the right-most SELECT statement only.
2157 ** SELECT statements to the left always skip this part. The right-most
2158 ** SELECT might also skip this part if it has no ORDER BY clause and
2159 ** no temp tables are required.
2160 */
2161 if ( ( p.selFlags & SF_UsesEphemeral ) != 0 )
2162 {
2163 int i; /* Loop counter */
2164 KeyInfo pKeyInfo; /* Collating sequence for the result set */
2165 Select pLoop; /* For looping through SELECT statements */
2166 CollSeq apColl; /* For looping through pKeyInfo.aColl[] */
2167 int nCol; /* Number of columns in result set */
2168  
2169 Debug.Assert( p.pRightmost == p );
2170 nCol = p.pEList.nExpr;
2171 pKeyInfo = new KeyInfo(); //sqlite3DbMallocZero(db,
2172 pKeyInfo.aColl = new CollSeq[nCol]; //sizeof(*pKeyInfo)+nCol*(CollSeq*.Length + 1));
2173 //if ( pKeyInfo == null )
2174 //{
2175 // rc = SQLITE_NOMEM;
2176 // goto multi_select_end;
2177 //}
2178  
2179 pKeyInfo.enc = db.aDbStatic[0].pSchema.enc;// ENC( pParse.db );
2180 pKeyInfo.nField = (u16)nCol;
2181  
2182 for ( i = 0; i < nCol; i++ )
2183 {//, apColl++){
2184 apColl = multiSelectCollSeq( pParse, p, i );
2185 if ( null == apColl )
2186 {
2187 apColl = db.pDfltColl;
2188 }
2189 pKeyInfo.aColl[i] = apColl;
2190 }
2191  
2192 for ( pLoop = p; pLoop != null; pLoop = pLoop.pPrior )
2193 {
2194 for ( i = 0; i < 2; i++ )
2195 {
2196 int addr = pLoop.addrOpenEphm[i];
2197 if ( addr < 0 )
2198 {
2199 /* If [0] is unused then [1] is also unused. So we can
2200 ** always safely abort as soon as the first unused slot is found */
2201 Debug.Assert( pLoop.addrOpenEphm[1] < 0 );
2202 break;
2203 }
2204 sqlite3VdbeChangeP2( v, addr, nCol );
2205 sqlite3VdbeChangeP4( v, addr, pKeyInfo, P4_KEYINFO );
2206 pLoop.addrOpenEphm[i] = -1;
2207 }
2208 }
2209 sqlite3DbFree( db, ref pKeyInfo );
2210 }
2211  
2212 multi_select_end:
2213 pDest.iMem = dest.iMem;
2214 pDest.nMem = dest.nMem;
2215 sqlite3SelectDelete( db, ref pDelete );
2216 return rc;
2217 }
2218 #endif // * SQLITE_OMIT_COMPOUND_SELECT */
2219  
2220 /*
2221 ** Code an output subroutine for a coroutine implementation of a
2222 ** SELECT statment.
2223 **
2224 ** The data to be output is contained in pIn.iMem. There are
2225 ** pIn.nMem columns to be output. pDest is where the output should
2226 ** be sent.
2227 **
2228 ** regReturn is the number of the register holding the subroutine
2229 ** return address.
2230 **
2231 ** If regPrev>0 then it is the first register in a vector that
2232 ** records the previous output. mem[regPrev] is a flag that is false
2233 ** if there has been no previous output. If regPrev>0 then code is
2234 ** generated to suppress duplicates. pKeyInfo is used for comparing
2235 ** keys.
2236 **
2237 ** If the LIMIT found in p.iLimit is reached, jump immediately to
2238 ** iBreak.
2239 */
2240 static int generateOutputSubroutine(
2241 Parse pParse, /* Parsing context */
2242 Select p, /* The SELECT statement */
2243 SelectDest pIn, /* Coroutine supplying data */
2244 SelectDest pDest, /* Where to send the data */
2245 int regReturn, /* The return address register */
2246 int regPrev, /* Previous result register. No uniqueness if 0 */
2247 KeyInfo pKeyInfo, /* For comparing with previous entry */
2248 int p4type, /* The p4 type for pKeyInfo */
2249 int iBreak /* Jump here if we hit the LIMIT */
2250 )
2251 {
2252 Vdbe v = pParse.pVdbe;
2253 int iContinue;
2254 int addr;
2255  
2256 addr = sqlite3VdbeCurrentAddr( v );
2257 iContinue = sqlite3VdbeMakeLabel( v );
2258  
2259 /* Suppress duplicates for UNION, EXCEPT, and INTERSECT
2260 */
2261 if ( regPrev != 0 )
2262 {
2263 int j1, j2;
2264 j1 = sqlite3VdbeAddOp1( v, OP_IfNot, regPrev );
2265 j2 = sqlite3VdbeAddOp4( v, OP_Compare, pIn.iMem, regPrev + 1, pIn.nMem,
2266 pKeyInfo, p4type );
2267 sqlite3VdbeAddOp3( v, OP_Jump, j2 + 2, iContinue, j2 + 2 );
2268 sqlite3VdbeJumpHere( v, j1 );
2269 sqlite3ExprCodeCopy( pParse, pIn.iMem, regPrev + 1, pIn.nMem );
2270 sqlite3VdbeAddOp2( v, OP_Integer, 1, regPrev );
2271 }
2272 //if ( pParse.db.mallocFailed != 0 ) return 0;
2273  
2274 /* Suppress the the first OFFSET entries if there is an OFFSET clause
2275 */
2276 codeOffset( v, p, iContinue );
2277  
2278 switch ( pDest.eDest )
2279 {
2280 /* Store the result as data using a unique key.
2281 */
2282 case SRT_Table:
2283 case SRT_EphemTab:
2284 {
2285 int r1 = sqlite3GetTempReg( pParse );
2286 int r2 = sqlite3GetTempReg( pParse );
2287 testcase( pDest.eDest == SRT_Table );
2288 testcase( pDest.eDest == SRT_EphemTab );
2289 sqlite3VdbeAddOp3( v, OP_MakeRecord, pIn.iMem, pIn.nMem, r1 );
2290 sqlite3VdbeAddOp2( v, OP_NewRowid, pDest.iParm, r2 );
2291 sqlite3VdbeAddOp3( v, OP_Insert, pDest.iParm, r1, r2 );
2292 sqlite3VdbeChangeP5( v, OPFLAG_APPEND );
2293 sqlite3ReleaseTempReg( pParse, r2 );
2294 sqlite3ReleaseTempReg( pParse, r1 );
2295 break;
2296 }
2297  
2298 #if !SQLITE_OMIT_SUBQUERY
2299 /* If we are creating a set for an "expr IN (SELECT ...)" construct,
2300 ** then there should be a single item on the stack. Write this
2301 ** item into the set table with bogus data.
2302 */
2303 case SRT_Set:
2304 {
2305 int r1;
2306 Debug.Assert( pIn.nMem == 1 );
2307 p.affinity =
2308 sqlite3CompareAffinity( p.pEList.a[0].pExpr, pDest.affinity );
2309 r1 = sqlite3GetTempReg( pParse );
2310 sqlite3VdbeAddOp4( v, OP_MakeRecord, pIn.iMem, 1, r1, p.affinity, 1 );
2311 sqlite3ExprCacheAffinityChange( pParse, pIn.iMem, 1 );
2312 sqlite3VdbeAddOp2( v, OP_IdxInsert, pDest.iParm, r1 );
2313 sqlite3ReleaseTempReg( pParse, r1 );
2314 break;
2315 }
2316  
2317 #if FALSE //* Never occurs on an ORDER BY query */
2318 /* If any row exist in the result set, record that fact and abort.
2319 */
2320 case SRT_Exists: {
2321 sqlite3VdbeAddOp2(v, OP_Integer, 1, pDest.iParm);
2322 /* The LIMIT clause will terminate the loop for us */
2323 break;
2324 }
2325 #endif
2326  
2327 /* If this is a scalar select that is part of an expression, then
2328 ** store the results in the appropriate memory cell and break out
2329 ** of the scan loop.
2330 */
2331 case SRT_Mem:
2332 {
2333 Debug.Assert( pIn.nMem == 1 );
2334 sqlite3ExprCodeMove( pParse, pIn.iMem, pDest.iParm, 1 );
2335 /* The LIMIT clause will jump out of the loop for us */
2336 break;
2337 }
2338 #endif //* #if !SQLITE_OMIT_SUBQUERY */
2339  
2340 /* The results are stored in a sequence of registers
2341 ** starting at pDest.iMem. Then the co-routine yields.
2342 */
2343 case SRT_Coroutine:
2344 {
2345 if ( pDest.iMem == 0 )
2346 {
2347 pDest.iMem = sqlite3GetTempRange( pParse, pIn.nMem );
2348 pDest.nMem = pIn.nMem;
2349 }
2350 sqlite3ExprCodeMove( pParse, pIn.iMem, pDest.iMem, pDest.nMem );
2351 sqlite3VdbeAddOp1( v, OP_Yield, pDest.iParm );
2352 break;
2353 }
2354  
2355 /* If none of the above, then the result destination must be
2356 ** SRT_Output. This routine is never called with any other
2357 ** destination other than the ones handled above or SRT_Output.
2358 **
2359 ** For SRT_Output, results are stored in a sequence of registers.
2360 ** Then the OP_ResultRow opcode is used to cause sqlite3_step() to
2361 ** return the next row of result.
2362 */
2363 default:
2364 {
2365 Debug.Assert( pDest.eDest == SRT_Output );
2366 sqlite3VdbeAddOp2( v, OP_ResultRow, pIn.iMem, pIn.nMem );
2367 sqlite3ExprCacheAffinityChange( pParse, pIn.iMem, pIn.nMem );
2368 break;
2369 }
2370 }
2371  
2372 /* Jump to the end of the loop if the LIMIT is reached.
2373 */
2374 if ( p.iLimit != 0 )
2375 {
2376 sqlite3VdbeAddOp3( v, OP_IfZero, p.iLimit, iBreak, -1 );
2377 }
2378  
2379 /* Generate the subroutine return
2380 */
2381 sqlite3VdbeResolveLabel( v, iContinue );
2382 sqlite3VdbeAddOp1( v, OP_Return, regReturn );
2383  
2384 return addr;
2385 }
2386  
2387 /*
2388 ** Alternative compound select code generator for cases when there
2389 ** is an ORDER BY clause.
2390 **
2391 ** We assume a query of the following form:
2392 **
2393 ** <selectA> <operator> <selectB> ORDER BY <orderbylist>
2394 **
2395 ** <operator> is one of UNION ALL, UNION, EXCEPT, or INTERSECT. The idea
2396 ** is to code both <selectA> and <selectB> with the ORDER BY clause as
2397 ** co-routines. Then run the co-routines in parallel and merge the results
2398 ** into the output. In addition to the two coroutines (called selectA and
2399 ** selectB) there are 7 subroutines:
2400 **
2401 ** outA: Move the output of the selectA coroutine into the output
2402 ** of the compound query.
2403 **
2404 ** outB: Move the output of the selectB coroutine into the output
2405 ** of the compound query. (Only generated for UNION and
2406 ** UNION ALL. EXCEPT and INSERTSECT never output a row that
2407 ** appears only in B.)
2408 **
2409 ** AltB: Called when there is data from both coroutines and A<B.
2410 **
2411 ** AeqB: Called when there is data from both coroutines and A==B.
2412 **
2413 ** AgtB: Called when there is data from both coroutines and A>B.
2414 **
2415 ** EofA: Called when data is exhausted from selectA.
2416 **
2417 ** EofB: Called when data is exhausted from selectB.
2418 **
2419 ** The implementation of the latter five subroutines depend on which
2420 ** <operator> is used:
2421 **
2422 **
2423 ** UNION ALL UNION EXCEPT INTERSECT
2424 ** ------------- ----------------- -------------- -----------------
2425 ** AltB: outA, nextA outA, nextA outA, nextA nextA
2426 **
2427 ** AeqB: outA, nextA nextA nextA outA, nextA
2428 **
2429 ** AgtB: outB, nextB outB, nextB nextB nextB
2430 **
2431 ** EofA: outB, nextB outB, nextB halt halt
2432 **
2433 ** EofB: outA, nextA outA, nextA outA, nextA halt
2434 **
2435 ** In the AltB, AeqB, and AgtB subroutines, an EOF on A following nextA
2436 ** causes an immediate jump to EofA and an EOF on B following nextB causes
2437 ** an immediate jump to EofB. Within EofA and EofB, and EOF on entry or
2438 ** following nextX causes a jump to the end of the select processing.
2439 **
2440 ** Duplicate removal in the UNION, EXCEPT, and INTERSECT cases is handled
2441 ** within the output subroutine. The regPrev register set holds the previously
2442 ** output value. A comparison is made against this value and the output
2443 ** is skipped if the next results would be the same as the previous.
2444 **
2445 ** The implementation plan is to implement the two coroutines and seven
2446 ** subroutines first, then put the control logic at the bottom. Like this:
2447 **
2448 ** goto Init
2449 ** coA: coroutine for left query (A)
2450 ** coB: coroutine for right query (B)
2451 ** outA: output one row of A
2452 ** outB: output one row of B (UNION and UNION ALL only)
2453 ** EofA: ...
2454 ** EofB: ...
2455 ** AltB: ...
2456 ** AeqB: ...
2457 ** AgtB: ...
2458 ** Init: initialize coroutine registers
2459 ** yield coA
2460 ** if eof(A) goto EofA
2461 ** yield coB
2462 ** if eof(B) goto EofB
2463 ** Cmpr: Compare A, B
2464 ** Jump AltB, AeqB, AgtB
2465 ** End: ...
2466 **
2467 ** We call AltB, AeqB, AgtB, EofA, and EofB "subroutines" but they are not
2468 ** actually called using Gosub and they do not Return. EofA and EofB loop
2469 ** until all data is exhausted then jump to the "end" labe. AltB, AeqB,
2470 ** and AgtB jump to either L2 or to one of EofA or EofB.
2471 */
2472 #if !SQLITE_OMIT_COMPOUND_SELECT
2473 static int multiSelectOrderBy(
2474 Parse pParse, /* Parsing context */
2475 Select p, /* The right-most of SELECTs to be coded */
2476 SelectDest pDest /* What to do with query results */
2477 )
2478 {
2479 int i, j; /* Loop counters */
2480 Select pPrior; /* Another SELECT immediately to our left */
2481 Vdbe v; /* Generate code to this VDBE */
2482 SelectDest destA = new SelectDest(); /* Destination for coroutine A */
2483 SelectDest destB = new SelectDest(); /* Destination for coroutine B */
2484 int regAddrA; /* Address register for select-A coroutine */
2485 int regEofA; /* Flag to indicate when select-A is complete */
2486 int regAddrB; /* Address register for select-B coroutine */
2487 int regEofB; /* Flag to indicate when select-B is complete */
2488 int addrSelectA; /* Address of the select-A coroutine */
2489 int addrSelectB; /* Address of the select-B coroutine */
2490 int regOutA; /* Address register for the output-A subroutine */
2491 int regOutB; /* Address register for the output-B subroutine */
2492 int addrOutA; /* Address of the output-A subroutine */
2493 int addrOutB = 0; /* Address of the output-B subroutine */
2494 int addrEofA; /* Address of the select-A-exhausted subroutine */
2495 int addrEofB; /* Address of the select-B-exhausted subroutine */
2496 int addrAltB; /* Address of the A<B subroutine */
2497 int addrAeqB; /* Address of the A==B subroutine */
2498 int addrAgtB; /* Address of the A>B subroutine */
2499 int regLimitA; /* Limit register for select-A */
2500 int regLimitB; /* Limit register for select-A */
2501 int regPrev; /* A range of registers to hold previous output */
2502 int savedLimit; /* Saved value of p.iLimit */
2503 int savedOffset; /* Saved value of p.iOffset */
2504 int labelCmpr; /* Label for the start of the merge algorithm */
2505 int labelEnd; /* Label for the end of the overall SELECT stmt */
2506 int j1; /* Jump instructions that get retargetted */
2507 int op; /* One of TK_ALL, TK_UNION, TK_EXCEPT, TK_INTERSECT */
2508 KeyInfo pKeyDup = null; /* Comparison information for duplicate removal */
2509 KeyInfo pKeyMerge; /* Comparison information for merging rows */
2510 sqlite3 db; /* Database connection */
2511 ExprList pOrderBy; /* The ORDER BY clause */
2512 int nOrderBy; /* Number of terms in the ORDER BY clause */
2513 int[] aPermute; /* Mapping from ORDER BY terms to result set columns */
2514 #if !SQLITE_OMIT_EXPLAIN
2515 int iSub1 = 0; /* EQP id of left-hand query */
2516 int iSub2 = 0; /* EQP id of right-hand query */
2517 #endif
2518  
2519 Debug.Assert( p.pOrderBy != null );
2520 Debug.Assert( pKeyDup == null ); /* "Managed" code needs this. Ticket #3382. */
2521 db = pParse.db;
2522 v = pParse.pVdbe;
2523 Debug.Assert( v != null ); /* Already thrown the error if VDBE alloc failed */
2524 labelEnd = sqlite3VdbeMakeLabel( v );
2525 labelCmpr = sqlite3VdbeMakeLabel( v );
2526  
2527  
2528 /* Patch up the ORDER BY clause
2529 */
2530 op = p.op;
2531 pPrior = p.pPrior;
2532 Debug.Assert( pPrior.pOrderBy == null );
2533 pOrderBy = p.pOrderBy;
2534 Debug.Assert( pOrderBy != null );
2535 nOrderBy = pOrderBy.nExpr;
2536  
2537 /* For operators other than UNION ALL we have to make sure that
2538 ** the ORDER BY clause covers every term of the result set. Add
2539 ** terms to the ORDER BY clause as necessary.
2540 */
2541 if ( op != TK_ALL )
2542 {
2543 for ( i = 1; /* db.mallocFailed == 0 && */ i <= p.pEList.nExpr; i++ )
2544 {
2545 ExprList_item pItem;
2546 for ( j = 0; j < nOrderBy; j++ )//, pItem++)
2547 {
2548 pItem = pOrderBy.a[j];
2549 Debug.Assert( pItem.iCol > 0 );
2550 if ( pItem.iCol == i )
2551 break;
2552 }
2553 if ( j == nOrderBy )
2554 {
2555 Expr pNew = sqlite3Expr( db, TK_INTEGER, null );
2556 //if ( pNew == null )
2557 // return SQLITE_NOMEM;
2558 pNew.flags |= EP_IntValue;
2559 pNew.u.iValue = i;
2560 pOrderBy = sqlite3ExprListAppend( pParse, pOrderBy, pNew );
2561 pOrderBy.a[nOrderBy++].iCol = (u16)i;
2562 }
2563 }
2564 }
2565  
2566 /* Compute the comparison permutation and keyinfo that is used with
2567 ** the permutation used to determine if the next
2568 ** row of results comes from selectA or selectB. Also add explicit
2569 ** collations to the ORDER BY clause terms so that when the subqueries
2570 ** to the right and the left are evaluated, they use the correct
2571 ** collation.
2572 */
2573 aPermute = new int[nOrderBy];// sqlite3DbMallocRaw( db, sizeof( int ) * nOrderBy );
2574 if ( aPermute != null )
2575 {
2576 ExprList_item pItem;
2577 for ( i = 0; i < nOrderBy; i++ )//, pItem++)
2578 {
2579 pItem = pOrderBy.a[i];
2580 Debug.Assert( pItem.iCol > 0 && pItem.iCol <= p.pEList.nExpr );
2581 aPermute[i] = pItem.iCol - 1;
2582 }
2583 pKeyMerge = new KeyInfo();// sqlite3DbMallocRaw(db, sizeof(*pKeyMerge)+nOrderBy*(sizeof(CollSeq)+1));
2584 if ( pKeyMerge != null )
2585 {
2586 pKeyMerge.aColl = new CollSeq[nOrderBy];
2587 pKeyMerge.aSortOrder = new byte[nOrderBy];//(u8)&pKeyMerge.aColl[nOrderBy];
2588 pKeyMerge.nField = (u16)nOrderBy;
2589 pKeyMerge.enc = ENC( db );
2590 for ( i = 0; i < nOrderBy; i++ )
2591 {
2592 CollSeq pColl;
2593 Expr pTerm = pOrderBy.a[i].pExpr;
2594 if ( ( pTerm.flags & EP_ExpCollate ) != 0 )
2595 {
2596 pColl = pTerm.pColl;
2597 }
2598 else
2599 {
2600 pColl = multiSelectCollSeq( pParse, p, aPermute[i] );
2601 pTerm.flags |= EP_ExpCollate;
2602 pTerm.pColl = pColl;
2603 }
2604 pKeyMerge.aColl[i] = pColl;
2605 pKeyMerge.aSortOrder[i] = (byte)pOrderBy.a[i].sortOrder;
2606 }
2607 }
2608 }
2609 else
2610 {
2611 pKeyMerge = null;
2612 }
2613  
2614 /* Reattach the ORDER BY clause to the query.
2615 */
2616 p.pOrderBy = pOrderBy;
2617 pPrior.pOrderBy = sqlite3ExprListDup( pParse.db, pOrderBy, 0 );
2618  
2619 /* Allocate a range of temporary registers and the KeyInfo needed
2620 ** for the logic that removes duplicate result rows when the
2621 ** operator is UNION, EXCEPT, or INTERSECT (but not UNION ALL).
2622 */
2623 if ( op == TK_ALL )
2624 {
2625 regPrev = 0;
2626 }
2627 else
2628 {
2629 int nExpr = p.pEList.nExpr;
2630 Debug.Assert( nOrderBy >= nExpr /*|| db.mallocFailed != 0 */ );
2631 regPrev = sqlite3GetTempRange( pParse, nExpr + 1 );
2632 sqlite3VdbeAddOp2( v, OP_Integer, 0, regPrev );
2633 pKeyDup = new KeyInfo();//sqlite3DbMallocZero(db,
2634 //sizeof(*pKeyDup) + nExpr*(sizeof(CollSeq)+1) );
2635 if ( pKeyDup != null )
2636 {
2637 pKeyDup.aColl = new CollSeq[nExpr];
2638 pKeyDup.aSortOrder = new byte[nExpr];//(u8)&pKeyDup.aColl[nExpr];
2639 pKeyDup.nField = (u16)nExpr;
2640 pKeyDup.enc = ENC( db );
2641 for ( i = 0; i < nExpr; i++ )
2642 {
2643 pKeyDup.aColl[i] = multiSelectCollSeq( pParse, p, i );
2644 pKeyDup.aSortOrder[i] = 0;
2645 }
2646 }
2647 }
2648  
2649 /* Separate the left and the right query from one another
2650 */
2651 p.pPrior = null;
2652 sqlite3ResolveOrderGroupBy( pParse, p, p.pOrderBy, "ORDER" );
2653 if ( pPrior.pPrior == null )
2654 {
2655 sqlite3ResolveOrderGroupBy( pParse, pPrior, pPrior.pOrderBy, "ORDER" );
2656 }
2657  
2658 /* Compute the limit registers */
2659 computeLimitRegisters( pParse, p, labelEnd );
2660 if ( p.iLimit != 0 && op == TK_ALL )
2661 {
2662 regLimitA = ++pParse.nMem;
2663 regLimitB = ++pParse.nMem;
2664 sqlite3VdbeAddOp2( v, OP_Copy, ( p.iOffset != 0 ) ? p.iOffset + 1 : p.iLimit,
2665 regLimitA );
2666 sqlite3VdbeAddOp2( v, OP_Copy, regLimitA, regLimitB );
2667 }
2668 else
2669 {
2670 regLimitA = regLimitB = 0;
2671 }
2672 sqlite3ExprDelete( db, ref p.pLimit );
2673 p.pLimit = null;
2674 sqlite3ExprDelete( db, ref p.pOffset );
2675 p.pOffset = null;
2676  
2677 regAddrA = ++pParse.nMem;
2678 regEofA = ++pParse.nMem;
2679 regAddrB = ++pParse.nMem;
2680 regEofB = ++pParse.nMem;
2681 regOutA = ++pParse.nMem;
2682 regOutB = ++pParse.nMem;
2683 sqlite3SelectDestInit( destA, SRT_Coroutine, regAddrA );
2684 sqlite3SelectDestInit( destB, SRT_Coroutine, regAddrB );
2685  
2686 /* Jump past the various subroutines and coroutines to the main
2687 ** merge loop
2688 */
2689 j1 = sqlite3VdbeAddOp0( v, OP_Goto );
2690 addrSelectA = sqlite3VdbeCurrentAddr( v );
2691  
2692  
2693 /* Generate a coroutine to evaluate the SELECT statement to the
2694 ** left of the compound operator - the "A" select.
2695 */
2696 VdbeNoopComment( v, "Begin coroutine for left SELECT" );
2697 pPrior.iLimit = regLimitA;
2698 explainSetInteger( ref iSub1, pParse.iNextSelectId );
2699 sqlite3Select( pParse, pPrior, ref destA );
2700 sqlite3VdbeAddOp2( v, OP_Integer, 1, regEofA );
2701 sqlite3VdbeAddOp1( v, OP_Yield, regAddrA );
2702 VdbeNoopComment( v, "End coroutine for left SELECT" );
2703  
2704 /* Generate a coroutine to evaluate the SELECT statement on
2705 ** the right - the "B" select
2706 */
2707 addrSelectB = sqlite3VdbeCurrentAddr( v );
2708 VdbeNoopComment( v, "Begin coroutine for right SELECT" );
2709 savedLimit = p.iLimit;
2710 savedOffset = p.iOffset;
2711 p.iLimit = regLimitB;
2712 p.iOffset = 0;
2713 explainSetInteger( ref iSub2, pParse.iNextSelectId );
2714 sqlite3Select( pParse, p, ref destB );
2715 p.iLimit = savedLimit;
2716 p.iOffset = savedOffset;
2717 sqlite3VdbeAddOp2( v, OP_Integer, 1, regEofB );
2718 sqlite3VdbeAddOp1( v, OP_Yield, regAddrB );
2719 VdbeNoopComment( v, "End coroutine for right SELECT" );
2720  
2721 /* Generate a subroutine that outputs the current row of the A
2722 ** select as the next output row of the compound select.
2723 */
2724 VdbeNoopComment( v, "Output routine for A" );
2725 addrOutA = generateOutputSubroutine( pParse,
2726 p, destA, pDest, regOutA,
2727 regPrev, pKeyDup, P4_KEYINFO_HANDOFF, labelEnd );
2728  
2729 /* Generate a subroutine that outputs the current row of the B
2730 ** select as the next output row of the compound select.
2731 */
2732 if ( op == TK_ALL || op == TK_UNION )
2733 {
2734 VdbeNoopComment( v, "Output routine for B" );
2735 addrOutB = generateOutputSubroutine( pParse,
2736 p, destB, pDest, regOutB,
2737 regPrev, pKeyDup, P4_KEYINFO_STATIC, labelEnd );
2738 }
2739  
2740 /* Generate a subroutine to run when the results from select A
2741 ** are exhausted and only data in select B remains.
2742 */
2743 VdbeNoopComment( v, "eof-A subroutine" );
2744 if ( op == TK_EXCEPT || op == TK_INTERSECT )
2745 {
2746 addrEofA = sqlite3VdbeAddOp2( v, OP_Goto, 0, labelEnd );
2747 }
2748 else
2749 {
2750 addrEofA = sqlite3VdbeAddOp2( v, OP_If, regEofB, labelEnd );
2751 sqlite3VdbeAddOp2( v, OP_Gosub, regOutB, addrOutB );
2752 sqlite3VdbeAddOp1( v, OP_Yield, regAddrB );
2753 sqlite3VdbeAddOp2( v, OP_Goto, 0, addrEofA );
2754 p.nSelectRow += pPrior.nSelectRow;
2755 }
2756  
2757 /* Generate a subroutine to run when the results from select B
2758 ** are exhausted and only data in select A remains.
2759 */
2760 if ( op == TK_INTERSECT )
2761 {
2762 addrEofB = addrEofA;
2763 if ( p.nSelectRow > pPrior.nSelectRow )
2764 p.nSelectRow = pPrior.nSelectRow;
2765 }
2766 else
2767 {
2768 VdbeNoopComment( v, "eof-B subroutine" );
2769 addrEofB = sqlite3VdbeAddOp2( v, OP_If, regEofA, labelEnd );
2770 sqlite3VdbeAddOp2( v, OP_Gosub, regOutA, addrOutA );
2771 sqlite3VdbeAddOp1( v, OP_Yield, regAddrA );
2772 sqlite3VdbeAddOp2( v, OP_Goto, 0, addrEofB );
2773 }
2774  
2775 /* Generate code to handle the case of A<B
2776 */
2777 VdbeNoopComment( v, "A-lt-B subroutine" );
2778 addrAltB = sqlite3VdbeAddOp2( v, OP_Gosub, regOutA, addrOutA );
2779 sqlite3VdbeAddOp1( v, OP_Yield, regAddrA );
2780 sqlite3VdbeAddOp2( v, OP_If, regEofA, addrEofA );
2781 sqlite3VdbeAddOp2( v, OP_Goto, 0, labelCmpr );
2782  
2783 /* Generate code to handle the case of A==B
2784 */
2785 if ( op == TK_ALL )
2786 {
2787 addrAeqB = addrAltB;
2788 }
2789 else if ( op == TK_INTERSECT )
2790 {
2791 addrAeqB = addrAltB;
2792 addrAltB++;
2793 }
2794 else
2795 {
2796 VdbeNoopComment( v, "A-eq-B subroutine" );
2797 addrAeqB =
2798 sqlite3VdbeAddOp1( v, OP_Yield, regAddrA );
2799 sqlite3VdbeAddOp2( v, OP_If, regEofA, addrEofA );
2800 sqlite3VdbeAddOp2( v, OP_Goto, 0, labelCmpr );
2801 }
2802  
2803 /* Generate code to handle the case of A>B
2804 */
2805 VdbeNoopComment( v, "A-gt-B subroutine" );
2806 addrAgtB = sqlite3VdbeCurrentAddr( v );
2807 if ( op == TK_ALL || op == TK_UNION )
2808 {
2809 sqlite3VdbeAddOp2( v, OP_Gosub, regOutB, addrOutB );
2810 }
2811 sqlite3VdbeAddOp1( v, OP_Yield, regAddrB );
2812 sqlite3VdbeAddOp2( v, OP_If, regEofB, addrEofB );
2813 sqlite3VdbeAddOp2( v, OP_Goto, 0, labelCmpr );
2814  
2815 /* This code runs once to initialize everything.
2816 */
2817 sqlite3VdbeJumpHere( v, j1 );
2818 sqlite3VdbeAddOp2( v, OP_Integer, 0, regEofA );
2819 sqlite3VdbeAddOp2( v, OP_Integer, 0, regEofB );
2820 sqlite3VdbeAddOp2( v, OP_Gosub, regAddrA, addrSelectA );
2821 sqlite3VdbeAddOp2( v, OP_Gosub, regAddrB, addrSelectB );
2822 sqlite3VdbeAddOp2( v, OP_If, regEofA, addrEofA );
2823 sqlite3VdbeAddOp2( v, OP_If, regEofB, addrEofB );
2824  
2825 /* Implement the main merge loop
2826 */
2827 sqlite3VdbeResolveLabel( v, labelCmpr );
2828 sqlite3VdbeAddOp4( v, OP_Permutation, 0, 0, 0, aPermute, P4_INTARRAY );
2829 sqlite3VdbeAddOp4( v, OP_Compare, destA.iMem, destB.iMem, nOrderBy,
2830 pKeyMerge, P4_KEYINFO_HANDOFF );
2831 sqlite3VdbeAddOp3( v, OP_Jump, addrAltB, addrAeqB, addrAgtB );
2832  
2833 /* Release temporary registers
2834 */
2835 if ( regPrev != 0 )
2836 {
2837 sqlite3ReleaseTempRange( pParse, regPrev, nOrderBy + 1 );
2838 }
2839  
2840 /* Jump to the this point in order to terminate the query.
2841 */
2842 sqlite3VdbeResolveLabel( v, labelEnd );
2843  
2844 /* Set the number of output columns
2845 */
2846 if ( pDest.eDest == SRT_Output )
2847 {
2848 Select pFirst = pPrior;
2849 while ( pFirst.pPrior != null )
2850 pFirst = pFirst.pPrior;
2851 generateColumnNames( pParse, null, pFirst.pEList );
2852 }
2853  
2854 /* Reassembly the compound query so that it will be freed correctly
2855 ** by the calling function */
2856 if ( p.pPrior != null )
2857 {
2858 sqlite3SelectDelete( db, ref p.pPrior );
2859 }
2860 p.pPrior = pPrior;
2861  
2862 /*** TBD: Insert subroutine calls to close cursors on incomplete
2863 **** subqueries ****/
2864 explainComposite( pParse, p.op, iSub1, iSub2, false );
2865 return SQLITE_OK;
2866 }
2867 #endif
2868 #if !(SQLITE_OMIT_SUBQUERY) || !(SQLITE_OMIT_VIEW)
2869 /* Forward Declarations */
2870 //static void substExprList(sqlite3*, ExprList*, int, ExprList);
2871 //static void substSelect(sqlite3*, Select *, int, ExprList );
2872  
2873 /*
2874 ** Scan through the expression pExpr. Replace every reference to
2875 ** a column in table number iTable with a copy of the iColumn-th
2876 ** entry in pEList. (But leave references to the ROWID column
2877 ** unchanged.)
2878 **
2879 ** This routine is part of the flattening procedure. A subquery
2880 ** whose result set is defined by pEList appears as entry in the
2881 ** FROM clause of a SELECT such that the VDBE cursor assigned to that
2882 ** FORM clause entry is iTable. This routine make the necessary
2883 ** changes to pExpr so that it refers directly to the source table
2884 ** of the subquery rather the result set of the subquery.
2885 */
2886 static Expr substExpr(
2887 sqlite3 db, /* Report malloc errors to this connection */
2888 Expr pExpr, /* Expr in which substitution occurs */
2889 int iTable, /* Table to be substituted */
2890 ExprList pEList /* Substitute expressions */
2891 )
2892 {
2893 if ( pExpr == null )
2894 return null;
2895 if ( pExpr.op == TK_COLUMN && pExpr.iTable == iTable )
2896 {
2897 if ( pExpr.iColumn < 0 )
2898 {
2899 pExpr.op = TK_NULL;
2900 }
2901 else
2902 {
2903 Expr pNew;
2904 Debug.Assert( pEList != null && pExpr.iColumn < pEList.nExpr );
2905 Debug.Assert( pExpr.pLeft == null && pExpr.pRight == null );
2906 pNew = sqlite3ExprDup( db, pEList.a[pExpr.iColumn].pExpr, 0 );
2907 if ( pExpr.pColl != null )
2908 {
2909 pNew.pColl = pExpr.pColl;
2910 }
2911 sqlite3ExprDelete( db, ref pExpr );
2912 pExpr = pNew;
2913 }
2914 }
2915 else
2916 {
2917 pExpr.pLeft = substExpr( db, pExpr.pLeft, iTable, pEList );
2918 pExpr.pRight = substExpr( db, pExpr.pRight, iTable, pEList );
2919 if ( ExprHasProperty( pExpr, EP_xIsSelect ) )
2920 {
2921 substSelect( db, pExpr.x.pSelect, iTable, pEList );
2922 }
2923 else
2924 {
2925 substExprList( db, pExpr.x.pList, iTable, pEList );
2926 }
2927 }
2928 return pExpr;
2929 }
2930  
2931 static void substExprList(
2932 sqlite3 db, /* Report malloc errors here */
2933 ExprList pList, /* List to scan and in which to make substitutes */
2934 int iTable, /* Table to be substituted */
2935 ExprList pEList /* Substitute values */
2936 )
2937 {
2938 int i;
2939 if ( pList == null )
2940 return;
2941 for ( i = 0; i < pList.nExpr; i++ )
2942 {
2943 pList.a[i].pExpr = substExpr( db, pList.a[i].pExpr, iTable, pEList );
2944 }
2945 }
2946  
2947 static void substSelect(
2948 sqlite3 db, /* Report malloc errors here */
2949 Select p, /* SELECT statement in which to make substitutions */
2950 int iTable, /* Table to be replaced */
2951 ExprList pEList /* Substitute values */
2952 )
2953 {
2954 SrcList pSrc;
2955 SrcList_item pItem;
2956 int i;
2957 if ( p == null )
2958 return;
2959 substExprList( db, p.pEList, iTable, pEList );
2960 substExprList( db, p.pGroupBy, iTable, pEList );
2961 substExprList( db, p.pOrderBy, iTable, pEList );
2962 p.pHaving = substExpr( db, p.pHaving, iTable, pEList );
2963 p.pWhere = substExpr( db, p.pWhere, iTable, pEList );
2964 substSelect( db, p.pPrior, iTable, pEList );
2965 pSrc = p.pSrc;
2966 Debug.Assert( pSrc != null ); /* Even for (SELECT 1) we have: pSrc!=0 but pSrc->nSrc==0 */
2967 if ( ALWAYS( pSrc ) )
2968 {
2969 for ( i = pSrc.nSrc; i > 0; i-- )//, pItem++ )
2970 {
2971 pItem = pSrc.a[pSrc.nSrc - i];
2972 substSelect( db, pItem.pSelect, iTable, pEList );
2973 }
2974 }
2975 }
2976 #endif //* !SQLITE_OMIT_SUBQUERY) || !SQLITE_OMIT_VIEW) */
2977  
2978 #if !(SQLITE_OMIT_SUBQUERY) || !(SQLITE_OMIT_VIEW)
2979 /*
2980 ** This routine attempts to flatten subqueries in order to speed
2981 ** execution. It returns 1 if it makes changes and 0 if no flattening
2982 ** occurs.
2983 **
2984 ** To understand the concept of flattening, consider the following
2985 ** query:
2986 **
2987 ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
2988 **
2989 ** The default way of implementing this query is to execute the
2990 ** subquery first and store the results in a temporary table, then
2991 ** run the outer query on that temporary table. This requires two
2992 ** passes over the data. Furthermore, because the temporary table
2993 ** has no indices, the WHERE clause on the outer query cannot be
2994 ** optimized.
2995 **
2996 ** This routine attempts to rewrite queries such as the above into
2997 ** a single flat select, like this:
2998 **
2999 ** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
3000 **
3001 ** The code generated for this simpification gives the same result
3002 ** but only has to scan the data once. And because indices might
3003 ** exist on the table t1, a complete scan of the data might be
3004 ** avoided.
3005 **
3006 ** Flattening is only attempted if all of the following are true:
3007 **
3008 ** (1) The subquery and the outer query do not both use aggregates.
3009 **
3010 ** (2) The subquery is not an aggregate or the outer query is not a join.
3011 **
3012 ** (3) The subquery is not the right operand of a left outer join
3013 ** (Originally ticket #306. Strengthened by ticket #3300)
3014 **
3015 ** (4) The subquery is not DISTINCT.
3016 **
3017 ** (*) At one point restrictions (4) and (5) defined a subset of DISTINCT
3018 ** sub-queries that were excluded from this optimization. Restriction
3019 ** (4) has since been expanded to exclude all DISTINCT subqueries.
3020 **
3021 ** (6) The subquery does not use aggregates or the outer query is not
3022 ** DISTINCT.
3023 **
3024 ** (7) The subquery has a FROM clause.
3025 **
3026 ** (8) The subquery does not use LIMIT or the outer query is not a join.
3027 **
3028 ** (9) The subquery does not use LIMIT or the outer query does not use
3029 ** aggregates.
3030 **
3031 ** (10) The subquery does not use aggregates or the outer query does not
3032 ** use LIMIT.
3033 **
3034 ** (11) The subquery and the outer query do not both have ORDER BY clauses.
3035 **
3036 ** (*) Not implemented. Subsumed into restriction (3). Was previously
3037 ** a separate restriction deriving from ticket #350.
3038 **
3039 ** (13) The subquery and outer query do not both use LIMIT.
3040 **
3041 ** (14) The subquery does not use OFFSET.
3042 **
3043 ** (15) The outer query is not part of a compound select or the
3044 ** subquery does not have a LIMIT clause.
3045 ** (See ticket #2339 and ticket [02a8e81d44]).
3046 **
3047 ** (16) The outer query is not an aggregate or the subquery does
3048 ** not contain ORDER BY. (Ticket #2942) This used to not matter
3049 ** until we introduced the group_concat() function.
3050 **
3051 ** (17) The sub-query is not a compound select, or it is a UNION ALL
3052 ** compound clause made up entirely of non-aggregate queries, and
3053 ** the parent query:
3054 **
3055 ** * is not itself part of a compound select,
3056 ** * is not an aggregate or DISTINCT query, and
3057 ** * has no other tables or sub-selects in the FROM clause.
3058 **
3059 ** The parent and sub-query may contain WHERE clauses. Subject to
3060 ** rules (11), (13) and (14), they may also contain ORDER BY,
3061 ** LIMIT and OFFSET clauses.
3062 **
3063 ** (18) If the sub-query is a compound select, then all terms of the
3064 ** ORDER by clause of the parent must be simple references to
3065 ** columns of the sub-query.
3066 **
3067 ** (19) The subquery does not use LIMIT or the outer query does not
3068 ** have a WHERE clause.
3069 **
3070 ** (20) If the sub-query is a compound select, then it must not use
3071 ** an ORDER BY clause. Ticket #3773. We could relax this constraint
3072 ** somewhat by saying that the terms of the ORDER BY clause must
3073 ** appear as unmodified result columns in the outer query. But
3074 ** have other optimizations in mind to deal with that case.
3075 **
3076 ** (21) The subquery does not use LIMIT or the outer query is not
3077 ** DISTINCT. (See ticket [752e1646fc]).
3078 **
3079 ** In this routine, the "p" parameter is a pointer to the outer query.
3080 ** The subquery is p.pSrc.a[iFrom]. isAgg is true if the outer query
3081 ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
3082 **
3083 ** If flattening is not attempted, this routine is a no-op and returns 0.
3084 ** If flattening is attempted this routine returns 1.
3085 **
3086 ** All of the expression analysis must occur on both the outer query and
3087 ** the subquery before this routine runs.
3088 */
3089 static int flattenSubquery(
3090 Parse pParse, /* Parsing context */
3091 Select p, /* The parent or outer SELECT statement */
3092 int iFrom, /* Index in p.pSrc.a[] of the inner subquery */
3093 bool isAgg, /* True if outer SELECT uses aggregate functions */
3094 bool subqueryIsAgg /* True if the subquery uses aggregate functions */
3095 )
3096 {
3097 string zSavedAuthContext = pParse.zAuthContext;
3098 Select pParent;
3099 Select pSub; /* The inner query or "subquery" */
3100 Select pSub1; /* Pointer to the rightmost select in sub-query */
3101 SrcList pSrc; /* The FROM clause of the outer query */
3102 SrcList pSubSrc; /* The FROM clause of the subquery */
3103 ExprList pList; /* The result set of the outer query */
3104 int iParent; /* VDBE cursor number of the pSub result set temp table */
3105 int i; /* Loop counter */
3106 Expr pWhere; /* The WHERE clause */
3107 SrcList_item pSubitem;/* The subquery */
3108 sqlite3 db = pParse.db;
3109  
3110 /* Check to see if flattening is permitted. Return 0 if not.
3111 */
3112 Debug.Assert( p != null );
3113 Debug.Assert( p.pPrior == null ); /* Unable to flatten compound queries */
3114 if ( ( db.flags & SQLITE_QueryFlattener ) != 0 )
3115 return 0;
3116 pSrc = p.pSrc;
3117 Debug.Assert( pSrc != null && iFrom >= 0 && iFrom < pSrc.nSrc );
3118 pSubitem = pSrc.a[iFrom];
3119 iParent = pSubitem.iCursor;
3120 pSub = pSubitem.pSelect;
3121 Debug.Assert( pSub != null );
3122 if ( isAgg && subqueryIsAgg )
3123 return 0; /* Restriction (1) */
3124 if ( subqueryIsAgg && pSrc.nSrc > 1 )
3125 return 0; /* Restriction (2) */
3126 pSubSrc = pSub.pSrc;
3127 Debug.Assert( pSubSrc != null );
3128 /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
3129 ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET
3130 ** because they could be computed at compile-time. But when LIMIT and OFFSET
3131 ** became arbitrary expressions, we were forced to add restrictions (13)
3132 ** and (14). */
3133 if ( pSub.pLimit != null && p.pLimit != null )
3134 return 0; /* Restriction (13) */
3135 if ( pSub.pOffset != null )
3136 return 0; /* Restriction (14) */
3137 if ( p.pRightmost != null && pSub.pLimit != null )
3138 {
3139 return 0; /* Restriction (15) */
3140 }
3141 if ( pSubSrc.nSrc == 0 )
3142 return 0; /* Restriction (7) */
3143 if ( ( pSub.selFlags & SF_Distinct ) != 0 )
3144 return 0; /* Restriction (5) */
3145 if ( pSub.pLimit != null && ( pSrc.nSrc > 1 || isAgg ) )
3146 {
3147 return 0; /* Restrictions (8)(9) */
3148 }
3149 if ( ( p.selFlags & SF_Distinct ) != 0 && subqueryIsAgg )
3150 {
3151 return 0; /* Restriction (6) */
3152 }
3153 if ( p.pOrderBy != null && pSub.pOrderBy != null )
3154 {
3155 return 0; /* Restriction (11) */
3156 }
3157 if ( isAgg && pSub.pOrderBy != null )
3158 return 0; /* Restriction (16) */
3159 if ( pSub.pLimit != null && p.pWhere != null )
3160 return 0; /* Restriction (19) */
3161 if ( pSub.pLimit != null && ( p.selFlags & SF_Distinct ) != 0 )
3162 {
3163 return 0; /* Restriction (21) */
3164 }
3165  
3166 /* OBSOLETE COMMENT 1:
3167 ** Restriction 3: If the subquery is a join, make sure the subquery is
3168 ** not used as the right operand of an outer join. Examples of why this
3169 ** is not allowed:
3170 **
3171 ** t1 LEFT OUTER JOIN (t2 JOIN t3)
3172 **
3173 ** If we flatten the above, we would get
3174 **
3175 ** (t1 LEFT OUTER JOIN t2) JOIN t3
3176 **
3177 ** which is not at all the same thing.
3178 **
3179 ** OBSOLETE COMMENT 2:
3180 ** Restriction 12: If the subquery is the right operand of a left outer
3181  
3182 /* Restriction 12: If the subquery is the right operand of a left outer
3183 ** join, make sure the subquery has no WHERE clause.
3184 ** An examples of why this is not allowed:
3185 **
3186 ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0)
3187 **
3188 ** If we flatten the above, we would get
3189 **
3190 ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0
3191 **
3192 ** But the t2.x>0 test will always fail on a NULL row of t2, which
3193 ** effectively converts the OUTER JOIN into an INNER JOIN.
3194 **
3195 ** THIS OVERRIDES OBSOLETE COMMENTS 1 AND 2 ABOVE:
3196 ** Ticket #3300 shows that flattening the right term of a LEFT JOIN
3197 ** is fraught with danger. Best to avoid the whole thing. If the
3198 ** subquery is the right term of a LEFT JOIN, then do not flatten.
3199 */
3200 if ( ( pSubitem.jointype & JT_OUTER ) != 0 )
3201 {
3202 return 0;
3203 }
3204  
3205 /* Restriction 17: If the sub-query is a compound SELECT, then it must
3206 ** use only the UNION ALL operator. And none of the simple select queries
3207 ** that make up the compound SELECT are allowed to be aggregate or distinct
3208 ** queries.
3209 */
3210 if ( pSub.pPrior != null )
3211 {
3212 if ( pSub.pOrderBy != null )
3213 {
3214 return 0; /* Restriction 20 */
3215 }
3216 if ( isAgg || ( p.selFlags & SF_Distinct ) != 0 || pSrc.nSrc != 1 )
3217 {
3218 return 0;
3219 }
3220 for ( pSub1 = pSub; pSub1 != null; pSub1 = pSub1.pPrior )
3221 {
3222 testcase( ( pSub1.selFlags & ( SF_Distinct | SF_Aggregate ) ) == SF_Distinct );
3223 testcase( ( pSub1.selFlags & ( SF_Distinct | SF_Aggregate ) ) == SF_Aggregate );
3224 if ( ( pSub1.selFlags & ( SF_Distinct | SF_Aggregate ) ) != 0
3225 || ( pSub1.pPrior != null && pSub1.op != TK_ALL )
3226 || NEVER( pSub1.pSrc == null ) || pSub1.pSrc.nSrc != 1
3227 )
3228 {
3229 return 0;
3230 }
3231 }
3232  
3233 /* Restriction 18. */
3234 if ( p.pOrderBy != null )
3235 {
3236 int ii;
3237 for ( ii = 0; ii < p.pOrderBy.nExpr; ii++ )
3238 {
3239 if ( p.pOrderBy.a[ii].iCol == 0 )
3240 return 0;
3241 }
3242 }
3243 }
3244  
3245 /***** If we reach this point, flattening is permitted. *****/
3246  
3247 /* Authorize the subquery */
3248 pParse.zAuthContext = pSubitem.zName;
3249 sqlite3AuthCheck( pParse, SQLITE_SELECT, null, null, null );
3250 pParse.zAuthContext = zSavedAuthContext;
3251  
3252 /* If the sub-query is a compound SELECT statement, then (by restrictions
3253 ** 17 and 18 above) it must be a UNION ALL and the parent query must
3254 ** be of the form:
3255 **
3256 ** SELECT <expr-list> FROM (<sub-query>) <where-clause>
3257 **
3258 ** followed by any ORDER BY, LIMIT and/or OFFSET clauses. This block
3259 ** creates N-1 copies of the parent query without any ORDER BY, LIMIT or
3260 ** OFFSET clauses and joins them to the left-hand-side of the original
3261 ** using UNION ALL operators. In this case N is the number of simple
3262 ** select statements in the compound sub-query.
3263 **
3264 ** Example:
3265 **
3266 ** SELECT a+1 FROM (
3267 ** SELECT x FROM tab
3268 ** UNION ALL
3269 ** SELECT y FROM tab
3270 ** UNION ALL
3271 ** SELECT abs(z*2) FROM tab2
3272 ** ) WHERE a!=5 ORDER BY 1
3273 **
3274 ** Transformed into:
3275 **
3276 ** SELECT x+1 FROM tab WHERE x+1!=5
3277 ** UNION ALL
3278 ** SELECT y+1 FROM tab WHERE y+1!=5
3279 ** UNION ALL
3280 ** SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5
3281 ** ORDER BY 1
3282 **
3283 ** We call this the "compound-subquery flattening".
3284 */
3285 for ( pSub = pSub.pPrior; pSub != null; pSub = pSub.pPrior )
3286 {
3287 Select pNew;
3288 ExprList pOrderBy = p.pOrderBy;
3289 Expr pLimit = p.pLimit;
3290 Select pPrior = p.pPrior;
3291 p.pOrderBy = null;
3292 p.pSrc = null;
3293 p.pPrior = null;
3294 p.pLimit = null;
3295 pNew = sqlite3SelectDup( db, p, 0 );
3296 p.pLimit = pLimit;
3297 p.pOrderBy = pOrderBy;
3298 p.pSrc = pSrc;
3299 p.op = TK_ALL;
3300 p.pRightmost = null;
3301 if ( pNew == null )
3302 {
3303 pNew = pPrior;
3304 }
3305 else
3306 {
3307 pNew.pPrior = pPrior;
3308 pNew.pRightmost = null;
3309 }
3310 p.pPrior = pNew;
3311 // if ( db.mallocFailed != 0 ) return 1;
3312 }
3313  
3314 /* Begin flattening the iFrom-th entry of the FROM clause
3315 ** in the outer query.
3316 */
3317 pSub = pSub1 = pSubitem.pSelect;
3318 /* Delete the transient table structure associated with the
3319 ** subquery
3320 */
3321  
3322 sqlite3DbFree( db, ref pSubitem.zDatabase );
3323 sqlite3DbFree( db, ref pSubitem.zName );
3324 sqlite3DbFree( db, ref pSubitem.zAlias );
3325 pSubitem.zDatabase = null;
3326 pSubitem.zName = null;
3327 pSubitem.zAlias = null;
3328 pSubitem.pSelect = null;
3329 /* Defer deleting the Table object associated with the
3330 ** subquery until code generation is
3331 ** complete, since there may still exist Expr.pTab entries that
3332 ** refer to the subquery even after flattening. Ticket #3346.
3333 **
3334 ** pSubitem->pTab is always non-NULL by test restrictions and tests above.
3335 */
3336 if ( ALWAYS( pSubitem.pTab != null ) )
3337 {
3338 Table pTabToDel = pSubitem.pTab;
3339 if ( pTabToDel.nRef == 1 )
3340 {
3341 Parse pToplevel = sqlite3ParseToplevel( pParse );
3342 pTabToDel.pNextZombie = pToplevel.pZombieTab;
3343 pToplevel.pZombieTab = pTabToDel;
3344 }
3345 else
3346 {
3347 pTabToDel.nRef--;
3348 }
3349 pSubitem.pTab = null;
3350 }
3351  
3352 /* The following loop runs once for each term in a compound-subquery
3353 ** flattening (as described above). If we are doing a different kind
3354 ** of flattening - a flattening other than a compound-subquery flattening -
3355 ** then this loop only runs once.
3356 **
3357 ** This loop moves all of the FROM elements of the subquery into the
3358 ** the FROM clause of the outer query. Before doing this, remember
3359 ** the cursor number for the original outer query FROM element in
3360 ** iParent. The iParent cursor will never be used. Subsequent code
3361 ** will scan expressions looking for iParent references and replace
3362 ** those references with expressions that resolve to the subquery FROM
3363 ** elements we are now copying in.
3364 */
3365 for ( pParent = p; pParent != null; pParent = pParent.pPrior, pSub = pSub.pPrior )
3366 {
3367 int nSubSrc;
3368 u8 jointype = 0;
3369 pSubSrc = pSub.pSrc; /* FROM clause of subquery */
3370 nSubSrc = pSubSrc.nSrc; /* Number of terms in subquery FROM clause */
3371 pSrc = pParent.pSrc; /* FROM clause of the outer query */
3372  
3373 if ( pSrc != null )
3374 {
3375 Debug.Assert( pParent == p ); /* First time through the loop */
3376 jointype = pSubitem.jointype;
3377 }
3378 else
3379 {
3380 Debug.Assert( pParent != p ); /* 2nd and subsequent times through the loop */
3381 pSrc = pParent.pSrc = sqlite3SrcListAppend( db, null, null, null );
3382 //if ( pSrc == null )
3383 //{
3384 // //Debug.Assert( db.mallocFailed != 0 );
3385 // break;
3386 //}
3387 }
3388  
3389 /* The subquery uses a single slot of the FROM clause of the outer
3390 ** query. If the subquery has more than one element in its FROM clause,
3391 ** then expand the outer query to make space for it to hold all elements
3392 ** of the subquery.
3393 **
3394 ** Example:
3395 **
3396 ** SELECT * FROM tabA, (SELECT * FROM sub1, sub2), tabB;
3397 **
3398 ** The outer query has 3 slots in its FROM clause. One slot of the
3399 ** outer query (the middle slot) is used by the subquery. The next
3400 ** block of code will expand the out query to 4 slots. The middle
3401 ** slot is expanded to two slots in order to make space for the
3402 ** two elements in the FROM clause of the subquery.
3403 */
3404 if ( nSubSrc > 1 )
3405 {
3406 pParent.pSrc = pSrc = sqlite3SrcListEnlarge( db, pSrc, nSubSrc - 1, iFrom + 1 );
3407 //if ( db.mallocFailed != 0 )
3408 //{
3409 // break;
3410 //}
3411 }
3412  
3413 /* Transfer the FROM clause terms from the subquery into the
3414 ** outer query.
3415 */
3416 for ( i = 0; i < nSubSrc; i++ )
3417 {
3418 sqlite3IdListDelete( db, ref pSrc.a[i + iFrom].pUsing );
3419 pSrc.a[i + iFrom] = pSubSrc.a[i];
3420 pSubSrc.a[i] = new SrcList_item();//memset(pSubSrc.a[i], 0, sizeof(pSubSrc.a[i]));
3421 }
3422 pSubitem = pSrc.a[iFrom]; // Reset for C#
3423 pSrc.a[iFrom].jointype = jointype;
3424  
3425 /* Now begin substituting subquery result set expressions for
3426 ** references to the iParent in the outer query.
3427 **
3428 ** Example:
3429 **
3430 ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
3431 ** \ \_____________ subquery __________/ /
3432 ** \_____________________ outer query ______________________________/
3433 **
3434 ** We look at every expression in the outer query and every place we see
3435 ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
3436 */
3437 pList = pParent.pEList;
3438 for ( i = 0; i < pList.nExpr; i++ )
3439 {
3440 if ( pList.a[i].zName == null )
3441 {
3442 string zSpan = pList.a[i].zSpan;
3443 if ( ALWAYS( zSpan ) )
3444 {
3445 pList.a[i].zName = zSpan;// sqlite3DbStrDup( db, zSpan );
3446 }
3447 }
3448 }
3449 substExprList( db, pParent.pEList, iParent, pSub.pEList );
3450 if ( isAgg )
3451 {
3452 substExprList( db, pParent.pGroupBy, iParent, pSub.pEList );
3453 pParent.pHaving = substExpr( db, pParent.pHaving, iParent, pSub.pEList );
3454 }
3455 if ( pSub.pOrderBy != null )
3456 {
3457 Debug.Assert( pParent.pOrderBy == null );
3458 pParent.pOrderBy = pSub.pOrderBy;
3459 pSub.pOrderBy = null;
3460 }
3461 else if ( pParent.pOrderBy != null )
3462 {
3463 substExprList( db, pParent.pOrderBy, iParent, pSub.pEList );
3464 }
3465 if ( pSub.pWhere != null )
3466 {
3467 pWhere = sqlite3ExprDup( db, pSub.pWhere, 0 );
3468 }
3469 else
3470 {
3471 pWhere = null;
3472 }
3473 if ( subqueryIsAgg )
3474 {
3475 Debug.Assert( pParent.pHaving == null );
3476 pParent.pHaving = pParent.pWhere;
3477 pParent.pWhere = pWhere;
3478 pParent.pHaving = substExpr( db, pParent.pHaving, iParent, pSub.pEList );
3479 pParent.pHaving = sqlite3ExprAnd( db, pParent.pHaving,
3480 sqlite3ExprDup( db, pSub.pHaving, 0 ) );
3481 Debug.Assert( pParent.pGroupBy == null );
3482 pParent.pGroupBy = sqlite3ExprListDup( db, pSub.pGroupBy, 0 );
3483 }
3484 else
3485 {
3486 pParent.pWhere = substExpr( db, pParent.pWhere, iParent, pSub.pEList );
3487 pParent.pWhere = sqlite3ExprAnd( db, pParent.pWhere, pWhere );
3488 }
3489  
3490 /* The flattened query is distinct if either the inner or the
3491 ** outer query is distinct.
3492 */
3493 pParent.selFlags = (u16)( pParent.selFlags | pSub.selFlags & SF_Distinct );
3494  
3495 /*
3496 ** SELECT ... FROM (SELECT ... LIMIT a OFFSET b) LIMIT x OFFSET y;
3497 **
3498 ** One is tempted to try to add a and b to combine the limits. But this
3499 ** does not work if either limit is negative.
3500 */
3501 if ( pSub.pLimit != null )
3502 {
3503 pParent.pLimit = pSub.pLimit;
3504 pSub.pLimit = null;
3505 }
3506 }
3507  
3508 /* Finially, delete what is left of the subquery and return
3509 ** success.
3510 */
3511 sqlite3SelectDelete( db, ref pSub );
3512 sqlite3SelectDelete( db, ref pSub1 );
3513 return 1;
3514 }
3515 #endif //* !SQLITE_OMIT_SUBQUERY) || !SQLITE_OMIT_VIEW) */
3516  
3517 /*
3518 ** Analyze the SELECT statement passed as an argument to see if it
3519 ** is a min() or max() query. Return WHERE_ORDERBY_MIN or WHERE_ORDERBY_MAX if
3520 ** it is, or 0 otherwise. At present, a query is considered to be
3521 ** a min()/max() query if:
3522 **
3523 ** 1. There is a single object in the FROM clause.
3524 **
3525 ** 2. There is a single expression in the result set, and it is
3526 ** either min(x) or max(x), where x is a column reference.
3527 */
3528 static u8 minMaxQuery( Select p )
3529 {
3530 Expr pExpr;
3531 ExprList pEList = p.pEList;
3532  
3533 if ( pEList.nExpr != 1 )
3534 return WHERE_ORDERBY_NORMAL;
3535 pExpr = pEList.a[0].pExpr;
3536 if ( pExpr.op != TK_AGG_FUNCTION )
3537 return 0;
3538 if ( NEVER( ExprHasProperty( pExpr, EP_xIsSelect ) ) )
3539 return 0;
3540 pEList = pExpr.x.pList;
3541 if ( pEList == null || pEList.nExpr != 1 )
3542 return 0;
3543 if ( pEList.a[0].pExpr.op != TK_AGG_COLUMN )
3544 return WHERE_ORDERBY_NORMAL;
3545 Debug.Assert( !ExprHasProperty( pExpr, EP_IntValue ) );
3546 if ( pExpr.u.zToken.Equals( "min", StringComparison.OrdinalIgnoreCase ) )
3547 {
3548 return WHERE_ORDERBY_MIN;
3549 }
3550 else if ( pExpr.u.zToken.Equals( "max", StringComparison.OrdinalIgnoreCase ) )
3551 {
3552 return WHERE_ORDERBY_MAX;
3553 }
3554 return WHERE_ORDERBY_NORMAL;
3555 }
3556  
3557 /*
3558 ** The select statement passed as the first argument is an aggregate query.
3559 ** The second argment is the associated aggregate-info object. This
3560 ** function tests if the SELECT is of the form:
3561 **
3562 ** SELECT count() FROM <tbl>
3563 **
3564 ** where table is a database table, not a sub-select or view. If the query
3565 ** does match this pattern, then a pointer to the Table object representing
3566 ** <tbl> is returned. Otherwise, 0 is returned.
3567 */
3568 static Table isSimpleCount( Select p, AggInfo pAggInfo )
3569 {
3570 Table pTab;
3571 Expr pExpr;
3572  
3573 Debug.Assert( null == p.pGroupBy );
3574  
3575 if ( p.pWhere != null || p.pEList.nExpr != 1
3576 || p.pSrc.nSrc != 1 || p.pSrc.a[0].pSelect != null
3577 )
3578 {
3579 return null;
3580 }
3581 pTab = p.pSrc.a[0].pTab;
3582 pExpr = p.pEList.a[0].pExpr;
3583 Debug.Assert( pTab != null && null == pTab.pSelect && pExpr != null );
3584  
3585 if ( IsVirtual( pTab ) )
3586 return null;
3587 if ( pExpr.op != TK_AGG_FUNCTION )
3588 return null;
3589 if ( ( pAggInfo.aFunc[0].pFunc.flags & SQLITE_FUNC_COUNT ) == 0 )
3590 return null;
3591 if ( ( pExpr.flags & EP_Distinct ) != 0 )
3592 return null;
3593  
3594 return pTab;
3595 }
3596  
3597 /*
3598 ** If the source-list item passed as an argument was augmented with an
3599 ** INDEXED BY clause, then try to locate the specified index. If there
3600 ** was such a clause and the named index cannot be found, return
3601 ** SQLITE_ERROR and leave an error in pParse. Otherwise, populate
3602 ** pFrom.pIndex and return SQLITE_OK.
3603 */
3604 static int sqlite3IndexedByLookup( Parse pParse, SrcList_item pFrom )
3605 {
3606 if ( pFrom.pTab != null && pFrom.zIndex != null && pFrom.zIndex.Length != 0 )
3607 {
3608 Table pTab = pFrom.pTab;
3609 string zIndex = pFrom.zIndex;
3610 Index pIdx;
3611 for ( pIdx = pTab.pIndex;
3612 pIdx != null && !pIdx.zName.Equals( zIndex, StringComparison.OrdinalIgnoreCase );
3613 pIdx = pIdx.pNext
3614 )
3615 ;
3616 if ( null == pIdx )
3617 {
3618 sqlite3ErrorMsg( pParse, "no such index: %s", zIndex );
3619 pParse.checkSchema = 1;
3620 return SQLITE_ERROR;
3621 }
3622 pFrom.pIndex = pIdx;
3623 }
3624 return SQLITE_OK;
3625 }
3626  
3627 /*
3628 ** This routine is a Walker callback for "expanding" a SELECT statement.
3629 ** "Expanding" means to do the following:
3630 **
3631 ** (1) Make sure VDBE cursor numbers have been assigned to every
3632 ** element of the FROM clause.
3633 **
3634 ** (2) Fill in the pTabList.a[].pTab fields in the SrcList that
3635 ** defines FROM clause. When views appear in the FROM clause,
3636 ** fill pTabList.a[].x.pSelect with a copy of the SELECT statement
3637 ** that implements the view. A copy is made of the view's SELECT
3638 ** statement so that we can freely modify or delete that statement
3639 ** without worrying about messing up the presistent representation
3640 ** of the view.
3641 **
3642 ** (3) Add terms to the WHERE clause to accomodate the NATURAL keyword
3643 ** on joins and the ON and USING clause of joins.
3644 **
3645 ** (4) Scan the list of columns in the result set (pEList) looking
3646 ** for instances of the "*" operator or the TABLE.* operator.
3647 ** If found, expand each "*" to be every column in every table
3648 ** and TABLE.* to be every column in TABLE.
3649 **
3650 */
3651 static int selectExpander( Walker pWalker, Select p )
3652 {
3653 Parse pParse = pWalker.pParse;
3654 int i, j, k;
3655 SrcList pTabList;
3656 ExprList pEList;
3657 SrcList_item pFrom;
3658 sqlite3 db = pParse.db;
3659  
3660 //if ( db.mallocFailed != 0 )
3661 //{
3662 // return WRC_Abort;
3663 //}
3664 if ( NEVER( p.pSrc == null ) || ( p.selFlags & SF_Expanded ) != 0 )
3665 {
3666 return WRC_Prune;
3667 }
3668 p.selFlags |= SF_Expanded;
3669 pTabList = p.pSrc;
3670 pEList = p.pEList;
3671  
3672 /* Make sure cursor numbers have been assigned to all entries in
3673 ** the FROM clause of the SELECT statement.
3674 */
3675 sqlite3SrcListAssignCursors( pParse, pTabList );
3676  
3677 /* Look up every table named in the FROM clause of the select. If
3678 ** an entry of the FROM clause is a subquery instead of a table or view,
3679 ** then create a transient table ure to describe the subquery.
3680 */
3681 for ( i = 0; i < pTabList.nSrc; i++ )// pFrom++ )
3682 {
3683 pFrom = pTabList.a[i];
3684 Table pTab;
3685 if ( pFrom.pTab != null )
3686 {
3687 /* This statement has already been prepared. There is no need
3688 ** to go further. */
3689 Debug.Assert( i == 0 );
3690 return WRC_Prune;
3691 }
3692 if ( pFrom.zName == null )
3693 {
3694 #if !SQLITE_OMIT_SUBQUERY
3695 Select pSel = pFrom.pSelect;
3696 /* A sub-query in the FROM clause of a SELECT */
3697 Debug.Assert( pSel != null );
3698 Debug.Assert( pFrom.pTab == null );
3699 sqlite3WalkSelect( pWalker, pSel );
3700 pFrom.pTab = pTab = new Table();// sqlite3DbMallocZero( db, sizeof( Table ) );
3701 if ( pTab == null )
3702 return WRC_Abort;
3703 pTab.nRef = 1;
3704 pTab.zName = sqlite3MPrintf( db, "sqlite_subquery_%p_", pTab );
3705 while ( pSel.pPrior != null )
3706 {
3707 pSel = pSel.pPrior;
3708 }
3709 selectColumnsFromExprList( pParse, pSel.pEList, ref pTab.nCol, ref pTab.aCol );
3710 pTab.iPKey = -1;
3711 pTab.nRowEst = 1000000;
3712 pTab.tabFlags |= TF_Ephemeral;
3713 #endif
3714 }
3715 else
3716 {
3717 /* An ordinary table or view name in the FROM clause */
3718 Debug.Assert( pFrom.pTab == null );
3719 pFrom.pTab = pTab =
3720 sqlite3LocateTable( pParse, 0, pFrom.zName, pFrom.zDatabase );
3721 if ( pTab == null )
3722 return WRC_Abort;
3723 pTab.nRef++;
3724 #if !(SQLITE_OMIT_VIEW) || !(SQLITE_OMIT_VIRTUALTABLE)
3725 if ( pTab.pSelect != null || IsVirtual( pTab ) )
3726 {
3727 /* We reach here if the named table is a really a view */
3728 if ( sqlite3ViewGetColumnNames( pParse, pTab ) != 0 )
3729 return WRC_Abort;
3730  
3731 pFrom.pSelect = sqlite3SelectDup( db, pTab.pSelect, 0 );
3732 sqlite3WalkSelect( pWalker, pFrom.pSelect );
3733 }
3734 #endif
3735 }
3736 /* Locate the index named by the INDEXED BY clause, if any. */
3737 if ( sqlite3IndexedByLookup( pParse, pFrom ) != 0 )
3738 {
3739 return WRC_Abort;
3740 }
3741 }
3742  
3743 /* Process NATURAL keywords, and ON and USING clauses of joins.
3744 */
3745 if ( /* db.mallocFailed != 0 || */ sqliteProcessJoin( pParse, p ) != 0 )
3746 {
3747 return WRC_Abort;
3748 }
3749  
3750 /* For every "*" that occurs in the column list, insert the names of
3751 ** all columns in all tables. And for every TABLE.* insert the names
3752 ** of all columns in TABLE. The parser inserted a special expression
3753 ** with the TK_ALL operator for each "*" that it found in the column list.
3754 ** The following code just has to locate the TK_ALL expressions and expand
3755 ** each one to the list of all columns in all tables.
3756 **
3757 ** The first loop just checks to see if there are any "*" operators
3758 ** that need expanding.
3759 */
3760 for ( k = 0; k < pEList.nExpr; k++ )
3761 {
3762 Expr pE = pEList.a[k].pExpr;
3763 if ( pE.op == TK_ALL )
3764 break;
3765 Debug.Assert( pE.op != TK_DOT || pE.pRight != null );
3766 Debug.Assert( pE.op != TK_DOT || ( pE.pLeft != null && pE.pLeft.op == TK_ID ) );
3767 if ( pE.op == TK_DOT && pE.pRight.op == TK_ALL )
3768 break;
3769 }
3770 if ( k < pEList.nExpr )
3771 {
3772 /*
3773 ** If we get here it means the result set contains one or more "*"
3774 ** operators that need to be expanded. Loop through each expression
3775 ** in the result set and expand them one by one.
3776 */
3777 ExprList_item[] a = pEList.a;
3778 ExprList pNew = null;
3779 int flags = pParse.db.flags;
3780 bool longNames = ( flags & SQLITE_FullColNames ) != 0
3781 && ( flags & SQLITE_ShortColNames ) == 0;
3782  
3783 for ( k = 0; k < pEList.nExpr; k++ )
3784 {
3785 Expr pE = a[k].pExpr;
3786 Debug.Assert( pE.op != TK_DOT || pE.pRight != null );
3787 if ( pE.op != TK_ALL && ( pE.op != TK_DOT || pE.pRight.op != TK_ALL ) )
3788 {
3789 /* This particular expression does not need to be expanded.
3790 */
3791 pNew = sqlite3ExprListAppend( pParse, pNew, a[k].pExpr );
3792 if ( pNew != null )
3793 {
3794 pNew.a[pNew.nExpr - 1].zName = a[k].zName;
3795 pNew.a[pNew.nExpr - 1].zSpan = a[k].zSpan;
3796 a[k].zName = null;
3797 a[k].zSpan = null;
3798 }
3799 a[k].pExpr = null;
3800 }
3801 else
3802 {
3803 /* This expression is a "*" or a "TABLE.*" and needs to be
3804 ** expanded. */
3805 int tableSeen = 0; /* Set to 1 when TABLE matches */
3806 string zTName; /* text of name of TABLE */
3807 if ( pE.op == TK_DOT )
3808 {
3809 Debug.Assert( pE.pLeft != null );
3810 Debug.Assert( !ExprHasProperty( pE.pLeft, EP_IntValue ) );
3811 zTName = pE.pLeft.u.zToken;
3812 }
3813 else
3814 {
3815 zTName = null;
3816 }
3817 for ( i = 0; i < pTabList.nSrc; i++ )//, pFrom++ )
3818 {
3819 pFrom = pTabList.a[i];
3820 Table pTab = pFrom.pTab;
3821 string zTabName = pFrom.zAlias;
3822 if ( zTabName == null )
3823 {
3824 zTabName = pTab.zName;
3825 }
3826 ///if ( db.mallocFailed != 0 ) break;
3827 if ( zTName != null && !zTName.Equals( zTabName, StringComparison.OrdinalIgnoreCase ) )
3828 {
3829 continue;
3830 }
3831 tableSeen = 1;
3832 for ( j = 0; j < pTab.nCol; j++ )
3833 {
3834 Expr pExpr, pRight;
3835 string zName = pTab.aCol[j].zName;
3836 string zColname; /* The computed column name */
3837 string zToFree; /* Malloced string that needs to be freed */
3838 Token sColname = new Token(); /* Computed column name as a token */
3839  
3840 /* If a column is marked as 'hidden' (currently only possible
3841 ** for virtual tables), do not include it in the expanded
3842 ** result-set list.
3843 */
3844 if ( IsHiddenColumn( pTab.aCol[j] ) )
3845 {
3846 Debug.Assert( IsVirtual( pTab ) );
3847 continue;
3848 }
3849  
3850 if ( i > 0 && ( zTName == null || zTName.Length == 0 ) )
3851 {
3852 int iDummy = 0;
3853 if ( ( pFrom.jointype & JT_NATURAL ) != 0
3854 && tableAndColumnIndex( pTabList, i, zName, ref iDummy, ref iDummy ) != 0
3855 )
3856 {
3857 /* In a NATURAL join, omit the join columns from the
3858 ** table to the right of the join */
3859 continue;
3860 }
3861 if ( sqlite3IdListIndex( pFrom.pUsing, zName ) >= 0 )
3862 {
3863 /* In a join with a USING clause, omit columns in the
3864 ** using clause from the table on the right. */
3865 continue;
3866 }
3867 }
3868 pRight = sqlite3Expr( db, TK_ID, zName );
3869 zColname = zName;
3870 zToFree = string.Empty;
3871 if ( longNames || pTabList.nSrc > 1 )
3872 {
3873 Expr pLeft;
3874 pLeft = sqlite3Expr( db, TK_ID, zTabName );
3875 pExpr = sqlite3PExpr( pParse, TK_DOT, pLeft, pRight, 0 );
3876 if ( longNames )
3877 {
3878 zColname = sqlite3MPrintf( db, "%s.%s", zTabName, zName );
3879 zToFree = zColname;
3880 }
3881 }
3882 else
3883 {
3884 pExpr = pRight;
3885 }
3886 pNew = sqlite3ExprListAppend( pParse, pNew, pExpr );
3887 sColname.z = zColname;
3888 sColname.n = sqlite3Strlen30( zColname );
3889 sqlite3ExprListSetName( pParse, pNew, sColname, 0 );
3890 sqlite3DbFree( db, ref zToFree );
3891 }
3892 }
3893 if ( tableSeen == 0 )
3894 {
3895 if ( zTName != null )
3896 {
3897 sqlite3ErrorMsg( pParse, "no such table: %s", zTName );
3898 }
3899 else
3900 {
3901 sqlite3ErrorMsg( pParse, "no tables specified" );
3902 }
3903 }
3904 }
3905 }
3906 sqlite3ExprListDelete( db, ref pEList );
3907 p.pEList = pNew;
3908 }
3909 //#if SQLITE_MAX_COLUMN
3910 if ( p.pEList != null && p.pEList.nExpr > db.aLimit[SQLITE_LIMIT_COLUMN] )
3911 {
3912 sqlite3ErrorMsg( pParse, "too many columns in result set" );
3913 }
3914 //#endif
3915 return WRC_Continue;
3916 }
3917  
3918 /*
3919 ** No-op routine for the parse-tree walker.
3920 **
3921 ** When this routine is the Walker.xExprCallback then expression trees
3922 ** are walked without any actions being taken at each node. Presumably,
3923 ** when this routine is used for Walker.xExprCallback then
3924 ** Walker.xSelectCallback is set to do something useful for every
3925 ** subquery in the parser tree.
3926 */
3927 static int exprWalkNoop( Walker NotUsed, ref Expr NotUsed2 )
3928 {
3929 UNUSED_PARAMETER2( NotUsed, NotUsed2 );
3930 return WRC_Continue;
3931 }
3932  
3933 /*
3934 ** This routine "expands" a SELECT statement and all of its subqueries.
3935 ** For additional information on what it means to "expand" a SELECT
3936 ** statement, see the comment on the selectExpand worker callback above.
3937 **
3938 ** Expanding a SELECT statement is the first step in processing a
3939 ** SELECT statement. The SELECT statement must be expanded before
3940 ** name resolution is performed.
3941 **
3942 ** If anything goes wrong, an error message is written into pParse.
3943 ** The calling function can detect the problem by looking at pParse.nErr
3944 ** and/or pParse.db.mallocFailed.
3945 */
3946 static void sqlite3SelectExpand( Parse pParse, Select pSelect )
3947 {
3948 Walker w = new Walker();
3949 w.xSelectCallback = selectExpander;
3950 w.xExprCallback = exprWalkNoop;
3951 w.pParse = pParse;
3952 sqlite3WalkSelect( w, pSelect );
3953 }
3954  
3955  
3956 #if !SQLITE_OMIT_SUBQUERY
3957 /*
3958 ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()
3959 ** interface.
3960 **
3961 ** For each FROM-clause subquery, add Column.zType and Column.zColl
3962 ** information to the Table ure that represents the result set
3963 ** of that subquery.
3964 **
3965 ** The Table ure that represents the result set was coned
3966 ** by selectExpander() but the type and collation information was omitted
3967 ** at that point because identifiers had not yet been resolved. This
3968 ** routine is called after identifier resolution.
3969 */
3970 static int selectAddSubqueryTypeInfo( Walker pWalker, Select p )
3971 {
3972 Parse pParse;
3973 int i;
3974 SrcList pTabList;
3975 SrcList_item pFrom;
3976  
3977 Debug.Assert( ( p.selFlags & SF_Resolved ) != 0 );
3978 if ( ( p.selFlags & SF_HasTypeInfo ) == 0 )
3979 {
3980 p.selFlags |= SF_HasTypeInfo;
3981 pParse = pWalker.pParse;
3982 pTabList = p.pSrc;
3983 for ( i = 0; i < pTabList.nSrc; i++ )//, pFrom++ )
3984 {
3985 pFrom = pTabList.a[i];
3986 Table pTab = pFrom.pTab;
3987 if ( ALWAYS( pTab != null ) && ( pTab.tabFlags & TF_Ephemeral ) != 0 )
3988 {
3989 /* A sub-query in the FROM clause of a SELECT */
3990 Select pSel = pFrom.pSelect;
3991 Debug.Assert( pSel != null );
3992 while ( pSel.pPrior != null )
3993 pSel = pSel.pPrior;
3994 selectAddColumnTypeAndCollation( pParse, pTab.nCol, pTab.aCol, pSel );
3995 }
3996 }
3997 }
3998 return WRC_Continue;
3999 }
4000 #endif
4001  
4002  
4003 /*
4004 ** This routine adds datatype and collating sequence information to
4005 ** the Table ures of all FROM-clause subqueries in a
4006 ** SELECT statement.
4007 **
4008 ** Use this routine after name resolution.
4009 */
4010 static void sqlite3SelectAddTypeInfo( Parse pParse, Select pSelect )
4011 {
4012 #if !SQLITE_OMIT_SUBQUERY
4013 Walker w = new Walker();
4014 w.xSelectCallback = selectAddSubqueryTypeInfo;
4015 w.xExprCallback = exprWalkNoop;
4016 w.pParse = pParse;
4017 sqlite3WalkSelect( w, pSelect );
4018 #endif
4019 }
4020  
4021  
4022 /*
4023 ** This routine sets of a SELECT statement for processing. The
4024 ** following is accomplished:
4025 **
4026 ** * VDBE VdbeCursor numbers are assigned to all FROM-clause terms.
4027 ** * Ephemeral Table objects are created for all FROM-clause subqueries.
4028 ** * ON and USING clauses are shifted into WHERE statements
4029 ** * Wildcards "*" and "TABLE.*" in result sets are expanded.
4030 ** * Identifiers in expression are matched to tables.
4031 **
4032 ** This routine acts recursively on all subqueries within the SELECT.
4033 */
4034 static void sqlite3SelectPrep(
4035 Parse pParse, /* The parser context */
4036 Select p, /* The SELECT statement being coded. */
4037 NameContext pOuterNC /* Name context for container */
4038 )
4039 {
4040 if ( NEVER( p == null ) )
4041 return;
4042 ////sqlite3 db = pParse.db;
4043 if ( ( p.selFlags & SF_HasTypeInfo ) != 0 )
4044 return;
4045 sqlite3SelectExpand( pParse, p );
4046 if ( pParse.nErr != 0 /*|| db.mallocFailed != 0 */ )
4047 return;
4048 sqlite3ResolveSelectNames( pParse, p, pOuterNC );
4049 if ( pParse.nErr != 0 /*|| db.mallocFailed != 0 */ )
4050 return;
4051 sqlite3SelectAddTypeInfo( pParse, p );
4052 }
4053  
4054 /*
4055 ** Reset the aggregate accumulator.
4056 **
4057 ** The aggregate accumulator is a set of memory cells that hold
4058 ** intermediate results while calculating an aggregate. This
4059 ** routine simply stores NULLs in all of those memory cells.
4060 */
4061 static void resetAccumulator( Parse pParse, AggInfo pAggInfo )
4062 {
4063 Vdbe v = pParse.pVdbe;
4064 int i;
4065 AggInfo_func pFunc;
4066 if ( pAggInfo.nFunc + pAggInfo.nColumn == 0 )
4067 {
4068 return;
4069 }
4070 for ( i = 0; i < pAggInfo.nColumn; i++ )
4071 {
4072 sqlite3VdbeAddOp2( v, OP_Null, 0, pAggInfo.aCol[i].iMem );
4073 }
4074 for ( i = 0; i < pAggInfo.nFunc; i++ )
4075 {//, pFunc++){
4076 pFunc = pAggInfo.aFunc[i];
4077 sqlite3VdbeAddOp2( v, OP_Null, 0, pFunc.iMem );
4078 if ( pFunc.iDistinct >= 0 )
4079 {
4080 Expr pE = pFunc.pExpr;
4081 Debug.Assert( !ExprHasProperty( pE, EP_xIsSelect ) );
4082 if ( pE.x.pList == null || pE.x.pList.nExpr != 1 )
4083 {
4084 sqlite3ErrorMsg( pParse, "DISTINCT aggregates must have exactly one " +
4085 "argument" );
4086 pFunc.iDistinct = -1;
4087 }
4088 else
4089 {
4090 KeyInfo pKeyInfo = keyInfoFromExprList( pParse, pE.x.pList );
4091 sqlite3VdbeAddOp4( v, OP_OpenEphemeral, pFunc.iDistinct, 0, 0,
4092 pKeyInfo, P4_KEYINFO_HANDOFF );
4093 }
4094 }
4095 }
4096 }
4097  
4098 /*
4099 ** Invoke the OP_AggFinalize opcode for every aggregate function
4100 ** in the AggInfo structure.
4101 */
4102 static void finalizeAggFunctions( Parse pParse, AggInfo pAggInfo )
4103 {
4104 Vdbe v = pParse.pVdbe;
4105 int i;
4106 AggInfo_func pF;
4107 for ( i = 0; i < pAggInfo.nFunc; i++ )
4108 {//, pF++){
4109 pF = pAggInfo.aFunc[i];
4110 ExprList pList = pF.pExpr.x.pList;
4111 Debug.Assert( !ExprHasProperty( pF.pExpr, EP_xIsSelect ) );
4112 sqlite3VdbeAddOp4( v, OP_AggFinal, pF.iMem, pList != null ? pList.nExpr : 0, 0,
4113 pF.pFunc, P4_FUNCDEF );
4114 }
4115 }
4116  
4117 /*
4118 ** Update the accumulator memory cells for an aggregate based on
4119 ** the current cursor position.
4120 */
4121 static void updateAccumulator( Parse pParse, AggInfo pAggInfo )
4122 {
4123 Vdbe v = pParse.pVdbe;
4124 int i;
4125 AggInfo_func pF;
4126 AggInfo_col pC;
4127  
4128 pAggInfo.directMode = 1;
4129 sqlite3ExprCacheClear( pParse );
4130 for ( i = 0; i < pAggInfo.nFunc; i++ )
4131 {//, pF++){
4132 pF = pAggInfo.aFunc[i];
4133 int nArg;
4134 int addrNext = 0;
4135 int regAgg;
4136 Debug.Assert( !ExprHasProperty( pF.pExpr, EP_xIsSelect ) );
4137 ExprList pList = pF.pExpr.x.pList;
4138 if ( pList != null )
4139 {
4140 nArg = pList.nExpr;
4141 regAgg = sqlite3GetTempRange( pParse, nArg );
4142 sqlite3ExprCodeExprList( pParse, pList, regAgg, true );
4143 }
4144 else
4145 {
4146 nArg = 0;
4147 regAgg = 0;
4148 }
4149 if ( pF.iDistinct >= 0 )
4150 {
4151 addrNext = sqlite3VdbeMakeLabel( v );
4152 Debug.Assert( nArg == 1 );
4153 codeDistinct( pParse, pF.iDistinct, addrNext, 1, regAgg );
4154 }
4155 if ( ( pF.pFunc.flags & SQLITE_FUNC_NEEDCOLL ) != 0 )
4156 {
4157 CollSeq pColl = null;
4158 ExprList_item pItem;
4159 int j;
4160 Debug.Assert( pList != null ); /* pList!=0 if pF->pFunc has NEEDCOLL */
4161 for ( j = 0; pColl == null && j < nArg; j++ )
4162 {//, pItem++){
4163 pItem = pList.a[j];
4164 pColl = sqlite3ExprCollSeq( pParse, pItem.pExpr );
4165 }
4166 if ( pColl == null )
4167 {
4168 pColl = pParse.db.pDfltColl;
4169 }
4170 sqlite3VdbeAddOp4( v, OP_CollSeq, 0, 0, 0, pColl, P4_COLLSEQ );
4171 }
4172 sqlite3VdbeAddOp4( v, OP_AggStep, 0, regAgg, pF.iMem,
4173 pF.pFunc, P4_FUNCDEF );
4174 sqlite3VdbeChangeP5( v, (u8)nArg );
4175 sqlite3ExprCacheAffinityChange( pParse, regAgg, nArg );
4176 sqlite3ReleaseTempRange( pParse, regAgg, nArg );
4177 if ( addrNext != 0 )
4178 {
4179 sqlite3VdbeResolveLabel( v, addrNext );
4180 sqlite3ExprCacheClear( pParse );
4181 }
4182 }
4183  
4184 /* Before populating the accumulator registers, clear the column cache.
4185 ** Otherwise, if any of the required column values are already present
4186 ** in registers, sqlite3ExprCode() may use OP_SCopy to copy the value
4187 ** to pC->iMem. But by the time the value is used, the original register
4188 ** may have been used, invalidating the underlying buffer holding the
4189 ** text or blob value. See ticket [883034dcb5].
4190 **
4191 ** Another solution would be to change the OP_SCopy used to copy cached
4192 ** values to an OP_Copy.
4193 */
4194 sqlite3ExprCacheClear( pParse );
4195 for ( i = 0; i < pAggInfo.nAccumulator; i++ )//, pC++)
4196 {
4197 pC = pAggInfo.aCol[i];
4198 sqlite3ExprCode( pParse, pC.pExpr, pC.iMem );
4199 }
4200 pAggInfo.directMode = 0;
4201 sqlite3ExprCacheClear( pParse );
4202 }
4203  
4204 /*
4205 ** Add a single OP_Explain instruction to the VDBE to explain a simple
4206 ** count() query ("SELECT count() FROM pTab").
4207 */
4208 #if !SQLITE_OMIT_EXPLAIN
4209 static void explainSimpleCount(
4210 Parse pParse, /* Parse context */
4211 Table pTab, /* Table being queried */
4212 Index pIdx /* Index used to optimize scan, or NULL */
4213 )
4214 {
4215 if ( pParse.explain == 2 )
4216 {
4217 string zEqp = sqlite3MPrintf( pParse.db, "SCAN TABLE %s %s%s(~%d rows)",
4218 pTab.zName,
4219 pIdx != null ? "USING COVERING INDEX " : string.Empty,
4220 pIdx != null ? pIdx.zName : string.Empty,
4221 pTab.nRowEst
4222 );
4223 sqlite3VdbeAddOp4(
4224 pParse.pVdbe, OP_Explain, pParse.iSelectId, 0, 0, zEqp, P4_DYNAMIC
4225 );
4226 }
4227 }
4228 #else
4229 //# define explainSimpleCount(a,b,c)
4230 static void explainSimpleCount(Parse a, Table b, Index c){}
4231 #endif
4232  
4233 /*
4234 ** Generate code for the SELECT statement given in the p argument.
4235 **
4236 ** The results are distributed in various ways depending on the
4237 ** contents of the SelectDest structure pointed to by argument pDest
4238 ** as follows:
4239 **
4240 ** pDest.eDest Result
4241 ** ------------ -------------------------------------------
4242 ** SRT_Output Generate a row of output (using the OP_ResultRow
4243 ** opcode) for each row in the result set.
4244 **
4245 ** SRT_Mem Only valid if the result is a single column.
4246 ** Store the first column of the first result row
4247 ** in register pDest.iParm then abandon the rest
4248 ** of the query. This destination implies "LIMIT 1".
4249 **
4250 ** SRT_Set The result must be a single column. Store each
4251 ** row of result as the key in table pDest.iParm.
4252 ** Apply the affinity pDest.affinity before storing
4253 ** results. Used to implement "IN (SELECT ...)".
4254 **
4255 ** SRT_Union Store results as a key in a temporary table pDest.iParm.
4256 **
4257 ** SRT_Except Remove results from the temporary table pDest.iParm.
4258 **
4259 ** SRT_Table Store results in temporary table pDest.iParm.
4260 ** This is like SRT_EphemTab except that the table
4261 ** is assumed to already be open.
4262 **
4263 ** SRT_EphemTab Create an temporary table pDest.iParm and store
4264 ** the result there. The cursor is left open after
4265 ** returning. This is like SRT_Table except that
4266 ** this destination uses OP_OpenEphemeral to create
4267 ** the table first.
4268 **
4269 ** SRT_Coroutine Generate a co-routine that returns a new row of
4270 ** results each time it is invoked. The entry point
4271 ** of the co-routine is stored in register pDest.iParm.
4272 **
4273 ** SRT_Exists Store a 1 in memory cell pDest.iParm if the result
4274 ** set is not empty.
4275 **
4276 ** SRT_Discard Throw the results away. This is used by SELECT
4277 ** statements within triggers whose only purpose is
4278 ** the side-effects of functions.
4279 **
4280 ** This routine returns the number of errors. If any errors are
4281 ** encountered, then an appropriate error message is left in
4282 ** pParse.zErrMsg.
4283 **
4284 ** This routine does NOT free the Select structure passed in. The
4285 ** calling function needs to do that.
4286 */
4287 static SelectDest sdDummy = null;
4288 static bool bDummy = false;
4289  
4290 static int sqlite3Select(
4291 Parse pParse, /* The parser context */
4292 Select p, /* The SELECT statement being coded. */
4293 ref SelectDest pDest /* What to do with the query results */
4294 )
4295 {
4296 int i, j; /* Loop counters */
4297 WhereInfo pWInfo; /* Return from sqlite3WhereBegin() */
4298 Vdbe v; /* The virtual machine under construction */
4299 bool isAgg; /* True for select lists like "count()" */
4300 ExprList pEList = new ExprList(); /* List of columns to extract. */
4301 SrcList pTabList = new SrcList(); /* List of tables to select from */
4302 Expr pWhere; /* The WHERE clause. May be NULL */
4303 ExprList pOrderBy; /* The ORDER BY clause. May be NULL */
4304 ExprList pGroupBy; /* The GROUP BY clause. May be NULL */
4305 Expr pHaving; /* The HAVING clause. May be NULL */
4306 bool isDistinct; /* True if the DISTINCT keyword is present */
4307 int distinct; /* Table to use for the distinct set */
4308 int rc = 1; /* Value to return from this function */
4309 int addrSortIndex; /* Address of an OP_OpenEphemeral instruction */
4310 AggInfo sAggInfo; /* Information used by aggregate queries */
4311 int iEnd; /* Address of the end of the query */
4312 sqlite3 db; /* The database connection */
4313  
4314 #if !SQLITE_OMIT_EXPLAIN
4315 int iRestoreSelectId = pParse.iSelectId;
4316 pParse.iSelectId = pParse.iNextSelectId++;
4317 #endif
4318  
4319 db = pParse.db;
4320 if ( p == null /*|| db.mallocFailed != 0 */ || pParse.nErr != 0 )
4321 {
4322 return 1;
4323 }
4324 #if !SQLITE_OMIT_AUTHORIZATION
4325 if (sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0)) return 1;
4326 #endif
4327 sAggInfo = new AggInfo();// memset(sAggInfo, 0, sAggInfo).Length;
4328  
4329 if ( pDest.eDest <= SRT_Discard ) //IgnorableOrderby(pDest))
4330 {
4331 Debug.Assert( pDest.eDest == SRT_Exists || pDest.eDest == SRT_Union ||
4332 pDest.eDest == SRT_Except || pDest.eDest == SRT_Discard );
4333 /* If ORDER BY makes no difference in the output then neither does
4334 ** DISTINCT so it can be removed too. */
4335 sqlite3ExprListDelete( db, ref p.pOrderBy );
4336 p.pOrderBy = null;
4337 p.selFlags = (u16)( p.selFlags & ~SF_Distinct );
4338 }
4339 sqlite3SelectPrep( pParse, p, null );
4340 pOrderBy = p.pOrderBy;
4341 pTabList = p.pSrc;
4342 pEList = p.pEList;
4343 if ( pParse.nErr != 0 /*|| db.mallocFailed != 0 */ )
4344 {
4345 goto select_end;
4346 }
4347 isAgg = ( p.selFlags & SF_Aggregate ) != 0;
4348 Debug.Assert( pEList != null );
4349  
4350 /* Begin generating code.
4351 */
4352 v = sqlite3GetVdbe( pParse );
4353 if ( v == null )
4354 goto select_end;
4355  
4356 /* If writing to memory or generating a set
4357 ** only a single column may be output.
4358 */
4359 #if !SQLITE_OMIT_SUBQUERY
4360 if ( checkForMultiColumnSelectError( pParse, pDest, pEList.nExpr ) )
4361 {
4362 goto select_end;
4363 }
4364 #endif
4365  
4366 /* Generate code for all sub-queries in the FROM clause
4367 */
4368 #if !SQLITE_OMIT_SUBQUERY || !SQLITE_OMIT_VIEW
4369 for ( i = 0; p.pPrior == null && i < pTabList.nSrc; i++ )
4370 {
4371 SrcList_item pItem = pTabList.a[i];
4372 SelectDest dest = new SelectDest();
4373 Select pSub = pItem.pSelect;
4374 bool isAggSub;
4375  
4376 if ( pSub == null || pItem.isPopulated != 0 )
4377 continue;
4378  
4379 /* Increment Parse.nHeight by the height of the largest expression
4380 ** tree refered to by this, the parent select. The child select
4381 ** may contain expression trees of at most
4382 ** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
4383 ** more conservative than necessary, but much easier than enforcing
4384 ** an exact limit.
4385 */
4386 pParse.nHeight += sqlite3SelectExprHeight( p );
4387  
4388 /* Check to see if the subquery can be absorbed into the parent. */
4389 isAggSub = ( pSub.selFlags & SF_Aggregate ) != 0;
4390 if ( flattenSubquery( pParse, p, i, isAgg, isAggSub ) != 0 )
4391 {
4392 if ( isAggSub )
4393 {
4394 isAgg = true;
4395 p.selFlags |= SF_Aggregate;
4396 }
4397 i = -1;
4398 }
4399 else
4400 {
4401 sqlite3SelectDestInit( dest, SRT_EphemTab, pItem.iCursor );
4402 Debug.Assert( 0 == pItem.isPopulated );
4403 explainSetInteger( ref pItem.iSelectId, (int)pParse.iNextSelectId );
4404 sqlite3Select( pParse, pSub, ref dest );
4405 pItem.isPopulated = 1;
4406 pItem.pTab.nRowEst = (uint)pSub.nSelectRow;
4407 }
4408 //if ( /* pParse.nErr != 0 || */ db.mallocFailed != 0 )
4409 //{
4410 // goto select_end;
4411 //}
4412 pParse.nHeight -= sqlite3SelectExprHeight( p );
4413 pTabList = p.pSrc;
4414 if ( !( pDest.eDest <= SRT_Discard ) )// if( null==IgnorableOrderby(pDest) )
4415 {
4416 pOrderBy = p.pOrderBy;
4417 }
4418 }
4419 pEList = p.pEList;
4420 #endif
4421 pWhere = p.pWhere;
4422 pGroupBy = p.pGroupBy;
4423 pHaving = p.pHaving;
4424 isDistinct = ( p.selFlags & SF_Distinct ) != 0;
4425  
4426 #if !SQLITE_OMIT_COMPOUND_SELECT
4427 /* If there is are a sequence of queries, do the earlier ones first.
4428 */
4429 if ( p.pPrior != null )
4430 {
4431 if ( p.pRightmost == null )
4432 {
4433 Select pLoop, pRight = null;
4434 int cnt = 0;
4435 int mxSelect;
4436 for ( pLoop = p; pLoop != null; pLoop = pLoop.pPrior, cnt++ )
4437 {
4438 pLoop.pRightmost = p;
4439 pLoop.pNext = pRight;
4440 pRight = pLoop;
4441 }
4442 mxSelect = db.aLimit[SQLITE_LIMIT_COMPOUND_SELECT];
4443 if ( mxSelect != 0 && cnt > mxSelect )
4444 {
4445 sqlite3ErrorMsg( pParse, "too many terms in compound SELECT" );
4446 goto select_end;
4447 }
4448 }
4449 rc = multiSelect( pParse, p, pDest );
4450 explainSetInteger( ref pParse.iSelectId, iRestoreSelectId );
4451 return rc;
4452 }
4453 #endif
4454  
4455 /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
4456 ** GROUP BY might use an index, DISTINCT never does.
4457 */
4458 Debug.Assert( p.pGroupBy == null || ( p.selFlags & SF_Aggregate ) != 0 );
4459 if ( ( p.selFlags & ( SF_Distinct | SF_Aggregate ) ) == SF_Distinct )
4460 {
4461 p.pGroupBy = sqlite3ExprListDup( db, p.pEList, 0 );
4462 pGroupBy = p.pGroupBy;
4463 p.selFlags = (u16)( p.selFlags & ~SF_Distinct );
4464 }
4465  
4466 /* If there is both a GROUP BY and an ORDER BY clause and they are
4467 ** identical, then disable the ORDER BY clause since the GROUP BY
4468 ** will cause elements to come out in the correct order. This is
4469 ** an optimization - the correct answer should result regardless.
4470 ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
4471 ** to disable this optimization for testing purposes.
4472 */
4473 if ( sqlite3ExprListCompare( p.pGroupBy, pOrderBy ) == 0
4474 && ( db.flags & SQLITE_GroupByOrder ) == 0 )
4475 {
4476 pOrderBy = null;
4477 }
4478  
4479 /* If there is an ORDER BY clause, then this sorting
4480 ** index might end up being unused if the data can be
4481 ** extracted in pre-sorted order. If that is the case, then the
4482 ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
4483 ** we figure out that the sorting index is not needed. The addrSortIndex
4484 ** variable is used to facilitate that change.
4485 */
4486 if ( pOrderBy != null )
4487 {
4488 KeyInfo pKeyInfo;
4489 pKeyInfo = keyInfoFromExprList( pParse, pOrderBy );
4490 pOrderBy.iECursor = pParse.nTab++;
4491 p.addrOpenEphm[2] = addrSortIndex =
4492 sqlite3VdbeAddOp4( v, OP_OpenEphemeral,
4493 pOrderBy.iECursor, pOrderBy.nExpr + 2, 0,
4494 pKeyInfo, P4_KEYINFO_HANDOFF );
4495 }
4496 else
4497 {
4498 addrSortIndex = -1;
4499 }
4500  
4501 /* If the output is destined for a temporary table, open that table.
4502 */
4503 if ( pDest.eDest == SRT_EphemTab )
4504 {
4505 sqlite3VdbeAddOp2( v, OP_OpenEphemeral, pDest.iParm, pEList.nExpr );
4506 }
4507  
4508 /* Set the limiter.
4509 */
4510 iEnd = sqlite3VdbeMakeLabel( v );
4511 p.nSelectRow = (double)LARGEST_INT64;
4512 computeLimitRegisters( pParse, p, iEnd );
4513  
4514 /* Open a virtual index to use for the distinct set.
4515 */
4516 if ( ( p.selFlags & SF_Distinct ) != 0 )
4517 {
4518 KeyInfo pKeyInfo;
4519 Debug.Assert( isAgg || pGroupBy != null );
4520 distinct = pParse.nTab++;
4521 pKeyInfo = keyInfoFromExprList( pParse, p.pEList );
4522 sqlite3VdbeAddOp4( v, OP_OpenEphemeral, distinct, 0, 0,
4523 pKeyInfo, P4_KEYINFO_HANDOFF );
4524 sqlite3VdbeChangeP5( v, BTREE_UNORDERED );
4525 }
4526 else
4527 {
4528 distinct = -1;
4529 }
4530  
4531 /* Aggregate and non-aggregate queries are handled differently */
4532 if ( !isAgg && pGroupBy == null )
4533 {
4534 /* This case is for non-aggregate queries
4535 ** Begin the database scan
4536 */
4537 pWInfo = sqlite3WhereBegin( pParse, pTabList, pWhere, ref pOrderBy, 0 );
4538 if ( pWInfo == null )
4539 goto select_end;
4540 if ( pWInfo.nRowOut < p.nSelectRow )
4541 p.nSelectRow = pWInfo.nRowOut;
4542  
4543 /* If sorting index that was created by a prior OP_OpenEphemeral
4544 ** instruction ended up not being needed, then change the OP_OpenEphemeral
4545 ** into an OP_Noop.
4546 */
4547 if ( addrSortIndex >= 0 && pOrderBy == null )
4548 {
4549 sqlite3VdbeChangeToNoop( v, addrSortIndex, 1 );
4550 p.addrOpenEphm[2] = -1;
4551 }
4552  
4553 /* Use the standard inner loop
4554 */
4555 Debug.Assert( !isDistinct );
4556 selectInnerLoop( pParse, p, pEList, 0, 0, pOrderBy, -1, pDest,
4557 pWInfo.iContinue, pWInfo.iBreak );
4558  
4559 /* End the database scan loop.
4560 */
4561 sqlite3WhereEnd( pWInfo );
4562 }
4563 else
4564 {
4565 /* This is the processing for aggregate queries */
4566 NameContext sNC; /* Name context for processing aggregate information */
4567 int iAMem; /* First Mem address for storing current GROUP BY */
4568 int iBMem; /* First Mem address for previous GROUP BY */
4569 int iUseFlag; /* Mem address holding flag indicating that at least
4570 ** one row of the input to the aggregator has been
4571 ** processed */
4572 int iAbortFlag; /* Mem address which causes query abort if positive */
4573 int groupBySort; /* Rows come from source in GR BY' clause thanROUP BY order */
4574  
4575 int addrEnd; /* End of processing for this SELECT */
4576  
4577 /* Remove any and all aliases between the result set and the
4578 ** GROUP BY clause.
4579 */
4580 if ( pGroupBy != null )
4581 {
4582 int k; /* Loop counter */
4583 ExprList_item pItem; /* For looping over expression in a list */
4584  
4585 for ( k = p.pEList.nExpr; k > 0; k-- )//, pItem++)
4586 {
4587 pItem = p.pEList.a[p.pEList.nExpr - k];
4588 pItem.iAlias = 0;
4589 }
4590 for ( k = pGroupBy.nExpr; k > 0; k-- )//, pItem++ )
4591 {
4592 pItem = pGroupBy.a[pGroupBy.nExpr - k];
4593 pItem.iAlias = 0;
4594 }
4595 if ( p.nSelectRow > (double)100 )
4596 p.nSelectRow = (double)100;
4597 }
4598 else
4599 {
4600 p.nSelectRow = (double)1;
4601 }
4602  
4603 /* Create a label to jump to when we want to abort the query */
4604 addrEnd = sqlite3VdbeMakeLabel( v );
4605  
4606 /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
4607 ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
4608 ** SELECT statement.
4609 */
4610 sNC = new NameContext(); // memset(sNC, 0, sNC).Length;
4611 sNC.pParse = pParse;
4612 sNC.pSrcList = pTabList;
4613 sNC.pAggInfo = sAggInfo;
4614 sAggInfo.nSortingColumn = pGroupBy != null ? pGroupBy.nExpr + 1 : 0;
4615 sAggInfo.pGroupBy = pGroupBy;
4616 sqlite3ExprAnalyzeAggList( sNC, pEList );
4617 sqlite3ExprAnalyzeAggList( sNC, pOrderBy );
4618 if ( pHaving != null )
4619 {
4620 sqlite3ExprAnalyzeAggregates( sNC, ref pHaving );
4621 }
4622 sAggInfo.nAccumulator = sAggInfo.nColumn;
4623 for ( i = 0; i < sAggInfo.nFunc; i++ )
4624 {
4625 Debug.Assert( !ExprHasProperty( sAggInfo.aFunc[i].pExpr, EP_xIsSelect ) );
4626 sqlite3ExprAnalyzeAggList( sNC, sAggInfo.aFunc[i].pExpr.x.pList );
4627 }
4628 // if ( db.mallocFailed != 0 ) goto select_end;
4629  
4630 /* Processing for aggregates with GROUP BY is very different and
4631 ** much more complex than aggregates without a GROUP BY.
4632 */
4633 if ( pGroupBy != null )
4634 {
4635 KeyInfo pKeyInfo; /* Keying information for the group by clause */
4636 int j1; /* A-vs-B comparision jump */
4637 int addrOutputRow; /* Start of subroutine that outputs a result row */
4638 int regOutputRow; /* Return address register for output subroutine */
4639 int addrSetAbort; /* Set the abort flag and return */
4640 int addrTopOfLoop; /* Top of the input loop */
4641 int addrSortingIdx; /* The OP_OpenEphemeral for the sorting index */
4642 int addrReset; /* Subroutine for resetting the accumulator */
4643 int regReset; /* Return address register for reset subroutine */
4644  
4645 /* If there is a GROUP BY clause we might need a sorting index to
4646 ** implement it. Allocate that sorting index now. If it turns out
4647 ** that we do not need it after all, the OpenEphemeral instruction
4648 ** will be converted into a Noop.
4649 */
4650 sAggInfo.sortingIdx = pParse.nTab++;
4651 pKeyInfo = keyInfoFromExprList( pParse, pGroupBy );
4652 addrSortingIdx = sqlite3VdbeAddOp4( v, OP_OpenEphemeral,
4653 sAggInfo.sortingIdx, sAggInfo.nSortingColumn,
4654 0, pKeyInfo, P4_KEYINFO_HANDOFF );
4655  
4656 /* Initialize memory locations used by GROUP BY aggregate processing
4657 */
4658 iUseFlag = ++pParse.nMem;
4659 iAbortFlag = ++pParse.nMem;
4660 regOutputRow = ++pParse.nMem;
4661 addrOutputRow = sqlite3VdbeMakeLabel( v );
4662 regReset = ++pParse.nMem;
4663 addrReset = sqlite3VdbeMakeLabel( v );
4664 iAMem = pParse.nMem + 1;
4665 pParse.nMem += pGroupBy.nExpr;
4666 iBMem = pParse.nMem + 1;
4667 pParse.nMem += pGroupBy.nExpr;
4668 sqlite3VdbeAddOp2( v, OP_Integer, 0, iAbortFlag );
4669 #if SQLITE_DEBUG
4670 VdbeComment( v, "clear abort flag" );
4671 #endif
4672 sqlite3VdbeAddOp2( v, OP_Integer, 0, iUseFlag );
4673 #if SQLITE_DEBUG
4674 VdbeComment( v, "indicate accumulator empty" );
4675 #endif
4676  
4677 /* Begin a loop that will extract all source rows in GROUP BY order.
4678 ** This might involve two separate loops with an OP_Sort in between, or
4679 ** it might be a single loop that uses an index to extract information
4680 ** in the right order to begin with.
4681 */
4682 sqlite3VdbeAddOp2( v, OP_Gosub, regReset, addrReset );
4683 pWInfo = sqlite3WhereBegin( pParse, pTabList, pWhere, ref pGroupBy, 0 );
4684 if ( pWInfo == null )
4685 goto select_end;
4686 if ( pGroupBy == null )
4687 {
4688 /* The optimizer is able to deliver rows in group by order so
4689 ** we do not have to sort. The OP_OpenEphemeral table will be
4690 ** cancelled later because we still need to use the pKeyInfo
4691 */
4692 pGroupBy = p.pGroupBy;
4693 groupBySort = 0;
4694 }
4695 else
4696 {
4697 /* Rows are coming out in undetermined order. We have to push
4698 ** each row into a sorting index, terminate the first loop,
4699 ** then loop over the sorting index in order to get the output
4700 ** in sorted order
4701 */
4702 int regBase;
4703 int regRecord;
4704 int nCol;
4705 int nGroupBy;
4706  
4707 explainTempTable( pParse,
4708 isDistinct && 0 == ( p.selFlags & SF_Distinct ) ? "DISTINCT" : "GROUP BY" );
4709  
4710 groupBySort = 1;
4711 nGroupBy = pGroupBy.nExpr;
4712 nCol = nGroupBy + 1;
4713 j = nGroupBy + 1;
4714 for ( i = 0; i < sAggInfo.nColumn; i++ )
4715 {
4716 if ( sAggInfo.aCol[i].iSorterColumn >= j )
4717 {
4718 nCol++;
4719 j++;
4720 }
4721 }
4722 regBase = sqlite3GetTempRange( pParse, nCol );
4723 sqlite3ExprCacheClear( pParse );
4724 sqlite3ExprCodeExprList( pParse, pGroupBy, regBase, false );
4725 sqlite3VdbeAddOp2( v, OP_Sequence, sAggInfo.sortingIdx, regBase + nGroupBy );
4726 j = nGroupBy + 1;
4727 for ( i = 0; i < sAggInfo.nColumn; i++ )
4728 {
4729 AggInfo_col pCol = sAggInfo.aCol[i];
4730 if ( pCol.iSorterColumn >= j )
4731 {
4732 int r1 = j + regBase;
4733 int r2;
4734 r2 = sqlite3ExprCodeGetColumn( pParse,
4735 pCol.pTab, pCol.iColumn, pCol.iTable, r1 );
4736 if ( r1 != r2 )
4737 {
4738 sqlite3VdbeAddOp2( v, OP_SCopy, r2, r1 );
4739 }
4740 j++;
4741 }
4742 }
4743 regRecord = sqlite3GetTempReg( pParse );
4744 sqlite3VdbeAddOp3( v, OP_MakeRecord, regBase, nCol, regRecord );
4745 sqlite3VdbeAddOp2( v, OP_IdxInsert, sAggInfo.sortingIdx, regRecord );
4746 sqlite3ReleaseTempReg( pParse, regRecord );
4747 sqlite3ReleaseTempRange( pParse, regBase, nCol );
4748 sqlite3WhereEnd( pWInfo );
4749 sqlite3VdbeAddOp2( v, OP_Sort, sAggInfo.sortingIdx, addrEnd );
4750 #if SQLITE_DEBUG
4751 VdbeComment( v, "GROUP BY sort" );
4752 #endif
4753 sAggInfo.useSortingIdx = 1;
4754 sqlite3ExprCacheClear( pParse );
4755 }
4756  
4757 /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
4758 ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
4759 ** Then compare the current GROUP BY terms against the GROUP BY terms
4760 ** from the previous row currently stored in a0, a1, a2...
4761 */
4762 addrTopOfLoop = sqlite3VdbeCurrentAddr( v );
4763 sqlite3ExprCacheClear( pParse );
4764 for ( j = 0; j < pGroupBy.nExpr; j++ )
4765 {
4766 if ( groupBySort != 0 )
4767 {
4768 sqlite3VdbeAddOp3( v, OP_Column, sAggInfo.sortingIdx, j, iBMem + j );
4769 }
4770 else
4771 {
4772 sAggInfo.directMode = 1;
4773 sqlite3ExprCode( pParse, pGroupBy.a[j].pExpr, iBMem + j );
4774 }
4775 }
4776 sqlite3VdbeAddOp4( v, OP_Compare, iAMem, iBMem, pGroupBy.nExpr,
4777 pKeyInfo, P4_KEYINFO );
4778 j1 = sqlite3VdbeCurrentAddr( v );
4779 sqlite3VdbeAddOp3( v, OP_Jump, j1 + 1, 0, j1 + 1 );
4780  
4781 /* Generate code that runs whenever the GROUP BY changes.
4782 ** Changes in the GROUP BY are detected by the previous code
4783 ** block. If there were no changes, this block is skipped.
4784 **
4785 ** This code copies current group by terms in b0,b1,b2,...
4786 ** over to a0,a1,a2. It then calls the output subroutine
4787 ** and resets the aggregate accumulator registers in preparation
4788 ** for the next GROUP BY batch.
4789 */
4790 sqlite3ExprCodeMove( pParse, iBMem, iAMem, pGroupBy.nExpr );
4791 sqlite3VdbeAddOp2( v, OP_Gosub, regOutputRow, addrOutputRow );
4792 #if SQLITE_DEBUG
4793 VdbeComment( v, "output one row" );
4794 #endif
4795 sqlite3VdbeAddOp2( v, OP_IfPos, iAbortFlag, addrEnd );
4796 #if SQLITE_DEBUG
4797 VdbeComment( v, "check abort flag" );
4798 #endif
4799 sqlite3VdbeAddOp2( v, OP_Gosub, regReset, addrReset );
4800 #if SQLITE_DEBUG
4801 VdbeComment( v, "reset accumulator" );
4802 #endif
4803  
4804 /* Update the aggregate accumulators based on the content of
4805 ** the current row
4806 */
4807 sqlite3VdbeJumpHere( v, j1 );
4808 updateAccumulator( pParse, sAggInfo );
4809 sqlite3VdbeAddOp2( v, OP_Integer, 1, iUseFlag );
4810 #if SQLITE_DEBUG
4811 VdbeComment( v, "indicate data in accumulator" );
4812 #endif
4813 /* End of the loop
4814 */
4815 if ( groupBySort != 0 )
4816 {
4817 sqlite3VdbeAddOp2( v, OP_Next, sAggInfo.sortingIdx, addrTopOfLoop );
4818 }
4819 else
4820 {
4821 sqlite3WhereEnd( pWInfo );
4822 sqlite3VdbeChangeToNoop( v, addrSortingIdx, 1 );
4823 }
4824  
4825 /* Output the final row of result
4826 */
4827 sqlite3VdbeAddOp2( v, OP_Gosub, regOutputRow, addrOutputRow );
4828 #if SQLITE_DEBUG
4829 VdbeComment( v, "output final row" );
4830 #endif
4831 /* Jump over the subroutines
4832 */
4833 sqlite3VdbeAddOp2( v, OP_Goto, 0, addrEnd );
4834  
4835 /* Generate a subroutine that outputs a single row of the result
4836 ** set. This subroutine first looks at the iUseFlag. If iUseFlag
4837 ** is less than or equal to zero, the subroutine is a no-op. If
4838 ** the processing calls for the query to abort, this subroutine
4839 ** increments the iAbortFlag memory location before returning in
4840 ** order to signal the caller to abort.
4841 */
4842 addrSetAbort = sqlite3VdbeCurrentAddr( v );
4843 sqlite3VdbeAddOp2( v, OP_Integer, 1, iAbortFlag );
4844 VdbeComment( v, "set abort flag" );
4845 sqlite3VdbeAddOp1( v, OP_Return, regOutputRow );
4846 sqlite3VdbeResolveLabel( v, addrOutputRow );
4847 addrOutputRow = sqlite3VdbeCurrentAddr( v );
4848 sqlite3VdbeAddOp2( v, OP_IfPos, iUseFlag, addrOutputRow + 2 );
4849 VdbeComment( v, "Groupby result generator entry point" );
4850 sqlite3VdbeAddOp1( v, OP_Return, regOutputRow );
4851 finalizeAggFunctions( pParse, sAggInfo );
4852 sqlite3ExprIfFalse( pParse, pHaving, addrOutputRow + 1, SQLITE_JUMPIFNULL );
4853 selectInnerLoop( pParse, p, p.pEList, 0, 0, pOrderBy,
4854 distinct, pDest,
4855 addrOutputRow + 1, addrSetAbort );
4856 sqlite3VdbeAddOp1( v, OP_Return, regOutputRow );
4857 VdbeComment( v, "end groupby result generator" );
4858  
4859 /* Generate a subroutine that will reset the group-by accumulator
4860 */
4861 sqlite3VdbeResolveLabel( v, addrReset );
4862 resetAccumulator( pParse, sAggInfo );
4863 sqlite3VdbeAddOp1( v, OP_Return, regReset );
4864  
4865 } /* endif pGroupBy. Begin aggregate queries without GROUP BY: */
4866 else
4867 {
4868 ExprList pDel = null;
4869 #if !SQLITE_OMIT_BTREECOUNT
4870 Table pTab;
4871 if ( ( pTab = isSimpleCount( p, sAggInfo ) ) != null )
4872 {
4873 /* If isSimpleCount() returns a pointer to a Table structure, then
4874 ** the SQL statement is of the form:
4875 **
4876 ** SELECT count() FROM <tbl>
4877 **
4878 ** where the Table structure returned represents table <tbl>.
4879 **
4880 ** This statement is so common that it is optimized specially. The
4881 ** OP_Count instruction is executed either on the intkey table that
4882 ** contains the data for table <tbl> or on one of its indexes. It
4883 ** is better to execute the op on an index, as indexes are almost
4884 ** always spread across less pages than their corresponding tables.
4885 */
4886 int iDb = sqlite3SchemaToIndex( pParse.db, pTab.pSchema );
4887 int iCsr = pParse.nTab++; /* Cursor to scan b-tree */
4888 Index pIdx; /* Iterator variable */
4889 KeyInfo pKeyInfo = null; /* Keyinfo for scanned index */
4890 Index pBest = null; /* Best index found so far */
4891 int iRoot = pTab.tnum; /* Root page of scanned b-tree */
4892  
4893 sqlite3CodeVerifySchema( pParse, iDb );
4894 sqlite3TableLock( pParse, iDb, pTab.tnum, 0, pTab.zName );
4895  
4896 /* Search for the index that has the least amount of columns. If
4897 ** there is such an index, and it has less columns than the table
4898 ** does, then we can assume that it consumes less space on disk and
4899 ** will therefore be cheaper to scan to determine the query result.
4900 ** In this case set iRoot to the root page number of the index b-tree
4901 ** and pKeyInfo to the KeyInfo structure required to navigate the
4902 ** index.
4903 **
4904 ** (2011-04-15) Do not do a full scan of an unordered index.
4905 **
4906 ** In practice the KeyInfo structure will not be used. It is only
4907 ** passed to keep OP_OpenRead happy.
4908 */
4909 for ( pIdx = pTab.pIndex; pIdx != null; pIdx = pIdx.pNext )
4910 {
4911 if ( pIdx.bUnordered == 0 && ( null == pBest || pIdx.nColumn < pBest.nColumn ) )
4912 {
4913 pBest = pIdx;
4914 }
4915 }
4916 if ( pBest != null && pBest.nColumn < pTab.nCol )
4917 {
4918 iRoot = pBest.tnum;
4919 pKeyInfo = sqlite3IndexKeyinfo( pParse, pBest );
4920 }
4921  
4922 /* Open a read-only cursor, execute the OP_Count, close the cursor. */
4923 sqlite3VdbeAddOp3( v, OP_OpenRead, iCsr, iRoot, iDb );
4924 if ( pKeyInfo != null )
4925 {
4926 sqlite3VdbeChangeP4( v, -1, pKeyInfo, P4_KEYINFO_HANDOFF );
4927 }
4928 sqlite3VdbeAddOp2( v, OP_Count, iCsr, sAggInfo.aFunc[0].iMem );
4929 sqlite3VdbeAddOp1( v, OP_Close, iCsr );
4930 explainSimpleCount( pParse, pTab, pBest );
4931 }
4932 else
4933 #endif //* SQLITE_OMIT_BTREECOUNT */
4934 {
4935  
4936 /* Check if the query is of one of the following forms:
4937 **
4938 ** SELECT min(x) FROM ...
4939 ** SELECT max(x) FROM ...
4940 **
4941 ** If it is, then ask the code in where.c to attempt to sort results
4942 ** as if there was an "ORDER ON x" or "ORDER ON x DESC" clause.
4943 ** If where.c is able to produce results sorted in this order, then
4944 ** add vdbe code to break out of the processing loop after the
4945 ** first iteration (since the first iteration of the loop is
4946 ** guaranteed to operate on the row with the minimum or maximum
4947 ** value of x, the only row required).
4948 **
4949 ** A special flag must be passed to sqlite3WhereBegin() to slightly
4950 ** modify behavior as follows:
4951 **
4952 ** + If the query is a "SELECT min(x)", then the loop coded by
4953 ** where.c should not iterate over any values with a NULL value
4954 ** for x.
4955 **
4956 ** + The optimizer code in where.c (the thing that decides which
4957 ** index or indices to use) should place a different priority on
4958 ** satisfying the 'ORDER BY' clause than it does in other cases.
4959 ** Refer to code and comments in where.c for details.
4960 */
4961 ExprList pMinMax = null;
4962 int flag = minMaxQuery( p );
4963 if ( flag != 0 )
4964 {
4965 Debug.Assert( !ExprHasProperty( p.pEList.a[0].pExpr, EP_xIsSelect ) );
4966 pMinMax = sqlite3ExprListDup( db, p.pEList.a[0].pExpr.x.pList, 0 );
4967 pDel = pMinMax;
4968 if ( pMinMax != null )///* && 0 == db.mallocFailed */ )
4969 {
4970 pMinMax.a[0].sortOrder = (u8)( flag != WHERE_ORDERBY_MIN ? 1 : 0 );
4971 pMinMax.a[0].pExpr.op = TK_COLUMN;
4972 }
4973 }
4974  
4975 /* This case runs if the aggregate has no GROUP BY clause. The
4976 ** processing is much simpler since there is only a single row
4977 ** of output.
4978 */
4979 resetAccumulator( pParse, sAggInfo );
4980 pWInfo = sqlite3WhereBegin( pParse, pTabList, pWhere, ref pMinMax, (byte)flag );
4981 if ( pWInfo == null )
4982 {
4983 sqlite3ExprListDelete( db, ref pDel );
4984 goto select_end;
4985 }
4986 updateAccumulator( pParse, sAggInfo );
4987 if ( pMinMax == null && flag != 0 )
4988 {
4989 sqlite3VdbeAddOp2( v, OP_Goto, 0, pWInfo.iBreak );
4990 #if SQLITE_DEBUG
4991 VdbeComment( v, "%s() by index",
4992 ( flag == WHERE_ORDERBY_MIN ? "min" : "max" ) );
4993 #endif
4994 }
4995 sqlite3WhereEnd( pWInfo );
4996 finalizeAggFunctions( pParse, sAggInfo );
4997 }
4998  
4999 pOrderBy = null;
5000 sqlite3ExprIfFalse( pParse, pHaving, addrEnd, SQLITE_JUMPIFNULL );
5001 selectInnerLoop( pParse, p, p.pEList, 0, 0, null, -1,
5002 pDest, addrEnd, addrEnd );
5003  
5004 sqlite3ExprListDelete( db, ref pDel );
5005 }
5006 sqlite3VdbeResolveLabel( v, addrEnd );
5007  
5008 } /* endif aggregate query */
5009  
5010 if ( distinct >= 0 )
5011 {
5012 explainTempTable( pParse, "DISTINCT" );
5013 }
5014  
5015 /* If there is an ORDER BY clause, then we need to sort the results
5016 ** and send them to the callback one by one.
5017 */
5018 if ( pOrderBy != null )
5019 {
5020 explainTempTable( pParse, "ORDER BY" );
5021 generateSortTail( pParse, p, v, pEList.nExpr, pDest );
5022 }
5023  
5024 /* Jump here to skip this query
5025 */
5026 sqlite3VdbeResolveLabel( v, iEnd );
5027  
5028 /* The SELECT was successfully coded. Set the return code to 0
5029 ** to indicate no errors.
5030 */
5031 rc = 0;
5032  
5033 /* Control jumps to here if an error is encountered above, or upon
5034 ** successful coding of the SELECT.
5035 */
5036 select_end:
5037 explainSetInteger( ref pParse.iSelectId, iRestoreSelectId );
5038  
5039 /* Identify column names if results of the SELECT are to be output.
5040 */
5041 if ( rc == SQLITE_OK && pDest.eDest == SRT_Output )
5042 {
5043 generateColumnNames( pParse, pTabList, pEList );
5044 }
5045  
5046 sqlite3DbFree( db, ref sAggInfo.aCol );
5047 sqlite3DbFree( db, ref sAggInfo.aFunc );
5048 return rc;
5049 }
5050  
5051 #if SQLITE_DEBUG
5052 /*
5053 *******************************************************************************
5054 ** The following code is used for testing and debugging only. The code
5055 ** that follows does not appear in normal builds.
5056 **
5057 ** These routines are used to print out the content of all or part of a
5058 ** parse structures such as Select or Expr. Such printouts are useful
5059 ** for helping to understand what is happening inside the code generator
5060 ** during the execution of complex SELECT statements.
5061 **
5062 ** These routine are not called anywhere from within the normal
5063 ** code base. Then are intended to be called from within the debugger
5064 ** or from temporary "printf" statements inserted for debugging.
5065 */
5066 void sqlite3PrintExpr( Expr p )
5067 {
5068 if ( !ExprHasProperty( p, EP_IntValue ) && p.u.zToken != null )
5069 {
5070 sqlite3DebugPrintf( "(%s", p.u.zToken );
5071 }
5072 else
5073 {
5074 sqlite3DebugPrintf( "(%d", p.op );
5075 }
5076 if ( p.pLeft != null )
5077 {
5078 sqlite3DebugPrintf( " " );
5079 sqlite3PrintExpr( p.pLeft );
5080 }
5081 if ( p.pRight != null )
5082 {
5083 sqlite3DebugPrintf( " " );
5084 sqlite3PrintExpr( p.pRight );
5085 }
5086 sqlite3DebugPrintf( ")" );
5087 }
5088 void sqlite3PrintExprList( ExprList pList )
5089 {
5090 int i;
5091 for ( i = 0; i < pList.nExpr; i++ )
5092 {
5093 sqlite3PrintExpr( pList.a[i].pExpr );
5094 if ( i < pList.nExpr - 1 )
5095 {
5096 sqlite3DebugPrintf( ", " );
5097 }
5098 }
5099 }
5100 void sqlite3PrintSelect( Select p, int indent )
5101 {
5102 sqlite3DebugPrintf( "%*sSELECT(%p) ", indent, string.Empty, p );
5103 sqlite3PrintExprList( p.pEList );
5104 sqlite3DebugPrintf( "\n" );
5105 if ( p.pSrc != null )
5106 {
5107 string zPrefix;
5108 int i;
5109 zPrefix = "FROM";
5110 for ( i = 0; i < p.pSrc.nSrc; i++ )
5111 {
5112 SrcList_item pItem = p.pSrc.a[i];
5113 sqlite3DebugPrintf( "%*s ", indent + 6, zPrefix );
5114 zPrefix = string.Empty;
5115 if ( pItem.pSelect != null )
5116 {
5117 sqlite3DebugPrintf( "(\n" );
5118 sqlite3PrintSelect( pItem.pSelect, indent + 10 );
5119 sqlite3DebugPrintf( "%*s)", indent + 8, string.Empty );
5120 }
5121 else if ( pItem.zName != null )
5122 {
5123 sqlite3DebugPrintf( "%s", pItem.zName );
5124 }
5125 if ( pItem.pTab != null )
5126 {
5127 sqlite3DebugPrintf( "(vtable: %s)", pItem.pTab.zName );
5128 }
5129 if ( pItem.zAlias != null )
5130 {
5131 sqlite3DebugPrintf( " AS %s", pItem.zAlias );
5132 }
5133 if ( i < p.pSrc.nSrc - 1 )
5134 {
5135 sqlite3DebugPrintf( "," );
5136 }
5137 sqlite3DebugPrintf( "\n" );
5138 }
5139 }
5140 if ( p.pWhere != null )
5141 {
5142 sqlite3DebugPrintf( "%*s WHERE ", indent, string.Empty );
5143 sqlite3PrintExpr( p.pWhere );
5144 sqlite3DebugPrintf( "\n" );
5145 }
5146 if ( p.pGroupBy != null )
5147 {
5148 sqlite3DebugPrintf( "%*s GROUP BY ", indent, string.Empty );
5149 sqlite3PrintExprList( p.pGroupBy );
5150 sqlite3DebugPrintf( "\n" );
5151 }
5152 if ( p.pHaving != null )
5153 {
5154 sqlite3DebugPrintf( "%*s HAVING ", indent, string.Empty );
5155 sqlite3PrintExpr( p.pHaving );
5156 sqlite3DebugPrintf( "\n" );
5157 }
5158 if ( p.pOrderBy != null )
5159 {
5160 sqlite3DebugPrintf( "%*s ORDER BY ", indent, string.Empty );
5161 sqlite3PrintExprList( p.pOrderBy );
5162 sqlite3DebugPrintf( "\n" );
5163 }
5164 }
5165 /* End of the structure debug printing code
5166 *****************************************************************************/
5167 #endif // * defined(SQLITE_TEST) || defined(SQLITE_DEBUG) */
5168 }
5169 }