Incorrect "Sort aborted" when GROUP/ORDER expression contains subquery

[Timour Katchaounov]

The following test case produces an incorrect "Sort aborted" warnig
when subquery execution in the GROUP/ORDER clause produces
the "Subquery returns more than 1 row" error.

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (2), (4), (1), (3);

CREATE TABLE t2 (b int, c int);
INSERT INTO t2 VALUES
  (2,1), (1,3), (2,1), (4,4), (2,2), (1,4);

--error ER_SUBQUERY_NO_1_ROW

SELECT a FROM t1 GROUP BY a HAVING (SELECT b FROM t2 WHERE b > 1) > 3;

Analysis:
- the optimizer no longer evaluates subqueries during optimization,
  thus we don't know till execution time if a subquery will actually
  return >1 rows.
- the plan chosen for this query uses filesort:
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Using temporary; Using filesort |
| 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
- During filesort execution evaluates the subquery and finds out it returns
  more than one row. The call chain is:

  JOIN::exec -> create_sort_index -> filesort -> find_all_keys ->
  SQL_SELECT::skip_record -> Item::val_int -> ...
  subselect_single_select_engine::exec -> JOIN::exec -> ...
  evaluate_join_record -> end_send -> select_singlerow_subselect::send_data

  The send_data call detects that there are >1 rows, and issues an error.

- When filesort gets an error from find_all_keys, it treats it as a
  generic error, jumps to the "err:" tag, and reports "Sort aborted".
- Thus the error: "Subquery returns more than 1 row" is re-mapped to
   "Sort aborted".

[Timour Katchaounov (timour)]

Counter example by Sergey that the same problem exists in 5.3,
and therefore this is not a bug.

create table t1( a int);
insert into t1 values (1),(2);
create table t2 as select * from t1;

select * from t1 where a > (select a+20 from t2 where t2.a +100 > t1.a +10) order by a+1;

ERROR 1242 (21000): Subquery returns more than 1 row
110202 17:53:58 [ERROR] mysqld: Sort aborted

[Timour Katchaounov (timour)]

Agreed that this is not a bug, as shown by the above example.