Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Honor ORDER BY clauses in VIEWs. Ticket #193. (CVS 792) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
dbf7893234a6c5d6bb2d931e52080bb0 |
User & Date: | drh 2002-12-03 02:22:52.000 |
Context
2002-12-03
| ||
02:34 | Allow an aggregate function in the HAVING clause even if no aggregates appear in the result set. Ticket #187. (CVS 793) (check-in: 33c6fd6b3d user: drh tags: trunk) | |
02:22 | Honor ORDER BY clauses in VIEWs. Ticket #193. (CVS 792) (check-in: dbf7893234 user: drh tags: trunk) | |
2002-12-02
| ||
04:25 | Change to the pager to avoid opening journal files unnecessarily. This can sometimes results in a significant speed improvement. (CVS 791) (check-in: fa5c042585 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
21 22 23 24 25 26 27 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** ** $Id: build.c,v 1.117 2002/12/03 02:22:52 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called when a new SQL statement is beginning to ** be parsed. Check to see if the schema for the database needs |
︙ | ︙ | |||
894 895 896 897 898 899 900 | sqliteStartTable(pParse, pBegin, pName, isTemp); p = pParse->pNewTable; if( p==0 ){ sqliteSelectDelete(pSelect); return; } | < < < < | | 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 | sqliteStartTable(pParse, pBegin, pName, isTemp); p = pParse->pNewTable; if( p==0 ){ sqliteSelectDelete(pSelect); return; } /* Make a copy of the entire SELECT statement that defines the view. ** This will force all the Expr.token.z values to be dynamically ** allocated rather than point to the input string - which means that ** they will persist after the current sqlite_exec() call returns. */ p->pSelect = sqliteSelectDup(pSelect); sqliteSelectDelete(pSelect); |
︙ | ︙ |
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.116 2002/12/03 02:22:52 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 | ** ** (9) The subquery does not use LIMIT or the outer query does not use ** aggregates. ** ** (10) The subquery does not use aggregates or the outer query does not ** use LIMIT. ** ** 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. ** | > > | 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 | ** ** (9) The subquery does not use LIMIT or the outer query does not use ** aggregates. ** ** (10) The subquery does not use aggregates or the outer query does not ** use LIMIT. ** ** (11) The subquery and the outer query do not both have ORDER BY clauses. ** ** 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. ** |
︙ | ︙ | |||
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 | pSubSrc = pSub->pSrc; assert( pSubSrc ); if( pSubSrc->nSrc!=1 ) return 0; if( (pSub->isDistinct || pSub->nLimit>=0) && (pSrc->nSrc>1 || isAgg) ){ return 0; } if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0; /* If we reach this point, it means flattening 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++){ Expr *pExpr; if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){ pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n); } } if( isAgg ){ substExprList(p->pGroupBy, iParent, pSub->pEList, iSub); substExpr(p->pHaving, iParent, pSub->pEList, iSub); } | > > > > > > > | > | 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 1541 1542 1543 1544 1545 1546 1547 1548 1549 | pSubSrc = pSub->pSrc; assert( pSubSrc ); if( pSubSrc->nSrc!=1 ) return 0; if( (pSub->isDistinct || pSub->nLimit>=0) && (pSrc->nSrc>1 || isAgg) ){ return 0; } if( (p->isDistinct || p->nLimit>=0) && subqueryIsAgg ) return 0; if( p->pOrderBy && pSub->pOrderBy ) return 0; /* If we reach this point, it means flattening 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++){ Expr *pExpr; if( pList->a[i].zName==0 && (pExpr = pList->a[i].pExpr)->span.z!=0 ){ pList->a[i].zName = sqliteStrNDup(pExpr->span.z, pExpr->span.n); } } if( isAgg ){ substExprList(p->pGroupBy, iParent, pSub->pEList, iSub); substExpr(p->pHaving, iParent, pSub->pEList, iSub); } if( pSub->pOrderBy ){ assert( p->pOrderBy==0 ); p->pOrderBy = pSub->pOrderBy; pSub->pOrderBy = 0; changeTablesInList(p->pOrderBy, iSub, iParent); }else if( p->pOrderBy ){ substExprList(p->pOrderBy, iParent, pSub->pEList, iSub); } if( pSub->pWhere ){ pWhere = sqliteExprDup(pSub->pWhere); if( iParent!=iSub ){ changeTables(pWhere, iSub, iParent); } }else{ pWhere = 0; |
︙ | ︙ |
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.67 2002/12/03 02:22:52 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. |
︙ | ︙ | |||
422 423 424 425 426 427 428 429 430 431 432 433 434 435 | int bestScore = 0; /* Check to see if there is an expression that uses only the ** ROWID field of this table. For terms of the form ROWID==expr ** set iDirectEq[i] to the index of the term. For terms of the ** form ROWID<expr or ROWID<=expr set iDirectLt[i] to the term index. ** For terms like ROWID>expr or ROWID>=expr set iDirectGt[i]. */ pWInfo->a[i].iCur = -1; iDirectEq[i] = -1; iDirectLt[i] = -1; iDirectGt[i] = -1; for(j=0; j<nExpr; j++){ if( aExpr[j].idxLeft==idx && aExpr[j].p->pLeft->iColumn<0 | > > | 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 | int bestScore = 0; /* Check to see if there is an expression that uses only the ** ROWID field of this table. For terms of the form ROWID==expr ** set iDirectEq[i] to the index of the term. For terms of the ** form ROWID<expr or ROWID<=expr set iDirectLt[i] to the term index. ** For terms like ROWID>expr or ROWID>=expr set iDirectGt[i]. ** ** (Added:) Treat ROWID IN expr like ROWID=expr. */ pWInfo->a[i].iCur = -1; iDirectEq[i] = -1; iDirectLt[i] = -1; iDirectGt[i] = -1; for(j=0; j<nExpr; j++){ if( aExpr[j].idxLeft==idx && aExpr[j].p->pLeft->iColumn<0 |
︙ | ︙ | |||
647 648 649 650 651 652 653 | for(i=0; i<pTabList->nSrc; i++){ int j, k; int idx = aOrder[i]; Index *pIdx; WhereLevel *pLevel = &pWInfo->a[i]; /* If this is the right table of a LEFT OUTER JOIN, allocate and | | | 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 | for(i=0; i<pTabList->nSrc; i++){ int j, k; int idx = aOrder[i]; Index *pIdx; WhereLevel *pLevel = &pWInfo->a[i]; /* If this is the right table of a LEFT OUTER JOIN, allocate and ** initialize a memory cell that records if this table matches any ** row of the left table of the join. */ if( i>0 && (pTabList->a[i-1].jointype & JT_LEFT)!=0 ){ if( !pParse->nMem ) pParse->nMem++; pLevel->iLeftJoin = pParse->nMem++; sqliteVdbeAddOp(v, OP_String, 0, 0); sqliteVdbeAddOp(v, OP_MemStore, pLevel->iLeftJoin, 1); |
︙ | ︙ |
Changes to test/view.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2002 February 26 # # 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 VIEW statements. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2002 February 26 # # 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 VIEW statements. # # $Id: view.test,v 1.12 2002/12/03 02:22:53 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test view-1.0 { execsql { CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(1,2,3); |
︙ | ︙ | |||
292 293 294 295 296 297 298 299 300 | } } 3 do_test view-8.5 { execsql { SELECT mx+10, mx*2 FROM v8; } } {13 6} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 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 340 341 | } } 3 do_test view-8.5 { execsql { SELECT mx+10, mx*2 FROM v8; } } {13 6} # Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW. # do_test view-9.1 { execsql { INSERT INTO t2 SELECT * FROM t2 WHERE a<5; INSERT INTO t2 SELECT * FROM t2 WHERE a<4; INSERT INTO t2 SELECT * FROM t2 WHERE a<3; SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1; } } {1 2 4 8} do_test view-9.2 { execsql { SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; } } {1 2 4} do_test view-9.3 { execsql { CREATE VIEW v9 AS SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; SELECT * FROM v9; } } {1 2 4} do_test view-9.4 { execsql { SELECT * FROM v9 ORDER BY 1 DESC; } } {4 2 1} do_test view-9.5 { execsql { CREATE VIEW v10 AS SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3; SELECT * FROM v10; } } {5 1 4 2 3 4} do_test view-9.6 { execsql { SELECT * FROM v10 ORDER BY 1; } } {3 4 4 2 5 1} finish_test |