SQLite

Check-in [8e2b25f9b8]
Login

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8e2b25f9b8a7ed087d3cece74239814bee19429e
User & Date: dan 2016-10-12 14:48:00.157
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: 443913d582 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: 61f0526978 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: 8e2b25f9b8 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: 6624c4964b user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
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

# 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);








>








13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 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);
︙︙
116
117
118
119
120
121
122





























123

  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








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


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

  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