[DB-SIG] Strange resultset with ORDER BY col1, GROUP BY col2
andy47 at halfcooked.com
Fri Jun 24 13:19:14 CEST 2005
Kolbe Kegel wrote:
> 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
> 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