Merge bb-10.2-ext into 10.3

41 files changed, 2861 insertions, 523 deletions

diff --git a/mysql-test/include/update_use_source.inc b/mysql-test/include/update_use_source.inc
new file mode 100644
index 00000000000..864b58e5d7f
--- /dev/null
+++ b/mysql-test/include/update_use_source.inc
@@ -0,0 +1,147 @@
+# Include to test update with same table as source and target
+
+--echo #
+--echo # Update a with value from subquery on the same table, no search clause. ALL access
+--echo #
+
+start transaction;
+--enable_info ONCE
+update t1
+   set c1=(select a.c3
+             from t1 a
+            where a.c3 = t1.c3);
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+rollback;
+
+--echo #
+--echo # Update with search clause on the same table
+--echo #
+
+start transaction;
+--enable_info ONCE
+update t1
+   set c1=10
+ where c1 <2
+   and exists (select 'X'
+                 from t1 a
+                where a.c1 = t1.c1);
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+rollback;
+
+--echo #
+--echo # Update via RANGE or INDEX access if an index or a primary key exists
+--echo #
+
+explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
+start transaction;
+--enable_info ONCE
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+rollback;
+
+--echo #
+--echo # Update with order by
+--echo #
+
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+rollback;
+
+--echo #
+--echo Update using a view in subquery
+--echo #
+
+start transaction;
+--enable_info ONCE
+update t1
+   set c1=c1 +(select max(a.c2)
+             from v1 a
+            where a.c1 = t1.c1) ;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+rollback;
+
+--echo #
+--echo # Update throw a view
+--echo #
+
+start transaction;
+--enable_info ONCE
+update v1
+   set c1=c1 + (select max(a.c2)
+             from t1 a
+            where a.c1 = v1.c1) +10
+where c3 > 3;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+rollback;
+
+--echo #
+--echo # Update through a view and using the view in subquery
+--echo #
+
+start transaction;
+--enable_info ONCE
+update v1
+   set c1=c1 + 1
+ where c1 <2
+   and exists (select 'X'
+                 from v1 a
+                where a.c1 = v1.c1);
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+rollback;
+
+--echo #
+--echo # Update through  a view and using the view in subquery
+--echo #
+
+start transaction;
+--enable_info ONCE
+update v1
+   set c1=(select max(a.c1)+10
+             from v1 a
+            where a.c1 = v1.c1)
+ where c1 <10
+   and exists (select 'X'
+                 from v1 a
+                where a.c2 = v1.c2);
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+rollback;
+
+--echo #
+--echo # Update of the index or primary key (c3)
+--echo #
+
+start transaction;
+explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+--enable_info ONCE
+update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+select c3 from t1;
+rollback;
+
+--echo #
+--echo # update with a limit
+--echo #
+
+start transaction;
+--enable_info ONCE
+update t1
+   set c1=(select a.c3
+             from t1 a
+            where a.c3 = t1.c3)
+    limit 2;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+rollback;
+
+--echo #
+--echo # update with a limit and an order by
+--echo #
+
+start transaction;
+--enable_info ONCE
+update t1
+   set c1=(select a.c3
+             from t1 a
+            where a.c3 = t1.c3)
+  order by c3 desc limit 2;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+rollback;
diff --git a/mysql-test/r/func_int.result b/mysql-test/r/func_int.result
new file mode 100644
index 00000000000..05573858b17
--- /dev/null
+++ b/mysql-test/r/func_int.result
@@ -0,0 +1,134 @@
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-13965 Parameter data type control for Item_longlong_func
+#
+SELECT ROW(1,1) | 1;
+ERROR HY000: Illegal parameter data type row for operation '|'
+SELECT 1 | ROW(1,1);
+ERROR HY000: Illegal parameter data type row for operation '|'
+SELECT ROW(1,1) & 1;
+ERROR HY000: Illegal parameter data type row for operation '&'
+SELECT 1 & ROW(1,1);
+ERROR HY000: Illegal parameter data type row for operation '&'
+SELECT ROW(1,1) << 1;
+ERROR HY000: Illegal parameter data type row for operation '<<'
+SELECT 1 << ROW(1,1);
+ERROR HY000: Illegal parameter data type row for operation '<<'
+SELECT ROW(1,1) >> 1;
+ERROR HY000: Illegal parameter data type row for operation '>>'
+SELECT 1 >> ROW(1,1);
+ERROR HY000: Illegal parameter data type row for operation '>>'
+SELECT ~ROW(1,1);
+ERROR HY000: Illegal parameter data type row for operation '~'
+SELECT TO_SECONDS(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'to_seconds'
+SELECT TIMESTAMPDIFF(SECOND,ROW(1,1), 1);
+ERROR HY000: Illegal parameter data type row for operation 'timestampdiff'
+SELECT TIMESTAMPDIFF(SECOND,1, ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'timestampdiff'
+SELECT INET_ATON(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'inet_aton'
+SELECT LAST_INSERT_ID(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'last_insert_id'
+#
+# MDEV-13967 Parameter data type control for Item_long_func
+#
+SELECT STRCMP(ROW(1,1),'');
+ERROR HY000: Illegal parameter data type row for operation 'strcmp'
+SELECT STRCMP('',ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'strcmp'
+SELECT CHAR_LENGTH(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'char_length'
+SELECT OCTET_LENGTH(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'octet_length'
+SELECT UNCOMPRESSED_LENGTH(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'uncompressed_length'
+SELECT COERCIBILITY(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'coercibility'
+SELECT ASCII(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'ascii'
+SELECT CRC32(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'crc32'
+SELECT ORD(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'ord'
+SELECT SIGN(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'sign'
+SELECT LOCATE(ROW(1,1),'a',1);
+ERROR HY000: Illegal parameter data type row for operation 'locate'
+SELECT LOCATE('a',ROW(1,1),1);
+ERROR HY000: Illegal parameter data type row for operation 'locate'
+SELECT LOCATE('a','a',ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'locate'
+SELECT BIT_COUNT(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'bit_count'
+SELECT BENCHMARK(1, ROW(1,1));
+ERROR 21000: Operand should contain 1 column(s)
+SELECT BENCHMARK(ROW(1,1),'');
+ERROR HY000: Illegal parameter data type row for operation 'benchmark'
+SELECT SLEEP(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'sleep'
+SELECT GET_LOCK('x', ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'get_lock'
+SELECT GET_LOCK(ROW(1,1),'x');
+ERROR HY000: Illegal parameter data type row for operation 'get_lock'
+SELECT PERIOD_ADD(ROW(1,1),1);
+ERROR HY000: Illegal parameter data type row for operation 'period_add'
+SELECT PERIOD_ADD(1,ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'period_add'
+SELECT PERIOD_DIFF(ROW(1,1),1);
+ERROR HY000: Illegal parameter data type row for operation 'period_diff'
+SELECT PERIOD_DIFF(1,ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'period_diff'
+SELECT TO_DAYS(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'to_days'
+SELECT DAYOFMONTH(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'dayofmonth'
+SELECT DAYOFYEAR(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'dayofyear'
+SELECT QUARTER(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'quarter'
+SELECT YEAR(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'year'
+SELECT YEARWEEK(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'yearweek'
+SELECT WEEK(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'week'
+SELECT WEEK(ROW(1,1),1);
+ERROR HY000: Illegal parameter data type row for operation 'week'
+SELECT WEEK(1,ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'week'
+SELECT HOUR(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'hour'
+SELECT MINUTE(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'minute'
+SELECT SECOND(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'second'
+SELECT MICROSECOND(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'microsecond'
+SELECT JSON_DEPTH(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'json_depth'
+SELECT JSON_LENGTH(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'json_length'
+SELECT JSON_LENGTH('json', ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'json_length'
+SELECT JSON_LENGTH(ROW(1,1), ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'json_length'
+SELECT REGEXP_INSTR(ROW(1,1),'');
+ERROR HY000: Illegal parameter data type row for operation 'regexp_instr'
+SELECT REGEXP_INSTR('',ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'regexp_instr'
+SELECT FIND_IN_SET(ROW(1,1),'');
+ERROR HY000: Illegal parameter data type row for operation 'find_in_set'
+SELECT FIND_IN_SET('',ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'find_in_set'
+SELECT RELEASE_LOCK(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'release_lock'
+SELECT IS_FREE_LOCK(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'is_free_lock'
+SELECT IS_USED_LOCK(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'is_used_lock'
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result
index 53c58d5dddb..187df785f69 100644
--- a/mysql-test/r/func_regexp.result
+++ b/mysql-test/r/func_regexp.result
@@ -157,3 +157,9 @@ SELECT ' '  REGEXP '[[:space:]]';
 SELECT '\t' REGEXP '[[:space:]]';
 '\t' REGEXP '[[:space:]]'
 1
+#
+# MDEV-13967 Parameter data type control for Item_long_func
+#
+SELECT REGEXP_INSTR('111222333',2);
+REGEXP_INSTR('111222333',2)
+4
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index fb0179026c9..9da6caef8e6 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -3294,3 +3294,34 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
 SET sql_mode=DEFAULT;
+#
+# MDEV-13966 Parameter data type control for Item_temporal_func
+#
+SELECT FROM_DAYS(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'from_days'
+SELECT MAKEDATE(ROW(1,1),1);
+ERROR HY000: Illegal parameter data type row for operation 'makedate'
+SELECT MAKEDATE(1, ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'makedate'
+SELECT LAST_DAY(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'last_day'
+SELECT SEC_TO_TIME(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'sec_to_time'
+SELECT TIMEDIFF(ROW(1,1),1);
+ERROR HY000: Illegal parameter data type row for operation 'timediff'
+SELECT TIMEDIFF(1, ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'timediff'
+SELECT MAKETIME(ROW(1,1),1,1);
+ERROR HY000: Illegal parameter data type row for operation 'maketime'
+SELECT MAKETIME(1, ROW(1,1), 1);
+ERROR HY000: Illegal parameter data type row for operation 'maketime'
+SELECT MAKETIME(1, 1, ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'maketime'
+SELECT FROM_UNIXTIME(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'from_unixtime'
+SELECT CONVERT_TZ(ROW(1,1),1,1);
+ERROR HY000: Illegal parameter data type row for operation 'convert_tz'
+SELECT CONVERT_TZ(1, ROW(1,1), 1);
+ERROR HY000: Illegal parameter data type row for operation 'convert_tz'
+SELECT CONVERT_TZ(1, 1, ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'convert_tz'
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index c9a2a83edd1..fe67da8001f 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -4668,5 +4668,255 @@ ERROR HY000: Illegal parameter data type varchar for operation 'st_touches'
 SELECT MBRTOUCHES(POINT(1,1), 'test');
 ERROR HY000: Illegal parameter data type varchar for operation 'st_touches'
 #
+# MDEV-13964 Parameter data type control for Item_real_func
+#
+SELECT EXP(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'exp'
+SELECT LN(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'ln'
+SELECT LOG2(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'log2'
+SELECT LOG10(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'log10'
+SELECT SQRT(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'sqrt'
+SELECT ACOS(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'acos'
+SELECT ASIN(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'asin'
+SELECT COS(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'cos'
+SELECT SIN(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'sin'
+SELECT TAN(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'tan'
+SELECT COT(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'cot'
+SELECT LOG(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'log'
+SELECT LOG(POINT(1,1),POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'log'
+SELECT LOG(1, POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'log'
+SELECT ATAN(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'atan'
+SELECT ATAN(POINT(1,1),POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'atan'
+SELECT ATAN(1, POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'atan'
+SELECT POW(POINT(1,1),POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'pow'
+SELECT RAND(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'rand'
+SELECT RADIANS(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'radians'
+SELECT DEGREES(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'degrees'
+SELECT EXP(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'exp'
+SELECT LN(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'ln'
+SELECT LOG2(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'log2'
+SELECT LOG10(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'log10'
+SELECT SQRT(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'sqrt'
+SELECT ACOS(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'acos'
+SELECT ASIN(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'asin'
+SELECT COS(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'cos'
+SELECT SIN(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'sin'
+SELECT TAN(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'tan'
+SELECT COT(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'cot'
+SELECT LOG(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'log'
+SELECT LOG(ROW(1,1),ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'log'
+SELECT LOG(1, ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'log'
+SELECT ATAN(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'atan'
+SELECT ATAN(ROW(1,1),ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'atan'
+SELECT ATAN(1, ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'atan'
+SELECT POW(ROW(1,1),ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'pow'
+SELECT RAND(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'rand'
+SELECT RADIANS(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'radians'
+SELECT DEGREES(ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'degrees'
+#
+# MDEV-13965 Parameter data type control for Item_longlong_func
+#
+SELECT POINT(1,1) | 1;
+ERROR HY000: Illegal parameter data type geometry for operation '|'
+SELECT 1 | POINT(1,1);
+ERROR HY000: Illegal parameter data type geometry for operation '|'
+SELECT POINT(1,1) & 1;
+ERROR HY000: Illegal parameter data type geometry for operation '&'
+SELECT 1 & POINT(1,1);
+ERROR HY000: Illegal parameter data type geometry for operation '&'
+SELECT POINT(1,1) << 1;
+ERROR HY000: Illegal parameter data type geometry for operation '<<'
+SELECT 1 << POINT(1,1);
+ERROR HY000: Illegal parameter data type geometry for operation '<<'
+SELECT POINT(1,1) >> 1;
+ERROR HY000: Illegal parameter data type geometry for operation '>>'
+SELECT 1 >> POINT(1,1);
+ERROR HY000: Illegal parameter data type geometry for operation '>>'
+SELECT ~POINT(1,1);
+ERROR HY000: Illegal parameter data type geometry for operation '~'
+SELECT TO_SECONDS(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'to_seconds'
+SELECT TIMESTAMPDIFF(SECOND,POINT(1,1), 1);
+ERROR HY000: Illegal parameter data type geometry for operation 'timestampdiff'
+SELECT TIMESTAMPDIFF(SECOND,1, POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'timestampdiff'
+SELECT INET_ATON(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'inet_aton'
+SELECT LAST_INSERT_ID(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'last_insert_id'
+#
+# MDEV-13966 Parameter data type control for Item_temporal_func
+#
+SELECT FROM_DAYS(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'from_days'
+SELECT MAKEDATE(POINT(1,1),1);
+ERROR HY000: Illegal parameter data type geometry for operation 'makedate'
+SELECT MAKEDATE(1, POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'makedate'
+SELECT LAST_DAY(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'last_day'
+SELECT SEC_TO_TIME(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'sec_to_time'
+SELECT TIMEDIFF(POINT(1,1),1);
+ERROR HY000: Illegal parameter data type geometry for operation 'timediff'
+SELECT TIMEDIFF(1, POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'timediff'
+SELECT MAKETIME(POINT(1,1),1,1);
+ERROR HY000: Illegal parameter data type geometry for operation 'maketime'
+SELECT MAKETIME(1, POINT(1,1), 1);
+ERROR HY000: Illegal parameter data type geometry for operation 'maketime'
+SELECT MAKETIME(1, 1, POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'maketime'
+SELECT FROM_UNIXTIME(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'from_unixtime'
+SELECT CONVERT_TZ(POINT(1,1),1,1);
+ERROR HY000: Illegal parameter data type geometry for operation 'convert_tz'
+SELECT CONVERT_TZ(1, POINT(1,1), 1);
+ERROR HY000: Illegal parameter data type geometry for operation 'convert_tz'
+SELECT CONVERT_TZ(1, 1, POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'convert_tz'
+#
+# MDEV-13967 Parameter data type control for Item_long_func
+#
+SELECT STRCMP(POINT(1,1),POINT(1,1));
+STRCMP(POINT(1,1),POINT(1,1))
+0
+SELECT CHAR_LENGTH(POINT(1,1));
+CHAR_LENGTH(POINT(1,1))
+25
+SELECT OCTET_LENGTH(POINT(1,1));
+OCTET_LENGTH(POINT(1,1))
+25
+SELECT UNCOMPRESSED_LENGTH(POINT(1,1));
+UNCOMPRESSED_LENGTH(POINT(1,1))
+0
+SELECT COERCIBILITY(POINT(1,1));
+COERCIBILITY(POINT(1,1))
+4
+SELECT ASCII(POINT(1,1));
+ASCII(POINT(1,1))
+0
+SELECT CRC32(POINT(1,1));
+CRC32(POINT(1,1))
+1349318989
+SELECT ORD(POINT(1,1));
+ORD(POINT(1,1))
+0
+SELECT SIGN(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'sign'
+SELECT LOCATE('a','a',POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'locate'
+SELECT LOCATE(POINT(1,1),POINT(1,1));
+LOCATE(POINT(1,1),POINT(1,1))
+1
+SELECT BIT_COUNT(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'bit_count'
+SELECT BENCHMARK(POINT(1,1),'');
+ERROR HY000: Illegal parameter data type geometry for operation 'benchmark'
+SELECT SLEEP(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'sleep'
+SELECT GET_LOCK('x', POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'get_lock'
+SELECT PERIOD_ADD(POINT(1,1),1);
+ERROR HY000: Illegal parameter data type geometry for operation 'period_add'
+SELECT PERIOD_ADD(1,POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'period_add'
+SELECT PERIOD_DIFF(POINT(1,1),1);
+ERROR HY000: Illegal parameter data type geometry for operation 'period_diff'
+SELECT PERIOD_DIFF(1,POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'period_diff'
+SELECT TO_DAYS(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'to_days'
+SELECT DAYOFMONTH(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'dayofmonth'
+SELECT DAYOFYEAR(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'dayofyear'
+SELECT QUARTER(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'quarter'
+SELECT YEAR(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'year'
+SELECT YEARWEEK(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'yearweek'
+SELECT WEEK(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'week'
+SELECT WEEK(POINT(1,1),1);
+ERROR HY000: Illegal parameter data type geometry for operation 'week'
+SELECT WEEK(1,POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'week'
+SELECT HOUR(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'hour'
+SELECT MINUTE(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'minute'
+SELECT SECOND(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'second'
+SELECT MICROSECOND(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'microsecond'
+SELECT JSON_DEPTH(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'json_depth'
+SELECT JSON_LENGTH(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'json_length'
+SELECT JSON_LENGTH('json', POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'json_length'
+SELECT JSON_LENGTH(POINT(1,1), POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'json_length'
+SELECT REGEXP_INSTR(POINT(1,1),'');
+REGEXP_INSTR(POINT(1,1),'')
+1
+SELECT REGEXP_INSTR('',POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'regexp_instr'
+SELECT FIND_IN_SET(POINT(1,1),'');
+FIND_IN_SET(POINT(1,1),'')
+0
+SELECT FIND_IN_SET('',POINT(1,1));
+FIND_IN_SET('',POINT(1,1))
+0
+SELECT RELEASE_LOCK(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'release_lock'
+SELECT IS_FREE_LOCK(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'is_free_lock'
+SELECT IS_USED_LOCK(POINT(1,1));
+ERROR HY000: Illegal parameter data type geometry for operation 'is_used_lock'
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index eb845c63a76..5a404fe46a6 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5883,7 +5883,7 @@ where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@co
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
-2	UNCACHEABLE SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+2	UNCACHEABLE SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 set @counter=0;
 select count(*) from t1 straight_join t2
 where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1);
diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result
index 6ccfe29b2cd..af53f67869d 100644
--- a/mysql-test/r/lowercase_view.result
+++ b/mysql-test/r/lowercase_view.result
@@ -15,74 +15,6 @@ create table t2aA (col1 int);
 create view v1Aa as select * from t1aA;
 create view v2aA as select * from v1aA;
 create view v3Aa as select v2Aa.col1 from v2aA,t2Aa where v2Aa.col1 = t2aA.col1;
-update v2aA set col1 = (select max(col1) from v1Aa);
-ERROR HY000: The definition of table 'v1Aa' prevents operation UPDATE on table 'v2aA'
-update v2Aa set col1 = (select max(col1) from t1Aa);
-ERROR HY000: The definition of table 'v2Aa' prevents operation UPDATE on table 'v2Aa'
-update v2aA set col1 = (select max(col1) from v2Aa);
-ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 'v2aA'
-update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1Aa' prevents operation UPDATE on table 't1aA'
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
-ERROR HY000: Table 'v1aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 't2Aa'
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1Aa' prevents operation UPDATE on table 't2Aa'
-update t2Aa,v1aA set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 't2Aa'
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v2aA'
-update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
-ERROR HY000: Table 't1Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 'v1aA'
-update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1;
-ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
-ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1;
-ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
-ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 't1aA'
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2Aa' prevents operation UPDATE on table 'v1aA'
-update t2Aa,v2aA set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 't2Aa'
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v2aA) where t1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 't2Aa'
-update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from v2Aa) where v1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2Aa' prevents operation UPDATE on table 't2Aa'
-update v3aA set v3Aa.col1 = (select max(col1) from v1aA);
-ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 'v3aA'
-update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
-ERROR HY000: The definition of table 'v3aA' prevents operation UPDATE on table 'v3aA'
-update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
-ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v3aA'
-update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
-ERROR HY000: Table 'v3aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1aA' prevents operation DELETE on table 'v2aA'
-delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 't1Aa'
-delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1;
-ERROR HY000: Table 'v1Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2Aa' prevents operation DELETE on table 'v2Aa'
-delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1;
-ERROR HY000: Table 't1Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 'v1Aa'
-delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1;
-ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2Aa' prevents operation DELETE on table 't1aA'
-delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1;
-ERROR HY000: The definition of table 'v2aA' prevents operation DELETE on table 'v1aA'
 insert into v2Aa values ((select max(col1) from v1aA));
 ERROR HY000: The definition of table 'v1aA' prevents operation INSERT on table 'v2Aa'
 insert into t1aA values ((select max(col1) from v1Aa));
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index f55251ac199..afea08593a6 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -3714,34 +3714,6 @@ insert into m1 (a) values ((select max(a) from v1));
 ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'
 insert into m1 (a) values ((select max(a) from tmp, v1));
 ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'
-update m1 set a = ((select max(a) from m1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from m2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t3, m1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t3, m2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t3, t1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from t3, t2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from tmp, m1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from tmp, m2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from tmp, t1));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from tmp, t2));
-ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update m1 set a = ((select max(a) from v1));
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
-update m1 set a = ((select max(a) from tmp, v1));
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'
 drop view v1;
 drop temporary table tmp;
 drop table t1, t2, t3, m1, m2;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 634b3897ba0..45239f6e090 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -1,4 +1,3 @@
-CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
 create table t1(id1 int not null auto_increment primary key, t char(12));
 create table t2(id2 int not null, t char(12));
 create table t3(id3 int not null, t char(12), index(id3));
@@ -429,6 +428,7 @@ connection root;
 revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
 revoke all privileges on mysqltest.* from mysqltest_1@localhost;
 delete from mysql.user where user=_binary'mysqltest_1';
+flush privileges;
 drop database mysqltest;
 connection default;
 disconnect user1;
@@ -442,7 +442,6 @@ drop table t1, t2, t3;
 create table t1 (col1 int);
 create table t2 (col1 int);
 update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
 ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 drop table t1,t2;
@@ -565,66 +564,6 @@ id	c1	c2
 2	test t	ppc
 9	abc	ppc
 drop table t1, t2;
-CREATE TABLE `t1` (
-`a` int(11) NOT NULL auto_increment,
-`b` int(11) default NULL,
-PRIMARY KEY  (`a`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
-CREATE TABLE `t2` (
-`a` int(11) NOT NULL auto_increment,
-`b` int(11) default NULL,
-PRIMARY KEY  (`a`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
-set @sav_binlog_format=  @@session.binlog_format;
-set @@session.binlog_format= mixed;
-insert into t1 values (1,1),(2,2);
-insert into t2 values (1,1),(4,4);
-reset master;
-UPDATE t2,t1 SET t2.a=t1.a+2;
-ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
-select * from t2 /* must be (3,1), (4,4) */;
-a	b
-3	1
-4	4
-include/show_binlog_events.inc
-Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
-master-bin.000001	#	Annotate_rows	#	#	UPDATE t2,t1 SET t2.a=t1.a+2
-master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
-master-bin.000001	#	Update_rows_v1	#	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	Query	#	#	COMMIT
-delete from t1;
-delete from t2;
-insert into t1 values (1,2),(3,4),(4,4);
-insert into t2 values (1,2),(3,4),(4,4);
-reset master;
-UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a;
-ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
-include/show_binlog_events.inc
-Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
-master-bin.000001	#	Annotate_rows	#	#	UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a
-master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
-master-bin.000001	#	Update_rows_v1	#	#	table_id: # flags: STMT_END_F
-master-bin.000001	#	Query	#	#	COMMIT
-drop table t1, t2;
-set @@session.binlog_format= @sav_binlog_format;
-CREATE TABLE t1 (a int, PRIMARY KEY (a));
-CREATE TABLE t2 (a int, PRIMARY KEY (a));
-CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;
-create trigger trg_del_t3 before  delete on t3 for each row insert into t1 values (1);
-insert into t2 values (1),(2);
-insert into t3 values (1),(2);
-reset master;
-delete t3.* from t2,t3 where t2.a=t3.a;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
-select count(*) from t1 /* must be 1 */;
-count(*)
-1
-select count(*) from t3 /* must be 1 */;
-count(*)
-1
-drop table t1, t2, t3;
 #
 # Bug#49534: multitable IGNORE update with sql_safe_updates error
 # causes debug assertion
@@ -1002,3 +941,30 @@ deallocate prepare stmt1;
 drop view v3,v2,v1;
 drop table t1,t2,t3;
 end of 5.5 tests
+create table t1 (c1 int, c3 int);
+insert t1(c3) values (1), (2), (3), (4), (5), (6), (7), (8);
+create table t2 select * from t1;
+update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 limit 3;
+select * from t1;
+c1	c3
+1	1
+2	2
+3	3
+NULL	4
+NULL	5
+NULL	6
+NULL	7
+NULL	8
+update t1 set c1=NULL;
+update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 desc limit 2;
+select * from t1;
+c1	c3
+NULL	1
+NULL	2
+NULL	3
+NULL	4
+NULL	5
+NULL	6
+7	7
+8	8
+drop table t1, t2;
diff --git a/mysql-test/r/multi_update_binlog.result b/mysql-test/r/multi_update_binlog.result
new file mode 100644
index 00000000000..e77a4530dbf
--- /dev/null
+++ b/mysql-test/r/multi_update_binlog.result
@@ -0,0 +1,61 @@
+CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
+CREATE TABLE `t1` (
+`a` int(11) NOT NULL auto_increment,
+`b` int(11) default NULL,
+PRIMARY KEY  (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
+CREATE TABLE `t2` (
+`a` int(11) NOT NULL auto_increment,
+`b` int(11) default NULL,
+PRIMARY KEY  (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
+set @sav_binlog_format=  @@session.binlog_format;
+set @@session.binlog_format= mixed;
+insert into t1 values (1,1),(2,2);
+insert into t2 values (1,1),(4,4);
+reset master;
+UPDATE t2,t1 SET t2.a=t1.a+2;
+ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+select * from t2 /* must be (3,1), (4,4) */;
+a	b
+3	1
+4	4
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Annotate_rows	#	#	UPDATE t2,t1 SET t2.a=t1.a+2
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+master-bin.000001	#	Update_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+delete from t1;
+delete from t2;
+insert into t1 values (1,2),(3,4),(4,4);
+insert into t2 values (1,2),(3,4),(4,4);
+reset master;
+UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a;
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Annotate_rows	#	#	UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
+master-bin.000001	#	Update_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+drop table t1, t2;
+set @@session.binlog_format= @sav_binlog_format;
+CREATE TABLE t1 (a int, PRIMARY KEY (a));
+CREATE TABLE t2 (a int, PRIMARY KEY (a));
+CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;
+create trigger trg_del_t3 before  delete on t3 for each row insert into t1 values (1);
+insert into t2 values (1),(2);
+insert into t3 values (1),(2);
+reset master;
+delete t3.* from t2,t3 where t2.a=t3.a;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+select count(*) from t1 /* must be 1 */;
+count(*)
+1
+select count(*) from t3 /* must be 1 */;
+count(*)
+1
+drop table t1, t2, t3;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 919693efffb..7ba8b545e6a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -583,8 +583,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 806475b3380..c09f3c94710 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -587,8 +587,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 237a6dbf9bb..0aefeaf44d9 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -590,8 +590,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index af1afe47f32..92defb3c36d 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -586,8 +586,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 75be8642069..b47dab2e79e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -589,8 +589,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index a6a6397375b..9d04ddd9829 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -586,8 +586,6 @@ a	b
 0	10
 1	11
 2	12
-update t1 set b= (select b from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
 update t1 set b= (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/r/update_use_source.result b/mysql-test/r/update_use_source.result
new file mode 100644
index 00000000000..e5585fcee5d
--- /dev/null
+++ b/mysql-test/r/update_use_source.result
@@ -0,0 +1,1201 @@
+create table t1 (old_c1 integer, old_c2 integer,c1 integer, c2 integer, c3 integer) engine=InnoDb;
+create view v1 as select * from t1 where c2=2;
+create trigger trg_t1 before update on t1 for each row
+begin
+set new.old_c1=old.c1;
+set new.old_c2=old.c2;
+end;
+/
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+commit;
+select * from t1;
+old_c1	old_c2	c1	c2	c3
+NULL	NULL	1	1	1
+NULL	NULL	1	2	2
+NULL	NULL	1	3	3
+NULL	NULL	2	1	4
+NULL	NULL	2	2	5
+NULL	NULL	2	3	6
+NULL	NULL	2	4	7
+NULL	NULL	2	5	8
+Test without any index
+#
+# Update a with value from subquery on the same table, no search clause. ALL access
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->1	1	 
+1->2	2	*
+1->3	3	*
+2->4	4	*
+2->5	5	*
+2->6	6	*
+2->7	7	*
+2->8	8	*
+rollback;
+#
+# Update with search clause on the same table
+#
+start transaction;
+update t1
+set c1=10
+where c1 <2
+and exists (select 'X'
+                 from t1 a
+where a.c1 = t1.c1);
+affected rows: 3
+info: Rows matched: 3  Changed: 3  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->10	1	*
+1->10	2	*
+1->10	3	*
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update via RANGE or INDEX access if an index or a primary key exists
+#
+explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+1->11	3	*
+NULL	4	 
+NULL	5	 
+2->12	6	*
+2->12	7	*
+2->12	8	*
+rollback;
+#
+# Update with order by
+#
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+1->11	3	*
+NULL	4	 
+NULL	5	 
+2->12	6	*
+2->12	7	*
+2->12	8	*
+rollback;
+#
+Update using a view in subquery
+#
+start transaction;
+update t1
+set c1=c1 +(select max(a.c2)
+from v1 a
+where a.c1 = t1.c1) ;
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+1->3	1	*
+1->3	2	*
+1->3	3	*
+2->4	4	*
+2->4	5	*
+2->4	6	*
+2->4	7	*
+2->4	8	*
+rollback;
+#
+# Update throw a view
+#
+start transaction;
+update v1
+set c1=c1 + (select max(a.c2)
+from t1 a
+where a.c1 = v1.c1) +10
+where c3 > 3;
+affected rows: 1
+info: Rows matched: 1  Changed: 1  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+2->17	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through a view and using the view in subquery
+#
+start transaction;
+update v1
+set c1=c1 + 1
+where c1 <2
+and exists (select 'X'
+                 from v1 a
+where a.c1 = v1.c1);
+affected rows: 1
+info: Rows matched: 1  Changed: 1  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+1->2	2	*
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through  a view and using the view in subquery
+#
+start transaction;
+update v1
+set c1=(select max(a.c1)+10
+from v1 a
+where a.c1 = v1.c1)
+where c1 <10
+and exists (select 'X'
+                 from v1 a
+where a.c2 = v1.c2);
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+1->11	2	*
+NULL	3	 
+NULL	4	 
+2->12	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update of the index or primary key (c3)
+#
+start transaction;
+explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
+update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select c3 from t1;
+c3
+11
+12
+13
+14
+15
+16
+17
+18
+rollback;
+#
+# update with a limit
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3)
+limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->1	1	 
+1->2	2	*
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# update with a limit and an order by
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3)
+order by c3 desc limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+2->7	7	*
+2->8	8	*
+rollback;
+Test with an index on updated columns
+create index t1_c2 on t1 (c2,c1);
+#
+# Update a with value from subquery on the same table, no search clause. ALL access
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->1	1	 
+1->2	2	*
+1->3	3	*
+2->4	4	*
+2->5	5	*
+2->6	6	*
+2->7	7	*
+2->8	8	*
+rollback;
+#
+# Update with search clause on the same table
+#
+start transaction;
+update t1
+set c1=10
+where c1 <2
+and exists (select 'X'
+                 from t1 a
+where a.c1 = t1.c1);
+affected rows: 3
+info: Rows matched: 3  Changed: 3  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->10	1	*
+1->10	2	*
+1->10	3	*
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update via RANGE or INDEX access if an index or a primary key exists
+#
+explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_c2	t1_c2	5	NULL	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	4	Using index
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+1->11	3	*
+NULL	4	 
+NULL	5	 
+2->12	6	*
+2->12	7	*
+2->12	8	*
+rollback;
+#
+# Update with order by
+#
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+1->11	3	*
+NULL	4	 
+NULL	5	 
+2->12	6	*
+2->12	7	*
+2->12	8	*
+rollback;
+#
+Update using a view in subquery
+#
+start transaction;
+update t1
+set c1=c1 +(select max(a.c2)
+from v1 a
+where a.c1 = t1.c1) ;
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+1->3	1	*
+1->3	2	*
+1->3	3	*
+2->4	4	*
+2->4	5	*
+2->4	6	*
+2->4	7	*
+2->4	8	*
+rollback;
+#
+# Update throw a view
+#
+start transaction;
+update v1
+set c1=c1 + (select max(a.c2)
+from t1 a
+where a.c1 = v1.c1) +10
+where c3 > 3;
+affected rows: 1
+info: Rows matched: 1  Changed: 1  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+2->17	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through a view and using the view in subquery
+#
+start transaction;
+update v1
+set c1=c1 + 1
+where c1 <2
+and exists (select 'X'
+                 from v1 a
+where a.c1 = v1.c1);
+affected rows: 1
+info: Rows matched: 1  Changed: 1  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+1->2	2	*
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through  a view and using the view in subquery
+#
+start transaction;
+update v1
+set c1=(select max(a.c1)+10
+from v1 a
+where a.c1 = v1.c1)
+where c1 <10
+and exists (select 'X'
+                 from v1 a
+where a.c2 = v1.c2);
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+1->11	2	*
+NULL	3	 
+NULL	4	 
+2->12	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update of the index or primary key (c3)
+#
+start transaction;
+explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	t1_c2	NULL	NULL	NULL	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	10	test.t1.c2,test.t1.c1	1	Using index; FirstMatch(t1)
+update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select c3 from t1;
+c3
+11
+12
+13
+14
+15
+16
+17
+18
+rollback;
+#
+# update with a limit
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3)
+limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->1	1	 
+1->2	2	*
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# update with a limit and an order by
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3)
+order by c3 desc limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+2->7	7	*
+2->8	8	*
+rollback;
+Test with an index on updated columns
+create index t1_c3 on t1 (c3);
+#
+# Update a with value from subquery on the same table, no search clause. ALL access
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->1	1	 
+1->2	2	*
+1->3	3	*
+2->4	4	*
+2->5	5	*
+2->6	6	*
+2->7	7	*
+2->8	8	*
+rollback;
+#
+# Update with search clause on the same table
+#
+start transaction;
+update t1
+set c1=10
+where c1 <2
+and exists (select 'X'
+                 from t1 a
+where a.c1 = t1.c1);
+affected rows: 3
+info: Rows matched: 3  Changed: 3  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->10	1	*
+1->10	2	*
+1->10	3	*
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update via RANGE or INDEX access if an index or a primary key exists
+#
+explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_c2	t1_c2	5	NULL	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+1->11	3	*
+NULL	4	 
+NULL	5	 
+2->12	6	*
+2->12	7	*
+2->12	8	*
+rollback;
+#
+# Update with order by
+#
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+1->11	3	*
+NULL	4	 
+NULL	5	 
+2->12	6	*
+2->12	7	*
+2->12	8	*
+rollback;
+#
+Update using a view in subquery
+#
+start transaction;
+update t1
+set c1=c1 +(select max(a.c2)
+from v1 a
+where a.c1 = t1.c1) ;
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+1->3	1	*
+1->3	2	*
+1->3	3	*
+2->4	4	*
+2->4	5	*
+2->4	6	*
+2->4	7	*
+2->4	8	*
+rollback;
+#
+# Update throw a view
+#
+start transaction;
+update v1
+set c1=c1 + (select max(a.c2)
+from t1 a
+where a.c1 = v1.c1) +10
+where c3 > 3;
+affected rows: 1
+info: Rows matched: 1  Changed: 1  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+2->17	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through a view and using the view in subquery
+#
+start transaction;
+update v1
+set c1=c1 + 1
+where c1 <2
+and exists (select 'X'
+                 from v1 a
+where a.c1 = v1.c1);
+affected rows: 1
+info: Rows matched: 1  Changed: 1  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+1->2	2	*
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through  a view and using the view in subquery
+#
+start transaction;
+update v1
+set c1=(select max(a.c1)+10
+from v1 a
+where a.c1 = v1.c1)
+where c1 <10
+and exists (select 'X'
+                 from v1 a
+where a.c2 = v1.c2);
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+1->11	2	*
+NULL	3	 
+NULL	4	 
+2->12	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update of the index or primary key (c3)
+#
+start transaction;
+explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	t1_c2	NULL	NULL	NULL	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	10	test.t1.c2,test.t1.c1	1	Using index; FirstMatch(t1)
+update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select c3 from t1;
+c3
+11
+12
+13
+14
+15
+16
+17
+18
+rollback;
+#
+# update with a limit
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3)
+limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->1	1	 
+1->2	2	*
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# update with a limit and an order by
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3)
+order by c3 desc limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+2->7	7	*
+2->8	8	*
+rollback;
+Test with a primary key on updated columns
+drop index t1_c3 on t1;
+alter table t1 add primary key (c3);
+#
+# Update a with value from subquery on the same table, no search clause. ALL access
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->1	1	 
+1->2	2	*
+1->3	3	*
+2->4	4	*
+2->5	5	*
+2->6	6	*
+2->7	7	*
+2->8	8	*
+rollback;
+#
+# Update with search clause on the same table
+#
+start transaction;
+update t1
+set c1=10
+where c1 <2
+and exists (select 'X'
+                 from t1 a
+where a.c1 = t1.c1);
+affected rows: 3
+info: Rows matched: 3  Changed: 3  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->10	1	*
+1->10	2	*
+1->10	3	*
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update via RANGE or INDEX access if an index or a primary key exists
+#
+explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_c2	t1_c2	5	NULL	2	Using where
+2	DEPENDENT SUBQUERY	a	ref	t1_c2	t1_c2	5	test.t1.c2	1	Using index
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+1->11	3	*
+NULL	4	 
+NULL	5	 
+2->12	6	*
+2->12	7	*
+2->12	8	*
+rollback;
+#
+# Update with order by
+#
+start transaction;
+update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+1->11	3	*
+NULL	4	 
+NULL	5	 
+2->12	6	*
+2->12	7	*
+2->12	8	*
+rollback;
+#
+Update using a view in subquery
+#
+start transaction;
+update t1
+set c1=c1 +(select max(a.c2)
+from v1 a
+where a.c1 = t1.c1) ;
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+1->3	1	*
+1->3	2	*
+1->3	3	*
+2->4	4	*
+2->4	5	*
+2->4	6	*
+2->4	7	*
+2->4	8	*
+rollback;
+#
+# Update throw a view
+#
+start transaction;
+update v1
+set c1=c1 + (select max(a.c2)
+from t1 a
+where a.c1 = v1.c1) +10
+where c3 > 3;
+affected rows: 1
+info: Rows matched: 1  Changed: 1  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+2->17	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through a view and using the view in subquery
+#
+start transaction;
+update v1
+set c1=c1 + 1
+where c1 <2
+and exists (select 'X'
+                 from v1 a
+where a.c1 = v1.c1);
+affected rows: 1
+info: Rows matched: 1  Changed: 1  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+1->2	2	*
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update through  a view and using the view in subquery
+#
+start transaction;
+update v1
+set c1=(select max(a.c1)+10
+from v1 a
+where a.c1 = v1.c1)
+where c1 <10
+and exists (select 'X'
+                 from v1 a
+where a.c2 = v1.c2);
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+1->11	2	*
+NULL	3	 
+NULL	4	 
+2->12	5	*
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# Update of the index or primary key (c3)
+#
+start transaction;
+explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	t1_c2	NULL	NULL	NULL	8	Using where
+1	PRIMARY	a	ref	t1_c2	t1_c2	10	test.t1.c2,test.t1.c1	1	Using index; FirstMatch(t1)
+update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+affected rows: 8
+info: Rows matched: 8  Changed: 8  Warnings: 0
+select c3 from t1;
+c3
+11
+14
+12
+15
+13
+16
+17
+18
+rollback;
+#
+# update with a limit
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3)
+limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+1->1	1	 
+1->2	2	*
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+NULL	7	 
+NULL	8	 
+rollback;
+#
+# update with a limit and an order by
+#
+start transaction;
+update t1
+set c1=(select a.c3
+from t1 a
+where a.c3 = t1.c3)
+order by c3 desc limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
+concat(old_c1,'->',c1)	c3	Changed
+NULL	1	 
+NULL	2	 
+NULL	3	 
+NULL	4	 
+NULL	5	 
+NULL	6	 
+2->7	7	*
+2->8	8	*
+rollback;
+# Update with error "Subquery returns more than 1 row"
+update t1 set c2=(select c2 from t1);
+ERROR 21000: Subquery returns more than 1 row
+# Update with error "Subquery returns more than 1 row" and order by
+update t1 set c2=(select c2 from t1) order by c3;
+ERROR 21000: Subquery returns more than 1 row
+Duplicate value on update a primary key
+start transaction;
+update t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
+rollback;
+Duplicate value on update a primary key with ignore
+start transaction;
+update ignore t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+affected rows: 4
+info: Rows matched: 4  Changed: 4  Warnings: 0
+rollback;
+Duplicate value on update a primary key and limit
+start transaction;
+update t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 limit 2;
+ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
+rollback;
+Duplicate value on update a primary key with ignore and limit
+start transaction;
+update ignore t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 limit 2;
+affected rows: 2
+info: Rows matched: 2  Changed: 2  Warnings: 0
+rollback;
+# Update no rows found
+update t1
+set c1=10
+where c1 <2
+and exists (select 'X'
+                 from t1 a
+where a.c1 = t1.c1 + 10);
+affected rows: 0
+info: Rows matched: 0  Changed: 0  Warnings: 0
+# Update no rows changed
+drop trigger trg_t1;
+start transaction;
+update t1
+set c1=c1
+where c1 <2
+and exists (select 'X'
+                 from t1 a
+where a.c1 = t1.c1);
+affected rows: 0
+info: Rows matched: 3  Changed: 0  Warnings: 0
+rollback;
+#
+# Check call of after trigger
+#
+create or replace trigger trg_t2 after update on t1 for each row
+begin
+declare msg varchar(100);
+if (new.c3 = 5) then
+set msg=concat('in after update trigger on ',new.c3);
+SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
+end if;
+end;
+/
+update t1 set c1=2 where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
+ERROR 45000: in after update trigger on 5
+#
+# Check update with order by and after trigger
+#
+update t1 set c1=2 where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1) order by t1.c2;
+ERROR 45000: in after update trigger on 5
+drop view v1;
+#
+# Check update on view with check option
+#
+create view v1 as select * from t1 where c2=2 with check option;
+start transaction;
+update v1 set c2=3 where c1=1;
+ERROR 44000: CHECK OPTION failed `test`.`v1`
+rollback;
+start transaction;
+update v1 set c2=(select max(c3) from v1) where c1=1;
+ERROR 44000: CHECK OPTION failed `test`.`v1`
+rollback;
+start transaction;
+update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1;
+rollback;
+drop view v1;
+drop table t1;
+#
+# Test with a temporary table
+#
+create temporary table t1 (c1 integer, c2 integer, c3 integer) engine=InnoDb;
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+start transaction;
+update t1
+set c1=(select a.c2
+from t1 a
+where a.c3 = t1.c3) limit 3;
+affected rows: 2
+info: Rows matched: 3  Changed: 2  Warnings: 0
+select * from t1 ;
+c1	c2	c3
+1	1	1
+2	2	2
+3	3	3
+2	1	4
+2	2	5
+2	3	6
+2	4	7
+2	5	8
+rollback;
+drop table t1;
+#
+# Test on dynamic columns (blob)
+#
+create table assets (
+item_name varchar(32) primary key, -- A common attribute for all items
+dynamic_cols  blob  -- Dynamic columns will be stored here
+);
+INSERT INTO assets VALUES ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));
+INSERT INTO assets VALUES ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets;
+item_name	color
+MariaDB T-shirt	blue
+Thinkpad Laptop	black
+UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years') WHERE item_name='Thinkpad Laptop';
+SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets;
+item_name	color
+MariaDB T-shirt	NULL
+Thinkpad Laptop	3 years
+UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years')
+WHERE item_name in (select b.item_name
+from assets b
+where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
+SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets;
+item_name	color
+MariaDB T-shirt	NULL
+Thinkpad Laptop	4 years
+UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', (select COLUMN_GET(b.dynamic_cols, 'color' as char)
+from assets b
+where assets.item_name = item_name));
+SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets;
+item_name	color
+MariaDB T-shirt	blue
+Thinkpad Laptop	black
+drop table assets ;
+#
+# Test on fulltext columns
+#
+CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)) ENGINE=MyISAM;
+INSERT INTO ft2(copy) VALUES
+('MySQL vs MariaDB database'),
+('Oracle vs MariaDB database'),
+('PostgreSQL vs MariaDB database'),
+('MariaDB overview'),
+('Foreign keys'),
+('Primary keys'),
+('Indexes'),
+('Transactions'),
+('Triggers');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+copy
+MySQL vs MariaDB database
+Oracle vs MariaDB database
+PostgreSQL vs MariaDB database
+update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5))) from ft2 b WHERE MATCH(b.copy) AGAINST('database'))
+where MATCH(copy) AGAINST('keys');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword');
+copy
+mykeyword Postg
+mykeyword Postg
+drop table ft2;
+#
+# Test with MyISAM
+#
+create table t1 (old_c1 integer, old_c2 integer,c1 integer, c2 integer, c3 integer) engine=MyISAM;
+insert t1 (c1,c2,c3) select 0,seq,seq%10 from seq_1_to_500;
+insert t1 (c1,c2,c3) select 1,seq,seq%10 from seq_1_to_400;
+insert t1 (c1,c2,c3) select 2,seq,seq%10 from seq_1_to_300;
+insert t1 (c1,c2,c3) select 3,seq,seq%10 from seq_1_to_200;
+create index t1_idx1 on t1(c3);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Table is already up to date
+update t1 set c1=2 where exists (select 'x' from t1);
+select count(*) from t1 where c1=2;
+count(*)
+1400
+update t1 set c1=3 where c3 in (select c3 from t1 b where t1.c3=b.c1);
+select count(*) from t1 where c1=3;
+count(*)
+140
+drop table t1;
+#
+# Test error on multi_update conversion on view with order by or limit
+#
+create table t1 (c1 integer) engine=InnoDb;
+create table t2 (c1 integer) engine=InnoDb;
+create view v1 as select t1.c1 as "t1c1" ,t2.c1 as "t2c1" from t1,t2 where t1.c1=t2.c1;
+update v1 set t1c1=2 order by 1;
+ERROR 42S22: Unknown column '1' in 'order clause'
+update v1 set t1c1=2 limit 1;
+drop table t1;
+drop table t2;
+drop view v1;
diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result
index 0dd8b80a568..bf3d4f6dada 100644
--- a/mysql-test/r/user_var.result
+++ b/mysql-test/r/user_var.result
@@ -1,4 +1,3 @@
-drop table if exists t1,t2;
 set @a := foo;
 ERROR 42S22: Unknown column 'foo' in 'field list'
 set @a := connection_id() + 3;
@@ -126,14 +125,14 @@ select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;
 set @a=0;
 select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
 @a	@a:="hello"	@a	@a:=3	@a	@a:="hello again"
-0	hello	0	3	3	hello again
-0	hello	0	3	3	hello again
-0	hello	0	3	3	hello again
+0	hello	0	3	0	hello again
+0	hello	0	3	0	hello again
+0	hello	0	3	0	hello again
 select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
 @a	@a:="hello"	@a	@a:=3	@a	@a:="hello again"
-hello again	hello	hello	3	3	hello again
-hello again	hello	hello	3	3	hello again
-hello again	hello	hello	3	3	hello again
+hello again	hello	hello again	3	hello again	hello again
+hello again	hello	hello again	3	hello again	hello again
+hello again	hello	hello again	3	hello again	hello again
 drop table t1;
 set @a=_latin2'test';
 select charset(@a),collation(@a),coercibility(@a);
@@ -570,6 +569,9 @@ End of 5.5 tests
 #
 set @var= repeat('a',20000);
 1
+explain select @a:=max(seq) from seq_1_to_1000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 #
 # Start of 10.3 tests
 #
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 676432690b4..dde58ed0ab2 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -1548,7 +1548,7 @@ one
 1
 explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0);
 one
@@ -1559,7 +1559,7 @@ one
 set sql_buffer_result=1;
 explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0);
 one
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 24c669308cd..6eead303c7a 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -943,74 +943,6 @@ create table t3 (col1 datetime not null);
 create view v1 as select * from t1;
 create view v2 as select * from v1;
 create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
-update v2 set col1 = (select max(col1) from v1);
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'
-update v2 set col1 = (select max(col1) from t1);
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'
-update v2 set col1 = (select max(col1) from v2);
-ERROR HY000: Table 'v2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'
-update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'
-update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
-ERROR HY000: Table 'v1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'
-update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'
-update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'
-update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'
-update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
-ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v1'
-update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
-ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
-ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
-ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
-ERROR HY000: Table 'v2' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't1'
-update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v1'
-update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'
-update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'
-update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't2'
-update v3 set v3.col1 = (select max(col1) from v1);
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v3'
-update v3 set v3.col1 = (select max(col1) from t1);
-ERROR HY000: The definition of table 'v3' prevents operation UPDATE on table 'v3'
-update v3 set v3.col1 = (select max(col1) from v2);
-ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3'
-update v3 set v3.col1 = (select max(col1) from v3);
-ERROR HY000: Table 'v3' is specified twice, both as a target for 'UPDATE' and as a separate source for data
-delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'
-delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 't1'
-delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1;
-ERROR HY000: Table 'v1' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'
-delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1;
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v1'
-delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1;
-ERROR HY000: Table 'v2' is specified twice, both as a target for 'DELETE' and as a separate source for data
-delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 't1'
-delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1;
-ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v1'
 insert into v2 values ((select max(col1) from v1));
 ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2'
 insert into t1 values ((select max(col1) from v1));
@@ -2024,8 +1956,6 @@ create view v1 as select f59, f60 from t1 where f59 in
 (select f59 from t1);
 update v1 set f60=2345;
 ERROR HY000: The target table v1 of the UPDATE is not updatable
-update t1 set f60=(select max(f60) from v1);
-ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'
 drop view v1;
 drop table t1;
 create table t1 (s1 int);
diff --git a/mysql-test/suite/galera/r/galera_mdev_13787.result b/mysql-test/suite/galera/r/galera_mdev_13787.result
new file mode 100644
index 00000000000..b1caec0283c
--- /dev/null
+++ b/mysql-test/suite/galera/r/galera_mdev_13787.result
@@ -0,0 +1,4 @@
+connection node_1;
+create table t(a int);
+insert into t select 1;
+DROP TABLE t;
diff --git a/mysql-test/suite/galera/t/galera_mdev_13787.opt b/mysql-test/suite/galera/t/galera_mdev_13787.opt
new file mode 100644
index 00000000000..27ec1e3f00e
--- /dev/null
+++ b/mysql-test/suite/galera/t/galera_mdev_13787.opt
@@ -0,0 +1 @@
+--innodb-stats-persistent=1
diff --git a/mysql-test/suite/galera/t/galera_mdev_13787.test b/mysql-test/suite/galera/t/galera_mdev_13787.test
new file mode 100644
index 00000000000..940cffb8b65
--- /dev/null
+++ b/mysql-test/suite/galera/t/galera_mdev_13787.test
@@ -0,0 +1,6 @@
+--source include/galera_cluster.inc
+--source include/have_innodb.inc
+--connection node_1
+create table t(a int);
+insert into t select 1;
+DROP TABLE t;
diff --git a/mysql-test/suite/innodb/r/innodb-truncate.result b/mysql-test/suite/innodb/r/innodb-truncate.result
index f63e9272850..a606868ae52 100644
--- a/mysql-test/suite/innodb/r/innodb-truncate.result
+++ b/mysql-test/suite/innodb/r/innodb-truncate.result
@@ -47,12 +47,24 @@ SET @@SESSION.foreign_key_checks = @old_foreign_key_checks;
 #
 # Test that TRUNCATE resets auto-increment.
 #
-CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT);
-INSERT INTO t1 VALUES (NULL), (NULL);
+CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
+b INT, c INT, d INT, e INT, f INT, g INT, h INT, i INT, j INT, k INT,
+l INT, m INT, n INT, o INT, p INT, q INT, r INT, s INT, t INT, u INT,
+KEY(b),KEY(c),KEY(d),KEY(e),KEY(f),KEY(g),KEY(h),KEY(i),KEY(j),KEY(k),
+KEY(l),KEY(m),KEY(n),KEY(o),KEY(p),KEY(q),KEY(r),KEY(s),KEY(t),KEY(u),
+KEY(c,b),KEY(d,b),KEY(e,b),KEY(f,b),KEY(g,b),KEY(h,b),KEY(i,b),KEY(j,b),
+KEY(k,b),KEY(l,b),KEY(m,b),KEY(n,b),KEY(o,b),KEY(p,b),KEY(q,b),KEY(r,b),
+KEY(s,b),KEY(t,b),KEY(u,b),
+KEY(d,c),KEY(e,c),KEY(f,c),KEY(g,c),KEY(h,c),KEY(i,c),KEY(j,c),
+KEY(k,c),KEY(l,c),KEY(m,c),KEY(n,c),KEY(o,c),KEY(p,c),KEY(q,c),KEY(r,c),
+KEY(s,c),KEY(t,c),KEY(u,c),
+KEY(e,d),KEY(f,d),KEY(g,d),KEY(h,d),KEY(i,d),KEY(j,d)
+) ENGINE=InnoDB;
+INSERT INTO t1 () VALUES (), ();
 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
 AUTO_INCREMENT
 3
-SELECT * FROM t1 ORDER BY a;
+SELECT a FROM t1 ORDER BY a;
 a
 1
 2
@@ -60,8 +72,8 @@ TRUNCATE TABLE t1;
 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
 AUTO_INCREMENT
 1
-INSERT INTO t1 VALUES (NULL), (NULL);
-SELECT * FROM t1 ORDER BY a;
+INSERT INTO t1 () VALUES (), ();
+SELECT a FROM t1 ORDER BY a;
 a
 1
 2
diff --git a/mysql-test/suite/innodb/t/innodb-truncate.test b/mysql-test/suite/innodb/t/innodb-truncate.test
index ae25aabd323..8f9b1f1f0e9 100644
--- a/mysql-test/suite/innodb/t/innodb-truncate.test
+++ b/mysql-test/suite/innodb/t/innodb-truncate.test
@@ -52,13 +52,24 @@ SET @@SESSION.foreign_key_checks = @old_foreign_key_checks;
 --echo # Test that TRUNCATE resets auto-increment.
 --echo #
 
-CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT);
-INSERT INTO t1 VALUES (NULL), (NULL);
+CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
+ b INT, c INT, d INT, e INT, f INT, g INT, h INT, i INT, j INT, k INT,
+ l INT, m INT, n INT, o INT, p INT, q INT, r INT, s INT, t INT, u INT,
+ KEY(b),KEY(c),KEY(d),KEY(e),KEY(f),KEY(g),KEY(h),KEY(i),KEY(j),KEY(k),
+ KEY(l),KEY(m),KEY(n),KEY(o),KEY(p),KEY(q),KEY(r),KEY(s),KEY(t),KEY(u),
+ KEY(c,b),KEY(d,b),KEY(e,b),KEY(f,b),KEY(g,b),KEY(h,b),KEY(i,b),KEY(j,b),
+ KEY(k,b),KEY(l,b),KEY(m,b),KEY(n,b),KEY(o,b),KEY(p,b),KEY(q,b),KEY(r,b),
+ KEY(s,b),KEY(t,b),KEY(u,b),
+ KEY(d,c),KEY(e,c),KEY(f,c),KEY(g,c),KEY(h,c),KEY(i,c),KEY(j,c),
+ KEY(k,c),KEY(l,c),KEY(m,c),KEY(n,c),KEY(o,c),KEY(p,c),KEY(q,c),KEY(r,c),
+ KEY(s,c),KEY(t,c),KEY(u,c),
+ KEY(e,d),KEY(f,d),KEY(g,d),KEY(h,d),KEY(i,d),KEY(j,d)
+) ENGINE=InnoDB;
+INSERT INTO t1 () VALUES (), ();
 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
-SELECT * FROM t1 ORDER BY a;
+SELECT a FROM t1 ORDER BY a;
 TRUNCATE TABLE t1;
 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
-INSERT INTO t1 VALUES (NULL), (NULL);
-SELECT * FROM t1 ORDER BY a;
+INSERT INTO t1 () VALUES (), ();
+SELECT a FROM t1 ORDER BY a;
 DROP TABLE t1;
-
diff --git a/mysql-test/suite/rpl/r/rpl_gtid_basic.result b/mysql-test/suite/rpl/r/rpl_gtid_basic.result
index 3722e438d32..32df09789cc 100644
--- a/mysql-test/suite/rpl/r/rpl_gtid_basic.result
+++ b/mysql-test/suite/rpl/r/rpl_gtid_basic.result
@@ -568,3 +568,16 @@ ERROR HY000: Function or expression 'binlog_gtid_pos()' cannot be used in the DE
 #
 # End  of 10.2 tests
 #
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-13967 Parameter data type control for Item_long_func
+#
+SELECT MASTER_GTID_WAIT(ROW(1,1),'str');
+ERROR HY000: Illegal parameter data type row for operation 'master_gtid_wait'
+SELECT MASTER_GTID_WAIT('str',ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'master_gtid_wait'
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/suite/rpl/r/rpl_master_pos_wait.result b/mysql-test/suite/rpl/r/rpl_master_pos_wait.result
index 18298986069..04f55fc1263 100644
--- a/mysql-test/suite/rpl/r/rpl_master_pos_wait.result
+++ b/mysql-test/suite/rpl/r/rpl_master_pos_wait.result
@@ -45,4 +45,17 @@ master_pos_wait('master-bin.000001',1000000,1,"my_slave")
 STOP SLAVE 'my_slave';
 RESET SLAVE 'my_slave' ALL;
 change master to master_port=MASTER_MYPORT, master_host='127.0.0.1', master_user='root';
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-13965 Parameter data type control for Item_longlong_func
+#
+SELECT MASTER_POS_WAIT('x',1,ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'master_pos_wait'
+SELECT MASTER_POS_WAIT('x',1,1,ROW(1,1));
+ERROR HY000: Illegal parameter data type row for operation 'master_pos_wait'
+#
+# End of 10.3 tests
+#
 include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_gtid_basic.test b/mysql-test/suite/rpl/t/rpl_gtid_basic.test
index b04f82e1725..e14bf89425d 100644
--- a/mysql-test/suite/rpl/t/rpl_gtid_basic.test
+++ b/mysql-test/suite/rpl/t/rpl_gtid_basic.test
@@ -14,3 +14,23 @@ CREATE TABLE t1 (a VARCHAR(100) DEFAULT BINLOG_GTID_POS("master-bin.000001", 600
 --echo #
 --echo # End  of 10.2 tests
 --echo #
+
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-13967 Parameter data type control for Item_long_func
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MASTER_GTID_WAIT(ROW(1,1),'str');
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MASTER_GTID_WAIT('str',ROW(1,1));
+
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
diff --git a/mysql-test/suite/rpl/t/rpl_master_pos_wait.test b/mysql-test/suite/rpl/t/rpl_master_pos_wait.test
index a3f3ff56464..d8c8162ed9f 100644
--- a/mysql-test/suite/rpl/t/rpl_master_pos_wait.test
+++ b/mysql-test/suite/rpl/t/rpl_master_pos_wait.test
@@ -56,5 +56,23 @@ eval change master to master_port=$MASTER_MYPORT, master_host='127.0.0.1', maste
 
 # End of 10.0 tests
 
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-13965 Parameter data type control for Item_longlong_func
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MASTER_POS_WAIT('x',1,ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MASTER_POS_WAIT('x',1,1,ROW(1,1));
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
+
+
 --let $rpl_only_running_threads= 1
 --source include/rpl_end.inc
diff --git a/mysql-test/t/func_int.test b/mysql-test/t/func_int.test
new file mode 100644
index 00000000000..98794561933
--- /dev/null
+++ b/mysql-test/t/func_int.test
@@ -0,0 +1,188 @@
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+
+--echo #
+--echo # MDEV-13965 Parameter data type control for Item_longlong_func
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ROW(1,1) | 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT 1 | ROW(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ROW(1,1) & 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT 1 & ROW(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ROW(1,1) << 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT 1 << ROW(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ROW(1,1) >> 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT 1 >> ROW(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ~ROW(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TO_SECONDS(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TIMESTAMPDIFF(SECOND,ROW(1,1), 1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TIMESTAMPDIFF(SECOND,1, ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT INET_ATON(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LAST_INSERT_ID(ROW(1,1));
+
+
+--echo #
+--echo # MDEV-13967 Parameter data type control for Item_long_func
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT STRCMP(ROW(1,1),'');
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT STRCMP('',ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CHAR_LENGTH(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT OCTET_LENGTH(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT UNCOMPRESSED_LENGTH(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT COERCIBILITY(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ASCII(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CRC32(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ORD(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SIGN(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOCATE(ROW(1,1),'a',1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOCATE('a',ROW(1,1),1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOCATE('a','a',ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT BIT_COUNT(ROW(1,1));
+
+--error ER_OPERAND_COLUMNS
+SELECT BENCHMARK(1, ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT BENCHMARK(ROW(1,1),'');
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SLEEP(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT GET_LOCK('x', ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT GET_LOCK(ROW(1,1),'x');
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT PERIOD_ADD(ROW(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT PERIOD_ADD(1,ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT PERIOD_DIFF(ROW(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT PERIOD_DIFF(1,ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TO_DAYS(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT DAYOFMONTH(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT DAYOFYEAR(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT QUARTER(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT YEAR(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT YEARWEEK(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT WEEK(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT WEEK(ROW(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT WEEK(1,ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT HOUR(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MINUTE(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SECOND(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MICROSECOND(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT JSON_DEPTH(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT JSON_LENGTH(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT JSON_LENGTH('json', ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT JSON_LENGTH(ROW(1,1), ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT REGEXP_INSTR(ROW(1,1),'');
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT REGEXP_INSTR('',ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT FIND_IN_SET(ROW(1,1),'');
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT FIND_IN_SET('',ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT RELEASE_LOCK(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT IS_FREE_LOCK(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT IS_USED_LOCK(ROW(1,1));
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
diff --git a/mysql-test/t/func_regexp.test b/mysql-test/t/func_regexp.test
index d0ab0cc9044..6d5186269a5 100644
--- a/mysql-test/t/func_regexp.test
+++ b/mysql-test/t/func_regexp.test
@@ -104,3 +104,9 @@ SELECT '\t' REGEXP '[[:blank:]]';
 
 SELECT ' '  REGEXP '[[:space:]]';
 SELECT '\t' REGEXP '[[:space:]]';
+
+--echo #
+--echo # MDEV-13967 Parameter data type control for Item_long_func
+--echo #
+SELECT REGEXP_INSTR('111222333',2);
+
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index a3f542aaf57..3c154a8f67e 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1880,3 +1880,44 @@ CREATE TABLE t1 AS SELECT TO_SECONDS('9999-12-31 23:59:59');
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 SET sql_mode=DEFAULT;
+
+
+--echo #
+--echo # MDEV-13966 Parameter data type control for Item_temporal_func
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT FROM_DAYS(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKEDATE(ROW(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKEDATE(1, ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LAST_DAY(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SEC_TO_TIME(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TIMEDIFF(ROW(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TIMEDIFF(1, ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKETIME(ROW(1,1),1,1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKETIME(1, ROW(1,1), 1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKETIME(1, 1, ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT FROM_UNIXTIME(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CONVERT_TZ(ROW(1,1),1,1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CONVERT_TZ(1, ROW(1,1), 1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CONVERT_TZ(1, 1, ROW(1,1));
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 91e4b3d49f4..94e56774bf4 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -2689,6 +2689,303 @@ SELECT MBRTOUCHES('test', POINT(1,1));
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT MBRTOUCHES(POINT(1,1), 'test');
 
+
+--echo #
+--echo # MDEV-13964 Parameter data type control for Item_real_func
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT EXP(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LN(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG2(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG10(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SQRT(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ACOS(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ASIN(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT COS(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SIN(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TAN(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT COT(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG(POINT(1,1),POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG(1, POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ATAN(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ATAN(POINT(1,1),POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ATAN(1, POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT POW(POINT(1,1),POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT RAND(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT RADIANS(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT DEGREES(POINT(1,1));
+
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT EXP(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LN(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG2(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG10(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SQRT(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ACOS(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ASIN(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT COS(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SIN(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TAN(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT COT(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG(ROW(1,1),ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOG(1, ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ATAN(ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ATAN(ROW(1,1),ROW(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ATAN(1, ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT POW(ROW(1,1),ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT RAND(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT RADIANS(ROW(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT DEGREES(ROW(1,1));
+
+
+--echo #
+--echo # MDEV-13965 Parameter data type control for Item_longlong_func
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT POINT(1,1) | 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT 1 | POINT(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT POINT(1,1) & 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT 1 & POINT(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT POINT(1,1) << 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT 1 << POINT(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT POINT(1,1) >> 1;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT 1 >> POINT(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT ~POINT(1,1);
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TO_SECONDS(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TIMESTAMPDIFF(SECOND,POINT(1,1), 1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TIMESTAMPDIFF(SECOND,1, POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT INET_ATON(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LAST_INSERT_ID(POINT(1,1));
+
+
+--echo #
+--echo # MDEV-13966 Parameter data type control for Item_temporal_func
+--echo #
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT FROM_DAYS(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKEDATE(POINT(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKEDATE(1, POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LAST_DAY(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SEC_TO_TIME(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TIMEDIFF(POINT(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TIMEDIFF(1, POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKETIME(POINT(1,1),1,1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKETIME(1, POINT(1,1), 1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MAKETIME(1, 1, POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT FROM_UNIXTIME(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CONVERT_TZ(POINT(1,1),1,1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CONVERT_TZ(1, POINT(1,1), 1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT CONVERT_TZ(1, 1, POINT(1,1));
+
+
+--echo #
+--echo # MDEV-13967 Parameter data type control for Item_long_func
+--echo #
+
+SELECT STRCMP(POINT(1,1),POINT(1,1));
+SELECT CHAR_LENGTH(POINT(1,1));
+SELECT OCTET_LENGTH(POINT(1,1));
+SELECT UNCOMPRESSED_LENGTH(POINT(1,1));
+SELECT COERCIBILITY(POINT(1,1));
+SELECT ASCII(POINT(1,1));
+SELECT CRC32(POINT(1,1));
+SELECT ORD(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SIGN(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT LOCATE('a','a',POINT(1,1));
+
+SELECT LOCATE(POINT(1,1),POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT BIT_COUNT(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT BENCHMARK(POINT(1,1),'');
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SLEEP(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT GET_LOCK('x', POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT PERIOD_ADD(POINT(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT PERIOD_ADD(1,POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT PERIOD_DIFF(POINT(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT PERIOD_DIFF(1,POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT TO_DAYS(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT DAYOFMONTH(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT DAYOFYEAR(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT QUARTER(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT YEAR(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT YEARWEEK(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT WEEK(POINT(1,1));
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT WEEK(POINT(1,1),1);
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT WEEK(1,POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT HOUR(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MINUTE(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT SECOND(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT MICROSECOND(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT JSON_DEPTH(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT JSON_LENGTH(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT JSON_LENGTH('json', POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT JSON_LENGTH(POINT(1,1), POINT(1,1));
+
+SELECT REGEXP_INSTR(POINT(1,1),'');
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT REGEXP_INSTR('',POINT(1,1));
+
+SELECT FIND_IN_SET(POINT(1,1),'');
+SELECT FIND_IN_SET('',POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT RELEASE_LOCK(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT IS_FREE_LOCK(POINT(1,1));
+
+--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
+SELECT IS_USED_LOCK(POINT(1,1));
+
+
 --echo #
 --echo # End of 10.3 tests
 --echo #
diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test
index 4c91383db60..cdd0256d639 100644
--- a/mysql-test/t/lowercase_view.test
+++ b/mysql-test/t/lowercase_view.test
@@ -24,74 +24,6 @@ create view v1Aa as select * from t1aA;
 create view v2aA as select * from v1aA;
 create view v3Aa as select v2Aa.col1 from v2aA,t2Aa where v2Aa.col1 = t2aA.col1;
 -- error 1443
-update v2aA set col1 = (select max(col1) from v1Aa);
--- error 1443
-update v2Aa set col1 = (select max(col1) from t1Aa);
--- error 1093
-update v2aA set col1 = (select max(col1) from v2Aa);
--- error 1443
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1;
--- error 1443
-update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1;
--- error 1093
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
--- error 1443
-update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1;
--- error 1443
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1;
--- error 1443
-update t2Aa,v1aA set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1;
--- error 1443
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1;
--- error 1093
-update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
--- error 1443
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1;
--- error 1093
-update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1;
--- error 1093
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1;
--- error 1093
-update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1;
--- error 1093
-update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
--- error 1443
-update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1;
--- error 1443
-update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1;
--- error 1443
-update t2Aa,v2aA set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1;
--- error 1443
-update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v2aA) where t1Aa.col1 = t2aA.col1;
--- error 1443
-update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from v2Aa) where v1Aa.col1 = t2aA.col1;
--- error 1443
-update v3aA set v3Aa.col1 = (select max(col1) from v1aA);
--- error 1443
-update v3aA set v3Aa.col1 = (select max(col1) from t1aA);
--- error 1443
-update v3aA set v3Aa.col1 = (select max(col1) from v2aA);
--- error 1093
-update v3aA set v3Aa.col1 = (select max(col1) from v3aA);
--- error 1443
-delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1;
--- error 1443
-delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1;
--- error 1093
-delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1;
--- error 1443
-delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1;
--- error 1093
-delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1;
--- error 1443
-delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1;
--- error 1093
-delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1;
--- error 1443
-delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1;
--- error 1443
-delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1;
--- error 1443
 insert into v2Aa values ((select max(col1) from v1aA));
 -- error 1443
 insert into t1aA values ((select max(col1) from v1Aa));
@@ -176,4 +108,3 @@ DROP TABLE `ttt`;
 
 
 --echo End of 5.0 tests.
-
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index 95c78caf034..99309e6a7f0 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -2732,38 +2732,6 @@ insert into m1 (a) values ((select max(a) from v1));
 insert into m1 (a) values ((select max(a) from tmp, v1));
 
 
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from m1));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from m2));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from t1));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from t2));
-
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from t3, m1));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from t3, m2));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from t3, t1));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from t3, t2));
-
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from tmp, m1));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from tmp, m2));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from tmp, t1));
---error ER_UPDATE_TABLE_USED
-update m1 set a = ((select max(a) from tmp, t2));
- 
---error ER_VIEW_PREVENT_UPDATE
-update m1 set a = ((select max(a) from v1));
---error ER_VIEW_PREVENT_UPDATE
-update m1 set a = ((select max(a) from tmp, v1));
-
 drop view v1;
 drop temporary table tmp;
 drop table t1, t2, t3, m1, m2;
@@ -2948,4 +2916,3 @@ eval set global storage_engine=$default;
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc
-
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 64e61f7c0b5..5feebe87a5a 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -2,12 +2,6 @@
 # Test of update statement that uses many tables.
 #
 
-# Requires grants, so won't work with embedded server test
-source include/not_embedded.inc;
-source include/have_log_bin.inc;
-
-CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
-
 create table t1(id1 int not null auto_increment primary key, t char(12));
 create table t2(id2 int not null, t char(12));
 create table t3(id3 int not null, t char(12), index(id3));
@@ -376,6 +370,7 @@ connection root;
 revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
 revoke all privileges on mysqltest.* from mysqltest_1@localhost;
 delete from mysql.user where user=_binary'mysqltest_1';
+flush privileges;
 drop database mysqltest;
 connection default;
 disconnect user1;
@@ -396,7 +391,6 @@ drop table t1, t2, t3;
 #
 create table t1 (col1 int);
 create table t2 (col1 int);
--- error ER_UPDATE_TABLE_USED
 update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
 -- error ER_UPDATE_TABLE_USED
 delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
@@ -497,84 +491,6 @@ select * from t1 order by i1;
 select * from t2 order by id;
 drop table t1, t2;
 
-#
-# Bug#27716 multi-update did partially and has not binlogged
-#
-
-CREATE TABLE `t1` (
-  `a` int(11) NOT NULL auto_increment,
-  `b` int(11) default NULL,
-  PRIMARY KEY  (`a`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
-
-CREATE TABLE `t2` (
-  `a` int(11) NOT NULL auto_increment,
-  `b` int(11) default NULL,
-  PRIMARY KEY  (`a`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
-
-# as the test is about to see erroed queries in binlog
-set @sav_binlog_format=  @@session.binlog_format;
-set @@session.binlog_format= mixed;
-
-
-# A. testing multi_update::send_error() effective update
-insert into t1 values (1,1),(2,2);
-insert into t2 values (1,1),(4,4);
-reset master;
---error ER_DUP_ENTRY
-UPDATE t2,t1 SET t2.a=t1.a+2;
-# check
-select * from t2 /* must be (3,1), (4,4) */;
-source include/show_binlog_events.inc;
-
-# B. testing multi_update::send_error() ineffective update
-# (as there is a policy described at mysql_update() still go to binlog)
-delete from t1;
-delete from t2;
-insert into t1 values (1,2),(3,4),(4,4);
-insert into t2 values (1,2),(3,4),(4,4);
-reset master;
---error ER_DUP_ENTRY
-UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a;
-source include/show_binlog_events.inc;
-
-# cleanup
-drop table t1, t2;
-set @@session.binlog_format= @sav_binlog_format;
-
-#
-# Bug#29136 erred multi-delete on trans table does not rollback
-#
-
-# prepare
-CREATE TABLE t1 (a int, PRIMARY KEY (a));
-CREATE TABLE t2 (a int, PRIMARY KEY (a));
-CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;
-create trigger trg_del_t3 before  delete on t3 for each row insert into t1 values (1);
-
-insert into t2 values (1),(2);
-insert into t3 values (1),(2);
-reset master;
-
-# exec cases B, A - see innodb.test
-
-# B. send_eof() and send_error() afterward
-
---error ER_DUP_ENTRY
-delete t3.* from t2,t3 where t2.a=t3.a;
-
-# check
-select count(*) from t1 /* must be 1 */;
-select count(*) from t3 /* must be 1 */;
-
-# cleanup
-drop table t1, t2, t3;
-
-#
-# Add further tests from here
-#
-
 --echo #
 --echo # Bug#49534: multitable IGNORE update with sql_safe_updates error
 --echo # causes debug assertion
@@ -984,3 +900,17 @@ deallocate prepare stmt1;
 drop view v3,v2,v1;
 drop table t1,t2,t3;
 --echo end of 5.5 tests
+
+#
+# MDEV-13911 Support ORDER BY and LIMIT in multi-table update
+#
+
+create table t1 (c1 int, c3 int);
+insert t1(c3) values (1), (2), (3), (4), (5), (6), (7), (8);
+create table t2 select * from t1;
+update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 limit 3;
+select * from t1;
+update t1 set c1=NULL;
+update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 desc limit 2;
+select * from t1;
+drop table t1, t2;
diff --git a/mysql-test/t/multi_update_binlog.test b/mysql-test/t/multi_update_binlog.test
new file mode 100644
index 00000000000..16155aa1af3
--- /dev/null
+++ b/mysql-test/t/multi_update_binlog.test
@@ -0,0 +1,82 @@
+#
+# Test of update statement that uses many tables.
+#
+
+source include/have_log_bin.inc;
+
+CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
+
+#
+# Bug#27716 multi-update did partially and has not binlogged
+#
+
+CREATE TABLE `t1` (
+  `a` int(11) NOT NULL auto_increment,
+  `b` int(11) default NULL,
+  PRIMARY KEY  (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
+
+CREATE TABLE `t2` (
+  `a` int(11) NOT NULL auto_increment,
+  `b` int(11) default NULL,
+  PRIMARY KEY  (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
+
+# as the test is about to see erroed queries in binlog
+set @sav_binlog_format=  @@session.binlog_format;
+set @@session.binlog_format= mixed;
+
+
+# A. testing multi_update::send_error() effective update
+insert into t1 values (1,1),(2,2);
+insert into t2 values (1,1),(4,4);
+reset master;
+--error ER_DUP_ENTRY
+UPDATE t2,t1 SET t2.a=t1.a+2;
+# check
+select * from t2 /* must be (3,1), (4,4) */;
+source include/show_binlog_events.inc;
+
+# B. testing multi_update::send_error() ineffective update
+# (as there is a policy described at mysql_update() still go to binlog)
+delete from t1;
+delete from t2;
+insert into t1 values (1,2),(3,4),(4,4);
+insert into t2 values (1,2),(3,4),(4,4);
+reset master;
+--error ER_DUP_ENTRY
+UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a;
+source include/show_binlog_events.inc;
+
+# cleanup
+drop table t1, t2;
+set @@session.binlog_format= @sav_binlog_format;
+
+#
+# Bug#29136 erred multi-delete on trans table does not rollback
+#
+
+# prepare
+CREATE TABLE t1 (a int, PRIMARY KEY (a));
+CREATE TABLE t2 (a int, PRIMARY KEY (a));
+CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;
+create trigger trg_del_t3 before  delete on t3 for each row insert into t1 values (1);
+
+insert into t2 values (1),(2);
+insert into t3 values (1),(2);
+reset master;
+
+# exec cases B, A - see innodb.test
+
+# B. send_eof() and send_error() afterward
+
+--error ER_DUP_ENTRY
+delete t3.* from t2,t3 where t2.a=t3.a;
+
+# check
+select count(*) from t1 /* must be 1 */;
+select count(*) from t3 /* must be 1 */;
+
+# cleanup
+drop table t1, t2, t3;
+
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 7412eae8ecf..5e1e1494fee 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -325,8 +325,6 @@ create table t2 (a int NOT NULL, b int, primary key (a));
 insert into t1 values (0, 10),(1, 11),(2, 12);
 insert into t2 values (1, 21),(2, 22),(3, 23);
 select * from t1;
--- error ER_UPDATE_TABLE_USED
-update t1 set b= (select b from t1);
 -- error ER_SUBQUERY_NO_1_ROW
 update t1 set b= (select b from t2);
 update t1 set b= (select b from t2 where t1.a = t2.a);
diff --git a/mysql-test/t/update_use_source.test b/mysql-test/t/update_use_source.test
new file mode 100644
index 00000000000..7ed5f95d68d
--- /dev/null
+++ b/mysql-test/t/update_use_source.test
@@ -0,0 +1,245 @@
+--source include/have_sequence.inc
+--source include/have_innodb.inc
+
+create table t1 (old_c1 integer, old_c2 integer,c1 integer, c2 integer, c3 integer) engine=InnoDb;
+create view v1 as select * from t1 where c2=2;
+delimiter /;
+create trigger trg_t1 before update on t1 for each row
+begin
+  set new.old_c1=old.c1;
+  set new.old_c2=old.c2;
+end;
+/
+delimiter ;/
+
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+
+commit;
+select * from t1;
+
+--echo Test without any index
+--source include/update_use_source.inc
+
+--echo Test with an index on updated columns
+create index t1_c2 on t1 (c2,c1);
+--source include/update_use_source.inc
+
+--echo Test with an index on updated columns
+create index t1_c3 on t1 (c3);
+--source include/update_use_source.inc
+
+--echo Test with a primary key on updated columns
+drop index t1_c3 on t1;
+alter table t1 add primary key (c3);
+--source include/update_use_source.inc
+
+--echo # Update with error "Subquery returns more than 1 row"
+--error ER_SUBQUERY_NO_1_ROW
+update t1 set c2=(select c2 from t1);
+
+--echo # Update with error "Subquery returns more than 1 row" and order by
+--error ER_SUBQUERY_NO_1_ROW
+update t1 set c2=(select c2 from t1) order by c3;
+
+-- echo  Duplicate value on update a primary key
+start transaction;
+--error ER_DUP_ENTRY
+update t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+rollback;
+
+-- echo  Duplicate value on update a primary key with ignore
+start transaction;
+--enable_info ONCE
+update ignore t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+rollback;
+
+-- echo  Duplicate value on update a primary key and limit
+start transaction;
+--error ER_DUP_ENTRY
+update t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 limit 2;
+rollback;
+
+-- echo  Duplicate value on update a primary key with ignore and limit
+start transaction;
+--enable_info ONCE
+update ignore t1 set c3=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 limit 2;
+rollback;
+
+--echo # Update no rows found
+--enable_info ONCE
+update t1
+   set c1=10
+ where c1 <2
+   and exists (select 'X'
+                 from t1 a
+                where a.c1 = t1.c1 + 10);
+
+--echo # Update no rows changed
+drop trigger trg_t1;
+start transaction;
+--enable_info ONCE
+update t1
+   set c1=c1
+ where c1 <2
+   and exists (select 'X'
+                 from t1 a
+                where a.c1 = t1.c1);
+rollback;
+
+--echo #
+--echo # Check call of after trigger
+--echo #
+
+delimiter /;
+create or replace trigger trg_t2 after update on t1 for each row
+begin
+  declare msg varchar(100);
+  if (new.c3 = 5) then
+    set msg=concat('in after update trigger on ',new.c3);
+    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
+  end if;
+end;
+/
+delimiter ;/
+--error 1644
+update t1 set c1=2 where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
+
+--echo #
+--echo # Check update with order by and after trigger
+--echo #
+
+--error 1644
+update t1 set c1=2 where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1) order by t1.c2;
+
+drop view v1;
+--echo #
+--echo # Check update on view with check option
+--echo #
+
+create view v1 as select * from t1 where c2=2 with check option;
+
+start transaction;
+-- error 1369
+update v1 set c2=3 where c1=1;
+rollback;
+
+start transaction;
+-- error 1369
+update v1 set c2=(select max(c3) from v1) where c1=1;
+rollback;
+
+start transaction;
+update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1;
+rollback;
+
+drop view v1;
+drop table t1;
+
+--echo #
+--echo # Test with a temporary table
+--echo #
+
+create temporary table t1 (c1 integer, c2 integer, c3 integer) engine=InnoDb;
+insert into t1(c1,c2,c3) values (1,1,1);
+insert into t1(c1,c2,c3) values (1,2,2);
+insert into t1(c1,c2,c3) values (1,3,3);
+insert into t1(c1,c2,c3) values (2,1,4);
+insert into t1(c1,c2,c3) values (2,2,5);
+insert into t1(c1,c2,c3) values (2,3,6);
+insert into t1(c1,c2,c3) values (2,4,7);
+insert into t1(c1,c2,c3) values (2,5,8);
+
+start transaction;
+--enable_info ONCE
+update t1
+   set c1=(select a.c2
+             from t1 a
+            where a.c3 = t1.c3) limit 3;
+select * from t1 ;
+rollback;
+drop table t1;
+
+--echo #
+--echo # Test on dynamic columns (blob)
+--echo #
+
+create table assets (
+  item_name varchar(32) primary key, -- A common attribute for all items
+  dynamic_cols  blob  -- Dynamic columns will be stored here
+);
+INSERT INTO assets VALUES ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));
+INSERT INTO assets VALUES ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets;
+UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years') WHERE item_name='Thinkpad Laptop';
+SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets;
+UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years')
+ WHERE item_name in (select b.item_name
+                       from assets b
+                      where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
+SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets;
+
+UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', (select COLUMN_GET(b.dynamic_cols, 'color' as char)
+                                                                       from assets b
+                                                                      where assets.item_name = item_name));
+SELECT item_name, COLUMN_GET(dynamic_cols, 'warranty' as char) AS color FROM assets;
+drop table assets ;
+
+--echo #
+--echo # Test on fulltext columns
+--echo #
+CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)) ENGINE=MyISAM;
+INSERT INTO ft2(copy) VALUES
+ ('MySQL vs MariaDB database'),
+ ('Oracle vs MariaDB database'),
+ ('PostgreSQL vs MariaDB database'),
+ ('MariaDB overview'),
+ ('Foreign keys'),
+ ('Primary keys'),
+ ('Indexes'),
+ ('Transactions'),
+ ('Triggers');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5))) from ft2 b WHERE MATCH(b.copy) AGAINST('database'))
+ where MATCH(copy) AGAINST('keys');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword');
+drop table ft2;
+
+--echo #
+--echo # Test with MyISAM
+--echo #
+
+create table t1 (old_c1 integer, old_c2 integer,c1 integer, c2 integer, c3 integer) engine=MyISAM;
+insert t1 (c1,c2,c3) select 0,seq,seq%10 from seq_1_to_500;
+insert t1 (c1,c2,c3) select 1,seq,seq%10 from seq_1_to_400;
+insert t1 (c1,c2,c3) select 2,seq,seq%10 from seq_1_to_300;
+insert t1 (c1,c2,c3) select 3,seq,seq%10 from seq_1_to_200;
+create index t1_idx1 on t1(c3);
+analyze table t1;
+
+update t1 set c1=2 where exists (select 'x' from t1);
+select count(*) from t1 where c1=2;
+update t1 set c1=3 where c3 in (select c3 from t1 b where t1.c3=b.c1);
+select count(*) from t1 where c1=3;
+drop table t1;
+
+
+--echo #
+--echo # Test error on multi_update conversion on view with order by or limit
+--echo #
+
+create table t1 (c1 integer) engine=InnoDb;
+create table t2 (c1 integer) engine=InnoDb;
+create view v1 as select t1.c1 as "t1c1" ,t2.c1 as "t2c1" from t1,t2 where t1.c1=t2.c1;
+--error ER_BAD_FIELD_ERROR
+update v1 set t1c1=2 order by 1;
+update v1 set t1c1=2 limit 1;
+drop table t1;
+drop table t2;
+drop view v1;
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index aae12ae4cbd..54ceb8fd3a5 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -1,7 +1,5 @@
 # Initialise
---disable_warnings
-drop table if exists t1,t2;
---enable_warnings
+source include/have_sequence.inc;
 
 --error 1054
 set @a := foo;
@@ -501,6 +499,13 @@ eval select $tmp < $tmp2;
 --enable_column_names
 --enable_query_log
 
+#
+# MDEV-13897 SELECT @a := MAX(col) FROM t requires full index scan
+#
+explain select @a:=max(seq) from seq_1_to_1000000;
+
+# End of 10.1 tests
+
 --echo #
 --echo # Start of 10.3 tests
 --echo #
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 00be48c172c..4850b6c06cb 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -865,74 +865,6 @@ create view v1 as select * from t1;
 create view v2 as select * from v1;
 create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1;
 -- error ER_VIEW_PREVENT_UPDATE
-update v2 set col1 = (select max(col1) from v1);
--- error ER_VIEW_PREVENT_UPDATE
-update v2 set col1 = (select max(col1) from t1);
--- error ER_UPDATE_TABLE_USED
-update v2 set col1 = (select max(col1) from v2);
--- error ER_VIEW_PREVENT_UPDATE
-update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-update v3 set v3.col1 = (select max(col1) from v1);
--- error ER_VIEW_PREVENT_UPDATE
-update v3 set v3.col1 = (select max(col1) from t1);
--- error ER_VIEW_PREVENT_UPDATE
-update v3 set v3.col1 = (select max(col1) from v2);
--- error ER_UPDATE_TABLE_USED
-update v3 set v3.col1 = (select max(col1) from v3);
--- error ER_VIEW_PREVENT_UPDATE
-delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
-delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
-delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1;
--- error ER_VIEW_PREVENT_UPDATE
 insert into v2 values ((select max(col1) from v1));
 -- error ER_VIEW_PREVENT_UPDATE
 insert into t1 values ((select max(col1) from v1));
@@ -1803,8 +1735,6 @@ create view v1 as select f59, f60 from t1 where f59 in
          (select f59 from t1);
 -- error ER_NON_UPDATABLE_TABLE
 update v1 set f60=2345;
--- error ER_VIEW_PREVENT_UPDATE
-update t1 set f60=(select max(f60) from v1);
 drop view v1;
 drop table t1;