Debian Codegraph @

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

raw
1 create table t (a int) with system versioning;
2 set @before= UNIX_TIMESTAMP(now(6));
3 insert into t values (1);
4 set @after= UNIX_TIMESTAMP(now(6));
5 update t set a= 2;
6
7 show global variables like 'system_versioning_asof';
8 show variables like 'system_versioning_asof';
9 select * from t;
10
11 set system_versioning_asof= '2031-1-1 0:0:0';
12 show variables like 'system_versioning_asof';
13 select * from t;
14
15 set system_versioning_asof= '2011-1-1 0:0:0';
16 show variables like 'system_versioning_asof';
17 select * from t;
18
19 # global
20 --error ER_WRONG_VALUE_FOR_VAR
21 set global system_versioning_asof= 'alley';
22 --error ER_WRONG_VALUE_FOR_VAR
23 set global system_versioning_asof= null;
24 --error ER_WRONG_TYPE_FOR_VAR
25 set global system_versioning_asof= 1;
26 --error ER_WRONG_TYPE_FOR_VAR
27 set global system_versioning_asof= 1.1;
28 --error ER_WRONG_VALUE_FOR_VAR
29 set global system_versioning_asof= '2011-02-29 00:00';
30 --error ER_WRONG_VALUE_FOR_VAR
31 set global system_versioning_asof= '2011-02-28 24:00';
32 --error ER_WRONG_VALUE_FOR_VAR
33 set global system_versioning_asof= '2011-00-28 00:00';
34 --error ER_WRONG_VALUE_FOR_VAR
35 set global system_versioning_asof= '0000-00-00 00:00';
36
37 # session
38 --error ER_WRONG_VALUE_FOR_VAR
39 set system_versioning_asof= 'alley';
40 --error ER_WRONG_VALUE_FOR_VAR
41 set system_versioning_asof= null;
42 --error ER_WRONG_TYPE_FOR_VAR
43 set system_versioning_asof= 1;
44 --error ER_WRONG_TYPE_FOR_VAR
45 set system_versioning_asof= 1.1;
46 --error ER_WRONG_VALUE_FOR_VAR
47 set system_versioning_asof= '2011-02-29 00:00';
48 --error ER_WRONG_VALUE_FOR_VAR
49 set system_versioning_asof= '2011-02-28 24:00';
50 --error ER_WRONG_VALUE_FOR_VAR
51 set system_versioning_asof= '2011-00-28 00:00';
52 --error ER_WRONG_VALUE_FOR_VAR
53 set system_versioning_asof= '0000-00-00 00:00';
54
55 --echo # GLOBAL @@system_versioning_asof
56 set global system_versioning_asof= '1991-11-11 11:11:11.1111119';
57 show global variables like 'system_versioning_asof';
58
59 set global system_versioning_asof= '1990-01-01 00:00:00';
60 show global variables like 'system_versioning_asof';
61
62 --enable_prepare_warnings
63 set global system_versioning_asof= timestamp'1991-11-11 11:11:11.1111119';
64 --disable_prepare_warnings
65 show global variables like 'system_versioning_asof';
66
67 set @ts= timestamp'1990-01-01 00:00:00';
68 set global system_versioning_asof= @ts;
69 show global variables like 'system_versioning_asof';
70
71 set global system_versioning_asof= default;
72 select @@global.system_versioning_asof;
73
74 --echo # SESSION @@system_versioning_asof
75 set system_versioning_asof= '1991-11-11 11:11:11.1111119';
76 show variables like 'system_versioning_asof';
77
78 set system_versioning_asof= '1990-01-01 00:00:00';
79 show variables like 'system_versioning_asof';
80
81 --enable_prepare_warnings
82 set system_versioning_asof= timestamp'1991-11-11 11:11:11.1111119';
83 --disable_prepare_warnings
84 show variables like 'system_versioning_asof';
85
86 set @ts= timestamp'1990-01-01 00:00:00';
87 set system_versioning_asof= @ts;
88 show variables like 'system_versioning_asof';
89
90 --echo # DEFAULT: value is copied from GLOBAL to SESSION
91 set global time_zone= "+03:00";
92 set time_zone= "+10:00";
93 set global system_versioning_asof= timestamp'1991-11-11 11:11:11.111111';
94 set system_versioning_asof= '1990-01-01 00:00:00';
95 select @@global.system_versioning_asof != @@system_versioning_asof as different;
96 set system_versioning_asof= default;
97 select @@global.system_versioning_asof != @@system_versioning_asof as different;
98 set global system_versioning_asof= default;
99 select @@global.system_versioning_asof = @@system_versioning_asof as equal;
100
101 set global time_zone= DEFAULT;
102 set time_zone= DEFAULT;
103 set global system_versioning_asof= DEFAULT;
104 set system_versioning_asof= DEFAULT;
105 select @@global.system_versioning_asof, @@system_versioning_asof;
106
107 select * from t for system_time all;
108
109 select * from t;
110 select * from t for system_time as of timestamp current_timestamp(6);
111 select * from t for system_time all;
112 select * from t for system_time from '1970-01-01 00:00' to current_timestamp(6);
113 select * from t for system_time between '1970-01-01 00:00' and current_timestamp(6);
114
115 -- echo # MDEV-16026: Global system_versioning_asof must not be used if client sessions can have non-default time zone
116 -- echo # changing time zone should not abuse `system_versioning_asof`
117
118 set session time_zone = '+10:00';
119 set global system_versioning_asof = '1999-09-08 00:00:00.000000';
120 show global variables like 'system_versioning_asof';
121 set session time_zone = '+03:00';
122 show global variables like 'system_versioning_asof';
123
124 set session time_zone = '+03:00';
125 set session system_versioning_asof = '2000-09-08 00:00:00.000000';
126 show session variables like 'system_versioning_asof';
127 set session time_zone = '+10:00';
128 show session variables like 'system_versioning_asof';
129 -- echo # global and local time zones should not interfere
130 show global variables like 'system_versioning_asof';
131
132 set time_zone= "+10:00";
133 set system_versioning_asof= FROM_UNIXTIME(@before);
134 select * from t as empty;
135 set system_versioning_asof= FROM_UNIXTIME(@after);
136 select * from t as nonempty;
137
138 set time_zone= "+03:00";
139 set system_versioning_asof= FROM_UNIXTIME(@before);
140 select * from t as empty;
141 set system_versioning_asof= FROM_UNIXTIME(@after);
142 select * from t as nonempty;
143
144 --echo # MDEV-16481: set global system_versioning_asof=sf() crashes in specific case
145 --echo # Using global variable inside a stored function should not crash
146 create or replace function now_global() returns timestamp
147 return CONVERT_TZ(now(), @@session.time_zone, @@global.time_zone);
148 set global system_versioning_asof= now_global();
149 drop function now_global;
150
151 set global time_zone= "SYSTEM";
152 set time_zone= "SYSTEM";
153 set global system_versioning_asof= default;
154 set system_versioning_asof= default;
155
156 show status like "Feature_system_versioning";
157
158 drop table t;
159
160 --echo #
161 --echo # MDEV-22906 Disallow system_versioning_asof in DML
162 --echo #
163 create or replace table t1 (x int) with system versioning;
164 create or replace table t2 (y int);
165 insert into t1 values (1);
166 insert into t2 values (1);
167 set system_versioning_asof= '1970-01-02 00:00:00';
168 delete t1, t2 from t1 join t2 where t1.x = t2.y;
169 select * from t1 for system_time as of timestamp now(6);
170
171 insert into t1 values (1);
172 insert into t2 values (1);
173 update t1, t2 set x= 2, y= 2 where x = y;
174 select * from t1 for system_time as of timestamp now(6);
175
176 replace t2 select x + 1 from t1;
177 select * from t2;
178
179 insert t2 select x + 2 from t1;
180 select * from t2;
181
182 drop tables t1, t2;
183
184
185 --echo #
186 --echo # MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP
187 --echo #
188
189 SET sql_mode=TIME_ROUND_FRACTIONAL;
190 --enable_prepare_warnings
191 SET @@global.system_versioning_asof= timestamp'2001-12-31 23:59:59.9999999';
192 --disable_prepare_warnings
193 SELECT @@global.system_versioning_asof;
194 SET @@global.system_versioning_asof= DEFAULT;
195
196 --echo #
197 --echo # MDEV-23562 Assertion `time_type == MYSQL_TIMESTAMP_DATETIME' failed upon SELECT from versioned table
198 --echo #
199
200 CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
201 SET system_versioning_asof= DATE(NOW());
202 SELECT * FROM t1;
203 DROP TABLE t1;
204 SET system_versioning_asof= DEFAULT;
205
206 --echo # End of 10.4 tests