summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result')
-rw-r--r--mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result316
1 files changed, 316 insertions, 0 deletions
diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
new file mode 100644
index 00000000..467e7f8b
--- /dev/null
+++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
@@ -0,0 +1,316 @@
+SET @@session.default_storage_engine = 'MyISAM';
+create table t1 (a int,
+b int as (a/10),
+c int as (a/10) persistent);
+create table t2 (a timestamp);
+create trigger trg1 before insert on t1 for each row
+begin
+if (new.b < 10) then
+set new.a:= 100;
+set new.b:= 9;
+set new.c:= 9;
+end if;
+if (new.c > 50) then
+set new.a:= 500;
+end if;
+end|
+create trigger trg2 after insert on t1 for each row
+begin
+if (new.b >= 60) then
+insert into t2 values (now());
+end if;
+end|
+create function f1()
+returns int
+begin
+declare sum1 int default '0';
+declare cur1 cursor for select sum(b) from t1;
+open cur1;
+fetch cur1 into sum1;
+close cur1;
+return sum1;
+end|
+set sql_warnings = 1;
+insert into t1 (a) values (200);
+select * from t1;
+a b c
+200 20 20
+select * from t2;
+a
+insert into t1 (a) values (10);
+select * from t1;
+a b c
+200 20 20
+100 10 10
+select * from t2;
+a
+insert into t1 (a) values (600);
+select * from t1;
+a b c
+200 20 20
+100 10 10
+500 50 50
+select * from t2;
+a
+select f1();
+f1()
+80
+set sql_warnings = 0;
+drop trigger trg1;
+drop trigger trg2;
+drop table t2;
+create procedure p1()
+begin
+declare i int default '0';
+create table t2 like t1;
+insert into t2 (a) values (100), (200);
+begin
+declare cur1 cursor for select sum(c) from t2;
+open cur1;
+fetch cur1 into i;
+close cur1;
+if (i=30) then
+insert into t1 values (300,default,default);
+end if;
+end;
+end|
+delete from t1;
+call p1();
+select * from t2;
+a b c
+100 10 10
+200 20 20
+select * from t1;
+a b c
+300 30 30
+drop table t1,t2;
+drop procedure p1;
+#
+# MDEV-3845 values of virtual columns are not computed for triggers
+#
+CREATE TABLE t1 (
+a INTEGER UNSIGNED NULL DEFAULT NULL,
+b INTEGER UNSIGNED GENERATED ALWAYS AS (a) VIRTUAL
+);
+CREATE TABLE t2 (c INTEGER UNSIGNED NOT NULL);
+CREATE TRIGGER t1_ins_aft
+AFTER INSERT
+ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 (c) VALUES (NEW.b);
+END |
+CREATE TRIGGER t1_del_bef
+BEFORE DELETE
+ON t1
+FOR EACH ROW
+BEGIN
+INSERT INTO t2 (c) VALUES (OLD.b);
+END |
+INSERT INTO t1 (a) VALUES (1), (2), (3);
+SELECT * FROM t2;
+c
+1
+2
+3
+DELETE FROM t1;
+SELECT * FROM t2;
+c
+1
+2
+3
+1
+2
+3
+DROP TRIGGER t1_ins_aft;
+DROP TRIGGER t1_del_bef;
+DROP TABLE t1,t2;
+create table t1 (i int, t time not null, vt time(4) as (t) virtual);
+create trigger trg before update on t1 for each row set @a = 1;
+insert ignore into t1 (i) values (1);
+Warnings:
+Warning 1364 Field 't' doesn't have a default value
+drop table t1;
+#
+# Examine the number of times triggers are recalculated for updates
+#
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
+CREATE TABLE t1 (
+a INTEGER UNSIGNED NULL DEFAULT NULL,
+b CHAR(10) NULL DEFAULT NULL,
+c blob NULL DEFAULT NULL,
+blob_a blob GENERATED ALWAYS AS (last_value(@a:=@a+1,a)) VIRTUAL,
+blob_b blob GENERATED ALWAYS AS (last_value(@b:=@b+1,b)) VIRTUAL,
+blob_c blob GENERATED ALWAYS AS (last_value(@c:=@c+1,c)) VIRTUAL
+);
+CREATE TRIGGER t1_ins
+BEFORE INSERT
+ON t1
+FOR EACH ROW
+BEGIN
+IF NEW.b IS NULL THEN
+SET NEW.b="generated before insert";
+END IF;
+END |
+CREATE TRIGGER t1_update
+BEFORE UPDATE
+ON t1
+FOR EACH ROW
+BEGIN
+IF NEW.b IS NULL or NEW.c IS NULL THEN
+SET NEW.b="generated before update";
+SET NEW.c="generated before update";
+END IF;
+END |
+# Inserts
+set @a=0,@b=0,@c=0;
+insert into t1 (a) values(1);
+insert into t1 (a,b) values(2, "*2*");
+insert into t1 (a,b,c) values(3, "*3*", "**3**");
+insert into t1 (a,c) values(4, "**4**");
+select * from t1;
+a b c blob_a blob_b blob_c
+1 generated NULL 1 generated NULL
+2 *2* NULL 2 *2* NULL
+3 *3* **3** 3 *3* **3**
+4 generated **4** 4 generated **4**
+select @a,@b,@c;
+@a @b @c
+4 4 4
+select * from t1;
+a b c blob_a blob_b blob_c
+1 generated NULL 1 generated NULL
+2 *2* NULL 2 *2* NULL
+3 *3* **3** 3 *3* **3**
+4 generated **4** 4 generated **4**
+select @a,@b,@c;
+@a @b @c
+8 8 8
+select a,b,c from t1;
+a b c
+1 generated NULL
+2 *2* NULL
+3 *3* **3**
+4 generated **4**
+select @a,@b,@c;
+@a @b @c
+8 8 8
+select a,b,c,blob_a from t1;
+a b c blob_a
+1 generated NULL 1
+2 *2* NULL 2
+3 *3* **3** 3
+4 generated **4** 4
+select @a,@b,@c;
+@a @b @c
+12 8 8
+# updates
+set @a=0,@b=0,@c=0;
+update t1 set a=a+100 where a=1;
+update t1 set a=a+100, b="*102*" where a=2;
+update t1 set a=a+100, b=NULL where a=3;
+update t1 set a=a+100, b="invisible", c=NULL where a=4;
+select @a,@b,@c;
+@a @b @c
+0 0 0
+select * from t1;
+a b c blob_a blob_b blob_c
+101 generated generated before update 101 generated generated before update
+102 generated generated before update 102 generated generated before update
+103 generated generated before update 103 generated generated before update
+104 generated generated before update 104 generated generated before update
+drop trigger t1_ins;
+drop trigger t1_update;
+drop table t1;
+SET sql_mode = DEFAULT;
+#
+# Same test, but with virtual keys
+#
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
+CREATE TABLE t1 (
+a INTEGER UNSIGNED NULL DEFAULT NULL,
+b CHAR(10) NULL DEFAULT NULL,
+c blob NULL DEFAULT NULL,
+blob_a blob GENERATED ALWAYS AS (a) VIRTUAL,
+blob_b blob GENERATED ALWAYS AS (RTRIM(b)) VIRTUAL,
+blob_c blob GENERATED ALWAYS AS (c) VIRTUAL,
+key (a),
+key (blob_a(10)),
+key (blob_b(10)),
+key (blob_c(10))
+);
+CREATE TRIGGER t1_ins
+BEFORE INSERT
+ON t1
+FOR EACH ROW
+BEGIN
+IF NEW.b IS NULL THEN
+SET NEW.b="generated before insert";
+END IF;
+END |
+CREATE TRIGGER t1_update
+BEFORE UPDATE
+ON t1
+FOR EACH ROW
+BEGIN
+IF NEW.b IS NULL or NEW.c IS NULL THEN
+SET NEW.b="generated before update";
+SET NEW.c="generated before update";
+END IF;
+END |
+# Inserts
+insert into t1 (a) values(1);
+insert into t1 (a,b) values(2, "*2*");
+insert into t1 (a,b,c) values(3, "*3*", "**3**");
+insert into t1 (a,c) values(4, "**4**");
+select * from t1;
+a b c blob_a blob_b blob_c
+1 generated NULL 1 generated NULL
+2 *2* NULL 2 *2* NULL
+3 *3* **3** 3 *3* **3**
+4 generated **4** 4 generated **4**
+select @a,@b,@c;
+@a @b @c
+4 4 4
+select * from t1;
+a b c blob_a blob_b blob_c
+1 generated NULL 1 generated NULL
+2 *2* NULL 2 *2* NULL
+3 *3* **3** 3 *3* **3**
+4 generated **4** 4 generated **4**
+select @a,@b,@c;
+@a @b @c
+4 4 4
+select a,b,c from t1;
+a b c
+1 generated NULL
+2 *2* NULL
+3 *3* **3**
+4 generated **4**
+select @a,@b,@c;
+@a @b @c
+4 4 4
+select a,b,c,blob_a from t1;
+a b c blob_a
+1 generated NULL 1
+2 *2* NULL 2
+3 *3* **3** 3
+4 generated **4** 4
+select @a,@b,@c;
+@a @b @c
+4 4 4
+# updates
+update t1 set a=a+100 where a=1;
+update t1 set a=a+100, b="*102*" where a=2;
+update t1 set a=a+100, b=NULL where a=3;
+update t1 set a=a+100, b="invisible", c=NULL where a=4;
+select * from t1;
+a b c blob_a blob_b blob_c
+101 generated generated before update 101 generated generated before update
+102 generated generated before update 102 generated generated before update
+103 generated generated before update 103 generated generated before update
+104 generated generated before update 104 generated generated before update
+drop trigger t1_ins;
+drop trigger t1_update;
+drop table t1;
+SET sql_mode = DEFAULT;
generated by cgit v1.2.3 (git 2.39.1) at 2024-09-17 06:34:15 +0000