Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem affecting queries that match the pattern (... WHERE ipk IN (....) ORDER BY ? LIMIT ?). Fix for [96c1454c]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
8e2b25f9b8a7ed087d3cece74239814b |
| User & Date: | dan 2016-10-12 14:48:00 |
References
|
2016-10-12
| ||
| 18:55 | Avoid reading the -1-th element of an array in the query planner. Fix to a bug introduced by check-in [8e2b25f9b8a7] from earlier today. Curiously, the problem only appeared on 32-bit systems. check-in: 443913d5 user: drh tags: trunk | |
Context
|
2016-10-12
| ||
| 15:15 | New testcase() macros to ensure coverage of the ORDER BY LIMIT optimization code in where.c. check-in: 61f05269 user: drh tags: trunk | |
| 14:48 | Fix a problem affecting queries that match the pattern (... WHERE ipk IN (....) ORDER BY ? LIMIT ?). Fix for [96c1454c]. check-in: 8e2b25f9 user: dan tags: trunk | |
|
2016-10-10
| ||
| 14:48 | Remove a stray line from session4.test causing a memory leak. No changes to SQLite code. check-in: 6624c496 user: dan tags: trunk | |
Changes
Changes to src/where.c.
4119 4120 4121 4122 4123 4124 4125 4126 4127 4128 4129 4130 4131 4132 4133 4134 |
if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){
pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
}
}else{
pWInfo->nOBSat = pFrom->isOrdered;
pWInfo->revMask = pFrom->revLoop;
if( pWInfo->nOBSat<=0 ){
pWInfo->nOBSat = 0;
if( nLoop>0 && (pFrom->aLoop[nLoop-1]->wsFlags & WHERE_ONEROW)==0 ){
Bitmask m = 0;
int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
if( rc==pWInfo->pOrderBy->nExpr ){
pWInfo->bOrderedInnerLoop = 1;
pWInfo->revMask = m;
}
|
> | > > |
4119 4120 4121 4122 4123 4124 4125 4126 4127 4128 4129 4130 4131 4132 4133 4134 4135 4136 4137 |
if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){
pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
}
}else{
pWInfo->nOBSat = pFrom->isOrdered;
pWInfo->revMask = pFrom->revLoop;
if( pWInfo->nOBSat<=0 ){
u32 wsFlags = pFrom->aLoop[nLoop-1]->wsFlags;
pWInfo->nOBSat = 0;
if( nLoop>0 && (wsFlags & WHERE_ONEROW)==0
&& (wsFlags & (WHERE_IPK|WHERE_COLUMN_IN))!=(WHERE_IPK|WHERE_COLUMN_IN)
){
Bitmask m = 0;
int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
if( rc==pWInfo->pOrderBy->nExpr ){
pWInfo->bOrderedInnerLoop = 1;
pWInfo->revMask = m;
}
|
Changes to test/limit2.test.
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
116
117
118
119
120
121
122
123
|
# and in particular, the optimizations in the inner loop that cause an
# early exit of the inner loop when the LIMIT is reached and the inner
# loop is emitting rows in ORDER BY order.
set testdir [file dirname $argv0]
source $testdir/tester.tcl
do_execsql_test limit2-100 {
CREATE TABLE t1(a,b);
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
INSERT INTO t1(a,b) SELECT 1, (x*17)%1000 + 1000 FROM c;
INSERT INTO t1(a,b) VALUES(2,2),(3,1006),(4,4),(5,9999);
CREATE INDEX t1ab ON t1(a,b);
................................................................................
CREATE TABLE t400(a,b);
CREATE INDEX t400_ab ON t400(a,b);
INSERT INTO t400(a,b) VALUES(1,90),(1,40),(2,80),(2,30),(3,70),(3,20);
SELECT *,'x' FROM t400 WHERE a IN (1,2,3) ORDER BY b DESC LIMIT 3;
SELECT *,'y' FROM t400 WHERE a IN (1,2,3) ORDER BY +b DESC LIMIT 3;
} {1 90 x 2 80 x 3 70 x 1 90 y 2 80 y 3 70 y}
finish_test
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
...
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
|
# and in particular, the optimizations in the inner loop that cause an # early exit of the inner loop when the LIMIT is reached and the inner # loop is emitting rows in ORDER BY order. set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix limit2 do_execsql_test limit2-100 { CREATE TABLE t1(a,b); WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000) INSERT INTO t1(a,b) SELECT 1, (x*17)%1000 + 1000 FROM c; INSERT INTO t1(a,b) VALUES(2,2),(3,1006),(4,4),(5,9999); CREATE INDEX t1ab ON t1(a,b); ................................................................................ CREATE TABLE t400(a,b); CREATE INDEX t400_ab ON t400(a,b); INSERT INTO t400(a,b) VALUES(1,90),(1,40),(2,80),(2,30),(3,70),(3,20); SELECT *,'x' FROM t400 WHERE a IN (1,2,3) ORDER BY b DESC LIMIT 3; SELECT *,'y' FROM t400 WHERE a IN (1,2,3) ORDER BY +b DESC LIMIT 3; } {1 90 x 2 80 x 3 70 x 1 90 y 2 80 y 3 70 y} do_execsql_test 500 { CREATE TABLE t500(i INTEGER PRIMARY KEY, j); INSERT INTO t500 VALUES(1, 1); INSERT INTO t500 VALUES(2, 2); INSERT INTO t500 VALUES(3, 3); INSERT INTO t500 VALUES(4, 0); INSERT INTO t500 VALUES(5, 5); SELECT j FROM t500 WHERE i IN (1,2,3,4,5) ORDER BY j DESC LIMIT 3; } {5 3 2} do_execsql_test 501 { CREATE TABLE t501(i INTEGER PRIMARY KEY, j); INSERT INTO t501 VALUES(1, 5); INSERT INTO t501 VALUES(2, 4); INSERT INTO t501 VALUES(3, 3); INSERT INTO t501 VALUES(4, 6); INSERT INTO t501 VALUES(5, 1); SELECT j FROM t501 WHERE i IN (1,2,3,4,5) ORDER BY j LIMIT 3; } {1 3 4} do_execsql_test 502 { CREATE TABLE t502(i INT PRIMARY KEY, j); INSERT INTO t502 VALUES(1, 5); INSERT INTO t502 VALUES(2, 4); INSERT INTO t502 VALUES(3, 3); INSERT INTO t502 VALUES(4, 6); INSERT INTO t502 VALUES(5, 1); SELECT j FROM t502 WHERE i IN (1,2,3,4,5) ORDER BY j LIMIT 3; } {1 3 4} finish_test |