0

I have a SQL query that was working fine until we upgraded MySQL from version 5.5 to version 5.7. The SQL query should return the highest value of tx_id for each user. I don't understand why this query should return a different result now that MySQL has been upgraded. Am I using some quirk of SQL that returns values in an unspecified order (could "GROUP BY" be the culprit here?). I would like to understand what it is that I am doing that produces a different result.

This is my query:

SELECT t.tx_id FROM (
    SELECT tx_id, NAME, ACTION 
    FROM employee 
    ORDER BY tx_id DESC) t 
GROUP BY t.name

When running on version 5.5 the following is returned (correctly):

4 adam
6 bob

When running on version 5.7 however the following is returned: 

3 adam
4 bob

And here is some sample data and the table definition if you wish to try it out:

CREATE TABLE `employee` (
  `tx_id` int(10) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `action` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `employee` */
insert  into `employee`(`tx_id`,`name`,`action`) values 
(3,'adam',1),
(4,'adam',2),
(4,'bob',1),
(5,'bob',2),
(6,'bob',3);
Phil
  • 1,897
  • 4
  • 25
  • 48
  • 2
    You are selecting a column that is not part of the group, which is illegal in SQL; MySQL tolerates this and gives you the column value from a _random_ record in the group if configured accordingly, https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by – CBroe Nov 27 '17 at 10:28
  • 2
    *"When running on version 5.5 the following is returned (correctly)"* -- the query is invalid SQL. Before version 5.7.5, MySQL used to accept it but it reserved its right to return indeterminate values. By chance, it just happened that it picked the values you consider as "correct". It can return different values even on MySQL 5.5 if you export the data and import it on a different server. – axiac Nov 27 '17 at 10:28
  • The `ORDER BY` on the inner query doesn't matter. – axiac Nov 27 '17 at 10:30
  • @CBroe ok I follow what you are saying but I want to output the highest tx_id and the name for that tx_id. So how can I change my query to return the highest value? Should I be using MAX ? – Phil Nov 27 '17 at 10:35
  • @axiac You said "The ORDER BY" doesn't matter. I find that (on MySQL 5.5) that if I make the order by ascending then indeed it does take effect. – Phil Nov 27 '17 at 10:36
  • 1
    It is just a coincidence. As you can see, on version 5.7 it doesn't help any more. And this happens because the query is not valid and it cannot produce a unique result set. Read here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – axiac Nov 27 '17 at 10:40
  • 1
    *"The SQL query should return the highest value of `tx_id` for each user."* -- A correct query that produces the results you need could be `SELECT name, MAX(tx_id) FROM employee GROUP BY name` but it cannot return `action`. This happens because `GROUP BY` doesn't not returns rows from the tables. It **generates** the rows it puts into the resultset, one column at a time. – axiac Nov 27 '17 at 10:52

0 Answers0