MySQL Bugs: #24856: distinct with constant item and rollup => missing summary lines

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

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:
-

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)

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

Thank you for the report.

Verified as described using last BK sources. All versions are affected.

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.

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.

I change the severity for S2 as due to this bug some ROLLUP queries return wrong resurn wrong result sets.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Pushed into 4.1.23

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.