MySQL Bugs: #23732: ROLLUP does not NULLify superaggregated columns

Bug #23732	ROLLUP does not NULLify superaggregated columns
Submitted:	27 Oct 2006 18:12	Modified:	30 Oct 2006 20:51
Reporter:	Carlos Uldérico Cirello Filho	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.21	OS:	Linux (Fedora Core 5)
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY ROLLUP subquery view

Description:
MySQL does not NULLify supperaggregated rows when using subqueries views' alias in GROUP BY clausules.

How to repeat:
SELECT
'lançamento' as type_commercial,
(SELECT apelido FROM collectors cl_lea WHERE cl.leadered = cl_lea.id_user) as leadered,
sum(IF(first_contact BETWEEN NOW() - INTERVAL 31 DAY AND NOW(),1,0)) as total

FROM
search_customer as cu
INNER JOIN collectors cl ON (cu.id_commercial = cl.id_user),
customer_indicator as ci

WHERE
cu.id_indicator = ci.id_indicator AND
status = '0' AND
cu.id_indicator <> '10' AND
id_commercial IN (SELECT id_user FROM entering_collectors)

GROUP BY
type_commercial, leadered WITH ROLLUP
+-----------------+-------------------+-------+
| type_commercial | leadered          | total |
+-----------------+-------------------+-------+
| lançamento      | Anderson Gottardi |   175 |
| lançamento      | Alberto Yoshida   |     3 |
| lançamento      | Alberto Yoshida   |   178 |
| NULL            | Alberto Yoshida   |   178 |
+-----------------+-------------------+-------+
4 rows in set, 1 warning (1.31 sec)

The last line should be:

| NULL            | NULL              |   178 |

Suggested fix:
WORKAROUND.

Rewrite query so as to avoid the usage of subquery views.

SELECT
'lançamento' as type_commercial,
cl_lea.apelido as leadered,
sum(IF(first_contact BETWEEN NOW() - INTERVAL 31 DAY AND NOW(),1,0)) as total

FROM
search_customer as cu
INNER JOIN collectors cl ON (cu.id_commercial = cl.id_user)
LEFT OUTER JOIN collectors cl_lea ON (cl.leadered = cl_lea.id_user),
customer_indicator as ci

WHERE
cu.id_indicator = ci.id_indicator AND
status = '0' AND
cu.id_indicator <> '10' AND
id_commercial IN (SELECT id_user FROM entering_collectors)

GROUP BY
type_commercial, leadered WITH ROLLUP

+-----------------+-------------------+-------+
| type_commercial | leadered          | total |
+-----------------+-------------------+-------+
| lançamento      | Alberto Yoshida   |     3 |
| lançamento      | Anderson Gottardi |   175 |
| lançamento      | NULL              |   178 |
| NULL            | NULL              |   178 |
+-----------------+-------------------+-------+
4 rows in set (1.36 sec)

Thank you for the report.

Please try using current version accessible from dev.mysql.com/downloads/mysql/5.0.html and say us result.

Upgraded to 5.0.27 and kept returning wrong results.

[root@bd1 mysql]# mysql -u maber -p -h 192.168.253.252
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 486 to server version: 5.0.27-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use maber;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT
    -> 'lançamento' as type_commercial,
    -> (SELECT apelido FROM collectors cl_lea WHERE cl.leadered =
    -> cl_lea.id_user) as leadered,
    -> sum(IF(first_contact BETWEEN NOW() - INTERVAL 31 DAY AND NOW(),1,0)) as
    -> total
    ->
    -> FROM
    -> search_customer as cu
    -> INNER JOIN collectors cl ON (cu.id_commercial = cl.id_user),
    -> customer_indicator as ci
    ->
    -> WHERE
    -> cu.id_indicator = ci.id_indicator AND
    -> status = '0' AND
    -> cu.id_indicator <> '10' AND
    -> id_commercial IN (SELECT id_user FROM entering_collectors)
    ->
    -> GROUP BY
    -> type_commercial, leadered WITH ROLLUP
    -> ;
+-----------------+-------------------+-------+
| type_commercial | leadered          | total |
+-----------------+-------------------+-------+
| AAAAAAAAAA      | bbbbbbbbbbbbbbbbb |   167 |
| AAAAAAAAAA      | ccccccccccccccc   |    26 |
| AAAAAAAAAA      | ccccccccccccccc   |   193 |
| NULL            | ccccccccccccccc   |   193 |
+-----------------+-------------------+-------+
4 rows in set, 1 warning (0.96 sec)

Please, provide output of SHOW CREATE TABLE collectors statement for all tables you use in the statement.

Attached!

This is not a bug: issue SHOW WARNINGS command after the query.

Also read comment by Evgeny Potemkin for the bug #19042