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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
b0e7b4df6c2a8c479f8d210bde50c737 |
User & Date: | drh 2012-10-02 14:11:29.949 |
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: abcf6a5d05 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: b0e7b4df6c 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: 545bb33688 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
2732 2733 2734 2735 2736 2737 2738 | } 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{ | > > > | | | 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 | 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) ); | | | | | | | | > > > > > > > > > > > > | 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 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 | 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 } } {one-a one-c two-a two-b three-a three-c} # 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 { |
︙ | ︙ | |||
280 281 282 283 284 285 286 | } } {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 } | | | 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 | } } {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 |