Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix another problem involving vector range constraints and mixed ASC/DESC indexes. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | rowvalue |
| Files: | files | file ages | folders |
| SHA1: |
1559f4c43473e107f7196eea3ee91c53 |
| User & Date: | dan 2016-08-03 16:39:04 |
Context
|
2016-08-03
| ||
| 18:00 | Fix a problem with estimating the number of rows visited by a query that uses a multi-column IN(SELECT...) constraint. (check-in: 3c2f908f user: dan tags: rowvalue) | |
| 16:39 | Fix another problem involving vector range constraints and mixed ASC/DESC indexes. (check-in: 1559f4c4 user: dan tags: rowvalue) | |
| 16:14 | Fix stat4-based cost estimates for vector range constraints. (check-in: 18af74ab user: dan tags: rowvalue) | |
Changes
Changes to src/where.c.
2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 |
/* Check that the LHS of the comparison is a column reference to
** the right column of the right source table. And that the sort
** order of the index column is the same as the sort order of the
** leftmost index column. */
if( pLhs->op!=TK_COLUMN
|| pLhs->iTable!=iCur
|| pLhs->iColumn!=pIdx->aiColumn[i+nEq]
|| pIdx->aSortOrder[i]!=pIdx->aSortOrder[0]
){
break;
}
aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs));
idxaff = pIdx->pTable->aCol[pLhs->iColumn].affinity;
if( aff!=idxaff ) break;
|
| |
2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 |
/* Check that the LHS of the comparison is a column reference to
** the right column of the right source table. And that the sort
** order of the index column is the same as the sort order of the
** leftmost index column. */
if( pLhs->op!=TK_COLUMN
|| pLhs->iTable!=iCur
|| pLhs->iColumn!=pIdx->aiColumn[i+nEq]
|| pIdx->aSortOrder[i+nEq]!=pIdx->aSortOrder[nEq]
){
break;
}
aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs));
idxaff = pIdx->pTable->aCol[pLhs->iColumn].affinity;
if( aff!=idxaff ) break;
|
Changes to test/rowvalue4.test.
91
92
93
94
95
96
97
98
99
100
101
102
103
104
...
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); }
idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); }
idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); }
idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); }
idx7 { CREATE INDEX t2abc ON t2(a DESC, b DESC) }
idx8 { CREATE INDEX t2abc ON t2(c, b, a); }
idx9 { CREATE INDEX t2d ON t2(d); }
} {
drop_all_indexes
execsql $idx
foreach {tn where res} {
1 "(a, b, c) < (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13}
2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
................................................................................
7 "(a, b, c) >= (2, NULL, NULL)" {19 20 21 22 23 24 25 26 27}
8 "(a, b, c) <= (2, NULL, NULL)" {1 2 3 4 5 6 7 8 9}
9 "(a, b, c) < (SELECT a, b, c FROM t2 WHERE d=14)"
{1 2 3 4 5 6 7 8 9 10 11 12 13}
10 "(a, b, c) = (SELECT a, b, c FROM t2 WHERE d=14)" 14
} {
set result [db eval "SELECT d FROM t2 WHERE $where"]
do_test 2.1.$nm.$tn { lsort -integer $result } $res
}
foreach {tn e res} {
1 "(2, 1) IN (SELECT a, b FROM t2)" 1
|
>
>
>
>
|
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
...
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); }
idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); }
idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); }
idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); }
idx7 { CREATE INDEX t2abc ON t2(a DESC, b DESC) }
idx8 { CREATE INDEX t2abc ON t2(c, b, a); }
idx9 { CREATE INDEX t2d ON t2(d); }
idx10 { CREATE INDEX t2abc ON t2(a DESC, b, c DESC); }
} {
drop_all_indexes
execsql $idx
foreach {tn where res} {
1 "(a, b, c) < (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13}
2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
................................................................................
7 "(a, b, c) >= (2, NULL, NULL)" {19 20 21 22 23 24 25 26 27}
8 "(a, b, c) <= (2, NULL, NULL)" {1 2 3 4 5 6 7 8 9}
9 "(a, b, c) < (SELECT a, b, c FROM t2 WHERE d=14)"
{1 2 3 4 5 6 7 8 9 10 11 12 13}
10 "(a, b, c) = (SELECT a, b, c FROM t2 WHERE d=14)" 14
11 "a = 2 AND (b, c) > (2, 2)" {15 16 17 18}
12 "a = 2 AND (b, c) < (3, 3) AND (b, c) > (1, 1)" {11 12 13 14 15 16 17}
} {
set result [db eval "SELECT d FROM t2 WHERE $where"]
do_test 2.1.$nm.$tn { lsort -integer $result } $res
}
foreach {tn e res} {
1 "(2, 1) IN (SELECT a, b FROM t2)" 1
|