Debian Codegraph @

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

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