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
|
# name: test/sql/update/test_update_many_updaters_nulls.test
# description: Test update behavior with multiple updaters and NULL values
# group: [update]
statement ok
SET default_null_order='nulls_first';
statement ok
SET immediate_transaction_mode=true
statement ok updater
PRAGMA enable_verification
statement ok con1
PRAGMA enable_verification
statement ok con2
PRAGMA enable_verification
statement ok con3
PRAGMA enable_verification
statement ok con4
PRAGMA enable_verification
statement ok con5
PRAGMA enable_verification
# this test contains five query connections (con1, con2, con3, con4, con5)
# and one updating connection (updater)
# create a table, filled with 3 values (1), (2), (3)
statement ok con1
CREATE TABLE test (a INTEGER);
statement ok con1
INSERT INTO test VALUES (1), (2), (3)
# now we start updating specific values and reading different versions
statement ok con1
BEGIN TRANSACTION
query I updater
UPDATE test SET a=NULL WHERE a=1
----
1
statement ok con2
BEGIN TRANSACTION
query I updater
UPDATE test SET a=NULL WHERE a=2
----
1
statement ok con3
BEGIN TRANSACTION
query I updater
UPDATE test SET a=NULL WHERE a=3
----
1
statement ok con4
BEGIN TRANSACTION
query I updater
SELECT COUNT(*) FROM test WHERE a IS NULL
----
3
query I updater
UPDATE test SET a=99 WHERE a IS NULL
----
3
statement ok con5
BEGIN TRANSACTION
# now read the different states
# con sees {1, 2, 3}
query I con1
SELECT * FROM test ORDER BY a
----
1
2
3
# con2 sees {NULL, 2, 3}
query I con2
SELECT * FROM test ORDER BY a
----
NULL
2
3
# con3 sees {NULL, NULL, 3}
query I con3
SELECT * FROM test ORDER BY a
----
NULL
NULL
3
# con4 sees {NULL, NULL, NULL}
query I con4
SELECT * FROM test ORDER BY a
----
NULL
NULL
NULL
# con5 sees {99, 99, 99}
query I con5
SELECT * FROM test ORDER BY a
----
99
99
99
# now verify that we get conflicts when we update values that have been updated AFTER we started
statement error con1
UPDATE test SET a=99 WHERE a=1
----
statement error con2
UPDATE test SET a=99 WHERE a=2
----
statement error con3
UPDATE test SET a=99 WHERE a=3
----
statement error con4
UPDATE test SET a=99 WHERE a IS NULL
----
|
