SQLite

Check-in [4b63136435]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix the LIMIT and OFFSET handling for UNION ALL queries that contain a subquery with ORDER BY on the right-hand side. Fix for ticket [b65cb2c8d91f668584].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4b631364354068af95a01630469cb6fbfe8b52fd
User & Date: drh 2015-10-06 17:27:18.737
References
2022-08-04
17:15
Fix a problem with the query optimizer for LIMIT/OFFSET queries when underlying query is a UNION ALL and both arms of the UNION ALL are subqueries with an ORDER BY clause. This bug was reported at forum post 6b5e9188f0657616. The problem was introduced in 2015 (SQLite version 3.9.0) by check-in [4b631364354068af]. See also ticket [b65cb2c8d91f6685]. (check-in: 6c806f64bb user: drh tags: trunk)
Context
2015-10-06
20:53
Optimizations for fts5 expressions that filter on column. More still to come. (check-in: bf1607ac15 user: dan tags: trunk)
17:27
Fix the LIMIT and OFFSET handling for UNION ALL queries that contain a subquery with ORDER BY on the right-hand side. Fix for ticket [b65cb2c8d91f668584]. (check-in: 4b63136435 user: drh tags: trunk)
01:44
Adjustments to sqlite3MemoryBarrier() when compiling with MSVC and/or WinCE. (check-in: 3168326ebf user: mistachkin tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
2269
2270
2271
2272
2273
2274
2275










2276
2277
2278
2279
2280
2281
2282
      }
      p->pPrior = 0;
      p->iLimit = pPrior->iLimit;
      p->iOffset = pPrior->iOffset;
      if( p->iLimit ){
        addr = sqlite3VdbeAddOp1(v, OP_IfNot, p->iLimit); VdbeCoverage(v);
        VdbeComment((v, "Jump ahead if LIMIT reached"));










      }
      explainSetInteger(iSub2, pParse->iNextSelectId);
      rc = sqlite3Select(pParse, p, &dest);
      testcase( rc!=SQLITE_OK );
      pDelete = p->pPrior;
      p->pPrior = pPrior;
      p->nSelectRow += pPrior->nSelectRow;







>
>
>
>
>
>
>
>
>
>







2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
      }
      p->pPrior = 0;
      p->iLimit = pPrior->iLimit;
      p->iOffset = pPrior->iOffset;
      if( p->iLimit ){
        addr = sqlite3VdbeAddOp1(v, OP_IfNot, p->iLimit); VdbeCoverage(v);
        VdbeComment((v, "Jump ahead if LIMIT reached"));
        if( p->iOffset ){
          int addr2;
          sqlite3VdbeAddOp3(v, OP_Add, p->iLimit, p->iOffset, p->iOffset+1);
          addr2 = sqlite3VdbeAddOp1(v, OP_IfPos, p->iOffset); VdbeCoverage(v);
          sqlite3VdbeAddOp2(v, OP_SCopy, p->iLimit, p->iOffset+1);
          sqlite3VdbeJumpHere(v, addr2);
          addr2 = sqlite3VdbeAddOp1(v, OP_IfPos, p->iLimit); VdbeCoverage(v);
          sqlite3VdbeAddOp2(v, OP_Integer, -1, p->iOffset+1);
          sqlite3VdbeJumpHere(v, addr2);
        }
      }
      explainSetInteger(iSub2, pParse->iNextSelectId);
      rc = sqlite3Select(pParse, p, &dest);
      testcase( rc!=SQLITE_OK );
      pDelete = p->pPrior;
      p->pPrior = pPrior;
      p->nSelectRow += pPrior->nSelectRow;
Added test/offset1.test.


































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
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
# 2015-10-06
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# 
# This file implements test cases for the [b65cb2c8d91f6685841d7d1e13b6]
# bug:  Correct handling of LIMIT and OFFSET on a UNION ALL query where
# the right-hand SELECT contains an ORDER BY in a subquery.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !compound {
  finish_test
  return
}

do_execsql_test offset1-1.1 {
  CREATE TABLE t1(a,b);
  INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
  CREATE TABLE t2(x,y);
  INSERT INTO t2 VALUES(8,'y'),(9,'z'),(6,'w'),(7,'x');
  SELECT count(*) FROM t1, t2;
} {20}

do_execsql_test offset1-1.2.0 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 0;
} {1 a 2 b 3 c}
do_execsql_test offset1-1.2.1 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 1;
} {2 b 3 c 4 d}
do_execsql_test offset1-1.2.2 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 2;
} {3 c 4 d 5 e}
do_execsql_test offset1-1.2.3 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 3;
} {4 d 5 e 6 w}
do_execsql_test offset1-1.2.4 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 4;
} {5 e 6 w 7 x}
do_execsql_test offset1-1.2.5 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 5;
} {6 w 7 x 8 y}
do_execsql_test offset1-1.2.6 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 6;
} {7 x 8 y 9 z}
do_execsql_test offset1-1.2.7 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 7;
} {8 y 9 z}
do_execsql_test offset1-1.2.8 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 8;
} {9 z}
do_execsql_test offset1-1.2.9 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 9;
} {}

do_execsql_test offset1-1.3.0 {
  SELECT * FROM t1 LIMIT 0;
} {}

do_execsql_test offset1-1.4.0 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 0 OFFSET 1;
} {}
do_execsql_test offset1-1.4.1 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 1 OFFSET 1;
} {2 b}
do_execsql_test offset1-1.4.2 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 2 OFFSET 1;
} {2 b 3 c}
do_execsql_test offset1-1.4.3 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 3 OFFSET 1;
} {2 b 3 c 4 d}
do_execsql_test offset1-1.4.4 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 4 OFFSET 1;
} {2 b 3 c 4 d 5 e}
do_execsql_test offset1-1.4.5 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 5 OFFSET 1;
} {2 b 3 c 4 d 5 e 6 w}
do_execsql_test offset1-1.4.6 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 6 OFFSET 1;
} {2 b 3 c 4 d 5 e 6 w 7 x}
do_execsql_test offset1-1.4.7 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 7 OFFSET 1;
} {2 b 3 c 4 d 5 e 6 w 7 x 8 y}
do_execsql_test offset1-1.4.8 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 8 OFFSET 1;
} {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z}
do_execsql_test offset1-1.4.9 {
  SELECT a, b FROM t1
  UNION ALL
  SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
  LIMIT 9 OFFSET 1;
} {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z}



finish_test