Re: Newer PostgreSQL breaks django_friends, maybe deserves ORM fix.

On Tue, Nov 3, 2009 at 7:05 AM, Russell Keith-Magee
<freakboy3742@gmail.com> wrote:
>
> On Tue, Nov 3, 2009 at 12:19 PM, Christophe Pettus <xof@thebuild.com> wrote:
>>
>>
>> On Nov 2, 2009, at 2:14 PM, Bill Freeman wrote:
>>> My presumption is that the older PostgreSQL, expecting to have to
>>> decide whether unquoted things are strings (e.g.; "status" in the
>>> query samples above), used to look at the context and decided that we
>>> had meant the 8 as a string.
>>
>> In particular, as of PostgreSQL 8.3 (and thus 8.4), non-text types are
>> not automatically cast to text.  You can find the details in the 8.3
>> release notes:
>>
>>        http://www.postgresql.org/docs/8.3/static/release-8-3.html
>>
>> The relevant section is E.9.2.1.
>>
>>> Thoughts?  Workaround suggestions?
>>
>> I'm not completely sure why the ORM is generating SQL that compares a
>> number with a character string in the first place; that sounds like a
>> bug in either the ORM or the client code, to me.
>
> I concur. This looks like it might be a Django bug.
>
> If I understand the original problem correctly, it is this:
>
> class MyObj(models.Model):
>    CHOICES = (
>        ('1', 'first choice')
>        ('2', 'second choice')
>    )
>    choice = models.CharField(max_length=1, choices=CHOICES)
>
> Now run two queries. First, query using an integer:
>
> MyObj.objects.filter(choice=1)
>
> This yields the SQL:
>
> ('SELECT `myapp_myobj`.`id`, `myapp_myobj`.`choice` FROM `myapp_myobj`
> WHERE `myapp_myobj`.`choice` = %s ', (1,))
>
> Now, query with an actual string:
>
> MyObj.objects.filter(choice='1')
>
> which yields the SQL:
>
> ('SELECT `myapp_myobj`.`id`, `myapp_myobj`.`choice` FROM `myapp_myobj`
> WHERE `myapp_myobj`.`choice` = %s ', ('1',))

Might that have to be:

'SELECT ... `choice` = `%s`', ('1',))

because %s renders both 1 and '1' as the single character 1.

But then, if I understand correctly, by the time this gets to the DB
it's actually something like:
SELECT ... = ? '

and I don't know how the quoting works there. Still that's the
province of the adaptor, and all django can do is decide what is
passed to psycopg2. I guess I'm going to learn more about the DB
adaptor.
>
> The fact that the first example (the integer lookup) passes at all is
> due to the good grace of the databases themselves - logically, I think
> Postgres 8.4 is correct in declaring this an error. "1" != 1.
>
> I think the fix is pretty simple. CharField doesn't currently have a
> get_db_prep_value() method, and it should.
>
> Compare and contrast with IntegerField or BooleanField - both these
> fields have get_db_prep_value() methods that cast the provided value
> to int() and bool(). CharField (and TextField for that matter) should
> do the same, but with unicode(). This would force the filter value of
> 1 into '1', which will be passed to the backend as a string, as it
> should be.
>
> I've just opened ticket #12137 to track this. I've put it on the 1.2
> milestone, so we will endeavour to fix it before we hit v1.2. Any
> assistance in turning the example and suggested fix into a trunk-ready
> patch will be gratefully accepted.

Since this is impacting me right now, I'll spend some quality time
with pdb to try and come up with a patch.

News at it happens.
>
> Yours,
> Russ Magee %-)

Bill

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---