Distinct query returns different results on different servers

Hi everybody,

I have encountered a problem for which I can see no solution, and I really hope someone here can shed some light on this issue.

All of this is from memory since I'm not at work at the moment, but I'll try to fill in as much detail as possible.

There are three servers, each running MySQL (one runs 5.0, one I don't know and one is running 5.6.16). Each server has the exact same database, with the exact same tables and records.

There's also a page-long query which fetches data from a table (models) left-outer-joined three times on the same table (parameters), which looks like this (pseudo-code):

select distinct m.model, m.article, m.otherfields,
p1.parmeterno, p1.parametercode,
p2.parmeterno, p2.parametercode,
p3.parmeterno, p3.parametercode
from models m
left outer join parameters p1 on p1.model = m.model and <restriction1>
left outer join parameters p2 on p2.model = m.model and <restriction2>
left outer join parameters p3 on p3.model = m.model and <restriction3>
where <conditions>

Code (markup):

As you can see, this fetches model records for a certain set of conditions together with three different parameters for said model. So far so good.

On server 1, this query returns 488 records. On server 2, 488 records. This is the correct (and verified) result.

On server 3 (the 5.6.16) however, the query returns 34 records, even though both data and sql are the same!

If I remove p1.* from the select statement, I get more records. Even more if I remove p2.*, and if I remover the last p3.*, I get 488 records, just like the other servers do.

Now it gets really weird imho - if I execute the query, I get 34 records. If I then add "and model = x" to the conditions and I specify a model which does NOT exist in those 34 records but does exist in the remaining (from the 488 records the others return), I get exactly one record for that model!!

I am completely baffled by this - how can I select a record by adding a condition, when that record did not exist in the greater resultset??

The only difference I found between those servers is the character set - the 5.6.16 is a UTF8 database, the others are latin1. Could this be the cause?

What am I missing? Do I use "distinct" incorrectly? Is there a variable which specifies the behaviour of "distinct" queries?

We've even installed a fourth MySQL instance on a developer machine (also 5.6.16), which also gives 34 records.

If anyone has any ideas or suggestions, I'd really appreciate hearing them as I haven't go a clue how to solve this...

Thanks very much, and sorry for the long post.

Marlon

 

Hi, imho this is the reason. Set named pipe before query should do the fix.

set names utf8;

Code (markup):

Regards

 

Hi Koko5,

Thank you for the suggestion!

In fact, turns out we were hitting a bug where an incorrect number of records is fetched from the database if you combine select count distinct with a group by, order by and a limit in your query.

After rewriting the query without the group by, we got the results we expected.

Thanks again!

Marlon