Debian Codegraph @

test/offset1.test

raw
1 # 2015-10-06
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 #
12 # This file implements test cases for the [b65cb2c8d91f6685841d7d1e13b6]
13 # bug: Correct handling of LIMIT and OFFSET on a UNION ALL query where
14 # the right-hand SELECT contains an ORDER BY in a subquery.
15 #
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19
20 ifcapable !compound {
21 finish_test
22 return
23 }
24
25 do_execsql_test offset1-1.1 {
26 CREATE TABLE t1(a,b);
27 INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
28 CREATE TABLE t2(x,y);
29 INSERT INTO t2 VALUES(8,'y'),(9,'z'),(6,'w'),(7,'x');
30 SELECT count(*) FROM t1, t2;
31 } {20}
32
33 do_execsql_test offset1-1.2.0 {
34 SELECT a, b FROM t1
35 UNION ALL
36 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
37 LIMIT 3 OFFSET 0;
38 } {1 a 2 b 3 c}
39 do_execsql_test offset1-1.2.1 {
40 SELECT a, b FROM t1
41 UNION ALL
42 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
43 LIMIT 3 OFFSET 1;
44 } {2 b 3 c 4 d}
45 do_execsql_test offset1-1.2.2 {
46 SELECT a, b FROM t1
47 UNION ALL
48 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
49 LIMIT 3 OFFSET 2;
50 } {3 c 4 d 5 e}
51 do_execsql_test offset1-1.2.3 {
52 SELECT a, b FROM t1
53 UNION ALL
54 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
55 LIMIT 3 OFFSET 3;
56 } {4 d 5 e 6 w}
57 do_execsql_test offset1-1.2.4 {
58 SELECT a, b FROM t1
59 UNION ALL
60 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
61 LIMIT 3 OFFSET 4;
62 } {5 e 6 w 7 x}
63 do_execsql_test offset1-1.2.5 {
64 SELECT a, b FROM t1
65 UNION ALL
66 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
67 LIMIT 3 OFFSET 5;
68 } {6 w 7 x 8 y}
69 do_execsql_test offset1-1.2.6 {
70 SELECT a, b FROM t1
71 UNION ALL
72 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
73 LIMIT 3 OFFSET 6;
74 } {7 x 8 y 9 z}
75 do_execsql_test offset1-1.2.7 {
76 SELECT a, b FROM t1
77 UNION ALL
78 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
79 LIMIT 3 OFFSET 7;
80 } {8 y 9 z}
81 do_execsql_test offset1-1.2.8 {
82 SELECT a, b FROM t1
83 UNION ALL
84 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
85 LIMIT 3 OFFSET 8;
86 } {9 z}
87 do_execsql_test offset1-1.2.9 {
88 SELECT a, b FROM t1
89 UNION ALL
90 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
91 LIMIT 3 OFFSET 9;
92 } {}
93
94 do_execsql_test offset1-1.3.0 {
95 SELECT * FROM t1 LIMIT 0;
96 } {}
97
98 do_execsql_test offset1-1.4.0 {
99 SELECT a, b FROM t1
100 UNION ALL
101 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
102 LIMIT 0 OFFSET 1;
103 } {}
104 do_execsql_test offset1-1.4.1 {
105 SELECT a, b FROM t1
106 UNION ALL
107 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
108 LIMIT 1 OFFSET 1;
109 } {2 b}
110 do_execsql_test offset1-1.4.2 {
111 SELECT a, b FROM t1
112 UNION ALL
113 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
114 LIMIT 2 OFFSET 1;
115 } {2 b 3 c}
116 do_execsql_test offset1-1.4.3 {
117 SELECT a, b FROM t1
118 UNION ALL
119 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
120 LIMIT 3 OFFSET 1;
121 } {2 b 3 c 4 d}
122 do_execsql_test offset1-1.4.4 {
123 SELECT a, b FROM t1
124 UNION ALL
125 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
126 LIMIT 4 OFFSET 1;
127 } {2 b 3 c 4 d 5 e}
128 do_execsql_test offset1-1.4.5 {
129 SELECT a, b FROM t1
130 UNION ALL
131 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
132 LIMIT 5 OFFSET 1;
133 } {2 b 3 c 4 d 5 e 6 w}
134 do_execsql_test offset1-1.4.6 {
135 SELECT a, b FROM t1
136 UNION ALL
137 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
138 LIMIT 6 OFFSET 1;
139 } {2 b 3 c 4 d 5 e 6 w 7 x}
140 do_execsql_test offset1-1.4.7 {
141 SELECT a, b FROM t1
142 UNION ALL
143 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
144 LIMIT 7 OFFSET 1;
145 } {2 b 3 c 4 d 5 e 6 w 7 x 8 y}
146 do_execsql_test offset1-1.4.8 {
147 SELECT a, b FROM t1
148 UNION ALL
149 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
150 LIMIT 8 OFFSET 1;
151 } {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z}
152 do_execsql_test offset1-1.4.9 {
153 SELECT a, b FROM t1
154 UNION ALL
155 SELECT * FROM (SELECT x, y FROM t2 ORDER BY y)
156 LIMIT 9 OFFSET 1;
157 } {2 b 3 c 4 d 5 e 6 w 7 x 8 y 9 z}
158
159 # 2022-08-04
160 # https://sqlite.org/forum/forumpost/6b5e9188f0657616
161 #
162 do_execsql_test offset1-2.0 {
163 CREATE TABLE employees (
164 id integer primary key,
165 name text,
166 city text,
167 department text,
168 salary integer
169 );
170 INSERT INTO employees VALUES
171 (11,'Diane','London','hr',70),
172 (12,'Bob','London','hr',78),
173 (21,'Emma','London','it',84),
174 (22,'Grace','Berlin','it',90),
175 (23,'Henry','London','it',104),
176 (24,'Irene','Berlin','it',104),
177 (25,'Frank','Berlin','it',120),
178 (31,'Cindy','Berlin','sales',96),
179 (32,'Dave','London','sales',96),
180 (33,'Alice','Berlin','sales',100);
181 CREATE VIEW v AS
182 SELECT * FROM (
183 SELECT * FROM employees
184 WHERE salary < 100
185 ORDER BY salary desc)
186 UNION ALL
187 SELECT * FROM (
188 SELECT * FROM employees
189 WHERE salary >= 100
190 ORDER BY salary asc);
191 } {}
192 do_execsql_test offset1-2.1 {
193 SELECT * FROM v ORDER BY +id;
194 } {
195 11 Diane London hr 70
196 12 Bob London hr 78
197 21 Emma London it 84
198 22 Grace Berlin it 90
199 23 Henry London it 104
200 24 Irene Berlin it 104
201 25 Frank Berlin it 120
202 31 Cindy Berlin sales 96
203 32 Dave London sales 96
204 33 Alice Berlin sales 100
205 }
206 do_execsql_test offset1-2.2 {
207 SELECT * FROM v LIMIT 5 OFFSET 2;
208 } {
209 22 Grace Berlin it 90
210 21 Emma London it 84
211 12 Bob London hr 78
212 11 Diane London hr 70
213 33 Alice Berlin sales 100
214 }
215 do_execsql_test offset1-2.3 {
216 SELECT * FROM v LIMIT 3 OFFSET 6;
217 } {
218 33 Alice Berlin sales 100
219 23 Henry London it 104
220 24 Irene Berlin it 104
221 }
222 do_execsql_test offset1-2.4 {
223 SELECT * FROM v LIMIT 3 OFFSET 1;
224 } {
225 32 Dave London sales 96
226 22 Grace Berlin it 90
227 21 Emma London it 84
228 }
229
230 finish_test