Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | When determining whether an == or IS constraint in a WHERE clause makes an ORDER BY term redundant, consider the collation sequence used by the == or IS comparison, not the collation sequence of the comparison expression itself. Possible fix for [fb8c538a8f]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
16aed5d0c63dcdc2054dbb8a4b6b9924 |
| User & Date: | dan 2020-02-12 11:57:35 |
Context
|
2020-02-12
| ||
| 20:50 | Increase the default upper bound on the number of parameters in a single SQL statement to 32766 (from 999). (check-in: 2def7569 user: drh tags: trunk) | |
| 11:57 | When determining whether an == or IS constraint in a WHERE clause makes an ORDER BY term redundant, consider the collation sequence used by the == or IS comparison, not the collation sequence of the comparison expression itself. Possible fix for [fb8c538a8f]. (check-in: 16aed5d0 user: dan tags: trunk) | |
|
2020-02-10
| ||
| 19:24 | Mark the sha1() extension function as SQLITE_DETERMINISTIC. (check-in: 7d8dcfb9 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
3748 3749 3750 3751 3752 3753 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 |
** optimization, and then only if they are actually used
** by the query plan */
assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){}
if( j>=pLoop->nLTerm ) continue;
}
if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
if( sqlite3ExprCollSeqMatch(pWInfo->pParse,
pOrderBy->a[i].pExpr, pTerm->pExpr)==0 ){
continue;
}
testcase( pTerm->pExpr->op==TK_IS );
}
obSat |= MASKBIT(i);
}
|
| | > > > |
3748 3749 3750 3751 3752 3753 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 3764 3765 3766 |
** optimization, and then only if they are actually used
** by the query plan */
assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){}
if( j>=pLoop->nLTerm ) continue;
}
if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
Parse *pParse = pWInfo->pParse;
CollSeq *pColl1 = sqlite3ExprNNCollSeq(pParse, pOrderBy->a[i].pExpr);
CollSeq *pColl2 = sqlite3ExprCompareCollSeq(pParse, pTerm->pExpr);
assert( pColl1 && (pParse->nErr || pColl2) );
if( pColl2==0 || sqlite3StrICmp(pColl1->zName, pColl2->zName) ){
continue;
}
testcase( pTerm->pExpr->op==TK_IS );
}
obSat |= MASKBIT(i);
}
|
Changes to test/orderby5.test.
122 123 124 125 126 127 128 129 130 |
DROP TABLE t3;
CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f) WITHOUT rowid;
CREATE INDEX t3bcde ON t3(b, c, d, e);
EXPLAIN QUERY PLAN
SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC;
} {~/B-TREE/}
finish_test
|
> > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 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 |
DROP TABLE t3;
CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f) WITHOUT rowid;
CREATE INDEX t3bcde ON t3(b, c, d, e);
EXPLAIN QUERY PLAN
SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC;
} {~/B-TREE/}
#-------------------------------------------------------------------------
do_execsql_test 4.1.0 {
CREATE TABLE t4(b COLLATE nocase);
INSERT INTO t4 VALUES('abc');
INSERT INTO t4 VALUES('ABC');
INSERT INTO t4 VALUES('aBC');
}
do_execsql_test 4.1.1 {
SELECT * FROM t4 ORDER BY b COLLATE binary
} {ABC aBC abc}
do_execsql_test 4.1.2 {
SELECT * FROM t4 WHERE b='abc' ORDER BY b COLLATE binary
} {ABC aBC abc}
do_execsql_test 4.2.1 {
CREATE TABLE Records(typeID INTEGER, key TEXT COLLATE nocase, value TEXT);
CREATE INDEX RecordsIndex ON Records(typeID, key, value);
}
do_execsql_test 4.2.2 {
explain query plan
SELECT typeID, key, value FROM Records
WHERE typeID = 2 AND key = 'x'
ORDER BY key, value;
} {~/TEMP B-TREE/}
do_execsql_test 4.2.3 {
explain query plan
SELECT typeID, key, value FROM Records
WHERE typeID = 2 AND (key = 'x' COLLATE binary)
ORDER BY key, value;
} {~/TEMP B-TREE/}
do_execsql_test 4.2.4 {
explain query plan
SELECT typeID, key, value FROM Records
WHERE typeID = 2
ORDER BY key, value;
} {~/TEMP B-TREE/}
db collate hello [list string match]
do_execsql_test 4.3.1 {
CREATE TABLE t5(a INTEGER PRIMARY KEY, b COLLATE hello, c, d);
}
db close
sqlite3 db test.db
do_catchsql_test 4.3.2 {
SELECT a FROM t5 WHERE b='def' ORDER BY b;
} {1 {no such collation sequence: hello}}
finish_test
|