/trunk/test/wherelimit.test |
@@ -0,0 +1,284 @@ |
# 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} { |
|
# 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 |
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} |
|
} |
|
finish_test |