1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
#
# Test how UPDATE detects what columns need to be read (or generated) in a row
#
# stored column depends on virtual column depends on updated column.
# this tests TABLE::mark_virtual_columns_for_write()
#
create table t1 (a int, b int as (a+1), c int as (b+1) stored);
insert t1 set a=1;
select * from t1;
update t1 set a=2;
select * from t1;
drop table t1;
#
# one keypart is virtual, the other keypart is updated
# this tests TABLE::mark_columns_needed_for_update()
#
create table t1 (a int, c int as(a), p varchar(20) as(rtrim(y)), y char(20), index (p,c));
show create table t1;
insert into t1 (a,y) values(1, "yyy");
update t1 set a = 100 where a = 1;
check table t1;
drop table t1;
#
# note: prefix keys below
#
create table t1 (
a varchar(10000),
b varchar(3000),
c varchar(14000) generated always as (concat(a,b)) virtual,
d varchar(5000) generated always as (b) virtual,
e int(11) generated always as (10) virtual,
h int(11) not null primary key,
index(c(100), d(20)));
insert t1 (a,b,h) values (repeat('g', 10000), repeat('x', 2800), 1);
update t1 set a = repeat(cast(1 as char), 2000);
drop table t1;
create table t1 (
a varchar(10000),
b varchar(3000),
c varchar(14000) generated always as (concat(a,b)) virtual,
i varchar(5000) generated always as (b) virtual,
d varchar(5000) generated always as (i) virtual,
e int(11) generated always as (10) virtual,
h int(11) not null primary key,
index(c(100), d(20)));
insert t1 (a,b,h) values (repeat('g', 10000), repeat('x', 2800), 1);
update t1 set a = repeat(cast(1 as char), 2000);
drop table t1;
#
# UPDATE disguised as INSERT
#
create table t1(a blob not null, b int, c varbinary (10) generated always as (a) virtual, unique (c(9)));
insert t1 (a,b) values ('a', 1);
replace t1 set a = 'a',b =1;
insert t1 (a,b) values ('a', 1) on duplicate key update a='b', b=2;
select * from t1;
drop table t1;
#
# multi-UPDATE and const tables
#
create table t (a int primary key, b int, c int as (b), index (c));
insert t (a,b) values (9,0);
create table t2 select * from t;
update t, t2 set t.b=10 where t.a=t2.a;
check table t; select * from t;
drop table t, t2;
#
# blobs
# This tests BLOB_VALUE_ORPHANAGE
#
create table t1 (a int, b int, c int, d int, e int);
insert t1 values (1,2,3,4,5), (1,2,3,4,5);
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
create table t (a int primary key,
b int, c blob as (b), index (c(57)),
d blob, e blob as (d), index (e(57)))
replace select * from t1;
check table t; select * from t;
update t set a=10, b=1, d=1;
check table t; select * from t;
replace t (a,b,d) values (10,2,2);
check table t; select * from t;
--error ER_WRONG_VALUE_COUNT_ON_ROW
insert t(a,b,d) values (10) on duplicate key update b=3;
insert t(a,b,d) values (10,2,2) on duplicate key update b=3, d=3;
check table t; select * from t;
replace t (a,b,d) select 10,4,4;
check table t; select * from t;
insert t(a,b,d) select 10,4,4 on duplicate key update b=5, d=5;
check table t; select * from t;
replace delayed t (a,b,d) values (10,6,6);
flush tables t;
check table t; select * from t;
insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7;
flush tables t;
check table t; select * from t;
--write_file $MYSQLTEST_VARDIR/tmp/vblobs.txt
10 8 foo 8 foo
EOF
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
--eval load data infile '$MYSQLTEST_VARDIR/tmp/vblobs.txt' replace into table t
--remove_file $MYSQLTEST_VARDIR/tmp/vblobs.txt
check table t; select * from t;
update t set a=11, b=9, d=9 where a>5;
check table t; select * from t;
create table t2 select * from t;
update t, t2 set t.b=10, t.d=10 where t.a=t2.a;
check table t; select * from t;
update t, t tt set t.b=11, tt.d=11 where t.a=tt.a;
check table t; select * from t;
drop table t, t1, t2;
#
# MDEV-13623 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in virtual longlong Field_long::val_int
#
create table t (f1 int, f2 int, f3 int as (f1*2) virtual, key(f3,f2));
insert into t (f1,f2) values (1,1),(2,2);
create view v as
select a2.f1, a2.f2, a1.f3
from t a1, t a2
where a2.f3 <> 0
with local check option;
update v set f3 = 52;
drop view v;
drop table t;
|
