inconsistent distinct + order by behavior between mysql 5.6 and 5.7

I use MySQL 5.6 and 5.7 on Ubuntu 16.04.3 LTS and I have a problem with the following query (which is derived from drupal 7 but have simplified it in order to pinpoint the issue):

select distinct node.nid, node.title, users.name
  from node
  left join users on node.uid = users.uid
  order by node.nid desc
  limit 4;

My servers are provisioned with the exact same ansible script which uses the following my.cnf:

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 0.0.0.0
key_buffer_size         = 16M
max_allowed_packet      = 1G
thread_stack            = 192K
thread_cache_size       = 8
query_cache_type        = 0
slow_launch_time=1
innodb_buffer_pool_size = 1G
innodb_file_per_table
open-files-limit=10000
log_error = /var/log/mysql/error.log
long_query_time=2
slow_query_log=on
slow_query_log_file=/var/log/mysql/mysql-slow-queries.log
general_log=off
general_log_file=/var/log/mysql/general.log
innodb_flush_log_at_trx_commit=0

I import my database and then connect to CLI using:

echo "create database drupal" | mysql -u root -ppass
mysql -u root -ppass drupal < /vagrant/2017-10-13--08-00-backup-xxxx-db.sql
mysql -u root -ppass drupal -A

On servers with 5.6 (show variables like 'version'; gives me 5.6.16-1~exp1) the query returns the following rows which are in the correct descending order (note: used some x characters to anonymize dataset):

```
+-------+------------------------------------------+------+
| nid | title | name |
+-------+------------------------------------------+------+
| 56217 | A Coxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | ztex |
| 56216 | Estxxxxxxx

We don't get to say this often but in this case:

If this happens, it's a bug.

A query that has:

SELECT
  .. DISTINCT 
  ..
ORDER BY node.nid DESC
LIMIT 4 ;

should apply the ORDER BY and LIMIT after the SELECT - whether it's a SELECT DISTINCT or not - and return the rows with the highest four nid values in descending order. In the 5.7.20 case, it seems that it doesn't and the ORDER BY is ignored (or it runs as ASC), and the rows are returned with ascending order, so it must be a bug.

The right thing to do in these cases, is to report the bug with a sample test case, to MySQL bug list.

Things you could also do - to confirm, make it easier for MySQL developers to fix, and remedy the issue until the bug is fixed - is to try:

• remove the DISTINCT, seems useless anyway. The node.nid is unique since it's the primary key and the join is on user.uid which is the primary key of the other table, so the distinct will have no effect.

• remove the DISTINCT and add GROUP BY node.nid, node.title, users.name or just GROUP BY node.nid, users.name.

• remove the ONLY_FULL_GROUP_BY setting.

• try the query in older minor versions (5.7.19, 5.7.18, ...)

And please include the findings (if the bug persists with the above tries) in your bug report to MySQL - to help them identify the cause of the problem. I suspect it's a regression due to some optimization improvement related to GROUP BY.

StackExchange Database Administrators Q#191787, answer score: 3