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 do_test wherelimit-1.4 { 98 # limit 5, offset 2 99 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 2} 100 execsql {SELECT count(*) FROM t1} 101 } {10} 102 do_test wherelimit-1.5 { 103 # limit 5, offset -2 104 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2} 105 execsql {SELECT count(*) FROM t1} 106 } {5} 107 do_test wherelimit-1.6 { 108 # limit -5 (no limit), offset 2 109 execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5} 110 execsql {SELECT count(*) FROM t1} 111 } {2} 112 do_test wherelimit-1.7 { 113 # limit 5, offset -2 (no offset) 114 execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5} 115 execsql {SELECT count(*) FROM t1} 116 } {0} 117 create_test_data 5 118 do_test wherelimit-1.8 { 119 # limit -5 (no limit), offset -2 (no offset) 120 execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5} 121 execsql {SELECT count(*) FROM t1} 122 } {0} 123 create_test_data 3 124 do_test wherelimit-1.9 { 125 # limit 5, offset 2 126 execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5} 127 execsql {SELECT count(*) FROM t1} 128 } {4} 129 do_test wherelimit-1.10 { 130 # limit 5, offset 5 131 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5} 132 execsql {SELECT count(*) FROM t1} 133 } {4} 134 do_test wherelimit-1.11 { 135 # limit 50, offset 30 136 execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30} 137 execsql {SELECT count(*) FROM t1} 138 } {4} 139 do_test wherelimit-1.12 { 140 # limit 50, offset 30 141 execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50} 142 execsql {SELECT count(*) FROM t1} 143 } {4} 144 do_test wherelimit-1.13 { 145 execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50} 146 execsql {SELECT count(*) FROM t1} 147 } {4} 148 149 150 create_test_data 6 151 do_test wherelimit-2.0 { 152 execsql {SELECT count(*) FROM t1} 153 } {36} 154 do_test wherelimit-2.1 { 155 execsql {DELETE FROM t1 WHERE x=1} 156 execsql {SELECT count(*) FROM t1} 157 } {30} 158 create_test_data 6 159 do_test wherelimit-2.2 { 160 execsql {DELETE FROM t1 WHERE x=1 LIMIT 5} 161 execsql {SELECT count(*) FROM t1} 162 } {31} 163 do_test wherelimit-2.3 { 164 # limit 5 165 execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5} 166 execsql {SELECT count(*) FROM t1} 167 } {30} 168 do_test wherelimit-2.4 { 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 } {26} 173 do_test wherelimit-2.5 { 174 # limit 5, offset -2 175 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2} 176 execsql {SELECT count(*) FROM t1} 177 } {24} 178 do_test wherelimit-2.6 { 179 # limit -5 (no limit), offset 2 180 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5} 181 execsql {SELECT count(*) FROM t1} 182 } {20} 183 do_test wherelimit-2.7 { 184 # limit 5, offset -2 (no offset) 185 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5} 186 execsql {SELECT count(*) FROM t1} 187 } {18} 188 do_test wherelimit-2.8 { 189 # limit -5 (no limit), offset -2 (no offset) 190 execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5} 191 execsql {SELECT count(*) FROM t1} 192 } {12} 193 create_test_data 6 194 do_test wherelimit-2.9 { 195 # limit 5, offset 2 196 execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5} 197 execsql {SELECT count(*) FROM t1} 198 } {32} 199 do_test wherelimit-2.10 { 200 # limit 5, offset 5 201 execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5} 202 execsql {SELECT count(*) FROM t1} 203 } {31} 204 do_test wherelimit-2.11 { 205 # limit 50, offset 30 206 execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30} 207 execsql {SELECT count(*) FROM t1} 208 } {31} 209 do_test wherelimit-2.12 { 210 # limit 50, offset 30 211 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50} 212 execsql {SELECT count(*) FROM t1} 213 } {31} 214 do_test wherelimit-2.13 { 215 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} 216 execsql {SELECT count(*) FROM t1} 217 } {31} 218 219 220 create_test_data 6 221 do_test wherelimit-3.0 { 222 execsql {SELECT count(*) FROM t1} 223 } {36} 224 do_test wherelimit-3.1 { 225 execsql {UPDATE t1 SET y=1 WHERE x=1} 226 execsql {SELECT count(*) FROM t1 WHERE y=1} 227 } {11} 228 create_test_data 6 229 do_test wherelimit-3.2 { 230 execsql {UPDATE t1 SET y=1 WHERE x=1 LIMIT 5} 231 execsql {SELECT count(*) FROM t1 WHERE y=1} 232 } {10} 233 do_test wherelimit-3.3 { 234 # limit 5 235 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5} 236 execsql {SELECT count(*) FROM t1 WHERE y=2} 237 } {9} 238 create_test_data 6 239 do_test wherelimit-3.4 { 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 } {6} 244 do_test wherelimit-3.5 { 245 # limit 5, offset -2 246 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2} 247 execsql {SELECT count(*) FROM t1 WHERE y=1} 248 } {5} 249 do_test wherelimit-3.6 { 250 # limit -5 (no limit), offset 2 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 } {8} 254 do_test wherelimit-3.7 { 255 # limit 5, offset -2 (no offset) 256 execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5} 257 execsql {SELECT count(*) FROM t1 WHERE y=3} 258 } {10} 259 260 do_test wherelimit-3.8 { 261 # limit -5 (no limit), offset -2 (no offset) 262 execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5} 263 execsql {SELECT count(*) FROM t1 WHERE y=4} 264 } {9} 265 create_test_data 6 266 do_test wherelimit-3.9 { 267 # limit 5, offset 2 268 execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5} 269 execsql {SELECT count(*) FROM t1 WHERE y=4} 270 } {9} 271 do_test wherelimit-3.10 { 272 # limit 5, offset 5 273 execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5} 274 execsql {SELECT count(*) FROM t1 WHERE y=1} 275 } {6} 276 do_test wherelimit-3.11 { 277 # limit 50, offset 30 278 execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30} 279 execsql {SELECT count(*) FROM t1 WHERE y=1} 280 } {6} 281 do_test wherelimit-3.12 { 282 # limit 50, offset 30 283 execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50} 284 execsql {SELECT count(*) FROM t1 WHERE y=1} 285 } {6} 286 do_test wherelimit-3.13 { 287 execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} 288 execsql {SELECT count(*) FROM t1 WHERE y=1} 289 } {6} 290 291 # Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table 292 # or a VIEW. (We should fix this someday). 293 # 294 db close 295 sqlite3 db :memory: 296 do_execsql_test wherelimit-4.1 { 297 CREATE TABLE t1(a int); 298 INSERT INTO t1 VALUES(1); 299 INSERT INTO t1 VALUES(2); 300 INSERT INTO t1 VALUES(3); 301 CREATE TABLE t2(a int); 302 INSERT INTO t2 SELECT a+100 FROM t1; 303 CREATE VIEW tv(r,a) AS 304 SELECT rowid, a FROM t2 UNION ALL SELECT rowid, a FROM t1; 305 CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv 306 BEGIN 307 DELETE FROM t1 WHERE rowid=old.r; 308 DELETE FROM t2 WHERE rowid=old.r; 309 END; 310 } {} 311 do_catchsql_test wherelimit-4.2 { 312 DELETE FROM tv WHERE 1 LIMIT 2; 313 } {0 {}} 314 do_catchsql_test wherelimit-4.3 { 315 DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2; 316 } {0 {}} 317 do_execsql_test wherelimit-4.10 { 318 CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID; 319 INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12); 320 } {} 321 do_catchsql_test wherelimit-4.11 { 322 DELETE FROM t3 WHERE a=5 LIMIT 2; 323 } {0 {}} 324 do_execsql_test wherelimit-4.12 { 325 SELECT a,b,c,d FROM t3 ORDER BY 1; 326 } {1 2 3 4 9 10 11 12} 327 328} 329 330finish_test 331