Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | All test cases (veryquick.tcl and min.rc) pass. A few branch operations in ORDER BY optimization logic are untested by min.rc. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | qp-enhancements |
| Files: | files | file ages | folders |
| SHA1: |
8314fd6078a3a71aea9d6386ff277cfe |
| User & Date: | drh 2012-10-08 21:01:15 |
Context
|
2012-10-08
| ||
| 21:51 | Merge ORDER BY optimization refactoring and repair into trunk. (check-in: c027a9af user: drh tags: trunk) | |
| 21:01 | All test cases (veryquick.tcl and min.rc) pass. A few branch operations in ORDER BY optimization logic are untested by min.rc. (Closed-Leaf check-in: 8314fd60 user: drh tags: qp-enhancements) | |
| 20:27 | Further tweaks to the ORDER BY optimizer, to fix a bug and to get the optimizer to recognize some additional cases. (check-in: bcb4f262 user: drh tags: qp-enhancements) | |
Changes
Changes to src/where.c.
2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 |
}else{
Expr *pRight = pConstraint->pExpr->pRight;
if( pRight->op==TK_COLUMN ){
WHERETRACE((" .. isOrderedColumn(tab=%d,col=%d)",
pRight->iTable, pRight->iColumn));
isEq = isOrderedColumn(p, pRight->iTable, pRight->iColumn);
WHERETRACE((" -> isEq=%d\n", isEq));
if( isEq>=2 && isEq!=pOBItem->sortOrder+2 ){
break;
}
}else{
isEq = 0;
}
}
assert( pOBItem->sortOrder==0 || pOBItem->sortOrder==1 );
|
| |
2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 |
}else{
Expr *pRight = pConstraint->pExpr->pRight;
if( pRight->op==TK_COLUMN ){
WHERETRACE((" .. isOrderedColumn(tab=%d,col=%d)",
pRight->iTable, pRight->iColumn));
isEq = isOrderedColumn(p, pRight->iTable, pRight->iColumn);
WHERETRACE((" -> isEq=%d\n", isEq));
if( isMatch && isEq>=2 && isEq!=pOBItem->sortOrder+2 ){
break;
}
}else{
isEq = 0;
}
}
assert( pOBItem->sortOrder==0 || pOBItem->sortOrder==1 );
|
Changes to test/orderby1.test.
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 ... 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 ... 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 ... 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 ... 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 ... 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 |
}
} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting
do_test 1.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC
do_test 1.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
................................................................................
}
} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting
do_test 1.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC
do_test 1.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
do_test 1.6b {
................................................................................
}
} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting
do_test 2.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC
do_test 2.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
................................................................................
}
} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting
do_test 2.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {/ORDER BY/} ;# separate sorting pass due to mixed ASC/DESC
do_test 2.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
do_test 2.6b {
................................................................................
}
} {one-a one-c two-a two-b three-a three-c} ;# verify same order after sorting
do_test 3.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
}
} {/ORDER BY/} ;# separate sorting pass due to mismatched DESC/ASC
do_test 3.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
................................................................................
}
} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting
do_test 3.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {/ORDER BY/} ;# separate sorting pass due to mismatched ASC/DESC
do_test 3.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {three-a three-c two-a two-b one-a one-c}
|
| | | | | | |
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 ... 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 ... 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 ... 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 ... 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 ... 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 |
}
} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting
do_test 1.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {~/ORDER BY/} ;# optimized out
do_test 1.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
................................................................................
}
} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting
do_test 1.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {~/ORDER BY/} ;# optimized out
do_test 1.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
do_test 1.6b {
................................................................................
}
} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting
do_test 2.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {~/ORDER BY/} ;# optimized out
do_test 2.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
................................................................................
}
} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting
do_test 2.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {~/ORDER BY/} ;# optimized out
do_test 2.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
do_test 2.6b {
................................................................................
}
} {one-a one-c two-a two-b three-a three-c} ;# verify same order after sorting
do_test 3.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
}
} {~/ORDER BY/} ;# optimized out
do_test 3.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
................................................................................
}
} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting
do_test 3.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {~/ORDER BY/} ;# optimzed out
do_test 3.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {three-a three-c two-a two-b one-a one-c}
|