test/wherelimit.test

   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   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
 342 finish_test