Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Only continue an ORDER BY optimization into inner loops if the equality constraints on the inner loop match terms of an outer ordered index that are actually used by the ORDER BY clause. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
b0e7b4df6c2a8c479f8d210bde50c737 |
| User & Date: | drh 2012-10-02 14:11:29 |
Context
|
2012-10-02
| ||
| 15:19 | More lenient handling of ORDER BY optimization in joins with mixed ASC/DESC. This is a better and less restrictive fix for the problem addressed by the previous check-in. (check-in: abcf6a5d user: drh tags: trunk) | |
| 14:11 | Only continue an ORDER BY optimization into inner loops if the equality constraints on the inner loop match terms of an outer ordered index that are actually used by the ORDER BY clause. (check-in: b0e7b4df user: drh tags: trunk) | |
| 01:46 | Factor an invariant out the loop termination condition for the ORDER BY satisfied-by-index analyzer routine. (check-in: 545bb336 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 |
}
if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
pIdx = pLevel->plan.u.pIdx;
if( iCol<0 ){
sortOrder = 0;
testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 );
}else{
for(j=0; j<pIdx->nColumn; j++){
if( iCol==pIdx->aiColumn[j] ) break;
}
if( j>=pIdx->nColumn ) return 0;
sortOrder = pIdx->aSortOrder[j];
testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 );
}
}else{
if( iCol!=(-1) ) return 0;
sortOrder = 0;
testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 );
|
> > | > | |
2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 |
}
if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
pIdx = pLevel->plan.u.pIdx;
if( iCol<0 ){
sortOrder = 0;
testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 );
}else{
int n = pLevel->plan.nOBSat;
if( p->i>=2 ) n -= pLevel[-1].plan.nOBSat;
assert( n<=pIdx->nColumn );
for(j=0; j<n; j++){
if( iCol==pIdx->aiColumn[j] ) break;
}
if( j>=n ) return 0;
sortOrder = pIdx->aSortOrder[j];
testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 );
}
}else{
if( iCol!=(-1) ) return 0;
sortOrder = 0;
testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 );
|
Changes to test/orderby1.test.
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 ... 189 190 191 192 193 194 195 196 197 198 199 200 201 202 ... 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 |
CREATE INDEX album_i1 ON album(title, aid);
CREATE TABLE track(
aid INTEGER NOT NULL REFERENCES album,
tn INTEGER NOT NULL,
name TEXT,
UNIQUE(aid, tn)
);
INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three');
INSERT INTO track VALUES
(1, 1, 'one-a'),
(2, 2, 'two-b'),
(3, 3, 'three-c'),
(1, 3, 'one-c'),
(2, 1, 'two-a'),
(3, 1, 'three-a');
COMMIT;
}
} {}
do_test 2.1a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
}
................................................................................
# Verify that the ORDER BY clause is optimized out
#
do_test 2.1b {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
}
} {~/ORDER BY/} ;# ORDER BY optimized out
# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 2.2a {
................................................................................
}
} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting
do_test 2.6c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {~/ORDER BY/} ;# ORDER BY optimized-out
# Generate another test dataset, but this time using mixed ASC/DESC indices.
#
do_test 3.0 {
db eval {
BEGIN;
|
| | | | | | | > > > > > > > > > > > > | |
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 ... 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 ... 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 |
CREATE INDEX album_i1 ON album(title, aid);
CREATE TABLE track(
aid INTEGER NOT NULL REFERENCES album,
tn INTEGER NOT NULL,
name TEXT,
UNIQUE(aid, tn)
);
INSERT INTO album VALUES(1, '1-one'), (20, '2-two'), (3, '3-three');
INSERT INTO track VALUES
(1, 1, 'one-a'),
(20, 2, 'two-b'),
(3, 3, 'three-c'),
(1, 3, 'one-c'),
(20, 1, 'two-a'),
(3, 1, 'three-a');
COMMIT;
}
} {}
do_test 2.1a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
}
................................................................................
# Verify that the ORDER BY clause is optimized out
#
do_test 2.1b {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
}
} {/ORDER BY/} ;# ORDER BY required because of missing aid term in ORDER BY
do_test 2.1c {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, aid, tn
}
} {one-a one-c two-a two-b three-a three-c}
do_test 2.1d {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, aid, tn
}
} {~/ORDER BY/} ;# ORDER BY optimized out
# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 2.2a {
................................................................................
}
} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting
do_test 2.6c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {/ORDER BY/} ;# ORDER BY required
# Generate another test dataset, but this time using mixed ASC/DESC indices.
#
do_test 3.0 {
db eval {
BEGIN;
|
Changes to test/orderby2.test.
63 64 65 66 67 68 69 70 71 |
do_test 1.3b {
db eval {
EXPLAIN QUERY PLAN
SELECT e, b FROM t1, t2 WHERE a=1 ORDER BY d, e;
}
} {~/ORDER BY/}
finish_test
|
> > > > > > > | > > > > > > > > > > > > > > > > > > > |
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
do_test 1.3b {
db eval {
EXPLAIN QUERY PLAN
SELECT e, b FROM t1, t2 WHERE a=1 ORDER BY d, e;
}
} {~/ORDER BY/}
# After where34.314 in TH3:
do_test 2.0 {
db eval {
CREATE TABLE t31(a,b); CREATE INDEX t31ab ON t31(a,b);
CREATE TABLE t32(c,d); CREATE INDEX t32cd ON t32(c,d);
CREATE TABLE t33(e,f); CREATE INDEX t33ef ON t33(e,f);
CREATE TABLE t34(g,h); CREATE INDEX t34gh ON t34(g,h);
INSERT INTO t31 VALUES(1,4), (2,3), (1,3);
INSERT INTO t32 VALUES(4,5), (3,6), (3,7), (4,8);
INSERT INTO t33 VALUES(5,9), (7,10), (6,11), (8,12), (8,13), (7,14);
INSERT INTO t34 VALUES(11,20), (10,21), (12,22), (9,23), (13,24),
(14,25), (12,26);
SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34
WHERE c=b AND e=d AND g=f
ORDER BY a ASC, c ASC, e DESC, g ASC;
}
} {1,3,7,10 1,3,7,14 1,3,6,11 1,4,8,12 1,4,8,12 1,4,8,13 1,4,5,9 2,3,7,10 2,3,7,14 2,3,6,11}
do_test 2.1 {
db eval {
SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34
WHERE c=b AND e=d AND g=f
ORDER BY +a ASC, +c ASC, +e DESC, +g ASC;
}
} {1,3,7,10 1,3,7,14 1,3,6,11 1,4,8,12 1,4,8,12 1,4,8,13 1,4,5,9 2,3,7,10 2,3,7,14 2,3,6,11}
finish_test
|