9 files changed, 314 insertions, 0 deletions
diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result
index 90f59b96e61..15ef32447a8 100644
--- a/mysql-test/r/innodb_mrr_cpk.result
+++ b/mysql-test/r/innodb_mrr_cpk.result
@@ -149,3 +149,25 @@ set @@join_cache_level= @save_join_cache_level;
 set storage_engine=@save_storage_engine;
 set optimizer_switch=@innodb_mrr_cpk_tmp;
 drop table t0;
+#
+# MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions
+#
+set @tmp_mdev3817=@@optimizer_switch;
+SET optimizer_switch='index_merge=on,index_merge_intersection=on';
+CREATE TABLE t1 (
+a INT PRIMARY KEY,
+b INT,
+c VARCHAR(1024) CHARACTER SET utf8,
+d INT,
+KEY (b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES 
+(1, 9, 'one', 11), (2, 6, 'two', 12), (3, 2, 'three', 13), (4, 5, 'four', 14);
+CREATE TABLE t2 (e INT, g INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,9), (2,6) ;
+SELECT * FROM t1, t2 WHERE g = b AND ( a < 7 OR a > e );
+a	b	c	d	e	g
+1	9	one	11	1	9
+2	6	two	12	2	6
+DROP TABLE t1, t2;
+set optimizer_switch=@tmp_mdev3817;
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 3215a60387a..adfe3287459 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -576,4 +576,24 @@ a	b
 1	2
 0	1
 DROP TABLE t1;
+#
+# BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET ==
+# SAVE_READ_SET
+#
+CREATE TABLE t1 (
+a INT,
+b INT,
+c INT,
+PRIMARY KEY (c,a), KEY (a),KEY (a)
+) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2;
+INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1);
+UPDATE t1 SET b = 0, c=1 WHERE a <=>0;
+SELECT * FROM t1;
+a	b	c
+1	5	1
+2	4	1
+3	3	1
+4	2	1
+5	1	1
+DROP TABLE t1;
 set global default_storage_engine=default;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 3b7d07d525e..146d250d687 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1953,3 +1953,49 @@ a	b	c	d
 14	1	1	q
 9	7	1	s
 DROP TABLE t1;
+#
+# BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET ==
+# SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 
+#
+CREATE TABLE t1 (
+f1 INT AUTO_INCREMENT,
+f2 INT,
+f3 INT,
+f4 INT,
+PRIMARY KEY (f1),KEY(f2)
+) ENGINE=INNODB;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103;
+INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0),
+(NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157);
+SELECT * FROM v2;
+f1	f2	f3	f4
+UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1;
+SELECT * FROM v2;
+f1	f2	f3	f4
+DROP TABLE t1;
+DROP VIEW v2;
+CREATE TABLE t1 (
+f1 INT AUTO_INCREMENT,
+f2 INT,
+f3 INT,
+f4 INT,
+PRIMARY KEY (f1),KEY(f2)
+) ENGINE=INNODB;
+INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18),
+(10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3);
+CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ;
+SELECT * FROM v3;
+f1	f2	f3	f4
+1	NULL	NULL	0
+2	2	0	3
+9	0	107	18
+10	0	0	0
+UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1;
+SELECT * FROM v3;
+f1	f2	f3	f4
+1	NULL	NULL	0
+2	2	0	3
+9	0	107	18
+10	0	0	0
+DROP TABLE t1;
+DROP VIEW v3;
diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result
index 354a4758e7c..3769ceb9145 100644
--- a/mysql-test/r/range_mrr_icp.result
+++ b/mysql-test/r/range_mrr_icp.result
@@ -1955,4 +1955,50 @@ a	b	c	d
 9	7	1	s
 14	1	1	q
 DROP TABLE t1;
+#
+# BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET ==
+# SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 
+#
+CREATE TABLE t1 (
+f1 INT AUTO_INCREMENT,
+f2 INT,
+f3 INT,
+f4 INT,
+PRIMARY KEY (f1),KEY(f2)
+) ENGINE=INNODB;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103;
+INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0),
+(NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157);
+SELECT * FROM v2;
+f1	f2	f3	f4
+UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1;
+SELECT * FROM v2;
+f1	f2	f3	f4
+DROP TABLE t1;
+DROP VIEW v2;
+CREATE TABLE t1 (
+f1 INT AUTO_INCREMENT,
+f2 INT,
+f3 INT,
+f4 INT,
+PRIMARY KEY (f1),KEY(f2)
+) ENGINE=INNODB;
+INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18),
+(10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3);
+CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ;
+SELECT * FROM v3;
+f1	f2	f3	f4
+1	NULL	NULL	0
+2	2	0	3
+9	0	107	18
+10	0	0	0
+UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1;
+SELECT * FROM v3;
+f1	f2	f3	f4
+1	NULL	NULL	0
+2	2	0	3
+9	0	107	18
+10	0	0	0
+DROP TABLE t1;
+DROP VIEW v3;
 set optimizer_switch=@mrr_icp_extra_tmp;
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 427374fd995..3f3a3ac07df 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -561,3 +561,45 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function
 DROP VIEW v1;
 DROP FUNCTION f1;
 DROP TABLE t1;
+#
+# Verify that UPDATE does the same number of handler_update
+# operations, no matter if there is ORDER BY or not.
+#
+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;
+START TRANSACTION;
+UPDATE t2 SET d = 10 WHERE b = 10 LIMIT 5;
+SHOW STATUS LIKE 'HANDLER_UPDATE';
+Variable_name	Value
+Handler_update	1
+ROLLBACK;
+FLUSH STATUS;
+START TRANSACTION;
+UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
+SHOW STATUS LIKE 'HANDLER_UPDATE';
+Variable_name	Value
+Handler_update	1
+ROLLBACK;
+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';
+Variable_name	Value
+Handler_update	5
+ROLLBACK;
+FLUSH STATUS;
+START TRANSACTION;
+UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
+SHOW STATUS LIKE 'HANDLER_UPDATE';
+Variable_name	Value
+Handler_update	5
+ROLLBACK;
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/innodb_mrr_cpk.test b/mysql-test/t/innodb_mrr_cpk.test
index a157ddd792f..a7b2d9c0ddd 100644
--- a/mysql-test/t/innodb_mrr_cpk.test
+++ b/mysql-test/t/innodb_mrr_cpk.test
@@ -139,3 +139,29 @@ set storage_engine=@save_storage_engine;
 set optimizer_switch=@innodb_mrr_cpk_tmp;
 drop table t0;
 
+--echo #
+--echo # MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions
+--echo #
+
+set @tmp_mdev3817=@@optimizer_switch;
+SET optimizer_switch='index_merge=on,index_merge_intersection=on';
+
+CREATE TABLE t1 (
+ a INT PRIMARY KEY,
+ b INT,
+ c VARCHAR(1024) CHARACTER SET utf8,
+ d INT,
+ KEY (b)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES 
+(1, 9, 'one', 11), (2, 6, 'two', 12), (3, 2, 'three', 13), (4, 5, 'four', 14);
+
+CREATE TABLE t2 (e INT, g INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,9), (2,6) ;
+
+SELECT * FROM t1, t2 WHERE g = b AND ( a < 7 OR a > e );
+
+DROP TABLE t1, t2;
+set optimizer_switch=@tmp_mdev3817;
+
diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index 4c7f55ffa30..a743ad45568 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -665,4 +665,19 @@ SHOW CREATE TABLE t1;
 SELECT * FROM t1;
 DROP TABLE t1;
 
+--echo #
+--echo # BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET ==
+--echo # SAVE_READ_SET
+--echo #
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ c INT,
+ PRIMARY KEY (c,a), KEY (a),KEY (a)
+) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2;
+INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1);
+UPDATE t1 SET b = 0, c=1 WHERE a <=>0;
+SELECT * FROM t1;
+DROP TABLE t1;
+
 set global default_storage_engine=default;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 6ac883ffcc1..05f85466d45 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1,6 +1,7 @@
 #
 # Problem with range optimizer
 #
+--source include/have_innodb.inc
 
 --disable_warnings
 drop table if exists t1, t2, t3, t10, t100;
@@ -1549,3 +1550,58 @@ SELECT * FROM t1 ignore index(d) WHERE d = 'q' OR d >= 'q' OR (d IN ( 'j' , 's'
 SELECT * FROM t1 force index(d)  WHERE d = 'q' OR d >= 'q' OR (d IN ( 'j' , 's' , 'i' ) AND ( b = 102 ));
 
 DROP TABLE t1;
+
+--disable_parsing
+# MariaDB: Moved the following to partition.test
+--echo #
+--echo # BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET ==
+--echo # SAVE_READ_SET
+--echo #
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ c INT,
+ PRIMARY KEY (c,a), KEY (a),KEY (a)
+) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2;
+INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1);
+UPDATE t1 SET b = 0, c=1 WHERE a <=>0;
+SELECT * FROM t1;
+DROP TABLE t1;
+--enable_parsing
+
+--echo #
+--echo # BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET ==
+--echo # SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 
+--echo #
+CREATE TABLE t1 (
+ f1 INT AUTO_INCREMENT,
+ f2 INT,
+ f3 INT,
+ f4 INT,
+ PRIMARY KEY (f1),KEY(f2)
+) ENGINE=INNODB;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103;
+INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0),
+ (NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157);
+SELECT * FROM v2;
+UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1;
+SELECT * FROM v2;
+DROP TABLE t1;
+DROP VIEW v2;
+
+CREATE TABLE t1 (
+ f1 INT AUTO_INCREMENT,
+ f2 INT,
+ f3 INT,
+ f4 INT,
+ PRIMARY KEY (f1),KEY(f2)
+) ENGINE=INNODB;
+INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18),
+ (10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3);
+CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ;
+SELECT * FROM v3;
+UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1; 
+SELECT * FROM v3;
+DROP TABLE t1;
+DROP VIEW v3;
+
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index c515f8873d8..daa20509ab6 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -2,6 +2,8 @@
 # test of updating of keys
 #
 
+-- source include/have_innodb.inc
+
 --disable_warnings
 drop table if exists t1,t2;
 --enable_warnings
@@ -503,3 +505,42 @@ UPDATE v1 SET pk = 7 WHERE pk > 0;
 DROP VIEW v1;
 DROP FUNCTION f1;
 DROP TABLE t1;
+
+--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;
+