Problem with aggregates within subqueries - Take 2

[Lee Bieber]

The following drizzle-automation query fails with Drizzle, passes with MySQL. Show stopper for us to use drizzle-automation

SELECT
 i.operation_name
, ((AVG(i.seconds) - agg.avg_seconds) / agg.avg_seconds) * 100 as avg
FROM bench_config c
NATURAL JOIN bench_runs r
NATURAL JOIN sqlbench_new i
INNER JOIN (
  SELECT
    operation_name
  , AVG(seconds) as avg_seconds
  FROM sqlbench_new iter
  WHERE run_id IN (611,610,609)
  GROUP BY operation_name
) AS agg
  ON i.operation_name = agg.operation_name
WHERE r.run_id = 611
GROUP BY i.operation_name
ORDER BY i.operation_name

% drizzle drizzle_stats < /tmp/test.sql
ERROR 1365 (22012) at line 19: Division by 0

% mysql -u drizzle --password=drizzle drizzle_stats < /tmp/test.sql
operation_name avg
alter_table_add 14.8936170213
alter_table_drop 12.8205128205
connect 0.0000000000
connect+select_1_row -20.0000000000
connect+select_simple -20.0000000000
count 0.0000000000
count_distinct 0.0000000000
count_distinct_2 0.0000000000
count_distinct_big 0.0000000000
count_distinct_group 0.0000000000
.......

[Lee Bieber (kalebral-deactivatedaccount)]

Data set to reproduce the issue

[Andrew Hutchings (linuxjedi)]

I believe this stemmed from this thread nearly 2 years ago:
http://<email address hidden>/msg02146.html

Basically divide by zero in MySQL just gives a NULL result. In Drizzle it was decided that divide by zero is an error. When agg.average_seconds is 0 this triggers a divide by zero condition causing an error.

The fix in this case is to change the where condition of the query, so that it is:

SELECT
 i.operation_name
, ((AVG(i.seconds) - agg.avg_seconds) / agg.avg_seconds) * 100 as avg
FROM bench_config c
NATURAL JOIN bench_runs r
NATURAL JOIN sqlbench_new i
INNER JOIN (
  SELECT
    operation_name
  , AVG(seconds) as avg_seconds
  FROM sqlbench_new iter
  WHERE run_id IN (611,610,609)
  GROUP BY operation_name
) AS agg
  ON i.operation_name = agg.operation_name
WHERE r.run_id = 611 AND agg.avg_seconds > 0
GROUP BY i.operation_name
ORDER BY i.operation_name

This should probably be discussed on the weekly call if you think this behavior should change to a warning or something.

[Lee Bieber (kalebral-deactivatedaccount)]

Changing the sql statement should be fine.