Details
Major Description
AVG() function reports incorrect query results in SELECT.
Optimizer Switch configuration:
show variables like '%optimizer_switch%';
|
|
|
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on
|
Setup:
CREATE TABLE `avg_calc_test` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`name` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; |
|
|
INSERT INTO avg_calc_test (name) values ('ORANGE'), ('BLUE'), ('BLACK'), ('ORANGE'), ('BLACK'); |
Test case exhibiting expected result:
-- correct result 2.00
|
SELECT |
AVG(CASE WHEN name = 'orange' THEN 3 WHEN name = 'blue' THEN 2 WHEN name = 'black' THEN 1 ELSE 0 END) AS metric_val |
FROM avg_calc_test; |
Test cases exhibiting incorrect results:
--Incorrect result 0.99
|
SELECT * FROM ( |
SELECT |
AVG(CASE WHEN name = 'orange' THEN 3 WHEN name = 'blue' THEN 2 WHEN name = 'black' THEN 1 ELSE 0 END) AS metric_val |
FROM avg_calc_test |
) T;
|
|
|
SET optimizer_switch="split_materialized=off"; |
--Incorrect result 0.99
|
SELECT * FROM ( |
SELECT |
AVG(CASE WHEN name = 'orange' THEN 3 WHEN name = 'blue' THEN 2 WHEN name = 'black' THEN 1 ELSE 0 END) AS metric_val |
FROM avg_calc_test |
) T;
|