Debian Codegraph @

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

raw
1 create table t1 (a int, b int, v int as (a+1), index idx(b));
2 insert into t1(a, b) values
3 (4, 40), (3, 30), (5, 50), (7, 70), (8, 80), (2, 20), (1, 10);
4 select * from t1 order by b;
5 a b v
6 1 10 2
7 2 20 3
8 3 30 4
9 4 40 5
10 5 50 6
11 7 70 8
12 8 80 9
13 delete from t1 where v > 6 order by b limit 1;
14 select * from t1 order by b;
15 a b v
16 1 10 2
17 2 20 3
18 3 30 4
19 4 40 5
20 5 50 6
21 8 80 9
22 update t1 set a=v order by b limit 1;
23 select * from t1 order by b;
24 a b v
25 2 10 3
26 2 20 3
27 3 30 4
28 4 40 5
29 5 50 6
30 8 80 9
31 drop table t1;
32 CREATE TABLE t1 (
33 a int NOT NULL DEFAULT '0',
34 v double AS ((1, a)) VIRTUAL
35 );
36 ERROR 21000: Operand should contain 1 column(s)
37 CREATE TABLE t1 (
38 a CHAR(255) BINARY NOT NULL DEFAULT 0,
39 b CHAR(255) BINARY NOT NULL DEFAULT 0,
40 v CHAR(255) BINARY AS (CONCAT(a,b)) VIRTUAL );
41 INSERT INTO t1(a,b) VALUES ('4','7'), ('4','6');
42 SELECT 1 AS C FROM t1 ORDER BY v;
43 C
44 1
45 1
46 DROP TABLE t1;
47 CREATE TABLE t1(a int, b int DEFAULT 0, v INT AS (b+10) PERSISTENT);
48 INSERT INTO t1(a) VALUES (1);
49 SELECT b, v FROM t1;
50 b v
51 0 10
52 DROP TABLE t1;
53 CREATE TABLE t1(a int DEFAULT 100, v int AS (a+1) PERSISTENT);
54 INSERT INTO t1 () VALUES ();
55 CREATE TABLE t2(a int DEFAULT 100 , v int AS (a+1));
56 INSERT INTO t2 () VALUES ();
57 SELECT a, v FROM t1;
58 a v
59 100 101
60 SELECT a, v FROM t2;
61 a v
62 100 101
63 DROP TABLE t1,t2;
64 CREATE TABLE t1 (
65 a datetime NOT NULL DEFAULT '2000-01-01',
66 v boolean AS (a < '2001-01-01')
67 );
68 INSERT INTO t1(a) VALUES ('2002-02-15');
69 INSERT INTO t1(a) VALUES ('2000-10-15');
70 SELECT a, v FROM t1;
71 a v
72 2002-02-15 00:00:00 0
73 2000-10-15 00:00:00 1
74 SELECT a, v FROM t1;
75 a v
76 2002-02-15 00:00:00 0
77 2000-10-15 00:00:00 1
78 CREATE TABLE t2 (
79 a datetime NOT NULL DEFAULT '2000-01-01',
80 v boolean AS (a < '2001-01-01') PERSISTENT
81 );
82 INSERT INTO t2(a) VALUES ('2002-02-15');
83 INSERT INTO t2(a) VALUES ('2000-10-15');
84 SELECT * FROM t2;
85 a v
86 2002-02-15 00:00:00 0
87 2000-10-15 00:00:00 1
88 DROP TABLE t1, t2;
89 CREATE TABLE t1 (
90 a char(255), b char(255), c char(255), d char(255),
91 v char(255) AS (CONCAT(c,d) ) VIRTUAL
92 );
93 INSERT INTO t1(a,b,c,d) VALUES ('w','x','y','z'), ('W','X','Y','Z');
94 SELECT v FROM t1 ORDER BY CONCAT(a,b);
95 v
96 yz
97 YZ
98 DROP TABLE t1;
99 CREATE TABLE t1 (f1 INTEGER, v1 INTEGER AS (f1) VIRTUAL);
100 CREATE TABLE t2 AS SELECT v1 FROM t1;
101 SHOW CREATE TABLE t2;
102 Table Create Table
103 t2 CREATE TABLE `t2` (
104 `v1` int(11) DEFAULT NULL
105 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
106 DROP TABLE t1,t2;
107 CREATE TABLE t1 (p int, a double NOT NULL, v double AS (ROUND(a,p)) VIRTUAL);
108 INSERT IGNORE INTO t1 VALUES (0,1,0);
109 Warnings:
110 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
111 INSERT IGNORE INTO t1 VALUES (NULL,0,0);
112 Warnings:
113 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
114 SELECT a, p, v, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
115 a p v ROUND(a,p) ROUND(a,p+NULL)
116 1 0 1 1 NULL
117 0 NULL NULL NULL NULL
118 DROP TABLE t1;
119 CREATE TABLE t1 (p int, a double NOT NULL);
120 INSERT INTO t1(p,a) VALUES (0,1);
121 INSERT INTO t1(p,a) VALUES (NULL,0);
122 SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
123 a p ROUND(a,p) ROUND(a,p+NULL)
124 1 0 1 NULL
125 0 NULL NULL NULL
126 DROP TABLE t1;
127 CREATE TABLE t1 (a char(32), v char(32) CHARACTER SET ucs2 AS (a) VIRTUAL);
128 SHOW CREATE TABLE t1;
129 Table Create Table
130 t1 CREATE TABLE `t1` (
131 `a` char(32) DEFAULT NULL,
132 `v` char(32) CHARACTER SET ucs2 COLLATE ucs2_uca1400_ai_ci GENERATED ALWAYS AS (`a`) VIRTUAL
133 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
134 DROP TABLE t1;
135 CREATE TABLE t1 (a int, b int);
136 CREATE TABLE t2 (a int, b int as (a+1) VIRTUAL);
137 SELECT table_schema, table_name, column_name, column_type, extra
138 FROM information_schema.columns WHERE table_name = 't1';
139 table_schema table_name column_name column_type extra
140 test t1 a int(11)
141 test t1 b int(11)
142 SELECT table_schema, table_name, column_name, column_type, extra
143 FROM information_schema.columns WHERE table_name = 't2';
144 table_schema table_name column_name column_type extra
145 test t2 a int(11)
146 test t2 b int(11) VIRTUAL GENERATED
147 DROP TABLE t1,t2;
148 create table t1 (
149 a int not null, b char(2) not null,
150 c enum('Y','N') as (case when b = 'aa' then 'Y' else 'N' end) persistent
151 );
152 show create table t1;
153 Table Create Table
154 t1 CREATE TABLE `t1` (
155 `a` int(11) NOT NULL,
156 `b` char(2) NOT NULL,
157 `c` enum('Y','N') GENERATED ALWAYS AS (case when `b` = 'aa' then 'Y' else 'N' end) STORED
158 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
159 insert into t1(a,b) values (1,'bb'), (2,'aa'), (3,'cc');
160 select * from t1;
161 a b c
162 1 bb N
163 2 aa Y
164 3 cc N
165 create table t2 (
166 a int, b int,
167 c set("y","n")
168 as (if(a=0,if(b=0,('n,n'),('n,y')),if(b=0,('y,n'),('y,y')))) persistent
169 );
170 show create table t2;
171 Table Create Table
172 t2 CREATE TABLE `t2` (
173 `a` int(11) DEFAULT NULL,
174 `b` int(11) DEFAULT NULL,
175 `c` set('y','n') GENERATED ALWAYS AS (if(`a` = 0,if(`b` = 0,'n,n','n,y'),if(`b` = 0,'y,n','y,y'))) STORED
176 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
177 insert into t2(a,b) values (7,0), (2,3), (0,1);
178 select * from t2;
179 a b c
180 7 0 y,n
181 2 3 y
182 0 1 y,n
183 drop table t1,t2;
184 SET @old_debug= @@global.debug_dbug;
185 SET @old_debug= @@global.debug_dbug;
186 SET GLOBAL debug_dbug= "+d,write_delay_wakeup";
187 CREATE TABLE t1 (a int,
188 ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
189 tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
190 ) ENGINE=MyISAM;
191 # First test FLUSH TABLES
192 INSERT INTO t1 (a,tsv) VALUES (1,DEFAULT);
193 INSERT DELAYED INTO t1 (a,tsv) VALUES (2,DEFAULT);
194 FLUSH TABLES;
195 SELECT COUNT(*) > 0 FROM t1;
196 COUNT(*) > 0
197 1
198 # Then test FLUSH TABLES t1;
199 INSERT INTO t1 (a,tsv) VALUES (3,DEFAULT);
200 INSERT DELAYED INTO t1 (a,tsv) VALUES (4,DEFAULT);
201 FLUSH TABLES t1;
202 SELECT COUNT(*) FROM t1;
203 COUNT(*)
204 4
205 # Then test FLUSH TABLES WITH READ LOCK;
206 INSERT INTO t1 (a,tsv) VALUES (5,DEFAULT);
207 INSERT DELAYED INTO t1 (a,tsv) VALUES (6,DEFAULT);
208 FLUSH TABLES WITH READ LOCK;
209 SELECT COUNT(*) FROM t1;
210 COUNT(*)
211 6
212 set GLOBAL debug_dbug= @old_debug;
213 unlock tables;
214 DROP TABLE t1;
215 #
216 # MDEV-4823 Server crashes in Item_func_not::fix_fields on
217 # creating a table with a virtual column using NOT
218 #
219 CREATE TABLE t1 ( f1 INT, v4 INT AS ( NOT f1 ) VIRTUAL );
220 drop table t1;
221 # end of 5.2 tests
222 create table t1 (a int, b int);
223 insert into t1 values (3, 30), (4, 20), (1, 20);
224 create table t2 (c int, d int, v int as (d+1), index idx(c));
225 insert into t2(c,d) values
226 (20, 100), (20, 300), (30, 100), (30, 200), (40, 500),
227 (70, 100), (40, 300), (60, 100), (40, 100), (70, 100);
228 insert into t2(c,d) values
229 (120, 100), (150, 300), (130, 100), (130, 200), (140, 500),
230 (170, 100), (180, 300), (160, 100), (40, 100), (170, 100);
231 set join_cache_level=6;
232 explain
233 select * from t1,t2 where t1.b=t2.c and d <= 100;
234 id select_type table type possible_keys key key_len ref rows Extra
235 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
236 1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where
237 select * from t1,t2 where t1.b=t2.c and d <= 100;
238 a b c d v
239 3 30 30 100 101
240 4 20 20 100 101
241 1 20 20 100 101
242 set join_cache_level=default;
243 drop table t1, t2;
244 create table t1 (a bigint, b bigint as (a > '2'));
245 show create table t1;
246 Table Create Table
247 t1 CREATE TABLE `t1` (
248 `a` bigint(20) DEFAULT NULL,
249 `b` bigint(20) GENERATED ALWAYS AS (`a` > 2) VIRTUAL
250 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
251 insert into t1 (a) values (1),(3);
252 select * from t1;
253 a b
254 1 0
255 3 1
256 select * from t1;
257 a b
258 1 0
259 3 1
260 drop table t1;
261 create table t1 (a bigint, b bigint as (a between 0 and 2));
262 show create table t1;
263 Table Create Table
264 t1 CREATE TABLE `t1` (
265 `a` bigint(20) DEFAULT NULL,
266 `b` bigint(20) GENERATED ALWAYS AS (`a` between 0 and 2) VIRTUAL
267 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
268 insert into t1 (a) values (1),(3);
269 select * from t1;
270 a b
271 1 1
272 3 0
273 select * from t1;
274 a b
275 1 1
276 3 0
277 drop table t1;
278 create table t1 (a char(10), b char(10) as (a between 0 and 2));
279 show create table t1;
280 Table Create Table
281 t1 CREATE TABLE `t1` (
282 `a` char(10) DEFAULT NULL,
283 `b` char(10) GENERATED ALWAYS AS (`a` between 0 and 2) VIRTUAL
284 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
285 insert into t1 (a) values (1),(3);
286 select * from t1;
287 a b
288 1 1
289 3 0
290 select * from t1;
291 a b
292 1 1
293 3 0
294 drop table t1;
295 CREATE TABLE `t1` (
296 `a` int(11) NOT NULL,
297 `b` varchar(32) DEFAULT NULL,
298 `c` int(11) AS (a MOD 10) VIRTUAL,
299 `d` varchar(5) AS (LEFT(b,5)) PERSISTENT
300 ) ENGINE=MyISAM;
301 show create table t1;
302 Table Create Table
303 t1 CREATE TABLE `t1` (
304 `a` int(11) NOT NULL,
305 `b` varchar(32) DEFAULT NULL,
306 `c` int(11) GENERATED ALWAYS AS (`a` MOD 10) VIRTUAL,
307 `d` varchar(5) GENERATED ALWAYS AS (left(`b`,5)) STORED
308 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
309 show columns from t1;
310 Field Type Null Key Default Extra
311 a int(11) NO NULL
312 b varchar(32) YES NULL
313 c int(11) YES NULL VIRTUAL GENERATED
314 d varchar(5) YES NULL STORED GENERATED
315 show full columns from t1;
316 Field Type Collation Null Key Default Extra Privileges Comment
317 a int(11) NULL NO NULL #
318 b varchar(32) utf8mb4_uca1400_ai_ci YES NULL #
319 c int(11) NULL YES NULL VIRTUAL GENERATED #
320 d varchar(5) utf8mb4_uca1400_ai_ci YES NULL STORED GENERATED #
321 INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL);
322 UPDATE IGNORE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a';
323 Warnings:
324 Warning 1906 The value specified for generated column 'd' in table 't1' has been ignored
325 INSERT IGNORE INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
326 Warnings:
327 Warning 1906 The value specified for generated column 'd' in table 't1' has been ignored
328 set sql_mode='strict_all_tables';
329 UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a';
330 ERROR HY000: The value specified for generated column 'd' in table 't1' has been ignored
331 INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
332 ERROR HY000: The value specified for generated column 'd' in table 't1' has been ignored
333 drop table t1;
334 #
335 # MDEV-5611: self-referencing virtual column
336 #
337 create table t1 (a int, b int as (b is null) virtual);
338 ERROR 01000: Expression for field `b` is referring to uninitialized field `b`
339 create table t1 (a int as (1+1), b int as (a is null) virtual);
340 drop table t1;
341 # end of 5.3 tests
342 create table t1 (v1 varchar(255) as (c1) persistent, c1 varchar(50)) collate=latin1_general_ci;
343 show create table t1;
344 Table Create Table
345 t1 CREATE TABLE `t1` (
346 `v1` varchar(255) GENERATED ALWAYS AS (`c1`) STORED,
347 `c1` varchar(50) DEFAULT NULL
348 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
349 drop table t1;
350 set sql_mode='no_zero_date';
351 create table t1 (
352 ts timestamp not null default current_timestamp,
353 tsv timestamp as (adddate(ts, interval 1 day)) virtual
354 );
355 drop table t1;
356 set sql_mode=default;
357 #
358 # MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value
359 #
360 SET sql_mode='NO_ZERO_IN_DATE';
361 CREATE TABLE t1
362 (
363 a datetime DEFAULT NULL,
364 b datetime DEFAULT NULL,
365 c time GENERATED ALWAYS AS (timediff(`a`,`b`)) VIRTUAL
366 );
367 INSERT INTO t1 VALUES ('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002',DEFAULT);
368 SELECT * FROM t1;
369 a b c
370 2008-12-31 23:59:59 2008-12-30 01:01:01 46:58:58
371 DROP TABLE t1;
372 SET sql_mode=DEFAULT;
373 #
374 # MDEV-15834 The code in TABLE_SHARE::init_from_binary_frm_image() is not safe
375 #
376 SHOW TABLES;
377 Tables_in_test
378 t1
379 SHOW CREATE TABLE t1;
380 ERROR HY000: Incorrect information in file: './test/t1.frm'
381 ALTER TABLE t1;
382 ERROR HY000: Incorrect information in file: './test/t1.frm'
383 #
384 # End of 5.5 tests
385 #
386 #
387 # End of 10.0 tests
388 #
389 #
390 # MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column
391 #
392 CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)));
393 SHOW CREATE TABLE t1;
394 Table Create Table
395 t1 CREATE TABLE `t1` (
396 `a` datetime DEFAULT NULL,
397 `b` timestamp GENERATED ALWAYS AS (cast(`a` as datetime)) VIRTUAL
398 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
399 DROP TABLE t1;
400 CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)),c TIMESTAMP);
401 SHOW CREATE TABLE t1;
402 Table Create Table
403 t1 CREATE TABLE `t1` (
404 `a` datetime DEFAULT NULL,
405 `b` timestamp GENERATED ALWAYS AS (cast(`a` as datetime)) VIRTUAL,
406 `c` timestamp NULL DEFAULT NULL
407 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
408 DROP TABLE t1;
409 #
410 # MDEV-8839 COLUMN_GET() produces warnings with no data
411 #
412 SET @aaa= COLUMN_CREATE('price', _binary 0xF0F1F2F3F4F5F6F7);
413 SELECT COLUMN_GET(@aaa, 'price' AS DECIMAL) aaa;
414 aaa
415 0
416 Warnings:
417 Warning 1918 Encountered illegal value '\xF0\xF1\xF2\xF3\xF4\xF5\xF6\xF7' when converting to DECIMAL
418 SELECT COLUMN_GET(@aaa, 'price' AS INT) aaa;
419 aaa
420 0
421 Warnings:
422 Warning 1918 Encountered illegal value '\xF0\xF1\xF2\xF3\xF4\xF5\xF6\xF7' when converting to INT
423 SELECT COLUMN_GET(@aaa, 'price' AS DOUBLE) aaa;
424 aaa
425 0
426 Warnings:
427 Warning 1918 Encountered illegal value '\xF0\xF1\xF2\xF3\xF4\xF5\xF6\xF7' when converting to DOUBLE
428 #
429 # MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column
430 #
431 SET sql_mode=STRICT_ALL_TABLES;
432 CREATE OR REPLACE TABLE t1 (
433 a INT NOT NULL DEFAULT 10,
434 b INT AS (a+1) VIRTUAL
435 ) ENGINE=MyISAM;
436 INSERT INTO t1 (b) VALUES (10);
437 ERROR HY000: The value specified for generated column 'b' in table 't1' has been ignored
438 INSERT INTO t1 (b) VALUES (DEFAULT(a));
439 ERROR HY000: The value specified for generated column 'b' in table 't1' has been ignored
440 INSERT INTO t1 (b) VALUES (DEFAULT);
441 INSERT INTO t1 VALUES (10,10);
442 ERROR HY000: The value specified for generated column 'b' in table 't1' has been ignored
443 INSERT INTO t1 VALUES (10,DEFAULT(a));
444 ERROR HY000: The value specified for generated column 'b' in table 't1' has been ignored
445 INSERT INTO t1 VALUES (10, DEFAULT);
446 DROP TABLE t1;
447 SET sql_mode=DEFAULT;
448 #
449 # End of 10.1 tests
450 #
451 #
452 # MDEV-16518 MYSQL57_GENERATED_FIELD: The code in TABLE_SHARE::init_from_binary_frm_image() is not safe
453 #
454 SHOW TABLES;
455 Tables_in_test
456 t1
457 SHOW CREATE TABLE t1;
458 ERROR HY000: Incorrect information in file: './test/t1.frm'
459 ALTER TABLE t1;
460 ERROR HY000: Incorrect information in file: './test/t1.frm'
461 #
462 # MDEV-19771 REPLACE on table with virtual_field can cause crash in set_ok_status()
463
464 create or replace table t1 (pk int primary key, col_bit bit(15) default null,
465 vcol_bit bit(10) GENERATED ALWAYS AS (`col_bit`) VIRTUAL);
466 replace INTO `t1` (`pk`,col_bit) VALUES (99,1000);
467 select pk, col_bit+0, vcol_bit+0 from t1;
468 pk col_bit+0 vcol_bit+0
469 99 1000 1000
470 replace INTO `t1` (`pk`,col_bit) VALUES (99,10000);
471 select pk, col_bit+0, vcol_bit+0 from t1;
472 pk col_bit+0 vcol_bit+0
473 99 10000 1023
474 REPLACE LOW_PRIORITY INTO `t1` (`pk`) VALUES (99);
475 ERROR 22001: Data too long for column 'vcol_bit' at row 1
476 drop table t1;
477 #
478 # MDEV-17837 REPLACE on table with virtual_field can cause crash in set_ok_status()
479 #
480 SET @old_sql_mode=@@sql_mode;
481 SET sql_mode= STRICT_ALL_TABLES;
482 CREATE TABLE t1 (
483 pk INT,
484 i TINYINT,
485 vi TINYINT AS (i+1) PERSISTENT,
486 PRIMARY KEY(pk)
487 );
488 INSERT INTO t1 (pk,i) VALUES (1,1);
489 TRUNCATE TABLE t1;
490 INSERT IGNORE INTO t1 (pk,i) VALUES (1,127);
491 Warnings:
492 Warning 1264 Out of range value for column 'vi' at row 1
493 REPLACE INTO t1 (pk,i) VALUES (1,2);
494 ERROR 22003: Out of range value for column 'vi' at row 1
495 DROP TABLE t1;
496 SET @sql_mode=@old_sql_mode;
497 #
498 # MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column
499 # 10.2+ specific part
500 #
501 SET sql_mode=STRICT_ALL_TABLES;
502 CREATE OR REPLACE TABLE t1 (
503 a INT NOT NULL DEFAULT 10,
504 b INT AS (a+1) VIRTUAL
505 ) ENGINE=MyISAM;
506 EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING DEFAULT;
507 EXECUTE IMMEDIATE 'INSERT INTO t1 (b) VALUES(?)' USING IGNORE;
508 INSERT INTO t1 (b) VALUES (DEFAULT);
509 INSERT INTO t1 (b) VALUES (IGNORE);
510 SELECT * FROM t1;
511 a b
512 10 11
513 10 11
514 10 11
515 10 11
516 DELETE FROM t1;
517 EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES(10,?)' USING DEFAULT;
518 EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES(11,?)' USING IGNORE;
519 INSERT INTO t1 VALUES (12,DEFAULT);
520 INSERT INTO t1 VALUES (13,IGNORE);
521 SELECT * FROM t1;
522 a b
523 10 11
524 11 12
525 12 13
526 13 14
527 DROP TABLE t1;
528 SET sql_mode=DEFAULT;
529 #
530 # CONTEXT_ANALYSIS_ONLY_VCOL_EXPR
531 #
532 call mtr.add_suppression("Charset id.*trying to replace");
533 create table t1 (c1 char(1) character set ucs2 collate ucs2_test_ci,
534 v1 char(1) character set ucs2 collate ucs2_test_ci as (c1),
535 v2 int as (c1 = 'b'),
536 v3 int as (v1 = 'b'));
537 insert into t1 (c1) values ('a');
538 select * from t1 where v1 = 'b';
539 c1 v1 v2 v3
540 a a 1 1
541 show create table t1;
542 Table Create Table
543 t1 CREATE TABLE `t1` (
544 `c1` char(1) CHARACTER SET ucs2 COLLATE ucs2_test_ci DEFAULT NULL,
545 `v1` char(1) CHARACTER SET ucs2 COLLATE ucs2_test_ci GENERATED ALWAYS AS (`c1`) VIRTUAL,
546 `v2` int(11) GENERATED ALWAYS AS (`c1` = 'b') VIRTUAL,
547 `v3` int(11) GENERATED ALWAYS AS (`v1` = 'b') VIRTUAL
548 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
549 drop table t1;
550 #
551 # End of 10.2 tests
552 #
553 #
554 # RAND is session func
555 #
556 create table t1 (a int, b float default rand(1));
557 insert into t1 (a) values (1);
558 insert into t1 (a) values (2);
559 insert into t1 (a) values (3);
560 select * from t1;
561 a b
562 1 0.405404
563 2 0.405404
564 3 0.405404
565 drop table t1;
566 #
567 # End of 10.3 tests
568 #
569 #
570 # MDEV-31112 vcol circular references lead to stack overflow
571 #
572 create table t (a int, c int as (a));
573 alter table t alter column c drop default;
574 alter table t modify column a int as (c) stored;
575 ERROR 01000: Expression for field `a` is referring to uninitialized field `c`
576 drop table t;
577 #
578 # End of 10.4 tests
579 #
580 #
581 # MDEV-26891 Segfault in Field::register_field_in_read_map upon INSERT DELAYED with virtual columns
582 #
583 CREATE TABLE t (
584 id INT AUTO_INCREMENT,
585 a varchar(16) NOT NULL DEFAULT '',
586 b varchar(16) GENERATED ALWAYS AS (a) VIRTUAL,
587 KEY `col_year` (b(8),id)
588 ) ENGINE=MyISAM;
589 INSERT DELAYED INTO t (a) VALUES ('foo'),('bar');
590 DROP TABLE t;
591 #
592 # End of 10.5 tests
593 #