Debian Codegraph @

mysql-test/suite/versioning/t/replace.test

raw
1 --source suite/versioning/key_type.inc
2 if ($MTR_COMBINATION_SEC)
3 {
4 --skip pk or unique only
5 }
6 --source suite/versioning/common.inc
7 --source suite/versioning/engines.inc
8
9 --replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
10 eval create or replace table t(
11 id int,
12 $KEY_TYPE(id),
13 x int,
14 row_start $sys_datatype_expl as row start invisible,
15 row_end $sys_datatype_expl as row end invisible,
16 period for system_time(row_start, row_end)
17 ) with system versioning;
18
19 insert t values (1, 2);
20 replace t values (1, 3);
21 select *, current_row(row_end) as current from t for system_time all order by x;
22 drop table t;
23
24 --replace_result $sys_datatype_expl SYS_DATATYPE
25 eval create table t (
26 id int unique,
27 x int,
28 row_start $sys_datatype_expl as row start invisible,
29 row_end $sys_datatype_expl as row end invisible,
30 period for system_time (row_start, row_end)
31 ) with system versioning;
32
33 insert t values (1, 2);
34 replace t values (1, 3);
35 select *, current_row(row_end) as current from t for system_time all order by x;
36 drop table t;
37
38 --echo #
39 --echo # MDEV-15645 Assertion `table->insert_values' failed in write_record upon REPLACE into a view with underlying versioned table
40 --echo #
41 create or replace table t1 (a int, b int, primary key (a), unique(b)) with system versioning;
42 insert into t1 values (1,1);
43 create or replace table t2 (c int);
44 create or replace view v as select t1.* from t1 join t2;
45 replace into v (a, b) select a, b from t1;
46 drop view v;
47 drop tables t1, t2;
48
49 --replace_result $sys_datatype_expl SYS_DATATYPE
50 eval CREATE TABLE t1 (
51 pk INT AUTO_INCREMENT,
52 f INT,
53 row_start $sys_datatype_expl AS ROW START INVISIBLE,
54 row_end $sys_datatype_expl AS ROW END INVISIBLE,
55 PRIMARY KEY(pk),
56 UNIQUE(f),
57 PERIOD FOR SYSTEM_TIME(row_start, row_end)
58 ) WITH SYSTEM VERSIONING;
59 INSERT INTO t1 () VALUES (),(),(),(),(),();
60 UPDATE IGNORE t1 SET f = 1;
61 REPLACE t1 SELECT * FROM t1;
62 DROP TABLE t1;
63
64 --echo #
65 --echo # MDEV-22540 ER_DUP_ENTRY upon REPLACE or Assertion failed
66 --echo #
67 set timestamp=1589245268.41934;
68 create table t1 (a int primary key) with system versioning;
69 insert into t1 values (1),(2);
70
71 --connect (con1,localhost,root,,test)
72 set timestamp=1589245268.52093;
73 replace into t1 values (1),(2);
74
75 --connection default
76 replace into t1 values (1),(2);
77
78 --connection con1
79 --error ER_DUP_ENTRY
80 replace into t1 values (1),(2);
81 drop table t1;
82
83 --echo #
84 --echo # MDEV-14794 Limitations which the row end as a part of PK imposes due to
85 --echo # CURRENT_TIMESTAMP behavior and otherwise
86 --echo #
87 --echo # MDEV-15330 Server crash or assertion `table->insert_values' failure in write_record upon LOAD DATA
88 --echo #
89 if ($default_engine == MEMORY)
90 {
91 --disable_query_log
92 set default_storage_engine= myisam;
93 --enable_query_log
94 }
95 create table t1 (a int, b int, c int, vc int as (c), unique(a), unique(b)) with system versioning;
96 insert ignore into t1 (a,b,c) values (1,2,3);
97
98 select a, b, c into outfile '15330.data' from t1;
99 load data infile '15330.data' ignore into table t1 (a,b,c);
100 load data infile '15330.data' replace into table t1 (a,b,c);
101 --let $datadir=`select @@datadir`
102 --remove_file $datadir/test/15330.data
103
104 # cleanup
105 drop table t1;
106 --disable_query_log
107 eval set default_storage_engine= $default_engine;
108 --enable_query_log
109
110 --echo #
111 --echo # MDEV-35343 unexpected replace behaviour when long unique index on system versioned table
112 --echo #
113 create table t1 (data char(10));
114 insert into t1 values ('o');
115 alter ignore table t1 add unique index (data);
116 alter ignore table t1 add unique index (data);
117 alter table t1 add system versioning;
118 replace into t1 values ('o'), ('o');
119 drop table t1;
120
121 --echo #
122 --echo # MDEV-15990 REPLACE on a precise-versioned table returns duplicate key
123 --echo # error (ER_DUP_ENTRY)
124 --echo #
125
126 --replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
127 eval create or replace table t1 (
128 pk int $KEY_TYPE, i int,
129 row_start $sys_datatype_expl as row start invisible,
130 row_end $sys_datatype_expl as row end invisible,
131 period for system_time(row_start, row_end)
132 ) with system versioning;
133 replace into t1 (pk,i) values (1,10),(1,100),(1,1000);
134 select pk, i, check_row(row_start, row_end) from t1 for system_time all;
135 drop table t1;
136
137 --replace_result $sys_datatype_expl SYS_DATATYPE "$KEY_TYPE" KEY_TYPE
138 eval create or replace table t1 (
139 pk int $KEY_TYPE, i int,
140 row_start $sys_datatype_expl as row start invisible,
141 row_end $sys_datatype_expl as row end invisible,
142 period for system_time(row_start, row_end)
143 ) with system versioning;
144 set timestamp= (select unix_timestamp());
145 begin;
146 insert t1(pk, i) values(1,3);
147 delete from t1;
148 insert t1(pk, i) values(1,30);
149 delete from t1;
150 insert t1(pk, i) values(1,300);
151 commit;
152 select pk, i, check_row(row_start, row_end) from t1 for system_time all;
153 drop table t1;
154
155 --echo # In this case there'll be no unique constraint on a historical row.
156 --replace_result $sys_datatype_expl SYS_DATATYPE
157 eval create or replace table t1 (
158 pk int, i int,
159 row_start $sys_datatype_expl as row start invisible,
160 row_end $sys_datatype_expl as row end invisible,
161 period for system_time(row_start, row_end)
162 ) with system versioning;
163 set timestamp= (select unix_timestamp());
164 begin;
165 insert t1(pk, i) values(1,3);
166 delete from t1;
167 insert t1(pk, i) values(1,30);
168 delete from t1;
169 insert t1(pk, i) values(1,300);
170 commit;
171 select pk, i, check_row(row_start, row_end) from t1 for system_time all;
172 drop table t1;
173
174 --echo #
175 --echo # End of 10.5 tests
176 --echo #
177
178 --source suite/versioning/common_finish.inc