MariaDB and MYSQL5.6 returns different results

Question

I was trying to get the richest user in the table. This table consist of all the transaction and transaction time. The user's money in bank was determined by the latest value of bankbalance order by tstamp DESC

SELECT b.bankid,b.bankbalance,b.remark,b.tstamp 
  FROM (SELECT * FROM `bank` WHERE 1 ORDER BY tstamp DESC) AS b 
 WHERE 1 
group by warid 
ORDER BY bankbalance DESC LIMIT 0,10

the same query gives me different results.

5.6.27 - MySQL Community Server (GPL)

5.5.56-MariaDB - MariaDB Server

The table structure is here

CREATE TABLE `bank` (
  `bankid` int(20) NOT NULL,
  `warid` int(11) NOT NULL,
  `fbid` varchar(50) NOT NULL,
  `banknumber` varchar(20) NOT NULL,
  `gross` int(20) NOT NULL,
  `bankbalance` bigint(11) NOT NULL,
  `remark` varchar(12) NOT NULL,
  `tstamp` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `bank` (`bankid`, `warid`, `fbid`, `banknumber`, `gross`, `bankbalance`, `remark`, `tstamp`) VALUES
(24, 22, '10001', 'banknumber', 5, 5, '', 1477155284),
(25, 22, '10001', 'banknumber', 1, 6, '', 1477155591.617),
(26, 22, '10001', 'banknumber', 1, 7, '', 1477155726.2211),
(27, 22, '10001', 'banknumber', 1, 8, '', 1477155730.1399),
(28, 22, '10001', 'banknumber', 1, 9, '', 1477155736.8127),
(29, 22, '10001', 'banknumber', 1, 10, '', 1477155743.4662),
(30, 23, '13332', 'banknumber', 5, 5, '', 1477155830),
(31, 23, '13332', 'banknumber', 1, 6, '', 1477155839.9243),
(32, 23, '13332', 'banknumber', 1, 7, '', 1477155842.8865),
(33, 23, '13332', 'banknumber', 1, 8, '', 1477155845.4773);

Are you sure you pasted the exact query you are using? The one you pasted has a `group by warid` but `warid` isn't in the `select` statement. — Sam M, Mar 21 '18 at 04:30
yes, it's the query that I used. Please try it on Maria DB server if possible. thanks — Wils, Mar 21 '18 at 04:36
Newer versions of both MariaDB and MySQL throw away the `ORDER BY` in the derived table. MariaDB started doing that before MySQL, so that may be why you got it. The rational: "A derived table is an _unordered_ set, so any `ORDER BY` (without a `LIMIT`) is irrelevant." — Rick James, Mar 28 '18 at 16:01

Sam M · Accepted Answer · 2018-03-21T05:12:42.543

The feature that allows grouping by columns not in the SELECT portion of the query is a MySql extension. It would not be allowed in many other databases. According to the v5.6 MySql documentation (https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html):

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

The takeaway message from the documentation is that when you write the query as you have, you cannot influence the ordering, even if you add an ORDER BY clause. The result ordering is indeterminate.

Even if you were running two instances of the same database and version, you could very well end up with different results with the same query. The solution is to rewrite your query to meet an ANSI standard and explicitly define the grouping.

Your sql might look something like this:

SELECT b.warid, b.bankbalance
  FROM bank b
       INNER JOIN (SELECT warid, max(tstamp) AS tstamp FROM bank GROUP BY warid) b2
       ON (b.warid = b2.warid AND b.tstamp = b2.tstamp)

There are a number of other useful answers on Stackoverflow that go into detail for this same question. Here is one such question: MySQL wrong results with GROUP BY and ORDER BY.

your answer works, but how could you order by the timestamp DESC? Is it done automatically? — Wils, Mar 21 '18 at 07:31
If you want to order the result set, you'll need to explicitly put that into an order by clause at the end of the sql. Without an order by clause, it will sort by whatever rules the database engine has in place. — Sam M, Mar 22 '18 at 01:12

MariaDB and MYSQL5.6 returns different results

1 Answers1