Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Improved handling of constants and especially constant functions in the ORDER BY clause of a query. Do not optimize out "ORDER BY random()". Fix for ticket [65bdeb9739605cc2296]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
dca1945aeb3fb005263f9be00ee8e72b |
| User & Date: | drh 2014-02-26 02:26:09 |
Context
|
2014-02-26
| ||
| 13:53 | In the command-line shell for CSV import, if the lines are \r\n terminated and the last field is blank, make sure an empty string and not a "\r" string is imported. (check-in: 9c2e7612 user: drh tags: trunk) | |
| 02:26 | Improved handling of constants and especially constant functions in the ORDER BY clause of a query. Do not optimize out "ORDER BY random()". Fix for ticket [65bdeb9739605cc2296]. (check-in: dca1945a user: drh tags: trunk) | |
|
2014-02-25
| ||
| 21:55 | Do not allow temporary registers to be in use across an OP_Yield within a co-routine. Fix for ticket [8c63ff0eca81a9132d8]. (check-in: 97a8c973 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
4897 4898 4899 4900 4901 4902 4903 4904 4905 |
} /* end-if not one-row */
/* Mark off any other ORDER BY terms that reference pLoop */
if( isOrderDistinct ){
orderDistinctMask |= pLoop->maskSelf;
for(i=0; i<nOrderBy; i++){
Expr *p;
if( MASKBIT(i) & obSat ) continue;
p = pOrderBy->a[i].pExpr;
| > | > > | 4897 4898 4899 4900 4901 4902 4903 4904 4905 4906 4907 4908 4909 4910 4911 4912 4913 4914 4915 4916 |
} /* end-if not one-row */
/* Mark off any other ORDER BY terms that reference pLoop */
if( isOrderDistinct ){
orderDistinctMask |= pLoop->maskSelf;
for(i=0; i<nOrderBy; i++){
Expr *p;
Bitmask mTerm;
if( MASKBIT(i) & obSat ) continue;
p = pOrderBy->a[i].pExpr;
mTerm = exprTableUsage(&pWInfo->sMaskSet,p);
if( mTerm==0 && !sqlite3ExprIsConstant(p) ) continue;
if( (mTerm&~orderDistinctMask)==0 ){
obSat |= MASKBIT(i);
}
}
}
} /* End the loop over all WhereLoops from outer-most down to inner-most */
if( obSat==obDone ) return 1;
if( !isOrderDistinct ) return 0;
|
| ︙ | ︙ | |||
5522 5523 5524 5525 5526 5527 5528 |
** and work forward so that the added virtual terms are never processed.
*/
exprAnalyzeAll(pTabList, &pWInfo->sWC);
if( db->mallocFailed ){
goto whereBeginError;
}
| < < < < < < < < < < < < < < < < | 5525 5526 5527 5528 5529 5530 5531 5532 5533 5534 5535 5536 5537 5538 |
** and work forward so that the added virtual terms are never processed.
*/
exprAnalyzeAll(pTabList, &pWInfo->sWC);
if( db->mallocFailed ){
goto whereBeginError;
}
if( wctrlFlags & WHERE_WANT_DISTINCT ){
if( isDistinctRedundant(pParse, pTabList, &pWInfo->sWC, pResultSet) ){
/* The DISTINCT marking is pointless. Ignore it. */
pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
}else if( pOrderBy==0 ){
/* Try to ORDER BY the result set to make distinct processing easier */
pWInfo->wctrlFlags |= WHERE_DISTINCTBY;
|
| ︙ | ︙ |
Changes to test/where2.test.
| ︙ | ︙ | |||
116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
}
} {85 6 7396 7402 sort t1 i1xy}
do_test where2-2.3 {
queryplan {
SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random();
}
} {85 6 7396 7402 nosort t1 *}
# Efficient handling of forward and reverse table scans.
#
do_test where2-3.1 {
queryplan {
SELECT * FROM t1 ORDER BY rowid LIMIT 2
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 116 117 118 119 120 121 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 |
}
} {85 6 7396 7402 sort t1 i1xy}
do_test where2-2.3 {
queryplan {
SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random();
}
} {85 6 7396 7402 nosort t1 *}
# Ticket [65bdeb9739605cc22966f49208452996ff29a640] 2014-02-26
# Make sure "ORDER BY random" does not gets optimized out.
#
do_test where2-2.4 {
db eval {
CREATE TABLE x1(a INTEGER PRIMARY KEY, b DEFAULT 1);
WITH RECURSIVE
cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<50)
INSERT INTO x1 SELECT x, 1 FROM cnt;
CREATE TABLE x2(x INTEGER PRIMARY KEY);
INSERT INTO x2 VALUES(1);
}
set sql {SELECT * FROM x1, x2 WHERE x=1 ORDER BY random()}
set out1 [db eval $sql]
set out2 [db eval $sql]
set out3 [db eval $sql]
expr {$out1!=$out2 && $out2!=$out3}
} {1}
do_execsql_test where2-2.5 {
-- random() is not optimized out
EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY random();
} {/ random/}
do_execsql_test where2-2.5b {
-- random() is not optimized out
EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY random();
} {/ SorterOpen /}
do_execsql_test where2-2.6 {
-- other constant functions are optimized out
EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY abs(5);
} {~/ abs/}
do_execsql_test where2-2.6b {
-- other constant functions are optimized out
EXPLAIN SELECT * FROM x1, x2 WHERE x=1 ORDER BY abs(5);
} {~/ SorterOpen /}
# Efficient handling of forward and reverse table scans.
#
do_test where2-3.1 {
queryplan {
SELECT * FROM t1 ORDER BY rowid LIMIT 2
|
| ︙ | ︙ |