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
Triage: Triaged: D2 (Serious)

[2 Nov 2009 14:06] Alexey Kopytov
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.
[2 Nov 2009 22:24] Godofredo Miguel Solorzano
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)
[6 Nov 2009 6:44] Bugs System
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.
[12 Nov 2009 17:12] Bugs System
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.
[2 Dec 2009 8:00] Bugs System
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)
[2 Dec 2009 8:07] Bugs System
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)
[3 Dec 2009 2:48] Paul Dubois
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+.
[16 Dec 2009 8:39] Bugs System
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)
[16 Dec 2009 8:45] Bugs System
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)
[16 Dec 2009 8:52] Bugs System
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)
[18 Dec 2009 2:05] Paul Dubois
Noted in 5.5.1, 6.0.14 changelogs.
[12 Mar 2010 14:12] Bugs System
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)
[12 Mar 2010 14:28] Bugs System
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)
[12 Mar 2010 14:43] Bugs System
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)