1# 2008 October 6 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# This file implements regression tests for SQLite library. The 12# focus of this file is testing the LIMIT ... OFFSET ... clause 13# of UPDATE and DELETE statements. 14# 15# $Id: wherelimit.test,v 1.2 2008/10/10 18:25:46 shane Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20proc create_test_data {size} { 21 # Build some test data 22 # 23 execsql { 24 DROP TABLE IF EXISTS t1; 25 CREATE TABLE t1(x int, y int); 26 BEGIN; 27 } 28 for {set i 1} {$i<=$size} {incr i} { 29 for {set j 1} {$j<=$size} {incr j} { 30 execsql "INSERT INTO t1 VALUES([expr {$i}],[expr {$j}])" 31 } 32 } 33 execsql { 34 COMMIT; 35 } 36 return {} 37} 38 39ifcapable {update_delete_limit} { 40 41 execsql { CREATE TABLE t1(x, y) } 42 43 # check syntax error support 44 do_test wherelimit-0.1 { 45 catchsql {DELETE FROM t1 ORDER BY x} 46 } {1 {ORDER BY without LIMIT on DELETE}} 47 do_test wherelimit-0.2 { 48 catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x} 49 } {1 {ORDER BY without LIMIT on DELETE}} 50 do_test wherelimit-0.3 { 51 catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x} 52 } {1 {ORDER BY without LIMIT on UPDATE}} 53 54 execsql { DROP TABLE t1 } 55 56 # no AS on table sources 57 do_test wherelimit-0.4 { 58 catchsql {DELETE FROM t1 AS a WHERE x=1} 59 } {1 {near "AS": syntax error}} 60 do_test wherelimit-0.5 { 61 catchsql {UPDATE t1 AS a SET y=1 WHERE x=1} 62 } {1 {near "AS": syntax error}} 63 64 # OFFSET w/o LIMIT 65 do_test wherelimit-0.6 { 66 catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2} 67 } {1 {near "OFFSET": syntax error}} 68 do_test wherelimit-0.7 { 69 catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2} 70 } {1 {near "OFFSET": syntax error}} 71 72 73 # check deletes w/o where clauses but with limit/offsets 74 create_test_data 5 75 do_test wherelimit-1.0 { 76 execsql {SELECT count(*) FROM t1} 77 } {25} 78 do_test wherelimit-1.1 { 79 execsql {DELETE FROM t1} 80 execsql {SELECT count(*) FROM t1} 81 } {0} 82 create_test_data 5 83 do_test wherelimit-1.2 { 84 execsql {DELETE FROM t1 LIMIT 5} 85 execsql {SELECT count(*) FROM t1} 86 } {20} 87 do_test wherelimit-1.3 { 88 # limit 5 89 execsql {DELETE FROM t1 ORDER BY x LIMIT 5} 90 execsql {SELECT count(*) FROM t1} 91 } {15} 92 do_test wherelimit-1.4 { 93 # limit 5, offset 2 94 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 2} 95 execsql {SELECT count(*) FROM t1} 96 } {10} 97 do_test wherelimit-1.5 { 98 # limit 5, offset -2 99 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2} 100 execsql {SELECT count(*) FROM t1} 101 } {5} 102 do_test wherelimit-1.6 { 103 # limit -5 (no limit), offset 2 104 execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5} 105 execsql {SELECT count(*) FROM t1} 106 } {2} 107 do_test wherelimit-1.7 { 108 # limit 5, offset -2 (no offset) 109 execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5} 110 execsql {SELECT count(*) FROM t1} 111 } {0} 112 create_test_data 5 113 do_test wherelimit-1.8 { 114 # limit -5 (no limit), offset -2 (no offset) 115 execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5} 116 execsql {SELECT count(*) FROM t1} 117 } {0} 118 create_test_data 3 119 do_test wherelimit-1.9 { 120 # limit 5, offset 2 121 execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5} 122 execsql {SELECT count(*) FROM t1} 123 } {4} 124 do_test wherelimit-1.10 { 125 # limit 5, offset 5 126 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5} 127 execsql {SELECT count(*) FROM t1} 128 } {4} 129 do_test wherelimit-1.11 { 130 # limit 50, offset 30 131 execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30} 132 execsql {SELECT count(*) FROM t1} 133 } {4} 134 do_test wherelimit-1.12 { 135 # limit 50, offset 30 136 execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50} 137 execsql {SELECT count(*) FROM t1} 138 } {4} 139 do_test wherelimit-1.13 { 140 execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50} 141 execsql {SELECT count(*) FROM t1} 142 } {4} 143 144 145 create_test_data 6 146 do_test wherelimit-2.0 { 147 execsql {SELECT count(*) FROM t1} 148 } {36} 149 do_test wherelimit-2.1 { 150 execsql {DELETE FROM t1 WHERE x=1} 151 execsql {SELECT count(*) FROM t1} 152 } {30} 153 create_test_data 6 154 do_test wherelimit-2.2 { 155 execsql {DELETE FROM t1 WHERE x=1 LIMIT 5} 156 execsql {SELECT count(*) FROM t1} 157 } {31} 158 do_test wherelimit-2.3 { 159 # limit 5 160 execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5} 161 execsql {SELECT count(*) FROM t1} 162 } {30} 163 do_test wherelimit-2.4 { 164 # limit 5, offset 2 165 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2} 166 execsql {SELECT count(*) FROM t1} 167 } {26} 168 do_test wherelimit-2.5 { 169 # limit 5, offset -2 170 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2} 171 execsql {SELECT count(*) FROM t1} 172 } {24} 173 do_test wherelimit-2.6 { 174 # limit -5 (no limit), offset 2 175 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5} 176 execsql {SELECT count(*) FROM t1} 177 } {20} 178 do_test wherelimit-2.7 { 179 # limit 5, offset -2 (no offset) 180 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5} 181 execsql {SELECT count(*) FROM t1} 182 } {18} 183 do_test wherelimit-2.8 { 184 # limit -5 (no limit), offset -2 (no offset) 185 execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5} 186 execsql {SELECT count(*) FROM t1} 187 } {12} 188 create_test_data 6 189 do_test wherelimit-2.9 { 190 # limit 5, offset 2 191 execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5} 192 execsql {SELECT count(*) FROM t1} 193 } {32} 194 do_test wherelimit-2.10 { 195 # limit 5, offset 5 196 execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5} 197 execsql {SELECT count(*) FROM t1} 198 } {31} 199 do_test wherelimit-2.11 { 200 # limit 50, offset 30 201 execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30} 202 execsql {SELECT count(*) FROM t1} 203 } {31} 204 do_test wherelimit-2.12 { 205 # limit 50, offset 30 206 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50} 207 execsql {SELECT count(*) FROM t1} 208 } {31} 209 do_test wherelimit-2.13 { 210 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} 211 execsql {SELECT count(*) FROM t1} 212 } {31} 213 214 215 create_test_data 6 216 do_test wherelimit-3.0 { 217 execsql {SELECT count(*) FROM t1} 218 } {36} 219 do_test wherelimit-3.1 { 220 execsql {UPDATE t1 SET y=1 WHERE x=1} 221 execsql {SELECT count(*) FROM t1 WHERE y=1} 222 } {11} 223 create_test_data 6 224 do_test wherelimit-3.2 { 225 execsql {UPDATE t1 SET y=1 WHERE x=1 LIMIT 5} 226 execsql {SELECT count(*) FROM t1 WHERE y=1} 227 } {10} 228 do_test wherelimit-3.3 { 229 # limit 5 230 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5} 231 execsql {SELECT count(*) FROM t1 WHERE y=2} 232 } {9} 233 create_test_data 6 234 do_test wherelimit-3.4 { 235 # limit 5, offset 2 236 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2} 237 execsql {SELECT count(*) FROM t1 WHERE y=1} 238 } {6} 239 do_test wherelimit-3.5 { 240 # limit 5, offset -2 241 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2} 242 execsql {SELECT count(*) FROM t1 WHERE y=1} 243 } {5} 244 do_test wherelimit-3.6 { 245 # limit -5 (no limit), offset 2 246 execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT 2, -5} 247 execsql {SELECT count(*) FROM t1 WHERE y=3} 248 } {8} 249 do_test wherelimit-3.7 { 250 # limit 5, offset -2 (no offset) 251 execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5} 252 execsql {SELECT count(*) FROM t1 WHERE y=3} 253 } {10} 254 255 do_test wherelimit-3.8 { 256 # limit -5 (no limit), offset -2 (no offset) 257 execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5} 258 execsql {SELECT count(*) FROM t1 WHERE y=4} 259 } {9} 260 create_test_data 6 261 do_test wherelimit-3.9 { 262 # limit 5, offset 2 263 execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5} 264 execsql {SELECT count(*) FROM t1 WHERE y=4} 265 } {9} 266 do_test wherelimit-3.10 { 267 # limit 5, offset 5 268 execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5} 269 execsql {SELECT count(*) FROM t1 WHERE y=1} 270 } {6} 271 do_test wherelimit-3.11 { 272 # limit 50, offset 30 273 execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30} 274 execsql {SELECT count(*) FROM t1 WHERE y=1} 275 } {6} 276 do_test wherelimit-3.12 { 277 # limit 50, offset 30 278 execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50} 279 execsql {SELECT count(*) FROM t1 WHERE y=1} 280 } {6} 281 do_test wherelimit-3.13 { 282 execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} 283 execsql {SELECT count(*) FROM t1 WHERE y=1} 284 } {6} 285 286 # Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table 287 # or a VIEW. (We should fix this someday). 288 # 289 db close 290 sqlite3 db :memory: 291 do_execsql_test wherelimit-4.1 { 292 CREATE TABLE t1(a int); 293 INSERT INTO t1 VALUES(1); 294 INSERT INTO t1 VALUES(2); 295 INSERT INTO t1 VALUES(3); 296 CREATE TABLE t2(a int); 297 INSERT INTO t2 SELECT a+100 FROM t1; 298 CREATE VIEW tv(r,a) AS 299 SELECT rowid, a FROM t2 UNION ALL SELECT rowid, a FROM t1; 300 CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv 301 BEGIN 302 DELETE FROM t1 WHERE rowid=old.r; 303 DELETE FROM t2 WHERE rowid=old.r; 304 END; 305 } {} 306 do_catchsql_test wherelimit-4.2 { 307 DELETE FROM tv WHERE 1 LIMIT 2; 308 } {0 {}} 309 do_catchsql_test wherelimit-4.3 { 310 DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2; 311 } {0 {}} 312 do_execsql_test wherelimit-4.10 { 313 CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID; 314 INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12); 315 } {} 316 do_catchsql_test wherelimit-4.11 { 317 DELETE FROM t3 WHERE a=5 LIMIT 2; 318 } {0 {}} 319 do_execsql_test wherelimit-4.12 { 320 SELECT a,b,c,d FROM t3 ORDER BY 1; 321 } {1 2 3 4 9 10 11 12} 322 323} 324 325finish_test 326