MySQL Bugs: #81342: Using ONLY_FULL_GROUP_BY rejects statements in MySQL 5.7 but not in MySQL 5.6

Bug #81342	Using ONLY_FULL_GROUP_BY rejects statements in MySQL 5.7 but not in MySQL 5.6
Submitted:	6 May 2016 22:22	Modified:	26 Jun 2018 2:40
Reporter:	monty solomon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.11	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
The documentation for ONLY_FULL_GROUP_BY in MySQL 5.7 seems to imply that it is more flexible than it was in MySQL 5.6 but queries that work with ONLY_FULL_GROUP_BY in MySQL 5.6 fail in MySQL 5.7.

https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

Implementation for the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, this mode is now enabled by default, to prohibit only nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.

How to repeat:
Execute this query on MySQL 5.6 using the SQL Mode "ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

select distinct dayStr as timeDimension from time_dimensions where utfId between 1397236500000 and 1398964500000 and timeZone='Etc/UTC' order by utfId

Execute the same query on MySQL 5.7 using the same SQL Mode

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'BroadcastApi.time_dimensions.utfId' which is not in SELECT list; this is incompatible with DISTINCT

HI Monty,

This is not related to GROUP BY, as you do not have any, but at the combo of DISTINCT and ORDER BY.

However, this is still a bug , but in documentation.

Verified as documentation bug.

FYI, I see this paragraph in the blog post about improvements in only_full_group_by.

There was one last fix done as part of this Worklog. That is having to do with some rare non-grouped queries of the form SELECT DISTINCT ... ORDER BY also being non-deterministic, but this post is already long so I’ll discuss that in a subsequent post. Stay tuned!

http://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-de...

That paragraph referred to "WL#2489: Better ONLY_FULL_GROUP_BY mode"
http://dev.mysql.com/worklog/task/?id=2489

Implement F-6: make it apply some restrictions to DISTINCT + ORDER BY
=====================================================================

in a query with DISTINCT and ORDER BY, we can get random order:
select distinct a from t1 order by b;
during execution, depending on the query plan, we may apply DISTINCT before
ORDER BY; when we apply DISTINCT we may pick a value of 'b' in the group of rows
which have the same value of 'a'. That gives random order of the final result.
This was filed as:
Bug #13581713 ONLY_FULL_GROUP_BY DOES NOT BLOCK "SELECT DISTINCT A ORDER BY B".
We see that this is a problem which is similar in nature to what
only_full_group_by wants to fight. Thus, we should make only_full_group_by add
restrictions on DISTINCT + ORDER BY. The proper restrictions are described in
the standard: if a query has ORDER BY, and one expression in ORDER BY is not
identical to some expression in the SELECT list, and involves columns of  tables
of the FROM clause, then the query should not have DISTINCT.
This worklog will fix Bug #13581713 .

http://bugs.mysql.com/bug.php?id=83978 marked as duplicate of this one.

Posted by developer:
 
Conditions for rejection of DISTINCT + ORDER BY queries when ONLY_FULL_GROUP_BY is enabled are given at:
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html