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
|
# name: test/sql/storage/mix/large_updates_deletes_persistent_segment.test_slow
# description: Test large updates/deletes/insertions on persistent segments
# group: [mix]
# load the DB from disk
load __TEST_DIR__/updates_deletes_persistent_segments.db
statement ok
CREATE TABLE test(a INTEGER, b INTEGER);
statement ok
INSERT INTO test SELECT a,b FROM (VALUES (1, 3), (NULL, NULL)) tbl1(a,b), repeat(0,1000)
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
1 3 1000
NULL NULL 1000
# reload the database from disk
restart
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
1 3 1000
NULL NULL 1000
statement ok
INSERT INTO test SELECT a,b FROM (VALUES (2, 2)) tbl1(a,b), repeat(0,347)
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
1 3 1000
2 2 347
NULL NULL 1000
restart
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
1 3 1000
2 2 347
NULL NULL 1000
statement ok
INSERT INTO test SELECT a,b FROM (VALUES (3, 3)) tbl1(a,b), repeat(0,1987)
statement ok
UPDATE test SET b=4 WHERE a=1
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
1 4 1000
2 2 347
3 3 1987
NULL NULL 1000
restart
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
1 4 1000
2 2 347
3 3 1987
NULL NULL 1000
statement ok
UPDATE test SET a=4, b=4 WHERE a=1
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
2 2 347
3 3 1987
4 4 1000
NULL NULL 1000
restart
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
2 2 347
3 3 1987
4 4 1000
NULL NULL 1000
statement ok
UPDATE test SET b=5, a=6 WHERE a=4
statement ok
DELETE FROM test WHERE a=2
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
3 3 1987
6 5 1000
NULL NULL 1000
restart
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
3 3 1987
6 5 1000
NULL NULL 1000
statement ok
UPDATE test SET b=7 WHERE a=3
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
3 7 1987
6 5 1000
NULL NULL 1000
restart
query III
SELECT a, b, COUNT(*) FROM test GROUP BY a, b ORDER BY a
----
3 7 1987
6 5 1000
NULL NULL 1000
|
