MySQL Bugs: #82334: unexpected order for grouped query

Bug #82334	unexpected order for grouped query
Submitted:	25 Jul 2016 10:58	Modified:	16 Dec 2016 14:00
Reporter:	Tobias Weber	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.13	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Unexpected Result Order for grouped query with two joined tables

How to repeat:
1. Create Test-Tables:

CREATE TABLE `Sub1` (
  `Col` double NOT NULL,
  `Ref` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `Sub1` (`Col`, `Ref`) VALUES
(10, 1),
(100, 2);

CREATE TABLE `Sub2` (
  `Col` text NOT NULL,
  `Ref` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Sub2` (`Col`, `Ref`) VALUES
('def', 3),
('xyz', 2),
('abc', 1);

CREATE TABLE `Main` (
  `PK` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Main` (`PK`) VALUES
(1),
(2),
(3);

2. Execute Statement

select `Sub2`.`Col`, AVG(`Sub1`.`Col`) from `Main` left outer join `Sub2` on (`Main`.`PK` = `Sub2`.`Ref`) left outer join `Sub1` on (`Main`.`PK` = `Sub1`.`Ref`) group by `Sub2`.`Col`  ORDER BY AVG(`Sub1`.`Col`)  ASC;

=>

Result: 

abc	10
xyz	100
def	NULL

Expected:

def	NULL
abc	10
xyz	100

Statement with DESC as sort direction results in same like with ASC

Hello Tobias,

Thank you for the report and test case.

Thanks,
Umesh

This looks like a duplicate of this bug: https://bugs.mysql.com/bug.php?id=79366 ("ORDER BY does not work correctly with grouped AVG() values extracted from JSON" -- misleading title as it's not a JSON issue)

The culprit is the AVG() aggregation plus the TEXT column. It works fine with SUM(), for example, and/or with VARCHAR in my testing.

Bug was fixed in 5.17.7

5.7.17...