MySQL Bugs: #71244: Wrong result computation using ALL() and GROUP BY

Bug #71244	Wrong result computation using ALL() and GROUP BY
Submitted:	30 Dec 2013 9:48	Modified:	4 Mar 2014 16:02
Reporter:	Ralf Adams	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6.15, 5.5.35, 5.1.72, 5.7.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALL(), GRPOUP BY, regression

Description:
I was migrating from 5.5.27 to 5.5.33 and after that to 5.6.15. By comparing the result sets of my scripts I got unexpected differences:

SELECT id1, SUM(value) AS `valuesum`
FROM
( -- creating a virtual table as data source
SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
UNION
SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
UNION
SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
UNION
SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
UNION
SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value'
) AS `values`
GROUP BY id1
HAVING valuesum > ALL(SELECT 6);

+-----+----------+
| id1 | valuesum |
+-----+----------+
|   1 |       33 |
|   2 |       28 |
+-----+----------+

I was expecting: 
+-----+----------+
| id1 | valuesum |
+-----+----------+
|   1 |       18 |
|   2 |       18 |
+-----+----------+

Using version 5.5.27 or "HAVING valuesum > 6" instead of ALL() leads to the correct result.

How to repeat:
SELECT id1, SUM(value) AS `valuesum`
FROM
( -- creating a virtual table as data source
SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
UNION
SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
UNION
SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
UNION
SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
UNION
SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value'
) AS `values`
GROUP BY id1
HAVING valuesum > ALL(SELECT 6);

Hello Ralf,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

// 5.6.15 affected

mysql> SELECT id1, SUM(value) AS `valuesum`
    -> FROM
    -> ( -- creating a virtual table as data source
    -> SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
    -> UNION
    -> SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
    -> UNION
    -> SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
    -> UNION
    -> SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
    -> UNION
    -> SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value'
    -> ) AS `values`
    -> GROUP BY id1
    -> HAVING valuesum > ALL(SELECT 6);
+-----+----------+
| id1 | valuesum |
+-----+----------+
|   1 |       33 |
|   2 |       28 |
+-----+----------+
2 rows in set (0.00 sec)

mysql> SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
    -> UNION
    -> SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
    -> UNION
    -> SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
    -> UNION
    -> SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
    -> UNION
    -> SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value';
+-----+-----+-------+
| id2 | id1 | value |
+-----+-----+-------+
|   1 |   1 |     3 |
|   2 |   1 |     6 |
|   3 |   1 |     9 |
|   4 |   2 |     8 |
|   5 |   2 |    10 |
+-----+-----+-------+
5 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.15-log |
+------------+
1 row in set (0.00 sec)

/// 5.1.72 -- affected

mysql> SELECT id1, SUM(value) AS `valuesum`
    -> FROM
    -> ( -- creating a virtual table as data source
    -> SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
    -> UNION
    -> SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
    -> UNION
    -> SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
    -> UNION
    -> SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
    -> UNION
    -> SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value'
    -> ) AS `values`
    -> GROUP BY id1
    -> HAVING valuesum > ALL(SELECT 6);
+-----+----------+
| id1 | valuesum |
+-----+----------+
|   1 |       33 |
|   2 |       28 |
+-----+----------+
2 rows in set (0.09 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.1.72-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

// 5.5.35 -- affected

mysql> SELECT id1, SUM(value) AS `valuesum`
    -> FROM
    -> ( -- creating a virtual table as data source
    -> SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
    -> UNION
    -> SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
    -> UNION
    -> SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
    -> UNION
    -> SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
    -> UNION
    -> SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value'
    -> ) AS `values`
    -> GROUP BY id1
    -> HAVING valuesum > ALL(SELECT 6);
+-----+----------+
| id1 | valuesum |
+-----+----------+
|   1 |       33 |
|   2 |       28 |
+-----+----------+
2 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.35-log |
+------------+
1 row in set (0.00 sec)

/// 5.1.63 - Not affected

mysql> SELECT id1, SUM(value) AS `valuesum`
    -> FROM
    -> ( -- creating a virtual table as data source
    -> SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
    -> UNION
    -> SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
    -> UNION
    -> SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
    -> UNION
    -> SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
    -> UNION
    -> SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value'
    -> ) AS `values`
    -> GROUP BY id1
    -> HAVING valuesum > ALL(SELECT 6);
+-----+----------+
| id1 | valuesum |
+-----+----------+
|   1 |       18 |
|   2 |       18 |
+-----+----------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.63    |
+-----------+
1 row in set (0.00 sec)

### Posgre results for this issue

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)

postgres=# SELECT id1, SUM(value) AS "valuesum"
FROM
(
SELECT 1 AS id2, 1 AS id1, 3 AS value
UNION
SELECT 2 AS id2, 1 AS id1, 6 AS value
UNION
SELECT 3 AS id2, 1 AS id1, 9 AS value
UNION
SELECT 4 AS id2, 2 AS id1, 8 AS value
UNION
SELECT 5 AS id2, 2 AS id1, 10 AS value
) AS values
GROUP BY id1 having SUM(value) > ALL(SELECT 6);
 id1 | valuesum 
-----+----------
   1 |       18
   2 |       18
(2 rows)

postgres=# SELECT id1, SUM(value) AS "valuesum"
FROM
(
SELECT 1 AS id2, 1 AS id1, 3 AS value
UNION
SELECT 2 AS id2, 1 AS id1, 6 AS value
UNION
SELECT 3 AS id2, 1 AS id1, 9 AS value
UNION
SELECT 4 AS id2, 2 AS id1, 8 AS value
UNION
SELECT 5 AS id2, 2 AS id1, 10 AS value
) AS values
GROUP BY id1;
 id1 | valuesum 
-----+----------
   1 |       18
   2 |       18
(2 rows)

// 5.7.3

mysql> select version();
+------------------------------------------+
| version()                                |
+------------------------------------------+
| 5.7.3-m13-enterprise-commercial-advanced |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT id1, SUM(value) AS `valuesum`
    -> FROM
    -> ( -- creating a virtual table as data source
    -> SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
    -> UNION
    -> SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
    -> UNION
    -> SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
    -> UNION
    -> SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
    -> UNION
    -> SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value'
    -> ) AS `values`
    -> GROUP BY id1
    -> HAVING valuesum > ALL(SELECT 6);
+-----+----------+
| id1 | valuesum |
+-----+----------+
|   1 |       33 |
|   2 |       28 |
+-----+----------+
2 rows in set (0.00 sec)

// If we remove HAVING clause then results expected one

mysql> SELECT id1, SUM(value) AS `valuesum`
    -> FROM
    -> ( -- creating a virtual table as data source
    -> SELECT 1 AS 'id2', 1 AS 'id1', 3 AS 'value'
    -> UNION
    -> SELECT 2 AS 'id2', 1 AS 'id1', 6 AS 'value'
    -> UNION
    -> SELECT 3 AS 'id2', 1 AS 'id1', 9 AS 'value'
    -> UNION
    -> SELECT 4 AS 'id2', 2 AS 'id1', 8 AS 'value'
    -> UNION
    -> SELECT 5 AS 'id2', 2 AS 'id1', 10 AS 'value'
    -> ) AS `values`
    -> GROUP BY id1
    -> ;
+-----+----------+
| id1 | valuesum |
+-----+----------+
|   1 |       18 |
|   2 |       18 |
+-----+----------+
2 rows in set (0.00 sec)

Noted in 5.6.17, 5.7.4 changelogs.

Aggregating the results of a subquery in the FROM clause could 
produce incorrect results.

5.6$ bzr log -r 5849 -n0
------------------------------------------------------------
revno: 5849
committer: Chaithra Gopalareddy <chaithra.gopalareddy@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-02-28 15:01:41 +0530
message:
  Bug#18014565: WRONG RESULT COMPUTATION USING ALL() AND GROUP BY
  
  Problem:
  Aggregation does not happen correctly because substitution for
  subquery is created with wrong item.
  
  Analysis:
  Currently while creating a substitution for ALL/ANY subqueries,
  optimizer uses "real_item" instead of the ref_item.
  If this happens to be a reference to an aggregate function
  then it would be creating the substitution with SUM_FUNC_ITEM
  rather than the REF_ITEM.
  
  If an aggregate function is present in having clause, optimizer
  calls split_sum_func2 to add the aggregate functions
  to the list of items in the select list.
  
  Ex: In the following query
  select f1, sum(f2) as sum from t1 group by f1 having sum >
                                                all (select 1);
  
  "sum" in having clause is a reference to sum(f2). So while
  creating the substitution we create using the real_item of
  "sum" which is sum(f2). In split_sum_func2 we add this
  "sum(f2)" to the item list. As a result the item list now
  becomes "sum(f2), f1, sum(f2)". This results in creation
  of three fields in tmp_table. But both the sum(f2)'s would
  be pointing to the same result_field. So, while aggregating
  same result is added twice.
  Before the fix for Bug#16095534, the type would remain
  REF_ITEM and thereby optimizer would not be adding "sum"
  to the select item list.
  
  Solution:
  The problem addressed in Bug#16095534 exists only for Item_ref
  objects created while resolving not for the ref objects created
  during parsing. So use real_item only for such items.

5.6$ bzr log -r 5850 -n0
------------------------------------------------------------
revno: 5850
tags: clone-5.6.17-build
committer: Chaithra Gopalareddy <chaithra.gopalareddy@oracle.com>
branch nick: mysql-5.6
timestamp: Mon 2014-03-03 15:58:21 +0530
message:
  
  Post push fix for Bug#18014565