Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the processing of ORDER BY clauses on compound queries to better match terms of the order by against expressions in the result set, in order to enable better query optimization. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
a49e909c8738317c8383ce93771c0a9c |
| User & Date: | drh 2012-04-27 01:09:06.388 |
References
|
2014-09-20
| ||
| 17:05 | • New ticket [d11a6e908f] Query planner fault on three-way nested join. (artifact: ce50af6bfe user: drh) | |
Context
|
2012-04-27
| ||
| 16:38 | Fix a minor deviation from the coding style guidelines. (check-in: 1e51bffe77 user: drh tags: trunk) | |
| 01:09 | Enhance the processing of ORDER BY clauses on compound queries to better match terms of the order by against expressions in the result set, in order to enable better query optimization. (check-in: a49e909c87 user: drh tags: trunk) | |
| 01:08 | Enhance the do_test proc in the test suite so that if the expected result is of the form "/.../" or "~/.../" then regular expression matching is done between result and the "..." part of the expectation. In the ~/.../ case, we expect there to be no match. (check-in: c9a734406c user: drh tags: trunk) | |
Changes
Changes to src/resolve.c.
| ︙ | ︙ | |||
879 880 881 882 883 884 885 |
*/
static int resolveOrderGroupBy(
NameContext *pNC, /* The name context of the SELECT statement */
Select *pSelect, /* The SELECT statement holding pOrderBy */
ExprList *pOrderBy, /* An ORDER BY or GROUP BY clause to resolve */
const char *zType /* Either "ORDER" or "GROUP", as appropriate */
){
| | | 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 |
*/
static int resolveOrderGroupBy(
NameContext *pNC, /* The name context of the SELECT statement */
Select *pSelect, /* The SELECT statement holding pOrderBy */
ExprList *pOrderBy, /* An ORDER BY or GROUP BY clause to resolve */
const char *zType /* Either "ORDER" or "GROUP", as appropriate */
){
int i, j; /* Loop counters */
int iCol; /* Column number */
struct ExprList_item *pItem; /* A term of the ORDER BY clause */
Parse *pParse; /* Parsing context */
int nResult; /* Number of terms in the result set */
if( pOrderBy==0 ) return 0;
nResult = pSelect->pEList->nExpr;
|
| ︙ | ︙ | |||
915 916 917 918 919 920 921 922 923 924 925 926 927 928 |
continue;
}
/* Otherwise, treat the ORDER BY term as an ordinary expression */
pItem->iOrderByCol = 0;
if( sqlite3ResolveExprNames(pNC, pE) ){
return 1;
}
}
return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
}
/*
** Resolve names in the SELECT statement p and all of its descendents.
| > > > > > | 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 |
continue;
}
/* Otherwise, treat the ORDER BY term as an ordinary expression */
pItem->iOrderByCol = 0;
if( sqlite3ResolveExprNames(pNC, pE) ){
return 1;
}
for(j=0; j<pSelect->pEList->nExpr; j++){
if( sqlite3ExprCompare(pE, pSelect->pEList->a[j].pExpr)==0 ){
pItem->iOrderByCol = j+1;
}
}
}
return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
}
/*
** Resolve names in the SELECT statement p and all of its descendents.
|
| ︙ | ︙ |
Changes to test/select9.test.
| ︙ | ︙ | |||
410 411 412 413 414 415 416 417 418 419 |
do_test select9-4.X {
execsql {
DROP INDEX i1;
DROP INDEX i2;
DROP VIEW v1;
}
} {}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 |
do_test select9-4.X {
execsql {
DROP INDEX i1;
DROP INDEX i2;
DROP VIEW v1;
}
} {}
# Testing to make sure that queries involving a view of a compound select
# are planned efficiently. This detects a problem reported on the mailing
# list on 2012-04-26. See
#
# http://www.mail-archive.com/sqlite-users%40sqlite.org/msg69746.html
#
# For additional information.
#
do_test select9-5.1 {
db eval {
CREATE TABLE t51(x, y);
CREATE TABLE t52(x, y);
CREATE VIEW v5 as
SELECT x, y FROM t51
UNION ALL
SELECT x, y FROM t52;
CREATE INDEX t51x ON t51(x);
CREATE INDEX t52x ON t52(x);
EXPLAIN QUERY PLAN
SELECT * FROM v5 WHERE x='12345' ORDER BY y;
}
} {~/SCAN TABLE/} ;# Uses indices with "*"
do_test select9-5.2 {
db eval {
EXPLAIN QUERY PLAN
SELECT x, y FROM v5 WHERE x='12345' ORDER BY y;
}
} {~/SCAN TABLE/} ;# Uses indices with "x, y"
do_test select9-5.3 {
db eval {
EXPLAIN QUERY PLAN
SELECT x, y FROM v5 WHERE +x='12345' ORDER BY y;
}
} {/SCAN TABLE/} ;# Full table scan if the "+x" prevents index usage.
finish_test
|