Bug #76229 SELECT DISTINCT WITH ROLLUP causes syntax error since 5.6

• Submitted: 9 Mar 2015 14:31
• Modified: 30 Nov 2018 3:25
• Reporter: Oli Sennhauser
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.6 and 5.7
• OS: Any
• CPU Architecture: Any
• Tags: syntax error, with rollup

[9 Mar 2015 14:31] Oli Sennhauser

Description:
SELECT DISTINCT WITH ROLLUP causes syntax error since 5.6

5.1.71, 5.5.38 ok
5.6.22, 5.7.5 err

How to repeat:
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
;

SET SESSION SQL_MODE=only_full_group_by;

SELECT DISTINCT LEFT(ts, 10), COUNT(*)
FROM test
GROUP BY LEFT(ts, 10)
WITH ROLLUP
;

ERROR 1221 (HY000): Incorrect usage of WITH ROLLUP and DISTINCT

+--------------+----------+
| LEFT(ts, 10) | COUNT(*) |
+--------------+----------+
| 2015-01-23   |        6 |
| NULL         |        6 |
+--------------+----------+

http://bugs.mysql.com/bug.php?id=70657
http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

Suggested fix:
Make it work again...

[9 Mar 2015 17:01] MySQL Verification Team

Thank you for the bug report.

Your MySQL connection id is 2
Server version: 5.5.43-log Source distribution pull: 2015.feb20

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > USE test
Database changed
mysql 5.5 > SET SESSION SQL_MODE=only_full_group_by;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >
mysql 5.5 > SELECT DISTINCT LEFT(ts, 10), COUNT(*)
    -> FROM test
    -> GROUP BY LEFT(ts, 10)
    -> WITH ROLLUP
    -> ;
Empty set (0.00 sec)

mysql 5.5 >

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24 Source distribution pull: 2015.feb20

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > CREATE TABLE `test` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `data` varchar(64) DEFAULT NULL,
    ->   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
    -> ;
Query OK, 0 rows affected (0.21 sec)

mysql 5.6 >
mysql 5.6 > SET SESSION SQL_MODE=only_full_group_by;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 >
mysql 5.6 > SELECT DISTINCT LEFT(ts, 10), COUNT(*)
    -> FROM test
    -> GROUP BY LEFT(ts, 10)
    -> WITH ROLLUP
    -> ;
ERROR 1221 (HY000): Incorrect usage of WITH ROLLUP and DISTINCT

<CUT>

mysql 5.7 >
mysql 5.7 > SET SESSION SQL_MODE=only_full_group_by;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 >
mysql 5.7 > SELECT DISTINCT LEFT(ts, 10), COUNT(*)
    -> FROM test
    -> GROUP BY LEFT(ts, 10)
    -> WITH ROLLUP
    -> ;
ERROR 1221 (HY000): Incorrect usage of WITH ROLLUP and DISTINCT

[30 Nov 2018 3:25] Paul DuBois

Posted by developer:
 
Fixed in 8.0.12.

MySQL now allows a query having a WITH ROLLUP modifier to use 
DISTINCT. See "SELECT Syntax", for more information.