Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Allow an index paired with an IS NULL constraint to be used for sorting under the condition that the index be treated as a non-unique index. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
d78949fc93077e1aa7f05cf9f7e94772 |
| User & Date: | drh 2011-02-11 03:56:11 |
| Original Comment: | Allow an index paired with a NOT NULL constraint to be used for sorting under the condition that the index be treated as a non-unique index. |
Context
|
2011-02-11
| ||
| 06:59 | Fix a bug in the new WHERE-clause processing that tries to use an index to resolve IS NOT NULL constraints when SQLITE_ENABLE_STAT2 is defined. The bug could cause memory overruns and segfaults. The bug was new to the code and has not appeared in an official release. Found during structural testing. (check-in: a5c36b9f user: drh tags: trunk) | |
| 03:56 | Allow an index paired with an IS NULL constraint to be used for sorting under the condition that the index be treated as a non-unique index. (check-in: d78949fc user: drh tags: trunk) | |
| 02:43 | Disable unused NULL tests when SQLITE_ENABLE_STAT2 is not in use. (check-in: 5ecd1178 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 .... 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 .... 2877 2878 2879 2880 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 |
static int isSortingIndex(
Parse *pParse, /* Parsing context */
WhereMaskSet *pMaskSet, /* Mapping from table cursor numbers to bitmaps */
Index *pIdx, /* The index we are testing */
int base, /* Cursor number for the table to be sorted */
ExprList *pOrderBy, /* The ORDER BY clause */
int nEqCol, /* Number of index columns with == constraints */
int *pbRev /* Set to 1 if ORDER BY is DESC */
){
int i, j; /* Loop counters */
int sortOrder = 0; /* XOR of index and ORDER BY sort direction */
int nTerm; /* Number of ORDER BY terms */
struct ExprList_item *pTerm; /* A term of the ORDER BY clause */
sqlite3 *db = pParse->db;
................................................................................
*pbRev = sortOrder!=0;
if( j>=nTerm ){
/* All terms of the ORDER BY clause are covered by this index so
** this index can be used for sorting. */
return 1;
}
if( pIdx->onError!=OE_None && i==pIdx->nColumn
&& !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){
/* All terms of this index match some prefix of the ORDER BY clause
** and the index is UNIQUE and no terms on the tail of the ORDER BY
** clause reference other tables in a join. If this is all true then
** the order by clause is superfluous. */
return 1;
}
return 0;
}
/*
** Prepare a crude estimate of the logarithm of the input value.
................................................................................
}
/* If there is an ORDER BY clause and the index being considered will
** naturally scan rows in the required order, set the appropriate flags
** in wsFlags. Otherwise, if there is an ORDER BY clause but the index
** will scan rows in a different order, set the bSort variable. */
if( pOrderBy ){
if( (wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0
&& isSortingIndex(pParse,pWC->pMaskSet,pProbe,iCur,pOrderBy,nEq,&rev)
){
wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_ORDERBY;
wsFlags |= (rev ? WHERE_REVERSE : 0);
}else{
bSort = 1;
}
}
|
> > | > > | | > |
1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 .... 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 .... 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 |
static int isSortingIndex( Parse *pParse, /* Parsing context */ WhereMaskSet *pMaskSet, /* Mapping from table cursor numbers to bitmaps */ Index *pIdx, /* The index we are testing */ int base, /* Cursor number for the table to be sorted */ ExprList *pOrderBy, /* The ORDER BY clause */ int nEqCol, /* Number of index columns with == constraints */ int wsFlags, /* Index usages flags */ int *pbRev /* Set to 1 if ORDER BY is DESC */ ){ int i, j; /* Loop counters */ int sortOrder = 0; /* XOR of index and ORDER BY sort direction */ int nTerm; /* Number of ORDER BY terms */ struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ sqlite3 *db = pParse->db; ................................................................................ *pbRev = sortOrder!=0; if( j>=nTerm ){ /* All terms of the ORDER BY clause are covered by this index so ** this index can be used for sorting. */ return 1; } if( pIdx->onError!=OE_None && i==pIdx->nColumn && (wsFlags & WHERE_COLUMN_NULL)==0 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){ /* All terms of this index match some prefix of the ORDER BY clause ** and the index is UNIQUE and no terms on the tail of the ORDER BY ** clause reference other tables in a join. If this is all true then ** the order by clause is superfluous. Not that if the matching ** condition is IS NULL then the result is not necessarily unique ** even on a UNIQUE index, so disallow those cases. */ return 1; } return 0; } /* ** Prepare a crude estimate of the logarithm of the input value. ................................................................................ } /* If there is an ORDER BY clause and the index being considered will ** naturally scan rows in the required order, set the appropriate flags ** in wsFlags. Otherwise, if there is an ORDER BY clause but the index ** will scan rows in a different order, set the bSort variable. */ if( pOrderBy ){ if( (wsFlags & WHERE_COLUMN_IN)==0 && isSortingIndex(pParse, pWC->pMaskSet, pProbe, iCur, pOrderBy, nEq, wsFlags, &rev) ){ wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_ORDERBY; wsFlags |= (rev ? WHERE_REVERSE : 0); }else{ bSort = 1; } } |
Changes to test/tkt3824.test.
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b, a;
}
} {5 9 sort}
do_test tkt3824-2.3 {
lsort [execsql_status {
SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b;
}]
} {5 9 sort}
do_test tkt3824-3.1 {
db eval {
CREATE TABLE t3(x,y);
INSERT INTO t3 SELECT a, b FROM t1;
INSERT INTO t3 VALUES(234,567);
CREATE UNIQUE INDEX t3y ON t3(y);
|
| |
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b, a;
}
} {5 9 sort}
do_test tkt3824-2.3 {
lsort [execsql_status {
SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b;
}]
} {5 9 nosort}
do_test tkt3824-3.1 {
db eval {
CREATE TABLE t3(x,y);
INSERT INTO t3 SELECT a, b FROM t1;
INSERT INTO t3 VALUES(234,567);
CREATE UNIQUE INDEX t3y ON t3(y);
|