Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | More test cases an bug fixes for the ORDER BY optimization of joins. All veryquick tests now pass. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | qp-enhancements |
| Files: | files | file ages | folders |
| SHA1: |
0d573320057b0903a5589cabfb1b1ece |
| User & Date: | drh 2012-09-27 19:53:38 |
Context
|
2012-09-27
| ||
| 23:27 | Fix some corner case behavior in the new ORDER BY optimization logic. Remove the SQLITE_OrderByIdx bit from the SQLITE_TESTCTRL_OPTIMIZATIONS mask, since enabling it caused many TH3 tests to fail when the NO_OPT configuration parameter was engaged, and since there really isn't any need to turn that optimization off. The SQLITE_OrderByIdxJoin bit remains. (Closed-Leaf check-in: 98b63371 user: drh tags: qp-enhancements) | |
| 19:53 | More test cases an bug fixes for the ORDER BY optimization of joins. All veryquick tests now pass. (check-in: 0d573320 user: drh tags: qp-enhancements) | |
| 17:31 | Test cases and bug fixes applied to the ORDER BY optimization for joins. Some test cases fail, but except for the new orderby1.test failures, all failures appear to be issues with the tests, not with the core code. (check-in: 75cda864 user: drh tags: qp-enhancements) | |
Changes
Changes to src/where.c.
1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 .... 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 .... 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 .... 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 .... 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 .... 5084 5085 5086 5087 5088 5089 5090 5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 |
int nTerm; /* Number of ORDER BY terms */
struct ExprList_item *pTerm; /* A term of the ORDER BY clause */
ExprList *pOrderBy; /* The ORDER BY clause */
Parse *pParse = p->pParse; /* Parser context */
sqlite3 *db = pParse->db; /* Database connection */
int nPriorSat; /* ORDER BY terms satisfied by outer loops */
int seenRowid = 0; /* True if an ORDER BY rowid term is seen */
if( OptimizationDisabled(db, SQLITE_OrderByIdx) ) return 0;
if( p->i==0 ){
nPriorSat = 0;
}else{
if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return 0;
nPriorSat = p->aLevel[p->i-1].plan.nOBSat;
}
if( p->i>0 && nEqCol==0 /*&& !allOuterLoopsUnique(p)*/ ) return nPriorSat;
pOrderBy = p->pOrderBy;
if( !pOrderBy ) return nPriorSat;
if( wsFlags & WHERE_COLUMN_IN ) return nPriorSat;
if( pIdx->bUnordered ) return nPriorSat;
nTerm = pOrderBy->nExpr;
................................................................................
return nPriorSat;
}
}
assert( pIdx->aSortOrder!=0 || iColumn==-1 );
assert( pTerm->sortOrder==0 || pTerm->sortOrder==1 );
assert( iSortOrder==0 || iSortOrder==1 );
termSortOrder = iSortOrder ^ pTerm->sortOrder;
if( i>nEqCol ){
if( termSortOrder!=sortOrder ){
/* Indices can only be used if all ORDER BY terms past the
** equality constraints are all either DESC or ASC. */
break;
}
}else{
sortOrder = termSortOrder;
................................................................................
j++;
pTerm++;
if( iColumn<0 ){
seenRowid = 1;
break;
}
}
*pbRev = bOuterRev ^ sortOrder;
/* If there was an "ORDER BY rowid" term that matched, or it is only
** possible for a single row from this table to match, then skip over
** any additional ORDER BY terms dealing with this table.
*/
if( seenRowid ||
( (wsFlags & WHERE_COLUMN_NULL)==0
................................................................................
** on one page and hence more pages have to be fetched.
**
** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do
** not give us data on the relative sizes of table and index records.
** So this computation assumes table records are about twice as big
** as index records
*/
if( wsFlags==WHERE_IDX_ONLY
&& (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
&& sqlite3GlobalConfig.bUseCis
&& OptimizationEnabled(pParse->db, SQLITE_CoverIdxScan)
){
/* This index is not useful for indexing, but it is a covering index.
** A full-scan of the index might be a little faster than a full-scan
** of the table, so give this case a cost slightly less than a table
................................................................................
}
}
if( nRow<2 ) nRow = 2;
}
WHERETRACE((
"%s(%s): nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
" notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f\n"
" used=0x%llx nOrdered=%d nOBSat=%d\n",
pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"),
nEq, nInMul, (int)rangeDiv, bSort, bLookup, wsFlags,
p->notReady, log10N, nRow, cost, used, nOrdered, nOBSat
));
/* If this index is the best we have seen so far, then record this
** index and its cost in the pCost structure.
................................................................................
bestJ = j;
}
if( doNotReorder ) break;
}
}
assert( bestJ>=0 );
assert( sWBI.notValid & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
WHERETRACE(("*** Optimizer selects table %d for loop %d"
" with cost=%.1f, nRow=%.1f, nOBSat=%d\n",
bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow,
bestPlan.plan.nOBSat));
if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
pWInfo->nOBSat = pOrderBy->nExpr;
}
if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){
assert( pWInfo->eDistinct==0 );
pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
}
|
> > > > | | | > | | | | | | |
1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 .... 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 .... 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 .... 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 3309 3310 .... 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 3437 3438 .... 5089 5090 5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 5102 5103 5104 5105 5106 |
int nTerm; /* Number of ORDER BY terms */ struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Parse *pParse = p->pParse; /* Parser context */ sqlite3 *db = pParse->db; /* Database connection */ int nPriorSat; /* ORDER BY terms satisfied by outer loops */ int seenRowid = 0; /* True if an ORDER BY rowid term is seen */ int nEqOneRow; /* Idx columns that ref unique values */ if( OptimizationDisabled(db, SQLITE_OrderByIdx) ) return 0; if( p->i==0 ){ nPriorSat = 0; nEqOneRow = nEqCol; }else{ if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return 0; nPriorSat = p->aLevel[p->i-1].plan.nOBSat; sortOrder = bOuterRev; nEqOneRow = 0; } if( p->i>0 && nEqCol==0 /*&& !allOuterLoopsUnique(p)*/ ) return nPriorSat; pOrderBy = p->pOrderBy; if( !pOrderBy ) return nPriorSat; if( wsFlags & WHERE_COLUMN_IN ) return nPriorSat; if( pIdx->bUnordered ) return nPriorSat; nTerm = pOrderBy->nExpr; ................................................................................ return nPriorSat; } } assert( pIdx->aSortOrder!=0 || iColumn==-1 ); assert( pTerm->sortOrder==0 || pTerm->sortOrder==1 ); assert( iSortOrder==0 || iSortOrder==1 ); termSortOrder = iSortOrder ^ pTerm->sortOrder; if( i>nEqOneRow ){ if( termSortOrder!=sortOrder ){ /* Indices can only be used if all ORDER BY terms past the ** equality constraints are all either DESC or ASC. */ break; } }else{ sortOrder = termSortOrder; ................................................................................ j++; pTerm++; if( iColumn<0 ){ seenRowid = 1; break; } } *pbRev = sortOrder; /* If there was an "ORDER BY rowid" term that matched, or it is only ** possible for a single row from this table to match, then skip over ** any additional ORDER BY terms dealing with this table. */ if( seenRowid || ( (wsFlags & WHERE_COLUMN_NULL)==0 ................................................................................ ** on one page and hence more pages have to be fetched. ** ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do ** not give us data on the relative sizes of table and index records. ** So this computation assumes table records are about twice as big ** as index records */ if( (wsFlags&~WHERE_REVERSE)==WHERE_IDX_ONLY && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 && sqlite3GlobalConfig.bUseCis && OptimizationEnabled(pParse->db, SQLITE_CoverIdxScan) ){ /* This index is not useful for indexing, but it is a covering index. ** A full-scan of the index might be a little faster than a full-scan ** of the table, so give this case a cost slightly less than a table ................................................................................ } } if( nRow<2 ) nRow = 2; } WHERETRACE(( "%s(%s):\n" " nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%08x\n" " notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f\n" " used=0x%llx nOrdered=%d nOBSat=%d\n", pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), nEq, nInMul, (int)rangeDiv, bSort, bLookup, wsFlags, p->notReady, log10N, nRow, cost, used, nOrdered, nOBSat )); /* If this index is the best we have seen so far, then record this ** index and its cost in the pCost structure. ................................................................................ bestJ = j; } if( doNotReorder ) break; } } assert( bestJ>=0 ); assert( sWBI.notValid & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); WHERETRACE(("*** Optimizer selects table %d for loop %d with:\n" " cost=%.1f, nRow=%.1f, nOBSat=%d wsFlags=0x%08x\n", bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow, bestPlan.plan.nOBSat, bestPlan.plan.wsFlags)); if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ pWInfo->nOBSat = pOrderBy->nExpr; } if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){ assert( pWInfo->eDistinct==0 ); pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } |
Changes to test/e_select.test.
1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 .... 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 .... 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 .... 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 |
# These tests also show that the following is not untrue:
#
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
1 "SELECT group_concat(one), two FROM b1 GROUP BY two" {
4,5 f 1 o 7,6 s 3,2 t
}
2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
1,2,3,4 10 5,6,7 18
}
3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
4 1,5 2,6 3,7
}
................................................................................
}
}
# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
do_select_tests e_select-4.10 {
1 "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1 3 2,4}
2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}
}
# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
# sequence with which to compare text values apply when evaluating
# expressions in a GROUP BY clause.
#
................................................................................
1 2 3 1 2 -20 1 4 93 1 5 -1
}
7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" {
2 4 93 2 5 -1 1 2 -20 1 2 3
1 2 7 1 2 8 1 4 93 1 5 -1
}
8 "SELECT z, x FROM d1 ORDER BY 2" {
3 1 8 1 7 1 -20 1
93 1 -1 1 -1 2 93 2
}
9 "SELECT z, x FROM d1 ORDER BY 1" {
-20 1 -1 2 -1 1 3 1
7 1 8 1 93 2 93 1
}
}
# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
# that corresponds to the alias of one of the output columns, then the
# expression is considered an alias for that column.
#
................................................................................
1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
-19 0 0 4 8 9 94 94
}
2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
94 94 9 8 4 0 0 -19
}
3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
3 1 8 1 7 1 -20 1 93 1 -1 1 -1 2 93 2
}
4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
-20 1 -1 2 -1 1 3 1 7 1 8 1 93 2 93 1
}
}
# EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is
# any other expression, it is evaluated and the returned value used to
# order the output rows.
#
|
| | | | | | | | |
1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 .... 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 .... 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 .... 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 |
# These tests also show that the following is not untrue:
#
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
1 "SELECT group_concat(one), two FROM b1 GROUP BY two" {
/#,# f 1 o #,# s #,# t/
}
2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
1,2,3,4 10 5,6,7 18
}
3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
4 1,5 2,6 3,7
}
................................................................................
}
}
# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
do_select_tests e_select-4.10 {
1 "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,# 3 #,#/}
2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}
}
# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
# sequence with which to compare text values apply when evaluating
# expressions in a GROUP BY clause.
#
................................................................................
1 2 3 1 2 -20 1 4 93 1 5 -1
}
7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" {
2 4 93 2 5 -1 1 2 -20 1 2 3
1 2 7 1 2 8 1 4 93 1 5 -1
}
8 "SELECT z, x FROM d1 ORDER BY 2" {
/# 1 # 1 # 1 # 1
# 1 # 1 # 2 # 2/
}
9 "SELECT z, x FROM d1 ORDER BY 1" {
/-20 1 -1 # -1 # 3 1
7 1 8 1 93 # 93 #/
}
}
# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
# that corresponds to the alias of one of the output columns, then the
# expression is considered an alias for that column.
#
................................................................................
1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
-19 0 0 4 8 9 94 94
}
2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
94 94 9 8 4 0 0 -19
}
3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
/# 1 # 1 # 1 # 1 # 1 # 1 # 2 # 2/
}
4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
/-20 1 -1 # -1 # 3 1 7 1 8 1 93 # 93 #/
}
}
# EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is
# any other expression, it is evaluated and the returned value used to
# order the output rows.
#
|
Changes to test/orderby1.test.
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 ... 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 ... 147 148 149 150 151 152 153 154 155 |
}
} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting
do_test 1.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {~/ORDER BY/} ;# ORDER BY optimized-out
do_test 1.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
................................................................................
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
}
} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting
do_test 1.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {~/ORDER BY/} ;# ORDER BY optimized-out
do_test 1.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
do_test 1.6b {
................................................................................
do_test 1.6c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {~/ORDER BY/} ;# ORDER BY optimized-out
finish_test
|
| | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 ... 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 ... 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 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 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 |
}
} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting
do_test 1.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC
do_test 1.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
................................................................................
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
}
} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting
do_test 1.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC
do_test 1.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
do_test 1.6b {
................................................................................
do_test 1.6c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {~/ORDER BY/} ;# ORDER BY optimized-out
# Reconstruct the test data to use indices rather than integer primary keys.
#
do_test 2.0 {
db eval {
BEGIN;
DROP TABLE album;
DROP TABLE track;
CREATE TABLE album(
aid INT PRIMARY KEY,
title TEXT NOT NULL
);
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'), (2, '2-two'), (3, '3-three');
INSERT INTO track VALUES
(1, 1, 'one-a'),
(2, 2, 'two-b'),
(3, 3, 'three-c'),
(1, 3, 'one-c'),
(2, 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 optimized out
# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 2.2a {
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}
# The output is sorted manually in this case.
#
do_test 2.2b {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
}
} {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms
# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 2.3a {
optimization_control db order-by-idx-join 0
db cache flush
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}
do_test 2.3b {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
}
} {/ORDER BY/} ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush
# Reverse order sorts
#
do_test 2.4a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {three-a three-c two-a two-b one-a one-c}
do_test 2.4b {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
}
} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting
do_test 2.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC
do_test 2.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
do_test 2.5b {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
}
} {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting
do_test 2.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {/ORDER BY/} ;# separate sorting pass due to mixed ASC/DESC
do_test 2.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
do_test 2.6b {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
}
} {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 optimized-out
# Generate another test dataset, but this time using mixed ASC/DESC indices.
#
do_test 3.0 {
db eval {
BEGIN;
DROP TABLE album;
DROP TABLE track;
CREATE TABLE album(
aid INTEGER PRIMARY KEY,
title TEXT UNIQUE NOT NULL
);
CREATE TABLE track(
tid INTEGER PRIMARY KEY,
aid INTEGER NOT NULL REFERENCES album,
tn INTEGER NOT NULL,
name TEXT,
UNIQUE(aid ASC, tn DESC)
);
INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three');
INSERT INTO track VALUES
(NULL, 1, 1, 'one-a'),
(NULL, 2, 2, 'two-b'),
(NULL, 3, 3, 'three-c'),
(NULL, 1, 3, 'one-c'),
(NULL, 2, 1, 'two-a'),
(NULL, 3, 1, 'three-a');
COMMIT;
}
} {}
do_test 3.1a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
# Verify that the ORDER BY clause is optimized out
#
do_test 3.1b {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {~/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 3.2a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
# The output is sorted manually in this case.
#
do_test 3.2b {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
}
} {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms
# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 3.3a {
optimization_control db order-by-idx-join 0
db cache flush
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {one-c one-a two-b two-a three-c three-a}
do_test 3.3b {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
}
} {/ORDER BY/} ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush
# Without the mixed ASC/DESC on ORDER BY
#
do_test 3.4a {
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}
do_test 3.4b {
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 same order after sorting
do_test 3.4c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
}
} {/ORDER BY/} ;# separate sorting pass due to mismatched DESC/ASC
do_test 3.5a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {three-c three-a two-b two-a one-c one-a}
do_test 3.5b {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
}
} {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting
do_test 3.5c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
}
} {/ORDER BY/} ;# separate sorting pass due to mismatched ASC/DESC
do_test 3.6a {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {three-a three-c two-a two-b one-a one-c}
do_test 3.6b {
db eval {
SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
}
} {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting
do_test 3.6c {
db eval {
EXPLAIN QUERY PLAN
SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
}
} {~/ORDER BY/} ;# inverted ASC/DESC is optimized out
finish_test
|
Changes to test/tester.tcl.
534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 |
if {![info exists ::G(match)] || [string match $::G(match) $name]} {
if {[catch {uplevel #0 "$cmd;\n"} result]} {
puts "\nError: $result"
fail_test $name
} else {
if {[regexp {^~?/.*/$} $expected]} {
if {[string index $expected 0]=="~"} {
set re [string range $expected 2 end-1]
set ok [expr {![regexp $re $result]}]
} else {
set re [string range $expected 1 end-1]
set ok [regexp $re $result]
}
} else {
set ok [expr {[string compare $result $expected]==0}]
}
if {!$ok} {
# if {![info exists ::testprefix] || $::testprefix eq ""} {
|
| | |
534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 |
if {![info exists ::G(match)] || [string match $::G(match) $name]} {
if {[catch {uplevel #0 "$cmd;\n"} result]} {
puts "\nError: $result"
fail_test $name
} else {
if {[regexp {^~?/.*/$} $expected]} {
if {[string index $expected 0]=="~"} {
set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]]
set ok [expr {![regexp $re $result]}]
} else {
set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]]
set ok [regexp $re $result]
}
} else {
set ok [expr {[string compare $result $expected]==0}]
}
if {!$ok} {
# if {![info exists ::testprefix] || $::testprefix eq ""} {
|
Changes to test/tkt-cbd054fa6b.test.
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
..
78
79
80
81
82
83
84
85
86
87
|
do_test tkt-cbd05-1.3 {
execsql {
SELECT tbl,idx,group_concat(sample,' ')
FROM sqlite_stat3
WHERE idx = 't1_x'
GROUP BY tbl,idx
}
} {t1 t1_x { A B C D E F G H I}}
do_test tkt-cbd05-2.1 {
db eval {
DROP TABLE t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL);
CREATE INDEX t1_x ON t1(b);
INSERT INTO t1 VALUES(NULL, X'');
................................................................................
do_test tkt-cbd05-2.3 {
execsql {
SELECT tbl,idx,group_concat(sample,' ')
FROM sqlite_stat3
WHERE idx = 't1_x'
GROUP BY tbl,idx
}
} {t1 t1_x { A B C D E F G H I}}
finish_test
|
|
|
|
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
..
78
79
80
81
82
83
84
85
86
87
|
do_test tkt-cbd05-1.3 {
execsql {
SELECT tbl,idx,group_concat(sample,' ')
FROM sqlite_stat3
WHERE idx = 't1_x'
GROUP BY tbl,idx
}
} {/t1 t1_x .[ ABCDEFGHI]{10}./}
do_test tkt-cbd05-2.1 {
db eval {
DROP TABLE t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL);
CREATE INDEX t1_x ON t1(b);
INSERT INTO t1 VALUES(NULL, X'');
................................................................................
do_test tkt-cbd05-2.3 {
execsql {
SELECT tbl,idx,group_concat(sample,' ')
FROM sqlite_stat3
WHERE idx = 't1_x'
GROUP BY tbl,idx
}
} {/t1 t1_x .[ ABCDEFGHI]{10}./}
finish_test
|
Changes to test/where.test.
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
....
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
|
SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
}
} {1 0 4 2 1 9 3 1 16 102}
do_test where-5.3 {
count {
SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
}
} {1 0 4 2 1 9 3 1 16 14}
do_test where-5.4 {
count {
SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
}
} {1 0 4 2 1 9 3 1 16 102}
do_test where-5.5 {
count {
................................................................................
}
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.5 {
# This test case changed from "nosort" to "sort". See ticket 2a5629202f.
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
}
} {4/4 4/1 1/4 1/1 sort}
do_test where-14.6 {
# This test case changed from "nosort" to "sort". See ticket 2a5629202f.
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
}
} {4/4 4/1 1/4 1/1 sort}
do_test where-14.7 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
}
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7.1 {
cksort {
|
|
|
|
|
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
....
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
|
SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
}
} {1 0 4 2 1 9 3 1 16 102}
do_test where-5.3 {
count {
SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
}
} {1 0 4 2 1 9 3 1 16 13}
do_test where-5.4 {
count {
SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
}
} {1 0 4 2 1 9 3 1 16 102}
do_test where-5.5 {
count {
................................................................................
}
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.5 {
# This test case changed from "nosort" to "sort". See ticket 2a5629202f.
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
}
} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
do_test where-14.6 {
# This test case changed from "nosort" to "sort". See ticket 2a5629202f.
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
}
} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
do_test where-14.7 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
}
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7.1 {
cksort {
|