xref: /sqlite-3.40.0/test/delete.test (revision b19a2bc6) 
1# 2001 September 15
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 DELETE FROM statement.
13#
14# $Id: delete.test,v 1.9 2001/09/16 00:13:28 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Try to delete from a non-existant table.
20#
21do_test delete-1.1 {
22  set v [catch {execsql {DELETE FROM test1}} msg]
23  lappend v $msg
24} {1 {no such table: test1}}
25
26# Try to delete from sqlite_master
27#
28do_test delete-2.1 {
29  set v [catch {execsql {DELETE FROM sqlite_master}} msg]
30  lappend v $msg
31} {1 {table sqlite_master may not be modified}}
32
33# Delete selected entries from a table with and without an index.
34#
35do_test delete-3.1a {
36  execsql {CREATE TABLE table1(f1 int, f2 int)}
37  execsql {INSERT INTO table1 VALUES(1,2)}
38  execsql {INSERT INTO table1 VALUES(2,4)}
39  execsql {INSERT INTO table1 VALUES(3,8)}
40  execsql {INSERT INTO table1 VALUES(4,16)}
41  execsql {SELECT * FROM table1 ORDER BY f1}
42} {1 2 2 4 3 8 4 16}
43do_test delete-3.1b {
44  execsql {DELETE FROM table1 WHERE f1=3}
45  execsql {SELECT * FROM table1 ORDER BY f1}
46} {1 2 2 4 4 16}
47do_test delete-3.1c {
48  execsql {CREATE INDEX index1 ON table1(f1)}
49  execsql {DELETE FROM 'table1' WHERE f1=3}
50  execsql {SELECT * FROM table1 ORDER BY f1}
51} {1 2 2 4 4 16}
52do_test delete-3.1d {
53  execsql {DELETE FROM table1 WHERE f1=2}
54  execsql {SELECT * FROM table1 ORDER BY f1}
55} {1 2 4 16}
56
57# Semantic errors in the WHERE clause
58#
59do_test delete-4.1 {
60  execsql {CREATE TABLE table2(f1 int, f2 int)}
61  set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
62  lappend v $msg
63} {1 {no such column: f3}}
64
65do_test delete-4.2 {
66  set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
67  lappend v $msg
68} {1 {no such function: xyzzy}}
69
70# Lots of deletes
71#
72do_test delete-5.1 {
73  execsql {DELETE FROM table1}
74  execsql {SELECT count(*) FROM table1}
75} {}
76do_test delete-5.2 {
77  for {set i 1} {$i<=200} {incr i} {
78     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
79  }
80  execsql {SELECT count(*) FROM table1}
81} {200}
82do_test delete-5.3 {
83  for {set i 1} {$i<=200} {incr i 4} {
84     execsql "DELETE FROM table1 WHERE f1==$i"
85  }
86  execsql {SELECT count(*) FROM table1}
87} {150}
88do_test delete-5.4 {
89  execsql "DELETE FROM table1 WHERE f1>50"
90  execsql {SELECT count(*) FROM table1}
91} {37}
92do_test delete-5.5 {
93  for {set i 1} {$i<=70} {incr i 3} {
94     execsql "DELETE FROM table1 WHERE f1==$i"
95  }
96  execsql {SELECT f1 FROM table1 ORDER BY f1}
97} {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}
98do_test delete-5.6 {
99  for {set i 1} {$i<40} {incr i} {
100     execsql "DELETE FROM table1 WHERE f1==$i"
101  }
102  execsql {SELECT f1 FROM table1 ORDER BY f1}
103} {42 44 47 48 50}
104do_test delete-5.7 {
105  execsql "DELETE FROM table1 WHERE f1!=48"
106  execsql {SELECT f1 FROM table1 ORDER BY f1}
107} {48}
108
109# Delete large quantities of data.  We want to test the List overflow
110# mechanism in the vdbe.
111#
112do_test delete-6.1 {
113  set fd [open data1.txt w]
114  for {set i 1} {$i<=3000} {incr i} {
115    puts $fd "[expr {$i}]\t[expr {$i*$i}]"
116  }
117  close $fd
118  execsql {DELETE FROM table1}
119  execsql {COPY table1 FROM 'data1.txt'}
120  execsql {DELETE FROM table2}
121  execsql {COPY table2 FROM 'data1.txt'}
122  file delete data1.txt
123  execsql {SELECT count(*) FROM table1}
124} {3000}
125do_test delete-6.2 {
126  execsql {SELECT count(*) FROM table2}
127} {3000}
128do_test delete-6.3 {
129  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
130} {1 2 3 4 5 6 7 8 9}
131do_test delete-6.4 {
132  execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
133} {1 2 3 4 5 6 7 8 9}
134do_test delete-6.5 {
135  execsql {DELETE FROM table1 WHERE f1>7}
136  execsql {SELECT f1 FROM table1 ORDER BY f1}
137} {1 2 3 4 5 6 7}
138do_test delete-6.6 {
139  execsql {DELETE FROM table2 WHERE f1>7}
140  execsql {SELECT f1 FROM table2 ORDER BY f1}
141} {1 2 3 4 5 6 7}
142do_test delete-6.7 {
143  execsql {DELETE FROM table1}
144  execsql {SELECT f1 FROM table1}
145} {}
146do_test delete-6.8 {
147  execsql {INSERT INTO table1 VALUES(2,3)}
148  execsql {SELECT f1 FROM table1}
149} {2}
150do_test delete-6.9 {
151  execsql {DELETE FROM table2}
152  execsql {SELECT f1 FROM table2}
153} {}
154do_test delete-6.10 {
155  execsql {INSERT INTO table2 VALUES(2,3)}
156  execsql {SELECT f1 FROM table2}
157} {2}
158
159
160
161finish_test
162