Problem with aggregates within subqueries
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Drizzle |
Fix Released
|
High
|
Andrew Hutchings | ||
7.0 |
Fix Released
|
High
|
Andrew Hutchings |
Bug Description
The following drizzle-automation query gets different results when using MySQL vs Drizzle. This is currently a show stopper for using Drizzle as our database in drizzle-automation
% mysql -u drizzle --password=drizzle drizzle_stats < /tmp/file.sql
operation_name seconds avg_seconds
alter_table_add 12.00 14.71
alter_table_drop 12.00 14.65
connect 2.00 1.98
connect+
connect+
count 13.00 15.94
count_distinct 5.00 6.51
count_distinct_2 12.00 13.07
count_distinct_big 21.00 21.81
count_distinct_
% drizzle --port=9306 drizzle_stats < /tmp/file.sql
operation_name seconds avg_seconds
alter_table_add 12.00 10000.00
alter_table_drop 12.00 10000.00
connect 2.00 10000.00
connect+
connect+
count 13.00 10000.00
count_distinct 5.00 10000.00
count_distinct_2 12.00 10000.00
count_distinct_big 21.00 10000.00
count_distinct_
% cat /tmp/file.sql
SELECT
i.operation_name
, ROUND(AVG(
, ROUND(agg.
FROM bench_config c
NATURAL JOIN bench_runs r
NATURAL JOIN sqlbench_
INNER JOIN (
SELECT
iter.
, AVG(seconds) as avg_seconds
FROM bench_config conf
NATURAL JOIN bench_runs runs
NATURAL JOIN sqlbench_
WHERE conf.name = 'sqlbench'
AND runs.server = 'drizzled'
AND runs.version LIKE 'staging%' GROUP BY iter.operation_name
) AS agg
ON i.operation_name= agg.operation_name WHERE r.run_id = 568
GROUP BY i.operation_name ORDER BY i.operation_name
limit 10
Related branches
- Drizzle Merge Team: Pending requested
-
Diff: 56 lines (+13/-9)2 files modifieddrizzled/field/decimal.cc (+9/-7)
tests/r/func_group.result (+4/-2)
Changed in drizzle: | |
assignee: | nobody → Andrew Hutchings (linuxjedi) |
Changed in drizzle: | |
importance: | Undecided → High |
milestone: | none → 2010-10-25 |
status: | New → In Progress |
Changed in drizzle: | |
status: | In Progress → Fix Committed |
Changed in drizzle: | |
status: | Fix Committed → Fix Released |
Adding data to reproduce the problem. The problem goes away if you try to reduce the data to any less than what is attached.