| 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; |