Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Subquery flattening is implemented and passes all regression tests. We still need to add addition tests to the suite to further exercise the flattener, however. (CVS 408) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d5d3e79cc58da5bd315cc1fea1f7cbf4 |
User & Date: | drh 2002-03-02 17:04:08.000 |
Context
2002-03-02
| ||
19:00 | Change the btree node balancers to sort nodes into accending order. This improves insert and delete speed by 25%. (CVS 409) (check-in: abbb999d4f user: drh tags: trunk) | |
17:04 | Subquery flattening is implemented and passes all regression tests. We still need to add addition tests to the suite to further exercise the flattener, however. (CVS 408) (check-in: d5d3e79cc5 user: drh tags: trunk) | |
2002-02-28
| ||
04:10 | Bug fix when -DMEMORY_DEBUG is off. (CVS 407) (check-in: e14b0c82f3 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
13 14 15 16 17 18 19 | ** when syntax rules are reduced. The routines in this file handle the ** following kinds of SQL syntax: ** ** CREATE TABLE ** DROP TABLE ** CREATE INDEX ** DROP INDEX | | | | 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ** when syntax rules are reduced. The routines in this file handle the ** following kinds of SQL syntax: ** ** CREATE TABLE ** DROP TABLE ** CREATE INDEX ** DROP INDEX ** creating ID lists ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** ** $Id: build.c,v 1.81 2002/03/02 17:04:08 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called after a single SQL statement has been ** parsed and we want to execute the VDBE code to implement |
︙ | ︙ | |||
787 788 789 790 791 792 793 | sqliteVdbeAddOp(v, OP_SetCookie, db->next_cookie, 0); sqliteVdbeAddOp(v, OP_Close, 0, 0); } if( pSelect ){ int op = p->isTemp ? OP_OpenWrAux : OP_OpenWrite; sqliteVdbeAddOp(v, op, 1, 0); pParse->nTab = 2; | | | 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 | sqliteVdbeAddOp(v, OP_SetCookie, db->next_cookie, 0); sqliteVdbeAddOp(v, OP_Close, 0, 0); } if( pSelect ){ int op = p->isTemp ? OP_OpenWrAux : OP_OpenWrite; sqliteVdbeAddOp(v, op, 1, 0); pParse->nTab = 2; sqliteSelect(pParse, pSelect, SRT_Table, 1, 0, 0, 0); } sqliteEndWriteOperation(pParse); } } /* ** The parser calls this routine in order to create a new VIEW |
︙ | ︙ |
Changes to src/delete.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 DELETE FROM statements. ** | | | 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 C code routines that are called by the parser ** to handle DELETE FROM statements. ** ** $Id: delete.c,v 1.28 2002/03/02 17:04:08 drh Exp $ */ #include "sqliteInt.h" /* ** Given a table name, find the corresponding table and make sure the ** table is writeable. Generate an error and return NULL if not. If |
︙ | ︙ | |||
98 99 100 101 102 103 104 105 | */ pTabList = sqliteTableTokenToIdList(pParse, pTableName); if( pTabList==0 ) goto delete_from_cleanup; pTab = pTabList->a[0].pTab; /* Resolve the column names in all the expressions. */ if( pWhere ){ | > < | | 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | */ pTabList = sqliteTableTokenToIdList(pParse, pTableName); if( pTabList==0 ) goto delete_from_cleanup; pTab = pTabList->a[0].pTab; /* Resolve the column names in all the expressions. */ base = pParse->nTab++; if( pWhere ){ if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){ goto delete_from_cleanup; } if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ goto delete_from_cleanup; } } |
︙ | ︙ | |||
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | if( pWhere==0 ){ if( db->flags & SQLITE_CountRows ){ /* If counting rows deleted, just count the total number of ** entries in the table. */ int endOfLoop = sqliteVdbeMakeLabel(v); int addr; openOp = pTab->isTemp ? OP_OpenAux : OP_Open; sqliteVdbeAddOp(v, openOp, 0, pTab->tnum); sqliteVdbeAddOp(v, OP_Rewind, 0, sqliteVdbeCurrentAddr(v)+2); addr = sqliteVdbeAddOp(v, OP_AddImm, 1, 0); sqliteVdbeAddOp(v, OP_Next, 0, addr); sqliteVdbeResolveLabel(v, endOfLoop); sqliteVdbeAddOp(v, OP_Close, 0, 0); } sqliteVdbeAddOp(v, OP_Clear, pTab->tnum, pTab->isTemp); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ sqliteVdbeAddOp(v, OP_Clear, pIdx->tnum, pTab->isTemp); } } /* The usual case: There is a WHERE clause so we have to scan through ** the table an pick which records to delete. */ else{ /* Begin the database scan */ | > | < | 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 | if( pWhere==0 ){ if( db->flags & SQLITE_CountRows ){ /* If counting rows deleted, just count the total number of ** entries in the table. */ int endOfLoop = sqliteVdbeMakeLabel(v); int addr; openOp = pTab->isTemp ? OP_OpenAux : OP_Open; assert( base==0 ); sqliteVdbeAddOp(v, openOp, 0, pTab->tnum); sqliteVdbeAddOp(v, OP_Rewind, 0, sqliteVdbeCurrentAddr(v)+2); addr = sqliteVdbeAddOp(v, OP_AddImm, 1, 0); sqliteVdbeAddOp(v, OP_Next, 0, addr); sqliteVdbeResolveLabel(v, endOfLoop); sqliteVdbeAddOp(v, OP_Close, 0, 0); } sqliteVdbeAddOp(v, OP_Clear, pTab->tnum, pTab->isTemp); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ sqliteVdbeAddOp(v, OP_Clear, pIdx->tnum, pTab->isTemp); } } /* The usual case: There is a WHERE clause so we have to scan through ** the table an pick which records to delete. */ else{ /* Begin the database scan */ pWInfo = sqliteWhereBegin(pParse, base, pTabList, pWhere, 1); if( pWInfo==0 ) goto delete_from_cleanup; /* Remember the key of every item to be deleted. */ sqliteVdbeAddOp(v, OP_ListWrite, 0, 0); if( db->flags & SQLITE_CountRows ){ sqliteVdbeAddOp(v, OP_AddImm, 1, 0); } /* End the database scan loop. */ sqliteWhereEnd(pWInfo); /* Delete every item whose key was written to the list during the ** database scan. We have to delete items after the scan is complete ** because deleting an item can change the scan order. */ sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; sqliteVdbeAddOp(v, openOp, base, pTab->tnum); for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){ sqliteVdbeAddOp(v, openOp, base+i, pIdx->tnum); } end = sqliteVdbeMakeLabel(v); |
︙ | ︙ |
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.52 2002/03/02 17:04:08 drh Exp $ */ #include "sqliteInt.h" /* ** Construct a new expression node and return a pointer to it. Memory ** for this node is obtained from sqliteMalloc(). The calling function |
︙ | ︙ | |||
168 169 170 171 172 173 174 175 176 177 178 179 180 181 | if( p==0 ) return 0; pNew = sqliteMalloc( sizeof(*p) ); if( pNew==0 ) return 0; pNew->op = p->op; pNew->pLeft = sqliteExprDup(p->pLeft); pNew->pRight = sqliteExprDup(p->pRight); pNew->pList = sqliteExprListDup(p->pList); pNew->token = p->token; pNew->span = p->span; pNew->pSelect = sqliteSelectDup(p->pSelect); return pNew; } ExprList *sqliteExprListDup(ExprList *p){ ExprList *pNew; | > > > | 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 | if( p==0 ) return 0; pNew = sqliteMalloc( sizeof(*p) ); if( pNew==0 ) return 0; pNew->op = p->op; pNew->pLeft = sqliteExprDup(p->pLeft); pNew->pRight = sqliteExprDup(p->pRight); pNew->pList = sqliteExprListDup(p->pList); pNew->iTable = p->iTable; pNew->iColumn = p->iColumn; pNew->iAgg = p->iAgg; pNew->token = p->token; pNew->span = p->span; pNew->pSelect = sqliteSelectDup(p->pSelect); return pNew; } ExprList *sqliteExprListDup(ExprList *p){ ExprList *pNew; |
︙ | ︙ | |||
306 307 308 309 310 311 312 | } return p->pLeft!=0 || p->pRight!=0 || (p->pList && p->pList->nExpr>0); } } return 0; } | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | | 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 | } return p->pLeft!=0 || p->pRight!=0 || (p->pList && p->pList->nExpr>0); } } return 0; } /* ** Return TRUE if the given string is a row-id column name. */ static int sqliteIsRowid(const char *z){ if( sqliteStrICmp(z, "_ROWID_")==0 ) return 1; if( sqliteStrICmp(z, "ROWID")==0 ) return 1; if( sqliteStrICmp(z, "OID")==0 ) return 1; return 0; } /* ** This routine walks an expression tree and resolves references to ** table columns. Nodes of the form ID.ID or ID resolve into an ** index to the table in the table list and a column offset. The ** Expr.opcode for such nodes is changed to TK_COLUMN. The Expr.iTable ** value is changed to the index of the referenced table in pTabList ** plus the "base" value. The base value will ultimately become the ** VDBE cursor number for a cursor that is pointing into the referenced ** table. The Expr.iColumn value is changed to the index of the column ** of the referenced table. The Expr.iColumn value for the special ** ROWID column is -1. Any INTEGER PRIMARY KEY column is tried as an ** alias for ROWID. ** ** We also check for instances of the IN operator. IN comes in two |
︙ | ︙ | |||
383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 | ** into a memory cell. ** ** Unknown columns or tables provoke an error. The function returns ** the number of errors seen and leaves an error message on pParse->zErrMsg. */ int sqliteExprResolveIds( Parse *pParse, /* The parser context */ IdList *pTabList, /* List of tables used to resolve column names */ ExprList *pEList, /* List of expressions used to resolve "AS" */ Expr *pExpr /* The expression to be analyzed. */ ){ if( pExpr==0 || pTabList==0 ) return 0; switch( pExpr->op ){ /* A lone identifier. Try and match it as follows: ** ** 1. To the name of a column of one of the tables in pTabList ** ** 2. To the right side of an AS keyword in the column list of ** a SELECT statement. (For example, match against 'x' in | > > | 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 | ** into a memory cell. ** ** Unknown columns or tables provoke an error. The function returns ** the number of errors seen and leaves an error message on pParse->zErrMsg. */ int sqliteExprResolveIds( Parse *pParse, /* The parser context */ int base, /* VDBE cursor number for first entry in pTabList */ IdList *pTabList, /* List of tables used to resolve column names */ ExprList *pEList, /* List of expressions used to resolve "AS" */ Expr *pExpr /* The expression to be analyzed. */ ){ if( pExpr==0 || pTabList==0 ) return 0; assert( base+pTabList->nId<=pParse->nTab ); switch( pExpr->op ){ /* A lone identifier. Try and match it as follows: ** ** 1. To the name of a column of one of the tables in pTabList ** ** 2. To the right side of an AS keyword in the column list of ** a SELECT statement. (For example, match against 'x' in |
︙ | ︙ | |||
414 415 416 417 418 419 420 | for(i=0; i<pTabList->nId; i++){ int j; Table *pTab = pTabList->a[i].pTab; if( pTab==0 ) continue; for(j=0; j<pTab->nCol; j++){ if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){ cnt++; | | | 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 | for(i=0; i<pTabList->nId; i++){ int j; Table *pTab = pTabList->a[i].pTab; if( pTab==0 ) continue; for(j=0; j<pTab->nCol; j++){ if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){ cnt++; pExpr->iTable = i + base; if( j==pTab->iPKey ){ /* Substitute the record number for the INTEGER PRIMARY KEY */ pExpr->iColumn = -1; }else{ pExpr->iColumn = j; } pExpr->op = TK_COLUMN; |
︙ | ︙ | |||
440 441 442 443 444 445 446 | pExpr->iColumn = j; pExpr->pLeft = pEList->a[j].pExpr; } } } if( cnt==0 && sqliteIsRowid(z) ){ pExpr->iColumn = -1; | | | 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 | pExpr->iColumn = j; pExpr->pLeft = pEList->a[j].pExpr; } } } if( cnt==0 && sqliteIsRowid(z) ){ pExpr->iColumn = -1; pExpr->iTable = base; cnt = 1 + (pTabList->nId>1); pExpr->op = TK_COLUMN; } sqliteFree(z); if( cnt==0 ){ sqliteSetNString(&pParse->zErrMsg, "no such column: ", -1, pExpr->token.z, pExpr->token.n, 0); |
︙ | ︙ | |||
492 493 494 495 496 497 498 | if( pTab==0 ) continue; if( pTabList->a[i].zAlias ){ zTab = pTabList->a[i].zAlias; }else{ zTab = pTab->zName; } if( sqliteStrICmp(zTab, zLeft)!=0 ) continue; | | | | 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 | if( pTab==0 ) continue; if( pTabList->a[i].zAlias ){ zTab = pTabList->a[i].zAlias; }else{ zTab = pTab->zName; } if( sqliteStrICmp(zTab, zLeft)!=0 ) continue; if( 0==(cntTab++) ) pExpr->iTable = i + base; for(j=0; j<pTab->nCol; j++){ if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){ cnt++; pExpr->iTable = i + base; if( j==pTab->iPKey ){ /* Substitute the record number for the INTEGER PRIMARY KEY */ pExpr->iColumn = -1; }else{ pExpr->iColumn = j; } } |
︙ | ︙ | |||
536 537 538 539 540 541 542 | pExpr->op = TK_COLUMN; break; } case TK_IN: { Vdbe *v = sqliteGetVdbe(pParse); if( v==0 ) return 1; | | > | | 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 | pExpr->op = TK_COLUMN; break; } case TK_IN: { Vdbe *v = sqliteGetVdbe(pParse); if( v==0 ) return 1; if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pExpr->pLeft) ){ return 1; } if( pExpr->pSelect ){ /* Case 1: expr IN (SELECT ...) ** ** Generate code to write the results of the select into a temporary ** table. The cursor number of the temporary table has already ** been put in iTable by sqliteExprResolveInSelect(). */ pExpr->iTable = pParse->nTab++; sqliteVdbeAddOp(v, OP_OpenTemp, pExpr->iTable, 1); sqliteSelect(pParse, pExpr->pSelect, SRT_Set, pExpr->iTable, 0,0,0); }else if( pExpr->pList ){ /* Case 2: expr IN (exprlist) ** ** Create a set to put the exprlist values in. The Set id is stored ** in iTable. */ int i, iSet; |
︙ | ︙ | |||
597 598 599 600 601 602 603 | case TK_SELECT: { /* This has to be a scalar SELECT. Generate code to put the ** value of this select in a memory cell and record the number ** of the memory cell in iColumn. */ pExpr->iColumn = pParse->nMem++; | | | | > | | 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 | case TK_SELECT: { /* This has to be a scalar SELECT. Generate code to put the ** value of this select in a memory cell and record the number ** of the memory cell in iColumn. */ pExpr->iColumn = pParse->nMem++; if( sqliteSelect(pParse, pExpr->pSelect, SRT_Mem, pExpr->iColumn,0,0,0) ){ return 1; } break; } /* For all else, just recursively walk the tree */ default: { if( pExpr->pLeft && sqliteExprResolveIds(pParse, base, pTabList, pEList, pExpr->pLeft) ){ return 1; } if( pExpr->pRight && sqliteExprResolveIds(pParse, base, pTabList, pEList, pExpr->pRight) ){ return 1; } if( pExpr->pList ){ int i; ExprList *pList = pExpr->pList; for(i=0; i<pList->nExpr; i++){ Expr *pArg = pList->a[i].pExpr; if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pArg) ){ return 1; } } } } } return 0; |
︙ | ︙ |
Changes to src/insert.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 INSERT statements 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 C code routines that are called by the parser ** to handle INSERT statements in SQLite. ** ** $Id: insert.c,v 1.46 2002/03/02 17:04:08 drh Exp $ */ #include "sqliteInt.h" /* ** This routine is call to handle SQL of the following forms: ** ** insert into TABLE (IDLIST) values(EXPRLIST) |
︙ | ︙ | |||
80 81 82 83 84 85 86 | ** in a temporary table. If data is coming from an expression list, ** then we just have to count the number of expressions. */ if( pSelect ){ int rc; srcTab = pParse->nTab++; sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0); | | < < < | | 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | ** in a temporary table. If data is coming from an expression list, ** then we just have to count the number of expressions. */ if( pSelect ){ int rc; srcTab = pParse->nTab++; sqliteVdbeAddOp(v, OP_OpenTemp, srcTab, 0); rc = sqliteSelect(pParse, pSelect, SRT_Table, srcTab, 0,0,0); if( rc || pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup; assert( pSelect->pEList ); nColumn = pSelect->pEList->nExpr; }else{ IdList dummy; assert( pList!=0 ); srcTab = -1; assert( pList ); nColumn = pList->nExpr; dummy.nId = 0; for(i=0; i<nColumn; i++){ if( sqliteExprResolveIds(pParse, 0, &dummy, 0, pList->a[i].pExpr) ){ goto insert_cleanup; } } } /* Make sure the number of columns in the source data matches the number ** of columns to be inserted into the table. |
︙ | ︙ | |||
179 180 181 182 183 184 185 186 187 188 189 190 191 192 | openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; sqliteVdbeAddOp(v, openOp, base, pTab->tnum); sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC); for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum); sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC); } /* If the data source is a SELECT statement, then we have to create ** a loop because there might be multiple rows of data. If the data ** source is an expression list, then exactly one row will be inserted ** and the loop is not used. */ if( srcTab>=0 ){ | > | 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 | openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; sqliteVdbeAddOp(v, openOp, base, pTab->tnum); sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC); for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){ sqliteVdbeAddOp(v, openOp, idx+base, pIdx->tnum); sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC); } pParse->nTab += idx; /* If the data source is a SELECT statement, then we have to create ** a loop because there might be multiple rows of data. If the data ** source is an expression list, then exactly one row will be inserted ** and the loop is not used. */ if( srcTab>=0 ){ |
︙ | ︙ |
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.54 2002/03/02 17:04:08 drh Exp $ */ %token_prefix TK_ %token_type {Token} %default_type {Token} %extra_argument {Parse *pParse} %syntax_error { sqliteSetString(&pParse->zErrMsg,"syntax error",0); |
︙ | ︙ | |||
198 199 200 201 202 203 204 | cmd ::= DROP VIEW ids(X). { sqliteDropTable(pParse, &X); } //////////////////////// The SELECT statement ///////////////////////////////// // cmd ::= select(X). { | | | 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 | cmd ::= DROP VIEW ids(X). { sqliteDropTable(pParse, &X); } //////////////////////// The SELECT statement ///////////////////////////////// // cmd ::= select(X). { sqliteSelect(pParse, X, SRT_Callback, 0, 0, 0, 0); sqliteSelectDelete(X); } %type select {Select*} %destructor select {sqliteSelectDelete($$);} %type oneselect {Select*} %destructor oneselect {sqliteSelectDelete($$);} |
︙ | ︙ |
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 | ** 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.71 2002/03/02 17:04:08 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ |
︙ | ︙ | |||
227 228 229 230 231 232 233 | } /* ** Generate code that will tell the VDBE how many columns there ** are in the result and the name for each column. This information ** is used to provide "argc" and "azCol[]" values in the callback. */ | | > > | > > | 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 | } /* ** Generate code that will tell the VDBE how many columns there ** are in the result and the name for each column. This information ** is used to provide "argc" and "azCol[]" values in the callback. */ static void generateColumnNames( Parse *pParse, /* Parser context */ int base, /* VDBE cursor corresponding to first entry in pTabList */ IdList *pTabList, /* List of tables */ ExprList *pEList /* Expressions defining the result set */ ){ Vdbe *v = pParse->pVdbe; int i; if( pParse->colNamesSet || v==0 || sqlite_malloc_failed ) return; pParse->colNamesSet = 1; sqliteVdbeAddOp(v, OP_ColumnCount, pEList->nExpr, 0); for(i=0; i<pEList->nExpr; i++){ Expr *p; |
︙ | ︙ | |||
251 252 253 254 255 256 257 | if( p==0 ) continue; showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0; if( p->span.z && p->span.z[0] && !showFullNames ){ int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0); sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n); sqliteVdbeCompressSpace(v, addr); }else if( p->op==TK_COLUMN && pTabList ){ | | | | 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 | if( p==0 ) continue; showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0; if( p->span.z && p->span.z[0] && !showFullNames ){ int addr = sqliteVdbeAddOp(v,OP_ColumnName, i, 0); sqliteVdbeChangeP3(v, -1, p->span.z, p->span.n); sqliteVdbeCompressSpace(v, addr); }else if( p->op==TK_COLUMN && pTabList ){ Table *pTab = pTabList->a[p->iTable - base].pTab; char *zCol; int iCol = p->iColumn; if( iCol<0 ) iCol = pTab->iPKey; assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) ); zCol = iCol<0 ? "_ROWID_" : pTab->aCol[iCol].zName; if( pTabList->nId>1 || showFullNames ){ char *zName = 0; char *zTab; zTab = pTabList->a[p->iTable - base].zAlias; if( showFullNames || zTab==0 ) zTab = pTab->zName; sqliteSetString(&zName, zTab, ".", zCol, 0); sqliteVdbeAddOp(v, OP_ColumnName, i, 0); sqliteVdbeChangeP3(v, -1, zName, strlen(zName)); sqliteFree(zName); }else{ sqliteVdbeAddOp(v, OP_ColumnName, i, 0); |
︙ | ︙ | |||
636 637 638 639 640 641 642 | }else{ sqliteVdbeAddOp(v, OP_OpenTemp, unionTab, 0); } } /* Code the SELECT statements to our left */ | | | | | 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 | }else{ sqliteVdbeAddOp(v, OP_OpenTemp, unionTab, 0); } } /* Code the SELECT statements to our left */ rc = sqliteSelect(pParse, pPrior, priorOp, unionTab, 0, 0, 0); if( rc ) return rc; /* Code the current SELECT statement */ switch( p->op ){ case TK_EXCEPT: op = SRT_Except; break; case TK_UNION: op = SRT_Union; break; case TK_ALL: op = SRT_Table; break; } p->pPrior = 0; rc = sqliteSelect(pParse, p, op, unionTab, 0, 0, 0); p->pPrior = pPrior; if( rc ) return rc; /* Convert the data in the temporary table into whatever form ** it is that we currently need. */ if( eDest!=priorOp ){ int iCont, iBreak, iStart; assert( p->pEList ); generateColumnNames(pParse, p->base, 0, p->pEList); iBreak = sqliteVdbeMakeLabel(v); iCont = sqliteVdbeMakeLabel(v); sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak); iStart = sqliteVdbeCurrentAddr(v); rc = selectInnerLoop(pParse, 0, unionTab, p->pEList->nExpr, p->pOrderBy, -1, eDest, iParm, iCont, iBreak); |
︙ | ︙ | |||
694 695 696 697 698 699 700 | return 1; } sqliteVdbeAddOp(v, OP_OpenTemp, tab1, 1); sqliteVdbeAddOp(v, OP_KeyAsData, tab1, 1); /* Code the SELECTs to our left into temporary table "tab1". */ | | | | | 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 | return 1; } sqliteVdbeAddOp(v, OP_OpenTemp, tab1, 1); sqliteVdbeAddOp(v, OP_KeyAsData, tab1, 1); /* Code the SELECTs to our left into temporary table "tab1". */ rc = sqliteSelect(pParse, pPrior, SRT_Union, tab1, 0, 0, 0); if( rc ) return rc; /* Code the current SELECT into temporary table "tab2" */ sqliteVdbeAddOp(v, OP_OpenTemp, tab2, 1); sqliteVdbeAddOp(v, OP_KeyAsData, tab2, 1); p->pPrior = 0; rc = sqliteSelect(pParse, p, SRT_Union, tab2, 0, 0, 0); p->pPrior = pPrior; if( rc ) return rc; /* Generate code to take the intersection of the two temporary ** tables. */ assert( p->pEList ); generateColumnNames(pParse, p->base, 0, p->pEList); iBreak = sqliteVdbeMakeLabel(v); iCont = sqliteVdbeMakeLabel(v); sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak); iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0); sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont); rc = selectInnerLoop(pParse, 0, tab1, p->pEList->nExpr, p->pOrderBy, -1, eDest, iParm, |
︙ | ︙ | |||
741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 | selectOpName(p->op), " do not have the same number of result columns", 0); pParse->nErr++; return 1; } pParse->nTab = base; return 0; } /* ** This routine attempts to flatten subqueries in order to speed ** execution. It returns 1 if it makes changes and 0 if no flattening ** occurs. ** ** To understand the concept of flattening, consider the following ** query: ** ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5 ** ** The default way of implementing this query is to execute the ** subquery first and store the results in a temporary table, then ** run the outer query on that temporary table. This requires two ** passes over the data. Furthermore, because the temporary table ** has no indices, the WHERE clause on the outer query cannot be | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | > > | > > > > > | > | > > > > > > > > > > | | < | | | | | > > > | > > > | > > > > > > > | > | > | > > > > | < > > > > > > > > > > | < > > > | > > > > > > > > > > > | < > | | > > > > > > > > > | | | 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 | selectOpName(p->op), " do not have the same number of result columns", 0); pParse->nErr++; return 1; } pParse->nTab = base; return 0; } /* ** Recursively scan through an expression tree. For every reference ** to a column in table number iFrom, change that reference to the ** same column in table number iTo. */ static void changeTables(Expr *pExpr, int iFrom, int iTo){ if( pExpr==0 ) return; if( pExpr->op==TK_COLUMN && pExpr->iTable==iFrom ){ pExpr->iTable = iTo; }else{ changeTables(pExpr->pLeft, iFrom, iTo); changeTables(pExpr->pRight, iFrom, iTo); if( pExpr->pList ){ int i; for(i=0; i<pExpr->pList->nExpr; i++){ changeTables(pExpr->pList->a[i].pExpr, iFrom, iTo); } } } } /* ** Scan through the expression pExpr. Replace every reference to ** a column in table number iTable with a copy of the corresponding ** entry in pEList. When make a copy of pEList, change references ** to columns in table iSub into references to table iTable. ** ** This routine is part of the flattening procedure. A subquery ** whose result set is defined by pEList appears as entry in the ** FROM clause of a SELECT such that the VDBE cursor assigned to that ** FORM clause entry is iTable. This routine make the necessary ** changes to pExpr so that it refers directly to the source table ** of the subquery rather the result set of the subquery. */ static void substExpr(Expr *pExpr, int iTable, ExprList *pEList, int iSub){ if( pExpr==0 ) return; if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable ){ Expr *pNew; assert( pEList!=0 && pExpr->iColumn>=0 && pExpr->iColumn<pEList->nExpr ); assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 ); pNew = pEList->a[pExpr->iColumn].pExpr; assert( pNew!=0 ); pExpr->op = pNew->op; pExpr->pLeft = sqliteExprDup(pNew->pLeft); pExpr->pRight = sqliteExprDup(pNew->pRight); pExpr->pList = sqliteExprListDup(pNew->pList); pExpr->iTable = pNew->iTable; pExpr->iColumn = pNew->iColumn; pExpr->iAgg = pNew->iAgg; if( iSub!=iTable ){ changeTables(pExpr, iSub, iTable); } }else{ static void substExprList(ExprList*,int,ExprList*,int); substExpr(pExpr->pLeft, iTable, pEList, iSub); substExpr(pExpr->pRight, iTable, pEList, iSub); substExprList(pExpr->pList, iTable, pEList, iSub); } } static void substExprList(ExprList *pList, int iTable, ExprList *pEList, int iSub){ int i; if( pList==0 ) return; for(i=0; i<pList->nExpr; i++){ substExpr(pList->a[i].pExpr, iTable, pEList, iSub); } } /* ** This routine attempts to flatten subqueries in order to speed ** execution. It returns 1 if it makes changes and 0 if no flattening ** occurs. ** ** To understand the concept of flattening, consider the following ** query: ** ** SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5 ** ** The default way of implementing this query is to execute the ** subquery first and store the results in a temporary table, then ** run the outer query on that temporary table. This requires two ** passes over the data. Furthermore, because the temporary table ** has no indices, the WHERE clause on the outer query cannot be ** optimized. ** ** This routine attempts to rewrite queries such as the above into ** a single flat select, like this: ** ** SELECT x+y AS a FROM t1 WHERE z<100 AND a>5 ** ** The code generated for this simpification gives the same result ** but only has to scan the data once. And because indices might ** exist on the table t1, a complete scan of the data might be ** avoided. ** ** Flattening is only attempted if all of the following are true: ** ** (1) The subquery and the outer query do not both use aggregates. ** ** (2) The subquery is not an aggregate or the outer query is not a join. ** ** (3) The subquery is not a join. ** ** (4) The subquery is not DISTINCT or the outer query is not a join. ** ** (5) The subquery is not DISTINCT or the outer query does not use ** aggregates. ** ** (6) The subquery does not use aggregates or the outer query is not ** DISTINCT. ** ** In this routine, the "p" parameter is a pointer to the outer query. ** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates. ** ** If flattening is not attempted, this routine is a no-op and return 0. ** If flattening is attempted this routine returns 1. ** ** All of the expression analysis must occur on both the outer query and ** the subquery before this routine runs. */ int flattenSubquery(Select *p, int iFrom, int isAgg, int subqueryIsAgg){ Select *pSub; IdList *pSrc, *pSubSrc; ExprList *pList; int i; int iParent, iSub; Expr *pWhere; /* Check to see if flattening is permitted. Return 0 if not. */ if( p==0 ) return 0; pSrc = p->pSrc; assert( pSrc && iFrom>=0 && iFrom<pSrc->nId ); pSub = pSrc->a[iFrom].pSelect; assert( pSub!=0 ); if( isAgg && subqueryIsAgg ) return 0; if( subqueryIsAgg && pSrc->nId>1 ) return 0; pSubSrc = pSub->pSrc; assert( pSubSrc ); if( pSubSrc->nId>1 ) return 0; if( pSub->isDistinct && pSrc->nId>1 ) return 0; if( pSub->isDistinct && isAgg ) return 0; if( p->isDistinct && subqueryIsAgg ) return 0; /* If we reach this point, it means flatting is permitted for the ** i-th entry of the FROM clause in the outer query. */ iParent = p->base + iFrom; iSub = pSub->base; substExprList(p->pEList, iParent, pSub->pEList, iSub); pList = p->pEList; for(i=0; i<pList->nExpr; i++){ if( pList->a[i].zName==0 ){ Expr *pExpr = pList->a[i].pExpr; pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n); } } substExprList(p->pGroupBy, iParent, pSub->pEList, iSub); substExpr(p->pHaving, iParent, pSub->pEList, iSub); substExprList(p->pOrderBy, iParent, pSub->pEList, iSub); if( pSub->pWhere ){ pWhere = sqliteExprDup(pSub->pWhere); if( iParent!=iSub ){ changeTables(pWhere, iSub, iParent); } }else{ pWhere = 0; } if( subqueryIsAgg ){ assert( p->pHaving==0 ); p->pHaving = pWhere; substExpr(p->pHaving, iParent, pSub->pEList, iSub); }else if( p->pWhere==0 ){ p->pWhere = pWhere; }else{ substExpr(p->pWhere, iParent, pSub->pEList, iSub); if( pWhere ){ p->pWhere = sqliteExpr(TK_AND, p->pWhere, pWhere, 0); } } p->isDistinct = p->isDistinct || pSub->isDistinct; if( pSrc->a[iFrom].pTab && pSrc->a[iFrom].pTab->isTransient ){ sqliteDeleteTable(0, pSrc->a[iFrom].pTab); } pSrc->a[iFrom].pTab = pSubSrc->a[0].pTab; pSubSrc->a[0].pTab = 0; pSrc->a[iFrom].pSelect = pSubSrc->a[0].pSelect; pSubSrc->a[0].pSelect = 0; sqliteSelectDelete(pSub); return 1; } /* ** Analyze the SELECT statement passed in as an argument to see if it ** is a simple min() or max() query. If it is and this query can be ** satisfied using a single seek to the beginning or end of an index, ** then generate the code for this SELECT return 1. If this is not a ** simple min() or max() query, then return 0; |
︙ | ︙ | |||
877 878 879 880 881 882 883 | /* Identify column names if we will be using the callback. This ** step is skipped if the output is going to a table or a memory cell. */ v = sqliteGetVdbe(pParse); if( v==0 ) return 0; if( eDest==SRT_Callback ){ | | | | 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 | /* Identify column names if we will be using the callback. This ** step is skipped if the output is going to a table or a memory cell. */ v = sqliteGetVdbe(pParse); if( v==0 ) return 0; if( eDest==SRT_Callback ){ generateColumnNames(pParse, p->base, p->pSrc, p->pEList); } /* Generating code to find the min or the max. Basically all we have ** to do is find the first or the last entry in the chosen index. If ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first ** or last entry in the main table. */ if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){ sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0); pParse->schemaVerified = 1; } openOp = pTab->isTemp ? OP_OpenAux : OP_Open; base = p->base; sqliteVdbeAddOp(v, openOp, base, pTab->tnum); sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC); if( pIdx==0 ){ sqliteVdbeAddOp(v, seekOp, base, 0); }else{ sqliteVdbeAddOp(v, openOp, base+1, pIdx->tnum); sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC); |
︙ | ︙ | |||
945 946 947 948 949 950 951 | ** This routine does NOT free the Select structure passed in. The ** calling function needs to do that. */ int sqliteSelect( Parse *pParse, /* The parser context */ Select *p, /* The SELECT statement being coded. */ int eDest, /* One of: SRT_Callback Mem Set Union Except */ | | > > > | 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 | ** This routine does NOT free the Select structure passed in. The ** calling function needs to do that. */ int sqliteSelect( Parse *pParse, /* The parser context */ Select *p, /* The SELECT statement being coded. */ int eDest, /* One of: SRT_Callback Mem Set Union Except */ int iParm, /* Save result in this memory location, if >=0 */ Select *pParent, /* Another SELECT for which this is a sub-query */ int parentTab, /* Index in pParent->pSrc of this query */ int parentAgg /* True if pParent uses aggregate functions */ ){ int i; WhereInfo *pWInfo; Vdbe *v; int isAgg = 0; /* True for select lists like "count(*)" */ ExprList *pEList; /* List of columns to extract. */ IdList *pTabList; /* List of tables to select from */ |
︙ | ︙ | |||
979 980 981 982 983 984 985 | pTabList = p->pSrc; pWhere = p->pWhere; pOrderBy = p->pOrderBy; pGroupBy = p->pGroupBy; pHaving = p->pHaving; isDistinct = p->isDistinct; | | > | | > < < < < < < < < < < > | < < < < < < < < < < < < < < < < < < | < | | | | | < < < < < < < < < < < | | > > | > > > > > > > > > > > | 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 | pTabList = p->pSrc; pWhere = p->pWhere; pOrderBy = p->pOrderBy; pGroupBy = p->pGroupBy; pHaving = p->pHaving; isDistinct = p->isDistinct; /* Allocate a block of VDBE cursors, one for each table in the FROM clause. ** The WHERE processing requires that the cursors for the tables in the ** FROM clause be consecutive. */ base = p->base = pParse->nTab; pParse->nTab += pTabList->nId; /* ** Do not even attempt to generate any code if we have already seen ** errors before this routine starts. */ if( pParse->nErr>0 ) goto select_end; /* Look up every table in the table list and create an appropriate ** columnlist in pEList if there isn't one already. (The parser leaves ** a NULL in the p->pEList if the SQL said "SELECT * FROM ...") */ if( fillInColumnList(pParse, p) ){ goto select_end; } pEList = p->pEList; if( pEList==0 ) goto select_end; /* If writing to memory or generating a set ** only a single column may be output. */ if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){ sqliteSetString(&pParse->zErrMsg, "only a single result allowed for " "a SELECT that is part of an expression", 0); pParse->nErr++; goto select_end; } /* ORDER BY is ignored if we are not sending the result to a callback. */ if( eDest!=SRT_Callback ){ sqliteExprListDelete(p->pOrderBy); pOrderBy = p->pOrderBy = 0; } /* At this point, we should have allocated all the cursors that we ** need to handle subquerys and temporary tables. ** ** Resolve the column names and do a semantics check on all the expressions. */ for(i=0; i<pEList->nExpr; i++){ if( sqliteExprResolveIds(pParse, base, pTabList, 0, pEList->a[i].pExpr) ){ goto select_end; } if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){ goto select_end; } } if( pWhere ){ if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pWhere) ){ goto select_end; } if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ goto select_end; } } if( pOrderBy ){ for(i=0; i<pOrderBy->nExpr; i++){ Expr *pE = pOrderBy->a[i].pExpr; if( sqliteExprIsConstant(pE) ){ sqliteSetString(&pParse->zErrMsg, "ORDER BY expressions should not be constant", 0); pParse->nErr++; goto select_end; } if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){ goto select_end; } if( sqliteExprCheck(pParse, pE, isAgg, 0) ){ goto select_end; } } } if( pGroupBy ){ for(i=0; i<pGroupBy->nExpr; i++){ Expr *pE = pGroupBy->a[i].pExpr; if( sqliteExprIsConstant(pE) ){ sqliteSetString(&pParse->zErrMsg, "GROUP BY expressions should not be constant", 0); pParse->nErr++; goto select_end; } if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){ goto select_end; } if( sqliteExprCheck(pParse, pE, isAgg, 0) ){ goto select_end; } } } if( pHaving ){ if( pGroupBy==0 ){ sqliteSetString(&pParse->zErrMsg, "a GROUP BY clause is required " "before HAVING", 0); pParse->nErr++; goto select_end; } if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pHaving) ){ goto select_end; } if( sqliteExprCheck(pParse, pHaving, isAgg, 0) ){ goto select_end; } } /* Check for the special case of a min() or max() function by itself ** in the result set. */ if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){ rc = 0; goto select_end; } /* Begin generating code. */ v = sqliteGetVdbe(pParse); if( v==0 ) goto select_end; /* Generate code for all sub-queries in the FROM clause */ for(i=0; i<pTabList->nId; i++){ if( pTabList->a[i].pSelect==0 ) continue; sqliteVdbeAddOp(v, OP_OpenTemp, base+i, 0); sqliteSelect(pParse, pTabList->a[i].pSelect, SRT_Table, base+i, p, i, isAgg); } /* Check to see if this is a subquery that can be "flattened" into its parent. ** If flattening is a possiblity, do so and return immediately. */ if( flattenSubquery(pParent, parentTab, parentAgg, isAgg) ){ return rc; } pTabList = p->pSrc; pWhere = p->pWhere; pOrderBy = p->pOrderBy; pGroupBy = p->pGroupBy; pHaving = p->pHaving; isDistinct = p->isDistinct; /* Do an analysis of aggregate expressions. */ sqliteAggregateInfoReset(pParse); if( isAgg ){ assert( pParse->nAgg==0 ); for(i=0; i<pEList->nExpr; i++){ |
︙ | ︙ | |||
1191 1192 1193 1194 1195 1196 1197 | } /* Identify column names if we will be using in the callback. This ** step is skipped if the output is going to a table or a memory cell. */ if( eDest==SRT_Callback ){ | | | 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 | } /* Identify column names if we will be using in the callback. This ** step is skipped if the output is going to a table or a memory cell. */ if( eDest==SRT_Callback ){ generateColumnNames(pParse, p->base, pTabList, pEList); } /* Reset the aggregator */ if( isAgg ){ sqliteVdbeAddOp(v, OP_AggReset, 0, pParse->nAgg); for(i=0; i<pParse->nAgg; i++){ |
︙ | ︙ | |||
1218 1219 1220 1221 1222 1223 1224 | /* Initialize the memory cell to NULL */ if( eDest==SRT_Mem ){ sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeAddOp(v, OP_MemStore, iParm, 1); } | | > > > > > > | | 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 | /* Initialize the memory cell to NULL */ if( eDest==SRT_Mem ){ sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeAddOp(v, OP_MemStore, iParm, 1); } /* Open a temporary table to use for the distinct set. */ if( isDistinct ){ distinct = pParse->nTab++; sqliteVdbeAddOp(v, OP_OpenTemp, distinct, 1); }else{ distinct = -1; } /* Begin the database scan */ pWInfo = sqliteWhereBegin(pParse, p->base, pTabList, pWhere, 0); if( pWInfo==0 ) goto select_end; /* Use the standard inner loop if we are not dealing with ** aggregates */ if( !isAgg ){ if( selectInnerLoop(pParse, pEList, 0, 0, pOrderBy, distinct, eDest, iParm, |
︙ | ︙ | |||
1305 1306 1307 1308 1309 1310 1311 | /* If there is an ORDER BY clause, then we need to sort the results ** and send them to the callback one by one. */ if( pOrderBy ){ generateSortTail(v, pEList->nExpr); } | < > | 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 | /* If there is an ORDER BY clause, then we need to sort the results ** and send them to the callback one by one. */ if( pOrderBy ){ generateSortTail(v, pEList->nExpr); } /* Issue a null callback if that is what the user wants. */ if( (pParse->db->flags & SQLITE_NullCallback)!=0 && eDest==SRT_Callback ){ sqliteVdbeAddOp(v, OP_NullCallback, pEList->nExpr, 0); } /* The SELECT was successfully coded. Set the return code to 0 ** to indicate no errors. */ rc = 0; /* Control jumps to here if an error is encountered above, or upon ** successful coding of the SELECT. */ select_end: pParse->nTab = base; sqliteAggregateInfoReset(pParse); return rc; } |
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.97 2002/03/02 17:04:08 drh Exp $ */ #include "sqlite.h" #include "hash.h" #include "vdbe.h" #include "parse.h" #include "btree.h" #include <stdio.h> |
︙ | ︙ | |||
430 431 432 433 434 435 436 437 438 439 440 441 442 443 | struct WhereInfo { Parse *pParse; IdList *pTabList; /* List of tables in the join */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int base; /* Index of first Open opcode */ int nLevel; /* Number of nested loop */ WhereLevel a[1]; /* Information about each nest loop in the WHERE */ }; /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** | > > | 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 | struct WhereInfo { Parse *pParse; IdList *pTabList; /* List of tables in the join */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int base; /* Index of first Open opcode */ int nLevel; /* Number of nested loop */ int savedNTab; /* Value of pParse->nTab before WhereBegin() */ int peakNTab; /* Value of pParse->nTab after WhereBegin() */ WhereLevel a[1]; /* Information about each nest loop in the WHERE */ }; /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** |
︙ | ︙ | |||
457 458 459 460 461 462 463 464 465 466 467 468 469 470 | ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ int op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ Select *pPrior; /* Prior select in a compound select statement */ int nLimit, nOffset; /* LIMIT and OFFSET values. -1 means not used */ char *zSelect; /* Complete text of the SELECT command */ }; /* ** The results of a select can be distributed in several ways. */ #define SRT_Callback 1 /* Invoke a callback with each row of result */ #define SRT_Mem 2 /* Store result in a memory cell */ | > | 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 | ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ int op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ Select *pPrior; /* Prior select in a compound select statement */ int nLimit, nOffset; /* LIMIT and OFFSET values. -1 means not used */ char *zSelect; /* Complete text of the SELECT command */ int base; /* Index of VDBE cursor for left-most FROM table */ }; /* ** The results of a select can be distributed in several ways. */ #define SRT_Callback 1 /* Invoke a callback with each row of result */ #define SRT_Mem 2 /* Store result in a memory cell */ |
︙ | ︙ | |||
517 518 519 520 521 522 523 | Vdbe *pVdbe; /* An engine for executing database bytecode */ int colNamesSet; /* TRUE after OP_ColumnCount has been issued to pVdbe */ int explain; /* True if the EXPLAIN flag is found on the query */ int initFlag; /* True if reparsing CREATE TABLEs */ int nameClash; /* A permanent table name clashes with temp table name */ int newTnum; /* Table number to use when reparsing CREATE TABLEs */ int nErr; /* Number of errors seen */ | | | 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 | Vdbe *pVdbe; /* An engine for executing database bytecode */ int colNamesSet; /* TRUE after OP_ColumnCount has been issued to pVdbe */ int explain; /* True if the EXPLAIN flag is found on the query */ int initFlag; /* True if reparsing CREATE TABLEs */ int nameClash; /* A permanent table name clashes with temp table name */ int newTnum; /* Table number to use when reparsing CREATE TABLEs */ int nErr; /* Number of errors seen */ int nTab; /* Number of previously allocated VDBE cursors */ int nMem; /* Number of memory cells used so far */ int nSet; /* Number of sets used so far */ int nAgg; /* Number of aggregate expressions */ AggExpr *aAgg; /* An array of aggregate expressions */ int useAgg; /* If true, extract field values from the aggregator ** while generating expressions. Normally false */ int schemaVerified; /* True if an OP_VerifySchema has been coded someplace |
︙ | ︙ | |||
582 583 584 585 586 587 588 | void sqliteDeleteTable(sqlite*, Table*); void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int); IdList *sqliteIdListAppend(IdList*, Token*); void sqliteIdListAddAlias(IdList*, Token*); void sqliteIdListDelete(IdList*); void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*); void sqliteDropIndex(Parse*, Token*); | | | | < | 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 | void sqliteDeleteTable(sqlite*, Table*); void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int); IdList *sqliteIdListAppend(IdList*, Token*); void sqliteIdListAddAlias(IdList*, Token*); void sqliteIdListDelete(IdList*); void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*); void sqliteDropIndex(Parse*, Token*); int sqliteSelect(Parse*, Select*, int, int, Select*, int, int); Select *sqliteSelectNew(ExprList*,IdList*,Expr*,ExprList*,Expr*,ExprList*, int,int,int); void sqliteSelectDelete(Select*); void sqliteSelectUnbind(Select*); Table *sqliteTableNameToTable(Parse*, const char*); IdList *sqliteTableTokenToIdList(Parse*, Token*); void sqliteDeleteFrom(Parse*, Token*, Expr*); void sqliteUpdate(Parse*, Token*, ExprList*, Expr*, int); WhereInfo *sqliteWhereBegin(Parse*, int, IdList*, Expr*, int); void sqliteWhereEnd(WhereInfo*); void sqliteExprCode(Parse*, Expr*); void sqliteExprIfTrue(Parse*, Expr*, int); void sqliteExprIfFalse(Parse*, Expr*, int); Table *sqliteFindTable(sqlite*,const char*); Index *sqliteFindIndex(sqlite*,const char*); void sqliteUnlinkAndDeleteIndex(sqlite*,Index*); void sqliteCopy(Parse*, Token*, Token*, Token*, int); void sqliteVacuum(Parse*, Token*); int sqliteGlobCompare(const unsigned char*,const unsigned char*); int sqliteLikeCompare(const unsigned char*,const unsigned char*); char *sqliteTableNameFromToken(Token*); int sqliteExprCheck(Parse*, Expr*, int, int*); int sqliteExprCompare(Expr*, Expr*); int sqliteFuncId(Token*); int sqliteExprResolveIds(Parse*, int, IdList*, ExprList*, Expr*); int sqliteExprAnalyzeAggregates(Parse*, Expr*); Vdbe *sqliteGetVdbe(Parse*); int sqliteRandomByte(void); int sqliteRandomInteger(void); void sqliteBeginTransaction(Parse*, int); void sqliteCommitTransaction(Parse*); void sqliteRollbackTransaction(Parse*); |
︙ | ︙ |
Changes to src/update.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 UPDATE statements. ** | | | 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 C code routines that are called by the parser ** to handle UPDATE statements. ** ** $Id: update.c,v 1.35 2002/03/02 17:04:09 drh Exp $ */ #include "sqliteInt.h" /* ** Process an UPDATE statement. */ void sqliteUpdate( |
︙ | ︙ | |||
62 63 64 65 66 67 68 | if( aXRef==0 ) goto update_cleanup; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; /* Resolve the column names in all the expressions in both the ** WHERE clause and in the new values. Also find the column index ** for each column to be updated in the pChanges array. */ | < | < < < < | | | 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | if( aXRef==0 ) goto update_cleanup; for(i=0; i<pTab->nCol; i++) aXRef[i] = -1; /* Resolve the column names in all the expressions in both the ** WHERE clause and in the new values. Also find the column index ** for each column to be updated in the pChanges array. */ base = pParse->nTab++; if( pWhere ){ if( sqliteExprResolveIds(pParse, base, pTabList, 0, pWhere) ){ goto update_cleanup; } if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ goto update_cleanup; } } chngRecno = 0; for(i=0; i<pChanges->nExpr; i++){ if( sqliteExprResolveIds(pParse, base, pTabList, 0, pChanges->a[i].pExpr) ){ goto update_cleanup; } if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){ goto update_cleanup; } for(j=0; j<pTab->nCol; j++){ if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){ |
︙ | ︙ | |||
146 147 148 149 150 151 152 | */ v = sqliteGetVdbe(pParse); if( v==0 ) goto update_cleanup; sqliteBeginMultiWriteOperation(pParse); /* Begin the database scan */ | | | 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | */ v = sqliteGetVdbe(pParse); if( v==0 ) goto update_cleanup; sqliteBeginMultiWriteOperation(pParse); /* Begin the database scan */ pWInfo = sqliteWhereBegin(pParse, base, pTabList, pWhere, 1); if( pWInfo==0 ) goto update_cleanup; /* Remember the index of every item to be updated. */ sqliteVdbeAddOp(v, OP_ListWrite, 0, 0); /* End the database scan loop. |
︙ | ︙ | |||
170 171 172 173 174 175 176 | /* Rewind the list of records that need to be updated and ** open every index that needs updating. Note that if any ** index could potentially invoke a REPLACE conflict resolution ** action, then we need to open all indices because we might need ** to be deleting some records. */ sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); | < > > | 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 | /* Rewind the list of records that need to be updated and ** open every index that needs updating. Note that if any ** index could potentially invoke a REPLACE conflict resolution ** action, then we need to open all indices because we might need ** to be deleting some records. */ sqliteVdbeAddOp(v, OP_ListRewind, 0, 0); openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite; sqliteVdbeAddOp(v, openOp, base, pTab->tnum); if( onError==OE_Replace ){ openAll = 1; }else{ openAll = 0; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->onError==OE_Replace ){ openAll = 1; break; } } } for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ if( openAll || aIdxUsed[i] ){ sqliteVdbeAddOp(v, openOp, base+i+1, pIdx->tnum); assert( pParse->nTab==base+i+1 ); } pParse->nTab++; } /* Loop over every record that needs updating. We have to load ** the old data for each record to be updated because some columns ** might not change and we will need to copy the old value. ** Also, the old data is needed to delete the old index entires. ** So make the cursor point at the old record. |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** ** $Id: where.c,v 1.38 2002/03/02 17:04:09 drh Exp $ */ #include "sqliteInt.h" /* ** The query generator uses an array of instances of this structure to ** help it analyze the subexpressions of the WHERE clause. Each WHERE ** clause subexpression is separated from the others by an AND operator. |
︙ | ︙ | |||
72 73 74 75 76 77 78 | ** if table 1 is used. And so forth. ** ** In order for this routine to work, the calling function must have ** previously invoked sqliteExprResolveIds() on the expression. See ** the header comment on that routine for additional information. ** ** "base" is the cursor number (the value of the iTable field) that | | < | 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | ** if table 1 is used. And so forth. ** ** In order for this routine to work, the calling function must have ** previously invoked sqliteExprResolveIds() on the expression. See ** the header comment on that routine for additional information. ** ** "base" is the cursor number (the value of the iTable field) that ** corresponds to the first entry in the table list. */ static int exprTableUsage(int base, Expr *p){ unsigned int mask = 0; if( p==0 ) return 0; if( p->op==TK_COLUMN ){ return 1<< (p->iTable - base); } |
︙ | ︙ | |||
115 116 117 118 119 120 121 | /* ** The input to this routine is an ExprInfo structure with only the ** "p" field filled in. The job of this routine is to analyze the ** subexpression and populate all the other fields of the ExprInfo ** structure. ** ** "base" is the cursor number (the value of the iTable field) that | | < | 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 | /* ** The input to this routine is an ExprInfo structure with only the ** "p" field filled in. The job of this routine is to analyze the ** subexpression and populate all the other fields of the ExprInfo ** structure. ** ** "base" is the cursor number (the value of the iTable field) that ** corresponds to the first entry in the table list. */ static void exprAnalyze(int base, ExprInfo *pInfo){ Expr *pExpr = pInfo->p; pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft); pInfo->prereqRight = exprTableUsage(base, pExpr->pRight); pInfo->indexable = 0; pInfo->idxLeft = -1; |
︙ | ︙ | |||
148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 | ** should invoke sqliteWhereEnd() with the return value of this function ** in order to complete the WHERE clause processing. ** ** If an error occurs, this routine returns NULL. */ WhereInfo *sqliteWhereBegin( Parse *pParse, /* The parser context */ IdList *pTabList, /* A list of all tables */ Expr *pWhere, /* The WHERE clause */ int pushKey /* If TRUE, leave the table key on the stack */ ){ int i; /* Loop counter */ WhereInfo *pWInfo; /* Will become the return value of this function */ Vdbe *v = pParse->pVdbe; /* The virtual database engine */ int brk, cont; /* Addresses used during code generation */ int *aOrder; /* Order in which pTabList entries are searched */ int nExpr; /* Number of subexpressions in the WHERE clause */ int loopMask; /* One bit set for each outer loop */ int haveKey; /* True if KEY is on the stack */ | > < < | < | | 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 | ** should invoke sqliteWhereEnd() with the return value of this function ** in order to complete the WHERE clause processing. ** ** If an error occurs, this routine returns NULL. */ WhereInfo *sqliteWhereBegin( Parse *pParse, /* The parser context */ int base, /* VDBE cursor index for left-most table in pTabList */ IdList *pTabList, /* A list of all tables */ Expr *pWhere, /* The WHERE clause */ int pushKey /* If TRUE, leave the table key on the stack */ ){ int i; /* Loop counter */ WhereInfo *pWInfo; /* Will become the return value of this function */ Vdbe *v = pParse->pVdbe; /* The virtual database engine */ int brk, cont; /* Addresses used during code generation */ int *aOrder; /* Order in which pTabList entries are searched */ int nExpr; /* Number of subexpressions in the WHERE clause */ int loopMask; /* One bit set for each outer loop */ int haveKey; /* True if KEY is on the stack */ int aDirect[32]; /* If TRUE, then index this table using ROWID */ int iDirectEq[32]; /* Term of the form ROWID==X for the N-th table */ int iDirectLt[32]; /* Term of the form ROWID<X or ROWID<=X */ int iDirectGt[32]; /* Term of the form ROWID>X or ROWID>=X */ ExprInfo aExpr[50]; /* The WHERE clause is divided into these expressions */ /* Allocate space for aOrder[] and aiMem[]. */ aOrder = sqliteMalloc( sizeof(int) * pTabList->nId ); /* Allocate and initialize the WhereInfo structure that will become the ** return value. */ pWInfo = sqliteMalloc( sizeof(WhereInfo) + pTabList->nId*sizeof(WhereLevel) ); if( sqlite_malloc_failed ){ sqliteFree(aOrder); sqliteFree(pWInfo); return 0; } pWInfo->pParse = pParse; pWInfo->pTabList = pTabList; pWInfo->base = base; pWInfo->peakNTab = pWInfo->savedNTab = pParse->nTab; /* Split the WHERE clause into as many as 32 separate subexpressions ** where each subexpression is separated by an AND operator. Any additional ** subexpressions are attached in the aExpr[32] and will not enter ** into the query optimizer computations. 32 is chosen as the cutoff ** since that is the number of bits in an integer that we use for an ** expression-used mask. |
︙ | ︙ | |||
384 385 386 387 388 389 390 | bestScore = score; } } pWInfo->a[i].pIdx = pBestIdx; pWInfo->a[i].score = bestScore; loopMask |= 1<<idx; if( pBestIdx ){ | | > | 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 | bestScore = score; } } pWInfo->a[i].pIdx = pBestIdx; pWInfo->a[i].score = bestScore; loopMask |= 1<<idx; if( pBestIdx ){ pWInfo->a[i].iCur = pParse->nTab++; pWInfo->peakNTab = pParse->nTab; } } /* Open all tables in the pTabList and all indices used by those tables. */ for(i=0; i<pTabList->nId; i++){ int openOp; |
︙ | ︙ | |||
790 791 792 793 794 795 796 | if( pTabList->a[i].pTab->isTransient ) continue; pLevel = &pWInfo->a[i]; sqliteVdbeAddOp(v, OP_Close, base+i, 0); if( pLevel->pIdx!=0 ){ sqliteVdbeAddOp(v, OP_Close, pLevel->iCur, 0); } } | > | > | 787 788 789 790 791 792 793 794 795 796 797 798 799 | if( pTabList->a[i].pTab->isTransient ) continue; pLevel = &pWInfo->a[i]; sqliteVdbeAddOp(v, OP_Close, base+i, 0); if( pLevel->pIdx!=0 ){ sqliteVdbeAddOp(v, OP_Close, pLevel->iCur, 0); } } if( pWInfo->pParse->nTab==pWInfo->peakNTab ){ pWInfo->pParse->nTab = pWInfo->savedNTab; } sqliteFree(pWInfo); return; } |
Changes to test/func.test.
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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing built-in functions. # | | > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 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 | # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing built-in functions. # # $Id: func.test,v 1.9 2002/03/02 17:04:09 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. # do_test func-0.0 { execsql {CREATE TABLE tbl1(t1 text)} foreach word {this program is free software} { execsql "INSERT INTO tbl1 VALUES('$word')" } execsql {SELECT t1 FROM tbl1 ORDER BY t1} } {free is program software this} do_test func-0.1 { execsql { CREATE TABLE t2(a); INSERT INTO t2 VALUES(1); INSERT INTO t2 VALUES(NULL); INSERT INTO t2 VALUES(345); INSERT INTO t2 VALUES(NULL); INSERT INTO t2 VALUES(67890); SELECT * FROM t2; } } {1 {} 345 {} 67890} # Check out the length() function # do_test func-1.0 { execsql {SELECT length(t1) FROM tbl1 ORDER BY t1} } {4 2 7 8 4} do_test func-1.1 { set r [catch {execsql {SELECT length(*) FROM tbl1 ORDER BY t1}} msg] lappend r $msg } {1 {wrong number of arguments to function length()}} do_test func-1.2 { set r [catch {execsql {SELECT length(t1,5) FROM tbl1 ORDER BY t1}} msg] lappend r $msg } {1 {wrong number of arguments to function length()}} do_test func-1.3 { execsql {SELECT length(t1), count(*) FROM tbl1 GROUP BY length(t1) ORDER BY length(t1)} } {2 1 4 2 7 1 8 1} do_test func-1.4 { execsql {SELECT length(a) FROM t2} } {1 0 3 0 5} # Check out the substr() function # do_test func-2.0 { execsql {SELECT substr(t1,1,2) FROM tbl1 ORDER BY t1} } {fr is pr so th} do_test func-2.1 { |
︙ | ︙ | |||
69 70 71 72 73 74 75 76 77 78 79 80 81 82 | } {ee is am re is} do_test func-2.7 { execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1} } {fr {} gr wa th} do_test func-2.8 { execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)} } {this software free program is} # Only do the following tests if TCL has UTF-8 capabilities and # the UTF-8 encoding is turned on in the SQLite library. # if {[sqlite -encoding]=="UTF-8" && "\u1234"!="u1234"} { # Put some UTF-8 characters in the database | > > > > > > | 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | } {ee is am re is} do_test func-2.7 { execsql {SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1} } {fr {} gr wa th} do_test func-2.8 { execsql {SELECT t1 FROM tbl1 ORDER BY substr(t1,2,20)} } {this software free program is} do_test func-2.9 { execsql {SELECT substr(a,1,1) FROM t2} } {1 {} 3 {} 6} do_test func-2.10 { execsql {SELECT substr(a,2,2) FROM t2} } {{} {} 45 {} 78} # Only do the following tests if TCL has UTF-8 capabilities and # the UTF-8 encoding is turned on in the SQLite library. # if {[sqlite -encoding]=="UTF-8" && "\u1234"!="u1234"} { # Put some UTF-8 characters in the database |
︙ | ︙ | |||
114 115 116 117 118 119 120 121 122 123 124 125 126 127 | } "s s o 8" do_test func-3.9 { execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1} } "er in \u1234h F-" do_test func-3.10 { execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1} } "ter ain i\u1234h TF-" } ;# End [sqlite -encoding]==UTF-8 and \u1234!=u1234 # Test the abs() and round() functions. # do_test func-4.1 { execsql { | > > > > > > > | 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 | } "s s o 8" do_test func-3.9 { execsql {SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1} } "er in \u1234h F-" do_test func-3.10 { execsql {SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1} } "ter ain i\u1234h TF-" do_test func-3.99 { execsql {DELETE FROM tbl1} foreach word {this program is free software} { execsql "INSERT INTO tbl1 VALUES('$word')" } execsql {SELECT t1 FROM tbl1} } {this program is free software} } ;# End [sqlite -encoding]==UTF-8 and \u1234!=u1234 # Test the abs() and round() functions. # do_test func-4.1 { execsql { |
︙ | ︙ | |||
137 138 139 140 141 142 143 144 145 146 147 148 149 150 | } {1 {wrong number of arguments to function abs()}} do_test func-4.3 { catchsql {SELECT abs(b) FROM t1 ORDER BY a} } {0 {2 1.2345678901234 2}} do_test func-4.4 { catchsql {SELECT abs(c) FROM t1 ORDER BY a} } {0 {3 12345.67890 5}} do_test func-4.5 { catchsql {SELECT round(a,b,c) FROM t1} } {1 {wrong number of arguments to function round()}} do_test func-4.6 { catchsql {SELECT round(b,2) FROM t1 ORDER BY b} } {0 {-2.00 1.23 2.00}} | > > > > > > | 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 | } {1 {wrong number of arguments to function abs()}} do_test func-4.3 { catchsql {SELECT abs(b) FROM t1 ORDER BY a} } {0 {2 1.2345678901234 2}} do_test func-4.4 { catchsql {SELECT abs(c) FROM t1 ORDER BY a} } {0 {3 12345.67890 5}} do_test func-4.4.1 { execsql {SELECT abs(a) FROM t2} } {1 {} 345 {} 67890} do_test func-4.4.2 { execsql {SELECT abs(t1) FROM tbl1} } {this program is free software} do_test func-4.5 { catchsql {SELECT round(a,b,c) FROM t1} } {1 {wrong number of arguments to function round()}} do_test func-4.6 { catchsql {SELECT round(b,2) FROM t1 ORDER BY b} } {0 {-2.00 1.23 2.00}} |
︙ | ︙ | |||
159 160 161 162 163 164 165 166 167 | } {0 {3.0 -12345.68 -5.000}} do_test func-4.10 { catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a} } {0 {x3.0y x-12345.68y x-5.000y}} do_test func-4.11 { catchsql {SELECT round() FROM t1 ORDER BY a} } {1 {wrong number of arguments to function round()}} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 | } {0 {3.0 -12345.68 -5.000}} do_test func-4.10 { catchsql {SELECT 'x' || round(c,a) || 'y' FROM t1 ORDER BY a} } {0 {x3.0y x-12345.68y x-5.000y}} do_test func-4.11 { catchsql {SELECT round() FROM t1 ORDER BY a} } {1 {wrong number of arguments to function round()}} do_test func-4.12 { execsql {SELECT round(a,2) FROM t2} } {1.00 0.00 345.00 0.00 67890.00} do_test func-4.13 { execsql {SELECT round(t1,2) FROM tbl1} } {0.00 0.00 0.00 0.00 0.00} # Test the upper() and lower() functions # do_test func-5.1 { execsql {SELECT upper(t1) FROM tbl1} } {THIS PROGRAM IS FREE SOFTWARE} do_test func-5.2 { execsql {SELECT lower(upper(t1)) FROM tbl1} } {this program is free software} do_test func-5.3 { execsql {SELECT upper(a), lower(a) FROM t2} } {1 1 {} {} 345 345 {} {} 67890 67890} do_test func-5.4 { catchsql {SELECT upper(a,5) FROM t2} } {1 {wrong number of arguments to function upper()}} do_test func-5.5 { catchsql {SELECT upper(*) FROM t2} } {1 {wrong number of arguments to function upper()}} # Test the coalesce() function # do_test func-6.1 { execsql {SELECT coalesce(a,'xyz') FROM t2} } {1 xyz 345 xyz 67890} do_test func-6.2 { execsql {SELECT coalesce(upper(a),'nil') FROM t2} } {1 nil 345 nil 67890} finish_test |
Changes to test/select1.test.
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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # | | | 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # # $Id: select1.test,v 1.22 2002/03/02 17:04:09 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to select on a non-existant table. # do_test select1-1.1 { |
︙ | ︙ | |||
93 94 95 96 97 98 99 | FROM test2, test1} } {11 2.2} do_test select1-1.13 { execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2) FROM test1, test2} } {1.1 22} | > > > | | | | > > > > > > > > > | 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | FROM test2, test1} } {11 2.2} do_test select1-1.13 { execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2) FROM test1, test2} } {1.1 22} set long {This is a string that is too big to fit inside a NBFS buffer} do_test select1-2.0 { execsql " DROP TABLE test2; DELETE FROM test1; INSERT INTO test1 VALUES(11,22); INSERT INTO test1 VALUES(33,44); CREATE TABLE t3(a,b); INSERT INTO t3 VALUES('abc',NULL); INSERT INTO t3 VALUES(NULL,'xyz'); INSERT INTO t3 SELECT * FROM test1; CREATE TABLE t4(a,b); INSERT INTO t4 VALUES(NULL,'$long'); SELECT * FROM t3; " } {abc {} {} xyz 11 22 33 44} # Error messges from sqliteExprCheck # do_test select1-2.1 { set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function count()}} |
︙ | ︙ | |||
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 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg] lappend v $msg } {0 2} do_test select1-2.5 { set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg] lappend v $msg } {0 3} do_test select1-2.6 { set v [catch {execsql {SELECT min(*) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function min()}} do_test select1-2.7 { set v [catch {execsql {SELECT Min(f1) FROM test1}} msg] lappend v $msg } {0 11} do_test select1-2.8 { set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg] lappend v [lsort $msg] } {0 {11 33}} do_test select1-2.9 { set v [catch {execsql {SELECT MAX(*) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function MAX()}} do_test select1-2.10 { set v [catch {execsql {SELECT Max(f1) FROM test1}} msg] lappend v $msg } {0 33} do_test select1-2.11 { set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg] lappend v [lsort $msg] } {0 {22 44}} do_test select1-2.12 { set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg] lappend v [lsort $msg] } {0 {23 45}} do_test select1-2.13 { set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] lappend v $msg } {0 34} do_test select1-2.14 { set v [catch {execsql {SELECT SUM(*) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function SUM()}} do_test select1-2.15 { set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg] lappend v $msg } {0 44} do_test select1-2.16 { set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function sum()}} do_test select1-2.17 { set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] lappend v $msg } {0 45} do_test select1-2.18 { set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg] lappend v $msg } {1 {no such function: XYZZY}} do_test select1-2.19 { set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg] lappend v $msg | > > > > > > > > > > > > > > > > > > > > > > > > > > > | 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 | set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg] lappend v $msg } {0 2} do_test select1-2.5 { set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg] lappend v $msg } {0 3} do_test select1-2.5.1 { execsql {SELECT count(*),count(a),count(b) FROM t3} } {4 3 3} do_test select1-2.5.2 { execsql {SELECT count(*),count(a),count(b) FROM t4} } {1 0 1} do_test select1-2.5.3 { execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5} } {0 0 0} do_test select1-2.6 { set v [catch {execsql {SELECT min(*) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function min()}} do_test select1-2.7 { set v [catch {execsql {SELECT Min(f1) FROM test1}} msg] lappend v $msg } {0 11} do_test select1-2.8 { set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg] lappend v [lsort $msg] } {0 {11 33}} do_test select1-2.8.1 { execsql {SELECT coalesce(min(a),'xyzzy') FROM t3} } {xyzzy} do_test select1-2.8.2 { execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3} } {11} do_test select1-2.8.3 { execsql {SELECT min(b), min(b) FROM t4} } [list $long $long] do_test select1-2.9 { set v [catch {execsql {SELECT MAX(*) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function MAX()}} do_test select1-2.10 { set v [catch {execsql {SELECT Max(f1) FROM test1}} msg] lappend v $msg } {0 33} do_test select1-2.11 { set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg] lappend v [lsort $msg] } {0 {22 44}} do_test select1-2.12 { set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg] lappend v [lsort $msg] } {0 {23 45}} do_test select1-2.13 { set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg] lappend v $msg } {0 34} do_test select1-2.13.1 { execsql {SELECT coalesce(max(a),'xyzzy') FROM t3} } {abc} do_test select1-2.13.1 { execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3} } {xyzzy} do_test select1-2.14 { set v [catch {execsql {SELECT SUM(*) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function SUM()}} do_test select1-2.15 { set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg] lappend v $msg } {0 44} do_test select1-2.16 { set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg] lappend v $msg } {1 {wrong number of arguments to function sum()}} do_test select1-2.17 { set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg] lappend v $msg } {0 45} do_test select1-2.17.1 { execsql {SELECT sum(a) FROM t3} } {44} do_test select1-2.18 { set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg] lappend v $msg } {1 {no such function: XYZZY}} do_test select1-2.19 { set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg] lappend v $msg |
︙ | ︙ |
Changes to www/changes.tcl.
︙ | ︙ | |||
24 25 26 27 28 29 30 31 32 33 34 35 36 37 | clause is handled as a special case which avoids a complete table scan.</li> <li>Automatically generated ROWIDs are now sequential.</li> <li>Do not allow dot-commands of the command-line shell to occur in the middle of a real SQL command.</li> <li>Modifications to the "lemon" parser generator so that the parser tables are 4 times smaller.</li> <li>Added support for user-defined functions implemented in C.</li> } chng {2002 Feb 18 (2.3.3)} { <li>Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.</li> <li>Added support for sub-queries in the FROM clause of a SELECT.</li> <li>More efficient implementation of sqliteFileExists() under Windows. | > > | 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | clause is handled as a special case which avoids a complete table scan.</li> <li>Automatically generated ROWIDs are now sequential.</li> <li>Do not allow dot-commands of the command-line shell to occur in the middle of a real SQL command.</li> <li>Modifications to the "lemon" parser generator so that the parser tables are 4 times smaller.</li> <li>Added support for user-defined functions implemented in C.</li> <li>Added support for VIEWs.</li> <li>Added the subquery flattening optimizer.</li> } chng {2002 Feb 18 (2.3.3)} { <li>Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.</li> <li>Added support for sub-queries in the FROM clause of a SELECT.</li> <li>More efficient implementation of sqliteFileExists() under Windows. |
︙ | ︙ |