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

[Kolbe]

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.

[Andy Dustman]

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.