Bug #24856 distinct with constant item and rollup => missing summary lines
Submitted: 6 Dec 2006 16:07 Modified: 8 May 2007 0:40
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.22 / 5.0.22, 5.1 BK OS:Linux (Linux)
Assigned to: Igor Babaev CPU Architecture:Any
Tags: distinct, rollup

[6 Dec 2006 16:07] Martin Friebe
Description:
Using distinct on a rollup query that groups by a constant (or constant function), will not display the roolup summary (even so they differ from the other line(s)).
(It works fine when grouping on a column)

 The 1st two block (grouping on 'x' or now()), produce 2 differnt rows without distinct. with distinct the 2nd row (1st column = null) is not returned)

functions seem to be affected, if no column is part of the expression. rand() is not affected. 
user variables are affected, unless modified in the query

How to repeat:
create table ru1 (a int); insert into ru1 values (1),(1),(2),(2),(2);

select 'x', group_concat(a) from ru1 group by 1 with rollup;
select distinct 'x', group_concat(a) from ru1 group by 1 with rollup;

select now(), group_concat(a) from ru1 group by 1 with rollup;
select distinct now(), group_concat(a) from ru1 group by 1 with rollup;

select a, group_concat(a) from ru1 group by 1 with rollup;
select distinct a, group_concat(a) from ru1 group by 1 with rollup;

drop table ru1;

Suggested fix:
-
[6 Dec 2006 17:41] Martin Friebe
This will be related, mysql assumes a olumn with a constant can not be null, but it can...

create table tt2 select 'x', group_concat(a) from ru1 group by 1 with rollup;
 Query OK, 2 rows affected, 1 warning (0.01 sec)
 Records: 2  Duplicates: 0  Warnings: 1

show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'x' at row 5 |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
[6 Dec 2006 18:40] Martin Friebe
if a is declared not null, then the column layout in the mysql command line client will be broken (as it does not assume the need for anything more but 1 digit)

  CREATE TABLE ru1 ( a int(11) NOT NULL default '0'); 
  select a,  group_concat(a) from ru1 group by 1 with rollup ;
+---+-----------------+
| a | group_concat(a) |
+---+-----------------+
| 1 | 1,1             |
| 2 | 2,2,2           |
|NULL | 1,1,2,2,2       |  # this line is not lined up
+---+-----------------+

--
Btw: this is an extension of bug 9681
[6 Dec 2006 22:23] Sveta Smirnova
Thank you for the report.

Verified as described using last BK sources. All versions are affected.
[29 Apr 2007 22:15] 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/25717

ChangeSet@1.2650, 2007-04-29 15:16:32-07:00, igor@olga.mysql.com +4 -0
  Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack
  some rollup rows (rows with NULLs for grouping attributes) if GROUP BY
  list contained constant expressions.
  
  This happened because the results of constant expressions were not put
  in the temporary table used for duplicate elimination. In fact a constant
  item from the GROUP BY list of a ROLLUP query can be replaced for an
  Item_null_result object when a rollup row is produced . 
  
  Now the JOIN::rollup_init function wraps any constant item referenced in
  the GROYP BY list of a ROLLUP query into an Item_func object of a special
  class that is never detected as constant item. This ensures creation of
  fields for such  constant items in temporary tables and guarantees right
  results when the result of the rollup operation first has to be written
  into a temporary table, e.g. in the cases when duplicate elimination is
  required.
[29 Apr 2007 23:03] 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/25718

ChangeSet@1.2650, 2007-04-29 16:04:43-07:00, igor@olga.mysql.com +4 -0
  Fixed bug #24856: the result set of a ROLLUP query with DISTINCT could lack
  some rollup rows (rows with NULLs for grouping attributes) if GROUP BY
  list contained constant expressions.
  
  This happened because the results of constant expressions were not put
  in the temporary table used for duplicate elimination. In fact a constant
  item from the GROUP BY list of a ROLLUP query can be replaced for an
  Item_null_result object when a rollup row is produced . 
  
  Now the JOIN::rollup_init function wraps any constant item referenced in
  the GROYP BY list of a ROLLUP query into an Item_func object of a special
  class that is never detected as constant item. This ensures creation of
  fields for such  constant items in temporary tables and guarantees right
  results when the result of the rollup operation first has to be written
  into a temporary table, e.g. in the cases when duplicate elimination is
  required.
[30 Apr 2007 0:03] Igor Babaev
I change the severity for S2 as due to this bug some ROLLUP queries return wrong resurn wrong result sets.
[1 May 2007 11:02] Bugs System
Pushed into 5.1.18-beta
[1 May 2007 11:03] Bugs System
Pushed into 5.0.42
[1 May 2007 11:05] Bugs System
Pushed into 4.1.23
[8 May 2007 0:40] Paul Dubois
Noted in 4.1.23, 5.0.42, 5.1.18 changelogs.

The result set of a query that used WITH ROLLUP and DISTINCT could
lack some rollup rows (rows with NULL values for grouping attributes)
if the GROUP BY list contained constant expressions.