MySQL Bugs: #47652: sub query and grouping results

Bug #47652	sub query and grouping results
Submitted:	25 Sep 2009 17:39	Modified:	25 Sep 2009 17:49
Reporter:	Gary Lawler	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1 comunity server	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When you use a subselect to sum a value with a group statement a problem occurs.
Below are two queries and the results. Both queries are identical with the exception of the group statement. The fifo column shown in the second result is incorrect. It appears that the result from the subselect is not being grouped.

mysql> SELECT stmov_brch,stmov_stockcode,SUM(stmov_qty) AS stock,SUM(stmov_qty*stmov_unitcost) AS cost,(SELECT SUM(stfifo_qty*stfifo_unitcost) AS fifotmp FROM stock_fifo WHERE stfifo_brch=stmov_brch AND stfifo_stockcode=stmov_stockcode) AS fifo FROM stock_movement WHERE stmov_date<='2009-9-25' AND stmov_brch='100' GROUP BY stmov_brch,stmov_stockcode ORDER BY stmov_brch,stmov_stockcode;
+------------+-----------------+---------+------------+------------+
| stmov_brch | stmov_stockcode | stock   | cost       | fifo       |
+------------+-----------------+---------+------------+------------+
|        100 |             259 |  59.000 |  295.01000 |  445.01000 |
|        100 |             260 |  50.000 |  500.00000 |  500.00000 |
|        100 |             261 | 651.000 | 6510.00000 | 6510.00000 |
|        100 |             262 | 100.000 | 1000.00000 | 1000.00000 |
|        100 |             263 | 200.000 | 2000.00000 | 2000.00000 |
|        100 |             264 | 100.000 | 1000.00000 | 1000.00000 |
|        100 |             265 |  50.000 |  500.00000 |  500.00000 |
|        100 |             284 |  90.000 | 3150.00000 | 3150.00000 |
|        100 |             285 | 190.000 | 6650.00000 | 6650.00000 |
|        100 |             286 |  89.000 | 3115.00000 | 3115.00000 |
|        100 |             287 |  40.000 | 1400.00000 | 1400.00000 |
|        100 |             291 | 418.000 | 2090.00000 | 1945.00000 |
|        100 |             296 | 227.000 | 1135.00000 | 1135.00000 |
|        100 |             297 |   6.000 |  600.00000 |  600.00000 |
|        100 |             298 |   2.000 |  200.00000 |  200.00000 |
|        100 |             315 | 100.000 | 1000.00000 | 1000.00000 |
|        100 |             316 | 200.000 | 2000.00000 | 2000.00000 |
|        100 |             317 | 300.000 | 3000.00000 | 3000.00000 |
+------------+-----------------+---------+------------+------------+
18 rows in set (0.00 sec)

mysql> SELECT stmov_brch,stmov_stockcode,SUM(stmov_qty) AS stock,SUM(stmov_qty*stmov_unitcost) AS cost,(SELECT SUM(stfifo_qty*stfifo_unitcost) AS fifotmp FROM stock_fifo WHERE stfifo_brch=stmov_brch AND stfifo_stockcode=stmov_stockcode) AS fifo FROM stock_movement WHERE stmov_date<='2009-9-25' AND stmov_brch='100' GROUP BY stmov_brch ORDER BY stmov_brch,stmov_stockcode;
+------------+-----------------+----------+-------------+------------+
| stmov_brch | stmov_stockcode | stock    | cost        | fifo       |
+------------+-----------------+----------+-------------+------------+
|        100 |             284 | 2872.000 | 36145.01000 | 3150.00000 |
+------------+-----------------+----------+-------------+------------+
1 row in set (0.00 sec)

mysql>
 

How to repeat:
mysql> SELECT stmov_brch,stmov_stockcode,SUM(stmov_qty) AS stock,SUM(stmov_qty*stmov_unitcost) AS cost,(SELECT SUM(stfifo_qty*stfifo_unitcost) AS fifotmp FROM stock_fifo WHERE stfifo_brch=stmov_brch AND stfifo_stockcode=stmov_stockcode) AS fifo FROM stock_movement WHERE stmov_date<='2009-9-25' AND stmov_brch='100' GROUP BY stmov_brch,stmov_stockcode ORDER BY stmov_brch,stmov_stockcode;
+------------+-----------------+---------+------------+------------+
| stmov_brch | stmov_stockcode | stock   | cost       | fifo       |
+------------+-----------------+---------+------------+------------+
|        100 |             259 |  59.000 |  295.01000 |  445.01000 |
|        100 |             260 |  50.000 |  500.00000 |  500.00000 |
|        100 |             261 | 651.000 | 6510.00000 | 6510.00000 |
|        100 |             262 | 100.000 | 1000.00000 | 1000.00000 |
|        100 |             263 | 200.000 | 2000.00000 | 2000.00000 |
|        100 |             264 | 100.000 | 1000.00000 | 1000.00000 |
|        100 |             265 |  50.000 |  500.00000 |  500.00000 |
|        100 |             284 |  90.000 | 3150.00000 | 3150.00000 |
|        100 |             285 | 190.000 | 6650.00000 | 6650.00000 |
|        100 |             286 |  89.000 | 3115.00000 | 3115.00000 |
|        100 |             287 |  40.000 | 1400.00000 | 1400.00000 |
|        100 |             291 | 418.000 | 2090.00000 | 1945.00000 |
|        100 |             296 | 227.000 | 1135.00000 | 1135.00000 |
|        100 |             297 |   6.000 |  600.00000 |  600.00000 |
|        100 |             298 |   2.000 |  200.00000 |  200.00000 |
|        100 |             315 | 100.000 | 1000.00000 | 1000.00000 |
|        100 |             316 | 200.000 | 2000.00000 | 2000.00000 |
|        100 |             317 | 300.000 | 3000.00000 | 3000.00000 |
+------------+-----------------+---------+------------+------------+
18 rows in set (0.00 sec)

mysql> SELECT stmov_brch,stmov_stockcode,SUM(stmov_qty) AS stock,SUM(stmov_qty*stmov_unitcost) AS cost,(SELECT SUM(stfifo_qty*stfifo_unitcost) AS fifotmp FROM stock_fifo WHERE stfifo_brch=stmov_brch AND stfifo_stockcode=stmov_stockcode) AS fifo FROM stock_movement WHERE stmov_date<='2009-9-25' AND stmov_brch='100' GROUP BY stmov_brch ORDER BY stmov_brch,stmov_stockcode;
+------------+-----------------+----------+-------------+------------+
| stmov_brch | stmov_stockcode | stock    | cost        | fifo       |
+------------+-----------------+----------+-------------+------------+
|        100 |             284 | 2872.000 | 36145.01000 | 3150.00000 |
+------------+-----------------+----------+-------------+------------+
1 row in set (0.00 sec)

mysql>

Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html:

"... all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."

This is what you get - values for columns from one of the rows in the group.