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

Kolbe Kegel kolbe at kolbekegel.com
Fri Jun 24 06:39:15 CEST 2005


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

More information about the DB-SIG mailing list