# MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions

Reconcile the fixes from:
#
# guilhem.bichot@oracle.com-20110805143029-ywrzuz15uzgontr0
# Fix for BUG#12698916 - "JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
# AFTER FLUSH TABLES [-INT VS NULL]"
#
# guilhem.bichot@oracle.com-20111209150650-tzx3ldzxe1yfwji6
# Fix for BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == SAVE_READ_SET
# and
#
and related fixes from: BUG#1006164, MDEV-376:

Now, ROR-merged QUICK_RANGE_SELECT objects make no assumptions about the values
of table->read_set and table->write_set.
Each QUICK_ROR_SELECT has (and had before) its own column bitmap, but now, all 
QUICK_ROR_SELECT's functions that care: reset(), init_ror_merged_scan(), and 
get_next()  will set table->read_set when invoked and restore it back to what 
it was before the call before they return.

This allows to avoid the mess when somebody else modifies table->read_set for 
some reason.

1 files changed, 41 insertions, 0 deletions

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;
+