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.
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.