Overview
| SHA1 Hash: | 0f9bb90100aa304a7f28023ca4173e68b445e8bd |
|---|---|
| Date: | 2012-10-03 12:38:19 |
| User: | drh |
| Comment: | Fix a query planner problem that only occurs when covering-index-scan is disabled. Fix to tests whose output changed due to the new and more aggressive ORDER BY optimization. |
Tags And Properties
- branch=qp-enhancements inherited from [6744d9a37f]
- closed added by [19cb817f55] on 2012-10-03 12:56:22
- sym-qp-enhancements inherited from [6744d9a37f]
Changes
Changes to src/where.c
3362 ** the cost function to err on the side of choosing an index over 3362 ** the cost function to err on the side of choosing an index over 3363 ** choosing a full scan. This 4x full-scan penalty is an arguable 3363 ** choosing a full scan. This 4x full-scan penalty is an arguable 3364 ** decision and one which we expect to revisit in the future. But 3364 ** decision and one which we expect to revisit in the future. But 3365 ** it seems to be working well enough at the moment. 3365 ** it seems to be working well enough at the moment. 3366 */ 3366 */ 3367 pc.rCost = aiRowEst[0]*4; 3367 pc.rCost = aiRowEst[0]*4; 3368 pc.plan.wsFlags &= ~WHERE_IDX_ONLY; 3368 pc.plan.wsFlags &= ~WHERE_IDX_ONLY; > 3369 if( pIdx ) pc.plan.wsFlags &= ~WHERE_ORDERED; 3369 }else{ 3370 }else{ 3370 log10N = estLog(aiRowEst[0]); 3371 log10N = estLog(aiRowEst[0]); 3371 pc.rCost = pc.plan.nRow; 3372 pc.rCost = pc.plan.nRow; 3372 if( pIdx ){ 3373 if( pIdx ){ 3373 if( bLookup ){ 3374 if( bLookup ){ 3374 /* For an index lookup followed by a table lookup: 3375 /* For an index lookup followed by a table lookup: 3375 ** nInMul index searches to find the start of each index range 3376 ** nInMul index searches to find the start of each index range
Changes to test/where.test
1084 CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE); 1084 CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE); 1085 INSERT INTO t8 VALUES(1,'one'); 1085 INSERT INTO t8 VALUES(1,'one'); 1086 INSERT INTO t8 VALUES(4,'four'); 1086 INSERT INTO t8 VALUES(4,'four'); 1087 } 1087 } 1088 cksort { 1088 cksort { 1089 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b 1089 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b 1090 } 1090 } 1091 } {1/4 1/1 4/4 4/1 sort} | 1091 } {1/4 1/1 4/4 4/1 nosort} 1092 do_test where-14.2 { 1092 do_test where-14.2 { 1093 cksort { 1093 cksort { 1094 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC 1094 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC 1095 } 1095 } 1096 } {1/1 1/4 4/1 4/4 sort} | 1096 } {1/1 1/4 4/1 4/4 nosort} 1097 do_test where-14.3 { 1097 do_test where-14.3 { 1098 cksort { 1098 cksort { 1099 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b 1099 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b 1100 } 1100 } 1101 } {1/4 1/1 4/4 4/1 nosort} 1101 } {1/4 1/1 4/4 4/1 nosort} 1102 do_test where-14.4 { 1102 do_test where-14.4 { 1103 cksort { 1103 cksort {