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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
a49e909c8738317c8383ce93771c0a9c |
| User & Date: | drh 2012-04-27 01:09:06 |
References
|
2014-09-20
| ||
| 17:05 | • New ticket [d11a6e90] Query planner fault on three-way nested join. (artifact: ce50af6b user: drh) | |
Context
|
2012-04-27
| ||
| 16:38 | Fix a minor deviation from the coding style guidelines. (check-in: 1e51bffe 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: a49e909c 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: c9a73440 user: drh tags: trunk) | |
Changes
Changes to src/resolve.c.
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
...
915
916
917
918
919
920
921
922
923
924
925
926
927
928
|
*/
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; /* Loop counter */
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;
................................................................................
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.
|
|
>
>
>
>
>
|
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
...
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
|
*/
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;
................................................................................
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
|