Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Because the vocabulary describing what I'd like to do can be so disparate, I haven't had any luck figuring out why MySQL 5.6 is doing what it does.

Consider the following two simple queries:

#1
select sum(amount) as amt,
    (sum(amount) * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50))) as cost
from gas_log gl
where date_format(gl.stamp,'%X')=2013;

#2
select sum(amount) as amt,
    (sum(amount) * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50))) as cost
from gas_log gl
where date_format(gl.stamp,'%X')=2013 group by yearweek(gl.stamp,0);

The second query is identical to the first, with a simple group_by to get the weekly totals instead of the yearly totals. Both queries have near-identical explain outputs:

+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | gl    | ALL  | NULL          | NULL | NULL    | NULL | 7428 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | gp    | ALL  | yw            | NULL | NULL    | NULL |   52 | Using where                                  |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+


+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | gl    | ALL  | NULL          | NULL | NULL    | NULL | 7428 | Using where |
|  2 | DEPENDENT SUBQUERY | gp    | ALL  | yw            | NULL | NULL    | NULL |   52 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

gas_log contains a timestamp of when gas was pumped and how much was pumped. gas_prices looks like this:

10:11:09> select * from gas_prices limit 2;
+------------+--------+-------+--------+
| date       | yw     | gas   | diesel |
+------------+--------+-------+--------+
| 2013-01-07 | 201301 | 3.235 |  3.870 |
| 2013-01-14 | 201302 | 3.265 |  3.834 |
+------------+--------+-------+--------+

For the first query, MySQL only executes the subquery once and uses that value (found by matching the first row retrieved from gas_log against its corresponding week in gas_prices) to multiply against the sum of all gallons logged in the gas log, whereas in the second query it does what I'm actually after: execute the subquery for each of the 52 grouped weeks in 2013, matching each week's gas price accordingly.

Using with rollup on the group by provides an interesting result; instead of using the first gas price in the timerange, it uses the last one! Thus, it still provides an incorrect cost total for any timerange spanning more than week.

Is there a way to rewrite the query to get MySQL to present one total for a given timerange, yet still match each gas_log row to its corresponding price in gas_prices?

share|improve this question
    
Some sample data with the expected results would be great. –  Mihai Jan 3 at 16:51
1  
Try with (sum(amount * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50)))) as cost in both queries. –  ypercube Jan 3 at 17:11
    
@ypercube, you are brilliant. This whole thing was victim to order of operations? Sure enough, simply using the sum of (amount * gas_price) convinces MySQL to behave properly--both using the plain sum and using rollup on the groupby. Can you tack on some kind of brief summary of why this works as an answer proper? I don't have quite enough rep to even upvote a comment on my own question yet. –  Eirik Jan 3 at 20:13
    
"...convinces MySQL to behave properly" It wasn't actually behaving improperly. You have to write a deterministic query to get a deterministic result, otherwise the server assumes that whatever you're asking for, it can give you via the cheapest path: "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate." ... the query should perhaps be written to materialize a derived table instead. –  Michael - sqlbot Jan 3 at 21:12

1 Answer 1

up vote 0 down vote accepted

(sum(amount * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50)))) as cost Is the answer, as provided by ypercube.

I was victim to order of operations--MySQL was taking the sum of gallons pumped, then multiplying that against the first/last result found from the gas prices sub query. As Michael quoted and noted:

"The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

So to get what I was after, the sum of each entry multiplied against the price of gas during that week, you simply wrap that exact expression in the comfortable embrace of a sum().

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.