Debian Codegraph @

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