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: | |
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 |
[27 Oct 2006 18:12]
Carlos Uldérico Cirello Filho
[27 Oct 2006 19:37]
Sveta Smirnova
Thank you for the report. Please try using current version accessible from dev.mysql.com/downloads/mysql/5.0.html and say us result.
[28 Oct 2006 19:27]
Carlos Uldérico Cirello Filho
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)
[30 Oct 2006 10:01]
Sveta Smirnova
Please, provide output of SHOW CREATE TABLE collectors statement for all tables you use in the statement.
[30 Oct 2006 14:12]
Carlos Uldérico Cirello Filho
Attached!
[30 Oct 2006 20:51]
Sveta Smirnova
This is not a bug: issue SHOW WARNINGS command after the query. Also read comment by Evgeny Potemkin for the bug #19042