Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a bug in the transitive_closure virtual table that NGQP exposes. Update between.test module to avoid using sqlite_query_plan. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
| Files: | files | file ages | folders |
| SHA1: |
aa66b864204b4b8717d845499132b987 |
| User & Date: | drh 2013-06-03 18:50:15 |
Context
|
2013-06-03
| ||
| 19:01 | Resolve ambiguities in test cases in collate2.test. check-in: dcbbcb2e user: drh tags: nextgen-query-plan-exp | |
| 18:50 | Fix a bug in the transitive_closure virtual table that NGQP exposes. Update between.test module to avoid using sqlite_query_plan. check-in: aa66b864 user: drh tags: nextgen-query-plan-exp | |
| 18:14 | Fix EXPLAIN QUERY PLAN row counts for the rtree virtual table tests. check-in: 15328987 user: drh tags: nextgen-query-plan-exp | |
Changes
Changes to ext/misc/closure.c.
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
...
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
|
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;
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->aConstraintUsage[i].omit = 1;
}
if( (iPlan & 0x0000f0)==0
&& pConstraint->iColumn==CLOSURE_COL_DEPTH
&& (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
|| pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE
|| pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ)
){
iPlan |= idx<<4;
pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
if( pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT ) iPlan |= 0x000002;
}
if( (iPlan & 0x000f00)==0
&& pConstraint->iColumn==CLOSURE_COL_TABLENAME
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
){
iPlan |= idx<<8;
pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
pIdxInfo->aConstraintUsage[i].omit = 1;
}
if( (iPlan & 0x00f000)==0
&& pConstraint->iColumn==CLOSURE_COL_IDCOLUMN
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
){
iPlan |= idx<<12;
pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
................................................................................
pIdxInfo->idxNum = iPlan;
if( pIdxInfo->nOrderBy==1
&& pIdxInfo->aOrderBy[0].iColumn==CLOSURE_COL_ID
&& pIdxInfo->aOrderBy[0].desc==0
){
pIdxInfo->orderByConsumed = 1;
}
pIdxInfo->estimatedCost = (double)10000;
return SQLITE_OK;
}
/*
** A virtual table module that implements the "approximate_match".
*/
|
>
>
>
>
|
|
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
...
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
|
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->aConstraintUsage[i].omit = 1;
rCost /= 100.0;
}
if( (iPlan & 0x0000f0)==0
&& pConstraint->iColumn==CLOSURE_COL_DEPTH
&& (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
|| pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE
|| pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ)
){
iPlan |= idx<<4;
pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
if( pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT ) iPlan |= 0x000002;
rCost /= 5.0;
}
if( (iPlan & 0x000f00)==0
&& pConstraint->iColumn==CLOSURE_COL_TABLENAME
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
){
iPlan |= idx<<8;
pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
pIdxInfo->aConstraintUsage[i].omit = 1;
rCost /= 5.0;
}
if( (iPlan & 0x00f000)==0
&& pConstraint->iColumn==CLOSURE_COL_IDCOLUMN
&& pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
){
iPlan |= idx<<12;
pIdxInfo->aConstraintUsage[i].argvIndex = ++idx;
................................................................................
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".
*/
|
Changes to test/between.test.
51
52
53
54
55
56
57
58
59
60
61
62
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
98
99
100
101
102
...
103
104
105
106
107
108
109
110
111
112
113
|
# is done. Then it appends the ::sqlite_query_plan variable.
#
proc queryplan {sql} {
set ::sqlite_sort_count 0
set data [execsql $sql]
if {$::sqlite_sort_count} {set x sort} {set x nosort}
lappend data $x
return [concat $data $::sqlite_query_plan]
}
do_test between-1.1.1 {
queryplan {
SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 i1w}
do_test between-1.1.2 {
queryplan {
SELECT * FROM t1 WHERE +w BETWEEN 5 AND 6 ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 {}}
do_test between-1.2.1 {
queryplan {
SELECT * FROM t1 WHERE w BETWEEN 5 AND 65-y ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 i1w}
do_test between-1.2.2 {
queryplan {
SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 {}}
do_test between-1.3.1 {
queryplan {
SELECT * FROM t1 WHERE w BETWEEN 41-y AND 6 ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 i1w}
do_test between-1.3.2 {
queryplan {
SELECT * FROM t1 WHERE +w BETWEEN 41-y AND 6 ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 {}}
do_test between-1.4 {
queryplan {
SELECT * FROM t1 WHERE w BETWEEN 41-y AND 65-y ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 {}}
do_test between-1.5.1 {
queryplan {
SELECT * FROM t1 WHERE 26 BETWEEN y AND z ORDER BY +w
}
} {4 2 25 27 sort t1 i1zyx}
do_test between-1.5.2 {
queryplan {
................................................................................
SELECT * FROM t1 WHERE 26 BETWEEN +y AND z ORDER BY +w
}
} {4 2 25 27 sort t1 i1zyx}
do_test between-1.5.3 {
queryplan {
SELECT * FROM t1 WHERE 26 BETWEEN y AND +z ORDER BY +w
}
} {4 2 25 27 sort t1 {}}
finish_test
|
|
>
>
>
>
>
>
>
>
>
>
|
|
|
|
|
|
51
52
53
54
55
56
57
58
59
60
61
62
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
...
113
114
115
116
117
118
119
120
121
122
123
|
# is done. Then it appends the ::sqlite_query_plan variable.
#
proc queryplan {sql} {
set ::sqlite_sort_count 0
set data [execsql $sql]
if {$::sqlite_sort_count} {set x sort} {set x nosort}
lappend data $x
set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
# puts eqp=$eqp
foreach {a b c x} $eqp {
if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
$x all as tab idx]} {
lappend data $tab $idx
} elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
lappend data $tab *
}
}
return $data
}
do_test between-1.1.1 {
queryplan {
SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 i1w}
do_test between-1.1.2 {
queryplan {
SELECT * FROM t1 WHERE +w BETWEEN 5 AND 6 ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 *}
do_test between-1.2.1 {
queryplan {
SELECT * FROM t1 WHERE w BETWEEN 5 AND 65-y ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 i1w}
do_test between-1.2.2 {
queryplan {
SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 *}
do_test between-1.3.1 {
queryplan {
SELECT * FROM t1 WHERE w BETWEEN 41-y AND 6 ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 i1w}
do_test between-1.3.2 {
queryplan {
SELECT * FROM t1 WHERE +w BETWEEN 41-y AND 6 ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 *}
do_test between-1.4 {
queryplan {
SELECT * FROM t1 WHERE w BETWEEN 41-y AND 65-y ORDER BY +w
}
} {5 2 36 38 6 2 49 51 sort t1 *}
do_test between-1.5.1 {
queryplan {
SELECT * FROM t1 WHERE 26 BETWEEN y AND z ORDER BY +w
}
} {4 2 25 27 sort t1 i1zyx}
do_test between-1.5.2 {
queryplan {
................................................................................
SELECT * FROM t1 WHERE 26 BETWEEN +y AND z ORDER BY +w
}
} {4 2 25 27 sort t1 i1zyx}
do_test between-1.5.3 {
queryplan {
SELECT * FROM t1 WHERE 26 BETWEEN y AND +z ORDER BY +w
}
} {4 2 25 27 sort t1 *}
finish_test
|