SQLite

Check-in [16aed5d0c6]
Login

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 16aed5d0c63dcdc2054dbb8a4b6b992476640433bf81e19301e6db5a3fc82633
User & Date: dan 2020-02-12 11:57:35.462
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: 2def75693a 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: 16aed5d0c6 user: dan tags: trunk)
2020-02-10
19:24
Mark the sha1() extension function as SQLITE_DETERMINISTIC. (check-in: 7d8dcfb95c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
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