Bug #119605 GROUP BY brings unnecessary order columns

• Submitted: 29 Dec 9:37
• Modified: 29 Dec 13:00
• Reporter: huahua xu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.27
• OS: Any
• CPU Architecture: Any

[29 Dec 9:37] huahua xu

Description:
If ORDER BY is a prefix of GROUP BY and if windowing or ROLLUP doesn't change this order, ORDER BY can be removed and the optimizer enforce GROUP BY to provide order. But the heuristic rule also brings unnecessary order columns.

How to repeat:
mysql> CREATE TABLE t4 (`c1` int NOT NULL, `c2` int DEFAULT NULL, `date1` datetime DEFAULT NULL);

mysql> explain format=tree select c1, c2+1 as  c3 from t4  group by c3,c1 order by c3;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: c3, t4.c1
    -> Table scan on <temporary>  (cost=0.37..2.59 rows=7)
        -> Temporary table with deduplication  (cost=2.02..4.24 rows=7)
            -> Table scan on t4  (cost=0.95 rows=7)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As for the query plan information mentioned above, the order list should not include the column `t4.c1`

[29 Dec 9:55] Chaithra Marsur Gopala Reddy

Hi huahua xu,

Thank you for the test case. Verified as described.

[29 Dec 13:00] huahua xu

Suggested fix: 
When group by has to respect ordering, move group columns which are Requested direction to order list.

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 97da519..b1bacfd 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4515,12 +4515,23 @@ bool JOIN::make_tmp_tables_info() {
                                     order by.
                                   */
       if (order.empty() && !skip_sort_order) {
+        assert(order.order == nullptr);
+        ORDER *prev = nullptr;
+
         for (ORDER *group = group_list.order; group; group = group->next) {
-          if (group->direction != ORDER_NOT_RELEVANT) {
-            order = group_list; /* order by group */
-            break;
+          if (group->direction != ORDER_NOT_RELEVANT) { /* order by group */
+            if (prev == nullptr) {
+              order = group_list;
+              order.order = group;
+            } else {
+              prev->next = group;
+            }
+
+            prev = group;
           }
         }
+
+        if (prev != nullptr) prev->next = nullptr;
       }
       group_list.clean();
     }