Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Do not optimize views with an ORDER BY clause if they are used in a UNION ALL that also has an ORDER BY clause. Ticket #1444. (CVS 2723) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6cc57fcf15cfa3ce73c78b1cac90f780 |
User & Date: | drh 2005-09-19 17:35:53.000 |
Context
2005-09-19
| ||
19:05 | Set the default maximum page size to 32768, not 8192. (CVS 2724) (check-in: b32e9ec248 user: drh tags: trunk) | |
17:35 | Do not optimize views with an ORDER BY clause if they are used in a UNION ALL that also has an ORDER BY clause. Ticket #1444. (CVS 2723) (check-in: 6cc57fcf15 user: drh tags: trunk) | |
15:37 | Fix uninitialized value on a UNION ALL select with an ORDER BY clause. (CVS 2722) (check-in: 92126a2161 user: drh tags: trunk) | |
Changes
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.271 2005/09/19 17:35:53 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. |
︙ | ︙ | |||
1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 | 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; p->pOrderBy = 0; pLimit = p->pLimit; p->pLimit = 0; pOffset = p->pOffset; p->pOffset = 0; rc = sqlite3Select(pParse, p, op, unionTab, 0, 0, 0, aff); p->pPrior = pPrior; p->pOrderBy = pOrderBy; | > | 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 | 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; p->pOrderBy = 0; p->disallowOrderBy = pOrderBy!=0; pLimit = p->pLimit; p->pLimit = 0; pOffset = p->pOffset; p->pOffset = 0; rc = sqlite3Select(pParse, p, op, unionTab, 0, 0, 0, aff); p->pPrior = pPrior; p->pOrderBy = pOrderBy; |
︙ | ︙ | |||
1967 1968 1969 1970 1971 1972 1973 | if( (pSub->pLimit && p->pLimit) || pSub->pOffset || (pSub->pLimit && isAgg) ) return 0; if( pSubSrc->nSrc==0 ) return 0; if( pSub->isDistinct && (pSrc->nSrc>1 || isAgg) ){ return 0; } if( p->isDistinct && subqueryIsAgg ) return 0; | | | 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 | if( (pSub->pLimit && p->pLimit) || pSub->pOffset || (pSub->pLimit && isAgg) ) return 0; if( pSubSrc->nSrc==0 ) return 0; if( pSub->isDistinct && (pSrc->nSrc>1 || isAgg) ){ return 0; } if( p->isDistinct && subqueryIsAgg ) return 0; if( (p->disallowOrderBy || p->pOrderBy) && pSub->pOrderBy ) return 0; /* Restriction 3: If the subquery is a join, make sure the subquery is ** not used as the right operand of an outer join. Examples of why this ** is not allowed: ** ** t1 LEFT OUTER JOIN (t2 JOIN t3) ** |
︙ | ︙ |
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.420 2005/09/19 17:35:53 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* ** Many people are failing to set -DNDEBUG=1 when compiling SQLite. ** Setting NDEBUG makes the code smaller and run faster. So the following |
︙ | ︙ | |||
1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 | struct Select { ExprList *pEList; /* The fields of the result */ u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ u8 isDistinct; /* True if the DISTINCT keyword is present */ u8 isResolved; /* True once sqlite3SelectResolve() has run. */ u8 isAgg; /* True if this is an aggregate query */ u8 usesVirt; /* True if uses an OpenVirtual opcode */ SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ Select *pRightmost; /* Right-most select in a compound select statement */ | > | 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 | struct Select { ExprList *pEList; /* The fields of the result */ u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ u8 isDistinct; /* True if the DISTINCT keyword is present */ u8 isResolved; /* True once sqlite3SelectResolve() has run. */ u8 isAgg; /* True if this is an aggregate query */ u8 usesVirt; /* True if uses an OpenVirtual opcode */ u8 disallowOrderBy; /* Do not allow an ORDER BY to be attached if TRUE */ SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ Select *pRightmost; /* Right-most select in a compound select statement */ |
︙ | ︙ |
Added test/tkt1444.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | # 2005 September 19 # # 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. # # This file implements tests to verify that ticket #1444 has been # fixed. # set testdir [file dirname $argv0] source $testdir/tester.tcl # The use of a VIEW that contained an ORDER BY clause within a UNION ALL # was causing problems. See ticket #1444. # do_test tkt1444-1.1 { execsql { CREATE TABLE DemoTable (x INTEGER, TextKey TEXT, DKey Real); CREATE INDEX DemoTableIdx ON DemoTable (TextKey); INSERT INTO DemoTable VALUES(9,8,7); INSERT INTO DemoTable VALUES(1,2,3); CREATE VIEW DemoView AS SELECT * FROM DemoTable ORDER BY TextKey; SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1; } } {1 2 3 1 2 3 9 8 7 9 8 7} do_test tkt1444-1.2 { execsql { SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView; } } {9 8 7 1 2 3 1 2 3 9 8 7} do_test tkt1444-1.3 { execsql { DROP VIEW DemoView; CREATE VIEW DemoView AS SELECT * FROM DemoTable; SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1; } } {1 2 3 1 2 3 9 8 7 9 8 7} do_test tkt1444-1.4 { execsql { SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView; } } {9 8 7 1 2 3 9 8 7 1 2 3} finish_test |