Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Adjust the xBestIndex methods on both the fuzzer and transitive_closure virtual tables so that an unused MATCH operator gets a really large cost. Remove ambiguities from the fuzzer test cases. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
| Files: | files | file ages | folders |
| SHA1: |
e2c1af78b65a8ace976fa6c035db212e |
| User & Date: | drh 2013-06-03 21:25:28 |
Context
|
2013-06-03
| ||
| 22:08 | Remove more vestiges of sqlite_query_plan from the test cases. check-in: eb27086e user: drh tags: nextgen-query-plan-exp | |
| 21:25 | Adjust the xBestIndex methods on both the fuzzer and transitive_closure virtual tables so that an unused MATCH operator gets a really large cost. Remove ambiguities from the fuzzer test cases. check-in: e2c1af78 user: drh tags: nextgen-query-plan-exp | |
| 20:46 | Pull in recent trunk changes. Fix the ORDER BY optimizer so that it is better able to deal with COLLATE clauses. Clean up ambiguities in the descidx1.test script. check-in: 6bc71dfc user: drh tags: nextgen-query-plan-exp | |
Changes
Changes to ext/misc/closure.c.
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
...
891
892
893
894
895
896
897
898
899
900
901
902
903
904
|
static int closureBestIndex(
sqlite3_vtab *pTab, /* The virtual table */
sqlite3_index_info *pIdxInfo /* Information about the query */
){
int iPlan = 0;
int i;
int idx = 1;
const struct sqlite3_index_constraint *pConstraint;
closure_vtab *pVtab = (closure_vtab*)pTab;
double rCost = 10000000.0;
pConstraint = pIdxInfo->aConstraint;
for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
if( pConstraint->usable==0 ) continue;
if( (iPlan & 1)==0
&& pConstraint->iColumn==CLOSURE_COL_ROOT
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
){
iPlan |= 1;
pIdxInfo->aConstraintUsage[i].argvIndex = 1;
................................................................................
pIdxInfo->idxNum = iPlan;
if( pIdxInfo->nOrderBy==1
&& pIdxInfo->aOrderBy[0].iColumn==CLOSURE_COL_ID
&& pIdxInfo->aOrderBy[0].desc==0
){
pIdxInfo->orderByConsumed = 1;
}
pIdxInfo->estimatedCost = rCost;
return SQLITE_OK;
}
/*
** A virtual table module that implements the "approximate_match".
|
>
>
>
>
>
>
|
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
...
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
|
static int closureBestIndex(
sqlite3_vtab *pTab, /* The virtual table */
sqlite3_index_info *pIdxInfo /* Information about the query */
){
int iPlan = 0;
int i;
int idx = 1;
int seenMatch = 0;
const struct sqlite3_index_constraint *pConstraint;
closure_vtab *pVtab = (closure_vtab*)pTab;
double rCost = 10000000.0;
pConstraint = pIdxInfo->aConstraint;
for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
if( pConstraint->iColumn==CLOSURE_COL_ROOT
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ ){
seenMatch = 1;
}
if( pConstraint->usable==0 ) continue;
if( (iPlan & 1)==0
&& pConstraint->iColumn==CLOSURE_COL_ROOT
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
){
iPlan |= 1;
pIdxInfo->aConstraintUsage[i].argvIndex = 1;
................................................................................
pIdxInfo->idxNum = iPlan;
if( pIdxInfo->nOrderBy==1
&& pIdxInfo->aOrderBy[0].iColumn==CLOSURE_COL_ID
&& pIdxInfo->aOrderBy[0].desc==0
){
pIdxInfo->orderByConsumed = 1;
}
if( seenMatch && (iPlan&1)==0 ) rCost *= 1e30;
pIdxInfo->estimatedCost = rCost;
return SQLITE_OK;
}
/*
** A virtual table module that implements the "approximate_match".
|
Changes to ext/misc/fuzzer.c.
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
....
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
|
** filter.argv[2] if both bit-1 and bit-2 are set.
*/
static int fuzzerBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
int iPlan = 0;
int iDistTerm = -1;
int iRulesetTerm = -1;
int i;
const struct sqlite3_index_constraint *pConstraint;
pConstraint = pIdxInfo->aConstraint;
for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
if( pConstraint->usable==0 ) continue;
if( (iPlan & 1)==0
&& pConstraint->iColumn==0
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH
){
iPlan |= 1;
pIdxInfo->aConstraintUsage[i].argvIndex = 1;
pIdxInfo->aConstraintUsage[i].omit = 1;
}
if( (iPlan & 2)==0
&& pConstraint->iColumn==1
&& (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
|| pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE)
){
iPlan |= 2;
iDistTerm = i;
}
if( (iPlan & 4)==0
&& pConstraint->iColumn==2
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
){
iPlan |= 4;
pIdxInfo->aConstraintUsage[i].omit = 1;
iRulesetTerm = i;
}
}
if( iPlan & 2 ){
pIdxInfo->aConstraintUsage[iDistTerm].argvIndex = 1+((iPlan&1)!=0);
}
if( iPlan & 4 ){
int idx = 1;
................................................................................
pIdxInfo->idxNum = iPlan;
if( pIdxInfo->nOrderBy==1
&& pIdxInfo->aOrderBy[0].iColumn==1
&& pIdxInfo->aOrderBy[0].desc==0
){
pIdxInfo->orderByConsumed = 1;
}
pIdxInfo->estimatedCost = (double)10000;
return SQLITE_OK;
}
/*
** A virtual table module that implements the "fuzzer".
*/
|
>
>
>
>
>
>
>
>
>
>
>
|
|
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
....
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
|
** filter.argv[2] if both bit-1 and bit-2 are set.
*/
static int fuzzerBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
int iPlan = 0;
int iDistTerm = -1;
int iRulesetTerm = -1;
int i;
int seenMatch = 0;
const struct sqlite3_index_constraint *pConstraint;
double rCost = 100000;
pConstraint = pIdxInfo->aConstraint;
for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
if( pConstraint->iColumn==0
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH ){
seenMatch = 1;
}
if( pConstraint->usable==0 ) continue;
if( (iPlan & 1)==0
&& pConstraint->iColumn==0
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_MATCH
){
iPlan |= 1;
pIdxInfo->aConstraintUsage[i].argvIndex = 1;
pIdxInfo->aConstraintUsage[i].omit = 1;
rCost /= 1000000.0;
}
if( (iPlan & 2)==0
&& pConstraint->iColumn==1
&& (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
|| pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE)
){
iPlan |= 2;
iDistTerm = i;
rCost /= 10.0;
}
if( (iPlan & 4)==0
&& pConstraint->iColumn==2
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
){
iPlan |= 4;
pIdxInfo->aConstraintUsage[i].omit = 1;
iRulesetTerm = i;
rCost /= 10.0;
}
}
if( iPlan & 2 ){
pIdxInfo->aConstraintUsage[iDistTerm].argvIndex = 1+((iPlan&1)!=0);
}
if( iPlan & 4 ){
int idx = 1;
................................................................................
pIdxInfo->idxNum = iPlan;
if( pIdxInfo->nOrderBy==1
&& pIdxInfo->aOrderBy[0].iColumn==1
&& pIdxInfo->aOrderBy[0].desc==0
){
pIdxInfo->orderByConsumed = 1;
}
if( seenMatch && (iPlan&1)==0 ) rCost *= 1e30;
pIdxInfo->estimatedCost = rCost;
return SQLITE_OK;
}
/*
** A virtual table module that implements the "fuzzer".
*/
|
Changes to test/fuzzer1.test.
1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 |
INSERT INTO x3_rules VALUES(2, 'a', 'z', 8);
CREATE VIRTUAL TABLE x3 USING fuzzer(x3_rules);
}
do_execsql_test 8.2.1 {
SELECT cFrom, cTo, word
FROM x3_rules CROSS JOIN x3
WHERE word MATCH 'a' AND cost=distance AND ruleset=2;
} {a x x a y y a z z}
do_execsql_test 8.2.2 {
SELECT cFrom, cTo, word
FROM x3 CROSS JOIN x3_rules
WHERE word MATCH 'a' AND cost=distance AND ruleset=2;
} {a z z a y y a x x}
do_execsql_test 8.2.3 {
SELECT cFrom, cTo, word
FROM x3_rules, x3
WHERE word MATCH 'a' AND cost=distance AND ruleset=2;
} {a z z a y y a x x}
do_execsql_test 8.2.4 {
SELECT cFrom, cTo, word
FROM x3, x3_rules
WHERE word MATCH 'a' AND cost=distance AND ruleset=2;
} {a z z a y y a x x}
do_execsql_test 8.2.5 {
CREATE INDEX i1 ON x3_rules(cost);
SELECT cFrom, cTo, word
FROM x3_rules, x3
WHERE word MATCH 'a' AND cost=distance AND ruleset=2;
} {a z z a y y a x x}
do_execsql_test 8.2.5 {
SELECT word FROM x3_rules, x3 WHERE word MATCH x3_rules.cFrom AND ruleset=2;
} {a z y x a z y x a z y x}
do_execsql_test 8.2.6 {
SELECT word FROM x3_rules, x3
WHERE word MATCH x3_rules.cFrom
AND ruleset=2
AND x3_rules.cost=8;
|
| > | > | > | > | > | |
1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 |
INSERT INTO x3_rules VALUES(2, 'a', 'z', 8);
CREATE VIRTUAL TABLE x3 USING fuzzer(x3_rules);
}
do_execsql_test 8.2.1 {
SELECT cFrom, cTo, word
FROM x3_rules CROSS JOIN x3
WHERE word MATCH 'a' AND cost=distance AND ruleset=2
ORDER BY +cTo;
} {a x x a y y a z z}
do_execsql_test 8.2.2 {
SELECT cFrom, cTo, word
FROM x3 CROSS JOIN x3_rules
WHERE word MATCH 'a' AND cost=distance AND ruleset=2
ORDER BY +cTo DESC
} {a z z a y y a x x}
do_execsql_test 8.2.3 {
SELECT cFrom, cTo, word
FROM x3_rules, x3
WHERE word MATCH 'a' AND cost=distance AND ruleset=2
ORDER BY +cTo DESC;
} {a z z a y y a x x}
do_execsql_test 8.2.4 {
SELECT cFrom, cTo, word
FROM x3, x3_rules
WHERE word MATCH 'a' AND cost=distance AND ruleset=2
ORDER BY +cTo DESC;
} {a z z a y y a x x}
do_execsql_test 8.2.5 {
CREATE INDEX i1 ON x3_rules(cost);
SELECT cFrom, cTo, word
FROM x3_rules, x3
WHERE word MATCH 'a' AND cost=distance AND ruleset=2
ORDER BY +cTo DESC;
} {a z z a y y a x x}
do_execsql_test 8.2.5 {
SELECT word FROM x3_rules, x3 WHERE word MATCH x3_rules.cFrom AND ruleset=2
} {a z y x a z y x a z y x}
do_execsql_test 8.2.6 {
SELECT word FROM x3_rules, x3
WHERE word MATCH x3_rules.cFrom
AND ruleset=2
AND x3_rules.cost=8;
|