BUG#58456 - Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output

            in opt_range.h

In this bug, there are two alternative access plans: 
 * Index merge range access
 * Const ref access

best_access_path() decided that the ref access was preferrable, 
but make_join_select() still decided to point 
SQL_SELECT::quick to the index merge because the table had 
type==JT_CONST which was not handled. 

At the same time the table's ref.key still referred to the 
index the ref access would use indicating that ref access 
should be used. In this state, different parts of the 
optimizer code have different perceptions of which access path
is in use (ref or range).

test_if_skip_sort_order() was called to check if the ref access
needed ordering, but test_if_skip_sort_order() got confused and
requested the index merge to return records in sorted order. 
Index merge cannot do this, and fired an ASSERT.

The fix is to take join_tab->type==JT_CONST into concideration
when make_join_select() decides whether or not to use the 
range access method.

mysql-test/r/join_outer_innodb.result:
  Add test for BUG#58456
mysql-test/t/join_outer_innodb.test:
  Add test for BUG#58456

1 files changed, 35 insertions, 0 deletions

diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
index e8a2d6f668b..7f6d4fb8553 100644
--- a/mysql-test/r/join_outer_innodb.result
+++ b/mysql-test/r/join_outer_innodb.result
@@ -17,3 +17,38 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
+#
+# BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output
+#            in opt_range.h
+#
+CREATE TABLE t1 (
+col_int INT,
+col_int_key INT,
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4);
+INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5);
+CREATE TABLE t2 (
+pk INT PRIMARY KEY
+) ENGINE=InnoDB;
+
+EXPLAIN SELECT t1.pk
+FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
+WHERE t1.col_int_key BETWEEN 5 AND 6 
+AND t1.pk IS NULL OR t1.pk IN (5)
+ORDER BY pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY,col_int_key	PRIMARY	4	const	2	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.col_int	1	Using index
+
+SELECT t1.pk
+FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
+WHERE t1.col_int_key BETWEEN 5 AND 6 
+AND t1.pk IS NULL OR t1.pk IN (5)
+ORDER BY pk;
+pk
+
+DROP TABLE t1,t2;
+# End BUG#58456