Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Allow expressions (including variables) in LIMIT and OFFSET clauses. Ticket #1096. (CVS 2316) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
515e5033a5482f55e7edb66d69ff3da7 |
User & Date: | danielk1977 2005-02-05 12:48:48.000 |
Context
2005-02-06
| ||
02:45 | Add the (highly experimental) omit_readlock pragma that disables the use of readlocks on read-only databases that are connected using ATTACH. (CVS 2317) (check-in: 2155448d21 user: drh tags: trunk) | |
2005-02-05
| ||
12:48 | Allow expressions (including variables) in LIMIT and OFFSET clauses. Ticket #1096. (CVS 2316) (check-in: 515e5033a5 user: danielk1977 tags: trunk) | |
07:33 | Add a numeric version number. Ticket #1097. (CVS 2315) (check-in: a9c33a804d user: danielk1977 tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** ** $Id: expr.c,v 1.192 2005/02/05 12:48:48 danielk1977 Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** Return the 'affinity' of the expression pExpr if any. ** |
︙ | ︙ | |||
507 508 509 510 511 512 513 | pNew->pSrc = sqlite3SrcListDup(p->pSrc); pNew->pWhere = sqlite3ExprDup(p->pWhere); pNew->pGroupBy = sqlite3ExprListDup(p->pGroupBy); pNew->pHaving = sqlite3ExprDup(p->pHaving); pNew->pOrderBy = sqlite3ExprListDup(p->pOrderBy); pNew->op = p->op; pNew->pPrior = sqlite3SelectDup(p->pPrior); | | | | 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 | pNew->pSrc = sqlite3SrcListDup(p->pSrc); pNew->pWhere = sqlite3ExprDup(p->pWhere); pNew->pGroupBy = sqlite3ExprListDup(p->pGroupBy); pNew->pHaving = sqlite3ExprDup(p->pHaving); pNew->pOrderBy = sqlite3ExprListDup(p->pOrderBy); pNew->op = p->op; pNew->pPrior = sqlite3SelectDup(p->pPrior); pNew->pLimit = sqlite3ExprDup(p->pLimit); pNew->pOffset = sqlite3ExprDup(p->pOffset); pNew->iLimit = -1; pNew->iOffset = -1; pNew->ppOpenTemp = 0; pNew->pFetch = 0; pNew->isResolved = 0; pNew->isAgg = 0; return pNew; |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** This file contains SQLite's grammar for SQL. Process this file ** using the lemon parser generator to generate C code that runs ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** This file contains SQLite's grammar for SQL. Process this file ** using the lemon parser generator to generate C code that runs ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** ** @(#) $Id: parse.y,v 1.165 2005/02/05 12:48:48 danielk1977 Exp $ */ %token_prefix TK_ %token_type {Token} %default_type {Token} %extra_argument {Parse *pParse} %syntax_error { if( pParse->zErrMsg==0 ){ |
︙ | ︙ | |||
35 36 37 38 39 40 41 | #include "parse.h" /* ** An instance of this structure holds information about the ** LIMIT clause of a SELECT statement. */ struct LimitVal { | | | | 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | #include "parse.h" /* ** An instance of this structure holds information about the ** LIMIT clause of a SELECT statement. */ struct LimitVal { Expr *pLimit; /* The LIMIT expression. NULL if there is no limit */ Expr *pOffset; /* The OFFSET expression. NULL if there is none */ }; /* ** An instance of this structure is used to store the LIKE, ** GLOB, NOT LIKE, and NOT GLOB operators. */ struct LikeOp { |
︙ | ︙ | |||
339 340 341 342 343 344 345 | multiselect_op(A) ::= UNION(OP). {A = @OP;} multiselect_op(A) ::= UNION ALL. {A = TK_ALL;} multiselect_op(A) ::= INTERSECT(OP). {A = @OP;} multiselect_op(A) ::= EXCEPT(OP). {A = @OP;} %endif // SQLITE_OMIT_COMPOUND_SELECT oneselect(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y) groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). { | | | 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 | multiselect_op(A) ::= UNION(OP). {A = @OP;} multiselect_op(A) ::= UNION ALL. {A = TK_ALL;} multiselect_op(A) ::= INTERSECT(OP). {A = @OP;} multiselect_op(A) ::= EXCEPT(OP). {A = @OP;} %endif // SQLITE_OMIT_COMPOUND_SELECT oneselect(A) ::= SELECT distinct(D) selcollist(W) from(X) where_opt(Y) groupby_opt(P) having_opt(Q) orderby_opt(Z) limit_opt(L). { A = sqlite3SelectNew(W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset); } // The "distinct" nonterminal is true (1) if the DISTINCT keyword is // present and false (0) if it is not. // %type distinct {int} distinct(A) ::= DISTINCT. {A = 1;} |
︙ | ︙ | |||
438 439 440 441 442 443 444 | // is contained inside parentheses. This can be either a subquery or // a grouping of table and subqueries. // %type seltablist_paren {Select*} %destructor seltablist_paren {sqlite3SelectDelete($$);} seltablist_paren(A) ::= select(S). {A = S;} seltablist_paren(A) ::= seltablist(F). { | | | 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 | // is contained inside parentheses. This can be either a subquery or // a grouping of table and subqueries. // %type seltablist_paren {Select*} %destructor seltablist_paren {sqlite3SelectDelete($$);} seltablist_paren(A) ::= select(S). {A = S;} seltablist_paren(A) ::= seltablist(F). { A = sqlite3SelectNew(0,F,0,0,0,0,0,0,0); } %endif // SQLITE_OMIT_SUBQUERY %type dbnm {Token} dbnm(A) ::= . {A.z=0; A.n=0;} dbnm(A) ::= DOT nm(X). {A = X;} |
︙ | ︙ | |||
509 510 511 512 513 514 515 | %type having_opt {Expr*} %destructor having_opt {sqlite3ExprDelete($$);} having_opt(A) ::= . {A = 0;} having_opt(A) ::= HAVING expr(X). {A = X;} %type limit_opt {struct LimitVal} | > > > > | | | | | | | 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 | %type having_opt {Expr*} %destructor having_opt {sqlite3ExprDelete($$);} having_opt(A) ::= . {A = 0;} having_opt(A) ::= HAVING expr(X). {A = X;} %type limit_opt {struct LimitVal} %destructor limit_opt { sqlite3ExprDelete($$.pLimit); sqlite3ExprDelete($$.pOffset); } limit_opt(A) ::= . {A.pLimit = 0; A.pOffset = 0;} limit_opt(A) ::= LIMIT expr(X). {A.pLimit = X; A.pOffset = 0;} limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y). {A.pLimit = X; A.pOffset = Y;} limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). {A.pOffset = X; A.pLimit = Y;} /////////////////////////// The DELETE statement ///////////////////////////// // cmd ::= DELETE FROM fullname(X) where_opt(Y). {sqlite3DeleteFrom(pParse,X,Y);} %type where_opt {Expr*} %destructor where_opt {sqlite3ExprDelete($$);} |
︙ | ︙ | |||
722 723 724 725 726 727 728 | if( A ) A->pSelect = Y; if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0); sqlite3ExprSpan(A,&X->span,&E); } expr(A) ::= expr(X) in_op(N) nm(Y) dbnm(Z). [IN] { SrcList *pSrc = sqlite3SrcListAppend(0,&Y,&Z); A = sqlite3Expr(TK_IN, X, 0, 0); | | | 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 | if( A ) A->pSelect = Y; if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0); sqlite3ExprSpan(A,&X->span,&E); } expr(A) ::= expr(X) in_op(N) nm(Y) dbnm(Z). [IN] { SrcList *pSrc = sqlite3SrcListAppend(0,&Y,&Z); A = sqlite3Expr(TK_IN, X, 0, 0); if( A ) A->pSelect = sqlite3SelectNew(0,pSrc,0,0,0,0,0,0,0); if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0); sqlite3ExprSpan(A,&X->span,Z.z?&Z:&Y); } expr(A) ::= EXISTS(B) LP select(Y) RP(E). { Expr *p = A = sqlite3Expr(TK_EXISTS, 0, 0, 0); if( p ){ p->pSelect = Y; |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | | > > > | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.239 2005/02/05 12:48:48 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ Select *sqlite3SelectNew( ExprList *pEList, /* which columns to include in the result */ SrcList *pSrc, /* the FROM clause -- which tables to scan */ Expr *pWhere, /* the WHERE clause */ ExprList *pGroupBy, /* the GROUP BY clause */ Expr *pHaving, /* the HAVING clause */ ExprList *pOrderBy, /* the ORDER BY clause */ int isDistinct, /* true if the DISTINCT keyword is present */ Expr *pLimit, /* LIMIT value. NULL means not used */ Expr *pOffset /* OFFSET value. NULL means no offset */ ){ Select *pNew; pNew = sqliteMalloc( sizeof(*pNew) ); assert( !pOffset || pLimit ); /* Can't have OFFSET without LIMIT. */ if( pNew==0 ){ sqlite3ExprListDelete(pEList); sqlite3SrcListDelete(pSrc); sqlite3ExprDelete(pWhere); sqlite3ExprListDelete(pGroupBy); sqlite3ExprDelete(pHaving); sqlite3ExprListDelete(pOrderBy); sqlite3ExprDelete(pLimit); sqlite3ExprDelete(pOffset); }else{ if( pEList==0 ){ pEList = sqlite3ExprListAppend(0, sqlite3Expr(TK_ALL,0,0,0), 0); } pNew->pEList = pEList; pNew->pSrc = pSrc; pNew->pWhere = pWhere; pNew->pGroupBy = pGroupBy; pNew->pHaving = pHaving; pNew->pOrderBy = pOrderBy; pNew->isDistinct = isDistinct; pNew->op = TK_SELECT; pNew->pLimit = pLimit; pNew->pOffset = pOffset; pNew->iLimit = -1; pNew->iOffset = -1; } return pNew; } /* |
︙ | ︙ | |||
304 305 306 307 308 309 310 311 312 313 | sqlite3ExprListDelete(p->pEList); sqlite3SrcListDelete(p->pSrc); sqlite3ExprDelete(p->pWhere); sqlite3ExprListDelete(p->pGroupBy); sqlite3ExprDelete(p->pHaving); sqlite3ExprListDelete(p->pOrderBy); sqlite3SelectDelete(p->pPrior); sqliteFree(p); } | > > < < < < < < < < < < < < | 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 | sqlite3ExprListDelete(p->pEList); sqlite3SrcListDelete(p->pSrc); sqlite3ExprDelete(p->pWhere); sqlite3ExprListDelete(p->pGroupBy); sqlite3ExprDelete(p->pHaving); sqlite3ExprListDelete(p->pOrderBy); sqlite3SelectDelete(p->pPrior); sqlite3ExprDelete(p->pLimit); sqlite3ExprDelete(p->pOffset); sqliteFree(p); } /* ** Insert code into "v" that will push the record on the top of the ** stack into the sorter. */ static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){ int i; for(i=0; i<pOrderBy->nExpr; i++){ |
︙ | ︙ | |||
343 344 345 346 347 348 349 | Vdbe *v, /* Generate code into this VM */ Select *p, /* The SELECT statement being coded */ int iContinue, /* Jump here to skip the current record */ int iBreak, /* Jump here to end the loop */ int nPop /* Number of times to pop stack when jumping */ ){ if( p->iOffset>=0 ){ | | | > | 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 | Vdbe *v, /* Generate code into this VM */ Select *p, /* The SELECT statement being coded */ int iContinue, /* Jump here to skip the current record */ int iBreak, /* Jump here to end the loop */ int nPop /* Number of times to pop stack when jumping */ ){ if( p->iOffset>=0 ){ int addr = sqlite3VdbeCurrentAddr(v) + 3; if( nPop>0 ) addr++; sqlite3VdbeAddOp(v, OP_MemIncr, p->iOffset, 0); sqlite3VdbeAddOp(v, OP_IfMemPos, p->iOffset, addr); if( nPop>0 ){ sqlite3VdbeAddOp(v, OP_Pop, nPop, 0); } sqlite3VdbeAddOp(v, OP_Goto, 0, iContinue); VdbeComment((v, "# skip OFFSET records")); } if( p->iLimit>=0 ){ |
︙ | ︙ | |||
1270 1271 1272 1273 1274 1275 1276 | v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db); } return v; } /* ** Compute the iLimit and iOffset fields of the SELECT based on the | | | | | | < < < | > | > | > > | | 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 | v = pParse->pVdbe = sqlite3VdbeCreate(pParse->db); } return v; } /* ** Compute the iLimit and iOffset fields of the SELECT based on the ** pLimit and pOffset expressions. nLimit and nOffset hold the expressions ** that appear in the original SQL statement after the LIMIT and OFFSET ** keywords. Or NULL if those keywords are omitted. iLimit and iOffset ** are the integer memory register numbers for counters used to compute ** the limit and offset. If there is no limit and/or offset, then ** iLimit and iOffset are negative. ** ** This routine changes the values if iLimit and iOffset only if ** a limit or offset is defined by nLimit and nOffset. iLimit and ** iOffset should have been preset to appropriate default values ** (usually but not always -1) prior to calling this routine. ** Only if nLimit>=0 or nOffset>0 do the limit registers get ** redefined. The UNION ALL operator uses this property to force ** the reuse of the same limit and offset registers across multiple ** SELECT statements. */ static void computeLimitRegisters(Parse *pParse, Select *p){ /* ** "LIMIT -1" always shows all rows. There is some ** contraversy about what the correct behavior should be. ** The current implementation interprets "LIMIT 0" to mean ** no rows. */ if( p->pLimit ){ int iMem = pParse->nMem++; Vdbe *v = sqlite3GetVdbe(pParse); if( v==0 ) return; sqlite3ExprCode(pParse, p->pLimit); sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); sqlite3VdbeAddOp(v, OP_Negative, 0, 0); sqlite3VdbeAddOp(v, OP_MemStore, iMem, 1); VdbeComment((v, "# LIMIT counter")); p->iLimit = iMem; } if( p->pOffset ){ int iMem = pParse->nMem++; Vdbe *v = sqlite3GetVdbe(pParse); if( v==0 ) return; sqlite3ExprCode(pParse, p->pOffset); sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0); sqlite3VdbeAddOp(v, OP_Negative, 0, 0); sqlite3VdbeAddOp(v, OP_MemStore, iMem, 1); VdbeComment((v, "# OFFSET counter")); p->iOffset = iMem; } } /* |
︙ | ︙ | |||
1459 1460 1461 1462 1463 1464 1465 | pPrior = p->pPrior; if( pPrior->pOrderBy ){ sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before", selectOpName(p->op)); rc = 1; goto multi_select_end; } | | | 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 | pPrior = p->pPrior; if( pPrior->pOrderBy ){ sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before", selectOpName(p->op)); rc = 1; goto multi_select_end; } if( pPrior->pLimit ){ sqlite3ErrorMsg(pParse,"LIMIT clause should come after %s not before", selectOpName(p->op)); rc = 1; goto multi_select_end; } /* Make sure we have a valid query engine. If not, create a new one. |
︙ | ︙ | |||
1500 1501 1502 1503 1504 1505 1506 | } /* Generate code for the left and right SELECT statements. */ switch( p->op ){ case TK_ALL: { if( p->pOrderBy==0 ){ | > | | | | | | | | 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 | } /* Generate code for the left and right SELECT statements. */ switch( p->op ){ case TK_ALL: { if( p->pOrderBy==0 ){ assert( !pPrior->pLimit ); pPrior->pLimit = p->pLimit; pPrior->pOffset = p->pOffset; rc = sqlite3Select(pParse, pPrior, eDest, iParm, 0, 0, 0, aff); if( rc ){ goto multi_select_end; } p->pPrior = 0; p->iLimit = pPrior->iLimit; p->iOffset = pPrior->iOffset; p->pLimit = 0; p->pOffset = 0; rc = sqlite3Select(pParse, p, eDest, iParm, 0, 0, 0, aff); p->pPrior = pPrior; if( rc ){ goto multi_select_end; } break; } /* For UNION ALL ... ORDER BY fall through to the next case */ } case TK_EXCEPT: case TK_UNION: { int unionTab; /* Cursor number of the temporary table holding result */ int op = 0; /* One of the SRT_ operations to apply to self */ int priorOp; /* The SRT_ operation to apply to prior selects */ Expr *pLimit, *pOffset; /* Saved values of p->nLimit and p->nOffset */ ExprList *pOrderBy; /* The ORDER BY clause for the right SELECT */ int addr; priorOp = p->op==TK_ALL ? SRT_Table : SRT_Union; if( eDest==priorOp && p->pOrderBy==0 && !p->pLimit && !p->pOffset ){ /* We can reuse a temporary table generated by a SELECT to our ** right. */ unionTab = iParm; }else{ /* We will need to create our own temporary table to hold the ** intermediate results. |
︙ | ︙ | |||
1576 1577 1578 1579 1580 1581 1582 | case TK_EXCEPT: op = SRT_Except; break; case TK_UNION: op = SRT_Union; break; case TK_ALL: op = SRT_Table; break; } p->pPrior = 0; pOrderBy = p->pOrderBy; p->pOrderBy = 0; | | | | | > | | | 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 | case TK_EXCEPT: op = SRT_Except; break; case TK_UNION: op = SRT_Union; break; case TK_ALL: op = SRT_Table; break; } p->pPrior = 0; pOrderBy = p->pOrderBy; p->pOrderBy = 0; pLimit = p->pLimit; p->pLimit = 0; pOffset = p->pOffset; p->pOffset = 0; rc = sqlite3Select(pParse, p, op, unionTab, 0, 0, 0, aff); p->pPrior = pPrior; p->pOrderBy = pOrderBy; sqlite3ExprDelete(p->pLimit); p->pLimit = pLimit; p->pOffset = pOffset; p->iLimit = -1; p->iOffset = -1; if( rc ){ goto multi_select_end; } |
︙ | ︙ | |||
1623 1624 1625 1626 1627 1628 1629 | sqlite3VdbeAddOp(v, OP_Close, unionTab, 0); } break; } case TK_INTERSECT: { int tab1, tab2; int iCont, iBreak, iStart; | | | 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 | sqlite3VdbeAddOp(v, OP_Close, unionTab, 0); } break; } case TK_INTERSECT: { int tab1, tab2; int iCont, iBreak, iStart; Expr *pLimit, *pOffset; int addr; /* INTERSECT is different from the others since it requires ** two temporary tables. Hence it has its own case. Begin ** by allocating the tables we will need. */ tab1 = pParse->nTab++; |
︙ | ︙ | |||
1665 1666 1667 1668 1669 1670 1671 | if( rc!=SQLITE_OK ){ goto multi_select_end; } sqlite3VdbeAddOp(v, OP_KeyAsData, tab2, 1); assert( nAddr<sizeof(aAddr)/sizeof(aAddr[0]) ); aAddr[nAddr++] = sqlite3VdbeAddOp(v, OP_SetNumColumns, tab2, 0); p->pPrior = 0; | | | | | > | | | 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 | if( rc!=SQLITE_OK ){ goto multi_select_end; } sqlite3VdbeAddOp(v, OP_KeyAsData, tab2, 1); assert( nAddr<sizeof(aAddr)/sizeof(aAddr[0]) ); aAddr[nAddr++] = sqlite3VdbeAddOp(v, OP_SetNumColumns, tab2, 0); p->pPrior = 0; pLimit = p->pLimit; p->pLimit = 0; pOffset = p->pOffset; p->pOffset = 0; rc = sqlite3Select(pParse, p, SRT_Union, tab2, 0, 0, 0, aff); p->pPrior = pPrior; sqlite3ExprDelete(p->pLimit); p->pLimit = pLimit; p->pOffset = pOffset; if( rc ){ goto multi_select_end; } /* Generate code to take the intersection of the two temporary ** tables. */ |
︙ | ︙ | |||
1955 1956 1957 1958 1959 1960 1961 1962 | pSubitem = &pSrc->a[iFrom]; pSub = pSubitem->pSelect; assert( pSub!=0 ); if( isAgg && subqueryIsAgg ) return 0; if( subqueryIsAgg && pSrc->nSrc>1 ) return 0; pSubSrc = pSub->pSrc; assert( pSubSrc ); if( pSubSrc->nSrc==0 ) return 0; | > > | | | 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 | pSubitem = &pSrc->a[iFrom]; pSub = pSubitem->pSelect; assert( pSub!=0 ); if( isAgg && subqueryIsAgg ) return 0; if( subqueryIsAgg && pSrc->nSrc>1 ) return 0; pSubSrc = pSub->pSrc; assert( pSubSrc ); if( (pSub->pLimit && p->pLimit) || pSub->pOffset || (pSub->pLimit && isAgg) ) return 0; if( pSubSrc->nSrc==0 ) return 0; if( pSub->isDistinct && (pSrc->nSrc>1 || isAgg) ){ return 0; } if( p->isDistinct && subqueryIsAgg ) return 0; if( p->pOrderBy && pSub->pOrderBy ) return 0; /* Restriction 3: If the subquery is a join, make sure the subquery is ** not used as the right operand of an outer join. Examples of why this ** is not allowed: ** ** t1 LEFT OUTER JOIN (t2 JOIN t3) |
︙ | ︙ | |||
2091 2092 2093 2094 2095 2096 2097 | } /* The flattened query is distinct if either the inner or the ** outer query is distinct. */ p->isDistinct = p->isDistinct || pSub->isDistinct; | < < < | < | < | | < < | 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 | } /* The flattened query is distinct if either the inner or the ** outer query is distinct. */ p->isDistinct = p->isDistinct || pSub->isDistinct; if( pSub->pLimit ){ p->pLimit = pSub->pLimit; pSub->pLimit = 0; } /* Finially, delete what is left of the subquery and return ** success. */ sqlite3SelectDelete(pSub); return 1; } |
︙ | ︙ | |||
2326 2327 2328 2329 2330 2331 2332 | /* Prepare the select statement. This call will allocate all cursors ** required to handle the tables and subqueries in the FROM clause. */ if( prepSelectStmt(pParse, p) ){ return SQLITE_ERROR; } | | | > < < > > > > > > > > > > > > > > | 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 | /* Prepare the select statement. This call will allocate all cursors ** required to handle the tables and subqueries in the FROM clause. */ if( prepSelectStmt(pParse, p) ){ return SQLITE_ERROR; } /* Resolve the expressions in the LIMIT and OFFSET clauses. These ** are not allowed to refer to any names, so pass an empty NameContext. */ sNC.pParse = pParse; sNC.hasAgg = 0; sNC.nErr = 0; sNC.nRef = 0; sNC.pEList = 0; sNC.allowAgg = 0; sNC.pSrcList = 0; sNC.pNext = 0; if( sqlite3ExprResolveNames(&sNC, p->pLimit) || sqlite3ExprResolveNames(&sNC, p->pOffset) ){ return SQLITE_ERROR; } /* Set up the local name-context to pass to ExprResolveNames() to ** resolve the expression-list. */ sNC.allowAgg = 1; sNC.pSrcList = p->pSrc; sNC.pNext = pOuterNC; /* NameContext.nDepth stores the depth of recursion for this query. For ** an outer query (e.g. SELECT * FROM sqlite_master) this is 1. For ** a subquery it is 2. For a subquery of a subquery, 3. And so on. ** Parse.nMaxDepth is the maximum depth for any subquery resolved so ** far. This is used to determine the number of aggregate contexts ** required at runtime. |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.367 2005/02/05 12:48:48 danielk1977 Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* ** These #defines should enable >2GB file support on Posix if the ** underlying operating system supports it. If the OS lacks |
︙ | ︙ | |||
1009 1010 1011 1012 1013 1014 1015 | u8 isDistinct; /* True if the DISTINCT keyword is present */ SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ | > | | 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 | u8 isDistinct; /* True if the DISTINCT keyword is present */ SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ Expr *pLimit; /* LIMIT expression. NULL means not used. */ Expr *pOffset; /* OFFSET expression. NULL means not used. */ int iLimit, iOffset; /* Memory registers holding LIMIT & OFFSET counters */ IdList **ppOpenTemp; /* OP_OpenTemp addresses used by multi-selects */ Fetch *pFetch; /* If this stmt is part of a FETCH command */ u8 isResolved; /* True once sqlite3SelectResolve() has run. */ u8 isAgg; /* True if this is an aggregate query */ }; |
︙ | ︙ | |||
1374 1375 1376 1377 1378 1379 1380 | void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Token*); void sqlite3DropIndex(Parse*, SrcList*); void sqlite3AddKeyType(Vdbe*, ExprList*); void sqlite3AddIdxKeyType(Vdbe*, Index*); int sqlite3Select(Parse*, Select*, int, int, Select*, int, int*, char *aff); Select *sqlite3SelectNew(ExprList*,SrcList*,Expr*,ExprList*,Expr*,ExprList*, | | | 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 | void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*, Token*); void sqlite3DropIndex(Parse*, SrcList*); void sqlite3AddKeyType(Vdbe*, ExprList*); void sqlite3AddIdxKeyType(Vdbe*, Index*); int sqlite3Select(Parse*, Select*, int, int, Select*, int, int*, char *aff); Select *sqlite3SelectNew(ExprList*,SrcList*,Expr*,ExprList*,Expr*,ExprList*, int,Expr*,Expr*); void sqlite3SelectDelete(Select*); void sqlite3SelectUnbind(Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTableForReading(Vdbe*, int iCur, Table*); void sqlite3OpenTable(Vdbe*, int iCur, Table*, int); void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); |
︙ | ︙ |
Changes to src/vacuum.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** This file contains code used to implement the VACUUM command. ** ** Most of the code in this file may be omitted by defining the ** SQLITE_OMIT_VACUUM macro. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** This file contains code used to implement the VACUUM command. ** ** Most of the code in this file may be omitted by defining the ** SQLITE_OMIT_VACUUM macro. ** ** $Id: vacuum.c,v 1.38 2005/02/05 12:48:48 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" #ifndef SQLITE_OMIT_VACUUM /* ** Generate a random name of 20 character in length. |
︙ | ︙ | |||
208 209 210 211 212 213 214 | if( rc!=SQLITE_OK ) goto end_of_vacuum; /* Copy over the sequence table */ rc = execExecSql(db, "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " "FROM sqlite_master WHERE name='sqlite_sequence' " | < > > > | 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 | if( rc!=SQLITE_OK ) goto end_of_vacuum; /* Copy over the sequence table */ rc = execExecSql(db, "SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' " "FROM sqlite_master WHERE name='sqlite_sequence' " ); if( rc!=SQLITE_OK ) goto end_of_vacuum; rc = execExecSql(db, "SELECT 'INSERT INTO vacuum_db.' || quote(name) " "|| ' SELECT * FROM ' || quote(name) || ';' " "FROM sqlite_master WHERE name=='sqlite_sequence';" ); if( rc!=SQLITE_OK ) goto end_of_vacuum; |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
39 40 41 42 43 44 45 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** ** $Id: vdbe.c,v 1.453 2005/02/05 12:48:48 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> #include "vdbeInt.h" /* |
︙ | ︙ | |||
4199 4200 4201 4202 4203 4204 4205 | break; } #endif /* SQLITE_OMIT_AUTOINCREMENT */ /* Opcode: MemIncr P1 P2 * ** ** Increment the integer valued memory cell P1 by 1. If P2 is not zero | | | > > > > > > > > > > > > > > > > > | 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 4209 4210 4211 4212 4213 4214 4215 4216 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 4238 4239 4240 4241 4242 4243 | break; } #endif /* SQLITE_OMIT_AUTOINCREMENT */ /* Opcode: MemIncr P1 P2 * ** ** Increment the integer valued memory cell P1 by 1. If P2 is not zero ** and the result after the increment is exactly 1, then jump ** to P2. ** ** This instruction throws an error if the memory cell is not initially ** an integer. */ case OP_MemIncr: { int i = pOp->p1; Mem *pMem; assert( i>=0 && i<p->nMem ); pMem = &p->aMem[i]; assert( pMem->flags==MEM_Int ); pMem->i++; if( pOp->p2>0 && pMem->i==1 ){ pc = pOp->p2 - 1; } break; } /* Opcode: IfMemPos P1 P2 * ** ** If the value of memory cell P1 is 1 or greater, jump to P2. This ** opcode assumes that memory cell P1 holds an integer value. */ case OP_IfMemPos: { int i = pOp->p1; Mem *pMem; assert( i>=0 && i<p->nMem ); pMem = &p->aMem[i]; assert( pMem->flags==MEM_Int ); if( pMem->i>0 ){ pc = pOp->p2 - 1; } break; } /* Opcode: AggReset P1 P2 P3 ** |
︙ | ︙ |
Changes to test/corrupt.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests to make sure SQLite does not crash or # segfault if it sees a corrupt database file. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests to make sure SQLite does not crash or # segfault if it sees a corrupt database file. # # $Id: corrupt.test,v 1.7 2005/02/05 12:48:49 danielk1977 Exp $ catch {file delete -force test.db} catch {file delete -force test.db-journal} set testdir [file dirname $argv0] source $testdir/tester.tcl |
︙ | ︙ | |||
117 118 119 120 121 122 123 | # index on t1) in sqlite_master. Then perform a few different queries # and make sure this is detected as corruption. # do_test corrupt-3.1 { db close copy_file test.bu test.db sqlite3 db test.db | | < | < < | | 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | # index on t1) in sqlite_master. Then perform a few different queries # and make sure this is detected as corruption. # do_test corrupt-3.1 { db close copy_file test.bu test.db sqlite3 db test.db list } {} do_test corrupt-3.2 { set t1_r [execsql {SELECT rootpage FROM sqlite_master WHERE name = 't1i1'}] set t1i1_r [execsql {SELECT rootpage FROM sqlite_master WHERE name = 't1'}] set cookie [expr [execsql {PRAGMA schema_version}] + 1] execsql " PRAGMA writable_schema = 1; UPDATE sqlite_master SET rootpage = $t1_r WHERE name = 't1'; UPDATE sqlite_master SET rootpage = $t1i1_r WHERE name = 't1i1'; PRAGMA writable_schema = 0; PRAGMA schema_version = $cookie; " } {} # This one tests the case caught by code in checkin [2313]. do_test corrupt-3.3 { db close sqlite3 db test.db catchsql { INSERT INTO t1 VALUES('abc'); |
︙ | ︙ |
Changes to test/limit.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the LIMIT ... OFFSET ... clause # of SELECT statements. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the LIMIT ... OFFSET ... clause # of SELECT statements. # # $Id: limit.test,v 1.24 2005/02/05 12:48:49 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # execsql { |
︙ | ︙ | |||
383 384 385 386 387 388 389 390 391 | catchsql { SELECT * FROM t6 LIMIT 3 UNION SELECT * FROM t7 LIMIT 3 } } {1 {LIMIT clause should come after UNION not before}} } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 | catchsql { SELECT * FROM t6 LIMIT 3 UNION SELECT * FROM t7 LIMIT 3 } } {1 {LIMIT clause should come after UNION not before}} } # Test LIMIT and OFFSET using SQL variables. do_test limit-10.1 { set limit 10 db eval { SELECT x FROM t1 LIMIT $limit; } } {31 30 29 28 27 26 25 24 23 22} do_test limit-10.2 { set limit 5 set offset 5 db eval { SELECT x FROM t1 LIMIT $limit OFFSET $offset; } } {26 25 24 23 22} do_test limit-10.3 { set limit -1 db eval { SELECT x FROM t1 WHERE x<10 LIMIT $limit; } } {9 8 7 6 5 4 3 2 1 0} do_test limit-10.4 { set limit 1.5 set rc [catch { db eval { SELECT x FROM t1 WHERE x<10 LIMIT $limit; } } msg] list $rc $msg } {1 {datatype mismatch}} do_test limit-10.5 { set limit "hello world" set rc [catch { db eval { SELECT x FROM t1 WHERE x<10 LIMIT $limit; } } msg] list $rc $msg } {1 {datatype mismatch}} finish_test |