Missing values in a query

Question

I encounter some strange results in the following query :

SET @indi_id = 768;
SET @generations = 8;

SELECT num, sosa, seq, len, dernier, ful_ful_nom 
FROM fullindi 
LEFT JOIN lignee_new 
ON ((ful_indi_id = dernier) AND (len BETWEEN 1 AND @generations)) 
RIGHT JOIN numbers 
ON ((sosa = num) AND (premier = @indi_id)) 
WHERE num BETWEEN 1 AND pow(2, @generations) 
GROUP BY num 
ORDER BY num;

The result looks like this :

Why the row just before a full NULL one doesn't display the existing values 'sosa', 'len', 'dernier', ful_ful_nom') but only the 'seq' value (see rows 43 and 47 in this example) ?

What am I missing?

As requested, here are data :

table lignee_new :

table fullindi :

Agree, need to see the data, but it is in the right or left join that leaves the nulls. — Dr. Aaron Dishno, Mar 31 '16 at 13:22
Another possibility, the where clause could cut out data where there is still a NUM field keeping the row. — Dr. Aaron Dishno, Mar 31 '16 at 13:24
The numbers table is just a one-column table containing values from 1 to 100000. — mlh, Mar 31 '16 at 13:52
Sounds like problem with group by, and non-aggregated columns: see [mysql docs](https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html) or this [SO question](http://stackoverflow.com/q/1066453/985375) — lp1051, Mar 31 '16 at 14:20

score 1 · Accepted Answer · answered Mar 31 '16 at 14:19

The problem is that MySQL does really dumb things when an Aggregate function is introduced, or a GROUP BY is included, but not all of the fields are in an Aggregate Function or your GROUP BY.

You are asking it to GROUP BY num but none of the other columns in your SELECT are included in the Group BY nor are they being aggregated with a function (SUM, MAX, MIN, AVG, etc..)

In any other RDBMS this query wouldn't run and would throw an error, but MySQL just carries on. It uses the logic to decide which value it should show for each field that isn't num by just grabbing the first value it finds in it's data storage which may be different between innoDB and whatever else folks use anymore.

My guess is that in your case you have more than one record in lignee_new that has a num of 43. Since you GROUP BY num and nothing else, it just grabs values randomly from your multiple records where num=43 and displays them... which is reasonable. By not including them in an aggregate function you are pretty much saying "I don't care what you display for these other fields, just bring something back" and so MySQL does.

Remove your GROUP BY clause completely and you'll see data that makes sense. Perhaps use WHERE to further filter your records to get rid of nulls or other things you don't need (don't use GROUP BY to filter).

Your answer helped me a lot. I solved the problem by adding another column in the GROUP BY. I'll retain the lesson. Thanks a lot. — mlh, Mar 31 '16 at 14:28
I'm glad it solved the problem! There's a lot I don't like about MySQL, but this one thing is at the very top of the list. It causes so much confusion. — JNevill, Mar 31 '16 at 14:30

Missing values in a query

1 Answers1