summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/vcol/t/update.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/vcol/t/update.test
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/vcol/t/update.test')
-rw-r--r--mysql-test/suite/vcol/t/update.test129
1 files changed, 129 insertions, 0 deletions
diff --git a/mysql-test/suite/vcol/t/update.test b/mysql-test/suite/vcol/t/update.test
new file mode 100644
index 00000000..6065f926
--- /dev/null
+++ b/mysql-test/suite/vcol/t/update.test
@@ -0,0 +1,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;