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