diff options
Diffstat (limited to 'test/wherelimit.test')
| -rw-r--r-- | test/wherelimit.test | 342 |
1 files changed, 342 insertions, 0 deletions
diff --git a/test/wherelimit.test b/test/wherelimit.test new file mode 100644 index 0000000..f6efab6 --- /dev/null +++ b/test/wherelimit.test @@ -0,0 +1,342 @@ +# 2008 October 6 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the LIMIT ... OFFSET ... clause +# of UPDATE and DELETE statements. +# +# $Id: wherelimit.test,v 1.2 2008/10/10 18:25:46 shane Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +proc create_test_data {size} { + # Build some test data + # + execsql { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(x int, y int); + BEGIN; + } + for {set i 1} {$i<=$size} {incr i} { + for {set j 1} {$j<=$size} {incr j} { + execsql "INSERT INTO t1 VALUES([expr {$i}],[expr {$j}])" + } + } + execsql { + COMMIT; + } + return {} +} + +ifcapable {update_delete_limit} { + + execsql { CREATE TABLE t1(x, y) } + + # check syntax error support + do_test wherelimit-0.1 { + catchsql {DELETE FROM t1 ORDER BY x} + } {1 {ORDER BY without LIMIT on DELETE}} + do_test wherelimit-0.2 { + catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x} + } {1 {ORDER BY without LIMIT on DELETE}} + do_test wherelimit-0.3 { + catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x} + } {1 {ORDER BY without LIMIT on UPDATE}} + + # no AS on table sources + # + # UPDATE: As of version 3.24, AS clauses are allowed as part of + # UPDATE or DELETE statements. + do_test wherelimit-0.4 { + catchsql {DELETE FROM t1 AS a WHERE a.x=1} + } {0 {}} + do_test wherelimit-0.5.1 { + catchsql {UPDATE t1 AS a SET y=1 WHERE x=1} + } {0 {}} + do_test wherelimit-0.5.2 { + catchsql {UPDATE t1 AS a SET y=1 WHERE t1.x=1} + } {1 {no such column: t1.x}} + + # OFFSET w/o LIMIT + do_test wherelimit-0.6 { + catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2} + } {1 {near "OFFSET": syntax error}} + do_test wherelimit-0.7 { + catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2} + } {1 {near "OFFSET": syntax error}} + + execsql { DROP TABLE t1 } + + # check deletes w/o where clauses but with limit/offsets + create_test_data 5 + do_test wherelimit-1.0 { + execsql {SELECT count(*) FROM t1} + } {25} + do_test wherelimit-1.1 { + execsql {DELETE FROM t1} + execsql {SELECT count(*) FROM t1} + } {0} + create_test_data 5 + do_test wherelimit-1.2 { + execsql {DELETE FROM t1 LIMIT 5} + execsql {SELECT count(*) FROM t1} + } {20} + do_test wherelimit-1.3 { + # limit 5 + execsql {DELETE FROM t1 ORDER BY x LIMIT 5} + execsql {SELECT count(*) FROM t1} + } {15} + create_test_data 4 + do_test wherelimit-1.3b { + # limit 5 + execsql {DELETE FROM t1 RETURNING x, y, '|' ORDER BY x, y LIMIT 5} + } {1 1 | 1 2 | 1 3 | 1 4 | 2 1 |} + do_test wherelimit-1.3c { + execsql {SELECT count(*) FROM t1} + } {11} + do_test wherelimit-1.4 { + # limit 5, offset 2 + execsql {DELETE FROM t1 RETURNING x, y, '|' ORDER BY x LIMIT 5 OFFSET 2} + } {2 4 | 3 1 | 3 2 | 3 3 | 3 4 |} + do_test wherelimit-1.4cnt { + execsql {SELECT count(*) FROM t1} + } {6} + do_test wherelimit-1.5 { + # limit 5, offset -2 + execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2} + execsql {SELECT count(*) FROM t1} + } {1} + do_test wherelimit-1.6 { + # limit -5 (no limit), offset 2 + execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5} + execsql {SELECT count(*) FROM t1} + } {1} + do_test wherelimit-1.7 { + # limit 5, offset -2 (no offset) + execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5} + execsql {SELECT count(*) FROM t1} + } {0} + create_test_data 5 + do_test wherelimit-1.8 { + # limit -5 (no limit), offset -2 (no offset) + execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5} + execsql {SELECT count(*) FROM t1} + } {0} + create_test_data 3 + do_test wherelimit-1.9 { + # limit 5, offset 2 + execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5} + execsql {SELECT count(*) FROM t1} + } {4} + do_test wherelimit-1.10 { + # limit 5, offset 5 + execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5} + execsql {SELECT count(*) FROM t1} + } {4} + do_test wherelimit-1.11 { + # limit 50, offset 30 + execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30} + execsql {SELECT count(*) FROM t1} + } {4} + do_test wherelimit-1.12 { + # limit 50, offset 30 + execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50} + execsql {SELECT count(*) FROM t1} + } {4} + do_test wherelimit-1.13 { + execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50} + execsql {SELECT count(*) FROM t1} + } {4} + + + create_test_data 6 + do_test wherelimit-2.0 { + execsql {SELECT count(*) FROM t1} + } {36} + do_test wherelimit-2.1 { + execsql {DELETE FROM t1 WHERE x=1} + execsql {SELECT count(*) FROM t1} + } {30} + create_test_data 6 + do_test wherelimit-2.2 { + execsql {DELETE FROM t1 WHERE x=1 LIMIT 5} + execsql {SELECT count(*) FROM t1} + } {31} + do_test wherelimit-2.3 { + # limit 5 + execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5} + execsql {SELECT count(*) FROM t1} + } {30} + do_test wherelimit-2.4 { + # limit 5, offset 2 + execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2} + execsql {SELECT count(*) FROM t1} + } {26} + do_test wherelimit-2.5 { + # limit 5, offset -2 + execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2} + execsql {SELECT count(*) FROM t1} + } {24} + do_test wherelimit-2.6 { + # limit -5 (no limit), offset 2 + execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5} + execsql {SELECT count(*) FROM t1} + } {20} + do_test wherelimit-2.7 { + # limit 5, offset -2 (no offset) + execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5} + execsql {SELECT count(*) FROM t1} + } {18} + do_test wherelimit-2.8 { + # limit -5 (no limit), offset -2 (no offset) + execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5} + execsql {SELECT count(*) FROM t1} + } {12} + create_test_data 6 + do_test wherelimit-2.9 { + # limit 5, offset 2 + execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5} + execsql {SELECT count(*) FROM t1} + } {32} + do_test wherelimit-2.10 { + # limit 5, offset 5 + execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5} + execsql {SELECT count(*) FROM t1} + } {31} + do_test wherelimit-2.11 { + # limit 50, offset 30 + execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30} + execsql {SELECT count(*) FROM t1} + } {31} + do_test wherelimit-2.12 { + # limit 50, offset 30 + execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50} + execsql {SELECT count(*) FROM t1} + } {31} + do_test wherelimit-2.13 { + execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} + execsql {SELECT count(*) FROM t1} + } {31} + + + create_test_data 6 + do_test wherelimit-3.0 { + execsql {SELECT count(*) FROM t1} + } {36} + do_test wherelimit-3.1 { + execsql {UPDATE t1 SET y=1 WHERE x=1} + execsql {SELECT count(*) FROM t1 WHERE y=1} + } {11} + create_test_data 6 + do_test wherelimit-3.2 { + execsql {UPDATE t1 SET y=1 WHERE x=1 RETURNING x, y, '|' LIMIT 5} + } {1 1 | 1 1 | 1 1 | 1 1 | 1 1 |} + do_test wherelimit-3.2cnt { + execsql {SELECT count(*) FROM t1 WHERE y=1} + } {10} + do_test wherelimit-3.3 { + # limit 5 + execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5} + execsql {SELECT count(*) FROM t1 WHERE y=2} + } {9} + create_test_data 6 + do_test wherelimit-3.4 { + # limit 5, offset 2 + execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2} + execsql {SELECT count(*) FROM t1 WHERE y=1} + } {6} + do_test wherelimit-3.5 { + # limit 5, offset -2 + execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2} + execsql {SELECT count(*) FROM t1 WHERE y=1} + } {5} + do_test wherelimit-3.6 { + # limit -5 (no limit), offset 2 + execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT 2, -5} + execsql {SELECT count(*) FROM t1 WHERE y=3} + } {8} + do_test wherelimit-3.7 { + # limit 5, offset -2 (no offset) + execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5} + execsql {SELECT count(*) FROM t1 WHERE y=3} + } {10} + + do_test wherelimit-3.8 { + # limit -5 (no limit), offset -2 (no offset) + execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5} + execsql {SELECT count(*) FROM t1 WHERE y=4} + } {9} + create_test_data 6 + do_test wherelimit-3.9 { + # limit 5, offset 2 + execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5} + execsql {SELECT count(*) FROM t1 WHERE y=4} + } {9} + do_test wherelimit-3.10 { + # limit 5, offset 5 + execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5} + execsql {SELECT count(*) FROM t1 WHERE y=1} + } {6} + do_test wherelimit-3.11 { + # limit 50, offset 30 + execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30} + execsql {SELECT count(*) FROM t1 WHERE y=1} + } {6} + do_test wherelimit-3.12 { + # limit 50, offset 30 + execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50} + execsql {SELECT count(*) FROM t1 WHERE y=1} + } {6} + do_test wherelimit-3.13 { + execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50} + execsql {SELECT count(*) FROM t1 WHERE y=1} + } {6} + + # Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table + # or a VIEW. (We should fix this someday). + # + db close + sqlite3 db :memory: + do_execsql_test wherelimit-4.1 { + CREATE TABLE t1(a int); + INSERT INTO t1 VALUES(1); + INSERT INTO t1 VALUES(2); + INSERT INTO t1 VALUES(3); + CREATE TABLE t2(a int); + INSERT INTO t2 SELECT a+100 FROM t1; + CREATE VIEW tv(r,a) AS + SELECT rowid, a FROM t2 UNION ALL SELECT rowid, a FROM t1; + CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv + BEGIN + DELETE FROM t1 WHERE rowid=old.r; + DELETE FROM t2 WHERE rowid=old.r; + END; + } {} + do_catchsql_test wherelimit-4.2 { + DELETE FROM tv WHERE 1 LIMIT 2; + } {0 {}} + do_catchsql_test wherelimit-4.3 { + DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2; + } {0 {}} + do_execsql_test wherelimit-4.10 { + CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID; + INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12); + } {} + do_catchsql_test wherelimit-4.11 { + DELETE FROM t3 WHERE a=5 LIMIT 2; + } {0 {}} + do_execsql_test wherelimit-4.12 { + SELECT a,b,c,d FROM t3 ORDER BY 1; + } {1 2 3 4 9 10 11 12} + +} + +finish_test |