Hi!

avillaci> Description: avillaci> When a query is made in the form: avillaci> select table_a.name, sum(table_b.value) as sum_values from table_a
left avillaci> join table_b on table_a.a_key = table_b.a_key group by table_b.a_key avillaci> and table_b happens to be empty, the result set incorrectly contains
only the avillaci> first row of table_a. It should contain at least as many rows as there
are on avillaci> table_a, with the group function (in this case, 'sum()'), evaluated to
the avillaci> appropriate value for a NULL field. <cut>

I have now looked more closely at this problem.

avillaci> delete from table_b; /* Notice, table_b is now empty */ avillaci> select table_a.name, table_b.value from table_a left join table_b on avillaci> table_a.a_key = table_b.a_key; /* Seems to work OK */ avillaci> select table_a.name, sum(table_b.value) as sum_values from table_a
left join avillaci> table_b on table_a.a_key = table_b.a_key group by table_b.a_key; avillaci> /* avillaci> The previous query should return: avillaci> +-------+------------+ avillaci> | name | sum_values | avillaci> +-------+------------+ avillaci> | Alice | 0 | avillaci> | Bob | 0 | avillaci> | Carol | 0 | avillaci> +-------+------------+

Let's see what you are grouping on:

mysql> select table_a.name, table_b.a_key from table_a left join table_b on
table_a.a_key = table_b.a_key; +-------+-------+ | name | a_key | +-------+-------+ | Alice | NULL | | Bob | NULL | | Carol | NULL | +-------+-------+

If you now do a group on a_key, you will only get one row in the result, as all group elements are NULL.

(This is the same things as "Ord Millar" answered in an earlier reply).

Regards, Monty