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# 2022-08-04 160# https://sqlite.org/forum/forumpost/6b5e9188f0657616 161# 162do_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} {} 192do_execsql_test offset1-2.1 { 193 SELECT * FROM v LIMIT 5 OFFSET 2; 194} { 195 22 Grace Berlin it 90 196 21 Emma London it 84 197 12 Bob London hr 78 198 11 Diane London hr 70 199 33 Alice Berlin sales 100 200} 201 202finish_test 203