New simpler bugfix for UPDATE and virtual BLOBs

When updating a table with virtual BLOB columns, the following might
happen:
- an old record is read from the table, it has no virtual blob values
- update_virtual_fields() is run, vcol blob gets its value into the
  record. But only a pointer to the value is in the table->record[0],
  the value is in Field_blob::value String (but it doesn't have to be!
  it can be in the record, if the column is just a copy of another
  columns: ... b VARCHAR, c BLOB AS (b) ...)
- store_record(table,record[1]), old record now is in record[1]
- fill_record() prepares new values in record[0], vcol blob is updated,
  new value replaces the old one in the Field_blob::value
- now both record[1] and record[0] have a pointer that points to the
  *new* vcol blob value. Or record[1] has a pointer to nowhere if
   Field_blob::value had to realloc.

To fix this I have introduced a new String object 'read_value' in
Field_blob.  When updating virtual columns when a row has been read,
the allocated value is stored in 'read_value' instead of 'value'.  The
allocated blobs for the new row is stored in 'value' as before.

I also made, as a safety precaution, the insert delayed handling of
blobs more general by using value to store strings instead of the
record.  This ensures that virtual functions on delayed insert should
work in as in the case of normal insert.

Triggers are now properly updating the read, write and vcol maps for used
fields. This means that we don't need VCOL_UPDATE_FOR_READ_WRITE anymore
and there is no need for any other special handling of triggers in
update_virtual_fields().

To be able to test how many times virtual fields are invoked, I also
relaxed rules that one can use local (@) variables in DEFAULT and non
persistent virtual field expressions.

17 files changed, 643 insertions, 52 deletions

diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result
index dc7a33d6b9c..ac449d44fee 100644
--- a/mysql-test/r/default.result
+++ b/mysql-test/r/default.result
@@ -480,6 +480,10 @@ a	b
 2	2
 3	4
 drop table t1;
+CREATE OR REPLACE TABLE t1 (a INT DEFAULT @v);
+drop table t1;
+CREATE TABLE t1 (a INT DEFAULT @v:=1);
+drop table t1;
 #
 # Error handling
 #
@@ -516,10 +520,6 @@ CREATE TABLE t1 (a INT DEFAULT(?));
 Got one of the listed errors
 CREATE TABLE t1 (a INT DEFAULT (b), b INT DEFAULT(a));
 ERROR 01000: Expression for field `a` is refering to uninitialized field `b`
-CREATE TABLE t1 (a INT DEFAULT @v);
-ERROR HY000: Function or expression '@v' cannot be used in the DEFAULT clause of `a`
-CREATE TABLE t1 (a INT DEFAULT @v:=1);
-ERROR HY000: Function or expression '@v' cannot be used in the DEFAULT clause of `a`
 CREATE TABLE t1 (a INT DEFAULT(NAME_CONST('xxx', 'yyy'));
 ERROR HY000: Function or expression 'name_const()' cannot be used in the DEFAULT clause of `a`
 CREATE TABLE t1 (a INT DEFAULT COUNT(*));
diff --git a/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
index eb7e6ad32b9..f807405d18d 100644
--- a/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
+++ b/mysql-test/suite/vcol/inc/vcol_trigger_sp.inc
@@ -149,3 +149,135 @@ DROP TRIGGER t1_ins_aft;
 DROP TRIGGER t1_del_bef;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Examine the number of times triggers are recalculated for updates
+--echo #
+
+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
+);
+
+DELIMITER |;
+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 |
+
+DELIMITER ;|
+
+--echo # 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;
+select @a,@b,@c;
+select * from t1;
+select @a,@b,@c;
+select a,b,c from t1;
+select @a,@b,@c;
+select a,b,c,blob_a from t1;
+select @a,@b,@c;
+
+--echo # 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;
+select * from t1;
+
+drop trigger t1_ins;
+drop trigger t1_update;
+drop table t1;
+
+--echo #
+--echo # Same test, but with virtual keys
+--echo #
+
+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 (b) VIRTUAL,
+  blob_c blob GENERATED ALWAYS AS (c) VIRTUAL,
+  key (a),
+  key (blob_a(10)),
+  key (blob_b(10)),
+  key (blob_c(10))
+);
+
+DELIMITER |;
+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 |
+
+DELIMITER ;|
+
+--echo # 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;
+select @a,@b,@c;
+select * from t1;
+select @a,@b,@c;
+select a,b,c from t1;
+select @a,@b,@c;
+select a,b,c,blob_a from t1;
+select @a,@b,@c;
+
+--echo # 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;
+
+drop trigger t1_ins;
+drop trigger t1_update;
+drop table t1;
diff --git a/mysql-test/suite/vcol/r/not_supported.result b/mysql-test/suite/vcol/r/not_supported.result
index f2c98706dd6..c804cf220d2 100644
--- a/mysql-test/suite/vcol/r/not_supported.result
+++ b/mysql-test/suite/vcol/r/not_supported.result
@@ -4,14 +4,14 @@ set time_zone='+10:00';
 set div_precision_increment=20;
 create table t1 (a int, b int, v decimal(20,19) as (a/3));
 create table t2 (a int, b int, v int as (a+@a));
-ERROR HY000: Function or expression '@a' cannot be used in the GENERATED ALWAYS AS clause of `v`
+drop table t2;
 create table t2 (a int, b int, v int as (a+@a) PERSISTENT);
 ERROR HY000: Function or expression '@a' cannot be used in the GENERATED ALWAYS AS clause of `v`
 create table t3_ok (a int, b int, v int as (a+@@error_count));
 create table t3 (a int, b int, v int as (a+@@error_count) PERSISTENT);
 ERROR HY000: Function or expression '@@error_count' cannot be used in the GENERATED ALWAYS AS clause of `v`
 create table t4 (a int, b int, v int as (@a:=a));
-ERROR HY000: Function or expression '@a' cannot be used in the GENERATED ALWAYS AS clause of `v`
+drop table t4;
 create table t4 (a int, b int, v int as (@a:=a) PERSISTENT);
 ERROR HY000: Function or expression '@a' cannot be used in the GENERATED ALWAYS AS clause of `v`
 create table t8 (a int, b int, v varchar(100) as (from_unixtime(a)));
diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result
index 1d78bbf50e4..0a82f1006e7 100644
--- a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result
@@ -125,3 +125,182 @@ c
 DROP TRIGGER t1_ins_aft;
 DROP TRIGGER t1_del_bef;
 DROP TABLE t1,t2;
+#
+# Examine the number of times triggers are recalculated for updates
+#
+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;
+#
+# Same test, but with virtual keys
+#
+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 (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;
diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
index 77efa8fe6b9..edafd474286 100644
--- a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result
@@ -125,3 +125,182 @@ c
 DROP TRIGGER t1_ins_aft;
 DROP TRIGGER t1_del_bef;
 DROP TABLE t1,t2;
+#
+# Examine the number of times triggers are recalculated for updates
+#
+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;
+#
+# Same test, but with virtual keys
+#
+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 (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;
diff --git a/mysql-test/suite/vcol/t/not_supported.test b/mysql-test/suite/vcol/t/not_supported.test
index b7544cb33a4..1ea7970523a 100644
--- a/mysql-test/suite/vcol/t/not_supported.test
+++ b/mysql-test/suite/vcol/t/not_supported.test
@@ -2,7 +2,7 @@
 # MDEV-7113 difference between check_vcol_func_processor and check_partition_func_processor
 #
 
-# the following functions must not be supported in virtual columns.
+# the following functions must not be supported in persistent columns.
 # but for compatibility reasons it won't be done in a GA version,
 # we'll only fix most critical issues (inconsistent results, crashes)
 
@@ -13,15 +13,13 @@ set time_zone='+10:00';
 set div_precision_increment=20;
 
 create table t1 (a int, b int, v decimal(20,19) as (a/3));
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t2 (a int, b int, v int as (a+@a));
+create table t2 (a int, b int, v int as (a+@a)); drop table t2;
 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
 create table t2 (a int, b int, v int as (a+@a) PERSISTENT);
 create table t3_ok (a int, b int, v int as (a+@@error_count));
 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
 create table t3 (a int, b int, v int as (a+@@error_count) PERSISTENT);
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create table t4 (a int, b int, v int as (@a:=a));
+create table t4 (a int, b int, v int as (@a:=a)); drop table t4;
 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
 create table t4 (a int, b int, v int as (@a:=a) PERSISTENT);
 create table t8 (a int, b int, v varchar(100) as (from_unixtime(a)));
diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test
index 41ed1613448..9ae088405fa 100644
--- a/mysql-test/t/default.test
+++ b/mysql-test/t/default.test
@@ -359,6 +359,8 @@ insert into t1 (b) values(2);
 insert into t1 (a,b) values(3,4);
 select * from t1;
 drop table t1;
+CREATE OR REPLACE TABLE t1 (a INT DEFAULT @v); drop table t1;
+CREATE TABLE t1 (a INT DEFAULT @v:=1); drop table t1;
 
 --echo #
 --echo # Error handling
@@ -408,12 +410,6 @@ CREATE TABLE t1 (a INT DEFAULT(?));
 CREATE TABLE t1 (a INT DEFAULT (b), b INT DEFAULT(a));
 
 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT @v);
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT @v:=1);
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
 CREATE TABLE t1 (a INT DEFAULT(NAME_CONST('xxx', 'yyy'));
 
 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
diff --git a/sql/field.h b/sql/field.h
index 0d278968eb5..400d2ef0e5e 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -3176,6 +3176,12 @@ protected:
     The 'value'-object is a cache fronting the storage engine.
   */
   String value;
+  /**
+     Cache for blob values when reading a row with a virtual blob
+     field. This is needed to not destroy the old cached value when
+     updating the blob with a new value when creating the new row.
+  */
+  String read_value;
 
   static void do_copy_blob(Copy_field *copy);
   static void do_conv_blob(Copy_field *copy);
@@ -3279,7 +3285,7 @@ public:
     return (uint32) (((ulonglong) 1 << (packlength*8)) -1);
   }
   int reset(void) { bzero(ptr, packlength+sizeof(uchar*)); return 0; }
-  void reset_fields() { bzero((uchar*) &value,sizeof(value)); }
+  void reset_fields() { bzero((uchar*) &value,sizeof(value)); bzero((uchar*) &read_value,sizeof(read_value)); }
   uint32 get_field_buffer_size(void) { return value.alloced_length(); }
   void store_length(uchar *i_ptr, uint i_packlength, uint32 i_number);
   inline void store_length(uint32 number)
@@ -3332,13 +3338,41 @@ public:
     memcpy(ptr+packlength, &tmp, sizeof(char*));
     return 0;
   }
+  /* store value for the duration of the current read record */
+  inline void swap_value_and_read_value()
+  {
+    read_value.swap(value);
+  }
+  inline void set_value(uchar *data)
+  {
+    /* Set value pointer. Lengths are not important */
+    value.reset((char*) data, 1, 1, &my_charset_bin);
+  }
   virtual uchar *pack(uchar *to, const uchar *from, uint max_length);
   virtual const uchar *unpack(uchar *to, const uchar *from,
                               const uchar *from_end, uint param_data);
   uint packed_col_length(const uchar *col_ptr, uint length);
   uint max_packed_col_length(uint max_length);
-  void free() { value.free(); }
-  inline void clear_temporary() { bzero((uchar*) &value, sizeof(value)); }
+  void free()
+  {
+    value.free();
+    read_value.free();
+  }
+  inline void clear_temporary()
+  {
+    uchar *tmp= get_ptr();
+    if (likely(value.ptr() == (char*) tmp))
+      bzero((uchar*) &value, sizeof(value));
+    else
+    {
+      /*
+        Currently read_value should never point to tmp, the following code
+        is mainly here to make things future proof.
+      */
+      if (unlikely(read_value.ptr() == (char*) tmp))
+        bzero((uchar*) &read_value, sizeof(read_value));
+    }
+  }
   uint size_of() const { return sizeof(*this); }
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 8e912fe83c8..754ba9d261c 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4580,7 +4580,7 @@ longlong Item_func_sleep::val_int()
 
 bool Item_func_user_var::check_vcol_func_processor(void *arg)
 {
-  return mark_unsupported_function("@", name.str, arg, VCOL_IMPOSSIBLE);
+  return mark_unsupported_function("@", name.str, arg, VCOL_NON_DETERMINISTIC);
 }
 
 #define extra_size sizeof(double)
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index b6cfd2f2cc1..46d7d07dbdf 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -8043,17 +8043,14 @@ fill_record_n_invoke_before_triggers(THD *thd, TABLE *table,
       Re-calculate virtual fields to cater for cases when base columns are
       updated by the triggers.
     */
-    List_iterator_fast<Item> f(fields);
-    Item *fld;
-    Item_field *item_field;
-    if (fields.elements)
+    if (table->vfield && fields.elements)
     {
-      fld= (Item_field*)f++;
-      item_field= fld->field_for_view_update();
-      if (item_field && table->vfield)
+      Item *fld= (Item_field*) fields.head();
+      Item_field *item_field= fld->field_for_view_update();
+      if (item_field)
       {
         DBUG_ASSERT(table == item_field->field->table);
-        result= table->update_virtual_fields(VCOL_UPDATE_FOR_WRITE);
+        result|= table->update_virtual_fields(VCOL_UPDATE_FOR_WRITE);
       }
     }
   }
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 7b583e1ddec..cea42667c48 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1691,8 +1691,12 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
       }
       if (table->vfield)
       {
+        /*
+          We have not yet called update_virtual_fields(VOL_UPDATE_FOR_READ)
+          in handler methods for the just read row in record[1].
+        */
         table->move_fields(table->field, table->record[1], table->record[0]);
-        table->update_virtual_fields(VCOL_UPDATE_INDEXED);
+        table->update_virtual_fields(VCOL_UPDATE_FOR_REPLACE);
         table->move_fields(table->field, table->record[0], table->record[1]);
       }
       if (info->handle_duplicates == DUP_UPDATE)
@@ -2912,6 +2916,8 @@ pthread_handler_t handle_delayed_insert(void *arg)
     thd->mdl_context.set_needs_thr_lock_abort(TRUE);
 
     di->table->mark_columns_needed_for_insert();
+    /* Mark all columns for write as we don't know which columns we get from user */
+    bitmap_set_all(di->table->write_set);
 
     /* Now wait until we get an insert or lock to handle */
     /* We will not abort as long as a client thread uses this thread */
@@ -3079,7 +3085,7 @@ pthread_handler_t handle_delayed_insert(void *arg)
 }
 
 
-/* Remove pointers from temporary fields to allocated values */
+/* Remove all pointers to data for blob fields so that original table doesn't try to free them */
 
 static void unlink_blobs(register TABLE *table)
 {
@@ -3097,9 +3103,23 @@ static void free_delayed_insert_blobs(register TABLE *table)
   for (Field **ptr=table->field ; *ptr ; ptr++)
   {
     if ((*ptr)->flags & BLOB_FLAG)
+      ((Field_blob *) *ptr)->free();
+  }
+}
+
+
+/* set value field for blobs to point to data in record */
+
+static void set_delayed_insert_blobs(register TABLE *table)
+{
+  for (Field **ptr=table->field ; *ptr ; ptr++)
+  {
+    if ((*ptr)->flags & BLOB_FLAG)
     {
-      my_free(((Field_blob *) (*ptr))->get_ptr());
-      ((Field_blob *) (*ptr))->reset();
+      Field_blob *blob= ((Field_blob *) *ptr);
+      uchar *data= blob->get_ptr();
+      if (data)
+        blob->set_value(data);     // Set value.ptr() to point to data
     }
   }
 }
@@ -3157,6 +3177,8 @@ bool Delayed_insert::handle_inserts(void)
     stacked_inserts--;
     mysql_mutex_unlock(&mutex);
     memcpy(table->record[0],row->record,table->s->reclength);
+    if (table->s->blob_fields)
+      set_delayed_insert_blobs(table);
 
     thd.start_time=row->start_time;
     thd.query_start_used=row->query_start_used;
@@ -3227,6 +3249,16 @@ bool Delayed_insert::handle_inserts(void)
     if (info.handle_duplicates == DUP_UPDATE)
       table->file->extra(HA_EXTRA_INSERT_WITH_UPDATE);
     thd.clear_error(); // reset error for binlog
+
+    if (table->vfield)
+    {
+      /*
+        Virtual fields where not calculated by caller as the temporary TABLE object used
+        had vcol_set empty. Better to calculate them here to make the caller faster.
+      */
+     table->update_virtual_fields(VCOL_UPDATE_FOR_WRITE);
+    }
+
     if (write_record(&thd, table, &info))
     {
       info.error_count++;				// Ignore errors
@@ -3348,6 +3380,7 @@ bool Delayed_insert::handle_inserts(void)
     if (table->s->blob_fields)
     {
       memcpy(table->record[0],row->record,table->s->reclength);
+      set_delayed_insert_blobs(table);
       free_delayed_insert_blobs(table);
     }
     delete row;
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 14812d2f73b..2d1619018b2 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -2266,6 +2266,7 @@ void Table_triggers_list::mark_fields_used(trg_event_type event)
 {
   int action_time;
   Item_trigger_field *trg_field;
+  DBUG_ENTER("Table_triggers_list::mark_fields_used");
 
   for (action_time= 0; action_time < (int)TRG_ACTION_MAX; action_time++)
   {
@@ -2280,14 +2281,19 @@ void Table_triggers_list::mark_fields_used(trg_event_type event)
         /* We cannot mark fields which does not present in table. */
         if (trg_field->field_idx != (uint)-1)
         {
+          DBUG_PRINT("info", ("marking field: %d", trg_field->field_idx));
           bitmap_set_bit(trigger_table->read_set, trg_field->field_idx);
           if (trg_field->get_settable_routine_parameter())
             bitmap_set_bit(trigger_table->write_set, trg_field->field_idx);
+          if (trigger_table->field[trg_field->field_idx]->vcol_info)
+            trigger_table->mark_virtual_col(trigger_table->
+                                            field[trg_field->field_idx]);
         }
       }
     }
   }
   trigger_table->file->column_bitmaps_signal();
+  DBUG_VOID_RETURN;
 }
 
 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 05f7080609a..476d1a4e104 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -618,8 +618,6 @@ int mysql_update(THD *thd,
       while (!(error=info.read_record(&info)) && !thd->killed)
       {
         explain->buf_tracker.on_record_read();
-        if (table->vfield)
-          table->update_virtual_fields(VCOL_UPDATE_FOR_READ_WRITE);
         thd->inc_examined_row_count(1);
 	if (!select || (error= select->skip_record(thd)) > 0)
 	{
@@ -735,8 +733,6 @@ int mysql_update(THD *thd,
   while (!(error=info.read_record(&info)) && !thd->killed)
   {
     explain->tracker.on_record_read();
-    if (table->vfield)
-      table->update_virtual_fields(VCOL_UPDATE_FOR_READ_WRITE);
     thd->inc_examined_row_count(1);
     if (!select || select->skip_record(thd) > 0)
     {
@@ -2327,6 +2323,17 @@ int multi_update::do_updates()
       goto err;
     }
     table->file->extra(HA_EXTRA_NO_CACHE);
+    /*
+      We have to clear the base record, if we have virtual indexed
+      blob fields, as some storage engines will access the blob fields
+      to calculate the keys to see if they have changed. Without
+      clearing the blob pointers will contain random values which can
+      cause a crash.
+      This is a workaround for engines that access columns not present in
+      either read or write set.
+    */
+    if (table->vfield)
+      empty_record(table);
 
     check_opt_it.rewind();
     while(TABLE *tbl= check_opt_it++)
diff --git a/sql/table.cc b/sql/table.cc
index 5c9d4805943..b7f83a502b7 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -6572,6 +6572,7 @@ bool TABLE::mark_virtual_columns_for_write(bool insert_fl)
 {
   Field **vfield_ptr, *tmp_vfield;
   bool bitmap_updated= false;
+  DBUG_ENTER("mark_virtual_columns_for_write");
 
   for (vfield_ptr= vfield; *vfield_ptr; vfield_ptr++)
   {
@@ -6610,7 +6611,7 @@ bool TABLE::mark_virtual_columns_for_write(bool insert_fl)
   }
   if (bitmap_updated)
     file->column_bitmaps_signal();
-  return bitmap_updated;
+  DBUG_RETURN(bitmap_updated);
 }
 
 /*
@@ -7324,6 +7325,7 @@ bool is_simple_order(ORDER *order)
 int TABLE::update_virtual_fields(enum_vcol_update_mode update_mode)
 {
   DBUG_ENTER("TABLE::update_virtual_fields");
+  DBUG_PRINT("enter", ("update_mode: %d", update_mode));
   Field **vfield_ptr, *vf;
   DBUG_ASSERT(vfield);
 
@@ -7336,25 +7338,39 @@ int TABLE::update_virtual_fields(enum_vcol_update_mode update_mode)
     DBUG_ASSERT(vcol_info);
     DBUG_ASSERT(vcol_info->expr);
 
-    bool update;
+    bool update, swap_values= 0;
     switch (update_mode) {
-    case VCOL_UPDATE_FOR_READ_WRITE:
-      if (triggers)
-      {
-        update= true;
-        break;
-      }
     case VCOL_UPDATE_FOR_READ:
       update= !vcol_info->stored_in_db
            && !(key_read && vf->part_of_key.is_set(file->active_index))
            && bitmap_is_set(vcol_set, vf->field_index);
+      swap_values= 1;
       break;
     case VCOL_UPDATE_FOR_WRITE:
-      update= triggers || bitmap_is_set(vcol_set, vf->field_index);
+      update= bitmap_is_set(vcol_set, vf->field_index);
       break;
-    case VCOL_UPDATE_INDEXED:
+    case VCOL_UPDATE_FOR_REPLACE:
       update= !vcol_info->stored_in_db && (vf->flags & PART_KEY_FLAG)
            && bitmap_is_set(vcol_set, vf->field_index);
+      if (update && (vf->flags & BLOB_FLAG))
+      {
+        /*
+          The row has been read into record[1] and Field_blob::value
+          contains the value for record[0].  Swap value and read_value
+          to ensure that the virtual column data for the read row will
+          be in read_value at the end of this function
+        */
+        ((Field_blob*) vf)->swap_value_and_read_value();
+        /* Ensure we call swap_value_and_read_value() after update */
+        swap_values= 1;
+      }
+      break;
+    case VCOL_UPDATE_INDEXED:
+      /* Read indexed fields that was not updated in VCOL_UPDATE_FOR_READ */
+      update= (!vcol_info->stored_in_db && (vf->flags & PART_KEY_FLAG) &&
+               bitmap_is_set(vcol_set, vf->field_index) &&
+               (key_read && vf->part_of_key.is_set(file->active_index)));
+      swap_values= 1;
       break;
     }
 
@@ -7363,6 +7379,16 @@ int TABLE::update_virtual_fields(enum_vcol_update_mode update_mode)
       /* Compute the actual value of the virtual fields */
       vcol_info->expr->save_in_field(vf, 0);
       DBUG_PRINT("info", ("field '%s' - updated", vf->field_name));
+      if (swap_values && (vf->flags & BLOB_FLAG))
+      {
+        /*
+          Remember the read value to allow other update_virtual_field() calls
+          for the same blob field for the row to be updated.
+          Field_blob->read_value always contains the virtual column data for
+          any read row.
+        */
+        ((Field_blob*) vf)->swap_value_and_read_value();
+      }
     }
     else
     {
diff --git a/sql/table.h b/sql/table.h
index b2d5599b740..facba06a3cc 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -327,9 +327,9 @@ enum release_type { RELEASE_NORMAL, RELEASE_WAIT_FOR_DROP };
 enum enum_vcol_update_mode
 {
   VCOL_UPDATE_FOR_READ= 0,
-  VCOL_UPDATE_FOR_READ_WRITE,
   VCOL_UPDATE_FOR_WRITE,
-  VCOL_UPDATE_INDEXED
+  VCOL_UPDATE_INDEXED,
+  VCOL_UPDATE_FOR_REPLACE
 };
 
 
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 53157258d2e..ca14542c9b5 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -1913,7 +1913,7 @@ innobase_row_to_mysql(
 	}
 	if (table->vfield) {
 		my_bitmap_map*	old_vcol_set = tmp_use_all_columns(table, table->vcol_set);
-		table->update_virtual_fields(VCOL_UPDATE_FOR_READ_WRITE);
+		table->update_virtual_fields(VCOL_UPDATE_FOR_READ);
 		tmp_restore_column_map(table->vcol_set, old_vcol_set);
 	}
 }
diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc
index 9e09853871d..881e90d95c1 100644
--- a/storage/myisam/ha_myisam.cc
+++ b/storage/myisam/ha_myisam.cc
@@ -664,8 +664,12 @@ static int compute_vcols(MI_INFO *info, uchar *record, int keynum)
   TABLE *table= (TABLE*)(info->external_ref);
   table->move_fields(table->field, record, table->field[0]->record_ptr());
   if (keynum == -1) // update all vcols
-    return table->update_virtual_fields(VCOL_UPDATE_INDEXED);
-
+  {
+    int error= table->update_virtual_fields(VCOL_UPDATE_FOR_READ);
+    if (table->update_virtual_fields(VCOL_UPDATE_INDEXED))
+      error= 1;
+    return error;
+  }
   // update only one key
   KEY *key= table->key_info + keynum;
   KEY_PART_INFO *kp= key->key_part, *end= kp + key->ext_key_parts;