MySQL Bugs: #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP and only const tables

Bug #48475	DISTINCT is ignored with GROUP BY WITH ROLLUP and only const tables
Submitted:	2 Nov 2009 14:06	Modified:	12 Mar 2010 16:57
Reporter:	Alexey Kopytov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.88, 5.1.41	OS:	Any
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
mysql> CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE t2 (b INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t2 VALUES (100);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
+------+
| b    |
+------+
|  100 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
+------+
| b    |
+------+
|  100 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

How to repeat:
See the test case in the description.

Thank you for the bug report.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-Win X64-debug-log Source distribution

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

mysql 5.1 >create database d7;
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >use d7
Database changed
mysql 5.1 >CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.19 sec)

mysql 5.1 >INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >CREATE TABLE t2 (b INT);
Query OK, 0 rows affected (0.12 sec)

mysql 5.1 >INSERT INTO t2 VALUES (100);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >SELECT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
+------+
| b    |
+------+
|  100 |
| NULL |
| NULL |
+------+
3 rows in set (0.06 sec)

mysql 5.1 >SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
+------+
| b    |
+------+
|  100 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89552

2842 Alexey Kopytov	2009-11-06
      Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP and
                  only const tables
      
      The problem was caused by two shortcuts in the optimizer that
      are inapplicable in the ROLLUP case.
      
      Normally in a case when only const tables are involved in a
      query, DISTINCT clause can be safely optimized away since there
      may be only one row produced by the join. Similarly, we don't
      need to create a temporary table to resolve DISTINCT/GROUP
      BY/ORDER BY. Both of these are inapplicable when the WITH
      ROLLUP modifier is present.
      
      Fixed by disabling the said optimizations for the WITH ROLLUP
      case.
     @ mysql-test/r/olap.result
        Added a test case for bug #48475.
     @ mysql-test/t/olap.test
        Added a test case for bug #48475.
     @ sql/sql_select.cc
        Disabled const-only table optimizations for the WITH ROLLUP
        case.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90260

2843 Alexey Kopytov	2009-11-12
      Fixed a gcc warning introduced by the patch for bug #48475.

Pushed into 5.0.89 (revid:joro@sun.com-20091202075830-mzl79q7mc1v72pf1) (version source revid:alexey.kopytov@sun.com-20091112171308-1ylrr7g751lmea0k) (merge vers: 5.0.88) (pib:13)

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:alexey.kopytov@sun.com-20091112171407-7dliq9j7ao4s1t72) (merge vers: 5.1.41) (pib:13)

Noted in 5.0.89, 5.1.42 changelogs.

DISTINCT was ignored for queries with GROUP BY WITH ROLLUP and only
const tables. 

Setting report to NDI pending push to 5.6.x+.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

Noted in 5.5.1, 6.0.14 changelogs.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)