Bug #76947 Single-table scalar subquery with LIMIT and ORDER BY gives wrong results

• Submitted: 6 May 2015 7:31
• Modified: 13 Jul 2015 13:43
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.6.25
• OS: Any
• CPU Architecture: Any

[6 May 2015 7:31] Guilhem Bichot

Description:
Bug is in 5.6/5.7, not 5.5.
If a subquery has one table, identical GROUP BY and ORDER BY (on the same unique, not null, column), ordering is lost.

Introduced by
commit 07b1d30ea4aa041baa2f25251a153e3b96d29054
Date:   Fri Dec 17 10:41:21 2010 +0100
    WL#1393 Optimizing filesort with small limit

How to repeat:
CREATE TABLE `t1` (
  `t1_id` int PRIMARY KEY
);
insert into t1 values(1),(2),(3);
let $query= select * from t1 group by t1_id order by t1_id desc limit 1;
eval $query;
eval select ($query) from dual;

As standalone query, result is 3 (good), as subquery it is 1 (bad).

Suggested fix:
In sql_optimizer.cc:

      if (!order || test_if_subpart(group_list, order))
      {
        if (skip_sorting_for_orderby ||
            select_lex->master_unit()->item) // This is a subquery <<<<<<
          order= NULL;

the marked line should not be. It's true that sometimes a subq can skip ORDER BY but remove_redundant_subquery_clauses() already handles those cases.

[13 Jul 2015 13:43] Paul Dubois

Noted in 5.7.8, 5.8.0 changelogs.

If a single-table subquery had identical GROUP BY and ORDER BY 
clauses on a UNIQUE NOT NULL column, the results could be incorrectly
ordered.