Debian Codegraph @

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

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