gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/offset1.test (about) 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 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 202 finish_test