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

[Andy Todd]

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?

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