MySQL Different results from same query/data

Question

I have two servers running MySQL. Both are on windows. One, is my local machime (Windows 7, MySQL 5.6.25, 32bit) and the other is my production webserver (Windows 2012, MySQL 5.7.11-log, 64bit (that's what show variables showed me).

The data is identical between the two. I backed the data up from the windows 7 (using MySQL Workbench) and restored it on the 2012 machine.

I am running a query on both machine but I am getting different results. I have two tables, projects and projectsnotes with a 1:m relationship between them related on projects.id to projectsnotes.idProject. Each note is marked with a date (dComment). The goal of the query is to retrieve project information and the latest comment only.

Here's the query:

select space(1) as cAction,
    p.id,
    p.iNum,
    p.cStatus,
    p.cName,
    p.cDesc,
    ifnull(pl.cNickName, 'UNASSIGNED') as cProjectLeader,
    IFNULL(concat (
            date_format(pn.dComment, '%Y-%m-%d'),
            ': ',
            pn.cComment
            ), '') as cComment,
    date_format(p.dRequested, '%Y-%m-%d') as dRequested,
    date_format(p.dRequired, '%Y-%m-%d') as dRequired,
    format(p.nPercentComplete, 2) as nPercentComplete,
    p.tLastUpdated,
    p.bCompleted,
    p.idProjectLeader
from projects p
left outer join projectleaders pl on p.idProjectLeader = pl.id
left outer join (
    select idProject,
        dComment,
        cComment
    from projectnotes
    order by dComment desc,
        tLastUpdated desc
    ) pn on p.id = pn.idProject
where p.cInstallCode = 'ITM'
    and cStatus in ('Pending', 'Active', 'On Hold', 'Completed', 'Cancelled')
    and bCompleted = 0
group by iNum
order by iNum;

Now, here's the weird part. When I run this on my Windows 7 machine, I get the right value for cComment. Specifically:

2017-03-28: Text from note replace

That is the latest note. When I run it on the 2012 server:

2016-05-17: Text from this note replaced too

If I run the subquery alone on the 2012 server, I get the right values (namely, a list of all the notes in the reverse order.

Oh, and this note is neither the first nor the last in the notes for this project.

So I am honestly wondering what is going on. Any thoughts on this would be greatly appreciated.

Thanks in advance.

Answer 1

This is expected behavior.

select ...
from projects p
left outer join projectleaders pl on p.idProjectLeader = pl.id
left outer join (...) pn on p.id = pn.idProject
where ...
group by iNum
order by iNum;

Due to MySQL's peculiar handling of GROUP BY, it will not report an error on this query. However, you must keep in mind that, since you use no aggregates, and the GROUP BY will eliminate lots of rows, the rows that are kept in the final result set are determined by rather obscure criteria...

For example:

SELECT a,b FROM t GROUP BY a

Which b will be returned? In some MySQL versions, this will be the first value of b that is found in table t. If table t is ordered in a certain way, this can be exploited. But I would definitely not trust that behavior to stay unchanged between versions... Also, remember MySQL is free to change your join order...