Debian Codegraph @

mysql-test/suite/gcol/r/gcol_supported_sql_funcs_myisam.result

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