MySQL Bugs: #14466: Sort Order Incorrect in GROUP

Bug #14466	Sort Order Incorrect in GROUP_CONCAT Function in a View
Submitted:	29 Oct 2005 13:13	Modified:	9 Nov 2005 4:06
Reporter:	Esteban Jesus	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	mysql-standard-5.0.15-osx10.4-powerpc-64/BK 5.0	OS:	MacOS (Mac OS X 10.4.2/Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
The DESC modifier doesn't seem to affect the ordering of fields used in a GROUP_CONCAT function as part of a view definition - the fields are always concatenated in ascending order.

How to repeat:
CREATE VIEW aView AS SELECT id, GROUP_CONCAT(field ORDER BY indexField DESC separator '.') FROM aTable;
SELECT * FROM aView;

Returns the same as:

CREATE VIEW aView AS SELECT id, GROUP_CONCAT(field ORDER BY indexField ASC separator '.') FROM aTable;
SELECT * FROM aView;

However, the result of the following standalone query is correct:

SELECT id, GROUP_CONCAT(aField ORDER BY indexField DESC separator '.') FROM aTable;

(The indexField is defined as 'tinyint unsigned NOT NULL')

Suggested fix:
Make GROUP_CONCAT honor the DESC modifier in view definitions.

Could you please provide a complete test case script: create table &
some inserts & create view. Please read:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

and you will see that GROUP_CONCAT is a GROUP BY (Aggregate) Function
and in your statement samples isn't showed a GROUP BY clause.

Thanks in advance.

Apologies, I typed the example query in a rush - here is a more complete example:

CREATE TABLE Domains (
  domainID int unsigned NOT NULL auto_increment,
  parentDomainID int unsigned NOT NULL,
  domainType enum('root','node','user') NOT NULL,
  
  domainName varchar(50) NOT NULL,
  domainPassword varchar(50),
  
  INDEX domainKey (domainID,parentDomainID),
  INDEX domainKey2 (parentDomainID,domainID),
  INDEX domainKey3 (domainID,domainName),
  UNIQUE (parentDomainID,domainName),
  
  PRIMARY KEY (domainID),
  FOREIGN KEY (parentDomainID) REFERENCES domains (domainID) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB CHARACTER SET UTF8;

CREATE TABLE DomainXReferences (
  domainID int unsigned NOT NULL,
  ancestorID int unsigned NOT NULL,
  indexID tinyint unsigned NOT NULL,
  
  INDEX domainKey (domainID,ancestorID),
  INDEX domainKey2 (ancestorID,domainID),
  INDEX domainKey3 (ancestorID,indexID),
  INDEX domainKey4 (indexID,ancestorID),
  
  PRIMARY KEY (domainID,ancestorID,indexID),
  FOREIGN KEY (domainID) REFERENCES domains (domainID) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (ancestorID) REFERENCES domains (domainID) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB CHARACTER SET UTF8;

INSERT INTO Domains VALUES (1,1,'root','Root','pass');
	INSERT INTO Domains VALUES (2,1,'user','abc','pass');
	INSERT INTO Domains VALUES (3,1,'user','def','pass');
	INSERT INTO Domains VALUES (4,1,'user','ghi','pass');
	INSERT INTO Domains VALUES (5,1,'user','jkl','pass');
	INSERT INTO Domains VALUES (6,1,'node','com','pass');
	INSERT INTO Domains VALUES (7,6,'node','mycompany','pass');
	INSERT INTO Domains VALUES (8,7,'node','internal','pass');
	INSERT INTO Domains VALUES (9,8,'node','test','pass');
	INSERT INTO Domains VALUES (10,8,'node','live','pass');
	INSERT INTO Domains VALUES (11,7,'node','demo','pass');

INSERT INTO DomainXReferences VALUES (2,2,1);
INSERT INTO DomainXReferences VALUES (3,3,1);
INSERT INTO DomainXReferences VALUES (4,4,1);
INSERT INTO DomainXReferences VALUES (5,5,1);
INSERT INTO DomainXReferences VALUES (6,6,1);
INSERT INTO DomainXReferences VALUES (7,7,1);
INSERT INTO DomainXReferences VALUES (7,6,2);
INSERT INTO DomainXReferences VALUES (8,8,1);
INSERT INTO DomainXReferences VALUES (8,7,2);
INSERT INTO DomainXReferences VALUES (8,6,3);
INSERT INTO DomainXReferences VALUES (9,9,1);
INSERT INTO DomainXReferences VALUES (9,8,2);
INSERT INTO DomainXReferences VALUES (9,7,3);
INSERT INTO DomainXReferences VALUES (9,6,4);
INSERT INTO DomainXReferences VALUES (10,10,1);
INSERT INTO DomainXReferences VALUES (10,8,2);
INSERT INTO DomainXReferences VALUES (10,7,3);
INSERT INTO DomainXReferences VALUES (10,6,4);
INSERT INTO DomainXReferences VALUES (11,11,1);
INSERT INTO DomainXReferences VALUES (11,7,2);
INSERT INTO DomainXReferences VALUES (11,6,3);

INSERT INTO DomainXReferences SELECT DISTINCT(domainID),1,0 FROM DomainXReferences;

SELECT e1.domainID,GROUP_CONCAT(e2.domainName ORDER BY e3.indexID DESC SEPARATOR '.') FROM Domains AS e1,Domains as e2,domainXReferences AS e3  WHERE e1.domainID = e3.domainID AND e3.ancestorID = e2.domainID AND e2.domainType <> 'root' GROUP BY e1.domainID;

Returns:

+----------+--------------------------------------------------------------------+
|        2 | abc                                                                |
|        3 | def                                                                |
|        4 | ghi                                                                |
|        5 | jkl                                                                |
|        6 | com                                                                |
|        7 | com.mycompany                                                      |
|        8 | com.mycompany.internal                                             |
|        9 | com.mycompany.internal.test                                        |
|       10 | com.mycompany.internal.live                                        |
|       11 | com.mycompany.demo                                                 |
+----------+--------------------------------------------------------------------+

Whereas,

CREATE OR REPLACE VIEW DomainLookup (domainID,qualifiedDomainName) AS SELECT e1.domainID,GROUP_CONCAT(e2.domainName ORDER BY e3.indexID DESC SEPARATOR '.') FROM Domains AS e1,Domains as e2,domainXReferences AS e3  WHERE e1.domainID = e3.domainID AND e3.ancestorID = e2.domainID AND e2.domainType <> 'root' GROUP BY e1.domainID;
SELECT * FROM DomainLookup;

Yields:

+----------+-----------------------------+
|        2 | abc                         |
|        3 | def                         |
|        4 | ghi                         |
|        5 | jkl                         |
|        6 | com                         |
|        7 | mycompany.com               |
|        8 | internal.mycompany.com      |
|        9 | test.internal.mycompany.com |
|       10 | live.internal.mycompany.com |
|       11 | demo.mycompany.com          |
+----------+-----------------------------+

As you can see, a standalone select statement returns the correctly ordered result whilst the same select statement used to create a view returns incorrectly ordered results.

Running 'SHOW CREATE VIEW DomainLookup;' returns: 

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `domainlookup` AS select `e1`.`domainID` AS `domainID`,group_concat(`e2`.`domainName` order by `e3`.`indexID` separator '.') AS `qualifiedDomainName` from ((`domains` `e1` join `domains` `e2`) join `domainxreferences` `e3`) where ((`e1`.`domainID` = `e3`.`domainID`) and (`e3`.`ancestorID` = `e2`.`domainID`) and (`e2`.`domainType` <> _utf8'root')) group by `e1`.`domainID`

Notice that the DESC modifier is missing from the GROUP_CONCAT function.

Verified also with MyISAM tables.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE Domains (
    ->   domainID int unsigned NOT NULL auto_increment,
    ->   parentDomainID int unsigned NOT NULL,
    ->   domainType enum('root','node','user') NOT NULL,
    ->   
    ->   domainName varchar(50) NOT NULL,
    ->   domainPassword varchar(50),
    ->   
    ->   INDEX domainKey (domainID,parentDomainID),
    ->   INDEX domainKey2 (parentDomainID,domainID),
    ->   INDEX domainKey3 (domainID,domainName),
    ->   UNIQUE (parentDomainID,domainName),
    ->   
    ->   PRIMARY KEY (domainID),
    ->   FOREIGN KEY (parentDomainID) REFERENCES domains (domainID) ON DELETE CASCADE
    -> ON UPDATE CASCADE
    -> ) TYPE=InnoDB CHARACTER SET UTF8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> 
mysql> CREATE TABLE DomainXReferences (
    ->   domainID int unsigned NOT NULL,
    ->   ancestorID int unsigned NOT NULL,
    ->   indexID tinyint unsigned NOT NULL,
    ->   
    ->   INDEX domainKey (domainID,ancestorID),
    ->   INDEX domainKey2 (ancestorID,domainID),
    ->   INDEX domainKey3 (ancestorID,indexID),
    ->   INDEX domainKey4 (indexID,ancestorID),
    ->   
    ->   PRIMARY KEY (domainID,ancestorID,indexID),
    ->   FOREIGN KEY (domainID) REFERENCES domains (domainID) ON DELETE CASCADE ON
    -> UPDATE CASCADE,
    ->   FOREIGN KEY (ancestorID) REFERENCES domains (domainID) ON DELETE CASCADE ON
    -> UPDATE CASCADE
    -> ) TYPE=InnoDB CHARACTER SET UTF8;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> INSERT INTO Domains VALUES (1,1,'root','Root','pass');
Query OK, 1 row affected (0.02 sec)

<CUT>

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

<CUT>

mysql> 
mysql> INSERT INTO DomainXReferences SELECT DISTINCT(domainID),1,0 FROM
    -> DomainXReferences;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT e1.domainID,GROUP_CONCAT(e2.domainName ORDER BY e3.indexID DESC SEPARATOR
    -> '.') FROM Domains AS e1,Domains as e2,DomainXReferences AS e3  WHERE e1.domainID
    -> = e3.domainID AND e3.ancestorID = e2.domainID AND e2.domainType <> 'root' GROUP
    -> BY e1.domainID;
+----------+--------------------------------------------------------------------+
| domainID | GROUP_CONCAT(e2.domainName ORDER BY e3.indexID DESC SEPARATOR
'.') |
+----------+--------------------------------------------------------------------+
|        2 | abc                                                                |
|        3 | def                                                                |
|        4 | ghi                                                                |
|        5 | jkl                                                                |
|        6 | com                                                                |
|        7 | com.mycompany                                                      |
|        8 | com.mycompany.internal                                             |
|        9 | com.mycompany.internal.test                                        |
|       10 | com.mycompany.internal.live                                        |
|       11 | com.mycompany.demo                                                 |
+----------+--------------------------------------------------------------------+
10 rows in set (0.01 sec)

mysql> CREATE OR REPLACE VIEW DomainLookup (domainID,qualifiedDomainName) AS SELECT
    -> e1.domainID,GROUP_CONCAT(e2.domainName ORDER BY e3.indexID DESC SEPARATOR '.')
    -> FROM Domains AS e1,Domains as e2,DomainXReferences AS e3  WHERE e1.domainID =
    -> e3.domainID AND e3.ancestorID = e2.domainID AND e2.domainType <> 'root' GROUP BY
    -> e1.domainID;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM DomainLookup;
+----------+-----------------------------+
| domainID | qualifiedDomainName         |
+----------+-----------------------------+
|        2 | abc                         |
|        3 | def                         |
|        4 | ghi                         |
|        5 | jkl                         |
|        6 | com                         |
|        7 | mycompany.com               |
|        8 | internal.mycompany.com      |
|        9 | test.internal.mycompany.com |
|       10 | live.internal.mycompany.com |
|       11 | demo.mycompany.com          |
+----------+-----------------------------+
10 rows in set (0.02 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/internals/31737

Item_func_group_concat::print() wasn't printing sort order thus creating wrong
view.

Fixed in 5.0.16, cset 1.1957.4.1

Noted in 5.0.16 changelog.