MySQL 5.7 also has such a patch:

commit 989bddd0550c04ed2b00f07749fe3a9854f8818a

Author: Ole John Aske <ole.john.aske@oracle.com>

Date:   Wed Jan 25 15:37:43 2012 +0100

    Fix for bug#13529048 TEST_IF_SKIP_SORT_ORDER() DON'T HAVE TO FILESORT A SINGLE ROW.

    test_if_skip_sort_order() should catch the jointypes

    JT_EQ_REF, JT_CONST and JT_SYSTEM and skip sort order for these.

    Didn't add a specific testcase for this bug as it should be

    covered by existing testcases which now will skip 'Using filesort'

psergei, please review bb-10.4-mdev-32475.

Hi Oleg!

It is true that MDEV-23596 technically covers this fix but after a while it will be completely not obvious that that test is also a test for some other bug which relies on some EXPLAIN in the subquery. So please,

• copy the testcase, it's small.
• make a {{ -echo # MDEV ... }} heading
• add a comment explaining what we expect to see in the EXPLAIN:

  --echo # Table t1 must use eq_ref, not index below:

• make table t1 (but not t2) larger (about 100 rows), and use ANALYZE TABLE ... PERISISTENT FOR ALL also.
  *

Make first line of the he commit comment shorter:

 MDEV-32475 test_if_skip_sort_order() should catch the join types JT_EQ_REF, JT_CONST and JT_SYSTEM and skip sort order for these

is too long. Something like this:

MDEV-32475: Skip sorting if we will read one row

The original line can go into the detailed comment.

Btw, normally, we don't need need to skip sorting like this. If you do

select * from t1 where pk=1 order by pk;

you will see that the optimizer has figured it's order-by-constant and has removed the ORDER BY.
But here we have an outer reference so ORDER BY removal didn't work.

Addressed your comments, force-pushed to the same branch

Ok to push.

Pushed to 10.4

oleg.smirnov, please check what happens in the non-unique case and perhaps file an MDEV...

Note for the changelog:

Fixed optimization of subqueries in form (SELECT col FROM t1 WHERE unique_col=ouside_ref ORDER BY .. LIMIT 1) . Equality on unique column means one row will be produced so any sorting is unnecessary (and the question whether to do sorting or use a matching index is irrelevant).

The same test case, but now table t1 doesn't have a PRIMARY KEY, only a non-unique indexes on fields "a" and "b":

CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b));

CREATE TABLE t2 (a INT, b INT);

INSERT INTO t1 SELECT seq, seq+1 FROM seq_1_to_100;

INSERT INTO t2 VALUES (0, 1),(1, 2);

ANALYZE TABLE t1, t2 PERSISTENT FOR ALL;

EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	

2	DEPENDENT SUBQUERY	t1	ref	a	a	5	test.t2.b	1	Using where; Using filesort

We can see that "ref" access is not substituted in test_if_skip_sort_order(). psergei, I believe it's the correct behaviour?