1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
# name: test/sql/delete/test_delete_indexed.test
# description: Test deletions on tables with indexes
# group: [delete]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE t (id INT PRIMARY KEY, s TEXT, j BIGINT);
statement ok
CREATE INDEX idx ON t(j);
statement ok
INSERT INTO t VALUES (1, 'a', 10), (2, 'b', 20), (3, 'c', 30);
# verify initial state
query III
SELECT * FROM t ORDER BY id;
----
1 a 10
2 b 20
3 c 30
# delete using primary key
statement ok
DELETE FROM t WHERE id = 2;
query III
SELECT * FROM t ORDER BY id;
----
1 a 10
3 c 30
# delete using indexed column
statement ok
DELETE FROM t WHERE j = 30;
query III
SELECT * FROM t ORDER BY id;
----
1 a 10
# insert more data
statement ok
INSERT INTO t VALUES (4, 'd', 40), (5, 'e', 50);
# test transaction rollback
statement ok
BEGIN TRANSACTION
statement ok
DELETE FROM t WHERE j > 10;
query III
SELECT * FROM t ORDER BY id;
----
1 a 10
statement ok
ROLLBACK
query III
SELECT * FROM t ORDER BY id;
----
1 a 10
4 d 40
5 e 50
# delete everything
statement ok
DELETE FROM t;
query I
SELECT COUNT(*) FROM t;
----
0
# test delete and insert in same transaction with overlapping values
statement ok
INSERT INTO t VALUES (1, 'a', 10), (2, 'b', 20), (3, 'c', 30);
statement ok
BEGIN TRANSACTION;
# delete rows with j=20,30
statement ok
DELETE FROM t WHERE j >= 20;
# insert new rows with same ids and j values
statement ok
INSERT INTO t VALUES (2, 'new_20', 20), (3, 'new_30', 30);
# verify index lookup during transaction
query III
SELECT * FROM t WHERE j = 20;
----
2 new_20 20
query III
SELECT * FROM t WHERE j = 30;
----
3 new_30 30
statement ok
COMMIT;
# verify index lookups after commit
query III
SELECT * FROM t WHERE j = 20;
----
2 new_20 20
query III
SELECT * FROM t WHERE j = 30;
----
3 new_30 30
# test rollback with overlapping values
statement ok
BEGIN TRANSACTION;
# delete rows with j=20,30
statement ok
DELETE FROM t WHERE j >= 20;
# insert rows with same ids and j values again
statement ok
INSERT INTO t VALUES (2, 'tmp_20', 20), (3, 'tmp_30', 30);
# verify index lookup during transaction
query III
SELECT * FROM t WHERE j = 20;
----
2 tmp_20 20
query III
SELECT * FROM t WHERE j = 30;
----
3 tmp_30 30
statement ok
ROLLBACK;
# verify index lookups after rollback
query III
SELECT * FROM t WHERE j = 20;
----
2 new_20 20
query III
SELECT * FROM t WHERE j = 30;
----
3 new_30 30
# verify range queries on index
query III
SELECT * FROM t WHERE j >= 20 ORDER BY j;
----
2 new_20 20
3 new_30 30
|
