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