Description:
An SQL SELECT DISTINCT statement executed flawlessly in multiple versions of MySQL but produces an error message in 5.7.16.
How to repeat:
To illustrate:
create table test.pronouns (
pronoun char(4) not null,
inorder tinyint(2) not null)
engine=MYISAM charset=latin1;
use test;
insert into pronouns (pronoun, inorder)
values ('he',6);
insert into pronouns(pronoun,inorder) values ('me',1);
insert into pronouns(pronoun,inorder) values ('you',2);
insert into pronouns(pronoun,inorder) values ('they',3);
insert into pronouns(pronoun,inorder) values ('them',4);
insert into pronouns(pronoun,inorder) values ('she',5);
insert into pronouns(pronoun,inorder) values ('me',1);
select distinct(pronoun) from pronouns order by inorder; <= this statement
works in 5.5, 5.6, etc., but in 5.7.16 comes up with an error:
Error Code: 3065. Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.pronouns.inorder' which is not in SELECT list; this is incompatible with DISTINCT
Suggested fix:
I don't know whether this is a bug or a correction -- were the previous versions of MySQL in error by correctly interpreting the intent of the SELECT query? I don't otherwise know a method to obtain the unique values of a column ordered by anything other than the column's values.