#143 GROUP_CONCAT w/ GROUP BY & ORDER BY gives malformed resultse

MySQLdb-1.1
closed
Andy Dustman
MySQLdb (285)
5
2012-09-19
2005-06-27
Kolbe
No

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).

Testcase:

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.

I don't reproduce it either with this query on my
original dataset (with the addition of a column id
containing the value 6 (arbitrary):

mysql> select * from gctest;
+---------+-------+------+
| comment | group | id |
+---------+-------+------+
| test | 1 | 6 |
+---------+-------+------+

Here is the query i used:

select sum(id) as x from gctest group by group order
by comment;

It simply returned "6", as one would hope it would do.

Perhaps interesting is the fact that this doesn't cause
any "problem" either (returns 'testtest'):

SELECT CONCAT(comment,comment) AS comment FROM gctest
GROUP BY group ORDER BY comment;

While not using the aggregate function in the field
list, it does still use the other elements. This must
mean that the problem is related to the aggregate
function, and more specifically to GROUP_CONCAT since
the problem is not triggered by SUM().

It seems like it is probably an issue with the MySQLdb
interface.

Discussion

  • Andy Dustman
    Andy Dustman
    2005-06-28

    Logged In: YES
    user_id=71372

    No, it's practically impossible for this to be a MySQLdb
    bug. MySQLdb does absolutely no parsing of your query, so
    if aggregate functions are triggering it, it's in MySQL
    itself (in the server). The reason you get an array is
    because the resulting column is a BLOB. You should report
    this here:

    http://bugs.mysql.com/

    After you cursor.execute(), cursor.description is set to a
    sequence of 7-tuples, one for each column in the result set.
    The first two items are the column name and the column type.
    The latter is a numeric field, corresponding to the column
    types defined in mysql.h, or MySQLdb.constants.FIELD_TYPE.
    You should include these in your bug report.

    Also, you are using MySQLdb-1.1.6, which is a pre-release
    version, so until you can reproduce this with 1.2.0, I'm not
    taking any action on it.