SQL error in generic_aggregate #3

sidmitra opened this Issue Sep 18, 2010 · 2 comments


None yet
2 participants


generic_aggregate(TaxExpert.objects.all(), Review.expert, Count('overall_review'))


(1064, "You have an error in your SQL syntax; 
 check the manual that corresponds to your MySQL server 
 version for the right syntax to use near 'integer AUTO_INCREMENT) IN 
 (\n  SELECT U0.`id` FROM `profiles_taxex' at line 5")

SQL generated:

SELECT Count(`overall_review`) AS aggregate_score
    FROM `expert_review`
        `content_type_id`=11 AND
        CAST(`object_id` AS integer AUTO_INCREMENT) IN (
            SELECT U0.`id` FROM `profiles_taxexpert` U0)

How my models look,
class Review(models.Model):
content_type = models.ForeignKey(ContentType)
object_id = models.PositiveIntegerField()
expert = generic.GenericForeignKey('content_type', 'object_id')
overall_review = models.PositiveIntegerField()

class TaxExpert(models.Model):
     reviews = generic.GenericRelation(Review)

Some debugging later i think i've pinpointed the issue to this sql line

 CAST(`object_id` AS integer AUTO_INCREMENT)

, which i changed manually to

 CAST(`object_id` AS UNSIGNED )

to test and now that works. So the field_type returned, doesn't match what the CAST method expects?...especially the AUTO_INCREMENT part.

I changed one line utils.py to

 gfk_expr = "CAST(%s AS %s)" % (qn(gfk_field.fk_field), 'UNSIGNED')#pk_field_type)

and it works now. But that might not work in every case. Maybe you can help me figure out a patch for it... or otherwise let me know what i'm doing wrong here? I'm leaning towards this being more of a PEBKAC, than an actual bug.

django - 1.1.1
MySQL - 5.1.41-3ubuntu12.6 (Ubuntu)

MySQL CAST: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

Ok the issues is in

gfk_field_type = gfk_field.model._meta.get_field(gfk_field.fk_field).db_type()

which returns INTEGER AUTOINCREMENT, but the CAST function doesn't accept that. All it does is UNSIGNED or SIGNED. I think this means there are going to be lots of if cases in that function like:

elif pk_field_type == 'integer AUTO_INCREMENT':
    pk_field_type = 'UNSIGNED'

coleifer commented Oct 5, 2010

This should be fixed, sorry its taken me so long, i need to check my notifications more!

This issue was closed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment