Debian Codegraph @

mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 #
3 # NUMERIC FUNCTIONS
4 #
5 # ABS()
6 set sql_warnings = 1;
7 create table t1 (a int, b int as (abs(a)));
8 show create table t1;
9 Table Create Table
10 t1 CREATE TABLE `t1` (
11 `a` int(11) DEFAULT NULL,
12 `b` int(11) GENERATED ALWAYS AS (abs(`a`)) VIRTUAL
13 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
14 insert into t1 values (-1, default);
15 select * from t1;
16 a b
17 -1 1
18 drop table t1;
19 set sql_warnings = 0;
20 # ACOS()
21 set sql_warnings = 1;
22 create table t1 (a double, b double as (format(acos(a),6)));
23 show create table t1;
24 Table Create Table
25 t1 CREATE TABLE `t1` (
26 `a` double DEFAULT NULL,
27 `b` double GENERATED ALWAYS AS (format(acos(`a`),6)) VIRTUAL
28 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
29 insert into t1 values (1, default);
30 insert ignore into t1 values (1.0001,default);
31 insert into t1 values (0,default);
32 select * from t1;
33 a b
34 1 0
35 1.0001 NULL
36 0 1.570796
37 drop table t1;
38 set sql_warnings = 0;
39 # ASIN()
40 set sql_warnings = 1;
41 create table t1 (a double, b double as (format(asin(a),6)));
42 show create table t1;
43 Table Create Table
44 t1 CREATE TABLE `t1` (
45 `a` double DEFAULT NULL,
46 `b` double GENERATED ALWAYS AS (format(asin(`a`),6)) VIRTUAL
47 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
48 insert into t1 values (0.2, default);
49 insert ignore into t1 values (1.0001,default);
50 select * from t1;
51 a b
52 0.2 0.201358
53 1.0001 NULL
54 drop table t1;
55 set sql_warnings = 0;
56 #ATAN
57 set sql_warnings = 1;
58 create table t1 (a double, b double, c double as (format(atan(a,b),6)));
59 show create table t1;
60 Table Create Table
61 t1 CREATE TABLE `t1` (
62 `a` double DEFAULT NULL,
63 `b` double DEFAULT NULL,
64 `c` double GENERATED ALWAYS AS (format(atan(`a`,`b`),6)) VIRTUAL
65 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
66 insert into t1 values (-2,2,default);
67 insert ignore into t1 values (format(PI(),6),0,default);
68 select * from t1;
69 a b c
70 -2 2 -0.785398
71 3.141593 0 1.570796
72 drop table t1;
73 set sql_warnings = 0;
74 set sql_warnings = 1;
75 create table t1 (a double, c double as (format(atan(a),6)));
76 show create table t1;
77 Table Create Table
78 t1 CREATE TABLE `t1` (
79 `a` double DEFAULT NULL,
80 `c` double GENERATED ALWAYS AS (format(atan(`a`),6)) VIRTUAL
81 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
82 insert into t1 values (-2,default);
83 insert ignore into t1 values (format(PI(),6),default);
84 select * from t1;
85 a c
86 -2 -1.107149
87 3.141593 1.262627
88 drop table t1;
89 set sql_warnings = 0;
90 # ATAN2
91 set sql_warnings = 1;
92 create table t1 (a double, b double, c double as (format(atan2(a,b),6)));
93 show create table t1;
94 Table Create Table
95 t1 CREATE TABLE `t1` (
96 `a` double DEFAULT NULL,
97 `b` double DEFAULT NULL,
98 `c` double GENERATED ALWAYS AS (format(atan(`a`,`b`),6)) VIRTUAL
99 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
100 insert into t1 values (-2,2,default);
101 insert ignore into t1 values (format(PI(),6),0,default);
102 select * from t1;
103 a b c
104 -2 2 -0.785398
105 3.141593 0 1.570796
106 drop table t1;
107 set sql_warnings = 0;
108 # CEIL()
109 set sql_warnings = 1;
110 create table t1 (a double, b int as (ceil(a)));
111 show create table t1;
112 Table Create Table
113 t1 CREATE TABLE `t1` (
114 `a` double DEFAULT NULL,
115 `b` int(11) GENERATED ALWAYS AS (ceiling(`a`)) VIRTUAL
116 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
117 insert into t1 values (1.23,default);
118 insert ignore into t1 values (-1.23,default);
119 select * from t1;
120 a b
121 1.23 2
122 -1.23 -1
123 drop table t1;
124 set sql_warnings = 0;
125 # CONV()
126 set sql_warnings = 1;
127 create table t1 (a varchar(10), b int, c int, d varchar(10) as (conv(a,b,c)));
128 show create table t1;
129 Table Create Table
130 t1 CREATE TABLE `t1` (
131 `a` varchar(10) DEFAULT NULL,
132 `b` int(11) DEFAULT NULL,
133 `c` int(11) DEFAULT NULL,
134 `d` varchar(10) GENERATED ALWAYS AS (conv(`a`,`b`,`c`)) VIRTUAL
135 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
136 insert into t1 values ('a',16,2,default);
137 insert ignore into t1 values ('6e',18,8,default);
138 insert into t1 values (-17,10,-18,default);
139 insert into t1 values (10+'10'+'10'+0xa,10,10,default);
140 select * from t1;
141 a b c d
142 a 16 2 1010
143 6e 18 8 172
144 -17 10 -18 -H
145 40 10 10 40
146 drop table t1;
147 set sql_warnings = 0;
148 # COS()
149 set sql_warnings = 1;
150 create table t1 (a double, b double as (format(cos(a),6)));
151 show create table t1;
152 Table Create Table
153 t1 CREATE TABLE `t1` (
154 `a` double DEFAULT NULL,
155 `b` double GENERATED ALWAYS AS (format(cos(`a`),6)) VIRTUAL
156 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
157 insert into t1 values (format(PI(),6),default);
158 select * from t1;
159 a b
160 3.141593 -1
161 drop table t1;
162 set sql_warnings = 0;
163 # COT()
164 set sql_warnings = 1;
165 create table t1 (a double, b double as (format(cot(a),6)));
166 show create table t1;
167 Table Create Table
168 t1 CREATE TABLE `t1` (
169 `a` double DEFAULT NULL,
170 `b` double GENERATED ALWAYS AS (format(cot(`a`),6)) VIRTUAL
171 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
172 insert into t1 values (12,default);
173 insert ignore into t1 values (1,default);
174 select * from t1;
175 a b
176 12 -1.572673
177 1 0.642093
178 drop table t1;
179 set sql_warnings = 0;
180 # CRC32()
181 set sql_warnings = 1;
182 create table t1 (a varchar(10), b long as (crc32(a)));
183 show create table t1;
184 Table Create Table
185 t1 CREATE TABLE `t1` (
186 `a` varchar(10) DEFAULT NULL,
187 `b` mediumtext GENERATED ALWAYS AS (crc32(`a`)) VIRTUAL
188 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
189 insert into t1 values ('MySQL',default);
190 insert ignore into t1 values ('mysql',default);
191 select * from t1;
192 a b
193 MySQL 3259397556
194 mysql 2501908538
195 drop table t1;
196 set sql_warnings = 0;
197 # DEGREES()
198 set sql_warnings = 1;
199 create table t1 (a double, b double as (format(degrees(a),6)));
200 show create table t1;
201 Table Create Table
202 t1 CREATE TABLE `t1` (
203 `a` double DEFAULT NULL,
204 `b` double GENERATED ALWAYS AS (format(degrees(`a`),6)) VIRTUAL
205 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
206 insert into t1 values (format(PI(),6),default);
207 insert ignore into t1 values (format(PI()/2,6),default);
208 select * from t1;
209 a b
210 3.141593 180.00002
211 1.570796 89.999981
212 drop table t1;
213 set sql_warnings = 0;
214 # /
215 set sql_warnings = 1;
216 create table t1 (a double, b double as (a/2));
217 show create table t1;
218 Table Create Table
219 t1 CREATE TABLE `t1` (
220 `a` double DEFAULT NULL,
221 `b` double GENERATED ALWAYS AS (`a` / 2) VIRTUAL
222 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
223 insert into t1 values (2,default);
224 select * from t1;
225 a b
226 2 1
227 drop table t1;
228 set sql_warnings = 0;
229 # EXP()
230 set sql_warnings = 1;
231 create table t1 (a double, b double as (format(exp(a),6)));
232 show create table t1;
233 Table Create Table
234 t1 CREATE TABLE `t1` (
235 `a` double DEFAULT NULL,
236 `b` double GENERATED ALWAYS AS (format(exp(`a`),6)) VIRTUAL
237 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
238 insert into t1 values (2,default);
239 insert ignore into t1 values (-2,default);
240 insert into t1 values (0,default);
241 select * from t1;
242 a b
243 2 7.389056
244 -2 0.135335
245 0 1
246 drop table t1;
247 set sql_warnings = 0;
248 # FLOOR()
249 set sql_warnings = 1;
250 create table t1 (a double, b long as (floor(a)));
251 show create table t1;
252 Table Create Table
253 t1 CREATE TABLE `t1` (
254 `a` double DEFAULT NULL,
255 `b` mediumtext GENERATED ALWAYS AS (floor(`a`)) VIRTUAL
256 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
257 insert into t1 values (1.23,default);
258 insert ignore into t1 values (-1.23,default);
259 select * from t1;
260 a b
261 1.23 1
262 -1.23 -2
263 drop table t1;
264 set sql_warnings = 0;
265 # LN()
266 set sql_warnings = 1;
267 create table t1 (a double, b double as (format(ln(a),6)));
268 show create table t1;
269 Table Create Table
270 t1 CREATE TABLE `t1` (
271 `a` double DEFAULT NULL,
272 `b` double GENERATED ALWAYS AS (format(ln(`a`),6)) VIRTUAL
273 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
274 insert into t1 values (2,default);
275 insert ignore into t1 values (-2,default);
276 Warnings:
277 Warning 1365 Division by 0
278 select * from t1;
279 a b
280 2 0.693147
281 -2 NULL
282 Warnings:
283 Warning 1365 Division by 0
284 drop table t1;
285 set sql_warnings = 0;
286 # LOG()
287 set sql_warnings = 1;
288 create table t1 (a double, b double, c double as (format(log(a,b),6)));
289 show create table t1;
290 Table Create Table
291 t1 CREATE TABLE `t1` (
292 `a` double DEFAULT NULL,
293 `b` double DEFAULT NULL,
294 `c` double GENERATED ALWAYS AS (format(log(`a`,`b`),6)) VIRTUAL
295 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
296 insert into t1 values (2,65536,default);
297 insert ignore into t1 values (10,100,default);
298 insert into t1 values (1,100,default);
299 Warnings:
300 Warning 1365 Division by 0
301 select * from t1;
302 a b c
303 2 65536 16
304 10 100 2
305 1 100 NULL
306 Warnings:
307 Warning 1365 Division by 0
308 drop table t1;
309 set sql_warnings = 0;
310 set sql_warnings = 1;
311 create table t1 (a double, b double as (format(log(a),6)));
312 show create table t1;
313 Table Create Table
314 t1 CREATE TABLE `t1` (
315 `a` double DEFAULT NULL,
316 `b` double GENERATED ALWAYS AS (format(log(`a`),6)) VIRTUAL
317 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
318 insert into t1 values (2,default);
319 insert ignore into t1 values (-2,default);
320 Warnings:
321 Warning 1365 Division by 0
322 select * from t1;
323 a b
324 2 0.693147
325 -2 NULL
326 Warnings:
327 Warning 1365 Division by 0
328 drop table t1;
329 set sql_warnings = 0;
330 # LOG2()
331 set sql_warnings = 1;
332 create table t1 (a double, b double as (format(log2(a),6)));
333 show create table t1;
334 Table Create Table
335 t1 CREATE TABLE `t1` (
336 `a` double DEFAULT NULL,
337 `b` double GENERATED ALWAYS AS (format(log2(`a`),6)) VIRTUAL
338 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
339 insert into t1 values (65536,default);
340 insert ignore into t1 values (-100,default);
341 Warnings:
342 Warning 1365 Division by 0
343 select * from t1;
344 a b
345 65536 16
346 -100 NULL
347 Warnings:
348 Warning 1365 Division by 0
349 drop table t1;
350 set sql_warnings = 0;
351 # LOG10()
352 set sql_warnings = 1;
353 create table t1 (a double, b double as (format(log10(a),6)));
354 show create table t1;
355 Table Create Table
356 t1 CREATE TABLE `t1` (
357 `a` double DEFAULT NULL,
358 `b` double GENERATED ALWAYS AS (format(log10(`a`),6)) VIRTUAL
359 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
360 insert into t1 values (2,default);
361 insert ignore into t1 values (100,default);
362 insert into t1 values (-100,default);
363 Warnings:
364 Warning 1365 Division by 0
365 select * from t1;
366 a b
367 2 0.30103
368 100 2
369 -100 NULL
370 Warnings:
371 Warning 1365 Division by 0
372 drop table t1;
373 set sql_warnings = 0;
374 # -
375 set sql_warnings = 1;
376 create table t1 (a double, b double as (a-1));
377 show create table t1;
378 Table Create Table
379 t1 CREATE TABLE `t1` (
380 `a` double DEFAULT NULL,
381 `b` double GENERATED ALWAYS AS (`a` - 1) VIRTUAL
382 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
383 insert into t1 values (2,default);
384 select * from t1;
385 a b
386 2 1
387 drop table t1;
388 set sql_warnings = 0;
389 # MOD()
390 set sql_warnings = 1;
391 create table t1 (a int, b int as (mod(a,10)));
392 show create table t1;
393 Table Create Table
394 t1 CREATE TABLE `t1` (
395 `a` int(11) DEFAULT NULL,
396 `b` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL
397 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
398 insert into t1 values (1,default);
399 insert ignore into t1 values (11,default);
400 select * from t1;
401 a b
402 1 1
403 11 1
404 drop table t1;
405 set sql_warnings = 0;
406 # %
407 set sql_warnings = 1;
408 create table t1 (a int, b int as (a % 10));
409 show create table t1;
410 Table Create Table
411 t1 CREATE TABLE `t1` (
412 `a` int(11) DEFAULT NULL,
413 `b` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL
414 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
415 insert into t1 values (1,default);
416 insert ignore into t1 values (11,default);
417 select * from t1;
418 a b
419 1 1
420 11 1
421 drop table t1;
422 set sql_warnings = 0;
423 # OCT()
424 set sql_warnings = 1;
425 create table t1 (a double, b varchar(10) as (oct(a)));
426 show create table t1;
427 Table Create Table
428 t1 CREATE TABLE `t1` (
429 `a` double DEFAULT NULL,
430 `b` varchar(10) GENERATED ALWAYS AS (conv(`a`,10,8)) VIRTUAL
431 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
432 insert into t1 values (12,default);
433 select * from t1;
434 a b
435 12 14
436 drop table t1;
437 set sql_warnings = 0;
438 # PI()
439 set sql_warnings = 1;
440 create table t1 (a double, b double as (format(PI()*a*a,6)));
441 show create table t1;
442 Table Create Table
443 t1 CREATE TABLE `t1` (
444 `a` double DEFAULT NULL,
445 `b` double GENERATED ALWAYS AS (format(pi() * `a` * `a`,6)) VIRTUAL
446 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
447 insert into t1 values (1,default);
448 select * from t1;
449 a b
450 1 3.141593
451 drop table t1;
452 set sql_warnings = 0;
453 # +
454 set sql_warnings = 1;
455 create table t1 (a int, b int as (a+1));
456 show create table t1;
457 Table Create Table
458 t1 CREATE TABLE `t1` (
459 `a` int(11) DEFAULT NULL,
460 `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL
461 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
462 insert into t1 values (1,default);
463 select * from t1;
464 a b
465 1 2
466 drop table t1;
467 set sql_warnings = 0;
468 # POW, POWER
469 set sql_warnings = 1;
470 create table t1 (a int, b int as (pow(a,2)), c int as (power(a,2)));
471 show create table t1;
472 Table Create Table
473 t1 CREATE TABLE `t1` (
474 `a` int(11) DEFAULT NULL,
475 `b` int(11) GENERATED ALWAYS AS (pow(`a`,2)) VIRTUAL,
476 `c` int(11) GENERATED ALWAYS AS (pow(`a`,2)) VIRTUAL
477 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
478 insert into t1 values (1,default,default);
479 insert ignore into t1 values (2,default,default);
480 select * from t1;
481 a b c
482 1 1 1
483 2 4 4
484 drop table t1;
485 set sql_warnings = 0;
486 # RADIANS()
487 set sql_warnings = 1;
488 create table t1 (a double, b double as (format(radians(a),6)));
489 show create table t1;
490 Table Create Table
491 t1 CREATE TABLE `t1` (
492 `a` double DEFAULT NULL,
493 `b` double GENERATED ALWAYS AS (format(radians(`a`),6)) VIRTUAL
494 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
495 insert into t1 values (90,default);
496 select * from t1;
497 a b
498 90 1.570796
499 drop table t1;
500 set sql_warnings = 0;
501 # ROUND()
502 set sql_warnings = 1;
503 create table t1 (a double, b int as (round(a)));
504 show create table t1;
505 Table Create Table
506 t1 CREATE TABLE `t1` (
507 `a` double DEFAULT NULL,
508 `b` int(11) GENERATED ALWAYS AS (round(`a`,0)) VIRTUAL
509 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
510 insert into t1 values (-1.23,default);
511 insert ignore into t1 values (-1.58,default);
512 insert into t1 values (1.58,default);
513 select * from t1;
514 a b
515 -1.23 -1
516 -1.58 -2
517 1.58 2
518 drop table t1;
519 set sql_warnings = 0;
520 set sql_warnings = 1;
521 create table t1 (a double, b double, c int as (round(a,b)));
522 show create table t1;
523 Table Create Table
524 t1 CREATE TABLE `t1` (
525 `a` double DEFAULT NULL,
526 `b` double DEFAULT NULL,
527 `c` int(11) GENERATED ALWAYS AS (round(`a`,`b`)) VIRTUAL
528 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
529 insert into t1 values (1.298,1,default);
530 insert ignore into t1 values (1.298,0,default);
531 insert into t1 values (23.298,-1,default);
532 select * from t1;
533 a b c
534 1.298 1 1
535 1.298 0 1
536 23.298 -1 20
537 drop table t1;
538 set sql_warnings = 0;
539 # SIGN()
540 set sql_warnings = 1;
541 create table t1 (a double, b int as (sign(a)));
542 show create table t1;
543 Table Create Table
544 t1 CREATE TABLE `t1` (
545 `a` double DEFAULT NULL,
546 `b` int(11) GENERATED ALWAYS AS (sign(`a`)) VIRTUAL
547 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
548 insert into t1 values (-32,default);
549 insert ignore into t1 values (0,default);
550 insert into t1 values (234,default);
551 select * from t1;
552 a b
553 -32 -1
554 0 0
555 234 1
556 drop table t1;
557 set sql_warnings = 0;
558 # SIN()
559 set sql_warnings = 1;
560 create table t1 (a double, b double as (format(sin(a),6)));
561 show create table t1;
562 Table Create Table
563 t1 CREATE TABLE `t1` (
564 `a` double DEFAULT NULL,
565 `b` double GENERATED ALWAYS AS (format(sin(`a`),6)) VIRTUAL
566 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
567 insert into t1 values (format(PI()/2,6),default);
568 select * from t1;
569 a b
570 1.570796 1
571 drop table t1;
572 set sql_warnings = 0;
573 # SQRT()
574 set sql_warnings = 1;
575 create table t1 (a double, b double as (format(sqrt(a),6)));
576 show create table t1;
577 Table Create Table
578 t1 CREATE TABLE `t1` (
579 `a` double DEFAULT NULL,
580 `b` double GENERATED ALWAYS AS (format(sqrt(`a`),6)) VIRTUAL
581 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
582 insert into t1 values (4,default);
583 insert ignore into t1 values (20,default);
584 insert into t1 values (-16,default);
585 select * from t1;
586 a b
587 4 2
588 20 4.472136
589 -16 NULL
590 drop table t1;
591 set sql_warnings = 0;
592 # TAN()
593 set sql_warnings = 1;
594 create table t1 (a double, b double as (format(tan(a),6)));
595 show create table t1;
596 Table Create Table
597 t1 CREATE TABLE `t1` (
598 `a` double DEFAULT NULL,
599 `b` double GENERATED ALWAYS AS (format(tan(`a`),6)) VIRTUAL
600 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
601 insert into t1 values (format(PI(),6),default);
602 insert ignore into t1 values (format(PI()+1,6),default);
603 select * from t1;
604 a b
605 3.141593 0
606 4.141593 1.557409
607 drop table t1;
608 set sql_warnings = 0;
609 # *
610 set sql_warnings = 1;
611 create table t1 (a double, b double as (a*3));
612 show create table t1;
613 Table Create Table
614 t1 CREATE TABLE `t1` (
615 `a` double DEFAULT NULL,
616 `b` double GENERATED ALWAYS AS (`a` * 3) VIRTUAL
617 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
618 insert into t1 values (0,default);
619 insert ignore into t1 values (1,default);
620 insert into t1 values (2,default);
621 select * from t1;
622 a b
623 0 0
624 1 3
625 2 6
626 drop table t1;
627 set sql_warnings = 0;
628 # TRUNCATE()
629 set sql_warnings = 1;
630 create table t1 (a double, b double as (truncate(a,4)));
631 show create table t1;
632 Table Create Table
633 t1 CREATE TABLE `t1` (
634 `a` double DEFAULT NULL,
635 `b` double GENERATED ALWAYS AS (truncate(`a`,4)) VIRTUAL
636 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
637 insert into t1 values (1.223,default);
638 insert ignore into t1 values (1.999,default);
639 insert into t1 values (1.999,default);
640 insert into t1 values (122,default);
641 select * from t1;
642 a b
643 1.223 1.223
644 1.999 1.999
645 1.999 1.999
646 122 122
647 drop table t1;
648 set sql_warnings = 0;
649 # Unary -
650 set sql_warnings = 1;
651 create table t1 (a double, b double as (-a));
652 show create table t1;
653 Table Create Table
654 t1 CREATE TABLE `t1` (
655 `a` double DEFAULT NULL,
656 `b` double GENERATED ALWAYS AS (-`a`) VIRTUAL
657 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
658 insert into t1 values (1,default);
659 insert ignore into t1 values (-1,default);
660 select * from t1;
661 a b
662 1 -1
663 -1 1
664 drop table t1;
665 set sql_warnings = 0;
666 #
667 # STRING FUNCTIONS
668 #
669 # ASCII()
670 set sql_warnings = 1;
671 create table t1 (a char(2), b int as (ascii(a)));
672 show create table t1;
673 Table Create Table
674 t1 CREATE TABLE `t1` (
675 `a` char(2) DEFAULT NULL,
676 `b` int(11) GENERATED ALWAYS AS (ascii(`a`)) VIRTUAL
677 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
678 insert into t1 values ('2',default);
679 insert ignore into t1 values (2,default);
680 insert into t1 values ('dx',default);
681 select * from t1;
682 a b
683 2 50
684 2 50
685 dx 100
686 drop table t1;
687 set sql_warnings = 0;
688 # BIN()
689 set sql_warnings = 1;
690 create table t1 (a int, b varchar(10) as (bin(a)));
691 show create table t1;
692 Table Create Table
693 t1 CREATE TABLE `t1` (
694 `a` int(11) DEFAULT NULL,
695 `b` varchar(10) GENERATED ALWAYS AS (conv(`a`,10,2)) VIRTUAL
696 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
697 insert into t1 values (12,default);
698 select * from t1;
699 a b
700 12 1100
701 drop table t1;
702 set sql_warnings = 0;
703 # BIT_LENGTH()
704 set sql_warnings = 1;
705 create table t1 (a varchar(10), b long as (bit_length(a)));
706 show create table t1;
707 Table Create Table
708 t1 CREATE TABLE `t1` (
709 `a` varchar(10) DEFAULT NULL,
710 `b` mediumtext GENERATED ALWAYS AS (bit_length(`a`)) VIRTUAL
711 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
712 insert into t1 values ('text',default);
713 select * from t1;
714 a b
715 text 32
716 drop table t1;
717 set sql_warnings = 0;
718 # CHAR_LENGTH()
719 set sql_warnings = 1;
720 create table t1 (a varchar(10), b long as (char_length(a)));
721 show create table t1;
722 Table Create Table
723 t1 CREATE TABLE `t1` (
724 `a` varchar(10) DEFAULT NULL,
725 `b` mediumtext GENERATED ALWAYS AS (char_length(`a`)) VIRTUAL
726 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
727 insert into t1 values ('text',default);
728 select * from t1;
729 a b
730 text 4
731 drop table t1;
732 set sql_warnings = 0;
733 # CHAR()
734 set sql_warnings = 1;
735 create table t1 (a int, b int, c varbinary(10) as (char(a,b)));
736 show create table t1;
737 Table Create Table
738 t1 CREATE TABLE `t1` (
739 `a` int(11) DEFAULT NULL,
740 `b` int(11) DEFAULT NULL,
741 `c` varbinary(10) GENERATED ALWAYS AS (char(`a`,`b`)) VIRTUAL
742 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
743 insert into t1 values (77,121,default);
744 select * from t1;
745 a b c
746 77 121 My
747 drop table t1;
748 set sql_warnings = 0;
749 # CHARACTER_LENGTH()
750 set sql_warnings = 1;
751 create table t1 (a varchar(10), b long as (character_length(a)));
752 show create table t1;
753 Table Create Table
754 t1 CREATE TABLE `t1` (
755 `a` varchar(10) DEFAULT NULL,
756 `b` mediumtext GENERATED ALWAYS AS (char_length(`a`)) VIRTUAL
757 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
758 insert into t1 values ('text',default);
759 select * from t1;
760 a b
761 text 4
762 drop table t1;
763 set sql_warnings = 0;
764 # CONCAT_WS()
765 set sql_warnings = 1;
766 create table t1 (a varchar(10), b varchar(10), c varchar(20) as (concat_ws(',',a,b)));
767 show create table t1;
768 Table Create Table
769 t1 CREATE TABLE `t1` (
770 `a` varchar(10) DEFAULT NULL,
771 `b` varchar(10) DEFAULT NULL,
772 `c` varchar(20) GENERATED ALWAYS AS (concat_ws(',',`a`,`b`)) VIRTUAL
773 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
774 insert into t1 values ('value1','value2',default);
775 select * from t1;
776 a b c
777 value1 value2 value1,value2
778 drop table t1;
779 set sql_warnings = 0;
780 # CONCAT()
781 set sql_warnings = 1;
782 create table t1 (a varchar(10), b varchar(10), c varchar(20) as (concat(a,',',b)));
783 show create table t1;
784 Table Create Table
785 t1 CREATE TABLE `t1` (
786 `a` varchar(10) DEFAULT NULL,
787 `b` varchar(10) DEFAULT NULL,
788 `c` varchar(20) GENERATED ALWAYS AS (concat(`a`,',',`b`)) VIRTUAL
789 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
790 insert into t1 values ('value1','value2',default);
791 select * from t1;
792 a b c
793 value1 value2 value1,value2
794 drop table t1;
795 set sql_warnings = 0;
796 # ELT()
797 set sql_warnings = 1;
798 create table t1 (a varchar(10), b varchar(10), c int, d varchar(10) as (elt(c,a,b)));
799 show create table t1;
800 Table Create Table
801 t1 CREATE TABLE `t1` (
802 `a` varchar(10) DEFAULT NULL,
803 `b` varchar(10) DEFAULT NULL,
804 `c` int(11) DEFAULT NULL,
805 `d` varchar(10) GENERATED ALWAYS AS (elt(`c`,`a`,`b`)) VIRTUAL
806 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
807 insert into t1 values ('value1','value2',1,default);
808 insert ignore into t1 values ('value1','value2',2,default);
809 select * from t1;
810 a b c d
811 value1 value2 1 value1
812 value1 value2 2 value2
813 drop table t1;
814 set sql_warnings = 0;
815 # EXPORT_SET()
816 set sql_warnings = 1;
817 create table t1 (a int, b varchar(10) as (export_set(a,'1','0','',10)));
818 show create table t1;
819 Table Create Table
820 t1 CREATE TABLE `t1` (
821 `a` int(11) DEFAULT NULL,
822 `b` varchar(10) GENERATED ALWAYS AS (export_set(`a`,'1','0','',10)) VIRTUAL
823 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
824 insert into t1 values (6,default);
825 select * from t1;
826 a b
827 6 0110000000
828 drop table t1;
829 set sql_warnings = 0;
830 # FIELD()
831 set sql_warnings = 1;
832 create table t1 (a varchar(10), b varchar(10), c int as (field('aa',a,b)));
833 show create table t1;
834 Table Create Table
835 t1 CREATE TABLE `t1` (
836 `a` varchar(10) DEFAULT NULL,
837 `b` varchar(10) DEFAULT NULL,
838 `c` int(11) GENERATED ALWAYS AS (field('aa',`a`,`b`)) VIRTUAL
839 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
840 insert into t1 values ('aa','bb',default);
841 insert ignore into t1 values ('bb','aa',default);
842 select * from t1;
843 a b c
844 aa bb 1
845 bb aa 2
846 drop table t1;
847 set sql_warnings = 0;
848 # FIND_IN_SET()
849 set sql_warnings = 1;
850 create table t1 (a varchar(10), b varchar(10), c int as (find_in_set(a,b)));
851 show create table t1;
852 Table Create Table
853 t1 CREATE TABLE `t1` (
854 `a` varchar(10) DEFAULT NULL,
855 `b` varchar(10) DEFAULT NULL,
856 `c` int(11) GENERATED ALWAYS AS (find_in_set(`a`,`b`)) VIRTUAL
857 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
858 insert into t1 values ('aa','aa,bb,cc',default);
859 insert ignore into t1 values ('aa','bb,aa,cc',default);
860 select * from t1;
861 a b c
862 aa aa,bb,cc 1
863 aa bb,aa,cc 2
864 drop table t1;
865 set sql_warnings = 0;
866 # FORMAT()
867 set sql_warnings = 1;
868 create table t1 (a double, b varchar(20) as (format(a,2)));
869 show create table t1;
870 Table Create Table
871 t1 CREATE TABLE `t1` (
872 `a` double DEFAULT NULL,
873 `b` varchar(20) GENERATED ALWAYS AS (format(`a`,2)) VIRTUAL
874 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
875 insert into t1 values (12332.123456,default);
876 select * from t1;
877 a b
878 12332.123456 12,332.12
879 drop table t1;
880 set sql_warnings = 0;
881 # HEX()
882 set sql_warnings = 1;
883 create table t1 (a int, b varchar(10) as (hex(a)));
884 show create table t1;
885 Table Create Table
886 t1 CREATE TABLE `t1` (
887 `a` int(11) DEFAULT NULL,
888 `b` varchar(10) GENERATED ALWAYS AS (hex(`a`)) VIRTUAL
889 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
890 insert into t1 values (17,default);
891 select * from t1;
892 a b
893 17 11
894 drop table t1;
895 set sql_warnings = 0;
896 set sql_warnings = 1;
897 create table t1 (a varchar(10), b varchar(10) as (hex(a)));
898 show create table t1;
899 Table Create Table
900 t1 CREATE TABLE `t1` (
901 `a` varchar(10) DEFAULT NULL,
902 `b` varchar(10) GENERATED ALWAYS AS (hex(`a`)) VIRTUAL
903 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
904 insert into t1 values ('abc',default);
905 select * from t1;
906 a b
907 abc 616263
908 drop table t1;
909 set sql_warnings = 0;
910 # INSERT()
911 set sql_warnings = 1;
912 create table t1 (a varchar(10), b varchar(10), c varchar(20) as (insert(a,length(a),length(b),b)));
913 show create table t1;
914 Table Create Table
915 t1 CREATE TABLE `t1` (
916 `a` varchar(10) DEFAULT NULL,
917 `b` varchar(10) DEFAULT NULL,
918 `c` varchar(20) GENERATED ALWAYS AS (insert(`a`,octet_length(`a`),octet_length(`b`),`b`)) VIRTUAL
919 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
920 insert into t1 values ('start,','end',default);
921 select * from t1;
922 a b c
923 start, end startend
924 drop table t1;
925 set sql_warnings = 0;
926 # INSTR()
927 set sql_warnings = 1;
928 create table t1 (a varchar(10), b varchar(10), c int as (instr(a,b)));
929 show create table t1;
930 Table Create Table
931 t1 CREATE TABLE `t1` (
932 `a` varchar(10) DEFAULT NULL,
933 `b` varchar(10) DEFAULT NULL,
934 `c` int(11) GENERATED ALWAYS AS (locate(`b`,`a`)) VIRTUAL
935 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
936 insert into t1 values ('foobarbar,','bar',default);
937 insert ignore into t1 values ('xbar,','foobar',default);
938 select * from t1;
939 a b c
940 foobarbar, bar 4
941 xbar, foobar 0
942 drop table t1;
943 set sql_warnings = 0;
944 # LCASE()
945 set sql_warnings = 1;
946 create table t1 (a varchar(10), b varchar(10) as (lcase(a)));
947 show create table t1;
948 Table Create Table
949 t1 CREATE TABLE `t1` (
950 `a` varchar(10) DEFAULT NULL,
951 `b` varchar(10) GENERATED ALWAYS AS (lcase(`a`)) VIRTUAL
952 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
953 insert into t1 values ('MySQL',default);
954 select * from t1;
955 a b
956 MySQL mysql
957 drop table t1;
958 set sql_warnings = 0;
959 # LEFT()
960 set sql_warnings = 1;
961 create table t1 (a varchar(10), b varchar(5) as (left(a,5)));
962 show create table t1;
963 Table Create Table
964 t1 CREATE TABLE `t1` (
965 `a` varchar(10) DEFAULT NULL,
966 `b` varchar(5) GENERATED ALWAYS AS (left(`a`,5)) VIRTUAL
967 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
968 insert into t1 values ('foobarbar',default);
969 select * from t1;
970 a b
971 foobarbar fooba
972 drop table t1;
973 set sql_warnings = 0;
974 # LENGTH()
975 set sql_warnings = 1;
976 create table t1 (a varchar(10), b int as (length(a)));
977 show create table t1;
978 Table Create Table
979 t1 CREATE TABLE `t1` (
980 `a` varchar(10) DEFAULT NULL,
981 `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL
982 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
983 insert into t1 values ('text',default);
984 select * from t1;
985 a b
986 text 4
987 drop table t1;
988 set sql_warnings = 0;
989 # LIKE
990 set sql_warnings = 1;
991 create table t1 (a varchar(10), b bool as (a like 'H%!o' escape '!'));
992 show create table t1;
993 Table Create Table
994 t1 CREATE TABLE `t1` (
995 `a` varchar(10) DEFAULT NULL,
996 `b` tinyint(1) GENERATED ALWAYS AS (`a` like 'H%!o' escape '!') VIRTUAL
997 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
998 insert into t1 values ('Hello',default);
999 insert ignore into t1 values ('MySQL',default);
1000 select * from t1;
1001 a b
1002 Hello 1
1003 MySQL 0
1004 drop table t1;
1005 set sql_warnings = 0;
1006 # LOCATE()
1007 set sql_warnings = 1;
1008 create table t1 (a varchar(10), b varchar(10) as (locate('bar',a)));
1009 show create table t1;
1010 Table Create Table
1011 t1 CREATE TABLE `t1` (
1012 `a` varchar(10) DEFAULT NULL,
1013 `b` varchar(10) GENERATED ALWAYS AS (locate('bar',`a`)) VIRTUAL
1014 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1015 insert into t1 values ('foobarbar',default);
1016 select * from t1;
1017 a b
1018 foobarbar 4
1019 drop table t1;
1020 set sql_warnings = 0;
1021 # LOWER()
1022 set sql_warnings = 1;
1023 create table t1 (a varchar(10), b varchar(10) as (lower(a)));
1024 show create table t1;
1025 Table Create Table
1026 t1 CREATE TABLE `t1` (
1027 `a` varchar(10) DEFAULT NULL,
1028 `b` varchar(10) GENERATED ALWAYS AS (lcase(`a`)) VIRTUAL
1029 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1030 insert into t1 values ('MySQL',default);
1031 select * from t1;
1032 a b
1033 MySQL mysql
1034 drop table t1;
1035 set sql_warnings = 0;
1036 # LPAD()
1037 set sql_warnings = 1;
1038 create table t1 (a varchar(10), b varchar(10) as (lpad(a,4,' ')));
1039 show create table t1;
1040 Table Create Table
1041 t1 CREATE TABLE `t1` (
1042 `a` varchar(10) DEFAULT NULL,
1043 `b` varchar(10) GENERATED ALWAYS AS (lpad(`a`,4,' ')) VIRTUAL
1044 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1045 insert into t1 values ('MySQL',default);
1046 insert ignore into t1 values ('M',default);
1047 select * from t1;
1048 a b
1049 MySQL MySQ
1050 M M
1051 drop table t1;
1052 set sql_warnings = 0;
1053 # LTRIM()
1054 set sql_warnings = 1;
1055 create table t1 (a varchar(10), b varchar(10) as (ltrim(a)));
1056 show create table t1;
1057 Table Create Table
1058 t1 CREATE TABLE `t1` (
1059 `a` varchar(10) DEFAULT NULL,
1060 `b` varchar(10) GENERATED ALWAYS AS (ltrim(`a`)) VIRTUAL
1061 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1062 insert into t1 values (' MySQL',default);
1063 insert ignore into t1 values ('MySQL',default);
1064 select * from t1;
1065 a b
1066 MySQL MySQL
1067 MySQL MySQL
1068 drop table t1;
1069 set sql_warnings = 0;
1070 # MAKE_SET()
1071 set sql_warnings = 1;
1072 create table t1 (a varchar(10), b varchar(10), c int, d varchar(30) as (make_set(c,a,b)));
1073 show create table t1;
1074 Table Create Table
1075 t1 CREATE TABLE `t1` (
1076 `a` varchar(10) DEFAULT NULL,
1077 `b` varchar(10) DEFAULT NULL,
1078 `c` int(11) DEFAULT NULL,
1079 `d` varchar(30) GENERATED ALWAYS AS (make_set(`c`,`a`,`b`)) VIRTUAL
1080 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1081 insert into t1 values ('a','b',1,default);
1082 insert ignore into t1 values ('a','b',3,default);
1083 select * from t1;
1084 a b c d
1085 a b 1 a
1086 a b 3 a,b
1087 drop table t1;
1088 set sql_warnings = 0;
1089 # MID()
1090 set sql_warnings = 1;
1091 create table t1 (a varchar(10), b varchar(10) as (mid(a,1,2)));
1092 show create table t1;
1093 Table Create Table
1094 t1 CREATE TABLE `t1` (
1095 `a` varchar(10) DEFAULT NULL,
1096 `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,1,2)) VIRTUAL
1097 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1098 insert into t1 values ('foobarbar',default);
1099 select * from t1;
1100 a b
1101 foobarbar fo
1102 drop table t1;
1103 set sql_warnings = 0;
1104 # NOT LIKE
1105 set sql_warnings = 1;
1106 create table t1 (a varchar(10), b bool as (a not like 'H%o'));
1107 show create table t1;
1108 Table Create Table
1109 t1 CREATE TABLE `t1` (
1110 `a` varchar(10) DEFAULT NULL,
1111 `b` tinyint(1) GENERATED ALWAYS AS (`a` not like 'H%o') VIRTUAL
1112 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1113 insert into t1 values ('Hello',default);
1114 insert ignore into t1 values ('MySQL',default);
1115 select * from t1;
1116 a b
1117 Hello 0
1118 MySQL 1
1119 drop table t1;
1120 set sql_warnings = 0;
1121 # NOT REGEXP
1122 set sql_warnings = 1;
1123 create table t1 (a varchar(10), b bool as (a not regexp 'H.+o'));
1124 show create table t1;
1125 Table Create Table
1126 t1 CREATE TABLE `t1` (
1127 `a` varchar(10) DEFAULT NULL,
1128 `b` tinyint(1) GENERATED ALWAYS AS (!(`a` regexp 'H.+o')) VIRTUAL
1129 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1130 insert into t1 values ('Hello',default);
1131 insert ignore into t1 values ('hello',default);
1132 select * from t1;
1133 a b
1134 Hello 0
1135 hello 0
1136 drop table t1;
1137 set sql_warnings = 0;
1138 # OCTET_LENGTH()
1139 set sql_warnings = 1;
1140 create table t1 (a varchar(10), b int as (octet_length(a)));
1141 show create table t1;
1142 Table Create Table
1143 t1 CREATE TABLE `t1` (
1144 `a` varchar(10) DEFAULT NULL,
1145 `b` int(11) GENERATED ALWAYS AS (octet_length(`a`)) VIRTUAL
1146 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1147 insert into t1 values ('text',default);
1148 select * from t1;
1149 a b
1150 text 4
1151 drop table t1;
1152 set sql_warnings = 0;
1153 # ORD()
1154 set sql_warnings = 1;
1155 create table t1 (a varchar(10), b long as (ord(a)));
1156 show create table t1;
1157 Table Create Table
1158 t1 CREATE TABLE `t1` (
1159 `a` varchar(10) DEFAULT NULL,
1160 `b` mediumtext GENERATED ALWAYS AS (ord(`a`)) VIRTUAL
1161 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1162 insert into t1 values ('2',default);
1163 select * from t1;
1164 a b
1165 2 50
1166 drop table t1;
1167 set sql_warnings = 0;
1168 # POSITION()
1169 set sql_warnings = 1;
1170 create table t1 (a varchar(10), b varchar(10) as (position('bar' in a)));
1171 show create table t1;
1172 Table Create Table
1173 t1 CREATE TABLE `t1` (
1174 `a` varchar(10) DEFAULT NULL,
1175 `b` varchar(10) GENERATED ALWAYS AS (locate('bar',`a`)) VIRTUAL
1176 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1177 insert into t1 values ('foobarbar',default);
1178 select * from t1;
1179 a b
1180 foobarbar 4
1181 drop table t1;
1182 set sql_warnings = 0;
1183 # QUOTE()
1184 set sql_warnings = 1;
1185 create table t1 (a varchar(10), b varchar(10) as (quote(a)));
1186 show create table t1;
1187 Table Create Table
1188 t1 CREATE TABLE `t1` (
1189 `a` varchar(10) DEFAULT NULL,
1190 `b` varchar(10) GENERATED ALWAYS AS (quote(`a`)) VIRTUAL
1191 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1192 insert into t1 values ('Don\'t',default);
1193 select * from t1;
1194 a b
1195 Don't 'Don\'t'
1196 drop table t1;
1197 set sql_warnings = 0;
1198 # REGEXP()
1199 set sql_warnings = 1;
1200 create table t1 (a varchar(10), b bool as (a regexp 'H.+o'));
1201 show create table t1;
1202 Table Create Table
1203 t1 CREATE TABLE `t1` (
1204 `a` varchar(10) DEFAULT NULL,
1205 `b` tinyint(1) GENERATED ALWAYS AS (`a` regexp 'H.+o') VIRTUAL
1206 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1207 insert into t1 values ('Hello',default);
1208 insert ignore into t1 values ('hello',default);
1209 select * from t1;
1210 a b
1211 Hello 1
1212 hello 1
1213 drop table t1;
1214 set sql_warnings = 0;
1215 # REPEAT()
1216 set sql_warnings = 1;
1217 create table t1 (a varchar(10), b varchar(30) as (repeat(a,3)));
1218 show create table t1;
1219 Table Create Table
1220 t1 CREATE TABLE `t1` (
1221 `a` varchar(10) DEFAULT NULL,
1222 `b` varchar(30) GENERATED ALWAYS AS (repeat(`a`,3)) VIRTUAL
1223 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1224 insert into t1 values ('MySQL',default);
1225 select * from t1;
1226 a b
1227 MySQL MySQLMySQLMySQL
1228 drop table t1;
1229 set sql_warnings = 0;
1230 # REPLACE()
1231 set sql_warnings = 1;
1232 create table t1 (a varchar(10), b varchar(30) as (replace(a,'aa','bb')));
1233 show create table t1;
1234 Table Create Table
1235 t1 CREATE TABLE `t1` (
1236 `a` varchar(10) DEFAULT NULL,
1237 `b` varchar(30) GENERATED ALWAYS AS (replace(`a`,'aa','bb')) VIRTUAL
1238 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1239 insert into t1 values ('maa',default);
1240 select * from t1;
1241 a b
1242 maa mbb
1243 drop table t1;
1244 set sql_warnings = 0;
1245 # REVERSE()
1246 set sql_warnings = 1;
1247 create table t1 (a varchar(10), b varchar(30) as (reverse(a)));
1248 show create table t1;
1249 Table Create Table
1250 t1 CREATE TABLE `t1` (
1251 `a` varchar(10) DEFAULT NULL,
1252 `b` varchar(30) GENERATED ALWAYS AS (reverse(`a`)) VIRTUAL
1253 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1254 insert into t1 values ('maa',default);
1255 select * from t1;
1256 a b
1257 maa aam
1258 drop table t1;
1259 set sql_warnings = 0;
1260 # RIGHT()
1261 set sql_warnings = 1;
1262 create table t1 (a varchar(10), b varchar(10) as (right(a,4)));
1263 show create table t1;
1264 Table Create Table
1265 t1 CREATE TABLE `t1` (
1266 `a` varchar(10) DEFAULT NULL,
1267 `b` varchar(10) GENERATED ALWAYS AS (right(`a`,4)) VIRTUAL
1268 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1269 insert into t1 values ('foobarbar',default);
1270 select * from t1;
1271 a b
1272 foobarbar rbar
1273 drop table t1;
1274 set sql_warnings = 0;
1275 # RLIKE()
1276 set sql_warnings = 1;
1277 create table t1 (a varchar(10), b bool as (a rlike 'H.+o'));
1278 show create table t1;
1279 Table Create Table
1280 t1 CREATE TABLE `t1` (
1281 `a` varchar(10) DEFAULT NULL,
1282 `b` tinyint(1) GENERATED ALWAYS AS (`a` regexp 'H.+o') VIRTUAL
1283 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1284 insert into t1 values ('Hello',default);
1285 insert ignore into t1 values ('MySQL',default);
1286 select * from t1;
1287 a b
1288 Hello 1
1289 MySQL 0
1290 drop table t1;
1291 set sql_warnings = 0;
1292 # RPAD()
1293 set sql_warnings = 1;
1294 create table t1 (a varchar(10), b varchar(10) as (rpad(a,4,'??')));
1295 show create table t1;
1296 Table Create Table
1297 t1 CREATE TABLE `t1` (
1298 `a` varchar(10) DEFAULT NULL,
1299 `b` varchar(10) GENERATED ALWAYS AS (rpad(`a`,4,'??')) VIRTUAL
1300 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1301 insert into t1 values ('He',default);
1302 select * from t1;
1303 a b
1304 He He??
1305 drop table t1;
1306 set sql_warnings = 0;
1307 # RTRIM();
1308 set sql_warnings = 1;
1309 create table t1 (a varchar(10), b varchar(10) as (rtrim(a)));
1310 show create table t1;
1311 Table Create Table
1312 t1 CREATE TABLE `t1` (
1313 `a` varchar(10) DEFAULT NULL,
1314 `b` varchar(10) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL
1315 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1316 insert into t1 values ('Hello ',default);
1317 select * from t1;
1318 a b
1319 Hello Hello
1320 drop table t1;
1321 set sql_warnings = 0;
1322 # SOUNDEX()
1323 set sql_warnings = 1;
1324 create table t1 (a varchar(10), b varchar(20) as (soundex(a)));
1325 show create table t1;
1326 Table Create Table
1327 t1 CREATE TABLE `t1` (
1328 `a` varchar(10) DEFAULT NULL,
1329 `b` varchar(20) GENERATED ALWAYS AS (soundex(`a`)) VIRTUAL
1330 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1331 insert into t1 values ('Hello',default);
1332 select * from t1;
1333 a b
1334 Hello H400
1335 drop table t1;
1336 set sql_warnings = 0;
1337 # SOUNDS LIKE
1338 set sql_warnings = 1;
1339 create table t1 (a varchar(10), b varchar(10), c bool as (a sounds like b));
1340 show create table t1;
1341 Table Create Table
1342 t1 CREATE TABLE `t1` (
1343 `a` varchar(10) DEFAULT NULL,
1344 `b` varchar(10) DEFAULT NULL,
1345 `c` tinyint(1) GENERATED ALWAYS AS (soundex(`a`) = soundex(`b`)) VIRTUAL
1346 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1347 insert into t1 values ('Hello','Hello',default);
1348 insert ignore into t1 values ('Hello','MySQL',default);
1349 insert into t1 values ('Hello','hello',default);
1350 select * from t1;
1351 a b c
1352 Hello Hello 1
1353 Hello MySQL 0
1354 Hello hello 1
1355 drop table t1;
1356 set sql_warnings = 0;
1357 # SPACE()
1358 set sql_warnings = 1;
1359 create table t1 (a varchar(5), b varchar(10) as (concat(a,space(5))));
1360 show create table t1;
1361 Table Create Table
1362 t1 CREATE TABLE `t1` (
1363 `a` varchar(5) DEFAULT NULL,
1364 `b` varchar(10) GENERATED ALWAYS AS (concat(`a`,space(5))) VIRTUAL
1365 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1366 insert into t1 values ('Hello', default);
1367 select * from t1;
1368 a b
1369 Hello Hello
1370 drop table t1;
1371 set sql_warnings = 0;
1372 # STRCMP()
1373 set sql_warnings = 1;
1374 create table t1 (a varchar(9), b varchar(9), c tinyint(1) as (strcmp(a,b)));
1375 show create table t1;
1376 Table Create Table
1377 t1 CREATE TABLE `t1` (
1378 `a` varchar(9) DEFAULT NULL,
1379 `b` varchar(9) DEFAULT NULL,
1380 `c` tinyint(1) GENERATED ALWAYS AS (strcmp(`a`,`b`)) VIRTUAL
1381 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1382 insert into t1 values ('Hello','Hello', default);
1383 insert ignore into t1 values ('Hello','Hello1', default);
1384 select * from t1;
1385 a b c
1386 Hello Hello 0
1387 Hello Hello1 -1
1388 drop table t1;
1389 set sql_warnings = 0;
1390 # SUBSTR()
1391 set sql_warnings = 1;
1392 create table t1 (a varchar(5), b varchar(10) as (substr(a,2)));
1393 show create table t1;
1394 Table Create Table
1395 t1 CREATE TABLE `t1` (
1396 `a` varchar(5) DEFAULT NULL,
1397 `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,2)) VIRTUAL
1398 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1399 insert into t1 values ('Hello',default);
1400 select * from t1;
1401 a b
1402 Hello ello
1403 drop table t1;
1404 set sql_warnings = 0;
1405 # SUBSTRING_INDEX()
1406 set sql_warnings = 1;
1407 create table t1 (a varchar(15), b varchar(10) as (substring_index(a,'.',2)));
1408 show create table t1;
1409 Table Create Table
1410 t1 CREATE TABLE `t1` (
1411 `a` varchar(15) DEFAULT NULL,
1412 `b` varchar(10) GENERATED ALWAYS AS (substring_index(`a`,'.',2)) VIRTUAL
1413 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1414 insert into t1 values ('www.mysql.com',default);
1415 select * from t1;
1416 a b
1417 www.mysql.com www.mysql
1418 drop table t1;
1419 set sql_warnings = 0;
1420 # SUBSTRING()
1421 set sql_warnings = 1;
1422 create table t1 (a varchar(5), b varchar(10) as (substring(a from 2 for 2)));
1423 show create table t1;
1424 Table Create Table
1425 t1 CREATE TABLE `t1` (
1426 `a` varchar(5) DEFAULT NULL,
1427 `b` varchar(10) GENERATED ALWAYS AS (substr(`a`,2,2)) VIRTUAL
1428 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1429 insert into t1 values ('Hello',default);
1430 select * from t1;
1431 a b
1432 Hello el
1433 drop table t1;
1434 set sql_warnings = 0;
1435 # TRIM()
1436 set sql_warnings = 1;
1437 create table t1 (a varchar(15), b varchar(10) as (trim(a)));
1438 show create table t1;
1439 Table Create Table
1440 t1 CREATE TABLE `t1` (
1441 `a` varchar(15) DEFAULT NULL,
1442 `b` varchar(10) GENERATED ALWAYS AS (trim(`a`)) VIRTUAL
1443 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1444 insert into t1 values (' aa ',default);
1445 select * from t1;
1446 a b
1447 aa aa
1448 drop table t1;
1449 set sql_warnings = 0;
1450 # UCASE()
1451 set sql_warnings = 1;
1452 create table t1 (a varchar(5), b varchar(10) as (ucase(a)));
1453 show create table t1;
1454 Table Create Table
1455 t1 CREATE TABLE `t1` (
1456 `a` varchar(5) DEFAULT NULL,
1457 `b` varchar(10) GENERATED ALWAYS AS (ucase(`a`)) VIRTUAL
1458 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1459 insert into t1 values ('MySQL',default);
1460 select * from t1;
1461 a b
1462 MySQL MYSQL
1463 drop table t1;
1464 set sql_warnings = 0;
1465 # UNHEX()
1466 set sql_warnings = 1;
1467 create table t1 (a varchar(15), b varchar(10) as (unhex(a)));
1468 show create table t1;
1469 Table Create Table
1470 t1 CREATE TABLE `t1` (
1471 `a` varchar(15) DEFAULT NULL,
1472 `b` varchar(10) GENERATED ALWAYS AS (unhex(`a`)) VIRTUAL
1473 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1474 insert into t1 values ('4D7953514C',default);
1475 select * from t1;
1476 a b
1477 4D7953514C MySQL
1478 drop table t1;
1479 set sql_warnings = 0;
1480 # UPPER()
1481 set sql_warnings = 1;
1482 create table t1 (a varchar(5), b varchar(10) as (upper(a)));
1483 show create table t1;
1484 Table Create Table
1485 t1 CREATE TABLE `t1` (
1486 `a` varchar(5) DEFAULT NULL,
1487 `b` varchar(10) GENERATED ALWAYS AS (ucase(`a`)) VIRTUAL
1488 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1489 insert into t1 values ('MySQL',default);
1490 select * from t1;
1491 a b
1492 MySQL MYSQL
1493 drop table t1;
1494 set sql_warnings = 0;
1495 #
1496 # CONTROL FLOW FUNCTIONS
1497 #
1498 # CASE
1499 set sql_warnings = 1;
1500 create table t1 (a varchar(10), b varchar(16) as (case a when NULL then 'asd' when 'b' then 'B' else a end));
1501 show create table t1;
1502 Table Create Table
1503 t1 CREATE TABLE `t1` (
1504 `a` varchar(10) DEFAULT NULL,
1505 `b` varchar(16) GENERATED ALWAYS AS (case `a` when NULL then 'asd' when 'b' then 'B' else `a` end) VIRTUAL
1506 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1507 insert into t1 values (NULL,default);
1508 insert ignore into t1 values ('b',default);
1509 insert into t1 values ('c',default);
1510 select * from t1;
1511 a b
1512 NULL NULL
1513 b B
1514 c c
1515 drop table t1;
1516 set sql_warnings = 0;
1517 # IF
1518 set sql_warnings = 1;
1519 create table t1 (a int, b int, c int as (if(a=1,a,b)));
1520 show create table t1;
1521 Table Create Table
1522 t1 CREATE TABLE `t1` (
1523 `a` int(11) DEFAULT NULL,
1524 `b` int(11) DEFAULT NULL,
1525 `c` int(11) GENERATED ALWAYS AS (if(`a` = 1,`a`,`b`)) VIRTUAL
1526 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1527 insert into t1 values (1,2,default);
1528 insert ignore into t1 values (3,4,default);
1529 select * from t1;
1530 a b c
1531 1 2 1
1532 3 4 4
1533 drop table t1;
1534 set sql_warnings = 0;
1535 # IFNULL
1536 set sql_warnings = 1;
1537 create table t1 (a varchar(10), b varchar(10), c varchar(10) as (ifnull(a,'DEFAULT')));
1538 show create table t1;
1539 Table Create Table
1540 t1 CREATE TABLE `t1` (
1541 `a` varchar(10) DEFAULT NULL,
1542 `b` varchar(10) DEFAULT NULL,
1543 `c` varchar(10) GENERATED ALWAYS AS (ifnull(`a`,'DEFAULT')) VIRTUAL
1544 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1545 insert into t1 values (NULL,'adf',default);
1546 insert ignore into t1 values ('a','adf',default);
1547 select * from t1;
1548 a b c
1549 NULL adf DEFAULT
1550 a adf a
1551 drop table t1;
1552 set sql_warnings = 0;
1553 # NULLIF
1554 set sql_warnings = 1;
1555 create table t1 (a varchar(10), b varchar(10) as (nullif(a,'DEFAULT')));
1556 show create table t1;
1557 Table Create Table
1558 t1 CREATE TABLE `t1` (
1559 `a` varchar(10) DEFAULT NULL,
1560 `b` varchar(10) GENERATED ALWAYS AS (nullif(`a`,'DEFAULT')) VIRTUAL
1561 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1562 insert into t1 values ('DEFAULT',default);
1563 insert ignore into t1 values ('a',default);
1564 select * from t1;
1565 a b
1566 DEFAULT NULL
1567 a a
1568 drop table t1;
1569 set sql_warnings = 0;
1570 #
1571 # OPERATORS
1572 #
1573 # AND, &&
1574 set sql_warnings = 1;
1575 create table t1 (a int, b bool as (a>0 && a<2));
1576 show create table t1;
1577 Table Create Table
1578 t1 CREATE TABLE `t1` (
1579 `a` int(11) DEFAULT NULL,
1580 `b` tinyint(1) GENERATED ALWAYS AS (`a` > 0 and `a` < 2) VIRTUAL
1581 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1582 insert into t1 values (-1,default);
1583 insert ignore into t1 values (1,default);
1584 select * from t1;
1585 a b
1586 -1 0
1587 1 1
1588 drop table t1;
1589 set sql_warnings = 0;
1590 # BETWEEN ... AND ...
1591 set sql_warnings = 1;
1592 create table t1 (a int, b bool as (a between 0 and 2));
1593 show create table t1;
1594 Table Create Table
1595 t1 CREATE TABLE `t1` (
1596 `a` int(11) DEFAULT NULL,
1597 `b` tinyint(1) GENERATED ALWAYS AS (`a` between 0 and 2) VIRTUAL
1598 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1599 insert into t1 values (-1,default);
1600 insert ignore into t1 values (1,default);
1601 select * from t1;
1602 a b
1603 -1 0
1604 1 1
1605 drop table t1;
1606 set sql_warnings = 0;
1607 # BINARY
1608 set sql_warnings = 1;
1609 create table t1 (a varchar(10), b varbinary(10) as (binary a));
1610 show create table t1;
1611 Table Create Table
1612 t1 CREATE TABLE `t1` (
1613 `a` varchar(10) DEFAULT NULL,
1614 `b` varbinary(10) GENERATED ALWAYS AS (cast(`a` as char charset binary)) VIRTUAL
1615 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1616 insert into t1 values ('11',default);
1617 insert ignore into t1 values (1,default);
1618 select * from t1;
1619 a b
1620 11 11
1621 1 1
1622 drop table t1;
1623 set sql_warnings = 0;
1624 # &
1625 set sql_warnings = 1;
1626 create table t1 (a int, b int as (a & 5));
1627 show create table t1;
1628 Table Create Table
1629 t1 CREATE TABLE `t1` (
1630 `a` int(11) DEFAULT NULL,
1631 `b` int(11) GENERATED ALWAYS AS (`a` & 5) VIRTUAL
1632 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1633 insert into t1 values (1,default);
1634 insert ignore into t1 values (0,default);
1635 select * from t1;
1636 a b
1637 1 1
1638 0 0
1639 drop table t1;
1640 set sql_warnings = 0;
1641 # ~
1642 set sql_warnings = 1;
1643 create table t1 (a int, b int as (~a));
1644 show create table t1;
1645 Table Create Table
1646 t1 CREATE TABLE `t1` (
1647 `a` int(11) DEFAULT NULL,
1648 `b` int(11) GENERATED ALWAYS AS (~`a`) VIRTUAL
1649 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1650 insert into t1 values (1,default);
1651 Warnings:
1652 Warning 1264 Out of range value for column 'b' at row 1
1653 select * from t1;
1654 a b
1655 1 2147483647
1656 drop table t1;
1657 set sql_warnings = 0;
1658 # |
1659 set sql_warnings = 1;
1660 create table t1 (a int, b int as (a | 5));
1661 show create table t1;
1662 Table Create Table
1663 t1 CREATE TABLE `t1` (
1664 `a` int(11) DEFAULT NULL,
1665 `b` int(11) GENERATED ALWAYS AS (`a` | 5) VIRTUAL
1666 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1667 insert into t1 values (1,default);
1668 insert ignore into t1 values (0,default);
1669 insert into t1 values (2,default);
1670 select * from t1;
1671 a b
1672 1 5
1673 0 5
1674 2 7
1675 drop table t1;
1676 set sql_warnings = 0;
1677 # ^
1678 set sql_warnings = 1;
1679 create table t1 (a int, b int as (a ^ 5));
1680 show create table t1;
1681 Table Create Table
1682 t1 CREATE TABLE `t1` (
1683 `a` int(11) DEFAULT NULL,
1684 `b` int(11) GENERATED ALWAYS AS (`a` ^ 5) VIRTUAL
1685 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1686 insert into t1 values (1,default);
1687 insert ignore into t1 values (0,default);
1688 insert into t1 values (2,default);
1689 select * from t1;
1690 a b
1691 1 4
1692 0 5
1693 2 7
1694 drop table t1;
1695 set sql_warnings = 0;
1696 # DIV
1697 set sql_warnings = 1;
1698 create table t1 (a int, b int as (a div 5));
1699 show create table t1;
1700 Table Create Table
1701 t1 CREATE TABLE `t1` (
1702 `a` int(11) DEFAULT NULL,
1703 `b` int(11) GENERATED ALWAYS AS (`a` DIV 5) VIRTUAL
1704 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1705 insert into t1 values (1,default);
1706 insert ignore into t1 values (7,default);
1707 select * from t1;
1708 a b
1709 1 0
1710 7 1
1711 drop table t1;
1712 set sql_warnings = 0;
1713 # <=>
1714 set sql_warnings = 1;
1715 create table t1 (a int, b int, c bool as (a <=> b));
1716 show create table t1;
1717 Table Create Table
1718 t1 CREATE TABLE `t1` (
1719 `a` int(11) DEFAULT NULL,
1720 `b` int(11) DEFAULT NULL,
1721 `c` tinyint(1) GENERATED ALWAYS AS (`a` <=> `b`) VIRTUAL
1722 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1723 insert into t1 values (1,1,default);
1724 insert ignore into t1 values (NULL,NULL,default);
1725 insert into t1 values (1,NULL,default);
1726 select * from t1;
1727 a b c
1728 1 1 1
1729 NULL NULL 1
1730 1 NULL 0
1731 drop table t1;
1732 set sql_warnings = 0;
1733 # =
1734 set sql_warnings = 1;
1735 create table t1 (a varchar(10), b varchar(10), c bool as (a=b));
1736 show create table t1;
1737 Table Create Table
1738 t1 CREATE TABLE `t1` (
1739 `a` varchar(10) DEFAULT NULL,
1740 `b` varchar(10) DEFAULT NULL,
1741 `c` tinyint(1) GENERATED ALWAYS AS (`a` = `b`) VIRTUAL
1742 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1743 insert into t1 values ('a','b',default);
1744 insert ignore into t1 values ('a','a',default);
1745 select * from t1;
1746 a b c
1747 a b 0
1748 a a 1
1749 drop table t1;
1750 set sql_warnings = 0;
1751 # >=
1752 set sql_warnings = 1;
1753 create table t1 (a varchar(10), b varchar(10), c bool as (a >= b));
1754 show create table t1;
1755 Table Create Table
1756 t1 CREATE TABLE `t1` (
1757 `a` varchar(10) DEFAULT NULL,
1758 `b` varchar(10) DEFAULT NULL,
1759 `c` tinyint(1) GENERATED ALWAYS AS (`a` >= `b`) VIRTUAL
1760 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1761 insert into t1 values ('a','b',default);
1762 insert ignore into t1 values ('a','a',default);
1763 select * from t1;
1764 a b c
1765 a b 0
1766 a a 1
1767 drop table t1;
1768 set sql_warnings = 0;
1769 # >
1770 set sql_warnings = 1;
1771 create table t1 (a varchar(10), b varchar(10), c bool as (a > b));
1772 show create table t1;
1773 Table Create Table
1774 t1 CREATE TABLE `t1` (
1775 `a` varchar(10) DEFAULT NULL,
1776 `b` varchar(10) DEFAULT NULL,
1777 `c` tinyint(1) GENERATED ALWAYS AS (`a` > `b`) VIRTUAL
1778 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1779 insert into t1 values ('a','b',default);
1780 insert ignore into t1 values ('a','a',default);
1781 select * from t1;
1782 a b c
1783 a b 0
1784 a a 0
1785 drop table t1;
1786 set sql_warnings = 0;
1787 # IS NOT NULL
1788 set sql_warnings = 1;
1789 create table t1 (a int, b bool as (a is not null));
1790 show create table t1;
1791 Table Create Table
1792 t1 CREATE TABLE `t1` (
1793 `a` int(11) DEFAULT NULL,
1794 `b` tinyint(1) GENERATED ALWAYS AS (`a` is not null) VIRTUAL
1795 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1796 insert into t1 values (1,default);
1797 insert ignore into t1 values (NULL,default);
1798 select * from t1;
1799 a b
1800 1 1
1801 NULL 0
1802 drop table t1;
1803 set sql_warnings = 0;
1804 # IS NULL
1805 set sql_warnings = 1;
1806 create table t1 (a int, b bool as (a is null));
1807 show create table t1;
1808 Table Create Table
1809 t1 CREATE TABLE `t1` (
1810 `a` int(11) DEFAULT NULL,
1811 `b` tinyint(1) GENERATED ALWAYS AS (`a` is null) VIRTUAL
1812 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1813 insert into t1 values (1,default);
1814 insert ignore into t1 values (NULL,default);
1815 select * from t1;
1816 a b
1817 1 0
1818 NULL 1
1819 drop table t1;
1820 set sql_warnings = 0;
1821 # <<
1822 set sql_warnings = 1;
1823 create table t1 (a int, b int as (a << 2));
1824 show create table t1;
1825 Table Create Table
1826 t1 CREATE TABLE `t1` (
1827 `a` int(11) DEFAULT NULL,
1828 `b` int(11) GENERATED ALWAYS AS (`a` << 2) VIRTUAL
1829 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1830 insert into t1 values (1,default);
1831 insert ignore into t1 values (3,default);
1832 select * from t1;
1833 a b
1834 1 4
1835 3 12
1836 drop table t1;
1837 set sql_warnings = 0;
1838 # <=
1839 set sql_warnings = 1;
1840 create table t1 (a varchar(10), b varchar(10), c bool as (a <= b));
1841 show create table t1;
1842 Table Create Table
1843 t1 CREATE TABLE `t1` (
1844 `a` varchar(10) DEFAULT NULL,
1845 `b` varchar(10) DEFAULT NULL,
1846 `c` tinyint(1) GENERATED ALWAYS AS (`a` <= `b`) VIRTUAL
1847 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1848 insert into t1 values ('b','a',default);
1849 insert ignore into t1 values ('b','b',default);
1850 insert into t1 values ('b','c',default);
1851 select * from t1;
1852 a b c
1853 b a 0
1854 b b 1
1855 b c 1
1856 drop table t1;
1857 set sql_warnings = 0;
1858 # <
1859 set sql_warnings = 1;
1860 create table t1 (a varchar(10), b varchar(10), c bool as (a < b));
1861 show create table t1;
1862 Table Create Table
1863 t1 CREATE TABLE `t1` (
1864 `a` varchar(10) DEFAULT NULL,
1865 `b` varchar(10) DEFAULT NULL,
1866 `c` tinyint(1) GENERATED ALWAYS AS (`a` < `b`) VIRTUAL
1867 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1868 insert into t1 values ('b','a',default);
1869 insert ignore into t1 values ('b','b',default);
1870 insert into t1 values ('b','c',default);
1871 select * from t1;
1872 a b c
1873 b a 0
1874 b b 0
1875 b c 1
1876 drop table t1;
1877 set sql_warnings = 0;
1878 # NOT BETWEEN ... AND ...
1879 set sql_warnings = 1;
1880 create table t1 (a int, b bool as (a not between 0 and 2));
1881 show create table t1;
1882 Table Create Table
1883 t1 CREATE TABLE `t1` (
1884 `a` int(11) DEFAULT NULL,
1885 `b` tinyint(1) GENERATED ALWAYS AS (`a` not between 0 and 2) VIRTUAL
1886 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1887 insert into t1 values (-1,default);
1888 insert ignore into t1 values (1,default);
1889 select * from t1;
1890 a b
1891 -1 1
1892 1 0
1893 drop table t1;
1894 set sql_warnings = 0;
1895 # <>
1896 set sql_warnings = 1;
1897 create table t1 (a varchar(10), b varchar(10), c bool as (a <> b));
1898 show create table t1;
1899 Table Create Table
1900 t1 CREATE TABLE `t1` (
1901 `a` varchar(10) DEFAULT NULL,
1902 `b` varchar(10) DEFAULT NULL,
1903 `c` tinyint(1) GENERATED ALWAYS AS (`a` <> `b`) VIRTUAL
1904 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1905 insert into t1 values ('b','a',default);
1906 insert ignore into t1 values ('b','b',default);
1907 insert into t1 values ('b','c',default);
1908 select * from t1;
1909 a b c
1910 b a 1
1911 b b 0
1912 b c 1
1913 drop table t1;
1914 set sql_warnings = 0;
1915 # !=
1916 set sql_warnings = 1;
1917 create table t1 (a varchar(10), b varchar(10), c bool as (a != b));
1918 show create table t1;
1919 Table Create Table
1920 t1 CREATE TABLE `t1` (
1921 `a` varchar(10) DEFAULT NULL,
1922 `b` varchar(10) DEFAULT NULL,
1923 `c` tinyint(1) GENERATED ALWAYS AS (`a` <> `b`) VIRTUAL
1924 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1925 insert into t1 values ('b','a',default);
1926 insert ignore into t1 values ('b','b',default);
1927 insert into t1 values ('b','c',default);
1928 select * from t1;
1929 a b c
1930 b a 1
1931 b b 0
1932 b c 1
1933 drop table t1;
1934 set sql_warnings = 0;
1935 # ||, OR
1936 set sql_warnings = 1;
1937 create table t1 (a int, b int as (a>5 || a<3));
1938 show create table t1;
1939 Table Create Table
1940 t1 CREATE TABLE `t1` (
1941 `a` int(11) DEFAULT NULL,
1942 `b` int(11) GENERATED ALWAYS AS (`a` > 5 or `a` < 3) VIRTUAL
1943 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1944 insert into t1 values (1,default);
1945 insert ignore into t1 values (4,default);
1946 select * from t1;
1947 a b
1948 1 1
1949 4 0
1950 drop table t1;
1951 set sql_warnings = 0;
1952 # >>
1953 set sql_warnings = 1;
1954 create table t1 (a int, b int as (a >> 2));
1955 show create table t1;
1956 Table Create Table
1957 t1 CREATE TABLE `t1` (
1958 `a` int(11) DEFAULT NULL,
1959 `b` int(11) GENERATED ALWAYS AS (`a` >> 2) VIRTUAL
1960 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1961 insert into t1 values (8,default);
1962 insert ignore into t1 values (3,default);
1963 select * from t1;
1964 a b
1965 8 2
1966 3 0
1967 drop table t1;
1968 set sql_warnings = 0;
1969 # XOR
1970 set sql_warnings = 1;
1971 create table t1 (a int, b int as (a xor 5));
1972 show create table t1;
1973 Table Create Table
1974 t1 CREATE TABLE `t1` (
1975 `a` int(11) DEFAULT NULL,
1976 `b` int(11) GENERATED ALWAYS AS (`a` xor 5) VIRTUAL
1977 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1978 insert into t1 values (0,default);
1979 insert ignore into t1 values (1,default);
1980 insert into t1 values (2,default);
1981 select * from t1;
1982 a b
1983 0 1
1984 1 0
1985 2 0
1986 drop table t1;
1987 set sql_warnings = 0;
1988 #
1989 # DATE AND TIME FUNCTIONS
1990 #
1991 # ADDDATE()
1992 set sql_warnings = 1;
1993 create table t1 (a datetime, b datetime as (adddate(a,interval 1 month)));
1994 show create table t1;
1995 Table Create Table
1996 t1 CREATE TABLE `t1` (
1997 `a` datetime DEFAULT NULL,
1998 `b` datetime GENERATED ALWAYS AS (`a` + interval 1 month) VIRTUAL
1999 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2000 insert into t1 values ('2008-08-31',default);
2001 select * from t1;
2002 a b
2003 2008-08-31 00:00:00 2008-09-30 00:00:00
2004 drop table t1;
2005 set sql_warnings = 0;
2006 # ADDTIME()
2007 set sql_warnings = 1;
2008 create table t1 (a datetime, b datetime as (addtime(a,'02:00:00')));
2009 show create table t1;
2010 Table Create Table
2011 t1 CREATE TABLE `t1` (
2012 `a` datetime DEFAULT NULL,
2013 `b` datetime GENERATED ALWAYS AS (addtime(`a`,'02:00:00')) VIRTUAL
2014 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2015 insert into t1 values ('2008-08-31',default);
2016 select * from t1;
2017 a b
2018 2008-08-31 00:00:00 2008-08-31 02:00:00
2019 drop table t1;
2020 set sql_warnings = 0;
2021 # CONVERT_TZ()
2022 set sql_warnings = 1;
2023 create table t1 (a datetime, b datetime as (convert_tz(a,'MET','UTC')));
2024 show create table t1;
2025 Table Create Table
2026 t1 CREATE TABLE `t1` (
2027 `a` datetime DEFAULT NULL,
2028 `b` datetime GENERATED ALWAYS AS (convert_tz(`a`,'MET','UTC')) VIRTUAL
2029 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2030 insert into t1 values ('2008-08-31',default);
2031 select * from t1;
2032 a b
2033 2008-08-31 00:00:00 2008-08-30 22:00:00
2034 drop table t1;
2035 set sql_warnings = 0;
2036 # DATE_ADD()
2037 set sql_warnings = 1;
2038 create table t1 (a datetime, b datetime as (date_add(a,interval 1 month)));
2039 show create table t1;
2040 Table Create Table
2041 t1 CREATE TABLE `t1` (
2042 `a` datetime DEFAULT NULL,
2043 `b` datetime GENERATED ALWAYS AS (`a` + interval 1 month) VIRTUAL
2044 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2045 insert into t1 values ('2008-08-31',default);
2046 select * from t1;
2047 a b
2048 2008-08-31 00:00:00 2008-09-30 00:00:00
2049 drop table t1;
2050 set sql_warnings = 0;
2051 # DATE_SUB()
2052 set sql_warnings = 1;
2053 create table t1 (a datetime, b datetime as (date_sub(a,interval 1 month)));
2054 show create table t1;
2055 Table Create Table
2056 t1 CREATE TABLE `t1` (
2057 `a` datetime DEFAULT NULL,
2058 `b` datetime GENERATED ALWAYS AS (`a` - interval 1 month) VIRTUAL
2059 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2060 insert into t1 values ('2008-08-31',default);
2061 select * from t1;
2062 a b
2063 2008-08-31 00:00:00 2008-07-31 00:00:00
2064 drop table t1;
2065 set sql_warnings = 0;
2066 # DATE()
2067 set sql_warnings = 1;
2068 create table t1 (a datetime, b datetime as (date(a)));
2069 show create table t1;
2070 Table Create Table
2071 t1 CREATE TABLE `t1` (
2072 `a` datetime DEFAULT NULL,
2073 `b` datetime GENERATED ALWAYS AS (cast(`a` as date)) VIRTUAL
2074 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2075 insert into t1 values ('2008-08-31 02:00:00',default);
2076 select * from t1;
2077 a b
2078 2008-08-31 02:00:00 2008-08-31 00:00:00
2079 drop table t1;
2080 set sql_warnings = 0;
2081 # DATEDIFF()
2082 set sql_warnings = 1;
2083 create table t1 (a datetime, b long as (datediff(a,'2000-01-01')));
2084 show create table t1;
2085 Table Create Table
2086 t1 CREATE TABLE `t1` (
2087 `a` datetime DEFAULT NULL,
2088 `b` mediumtext GENERATED ALWAYS AS (to_days(`a`) - to_days('2000-01-01')) VIRTUAL
2089 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2090 insert into t1 values ('2008-08-31',default);
2091 select * from t1;
2092 a b
2093 2008-08-31 00:00:00 3165
2094 drop table t1;
2095 set sql_warnings = 0;
2096 # DAY()
2097 set sql_warnings = 1;
2098 create table t1 (a datetime, b int as (day(a)));
2099 show create table t1;
2100 Table Create Table
2101 t1 CREATE TABLE `t1` (
2102 `a` datetime DEFAULT NULL,
2103 `b` int(11) GENERATED ALWAYS AS (dayofmonth(`a`)) VIRTUAL
2104 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2105 insert into t1 values ('2008-08-31',default);
2106 select * from t1;
2107 a b
2108 2008-08-31 00:00:00 31
2109 drop table t1;
2110 set sql_warnings = 0;
2111 # DAYOFMONTH()
2112 set sql_warnings = 1;
2113 create table t1 (a datetime, b int as (dayofmonth(a)));
2114 show create table t1;
2115 Table Create Table
2116 t1 CREATE TABLE `t1` (
2117 `a` datetime DEFAULT NULL,
2118 `b` int(11) GENERATED ALWAYS AS (dayofmonth(`a`)) VIRTUAL
2119 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2120 insert into t1 values ('2008-08-31',default);
2121 select * from t1;
2122 a b
2123 2008-08-31 00:00:00 31
2124 drop table t1;
2125 set sql_warnings = 0;
2126 # DAYOFWEEK()
2127 set sql_warnings = 1;
2128 create table t1 (a datetime, b int as (dayofweek(a)));
2129 show create table t1;
2130 Table Create Table
2131 t1 CREATE TABLE `t1` (
2132 `a` datetime DEFAULT NULL,
2133 `b` int(11) GENERATED ALWAYS AS (dayofweek(`a`)) VIRTUAL
2134 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2135 insert into t1 values ('2008-08-31',default);
2136 select * from t1;
2137 a b
2138 2008-08-31 00:00:00 1
2139 drop table t1;
2140 set sql_warnings = 0;
2141 # DAYOFYEAR()
2142 set sql_warnings = 1;
2143 create table t1 (a datetime, b int as (dayofyear(a)));
2144 show create table t1;
2145 Table Create Table
2146 t1 CREATE TABLE `t1` (
2147 `a` datetime DEFAULT NULL,
2148 `b` int(11) GENERATED ALWAYS AS (dayofyear(`a`)) VIRTUAL
2149 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2150 insert into t1 values ('2008-08-31',default);
2151 select * from t1;
2152 a b
2153 2008-08-31 00:00:00 244
2154 drop table t1;
2155 set sql_warnings = 0;
2156 # EXTRACT
2157 set sql_warnings = 1;
2158 create table t1 (a datetime, b int as (extract(year from a)));
2159 show create table t1;
2160 Table Create Table
2161 t1 CREATE TABLE `t1` (
2162 `a` datetime DEFAULT NULL,
2163 `b` int(11) GENERATED ALWAYS AS (extract(year from `a`)) VIRTUAL
2164 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2165 insert into t1 values ('2008-08-31',default);
2166 select * from t1;
2167 a b
2168 2008-08-31 00:00:00 2008
2169 drop table t1;
2170 set sql_warnings = 0;
2171 # FROM_DAYS()
2172 set sql_warnings = 1;
2173 create table t1 (a long, b datetime as (from_days(a)));
2174 show create table t1;
2175 Table Create Table
2176 t1 CREATE TABLE `t1` (
2177 `a` mediumtext DEFAULT NULL,
2178 `b` datetime GENERATED ALWAYS AS (from_days(`a`)) VIRTUAL
2179 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2180 insert into t1 values (730669,default);
2181 select * from t1;
2182 a b
2183 730669 2000-07-03 00:00:00
2184 drop table t1;
2185 set sql_warnings = 0;
2186 # FROM_UNIXTIME()
2187 set time_zone='UTC';
2188 set sql_warnings = 1;
2189 create table t1 (a long, b datetime as (from_unixtime(a)));
2190 show create table t1;
2191 Table Create Table
2192 t1 CREATE TABLE `t1` (
2193 `a` mediumtext DEFAULT NULL,
2194 `b` datetime GENERATED ALWAYS AS (from_unixtime(`a`)) VIRTUAL
2195 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2196 insert into t1 values (1196440219,default);
2197 select * from t1;
2198 a b
2199 1196440219 2007-11-30 16:30:19
2200 drop table t1;
2201 set sql_warnings = 0;
2202 # HOUR()
2203 set sql_warnings = 1;
2204 create table t1 (a time, b long as (hour(a)));
2205 show create table t1;
2206 Table Create Table
2207 t1 CREATE TABLE `t1` (
2208 `a` time DEFAULT NULL,
2209 `b` mediumtext GENERATED ALWAYS AS (hour(`a`)) VIRTUAL
2210 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2211 insert into t1 values ('10:05:03',default);
2212 select * from t1;
2213 a b
2214 10:05:03 10
2215 drop table t1;
2216 set sql_warnings = 0;
2217 # LAST_DAY()
2218 set sql_warnings = 1;
2219 create table t1 (a datetime, b datetime as (last_day(a)));
2220 show create table t1;
2221 Table Create Table
2222 t1 CREATE TABLE `t1` (
2223 `a` datetime DEFAULT NULL,
2224 `b` datetime GENERATED ALWAYS AS (last_day(`a`)) VIRTUAL
2225 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2226 insert into t1 values ('2003-02-05',default);
2227 insert ignore into t1 values ('2003-02-32',default);
2228 Warnings:
2229 Warning 1265 Data truncated for column 'a' at row 1
2230 select * from t1;
2231 a b
2232 2003-02-05 00:00:00 2003-02-28 00:00:00
2233 0000-00-00 00:00:00 NULL
2234 drop table t1;
2235 set sql_warnings = 0;
2236 # MAKEDATE()
2237 set sql_warnings = 1;
2238 create table t1 (a int, b datetime as (makedate(a,1)));
2239 show create table t1;
2240 Table Create Table
2241 t1 CREATE TABLE `t1` (
2242 `a` int(11) DEFAULT NULL,
2243 `b` datetime GENERATED ALWAYS AS (makedate(`a`,1)) VIRTUAL
2244 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2245 insert into t1 values (2001,default);
2246 select * from t1;
2247 a b
2248 2001 2001-01-01 00:00:00
2249 drop table t1;
2250 set sql_warnings = 0;
2251 # MAKETIME()
2252 set sql_warnings = 1;
2253 create table t1 (a int, b time as (maketime(a,1,3)));
2254 show create table t1;
2255 Table Create Table
2256 t1 CREATE TABLE `t1` (
2257 `a` int(11) DEFAULT NULL,
2258 `b` time GENERATED ALWAYS AS (maketime(`a`,1,3)) VIRTUAL
2259 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2260 insert into t1 values (12,default);
2261 select * from t1;
2262 a b
2263 12 12:01:03
2264 drop table t1;
2265 set sql_warnings = 0;
2266 # MICROSECOND()
2267 set sql_warnings = 1;
2268 create table t1 (a datetime, b long as (microsecond(a)));
2269 show create table t1;
2270 Table Create Table
2271 t1 CREATE TABLE `t1` (
2272 `a` datetime DEFAULT NULL,
2273 `b` mediumtext GENERATED ALWAYS AS (microsecond(`a`)) VIRTUAL
2274 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2275 insert into t1 values ('2009-12-31 12:00:00.123456',default);
2276 insert ignore into t1 values ('2009-12-31 23:59:59.000010',default);
2277 select * from t1;
2278 a b
2279 2009-12-31 12:00:00 0
2280 2009-12-31 23:59:59 0
2281 drop table t1;
2282 set sql_warnings = 0;
2283 # MINUTE()
2284 set sql_warnings = 1;
2285 create table t1 (a datetime, b int as (minute(a)));
2286 show create table t1;
2287 Table Create Table
2288 t1 CREATE TABLE `t1` (
2289 `a` datetime DEFAULT NULL,
2290 `b` int(11) GENERATED ALWAYS AS (minute(`a`)) VIRTUAL
2291 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2292 insert into t1 values ('2009-12-31 23:59:59.000010',default);
2293 select * from t1;
2294 a b
2295 2009-12-31 23:59:59 59
2296 drop table t1;
2297 set sql_warnings = 0;
2298 # MONTH()
2299 set sql_warnings = 1;
2300 create table t1 (a datetime, b int as (month(a)));
2301 show create table t1;
2302 Table Create Table
2303 t1 CREATE TABLE `t1` (
2304 `a` datetime DEFAULT NULL,
2305 `b` int(11) GENERATED ALWAYS AS (month(`a`)) VIRTUAL
2306 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2307 insert into t1 values ('2009-12-31 23:59:59.000010',default);
2308 select * from t1;
2309 a b
2310 2009-12-31 23:59:59 12
2311 drop table t1;
2312 set sql_warnings = 0;
2313 # PERIOD_ADD()
2314 set sql_warnings = 1;
2315 create table t1 (a int, b int as (period_add(a,2)));
2316 show create table t1;
2317 Table Create Table
2318 t1 CREATE TABLE `t1` (
2319 `a` int(11) DEFAULT NULL,
2320 `b` int(11) GENERATED ALWAYS AS (period_add(`a`,2)) VIRTUAL
2321 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2322 insert into t1 values (200801,default);
2323 select * from t1;
2324 a b
2325 200801 200803
2326 drop table t1;
2327 set sql_warnings = 0;
2328 # PERIOD_DIFF()
2329 set sql_warnings = 1;
2330 create table t1 (a int, b int, c int as (period_diff(a,b)));
2331 show create table t1;
2332 Table Create Table
2333 t1 CREATE TABLE `t1` (
2334 `a` int(11) DEFAULT NULL,
2335 `b` int(11) DEFAULT NULL,
2336 `c` int(11) GENERATED ALWAYS AS (period_diff(`a`,`b`)) VIRTUAL
2337 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2338 insert into t1 values (200802,200703,default);
2339 select * from t1;
2340 a b c
2341 200802 200703 11
2342 drop table t1;
2343 set sql_warnings = 0;
2344 # QUARTER()
2345 set sql_warnings = 1;
2346 create table t1 (a datetime, b int as (quarter(a)));
2347 show create table t1;
2348 Table Create Table
2349 t1 CREATE TABLE `t1` (
2350 `a` datetime DEFAULT NULL,
2351 `b` int(11) GENERATED ALWAYS AS (quarter(`a`)) VIRTUAL
2352 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2353 insert into t1 values ('2008-08-31',default);
2354 select * from t1;
2355 a b
2356 2008-08-31 00:00:00 3
2357 drop table t1;
2358 set sql_warnings = 0;
2359 # SEC_TO_TIME()
2360 set sql_warnings = 1;
2361 create table t1 (a long, b time as (sec_to_time(a)));
2362 show create table t1;
2363 Table Create Table
2364 t1 CREATE TABLE `t1` (
2365 `a` mediumtext DEFAULT NULL,
2366 `b` time GENERATED ALWAYS AS (sec_to_time(`a`)) VIRTUAL
2367 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2368 insert into t1 values (2378,default);
2369 select * from t1;
2370 a b
2371 2378 00:39:38
2372 drop table t1;
2373 set sql_warnings = 0;
2374 # SECOND()
2375 set sql_warnings = 1;
2376 create table t1 (a datetime, b int as (second(a)));
2377 show create table t1;
2378 Table Create Table
2379 t1 CREATE TABLE `t1` (
2380 `a` datetime DEFAULT NULL,
2381 `b` int(11) GENERATED ALWAYS AS (second(`a`)) VIRTUAL
2382 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2383 insert into t1 values ('10:05:03',default);
2384 select * from t1;
2385 a b
2386 2010-05-03 00:00:00 0
2387 drop table t1;
2388 set sql_warnings = 0;
2389 # STR_TO_DATE()
2390 set sql_warnings = 1;
2391 create table t1 (a varchar(64), b datetime as (str_to_date(a,'%m/%d/%Y')));
2392 show create table t1;
2393 Table Create Table
2394 t1 CREATE TABLE `t1` (
2395 `a` varchar(64) DEFAULT NULL,
2396 `b` datetime GENERATED ALWAYS AS (str_to_date(`a`,'%m/%d/%Y')) VIRTUAL
2397 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2398 insert into t1 values ('04/30/2004',default);
2399 select * from t1;
2400 a b
2401 04/30/2004 2004-04-30 00:00:00
2402 drop table t1;
2403 set sql_warnings = 0;
2404 # SUBDATE()
2405 set sql_warnings = 1;
2406 create table t1 (a datetime, b datetime as (subdate(a,interval 1 month)));
2407 show create table t1;
2408 Table Create Table
2409 t1 CREATE TABLE `t1` (
2410 `a` datetime DEFAULT NULL,
2411 `b` datetime GENERATED ALWAYS AS (`a` - interval 1 month) VIRTUAL
2412 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2413 insert into t1 values ('2008-08-31',default);
2414 select * from t1;
2415 a b
2416 2008-08-31 00:00:00 2008-07-31 00:00:00
2417 drop table t1;
2418 set sql_warnings = 0;
2419 # SUBTIME()
2420 set sql_warnings = 1;
2421 create table t1 (a datetime, b datetime as (subtime(a,'02:00:00')));
2422 show create table t1;
2423 Table Create Table
2424 t1 CREATE TABLE `t1` (
2425 `a` datetime DEFAULT NULL,
2426 `b` datetime GENERATED ALWAYS AS (subtime(`a`,'02:00:00')) VIRTUAL
2427 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2428 insert into t1 values ('2008-08-31',default);
2429 select * from t1;
2430 a b
2431 2008-08-31 00:00:00 2008-08-30 22:00:00
2432 drop table t1;
2433 set sql_warnings = 0;
2434 # TIME_TO_SEC()
2435 set sql_warnings = 1;
2436 create table t1 (a time, b long as (time_to_sec(a)));
2437 show create table t1;
2438 Table Create Table
2439 t1 CREATE TABLE `t1` (
2440 `a` time DEFAULT NULL,
2441 `b` mediumtext GENERATED ALWAYS AS (time_to_sec(`a`)) VIRTUAL
2442 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2443 insert into t1 values ('22:23:00',default);
2444 select * from t1;
2445 a b
2446 22:23:00 80580
2447 drop table t1;
2448 set sql_warnings = 0;
2449 # TIME()
2450 set sql_warnings = 1;
2451 create table t1 (a datetime, b time as (time(a)));
2452 show create table t1;
2453 Table Create Table
2454 t1 CREATE TABLE `t1` (
2455 `a` datetime DEFAULT NULL,
2456 `b` time GENERATED ALWAYS AS (cast(`a` as time)) VIRTUAL
2457 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2458 insert into t1 values ('2008-08-31 02:03:04',default);
2459 select * from t1;
2460 a b
2461 2008-08-31 02:03:04 02:03:04
2462 drop table t1;
2463 set sql_warnings = 0;
2464 # TIMEDIFF()
2465 set sql_warnings = 1;
2466 create table t1 (a datetime, b datetime, c long as (timediff(a,b)));
2467 show create table t1;
2468 Table Create Table
2469 t1 CREATE TABLE `t1` (
2470 `a` datetime DEFAULT NULL,
2471 `b` datetime DEFAULT NULL,
2472 `c` mediumtext GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL
2473 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2474 insert into t1 values ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',default);
2475 select * from t1;
2476 a b c
2477 2008-12-31 23:59:59 2008-12-30 01:01:01 46:58:58
2478 drop table t1;
2479 set sql_warnings = 0;
2480 # TIMESTAMP()
2481 set sql_warnings = 1;
2482 create table t1 (a datetime, b timestamp as (timestamp(a)));
2483 show create table t1;
2484 Table Create Table
2485 t1 CREATE TABLE `t1` (
2486 `a` datetime DEFAULT NULL,
2487 `b` timestamp GENERATED ALWAYS AS (cast(`a` as datetime)) VIRTUAL
2488 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2489 insert into t1 values ('2008-12-31',default);
2490 select * from t1;
2491 a b
2492 2008-12-31 00:00:00 2008-12-31 00:00:00
2493 drop table t1;
2494 set sql_warnings = 0;
2495 # TIMESTAMPADD()
2496 set sql_warnings = 1;
2497 create table t1 (a datetime, b timestamp as (timestampadd(minute,1,a)));
2498 show create table t1;
2499 Table Create Table
2500 t1 CREATE TABLE `t1` (
2501 `a` datetime DEFAULT NULL,
2502 `b` timestamp GENERATED ALWAYS AS (`a` + interval 1 minute) VIRTUAL
2503 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2504 insert into t1 values ('2003-01-02',default);
2505 select * from t1;
2506 a b
2507 2003-01-02 00:00:00 2003-01-02 00:01:00
2508 drop table t1;
2509 set sql_warnings = 0;
2510 # TIMESTAMPDIFF()
2511 set sql_warnings = 1;
2512 create table t1 (a timestamp, b timestamp, c long as (timestampdiff(MONTH, a,b)));
2513 show create table t1;
2514 Table Create Table
2515 t1 CREATE TABLE `t1` (
2516 `a` timestamp NULL DEFAULT NULL,
2517 `b` timestamp NULL DEFAULT NULL,
2518 `c` mediumtext GENERATED ALWAYS AS (timestampdiff(MONTH,`a`,`b`)) VIRTUAL
2519 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2520 insert into t1 values ('2003-02-01','2003-05-01',default);
2521 select * from t1;
2522 a b c
2523 2003-02-01 00:00:00 2003-05-01 00:00:00 3
2524 drop table t1;
2525 set sql_warnings = 0;
2526 # TO_DAYS()
2527 set sql_warnings = 1;
2528 create table t1 (a datetime, b long as (to_days(a)));
2529 show create table t1;
2530 Table Create Table
2531 t1 CREATE TABLE `t1` (
2532 `a` datetime DEFAULT NULL,
2533 `b` mediumtext GENERATED ALWAYS AS (to_days(`a`)) VIRTUAL
2534 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2535 insert into t1 values ('2007-10-07',default);
2536 select * from t1;
2537 a b
2538 2007-10-07 00:00:00 733321
2539 drop table t1;
2540 set sql_warnings = 0;
2541 # WEEK()
2542 set sql_warnings = 1;
2543 create table t1 (a datetime, b int as (week(a,0)));
2544 show create table t1;
2545 Table Create Table
2546 t1 CREATE TABLE `t1` (
2547 `a` datetime DEFAULT NULL,
2548 `b` int(11) GENERATED ALWAYS AS (week(`a`,0)) VIRTUAL
2549 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2550 insert into t1 values ('2008-09-01',default);
2551 select * from t1;
2552 a b
2553 2008-09-01 00:00:00 35
2554 drop table t1;
2555 set sql_warnings = 0;
2556 # WEEKDAY()
2557 set sql_warnings = 1;
2558 create table t1 (a datetime, b int as (weekday(a)));
2559 show create table t1;
2560 Table Create Table
2561 t1 CREATE TABLE `t1` (
2562 `a` datetime DEFAULT NULL,
2563 `b` int(11) GENERATED ALWAYS AS (weekday(`a`)) VIRTUAL
2564 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2565 insert into t1 values ('2008-09-01',default);
2566 select * from t1;
2567 a b
2568 2008-09-01 00:00:00 0
2569 drop table t1;
2570 set sql_warnings = 0;
2571 # WEEKOFYEAR()
2572 set sql_warnings = 1;
2573 create table t1 (a datetime, b int as (weekofyear(a)));
2574 show create table t1;
2575 Table Create Table
2576 t1 CREATE TABLE `t1` (
2577 `a` datetime DEFAULT NULL,
2578 `b` int(11) GENERATED ALWAYS AS (week(`a`,3)) VIRTUAL
2579 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2580 insert into t1 values ('2008-09-01',default);
2581 select * from t1;
2582 a b
2583 2008-09-01 00:00:00 36
2584 drop table t1;
2585 set sql_warnings = 0;
2586 # YEAR()
2587 set sql_warnings = 1;
2588 create table t1 (a datetime, b int as (year(a)));
2589 show create table t1;
2590 Table Create Table
2591 t1 CREATE TABLE `t1` (
2592 `a` datetime DEFAULT NULL,
2593 `b` int(11) GENERATED ALWAYS AS (year(`a`)) VIRTUAL
2594 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2595 insert into t1 values ('2008-09-01',default);
2596 select * from t1;
2597 a b
2598 2008-09-01 00:00:00 2008
2599 drop table t1;
2600 set sql_warnings = 0;
2601 # YEARWEEK()
2602 set sql_warnings = 1;
2603 create table t1 (a datetime, b int as (yearweek(a)));
2604 show create table t1;
2605 Table Create Table
2606 t1 CREATE TABLE `t1` (
2607 `a` datetime DEFAULT NULL,
2608 `b` int(11) GENERATED ALWAYS AS (yearweek(`a`,0)) VIRTUAL
2609 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2610 insert into t1 values ('2008-09-01',default);
2611 select * from t1;
2612 a b
2613 2008-09-01 00:00:00 200835
2614 drop table t1;
2615 set sql_warnings = 0;
2616 #
2617 # FULL TEXT SEARCH FUNCTIONS
2618 #
2619 # None.
2620 #
2621 # CAST FUNCTIONS AND OPERATORS
2622 #
2623 # CAST()
2624 set sql_warnings = 1;
2625 create table t1 (a int, b long as (cast(a as unsigned)));
2626 show create table t1;
2627 Table Create Table
2628 t1 CREATE TABLE `t1` (
2629 `a` int(11) DEFAULT NULL,
2630 `b` mediumtext GENERATED ALWAYS AS (cast(`a` as unsigned)) VIRTUAL
2631 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2632 insert into t1 values (1,default);
2633 insert ignore into t1 values (-1,default);
2634 Warnings:
2635 Note 1105 Cast to unsigned converted negative integer to it's positive complement
2636 select * from t1;
2637 a b
2638 1 1
2639 -1 18446744073709551615
2640 Warnings:
2641 Note 1105 Cast to unsigned converted negative integer to it's positive complement
2642 drop table t1;
2643 set sql_warnings = 0;
2644 # Convert()
2645 set sql_warnings = 1;
2646 create table t1 (a int, b long as (convert(a,unsigned)));
2647 show create table t1;
2648 Table Create Table
2649 t1 CREATE TABLE `t1` (
2650 `a` int(11) DEFAULT NULL,
2651 `b` mediumtext GENERATED ALWAYS AS (cast(`a` as unsigned)) VIRTUAL
2652 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2653 insert into t1 values (1,default);
2654 insert ignore into t1 values (-1,default);
2655 Warnings:
2656 Note 1105 Cast to unsigned converted negative integer to it's positive complement
2657 select * from t1;
2658 a b
2659 1 1
2660 -1 18446744073709551615
2661 Warnings:
2662 Note 1105 Cast to unsigned converted negative integer to it's positive complement
2663 drop table t1;
2664 set sql_warnings = 0;
2665 #
2666 # XML FUNCTIONS
2667 #
2668 # None.
2669 #
2670 # OTHER FUNCTIONS
2671 #
2672 # AES_DECRYPT(), AES_ENCRYPT()
2673 set sql_warnings = 1;
2674 create table t1 (a varchar(1024), b varchar(1024) as (aes_encrypt(aes_decrypt(a,'adf'),'adf')));
2675 show create table t1;
2676 Table Create Table
2677 t1 CREATE TABLE `t1` (
2678 `a` varchar(1024) DEFAULT NULL,
2679 `b` varchar(1024) GENERATED ALWAYS AS (aes_encrypt(aes_decrypt(`a`,'adf'),'adf')) VIRTUAL
2680 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2681 insert into t1 values ('MySQL',default);
2682 select * from t1;
2683 a b
2684 MySQL NULL
2685 drop table t1;
2686 set sql_warnings = 0;
2687 # BIT_COUNT()
2688 set sql_warnings = 1;
2689 create table t1 (a int, b int as (bit_count(a)));
2690 show create table t1;
2691 Table Create Table
2692 t1 CREATE TABLE `t1` (
2693 `a` int(11) DEFAULT NULL,
2694 `b` int(11) GENERATED ALWAYS AS (bit_count(`a`)) VIRTUAL
2695 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2696 insert into t1 values (5,default);
2697 select * from t1;
2698 a b
2699 5 2
2700 drop table t1;
2701 set sql_warnings = 0;
2702 # COMPRESS(), UNCOMPRESS()
2703 set sql_warnings = 1;
2704 create table t1 (a varchar(1024), b varchar(1024) as (uncompress(compress(a))));
2705 show create table t1;
2706 Table Create Table
2707 t1 CREATE TABLE `t1` (
2708 `a` varchar(1024) DEFAULT NULL,
2709 `b` varchar(1024) GENERATED ALWAYS AS (uncompress(compress(`a`))) VIRTUAL
2710 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2711 insert into t1 values ('MySQL',default);
2712 select * from t1;
2713 a b
2714 MySQL MySQL
2715 drop table t1;
2716 set sql_warnings = 0;
2717 # ENCODE(), DECODE()
2718 set sql_warnings = 1;
2719 create table t1 (a varchar(1024), b varchar(1024) as (decode(encode(a,'abc'),'abc')));
2720 show create table t1;
2721 Table Create Table
2722 t1 CREATE TABLE `t1` (
2723 `a` varchar(1024) DEFAULT NULL,
2724 `b` varchar(1024) GENERATED ALWAYS AS (decode(encode(`a`,'abc'),'abc')) VIRTUAL
2725 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2726 insert into t1 values ('MySQL',default);
2727 select * from t1;
2728 a b
2729 MySQL MySQL
2730 drop table t1;
2731 set sql_warnings = 0;
2732 # DEFAULT()
2733 set sql_warnings = 1;
2734 create table t1 (a varchar(1024) default 'aaa', b varchar(1024) as (ifnull(a,default(a))));
2735 show create table t1;
2736 Table Create Table
2737 t1 CREATE TABLE `t1` (
2738 `a` varchar(1024) DEFAULT 'aaa',
2739 `b` varchar(1024) GENERATED ALWAYS AS (ifnull(`a`,default(`a`))) VIRTUAL
2740 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2741 insert into t1 values ('any value',default);
2742 select * from t1;
2743 a b
2744 any value any value
2745 drop table t1;
2746 set sql_warnings = 0;
2747 # INET_ATON(), INET_NTOA()
2748 set sql_warnings = 1;
2749 create table t1 (a varchar(1024), b varchar(1024) as (inet_ntoa(inet_aton(a))));
2750 show create table t1;
2751 Table Create Table
2752 t1 CREATE TABLE `t1` (
2753 `a` varchar(1024) DEFAULT NULL,
2754 `b` varchar(1024) GENERATED ALWAYS AS (inet_ntoa(inet_aton(`a`))) VIRTUAL
2755 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2756 insert into t1 values ('127.0.0.1',default);
2757 select * from t1;
2758 a b
2759 127.0.0.1 127.0.0.1
2760 drop table t1;
2761 set sql_warnings = 0;
2762 # MD5()
2763 set sql_warnings = 1;
2764 create table t1 (a varchar(1024), b varbinary(32) as (md5(a)));
2765 show create table t1;
2766 Table Create Table
2767 t1 CREATE TABLE `t1` (
2768 `a` varchar(1024) DEFAULT NULL,
2769 `b` varbinary(32) GENERATED ALWAYS AS (md5(`a`)) VIRTUAL
2770 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2771 insert into t1 values ('testing',default);
2772 select * from t1;
2773 a b
2774 testing ae2b1fca515949e5d54fb22b8ed95575
2775 drop table t1;
2776 set sql_warnings = 0;
2777 # OLD_PASSWORD()
2778 set sql_warnings = 1;
2779 create table t1 (a varchar(1024), b varchar(1024) as (old_password(a)));
2780 show create table t1;
2781 Table Create Table
2782 t1 CREATE TABLE `t1` (
2783 `a` varchar(1024) DEFAULT NULL,
2784 `b` varchar(1024) GENERATED ALWAYS AS (old_password(`a`)) VIRTUAL
2785 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2786 insert into t1 values ('badpwd',default);
2787 select * from t1;
2788 a b
2789 badpwd 7f84554057dd964b
2790 drop table t1;
2791 set sql_warnings = 0;
2792 # PASSWORD()
2793 set sql_warnings = 1;
2794 create table t1 (a varchar(1024), b varchar(1024) as (password(a)));
2795 show create table t1;
2796 Table Create Table
2797 t1 CREATE TABLE `t1` (
2798 `a` varchar(1024) DEFAULT NULL,
2799 `b` varchar(1024) GENERATED ALWAYS AS (password(`a`)) VIRTUAL
2800 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2801 insert into t1 values ('badpwd',default);
2802 select * from t1;
2803 a b
2804 badpwd *AAB3E285149C0135D51A520E1940DD3263DC008C
2805 drop table t1;
2806 set sql_warnings = 0;
2807 # SHA1()
2808 set sql_warnings = 1;
2809 create table t1 (a varchar(1024), b varchar(1024) as (sha1(a)));
2810 show create table t1;
2811 Table Create Table
2812 t1 CREATE TABLE `t1` (
2813 `a` varchar(1024) DEFAULT NULL,
2814 `b` varchar(1024) GENERATED ALWAYS AS (sha(`a`)) VIRTUAL
2815 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2816 insert into t1 values ('abc',default);
2817 select * from t1;
2818 a b
2819 abc a9993e364706816aba3e25717850c26c9cd0d89d
2820 drop table t1;
2821 set sql_warnings = 0;
2822 # SHA()
2823 set sql_warnings = 1;
2824 create table t1 (a varchar(1024), b varchar(1024) as (sha(a)));
2825 show create table t1;
2826 Table Create Table
2827 t1 CREATE TABLE `t1` (
2828 `a` varchar(1024) DEFAULT NULL,
2829 `b` varchar(1024) GENERATED ALWAYS AS (sha(`a`)) VIRTUAL
2830 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2831 insert into t1 values ('abc',default);
2832 select * from t1;
2833 a b
2834 abc a9993e364706816aba3e25717850c26c9cd0d89d
2835 drop table t1;
2836 set sql_warnings = 0;
2837 # UNCOMPRESSED_LENGTH()
2838 set sql_warnings = 1;
2839 create table t1 (a char, b varchar(1024) as (uncompressed_length(compress(repeat(a,30)))));
2840 show create table t1;
2841 Table Create Table
2842 t1 CREATE TABLE `t1` (
2843 `a` char(1) DEFAULT NULL,
2844 `b` varchar(1024) GENERATED ALWAYS AS (uncompressed_length(compress(repeat(`a`,30)))) VIRTUAL
2845 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2846 insert into t1 values ('a',default);
2847 select * from t1;
2848 a b
2849 a 30
2850 drop table t1;
2851 set sql_warnings = 0;
2852 # MONTHNAME()
2853 set sql_warnings = 1;
2854 create table t1 (a date, b varchar(100) as (monthname(a)));
2855 show create table t1;
2856 Table Create Table
2857 t1 CREATE TABLE `t1` (
2858 `a` date DEFAULT NULL,
2859 `b` varchar(100) GENERATED ALWAYS AS (monthname(`a`)) VIRTUAL
2860 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2861 insert into t1 values ('2010-10-10',default);
2862 select * from t1;
2863 a b
2864 2010-10-10 October
2865 drop table t1;
2866 set sql_warnings = 0;
2867 # DAYNAME()
2868 set sql_warnings = 1;
2869 create table t1 (a date, b varchar(100) as (dayname(a)));
2870 show create table t1;
2871 Table Create Table
2872 t1 CREATE TABLE `t1` (
2873 `a` date DEFAULT NULL,
2874 `b` varchar(100) GENERATED ALWAYS AS (dayname(`a`)) VIRTUAL
2875 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2876 insert into t1 values ('2011-11-11',default);
2877 select * from t1;
2878 a b
2879 2011-11-11 Friday
2880 drop table t1;
2881 set sql_warnings = 0;
2882 # DATE_FORMAT()
2883 set sql_warnings = 1;
2884 create table t1 (a date, b varchar(100) as (date_format(a, '%W %a %M %b')));
2885 show create table t1;
2886 Table Create Table
2887 t1 CREATE TABLE `t1` (
2888 `a` date DEFAULT NULL,
2889 `b` varchar(100) GENERATED ALWAYS AS (date_format(`a`,'%W %a %M %b')) VIRTUAL
2890 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2891 insert into t1 values ('2012-12-12',default);
2892 select * from t1;
2893 a b
2894 2012-12-12 Wednesday Wed December Dec
2895 drop table t1;
2896 set sql_warnings = 0;
2897 # DATE_FORMAT() STORED
2898 set sql_warnings = 1;
2899 create table t1 (a date, b varchar(100) as (date_format(a, '%W %a %M %b', 'de_DE')) STORED);
2900 show create table t1;
2901 Table Create Table
2902 t1 CREATE TABLE `t1` (
2903 `a` date DEFAULT NULL,
2904 `b` varchar(100) GENERATED ALWAYS AS (date_format(`a`,'%W %a %M %b','de_DE')) STORED
2905 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2906 insert into t1 values ('2012-12-12',default);
2907 select * from t1;
2908 a b
2909 2012-12-12 Mittwoch Mi Dezember Dez
2910 drop table t1;
2911 set sql_warnings = 0;
2912 # CURRENT_USER()
2913 set sql_warnings = 1;
2914 create table t1 (a char, b varchar(32) as (current_user()));
2915 show create table t1;
2916 Table Create Table
2917 t1 CREATE TABLE `t1` (
2918 `a` char(1) DEFAULT NULL,
2919 `b` varchar(32) GENERATED ALWAYS AS (current_user()) VIRTUAL
2920 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2921 insert into t1 values ('a', default);
2922 select * from t1;
2923 a b
2924 a root@localhost
2925 drop table t1;
2926 set sql_warnings = 0;
2927 # TIME_FORMAT()
2928 set sql_warnings = 1;
2929 create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")));
2930 show create table t1;
2931 Table Create Table
2932 t1 CREATE TABLE `t1` (
2933 `a` datetime DEFAULT NULL,
2934 `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) VIRTUAL
2935 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2936 insert into t1 values ('2001-01-01 02:03:04',default);
2937 select * from t1;
2938 a b
2939 2001-01-01 02:03:04 02.03.04
2940 drop table t1;
2941 set sql_warnings = 0;
2942 # TIME_FORMAT() STORED
2943 set sql_warnings = 1;
2944 create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")) STORED);
2945 show create table t1;
2946 Table Create Table
2947 t1 CREATE TABLE `t1` (
2948 `a` datetime DEFAULT NULL,
2949 `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) STORED
2950 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2951 insert into t1 values ('2001-01-01 02:03:04',default);
2952 select * from t1;
2953 a b
2954 2001-01-01 02:03:04 02.03.04
2955 drop table t1;
2956 set sql_warnings = 0;