Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 705 insertions, 0 deletions

diff --git a/mysql-test/main/update.test b/mysql-test/main/update.test
new file mode 100644
index 00000000..0dbc3c33
--- /dev/null
+++ b/mysql-test/main/update.test
@@ -0,0 +1,705 @@
+#
+# test of updating of keys
+#
+
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+create table t1 (a int auto_increment , primary key (a));
+insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); 
+update t1 set a=a+10 where a > 34;
+update t1 set a=a+100 where a > 0;
+
+# Some strange updates to test some otherwise unused code
+update t1 set a=a+100 where a=1 and a=2;
+--error 1054
+update t1 set a=b+100 where a=1 and a=2; 
+--error 1054
+update t1 set a=b+100 where c=1 and a=2; 
+--error 1054
+update t1 set d=a+100 where a=1;
+select * from t1;
+drop table t1;
+
+CREATE TABLE t1
+ (
+ place_id int (10) unsigned NOT NULL,
+ shows int(10) unsigned DEFAULT '0' NOT NULL,
+ ishows int(10) unsigned DEFAULT '0' NOT NULL,
+ ushows int(10) unsigned DEFAULT '0' NOT NULL,
+ clicks int(10) unsigned DEFAULT '0' NOT NULL,
+ iclicks int(10) unsigned DEFAULT '0' NOT NULL,
+ uclicks int(10) unsigned DEFAULT '0' NOT NULL,
+ ts timestamp,
+ PRIMARY KEY (place_id,ts)
+ );
+
+INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
+VALUES (1,0,0,0,0,0,0,20000928174434);
+UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
+select place_id,shows from t1;
+drop table t1;
+
+#
+# Test bug with update reported by Jan Legenhausen
+#
+
+CREATE TABLE t1 (
+  lfdnr int(10) unsigned NOT NULL default '0',
+  ticket int(10) unsigned NOT NULL default '0',
+  client varchar(255) NOT NULL default '',
+  replyto varchar(255) NOT NULL default '',
+  subject varchar(100) NOT NULL default '',
+  timestamp int(10) unsigned NOT NULL default '0',
+  tstamp timestamp NOT NULL,
+  status int(3) NOT NULL default '0',
+  type varchar(15) NOT NULL default '',
+  assignment int(10) unsigned NOT NULL default '0',
+  fupcount int(4) unsigned NOT NULL default '0',
+  parent int(10) unsigned NOT NULL default '0',
+  activity int(10) unsigned NOT NULL default '0',
+  priority tinyint(1) unsigned NOT NULL default '1',
+  cc varchar(255) NOT NULL default '',
+  bcc varchar(255) NOT NULL default '',
+  body text NOT NULL,
+  comment text,
+  header text,
+  PRIMARY KEY  (lfdnr),
+  KEY k1 (timestamp),
+  KEY k2 (type),
+  KEY k3 (parent),
+  KEY k4 (assignment),
+  KEY ticket (ticket)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
+
+alter table t1 change lfdnr lfdnr int(10) unsigned not null auto_increment;
+update t1 set status=1 where type='Open';
+select status from t1;
+drop table t1;
+
+#
+# Test of ORDER BY
+#
+
+create table t1 (a int not null, b int not null, key (a));
+insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
+SET @tmp=0;
+update t1 set b=(@tmp:=@tmp+1) order by a;
+update t1 set b=99 where a=1 order by b asc limit 1;
+select * from t1 order by a,b;
+update t1 set b=100 where a=1 order by b desc limit 2;
+update t1 set a=a+10+b where a=1 order by b;
+select * from t1 order by a,b;
+create table t2 (a int not null, b int not null);
+insert into t2 values (1,1),(1,2),(1,3);
+update t1 set b=(select distinct 1 from (select * from t2) a);
+drop table t1,t2;
+
+#
+# Test with limit (Bug #393)
+#
+
+CREATE TABLE t1 (
+   `id_param` smallint(3) unsigned NOT NULL default '0',
+   `nom_option` char(40) NOT NULL default '',
+   `valid` tinyint(1) NOT NULL default '0',
+   KEY `id_param` (`id_param`,`nom_option`)
+ ) ENGINE=MyISAM;
+
+INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
+
+UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
+select * from t1;
+drop table t1;
+
+#
+# Multi table update test from bugs
+#
+
+create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
+
+insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
+('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
+('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
+('2','2','0',1,7);
+delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
+select * from t1;
+drop table t1;
+
+#
+# Bug#5553 - Multi table UPDATE IGNORE fails on duplicate keys 
+#
+
+CREATE TABLE t1 ( 
+   `colA` int(10) unsigned NOT NULL auto_increment,
+   `colB` int(11) NOT NULL default '0',
+   PRIMARY KEY  (`colA`)
+);
+INSERT INTO t1 VALUES (4433,5424);
+CREATE TABLE t2 (
+  `colC` int(10) unsigned NOT NULL default '0',
+  `colA` int(10) unsigned NOT NULL default '0',
+  `colD` int(10) unsigned NOT NULL default '0',
+  `colE` int(10) unsigned NOT NULL default '0',
+  `colF` int(10) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`colC`,`colA`,`colD`,`colE`)
+);
+INSERT INTO t2 VALUES (3,4433,10005,495,500);
+INSERT INTO t2 VALUES (3,4433,10005,496,500);
+INSERT INTO t2 VALUES (3,4433,10009,494,500);
+INSERT INTO t2 VALUES (3,4433,10011,494,500);
+INSERT INTO t2 VALUES (3,4433,10005,497,500);
+INSERT INTO t2 VALUES (3,4433,10013,489,500);
+INSERT INTO t2 VALUES (3,4433,10005,494,500);
+INSERT INTO t2 VALUES (3,4433,10005,493,500);
+INSERT INTO t2 VALUES (3,4433,10005,492,500);
+UPDATE IGNORE t2,t1 set t2.colE = t2.colE + 1,colF=0 WHERE t1.colA = t2.colA AND (t1.colB & 4096) > 0 AND (colE + 1) < colF;
+SELECT * FROM t2;
+DROP TABLE t1;
+DROP TABLE t2;
+
+#
+# Bug #6054 
+#
+create table t1 (c1 int, c2 char(6), c3 int);
+create table t2 (c1 int, c2 char(6));
+insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
+update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
+update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
+drop table t1, t2;
+
+#
+# Bug #8057
+#
+create table t1 (id int not null auto_increment primary key, id_str varchar(32));
+insert into t1 (id_str) values ("test");
+update t1 set id_str = concat(id_str, id) where id = last_insert_id();
+select * from t1;
+drop table t1;
+
+#
+# Bug #8942: a problem with update and partial key part
+#
+
+create table t1 (a int, b char(255), key(a, b(20)));
+insert into t1 values (0, '1');
+update t1 set b = b + 1 where a = 0;
+select * from t1;
+drop table t1;
+
+# BUG#9103 "Erroneous data truncation warnings on multi-table updates"
+create table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
+create table t2 (a int, b varchar(10)) engine=myisam;
+insert into t1 values ( 1, 'abcd1e');
+insert into t1 values ( 2, 'abcd2e');
+insert into t2 values ( 1, 'abcd1e');
+insert into t2 values ( 2, 'abcd2e');
+analyze table t1,t2;
+update t1, t2 set t1.a = t2.a where t2.b = t1.b;
+show warnings;
+drop table t1, t2;
+
+#
+# Bug #11868 Update with subquery with ref built with a key from the updated
+#            table crashes server
+#
+create table t1(f1 int, f2 int);
+create table t2(f3 int, f4 int);
+create index idx on t2(f3);
+insert into t1 values(1,0),(2,0);
+insert into t2 values(1,1),(2,2);
+UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
+select * from t1;
+drop table t1,t2;
+
+#
+# Bug #13180 sometimes server accepts sum func in update/delete where condition
+#
+create table t1(f1 int);
+select DATABASE();
+--error 1111
+update t1 set f1=1 where count(*)=1;
+select DATABASE();
+--error 1111
+delete from t1 where count(*)=1;
+drop table t1;
+
+# BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index
+create table t1 ( a int, b int default 0, index (a) );
+insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
+
+# the view protocol creates an additional statistics data
+--disable_ps2_protocol
+--disable_view_protocol
+flush status;
+select a from t1 order by a limit 1;
+show status like 'handler_read%';
+--enable_view_protocol
+--enable_ps2_protocol
+
+flush status;
+update t1 set a=9999 order by a limit 1;
+update t1 set b=9999 order by a limit 1;
+show status like 'handler_read%';
+
+flush status;
+delete from t1 order by a limit 1;
+show status like 'handler_read%';
+
+flush status;
+delete from t1 order by a desc limit 1;
+show status like 'handler_read%';
+
+alter table t1 disable keys;
+
+flush status;
+delete from t1 order by a limit 1;
+show status like 'handler_read%';
+
+select * from t1;
+update t1 set a=a+10,b=1 order by a limit 3;
+update t1 set a=a+11,b=2 order by a limit 3;
+update t1 set a=a+12,b=3 order by a limit 3;
+select * from t1 order by a;
+
+drop table t1;
+
+#
+# Bug#14186 select datefield is null not updated
+#
+create table t1 (f1 date not null);
+insert into t1 values('2000-01-01'),('0000-00-00');
+update t1 set f1='2002-02-02' where f1 is null;
+select * from t1;
+drop table t1;
+
+#
+# Bug#15028 Multitable update returns different numbers of matched rows
+#           depending on table order
+create table t1 (f1 int);
+create table t2 (f2 int);
+insert into t1 values(1),(2);
+insert into t2 values(1),(1);
+--enable_info
+update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
+--disable_info
+update t2 set f2=1;
+update t1 set f1=1 where f1=3;
+--enable_info
+update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
+--disable_info
+drop table t1,t2;
+
+
+# BUG#15935
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
+insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
+flush status;
+update t2 set a=3 where a=2;
+show status like 'handler_read%';
+drop table t1, t2;
+
+#
+# Bug #16510 Updating field named like '*name' caused server crash
+#
+create table t1(f1 int, `*f2` int);
+insert into t1 values (1,1);
+update t1 set `*f2`=1;
+drop table t1;
+
+#
+# Bug#25126: Wrongly resolved field leads to a crash
+#
+create table t1(f1 int);
+--error 1054
+update t1 set f2=1 order by f2;
+drop table t1;
+# End of 4.1 tests
+
+#
+# Bug #24035: performance degradation with condition int_field=big_decimal
+#
+
+CREATE TABLE t1 (
+  request_id int unsigned NOT NULL auto_increment,
+  user_id varchar(12) default NULL,
+  time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
+  ip_address varchar(15) default NULL,
+  PRIMARY KEY (request_id),
+  KEY user_id_2 (user_id,time_stamp)
+);
+
+INSERT INTO t1 (user_id) VALUES ('user1');
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+INSERT INTO t1(user_id) SELECT user_id FROM t1;
+
+--disable_ps2_protocol
+flush status;
+SELECT user_id FROM t1 WHERE request_id=9999999999999; 
+show status like '%Handler_read%';
+SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999; 
+show status like '%Handler_read%';
+UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
+show status like '%Handler_read%';
+UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
+show status like '%Handler_read%';
+--enable_ps2_protocol
+
+DROP TABLE t1;
+
+#
+# Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it 
+# doesn't select
+#
+CREATE TABLE t1 (
+
+  a INT(11),
+  quux decimal( 31, 30 ),
+
+  UNIQUE KEY bar (a),
+  KEY quux (quux)
+);
+
+INSERT INTO
+ t1 ( a, quux )
+VALUES
+    ( 1,    1 ),
+    ( 2,  0.1 );
+
+INSERT INTO t1( a )
+  SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
+
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+#
+# Bug #22364: Inconsistent "matched rows" when executing UPDATE
+#
+
+connect (con1,localhost,root,,test);
+connection con1;
+
+set tmp_table_size=1024;
+
+# Create the test tables
+create table t1 (id int, a int, key idx(a));
+create table t2 (id int unsigned not null auto_increment primary key, a int);
+insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
+insert into t2(a) select a from t2; 
+insert into t2(a) select a from t2;
+insert into t2(a) select a from t2; 
+update t2 set a=id;
+insert into t1 select * from t2;
+
+# Check that the number of matched rows is correct when the temporary
+# table is small enough to not be converted to MyISAM
+select count(*) from t1 join t2 on (t1.a=t2.a);
+--enable_info
+update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
+--disable_info
+
+# Increase table sizes
+insert into t2(a) select a from t2; 
+update t2 set a=id; 
+truncate t1; 
+insert into t1 select * from t2; 
+
+# Check that the number of matched rows is correct when the temporary
+# table has to be converted to MyISAM
+select count(*) from t1 join t2 on (t1.a=t2.a);
+--enable_info
+update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
+--disable_info
+
+# Check that the number of matched rows is correct when there are duplicate
+# key errors
+update t1 set a=1;
+update t2 set a=1;
+select count(*) from t1 join t2 on (t1.a=t2.a);
+--enable_info
+update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
+--disable_info
+
+drop table t1,t2;
+
+connection default;
+disconnect con1;
+
+#
+# Bug #40745: Error during WHERE clause calculation in UPDATE
+#             leads to an assertion failure
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+CREATE FUNCTION f1() RETURNS INT RETURN f1();
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1);
+
+--error ER_SP_NO_RECURSION
+UPDATE t1 SET i = 3 WHERE f1();
+--error ER_SP_NO_RECURSION
+UPDATE t1 SET i = f1();
+
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+--echo End of 5.0 tests
+
+--echo #
+--echo # Bug #47919 assert in open_table during ALTER temporary table
+--echo #
+
+CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
+CREATE TEMPORARY TABLE t2 LIKE t1;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+
+ALTER TABLE t2 COMMENT = 'ABC';
+UPDATE t2, t1 SET t2.f1 = 2, t1.f1 = 9;
+ALTER TABLE t2 COMMENT = 'DEF';
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#50545: Single table UPDATE IGNORE crashes on join view in
+--echo # sql_safe_updates mode.
+--echo #
+CREATE TABLE t1 ( a INT, KEY( a ) );
+INSERT INTO t1 VALUES (0), (1);
+CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
+SET SESSION sql_safe_updates = 1;
+
+--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
+UPDATE IGNORE v1 SET a = 1;
+
+SET SESSION sql_safe_updates = DEFAULT;
+DROP TABLE t1;
+DROP VIEW v1;
+
+--echo #
+--echo # Bug#54734 assert in Diagnostics_area::set_ok_status
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, not_exists;
+DROP FUNCTION IF EXISTS f1;
+DROP VIEW IF EXISTS v1;
+--enable_warnings
+
+CREATE TABLE t1 (PRIMARY KEY(pk)) AS SELECT 1 AS pk;
+CREATE FUNCTION f1() RETURNS INTEGER RETURN (SELECT 1 FROM not_exists);
+CREATE VIEW v1 AS SELECT pk FROM t1 WHERE f1() = 13;
+--error ER_VIEW_INVALID
+UPDATE v1 SET pk = 7 WHERE pk > 0;
+
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP TABLE t1;
+
+--echo # Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT
+--echo #                RESULTS
+
+CREATE TABLE t1 (a VARCHAR(50), b TEXT, c CHAR(50)) ENGINE=INNODB;
+
+INSERT INTO t1 (a, b, c) VALUES ('start trail', '', 'even longer string');
+UPDATE t1 SET b = a, a = 'inject';
+SELECT a, b FROM t1;
+UPDATE t1 SET b = c, c = 'inject';
+SELECT c, b FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-3948 Assertion `records_are_comparable(table)' fails in compare_record(const TABLE*) on UPDATE with simple AND condition, index_merge+index_merge_intersection, InnoDB
+--echo #
+--echo # Verify that UPDATE does the same number of handler_update
+--echo # operations, no matter if there is ORDER BY or not.
+--echo #
+
+CREATE TABLE t1 (i INT) ENGINE=INNODB;
+INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
+                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
+                      (30),(31),(32),(33),(34),(35);
+CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2),
+                 INDEX idx (a,b(1),c)) ENGINE=INNODB;
+INSERT INTO t2 SELECT i, i, i, i FROM t1;
+FLUSH STATUS; # FLUSH is autocommit, so we put it outside of transaction
+START TRANSACTION;
+UPDATE t2 SET d = 10 WHERE b = 10 LIMIT 5;
+SHOW STATUS LIKE 'HANDLER_UPDATE';
+ROLLBACK;
+FLUSH STATUS;
+START TRANSACTION;
+UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
+SHOW STATUS LIKE 'HANDLER_UPDATE';
+ROLLBACK;
+
+--echo Same test with a different UPDATE.
+
+ALTER TABLE t2 DROP INDEX idx, ADD INDEX idx2 (a, b);
+FLUSH STATUS;
+START TRANSACTION;
+UPDATE t2 SET c = 10 LIMIT 5;
+SHOW STATUS LIKE 'HANDLER_UPDATE';
+ROLLBACK;
+FLUSH STATUS;
+START TRANSACTION;
+UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
+SHOW STATUS LIKE 'HANDLER_UPDATE';
+ROLLBACK;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-8938: Server Crash on Update with joins
+--echo #
+
+CREATE TABLE `t1` (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+UPDATE `t1` SET value = CONCAT("*.",(SELECT `temptable`.`value` FROM (SELECT * FROM `t1` WHERE `name`="consoleproxy.url.domain") AS `temptable` WHERE `temptable`.`name`="consoleproxy.url.domain")) WHERE `name`="consoleproxy.url.domain";
+
+drop table t1;
+
+CREATE TABLE `t1` (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+create table t2 (
+  `name` varchar(255) NOT NULL,
+  `value` varchar(4095) DEFAULT NULL,
+  PRIMARY KEY (`name`)
+);
+
+UPDATE t1
+SET value = (SELECT value FROM t2 WHERE `name`= t1.name) 
+WHERE value is null ;
+
+drop table t1,t2;
+
+--echo #
+--echo #MDEV-8701: Crash on derived query
+--echo #
+
+CREATE TABLE t1 (
+  data_exit_entry_id int(11) NOT NULL,
+  data_entry_id int(11) NOT NULL,
+  data_entry_exit_id int(11) NOT NULL,
+  data_exit_entry_quantity double NOT NULL
+) DEFAULT CHARSET=utf8;
+
+CREATE TABLE t2 (
+  data_entry_id int(11) NOT NULL,
+  data_entry_cost double NOT NULL,
+  data_entry_quantity double NOT NULL
+) DEFAULT CHARSET=utf8;
+
+create  algorithm=temptable view v1 as SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost 
+          FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id;
+
+UPDATE t2 
+SET data_entry_cost
+  = ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
+	FROM 
+        v1 AS query 
+        WHERE data_entry_exit_id = t2.data_entry_id
+      )
+    );
+
+UPDATE t2 
+SET data_entry_cost
+  = ( ( SELECT SUM(data_exit_entry_quantity * data_entry_cost)
+	FROM 
+        ( SELECT data_entry_exit_id, data_exit_entry_quantity, data_entry_cost 
+          FROM t1 INNER JOIN t2 as dt ON dt.data_entry_id = t1.data_entry_id) AS query 
+        WHERE data_entry_exit_id = t2.data_entry_id
+      )
+    );
+
+drop view v1;
+drop table t1, t2; 
+--echo #
+--echo # MDEV-4410: update does not want to use a covering index, but select uses it.
+--echo #
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (key1 int, col1 int, key(key1));
+insert into t1
+select A.a + 10 * B.a + 100 * C.a, 1234 from t2 A, t2 B, t2 C;
+
+--echo # This must not have "Using filesort":
+explain 
+update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2;
+
+flush status;
+update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 limit 2;
+# Handler_read_next should be 1 (due to LIMIT), not 100:
+show status like 'Handler_read%';
+
+drop table t1, t2;
+
+--echo # End of MariaDB 10.0 tests
+
+--echo #
+--echo # MDEV-18945: multi-table update with constant table and
+--echo #             comparison of date field with integer field
+--echo #
+
+CREATE TABLE t1 (i1 int, d1 date , i2 int , d2 date) engine=myisam;
+INSERT INTO t1 VALUES (19,'0000-00-00',73,'2008-05-21');
+
+CREATE TABLE t2 (d1 date , i1 int, i2 int , d2 date) engine=myisam;
+INSERT INTO t2 VALUES
+  ('2006-01-12',-102,45,'2023-11-25'),('2034-12-19',-102,45,'2001-11-20');
+
+--error ER_TRUNCATED_WRONG_VALUE
+UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2;
+
+DROP TABLE t1,t2;
+
+--echo # End of MariaDB 10.2 tests
+
+--echo #
+--echo # MDEV-20773: UPDATE with LIKE predicate over non-indexed column
+--echo #             of VARCHAR type
+--echo #
+
+create table t1 (a1 varchar(30), a2 varchar(30) collate utf8_bin);
+insert into t1 values
+  ('aa','zzz'), ('b','xxaa'), ('ccc','yyy'), ('ddd','xxb');
+analyze table t1 persistent for all;
+
+explain extended
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+
+update t1 set a1 = 'u'
+  where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c');
+
+select * from t1;
+
+drop table t1;
+
+--echo # End of MariaDB 10.4 tests