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