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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 proc 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 39 ifcapable {update_delete_limit} { 40 41 # check syntax error support 42 do_test wherelimit-0.1 { 43 catchsql {DELETE FROM t1 ORDER BY x} 44 } {1 {ORDER BY without LIMIT on DELETE}} 45 do_test wherelimit-0.2 { 46 catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x} 47 } {1 {ORDER BY without LIMIT on DELETE}} 48 do_test wherelimit-0.3 { 49 catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x} 50 } {1 {ORDER BY without LIMIT on UPDATE}} 51 52 # no AS on table sources 53 do_test wherelimit-0.4 { 54 catchsql {DELETE FROM t1 AS a WHERE x=1} 55 } {1 {near "AS": syntax error}} 56 do_test wherelimit-0.5 { 57 catchsql {UPDATE t1 AS a SET y=1 WHERE x=1} 58 } {1 {near "AS": syntax error}} 59 60 # OFFSET w/o LIMIT 61 do_test wherelimit-0.6 { 62 catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2} 63 } {1 {near "OFFSET": syntax error}} 64 do_test wherelimit-0.7 { 65 catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2} 66 } {1 {near "OFFSET": syntax error}} 67 68 69 # check deletes w/o where clauses but with limit/offsets 70 create_test_data 5 71 do_test wherelimit-1.0 { 72 execsql {SELECT count(*) FROM t1} 73 } {25} 74 do_test wherelimit-1.1 { 75 execsql {DELETE FROM t1} 76 execsql {SELECT count(*) FROM t1} 77 } {0} 78 create_test_data 5 79 do_test wherelimit-1.2 { 80 execsql {DELETE FROM t1 LIMIT 5} 81 execsql {SELECT count(*) FROM t1} 82 } {20} 83 do_test wherelimit-1.3 { 84 # limit 5 85 execsql {DELETE FROM t1 ORDER BY x LIMIT 5} 86 execsql {SELECT count(*) FROM t1} 87 } {15} 88 do_test wherelimit-1.4 { 89 # limit 5, offset 2 90 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 2} 91 execsql {SELECT count(*) FROM t1} 92 } {10} 93 do_test wherelimit-1.5 { 94 # limit 5, offset -2 95 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2} 96 execsql {SELECT count(*) FROM t1} 97 } {5} 98 do_test wherelimit-1.6 { 99 # limit -5 (no limit), offset 2 100 execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5} 101 execsql {SELECT count(*) FROM t1} 102 } {2} 103 do_test wherelimit-1.7 { 104 # limit 5, offset -2 (no offset) 105 execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5} 106 execsql {SELECT count(*) FROM t1} 107 } {0} 108 create_test_data 5 109 do_test wherelimit-1.8 { 110 # limit -5 (no limit), offset -2 (no offset) 111 execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5} 112 execsql {SELECT count(*) FROM t1} 113 } {0} 114 create_test_data 3 115 do_test wherelimit-1.9 { 116 # limit 5, offset 2 117 execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5} 118 execsql {SELECT count(*) FROM t1} 119 } {4} 120 do_test wherelimit-1.10 { 121 # limit 5, offset 5 122 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5} 123 execsql {SELECT count(*) FROM t1} 124 } {4} 125 do_test wherelimit-1.11 { 126 # limit 50, offset 30 127 execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30} 128 execsql {SELECT count(*) FROM t1} 129 } {4} 130 do_test wherelimit-1.12 { 131 # limit 50, offset 30 132 execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50} 133 execsql {SELECT count(*) FROM t1} 134 } {4} 135 do_test wherelimit-1.13 { 136 execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50} 137 execsql {SELECT count(*) FROM t1} 138 } {4} 139 140 141 create_test_data 6 142 do_test wherelimit-2.0 { 143 execsql {SELECT count(*) FROM t1} 144 } {36} 145 do_test wherelimit-2.1 { 146 execsql {DELETE FROM t1 WHERE x=1} 147 execsql {SELECT count(*) FROM t1} 148 } {30} 149 create_test_data 6 150 do_test wherelimit-2.2 { 151 execsql {DELETE FROM t1 WHERE x=1 LIMIT 5} 152 execsql {SELECT count(*) FROM t1} 153 } {31} 154 do_test wherelimit-2.3 { 155 # limit 5 156 execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5} 157 execsql {SELECT count(*) FROM t1} 158 } {30} 159 do_test wherelimit-2.4 { 160 # limit 5, offset 2 161 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2} 162 execsql {SELECT count(*) FROM t1} 163 } {26} 164 do_test wherelimit-2.5 { 165 # limit 5, offset -2 166 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2} 167 execsql {SELECT count(*) FROM t1} 168 } {24} 169 do_test wherelimit-2.6 { 170 # limit -5 (no limit), offset 2 171 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5} 172 execsql {SELECT count(*) FROM t1} 173 } {20} 174 do_test wherelimit-2.7 { 175 # limit 5, offset -2 (no offset) 176 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5} 177 execsql {SELECT count(*) FROM t1} 178 } {18} 179 do_test wherelimit-2.8 { 180 # limit -5 (no limit), offset -2 (no offset) 181 execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5} 182 execsql {SELECT count(*) FROM t1} 183 } {12} 184 create_test_data 6 185 do_test wherelimit-2.9 { 186 # limit 5, offset 2 187 execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5} 188 execsql {SELECT count(*) FROM t1} 189 } {32} 190 do_test wherelimit-2.10 { 191 # limit 5, offset 5 192 execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5} 193 execsql {SELECT count(*) FROM t1} 194 } {31} 195 do_test wherelimit-2.11 { 196 # limit 50, offset 30 197 execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30} 198 execsql {SELECT count(*) FROM t1} 199 } {31} 200 do_test wherelimit-2.12 { 201 # limit 50, offset 30 202 execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50} 203 execsql {SELECT count(*) FROM t1} 204 } {31} 205 do_test wherelimit-2.13 { 206 execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} 207 execsql {SELECT count(*) FROM t1} 208 } {31} 209 210 211 create_test_data 6 212 do_test wherelimit-3.0 { 213 execsql {SELECT count(*) FROM t1} 214 } {36} 215 do_test wherelimit-3.1 { 216 execsql {UPDATE t1 SET y=1 WHERE x=1} 217 execsql {SELECT count(*) FROM t1 WHERE y=1} 218 } {11} 219 create_test_data 6 220 do_test wherelimit-3.2 { 221 execsql {UPDATE t1 SET y=1 WHERE x=1 LIMIT 5} 222 execsql {SELECT count(*) FROM t1 WHERE y=1} 223 } {10} 224 do_test wherelimit-3.3 { 225 # limit 5 226 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5} 227 execsql {SELECT count(*) FROM t1 WHERE y=2} 228 } {9} 229 create_test_data 6 230 do_test wherelimit-3.4 { 231 # limit 5, offset 2 232 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2} 233 execsql {SELECT count(*) FROM t1 WHERE y=1} 234 } {6} 235 do_test wherelimit-3.5 { 236 # limit 5, offset -2 237 execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2} 238 execsql {SELECT count(*) FROM t1 WHERE y=1} 239 } {5} 240 do_test wherelimit-3.6 { 241 # limit -5 (no limit), offset 2 242 execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT 2, -5} 243 execsql {SELECT count(*) FROM t1 WHERE y=3} 244 } {8} 245 do_test wherelimit-3.7 { 246 # limit 5, offset -2 (no offset) 247 execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5} 248 execsql {SELECT count(*) FROM t1 WHERE y=3} 249 } {10} 250 251 do_test wherelimit-3.8 { 252 # limit -5 (no limit), offset -2 (no offset) 253 execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5} 254 execsql {SELECT count(*) FROM t1 WHERE y=4} 255 } {9} 256 create_test_data 6 257 do_test wherelimit-3.9 { 258 # limit 5, offset 2 259 execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5} 260 execsql {SELECT count(*) FROM t1 WHERE y=4} 261 } {9} 262 do_test wherelimit-3.10 { 263 # limit 5, offset 5 264 execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5} 265 execsql {SELECT count(*) FROM t1 WHERE y=1} 266 } {6} 267 do_test wherelimit-3.11 { 268 # limit 50, offset 30 269 execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30} 270 execsql {SELECT count(*) FROM t1 WHERE y=1} 271 } {6} 272 do_test wherelimit-3.12 { 273 # limit 50, offset 30 274 execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50} 275 execsql {SELECT count(*) FROM t1 WHERE y=1} 276 } {6} 277 do_test wherelimit-3.13 { 278 execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} 279 execsql {SELECT count(*) FROM t1 WHERE y=1} 280 } {6} 281 282 } 283 284 finish_test 285
