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
|
#!/usr/bin/env tarantool
local test = require("sqltester")
test:plan(19)
local test_prefix = "gh-2931-savepoints-"
test:do_catchsql_test(
test_prefix.."1.0.1",
"CREATE TABLE t1 (a int primary key);")
test:do_catchsql_test(
test_prefix.."1.0.2",
"CREATE TABLE t2 (a int primary key);")
test:do_catchsql_test(
test_prefix.."1.0.3",
[[
CREATE TRIGGER TRIG1 BEFORE INSERT ON T1
FOR EACH ROW
begin
insert into t2 values(new.a);
end;]],
{0})
local testcases = {
{"1",
[[insert into t1 values(1);
select * from t1 union all select * from t2;]],
{0,{1,1}} },
{"3",
[[START TRANSACTION;
savepoint s1_1;
savepoint s1_2;]],
{0} },
{"4",
[[rollback to savepoint s1_1;
rollback to savepoint s1_1;
select * from t1 union all select * from t2;]],
{0,{1,1}}},
{"5",
[[rollback to savepoint s1_2;]],
{1, "Can not rollback to savepoint: the savepoint does not exist"}},
{"6",
[[insert into t1 values(2);
select * from t1 union all select * from t2;]],
{0,{1,2,1,2}}},
{"7",
[[rollback to savepoint s1_1;
select * from t1 union all select * from t2;]],
{0,{1,1}}},
{"8",
[[insert into t1 values(5),(6),(98);
rollback to savepoint s1_1;
select * from t1 union all select * from t2;]],
{0,{1,1}}},
{"9",
[[insert into t1 values(2);
insert into t2 values(4);
savepoint s2;
select * from t1 union all select * from t2;]],
{0,{1,2,1,2,4}}},
{"10",
[[insert into t1 values(10); savepoint s10;
insert into t1 values(11); savepoint s11;
insert into t1 values(12); savepoint s12;
insert into t1 values(13); savepoint s13;
insert into t1 values(14); savepoint s14;
select * from t1 union all select * from t2;]],
{0,{1,2,10,11,12,13,14,1,2,4,10,11,12,13,14}}},
{"11",
[[rollback to savepoint s13;
select * from t1 union all select * from t2;]],
{0,{1,2,10,11,12,13,1,2,4,10,11,12,13}}},
{"12",
[[rollback to savepoint s12;
select * from t1 union all select * from t2;]],
{0,{1,2,10,11,12,1,2,4,10,11,12}}},
{"13",
[[rollback to savepoint s11;
select * from t1 union all select * from t2;]],
{0,{1,2,10,11,1,2,4,10,11}}},
{"14",
[[insert into t1 values(4);]],
{1,"Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"}},
{"15",
[[select * from t1 union all select * from t2;]],
{0,{1,2,10,11,1,2,4,10,11}}},
{"16",
[[insert or rollback into t1 values(4);]],
{1,"Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"}},
{"17", -- should work as transaction is rolled back
[[insert or rollback into t1 values(4);
select * from t1 union all select * from t2;]],
{0,{1,4,1,4}}},
}
test:do_catchsql_set_test(
testcases,
test_prefix.."1.")
test:finish_test()
|
