From: Roy Lyseng
Date: June 21 2011 9:29am
Subject: bzr commit into mysql-trunk branch (roy.lyseng:3386) Bug#12603200
List-Archive: http://lists.mysql.com/commits/139550
X-Bug: 12603200
Message-Id: <20110621092928.2E9861F5@tyr67.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0316449491812282398=="

--===============0316449491812282398==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///home/rl136806/mysql/repo/mysql-work0/ based on revid:roy.lyseng@stripped

 3386 Roy Lyseng	2011-06-21
      Bug#12603200: Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
      
      The problematic query is semi-join transformed and a LooseScan
      strategy is selected. setup_semijoin_dups_elimination() inspects
      the provided quick select object and attempts to set it to require
      ordering of output rows. However, the quick select object was not
      selected in the first place (see
      Loose_scan_opt::check_ref_access_part1()), hence there is a missing
      check that the index covered by the quick select matches the index
      selected for the loose scan access. Will also delete the quick select
      object when it is clear it will not be used later.
      
      Fixed by adding this check, and also deleting the quick select object
      if it was not chosen for accessing this table.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#12603200.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Added test case result for bug#12603200.
      
      sql/sql_select.cc
        Fixed setup_semijoin_dups_elimination() as described above.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-06-20 15:47:50 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-06-21 09:29:01 +0000
@@ -3670,3 +3670,81 @@ eval $query;
 DROP TABLE t1, t2, t3;
 
 --echo # End of the test for bug#12640083.
+
+--echo #
+--echo # Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+--echo #
+
+CREATE TABLE t1 (
+  pk int NOT NULL,
+  col_int_key int NOT NULL,
+  col_varchar_nokey varchar(1) NOT NULL,
+  col_varchar_key varchar(1) NOT NULL,
+  PRIMARY KEY(pk),
+  KEY col_int_key(col_int_key),
+  KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+
+CREATE TABLE t2 (
+  pk int NOT NULL,
+  col_int_key int NOT NULL,
+  col_varchar_key varchar(1) NOT NULL,
+  PRIMARY KEY(pk),
+  KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+
+let $query=
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+                               FROM t2 AS innr
+                               WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+  AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo # End of bug#12603200

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-06-21 09:29:01 +0000
@@ -5563,4 +5563,85 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index	PRIMARY,col_varchar_key	col_varchar_key	7	NULL	20	Using where; Using index; Using temporary; Using filesort; LooseScan
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2011-06-21 09:29:01 +0000
@@ -5567,5 +5567,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index	PRIMARY,col_varchar_key	col_varchar_key	7	NULL	20	Using where; Using index; Using temporary; Using filesort; LooseScan
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; Using join buffer (BKA, incremental buffers)
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2011-06-21 09:29:01 +0000
@@ -5567,5 +5567,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index	PRIMARY,col_varchar_key	col_varchar_key	7	NULL	20	Using where; Using index; Using temporary; Using filesort; LooseScan
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-06-21 09:29:01 +0000
@@ -5562,4 +5562,85 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2011-06-21 09:29:01 +0000
@@ -5566,5 +5566,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2011-06-21 09:29:01 +0000
@@ -5566,5 +5566,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-06-21 09:29:01 +0000
@@ -5564,6 +5564,87 @@ ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
 #
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-06-21 09:29:01 +0000
@@ -5568,6 +5568,87 @@ ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
 #
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-06-21 09:29:01 +0000
@@ -5568,6 +5568,87 @@ ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
 #
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-06-21 09:29:01 +0000
@@ -5563,4 +5563,85 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index	PRIMARY,col_varchar_key	col_varchar_key	7	NULL	20	Using where; Using index; Using temporary; Using filesort; LooseScan
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2011-06-21 09:29:01 +0000
@@ -5567,5 +5567,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index	PRIMARY,col_varchar_key	col_varchar_key	7	NULL	20	Using where; Using index; Using temporary; Using filesort; LooseScan
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2011-06-21 09:29:01 +0000
@@ -5567,5 +5567,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index	PRIMARY,col_varchar_key	col_varchar_key	7	NULL	20	Using where; Using index; Using temporary; Using filesort; LooseScan
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-06-21 09:29:01 +0000
@@ -5563,4 +5563,85 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2011-06-21 09:29:01 +0000
@@ -5567,5 +5567,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2011-06-21 09:29:01 +0000
@@ -5567,5 +5567,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where; Using temporary; Using filesort; Start temporary
+1	PRIMARY	outr	ref	col_varchar_key	col_varchar_key	3	test.innr.col_varchar_key	1	Using where; End temporary
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-06-21 09:29:01 +0000
@@ -5785,4 +5785,85 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	outr	ALL	NULL	NULL	NULL	NULL	16	Using where; Using filesort
+2	SUBQUERY	innr	index_merge	PRIMARY,col_varchar_key	col_varchar_key,PRIMARY	3,4	NULL	2	Using sort_union(col_varchar_key,PRIMARY); Using where
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-06-21 09:29:01 +0000
@@ -5708,4 +5708,85 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	outr	ALL	NULL	NULL	NULL	NULL	16	Using where; Using filesort
+2	DEPENDENT SUBQUERY	innr	index_subquery	PRIMARY,col_varchar_key	col_varchar_key	3	func	1	Using index; Using where
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2011-06-21 09:29:01 +0000
@@ -5712,5 +5712,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	outr	ALL	NULL	NULL	NULL	NULL	16	Using where; Using filesort
+2	DEPENDENT SUBQUERY	innr	index_subquery	PRIMARY,col_varchar_key	col_varchar_key	3	func	1	Using index; Using where
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2011-06-20 15:47:50 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2011-06-21 09:29:01 +0000
@@ -5712,5 +5712,86 @@ a
 ab
 DROP TABLE t1, t2, t3;
 # End of the test for bug#12640083.
+#
+# Bug#12603200 - Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t1 VALUES
+(1,7,'a','a'),
+(2,0,'v','v'),
+(3,9,'c','c'),
+(4,3,'m','m'),
+(5,2,'a','a'),
+(6,1,'d','d'),
+(7,8,'y','y'),
+(8,6,'t','t'),
+(11,7,'a','x'),
+(12,0,'v','v'),
+(13,9,'c','c'),
+(14,3,'m','m'),
+(15,2,'a','x'),
+(16,1,'d','d'),
+(17,8,'y','y'),
+(18,6,'t','u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_key int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) engine=innodb;
+INSERT INTO t2(pk,col_int_key,col_varchar_key) VALUES
+(8,7,'c'),
+(11,4,'l'),
+(12,7,'b'),
+(13,0,'c'),
+(14,2,'i'),
+(15,9,'h'),
+(16,4,'q'),
+(17,1,'m'),
+(18,9,'b'),
+(19,2,'e'),
+(20,1,'c'),
+(21,7,'z'),
+(22,4,'l'),
+(23,7,'z'),
+(24,0,'c'),
+(25,2,'i'),
+(26,9,'h'),
+(27,4,'q'),
+(28,0,'a'),
+(29,1,'d');
+EXPLAIN SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	outr	ALL	NULL	NULL	NULL	NULL	16	Using where; Using filesort
+2	DEPENDENT SUBQUERY	innr	index_subquery	PRIMARY,col_varchar_key	col_varchar_key	3	func	1	Using index; Using where
+SELECT outr.col_varchar_key AS x, outr.pk AS y
+FROM t1 AS outr
+WHERE outr.col_varchar_key IN (SELECT innr.col_varchar_key
+FROM t2 AS innr
+WHERE innr.col_varchar_key = 'a' OR innr.pk = 8)
+AND outr.col_varchar_nokey < 't'
+ORDER BY outr.col_varchar_key, outr.pk;
+x	y
+a	1
+a	5
+c	3
+c	13
+DROP TABLE t1, t2;
+# End of bug#12603200
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-06-20 15:47:50 +0000
+++ b/sql/sql_select.cc	2011-06-21 09:29:01 +0000
@@ -1535,17 +1535,21 @@ bool setup_semijoin_dups_elimination(JOI
         tab->loosescan_match_tab= last_sj_tab;
 
         /* For LooseScan, duplicate elimination is based on rows being sorted 
-           on key. We need to make sure that range select keep the sorted index
+           on key. We need to make sure that range select keeps the sorted index
            order. (When using MRR it may not.)  
 
            Note: need_sorted_output() implementations for range select classes 
            that do not support sorted output, will trigger an assert. This 
-           should happen since LooseScan strategy will not be picked if sorted 
-           output is not supported.
+           should not happen since LooseScan strategy is only picked if sorted 
+           output is supported.
         */
         if (tab->select && tab->select->quick)
-          tab->select->quick->need_sorted_output(true);
-
+        {
+          if (tab->select->quick->index == pos->loosescan_key)
+            tab->select->quick->need_sorted_output(true);
+          else
+            tab->select->set_quick(NULL);
+        }
         /* Calculate key length */
         keylen= 0;
         keyno= pos->loosescan_key;


--===============0316449491812282398==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/roy.lyseng@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: roy.lyseng@stripped
# target_branch: file:///home/rl136806/mysql/repo/mysql-work0/
# testament_sha1: b5a17ae83c14a2cc74ecc67bbabe64cd5639b8b1
# timestamp: 2011-06-21 11:29:28 +0200
# base_revision_id: roy.lyseng@stripped\
#   v8xter9boutoccij
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWeYAC+cALMp/gEXwAAhc5///
f+//4L////pgHP4fbaS+bCSOsAAEV2YSAKAAClAAARoAAMhAARAASKUVUVEKCRKRmwpVCIQmp6ho
NBoNAAAyaaAAAAAABxkyaNAaNMRkaGIYE0aYgxGgwgAMOMmTRoDRpiMjQxDAmjTEGI0GEABgz1Uo
p5NRieoNDRoAAAABoAAAAApShBMiYQACNDTETTQaMoeUn5U/SJ6nqGJ7UeoFSiAJkATTQCaTamjU
ninqmJo9I/VND1GygAP1PR0R+F+lX4UaUfWTVGlH3ptRqv/x6L/ajgetPYtC9aHC/RfmnHaxMl8p
7Fo3i/pVifLdVHXR3qNqNKOVG1X/2jhR/pRtRxqyjqo93w6vi0fPj/jsyc1OlFVOVFjtLPya5/vY
1NaCihX/zx/q27dtVtB6rFUe8TcyN1UKoIk/Exg/ImIrxZMg02ORkBBkFhTAAA4iectLDvUf6E4C
SYToE3/Tvz20fM2wouM/ensmtJFc0hxeNGylsosp2p2tld/k4ddGd1GcKNKOuUatKMyjuivF9dRo
o+zfsyzHJpNLh5JRpR1UcqHykLgn/bOSSKieetcIj36cbePOxsdxZXosYWscLFM5DrXZsLJDj4pD
L6W3eH1zkPWR8+C3Uvaf7/n0emo+2o8fblGUelvqNN70diSzuWWc6yzFliFiFUREqVEEQm++0nE2
dDaadTLmY2UzsMNpqZsXJkxGMbjTlZhRJLb5bavS6Rc8sbcLbOUkMcLJDDNX5SQ+LKj6Kj1efrqO
FRuqN1Rs+77Sf1qPcT04T443/L0VH/sTW/cJuX0+vf8HoZjFGKMoxRxKH0VHQ9Unso2uUfiT+pMJ
pUvCTkTre/4Zy+fr8Fr+HDKyuDC/6kVXLmVKcHQjMRVURTy/FdoI7oj0kKbaZ7PPfZrarEUrS5hD
85IdZR4qj0VHTzc2KMUceelnTnf7/S126uHZt5/hJt3eLOG+wnRrz7qjuqO6o8VR+VR0SQ4bYjj1
bfjg+1JwpblzsySLa91KHFdh4deiOmNmFeRH+UkP5kVEVkhUjaRdrDZDOSUbURlt2YYcruWWd9tj
PC3G7AklsvSFIYCIgFBeouExJhhsZopEmDnvkPdVumcnIQhUFTaK5TKASlJCiP/VD9aJw9YivHPZ
lb2+rbZdhftvrnnvp80FMVT5f40ZijGUeWk0u6o6KjqJoT6SfAnlJ/yTcJ4ieIm8nlJ+hPGT1E3k
6CdJPWTkT9SdRMJoTpJzJzJwJ1E3E5E1J0k6Sf9aZRlGom4TQTcJ1icBNwnATsqPlCUyowCYJ8jy
/MVeIJTZ7MqOBVvTT9gnnJo6d3swTKj2uZVqT7uYmOeufYJJlGlGUfu1ff6NFHeTa2rIKmSDdvE1
/19dRrUft6Sd/kT5SYT5ieAmEwmE9pMJsTYm8mhNCbCYJqVbiaiYJ7hNibEwnwE1E0J8BMKuAmBP
EJ0CYTeTeTiJvJ0E4icCfATUm8mhME4JyJxJwJwJ0CbCbE0JoTcTYTUmgnAm8nUTtJ36NCdiOpg+
nj++W78Nl/Ctllt1br8Pm3N+VKeh5H1PV//fD5r3wlCpvZvhINminENOaxHPbTY/GUxfxOirjzu8
n25EfaxtPK3+pOBFp8hNVnzkOKpnDq3s7uWbbb+o4RVcKh5mtXGoWxEne7165XZbXOtd1a8K1vrX
PGxWiylt1cLZhjjdlmpJx7LNElmjdZ7LN5dGNW5KqqqgBjGPYusnX251NSdKL2LKrVg2zdp4fgRc
ScBcXDVJxbOPFDavSzVUxBgtVSDirnRJYJa7PLFaY2L2TNVnEi9VariplquerRVlkvQWCWFEkYsm
SYdVZVergUxfp/hisbF7ZPHFsZZXJIuUzb2jBluki+bGxa0WrnNuU0znBjh6bWPo0Yvc+ZD+iOG+
PHvoyNqOtT0k172PG1pXPjHT2c937CrCeYi7SakwnEjQ4pIppi7WkO1iTgsdmTeokGMzSRb5PDve
+4RsbBDTjy1ZdtlrIRlwoG3e8dz/ZIjaubrvOyzdj2vs3+eVl/PDCvWmzDXJm8ZDgI7/QNrJkcbx
GySP6JHBJx/frzcWIjxjASnRTLN0UcTbiIwdGfRiouEbFUlJdus1WLFrf/fgI1Yodm9gUf6kkd3R
fiRuVbmocp3+zSzVQx75uiLmLlcc1i95h2iLW1qq2smL0pglNH3PFJHB7nkirvkeFPL3I1pJDnvW
34O15HuQncR4EcCaE6lqTxpOKzloJeMrvvLTc8zZmzyIsSRfubd1/BQKGKjEj5e2isk56ubazxIs
ZWM8ZheRWtkxwVv9pDAix5LW1aRWRGi7zUmmrcGBGLC+s81pGi1SjNgzZXkcJJGK61k6MXpQTsRv
bW5uZGjc1Wsi9a1b1z6SFrBY2NuavZDSLqOzHZrIXyF2O3B4CLyL0YkVI5EZrHNil1XPYsCyRGrc
qpxY5NpixUZlHPYuMFzN0znG7Y5q7Fy1WjBYtsU2tWi16r4Tc5v9DoFvBtbmbcyfJVo4uCxqtVYT
S1R5s5Da6owl17nZxb7di21YpfzxCKEWCO4jgxC3BdJHRRIycHO/gpc37XgritVUXXNjBo5WL3j5
VWtzY1aKNgbmDo3qtW1lmt2t69rfNFjf1XXed4l2Kjdtb11zVx455aMcW1sl6m7hTiJgxWLlXFRd
gvWtziqo04sJDReyYsGxwfZnIf2cnq+r2weHtIcpZrzWSvdW/c8CN5G4ihFyYCdGTm5sl7gwWKMH
O5rgsOrRYmDSdXuJh114r2rKWmelVo73lbdbdWLBjz+fQOhhtTisW2tzg4MKOLBfXmpYb1yxmtfW
Q001bmxm5qc1yri0b1qz8FHFrIfb7Pd3sTlt8bOtlOM3WW7s9gnAnoJhPITZyDsa0nF6GjxcWPcq
YpMKIWbqWrCTuUSRmovouaOtklVF7q2McHitWr5ivaNqyPGjRRNKCi/VZu987KbFt9ry8tra1cGy
3a6m/co2MaKXMmlGbavJblvbljnjJG/fU5N7Vsb2jY5OTizZr+LeybG8PB2Z6vRNp4cNGtbN/gJJ
uI1IoR4EejqHQ0XPwyO5zd/fv5ZsklaMc7TFb9Kvpy8G6QvWOLo02MnVa2iVeTcuX3WPTLkwfJoy
1yVY24cWWCv2UasW/CSOFOFK729gzZNkqterBY4qb1jNcoo2G5o4sPUoXsZ0Vbm1n5b+M1zpVZS3
nJDf5CMiPIiwihFCMXVmuWqq4OjZsXLZC9cvqGiyzw/IN6zoxzbHesZNveuVavmq3Wtmd6WMe9W+
9vWL17s78Vy5JHBrMaLmbsqyw38GijVc4rG9ub2qSOXlu7N7Rg0WrnBYvXN6xmvewm2s7sE6WKpW
lNMq9iEsIsI81qssvokjPg2339yi5e48a2dHOiSOTNu2F7g1e+a5qHSh0ZsGjNml81UvZtGa1qw7
mC0sx6s1WzJRY0VaKLH1EsasHiotWN6xg5vQLFkx7qyPv/jEcLbStOvGsabqX8qpGHCTftUzXnFp
vU5i49Gafx9/gfJrbUYo1UbbKPHHZVhrVg+NZGW9PXUfQpm7FR/LsceOmUZWZT9knqi1mSqqqACI
mbEks5Hxss7PQsnUtsj/j0FX2k+o/QTn3KPvhWhUfcJx0qbR3sLFMzP2v8Cr9DdUcMJ8n95OAm1C
/NJ1X5VGkmxOwTVUdhVy0E6taF/4oX53/mU/uqO/JsJxKdnOjfJ3yL+8m0rro3VH+2VGvek/5VGn
RJrCvDrUZR+sm8TUTv/a01oxijBP0k78Kd8m4q8VC5g5GShgnAcc/qqPJCumTKVuqOFpJw3lDQTW
wTsB5tih5ROxC0qOdR2FDpJ0X6k+9OSHBwUaprRz6zdJwqOKoxUdwmSdsK/6VHkJqJ3BmRlgp6dB
NCo7qjvcqMo8EnaJ17XpGR3xOsobrroWxVyE1E6POfrPIJkm0pOnhF02MTKsMMkyF5uMo0dcTqFM
KvBUYJpK9Enkk1k73+X9F9VCfyUJX7RHdEU/YkFSq1Z9v2vuvrWyy261etstuvvuutttu/aISVEU
EUIoIsqJoTCZQsJvpPro4lfvTdStC3G/gpy1J/DTly5n+rek6WGKsME4qdNrK3VFXhUdVHhf5Px/
tRT8V9z/F978HDhjuqatnjaybNX3vyfi5HJ+L8VzB+zFe0Wqs2DVmzft+e1g71tuxo1era1/l2m5
Qju0at7FY4Lvwp4dHn4SQaNq/071uC5ycuzB65ift/J0/48E3PuiLfWDG2U9VPXpryskEGkgg6RI
zhTCBl/Rsd7fkokl7Srax6yG4Pte9xG+e/v2ebcp4PBoq83kooqzfcvezxUvezj3r1ra/xWtFKPu
bxF7eyKL3Hjm4tq47tG97b3u7RIyWNyzYxSR/l/zOpGchXwYiW3SIZN9RtRPhRpPvSFOFQmllnxI
cetPMEWljva3LNq5Y8X83e73i6r7C52XOzw62tz0XbeytbWqxcucFpwXMHyUcF6q5zT+2SlKDssZ
Od7Fi/mXrFs2PQTFvergsv8HRY1XvTwlV2CxuaPxYM/qnp3SCD16eF3oWcX4Nom7OL6UpKU+RCyS
DDpY5qMKvuJ+BPPV9gtLKsDQqNm5RgmpNwm1RuTRNKjbdRpUOvXh6fh9TvBewPpdlGvDMzMHu0Iq
pCk7fUhKphEUOb1ejhtxue0fRRtZmR6qPbR9zh7r13i10UqxfVRykjYwcVrNXjJGBtZstixa3uDd
IjYhLVz6vh8CMPdS83Nyj9HL4X6Ne5V5r/ivIHg3vySMe6Q8H2/5NH3tubu/RJ4sUaJD5SF+XCHJ
bEfO6i2n++iWMa/rd/DHn9CO1x3afwpJB3lIizwn0cHXm4bXpEeznVk92bJIvbMpC1scXm0aqqsH
zeK9+f4XuiSflEfrEdwjX3I8aK9R3xFwpIchLPP6Le5zdO1KbHD5mU7ZUpFFIh8t/dx/r6W/LrVy
8b6OslqjxeSrzULUiiR3vxdmrZizZfj5fYfioVSORq07qOWqDgrX3XnZZyZ+N2NLHCx/4tcpEcn1
f/PLS6aILJCvn50qIfYReken3yskaU4LM9fpWEzkhbij3Uc+HG0k6dqo4cm0t/0d6j2OWdNK04Um
3BjHqen3twuSeVinRSedUk5d9B6NuRFKUrEe3nWqUiPJWy98PmfE5qqUfh99i/e+GT6MMNF7Fgo8
nzZMccX0YMFj4713FYob26asWK99WrJbwYuS1m3sGaRa0q7scXgcnhU9Z8X+8nqk+WJ7S8TrozPd
su3v4K/rPBfIJrIjeFL1kkHBjsRZ1rIUEqI9HPNh5UWv4CM5973d7vWxtJG539aEPkfLh+eLxbXt
34NsVa+1q+l720571Hi3yG1c8yTLiI9qrXTs7w8WkhfXoLvbZId3rIjdXH7Xd34JHzfDgJxZrUke
yEfl3+KSNX2aJFilJk/V3cf6X+4n3pV+dEVi+sTw8arz5F3vFpUf2E4UcajqqxIJqSD+3+Hxp+Da
1eqNRPbtE0+KRS3yF49jNH5tpPNifZR6tEjzNXgI973VLym9+b+xo+Frcre9HB+i/6pIfFBFErSo
mYToaN+ftbVGn15z8XbEfHRlR7ajKqMxRlGEDbWjSjgwXpfuo6Q7HN5cz6hOo8imrPqN6p5xaLgu
oH3yFibyQWmBWSEk2+vDB6tfRR0yZzqGiTSjTnSad1G1Hs2N9iiyhhneT6/kwSRtZYsYRuI36tsh
28nz+skHf1ITWSDjh5pyK0JVLLN++IrW5HxxbbZVh525/q9dmSRlA2Uv7yLfkR3rqOFdSmY0cM11
al0s1ZyZJszVdUmr6lTy2lx3aWX+ErQVwMEwcOmTz6/G3OOQWVDjgmO9yTfuUV5BPBSOZ79VVPX4
OXlcYTpUwoYZlT5Kz6yHbzLXjMkkfJ2xbp43XibhFf629fJwfzVPHOFK+Oio+Rgj3fpwI3Frx+b8
oyXU83hP1qHaL3Qd/1P4bzyVW7FM32fM+JPPku02ow0184uqTwd2n0UaQht4u7/5hPpfjpUPX20v
A6PnyTKnTuU9Sjf3dvp4/sVGPKo/gVa1HhbcoMVech8bl6+7dT6XRHChMm3D7niJ+Qjx9mr8FJCj
pJI9Wz4ez1nH95UQrHl9nTBvN0jKsqMqNXf8eMZhVpwozdKm4qMqMVH8qPgG1Lm4VFW6jjVTXnrr
Bj426hDXePfnl9JpVeF6UG7Z92cSK4SRSkR6e1iRo7OWiSZZ1+S+tC7+Pmkjx541b5DTaJ1f4JFI
iv+ndtbHdE3GsngTRFpne7WtsjbTGNKsiPfR8SF+EulIu32/RYxx6aMFcKVkL04Uk5UYrm3RZpSH
b4yT1mZ5jrIeC5ax5CHdtiLIt3Rjzdu+j0ZdEnv7XKjjn9qlmKO4Xz8JOpjGKjxVPZ3nieOjmW9T
1SerMzM39vgk8O7ds2JydF6H4Sd6js2tsSsLYkl19XLNe62sAAAAAAAAAAAGtJjBlslmbO0u4njx
oRoeViFN2XXmEW29VL6YRHHLLCOHRtvjTfuzM40e6hb+DVvW+5YbVamE6qo6MqV02lW2+TdoNChh
z1o+1Jxpb09X6vVRz5cfxjnXPny5sZimzjB3aZuFq+Zzq8ZOwmyTMXlwfz6PFnPq22Mj+9wJlO4S
onKVJJDfIW2renLu12qfY6fL3ngo9FH8nnJ1N7s7LWiev+Tyuv6qPfVHuF+RMZ+/2to6/T+7qiZd
yeR7Gze6yFCV8rhMSIjLwfq7wij0dW56i9DmVdlRVq58rdpj+dGmNBed0PI7gud7i0f4vBYTZbJD
eptsI1T7pChyE3VGj0Ub6PS1U+J63mehtSdYvuo6nL97G6o7bFLI6qjRRpHso0Kjf39tRPS04bbF
XaEwgbZvqGnYxGtGCP4u5U+JRtR93KofJEf62wR3+inwGTz/R4JGTu1+Uh6sn4v3f5i7kinChIcw
AF84


--===============0316449491812282398==--