[DB-SIG] Strange resultset with ORDER BY col1, GROUP BY col2

Andy Todd andy47 at halfcooked.com
Fri Jun 24 13:19:14 CEST 2005

Kolbe Kegel wrote:
> Hello,
> I am very new to Python, but I have encountered something that seems to 
> be an undocumented, possibly erroneous behavior when fetching rows from 
> a cursor object. I am using MySQL 5.0.7 for these tests.
> Here is a way to test:
> 1) Create a table similar to this...
> CREATE TABLE `gctest` (
>    `comment` varchar(255) default NULL,
>    `group` int(10) unsigned NOT NULL
> )
> 2) Insert a row similar to this...
> INSERT INTO `gctest` VALUES ('test',1);
> 3) Execute the following SELECT in a Python program...
> SELECT GROUP_CONCAT(comment) AS comment FROM gctest GROUP BY `group` 
> ORDER BY comment;
> for example..
> import MySQLdb
> db = MySQLdb.connect(host="localhost", user="", passwd="", db="test")
> cursor = db.cursor()
> cursor.execute("SELECT GROUP_CONCAT(comment) AS comment FROM gctest 
> GROUP BY `group` ORDER BY comment;")
> for record in cursor.fetchall():
>          print record[0]
> 4) Observe the results...
> array('c', 'test')
> This indicates that an array of some sort is being returned. I don't 
> know the significance of its contents in the Python world, but it means 
> that while all other elements in the resultset are returned as strings, 
> this one bizarre exception exists.
> I was able to narrow my test case down to the behavior occurring when 
> GROUP BY and ORDER BY appear in the same statement AND they reference 
> different columns. That is, when the results are GROUPped BY one column 
> and ORDERed BY another, this behavior occurs.
> It seems like it is probably an issue with the MySQLdb interface. But I 
> am passing along this information and this test case so that I can hear 
> the thoughts of those more experience in these matters. I hope that 
> someone will be able to shed some light on this matter.
> Again, I am using MySQL 5.0.7. I am using Python 2.4.2. I am using 
> MySQLdb 1.1.6-1ubuntu2 (installed through apt on Ubuntu Linux 5.04).
> Thank you,
> Kolbe Kegel

As group_concat is a MySQL specific function I suspect that the problem 
you are seeing is specific to the MySQLdb module.

I'm running MySQL 4.0.17 and your query doesn't work as GROUP_CONCAT is 
not available in this version.

A couple of questions do spring to mind though;

- Do you really need to group by one thing and order by another?
- Do you need to do the group_concat transposition in SQL or would it be 
better achieved in Python after you've select all of the group, comment 
pairs from the database?

 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

More information about the DB-SIG mailing list