xref: /sqlite-3.40.0/test/delete.test (revision 0353cedd) 
1# Copyright (c) 1999, 2000 D. Richard Hipp
2#
3# This program is free software; you can redistribute it and/or
4# modify it under the terms of the GNU General Public
5# License as published by the Free Software Foundation; either
6# version 2 of the License, or (at your option) any later version.
7#
8# This program is distributed in the hope that it will be useful,
9# but WITHOUT ANY WARRANTY; without even the implied warranty of
10# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
11# General Public License for more details.
12#
13# You should have received a copy of the GNU General Public
14# License along with this library; if not, write to the
15# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
16# Boston, MA  02111-1307, USA.
17#
18# Author contact information:
19#   [email protected]
20#   http://www.hwaci.com/drh/
21#
22#***********************************************************************
23# This file implements regression tests for SQLite library.  The
24# focus of this file is testing the DELETE FROM statement.
25#
26# $Id: delete.test,v 1.8 2001/03/20 22:05:00 drh Exp $
27
28set testdir [file dirname $argv0]
29source $testdir/tester.tcl
30
31# Try to delete from a non-existant table.
32#
33do_test delete-1.1 {
34  set v [catch {execsql {DELETE FROM test1}} msg]
35  lappend v $msg
36} {1 {no such table: test1}}
37
38# Try to delete from sqlite_master
39#
40do_test delete-2.1 {
41  set v [catch {execsql {DELETE FROM sqlite_master}} msg]
42  lappend v $msg
43} {1 {table sqlite_master may not be modified}}
44
45# Delete selected entries from a table with and without an index.
46#
47do_test delete-3.1a {
48  execsql {CREATE TABLE table1(f1 int, f2 int)}
49  execsql {INSERT INTO table1 VALUES(1,2)}
50  execsql {INSERT INTO table1 VALUES(2,4)}
51  execsql {INSERT INTO table1 VALUES(3,8)}
52  execsql {INSERT INTO table1 VALUES(4,16)}
53  execsql {SELECT * FROM table1 ORDER BY f1}
54} {1 2 2 4 3 8 4 16}
55do_test delete-3.1b {
56  execsql {DELETE FROM table1 WHERE f1=3}
57  execsql {SELECT * FROM table1 ORDER BY f1}
58} {1 2 2 4 4 16}
59do_test delete-3.1c {
60  execsql {CREATE INDEX index1 ON table1(f1)}
61  execsql {DELETE FROM 'table1' WHERE f1=3}
62  execsql {SELECT * FROM table1 ORDER BY f1}
63} {1 2 2 4 4 16}
64do_test delete-3.1d {
65  execsql {DELETE FROM table1 WHERE f1=2}
66  execsql {SELECT * FROM table1 ORDER BY f1}
67} {1 2 4 16}
68
69# Semantic errors in the WHERE clause
70#
71do_test delete-4.1 {
72  execsql {CREATE TABLE table2(f1 int, f2 int)}
73  set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
74  lappend v $msg
75} {1 {no such column: f3}}
76
77do_test delete-4.2 {
78  set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
79  lappend v $msg
80} {1 {no such function: xyzzy}}
81
82# Lots of deletes
83#
84do_test delete-5.1 {
85  execsql {DELETE FROM table1}
86  execsql {SELECT count(*) FROM table1}
87} {}
88do_test delete-5.2 {
89  for {set i 1} {$i<=200} {incr i} {
90     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
91  }
92  execsql {SELECT count(*) FROM table1}
93} {200}
94do_test delete-5.3 {
95  for {set i 1} {$i<=200} {incr i 4} {
96     execsql "DELETE FROM table1 WHERE f1==$i"
97  }
98  execsql {SELECT count(*) FROM table1}
99} {150}
100do_test delete-5.4 {
101  execsql "DELETE FROM table1 WHERE f1>50"
102  execsql {SELECT count(*) FROM table1}
103} {37}
104do_test delete-5.5 {
105  for {set i 1} {$i<=70} {incr i 3} {
106     execsql "DELETE FROM table1 WHERE f1==$i"
107  }
108  execsql {SELECT f1 FROM table1 ORDER BY f1}
109} {2 3 6 8 11 12 14 15 18 20 23 24 26 27 30 32 35 36 38 39 42 44 47 48 50}
110do_test delete-5.6 {
111  for {set i 1} {$i<40} {incr i} {
112     execsql "DELETE FROM table1 WHERE f1==$i"
113  }
114  execsql {SELECT f1 FROM table1 ORDER BY f1}
115} {42 44 47 48 50}
116do_test delete-5.7 {
117  execsql "DELETE FROM table1 WHERE f1!=48"
118  execsql {SELECT f1 FROM table1 ORDER BY f1}
119} {48}
120
121# Delete large quantities of data.  We want to test the List overflow
122# mechanism in the vdbe.
123#
124do_test delete-6.1 {
125  set fd [open data1.txt w]
126  for {set i 1} {$i<=3000} {incr i} {
127    puts $fd "[expr {$i}]\t[expr {$i*$i}]"
128  }
129  close $fd
130  execsql {DELETE FROM table1}
131  execsql {COPY table1 FROM 'data1.txt'}
132  execsql {DELETE FROM table2}
133  execsql {COPY table2 FROM 'data1.txt'}
134  file delete data1.txt
135  execsql {SELECT count(*) FROM table1}
136} {3000}
137do_test delete-6.2 {
138  execsql {SELECT count(*) FROM table2}
139} {3000}
140do_test delete-6.3 {
141  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
142} {1 2 3 4 5 6 7 8 9}
143do_test delete-6.4 {
144  execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
145} {1 2 3 4 5 6 7 8 9}
146do_test delete-6.5 {
147  execsql {DELETE FROM table1 WHERE f1>7}
148  execsql {SELECT f1 FROM table1 ORDER BY f1}
149} {1 2 3 4 5 6 7}
150do_test delete-6.6 {
151  execsql {DELETE FROM table2 WHERE f1>7}
152  execsql {SELECT f1 FROM table2 ORDER BY f1}
153} {1 2 3 4 5 6 7}
154do_test delete-6.7 {
155  execsql {DELETE FROM table1}
156  execsql {SELECT f1 FROM table1}
157} {}
158do_test delete-6.8 {
159  execsql {INSERT INTO table1 VALUES(2,3)}
160  execsql {SELECT f1 FROM table1}
161} {2}
162do_test delete-6.9 {
163  execsql {DELETE FROM table2}
164  execsql {SELECT f1 FROM table2}
165} {}
166do_test delete-6.10 {
167  execsql {INSERT INTO table2 VALUES(2,3)}
168  execsql {SELECT f1 FROM table2}
169} {2}
170
171
172
173finish_test
174