Debian Codegraph @

mysql-test/suite/innodb/t/dml_operations_temp_table.test

raw
1
2 ####################################################################
3 # TC to test temp-table DML optimization changes for correctness #
4 # Sceanrio covered: #
5 # 1. autocommit: operating all dml stmt with auto-commit on #
6 # 2. begin-commit-rollback: sequence of dml stmt with trx that is #
7 # is committed and rollback post commit to see effect. #
8 # 3. begin-rollback: sequence of dml stmt with trx rollback. #
9 # given that undo logs are generated for trx, rollback should #
10 # succeed. #
11 # 4. begin-savepoint-rollback-commit: dml stmt with trx involving #
12 # savepoint. #
13 # 5. check error test-cases where-in insert/delete/update fails #
14 ####################################################################
15
16
17 #-------------------------------------------------------------------
18 #
19 # 1. autocommit: operating all dml stmt with auto-commit on #
20 #
21 use test;
22 --disable_warnings
23 drop table if exists t;
24 --enable_warnings
25 create temporary table t (
26 i int,
27 f float,
28 primary key pk_index(i),
29 index sec_index(f)
30 ) engine = innodb;
31 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
32 #
33 rollback;
34 --sorted_result
35 select * from t;
36 update t set i = 50 where i = 40;
37 update t set i = 60 where i = 30;
38 rollback;
39 --sorted_result
40 select * from t;
41 delete from t where i = 10;
42 delete from t where f < 3.4;
43 rollback;
44 --sorted_result
45 select * from t;
46 delete from t where i = 10;
47 delete from t;
48 --sorted_result
49 select * from t;
50 drop table if exists t;
51
52 #-------------------------------------------------------------------
53 #
54 # 2. begin-commit-rollback: sequence of dml stmt with trx that is #
55 # is committed and rollback post commit to see effect. #
56 #
57 create temporary table t (
58 i int,
59 f float,
60 primary key pk_index(i),
61 index sec_index(f)
62 ) engine = innodb;
63 begin;
64 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
65 --sorted_result
66 select * from t;
67 commit;
68 --sorted_result
69 select * from t;
70 rollback;
71 --sorted_result
72 select * from t;
73 drop table if exists t;
74
75 #-------------------------------------------------------------------
76 #
77 # 3. begin-rollback: sequence of dml stmt with trx rollback. #
78 # given that undo logs are generated for trx, rollback should #
79 # succeed. #
80 #
81 create temporary table t (
82 i int,
83 f float,
84 primary key pk_index(i),
85 index sec_index(f)
86 ) engine = innodb;
87 begin;
88 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
89 --sorted_result
90 select * from t;
91 rollback;
92 --sorted_result
93 select * from t;
94 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
95 begin;
96 delete from t;
97 --sorted_result
98 select * from t;
99 rollback;
100 --sorted_result
101 select * from t;
102 begin;
103 update t set i = 50 where i = 40;
104 update t set i = 60 where i = 30;
105 --sorted_result
106 select * from t;
107 rollback;
108 --sorted_result
109 select * from t;
110 drop table if exists t;
111
112 #-------------------------------------------------------------------
113 #
114 # 4. begin-savepoint-rollback-commit: dml stmt with trx involving #
115 # savepoint. #
116 #
117 create temporary table t (
118 i int,
119 f float,
120 primary key pk_index(i),
121 index sec_index(f)
122 ) engine = innodb;
123 start transaction;
124 savepoint first;
125 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
126 --sorted_result
127 select * from t;
128 rollback to first;
129 --sorted_result
130 select * from t;
131 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
132 begin;
133 delete from t where i = 10;
134 --sorted_result
135 select * from t;
136 savepoint first;
137 delete from t;
138 --sorted_result
139 select * from t;
140 rollback to first;
141 --sorted_result
142 select * from t;
143 commit;
144 --sorted_result
145 select * from t;
146 begin;
147 update t set i = 50 where i = 40;
148 savepoint first;
149 update t set i = 60 where i = 30;
150 --sorted_result
151 select * from t;
152 rollback to first;
153 --sorted_result
154 select * from t;
155 commit;
156 drop table if exists t;
157
158 #-------------------------------------------------------------------
159 #
160 # 5. check error test-cases where-in insert/delete/update fails #
161 #
162 use test;
163 create temporary table t1 (i int, f float,
164 primary key pk(i),
165 unique index fk(f)
166 ) engine = innodb;
167 insert into t1 values (1, 1.2), (2, 2.3), (3, 3.4);
168 --sorted_result
169 select * from t1;
170 --error ER_DUP_ENTRY
171 insert into t1 values (4, 4.5), (1, 5.6);
172 --sorted_result
173 select * from t1;
174 insert into t1 values (4, 4.5), (5, 5.6);
175 --sorted_result
176 select * from t1;
177 #
178 --error ER_DUP_ENTRY
179 update t1 set i = 1 where i = 4;
180 truncate table t1;
181 insert into t1 values (1, 1.2), (3, 2.3), (5, 3.4), (6, 7.4);
182 --sorted_result
183 select * from t1;
184 --error ER_DUP_ENTRY
185 update t1 set i = i + 1;
186 --sorted_result
187 select * from t1;
188 drop table t1;
189
190 #-------------------------------------------------------------------
191 #
192 # remove test-bed
193 #
194
195