Debian Codegraph @

mysql-test/suite/vcol/t/vcol_misc.test

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