1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# 2001 November 6 5# 6# The author disclaims copyright to this source code. In place of 7# a legal notice, here is a blessing: 8# 9# May you do good and not evil. 10# May you find forgiveness for yourself and forgive others. 11# May you share freely, never taking more than you give. 12# 13#*********************************************************************** 14# This file implements regression tests for SQLite library. The 15# focus of this file is testing the LIMIT ... OFFSET ... clause 16# of SELECT statements. 17# 18# $Id: limit.test,v 1.11.2.1 2004/07/19 23:33:04 drh Exp $ 19 20set testdir [file dirname $argv0] 21source $testdir/tester.tcl 22 23# Build some test data 24# 25set fd [open data1.txt w] 26for {set i 1} {$i<=32} {incr i} { 27 for {set j 0} {pow(2,$j)<$i} {incr j} {} 28 puts $fd "[expr {32-$i}]\t[expr {10-$j}]" 29} 30close $fd 31execsql { 32 CREATE TABLE t1(x int, y int); 33 COPY t1 FROM 'data1.txt' 34} 35file delete data1.txt 36 37do_test limit-1.0 { 38 execsql {SELECT count(*) FROM t1} 39} {32} 40do_test limit-1.1 { 41 execsql {SELECT count(*) FROM t1 LIMIT 5} 42} {32} 43do_test limit-1.2.1 { 44 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5} 45} {0 1 2 3 4} 46do_test limit-1.2.2 { 47 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 2} 48} {2 3 4 5 6} 49do_test limit-1.2.3 { 50 execsql {SELECT x FROM t1 ORDER BY x LIMIT 2, 5} 51} {2 3 4 5 6} 52do_test limit-1.3 { 53 execsql {SELECT x FROM t1 ORDER BY x LIMIT 5 OFFSET 5} 54} {5 6 7 8 9} 55do_test limit-1.4.1 { 56 execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 30} 57} {30 31} 58do_test limit-1.4.2 { 59 execsql {SELECT x FROM t1 ORDER BY x LIMIT 30, 50} 60} {30 31} 61do_test limit-1.5 { 62 execsql {SELECT x FROM t1 ORDER BY x LIMIT 50 OFFSET 50} 63} {} 64do_test limit-1.6 { 65 execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5} 66} {0 5 0 5 0 5 1 5 0 5 2 5 0 5 3 5 0 5 4 5} 67do_test limit-1.7 { 68 execsql {SELECT * FROM t1 AS a, t1 AS b ORDER BY a.x, b.x LIMIT 5 OFFSET 32} 69} {1 5 0 5 1 5 1 5 1 5 2 5 1 5 3 5 1 5 4 5} 70 71do_test limit-2.1 { 72 execsql { 73 CREATE VIEW v1 AS SELECT * FROM t1 LIMIT 2; 74 SELECT count(*) FROM (SELECT * FROM v1); 75 } 76} 2 77do_test limit-2.2 { 78 execsql { 79 CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 2; 80 SELECT count(*) FROM t2; 81 } 82} 2 83do_test limit-2.3 { 84 execsql { 85 SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2); 86 } 87} 2 88 89do_test limit-3.1 { 90 execsql { 91 SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10) 92 ORDER BY z LIMIT 5; 93 } 94} {50 51 52 53 54} 95 96do_test limit-4.1 { 97 execsql { 98 BEGIN; 99 CREATE TABLE t3(x); 100 INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1; 101 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 102 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 103 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 104 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 105 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 106 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 107 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 108 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 109 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 110 INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3; 111 END; 112 SELECT count(*) FROM t3; 113 } 114} {10240} 115do_test limit-4.2 { 116 execsql { 117 SELECT x FROM t3 LIMIT 2 OFFSET 10000 118 } 119} {10001 10002} 120do_test limit-4.3 { 121 execsql { 122 CREATE TABLE t4 AS SELECT x, 123 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 124 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 125 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 126 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x || 127 'abcdefghijklmnopqrstuvwyxz ABCDEFGHIJKLMNOPQRSTUVWYXZ' || x AS y 128 FROM t3 LIMIT 1000; 129 SELECT x FROM t4 ORDER BY y DESC LIMIT 1 OFFSET 999; 130 } 131} {1000} 132 133do_test limit-5.1 { 134 execsql { 135 CREATE TABLE t5(x,y); 136 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15 137 ORDER BY x LIMIT 2; 138 SELECT * FROM t5 ORDER BY x; 139 } 140} {5 15 6 16} 141do_test limit-5.2 { 142 execsql { 143 DELETE FROM t5; 144 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x BETWEEN 10 AND 15 145 ORDER BY x DESC LIMIT 2; 146 SELECT * FROM t5 ORDER BY x; 147 } 148} {9 19 10 20} 149do_test limit-5.3 { 150 execsql { 151 DELETE FROM t5; 152 INSERT INTO t5 SELECT x-y, x+y FROM t1 WHERE x ORDER BY x DESC LIMIT 31; 153 SELECT * FROM t5 ORDER BY x LIMIT 2; 154 } 155} {-4 6 -3 7} 156do_test limit-5.4 { 157 execsql { 158 SELECT * FROM t5 ORDER BY x DESC, y DESC LIMIT 2; 159 } 160} {21 41 21 39} 161do_test limit-5.5 { 162 execsql { 163 DELETE FROM t5; 164 INSERT INTO t5 SELECT a.x*100+b.x, a.y*100+b.y FROM t1 AS a, t1 AS b 165 ORDER BY 1, 2 LIMIT 1000; 166 SELECT count(*), sum(x), sum(y), min(x), max(x), min(y), max(y) FROM t5; 167 } 168} {1000 1528204 593161 0 3107 505 1005} 169 170# There is some contraversy about whether LIMIT 0 should be the same as 171# no limit at all or if LIMIT 0 should result in zero output rows. 172# 173do_test limit-6.1 { 174 execsql { 175 BEGIN; 176 CREATE TABLE t6(a); 177 INSERT INTO t6 VALUES(1); 178 INSERT INTO t6 VALUES(2); 179 INSERT INTO t6 SELECT a+2 FROM t6; 180 COMMIT; 181 SELECT * FROM t6; 182 } 183} {1 2 3 4} 184do_test limit-6.2 { 185 execsql { 186 SELECT * FROM t6 LIMIT -1 OFFSET -1; 187 } 188} {1 2 3 4} 189do_test limit-6.3 { 190 execsql { 191 SELECT * FROM t6 LIMIT 2 OFFSET -123; 192 } 193} {1 2} 194do_test limit-6.4 { 195 execsql { 196 SELECT * FROM t6 LIMIT -432 OFFSET 2; 197 } 198} {3 4} 199do_test limit-6.5 { 200 execsql { 201 SELECT * FROM t6 LIMIT -1 202 } 203} {1 2 3 4} 204do_test limit-6.6 { 205 execsql { 206 SELECT * FROM t6 LIMIT -1 OFFSET 1 207 } 208} {2 3 4} 209do_test limit-6.7 { 210 execsql { 211 SELECT * FROM t6 LIMIT 0 212 } 213} {} 214do_test limit-6.8 { 215 execsql { 216 SELECT * FROM t6 LIMIT 0 OFFSET 1 217 } 218} {} 219 220# Make sure LIMIT works well with compound SELECT statements. 221# Ticket #393 222# 223do_test limit-7.1.1 { 224 catchsql { 225 SELECT x FROM t2 LIMIT 5 UNION ALL SELECT a FROM t6; 226 } 227} {1 {LIMIT clause should come after UNION ALL not before}} 228do_test limit-7.1.2 { 229 catchsql { 230 SELECT x FROM t2 LIMIT 5 UNION SELECT a FROM t6; 231 } 232} {1 {LIMIT clause should come after UNION not before}} 233do_test limit-7.1.3 { 234 catchsql { 235 SELECT x FROM t2 LIMIT 5 EXCEPT SELECT a FROM t6 LIMIT 3; 236 } 237} {1 {LIMIT clause should come after EXCEPT not before}} 238do_test limit-7.1.4 { 239 catchsql { 240 SELECT x FROM t2 LIMIT 0,5 INTERSECT SELECT a FROM t6; 241 } 242} {1 {LIMIT clause should come after INTERSECT not before}} 243do_test limit-7.2 { 244 execsql { 245 SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 5; 246 } 247} {31 30 1 2 3} 248do_test limit-7.3 { 249 execsql { 250 SELECT x FROM t2 UNION ALL SELECT a FROM t6 LIMIT 3 OFFSET 1; 251 } 252} {30 1 2} 253do_test limit-7.4 { 254 execsql { 255 SELECT x FROM t2 UNION ALL SELECT a FROM t6 ORDER BY 1 LIMIT 3 OFFSET 1; 256 } 257} {2 3 4} 258do_test limit-7.5 { 259 execsql { 260 SELECT x FROM t2 UNION SELECT x+2 FROM t2 LIMIT 2 OFFSET 1; 261 } 262} {31 32} 263do_test limit-7.6 { 264 execsql { 265 SELECT x FROM t2 UNION SELECT x+2 FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 1; 266 } 267} {32 31} 268do_test limit-7.7 { 269 execsql { 270 SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 LIMIT 2; 271 } 272} {11 12} 273do_test limit-7.8 { 274 execsql { 275 SELECT a+9 FROM t6 EXCEPT SELECT y FROM t2 ORDER BY 1 DESC LIMIT 2; 276 } 277} {13 12} 278do_test limit-7.9 { 279 execsql { 280 SELECT a+26 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1; 281 } 282} {30} 283do_test limit-7.10 { 284 execsql { 285 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1; 286 } 287} {30} 288do_test limit-7.11 { 289 execsql { 290 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 LIMIT 1 OFFSET 1; 291 } 292} {31} 293do_test limit-7.12 { 294 execsql { 295 SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 296 ORDER BY 1 DESC LIMIT 1 OFFSET 1; 297 } 298} {30} 299 300# Tests for limit in conjunction with distinct. The distinct should 301# occur before both the limit and the offset. Ticket #749. 302# 303do_test limit-8.1 { 304 execsql { 305 SELECT DISTINCT round(x/100) FROM t3 LIMIT 5; 306 } 307} {0 1 2 3 4} 308do_test limit-8.2 { 309 execsql { 310 SELECT DISTINCT round(x/100) FROM t3 LIMIT 5 OFFSET 5; 311 } 312} {5 6 7 8 9} 313do_test limit-8.3 { 314 execsql { 315 SELECT DISTINCT round(x/100) FROM t3 LIMIT 5 OFFSET 25; 316 } 317} {25 26 27 28 29} 318 319 320finish_test 321