xref: /sqlite-3.40.0/test/offset1.test (revision c56fac74) 
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
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20ifcapable !compound {
21  finish_test
22  return
23}
24
25do_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
33do_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}
39do_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}
45do_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}
51do_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}
57do_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}
63do_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}
69do_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}
75do_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}
81do_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}
87do_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
94do_execsql_test offset1-1.3.0 {
95  SELECT * FROM t1 LIMIT 0;
96} {}
97
98do_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} {}
104do_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}
110do_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}
116do_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}
122do_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}
128do_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}
134do_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}
140do_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}
146do_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}
152do_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
160
161finish_test
162