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