Debian Codegraph @

mysql-test/suite/versioning/r/replace.result

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