Bug #20873 group_concat with distinct using weekday
Submitted: 5 Jul 2006 21:14 Modified: 24 Jan 2014 12:26
Reporter: Rik Druten Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.24-BK, 5.0.22 OS:Linux (Linux, freebsd)
Assigned to: CPU Architecture:Any

[5 Jul 2006 21:14] Rik Druten 
Description:
SELECT 

group_concat(DISTINCT weekday(p.start) ORDER BY weekday(p.start)) AS result

p.start is a DATE column.

Gives me the correct result 0,1,2,3,4,5,6....

BUT

group_concat(DISTINCT weekday(p.start) ORDER BY p.start) AS result

gives me to much results 0,1,2,3,4,5,6,0,1,2,3,4 etc....

OR is this a feature?

How to repeat:

CREATE TABLE `test` (
  `planregel_id` int(11) NOT NULL auto_increment,
  `start` date default NULL,
  `eind` date default NULL,
  `start_tijd` time default NULL,
  PRIMARY KEY  (`planregel_id`),
  KEY `start` (`start`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=56305 ;

INSERT INTO `test` (`planregel_id`, `start`, `eind`, `start_tijd`) VALUES (61, '2003-09-01', '2003-09-01', '00:00:00'),
(162, '2003-09-11', '2003-09-11', '11:00:00'),
(92, '2003-12-16', '2003-12-16', '15:00:00'),
(91, '2003-12-09', '2003-12-09', '15:00:00'),
(90, '2003-12-02', '2003-12-02', '15:00:00'),
(89, '2003-11-25', '2003-11-25', '15:00:00'),
(88, '2003-11-18', '2003-11-18', '15:00:00'),
(87, '2003-11-11', '2003-11-11', '15:00:00'),
(86, '2003-11-04', '2003-11-04', '15:00:00'),
(85, '2003-10-28', '2003-10-28', '15:00:00'),
(84, '2003-10-21', '2003-10-21', '15:00:00'),
(83, '2003-10-14', '2003-10-14', '15:00:00'),
(82, '2003-10-07', '2003-10-07', '15:00:00'),
(81, '2003-09-30', '2003-09-30', '15:00:00'),
(80, '2003-09-23', '2003-09-23', '15:00:00'),
(1355, '2004-08-31', '2004-08-31', '00:00:00'),
(74, '2003-09-01', '2003-09-01', '00:00:00'),
(78, '2003-09-09', '2003-09-09', '15:00:00'),
(76, '2004-08-31', '2004-08-31', '00:00:00'),
(1354, '2004-08-31', '2004-08-31', '00:00:00'),
(13152, '2003-09-01', '2003-09-01', '00:00:00'),
(1356, '2003-09-08', '2003-09-08', '00:00:00'),
(68, '2003-09-01', '2003-09-01', '00:00:00'),
(79, '2003-09-16', '2003-09-16', '15:00:00'),
(1352, '2004-08-31', '2004-08-31', '00:00:00'),
(64, '2003-09-01', '2003-09-01', '00:00:00'),
(13155, '2005-08-31', '2005-08-31', '00:00:00'),
(62, '2003-09-01', '2003-09-01', '00:00:00'),
(60, '2003-09-01', '2003-09-01', '00:00:00'),
(93, '2003-12-23', '2003-12-23', '15:00:00'),
(94, '2003-12-30', '2003-12-30', '15:00:00'),
(95, '2004-01-06', '2004-01-06', '15:00:00'),
(96, '2004-01-13', '2004-01-13', '15:00:00'),
(97, '2004-01-20', '2004-01-20', '15:00:00'),
(98, '2004-01-27', '2004-01-27', '15:00:00'),
(99, '2004-02-03', '2004-02-03', '15:00:00'),
(100, '2004-02-10', '2004-02-10', '15:00:00'),
(101, '2004-02-17', '2004-02-17', '15:00:00'),
(102, '2004-02-24', '2004-02-24', '15:00:00'),
(103, '2004-03-02', '2004-03-02', '15:00:00'),
(104, '2004-03-09', '2004-03-09', '15:00:00'),
(105, '2004-03-16', '2004-03-16', '15:00:00'),
(106, '2004-03-23', '2004-03-23', '15:00:00'),
(107, '2004-03-30', '2004-03-30', '15:00:00'),
(108, '2004-04-06', '2004-04-06', '15:00:00'),
(109, '2004-04-13', '2004-04-13', '15:00:00'),
(110, '2004-04-20', '2004-04-20', '15:00:00'),
(111, '2004-04-27', '2004-04-27', '15:00:00'),
(112, '2004-05-04', '2004-05-04', '15:00:00'),
(113, '2004-05-11', '2004-05-11', '15:00:00');

SELECT '1', 
group_concat( DISTINCT weekday( p.start ) ORDER BY weekday( p.start ) ) AS result, 
group_concat( DISTINCT weekday( p.start ) ORDER BY ( p.start) ) AS result2

FROM test p
GROUP BY 1 

I expected te same results but in a different order.
[6 Jul 2006 15:06] MySQL Verification Team 
Thank you for the bug report. Could you please provide the result are
you getting and what you expect with:

SELECT '1', 
group_concat( DISTINCT weekday( p.start ) ORDER BY weekday( p.start ) ) AS
result, 
group_concat( DISTINCT weekday( p.start ) ORDER BY ( p.start) ) AS result2
FROM test p
GROUP BY 1;

I was unable to repeat with current source server.

Thanks in advance.
[7 Jul 2006 7:36] Rik Druten 
Ok, the example database was not correct (wrong example), this is te correct version:

CREATE TABLE `test2` (
  `planregel_id` int(11) NOT NULL auto_increment,
  `start` date default NULL,
  `eind` date default NULL,
  `start_tijd` time default NULL,
  PRIMARY KEY  (`planregel_id`),
  KEY `start` (`start`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13156 ;

INSERT INTO `test2` (`planregel_id`, `start`, `eind`, `start_tijd`) VALUES (61, '2003-09-01', '2003-09-01', '00:00:00'),
(162, '2003-09-02', '2003-09-11', '11:00:00'),
(92, '2003-09-03', '2003-12-16', '15:00:00'),
(91, '2003-09-04', '2003-12-09', '15:00:00'),
(90, '2003-09-05', '2003-12-02', '15:00:00'),
(89, '2003-09-06', '2003-11-25', '15:00:00'),
(88, '2003-09-10', '2003-11-18', '15:00:00'),
(87, '2003-09-11', '2003-11-11', '15:00:00'),
(103, '2004-09-07', '2004-03-02', '15:00:00'),
(104, '2004-09-08', '2004-03-09', '15:00:00'),
(105, '2004-03-16', '2004-03-16', '15:00:00'),
(106, '2004-03-23', '2004-03-23', '15:00:00'),
(107, '2004-03-30', '2004-03-30', '15:00:00'),
(108, '2004-04-06', '2004-04-06', '15:00:00'),
(109, '2004-04-13', '2004-04-13', '15:00:00'),
(110, '2004-04-20', '2004-04-20', '15:00:00'),
(111, '2004-04-27', '2004-04-27', '15:00:00'),
(112, '2004-05-04', '2004-05-04', '15:00:00'),
(113, '2004-05-11', '2004-05-11', '15:00:00');

Query:

SELECT '1',
group_concat( DISTINCT weekday( p.start ) ORDER BY weekday( p.start ) ) AS result,
group_concat( DISTINCT weekday( p.start ) ORDER BY ( p.start) ) AS result2
FROM test2 p
GROUP BY 1 ;

I would expect result1 and result2 to be the same but in a different order.

The output:
result: 0,1,2,3,4,5
result2: 0,1,2,3,4,5,2

Is this a bug? Or intentional.
[8 Jul 2006 10:28] Valeriy Kravchuk 
I was able to repeat the behaviour you described with 5.0.24-BK on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.24    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test2` (
    ->   `planregel_id` int(11) NOT NULL auto_increment,
    ->   `start` date default NULL,
    ->   `eind` date default NULL,
    ->   `start_tijd` time default NULL,
    ->   PRIMARY KEY  (`planregel_id`),
    ->   KEY `start` (`start`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13156 ;

mysql> INSERT INTO `test2` (`planregel_id`, `start`, `eind`, `start_tijd`) VALU
ES (61,
    -> '2003-09-01', '2003-09-01', '00:00:00'),
    -> (162, '2003-09-02', '2003-09-11', '11:00:00'),
    -> (92, '2003-09-03', '2003-12-16', '15:00:00'),
    -> (91, '2003-09-04', '2003-12-09', '15:00:00'),
    -> (90, '2003-09-05', '2003-12-02', '15:00:00'),
    -> (89, '2003-09-06', '2003-11-25', '15:00:00'),
    -> (88, '2003-09-10', '2003-11-18', '15:00:00'),
    -> (87, '2003-09-11', '2003-11-11', '15:00:00'),
    -> (103, '2004-09-07', '2004-03-02', '15:00:00'),
    -> (104, '2004-09-08', '2004-03-09', '15:00:00'),
    -> (105, '2004-03-16', '2004-03-16', '15:00:00'),
    -> (106, '2004-03-23', '2004-03-23', '15:00:00'),
    -> (107, '2004-03-30', '2004-03-30', '15:00:00'),
    -> (108, '2004-04-06', '2004-04-06', '15:00:00'),
    -> (109, '2004-04-13', '2004-04-13', '15:00:00'),
    -> (110, '2004-04-20', '2004-04-20', '15:00:00'),
    -> (111, '2004-04-27', '2004-04-27', '15:00:00'),
    -> (112, '2004-05-04', '2004-05-04', '15:00:00'),
    -> (113, '2004-05-11', '2004-05-11', '15:00:00');
Query OK, 19 rows affected (0.07 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> SELECT '1',
    -> group_concat(DISTINCT weekday(p.start) ORDER BY weekday( p.start )) AS
    -> result,
    -> group_concat(DISTINCT weekday(p.start) ORDER BY (p.start)) AS result2
    -> FROM test2 p
    -> GROUP BY 1 ;
+---+-------------+---------------+
| 1 | result      | result2       |
+---+-------------+---------------+
| 1 | 0,1,2,3,4,5 | 0,1,2,3,4,5,2 |
+---+-------------+---------------+
1 row in set (0.07 sec)

But it looks like a known problem (http://dev.mysql.com/doc/refman/5.0/en/open-bugs.html):

"DISTINCT with ORDER BY  doesn't work inside GROUP_CONCAT() if you don't use all and only those columns that are in the DISTINCT list."

Your second group_concat does not use exactly same expression in ORDER BY as in DISTINCT.
[10 Jul 2006 8:02] Rik Druten 
Ok, thanks. It's the second time I couldn't found an already know issue.
I looked in the bug.php-database . I'm sorry. Next time I will read 
http://dev.mysql.com/doc/refman/5.0/en/open-bugs.html.

"DISTINCT with ORDER BY  doesn't work inside GROUP_CONCAT() if you
don't use all and only those columns that are in the DISTINCT list."

-->Your second group_concat does not use exactly same expression in ORDER
BY as in DISTINCT.

Ok. Is it true that in the future the output of result2 will be like result
but in a different order?

Now:
result: 0,1,2,3,4,5
result2: 0,1,2,3,4,5,2
[8 Jan 2014 11:38] Erlend Dahl 
Running the query on recent 5.1/5.5, I get 0,1,2,3,4,5 and 0,1,2,3,4,5 respectively.
[24 Jan 2014 12:26] Manyi Lu 
This bug seems to have been fixed in 5.1