# 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}} execsql { DROP TABLE t1 } # no AS on table sources do_test wherelimit-0.4 { catchsql {DELETE FROM t1 AS a WHERE x=1} } {1 {near "AS": syntax error}} do_test wherelimit-0.5 { catchsql {UPDATE t1 AS a SET y=1 WHERE x=1} } {1 {near "AS": syntax error}} # 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}} # 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} do_test wherelimit-1.4 { # limit 5, offset 2 execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 2} execsql {SELECT count(*) FROM t1} } {10} 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} } {5} 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} } {2} 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 LIMIT 5} 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