sql compiler execute: fetchone empty result with ordering_aliases fail.

[vcc@…]

fetchone return None where no more data.

[Alex Gaynor]

I confess I don't see the point of this change, can you write a test that demonstrates how this impacts user level code?

[anonymous]

File "g:\django\db\models\sql\compiler.py", line 680, in execute_sql

return cursor.fetchone()[:-len(self.query.ordering_aliases)]

TypeError: 'NoneType' object is unsubscriptable

[Russell Keith-Magee]

Marking invalid due to incomplete reproduction information. I don't doubt you're seeing an error, but unless you tell us how to see the same error, there's not much we can do to help.

[anonymous]

I'm not the original submitter. But I see this error on revision 12799 if I run the development server with a port definition and access the server via hostname instead of localhost. I'm trying to port my existing 1.1 app to 1.2 beta.

If I do:

./manage.py runserver

And attempt to access http://localhost:8000/admin (or my app); all is well.

If I try:

./manage.py runserver 0.0.0.0:8000

And attempt to access http://<hostname>:8000/admin (mind the access via hostname; if I access localhost it works fine); I get this stack:

Traceback (most recent call last):
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/core/servers/basehttp.py", line 280, in run
    self.result = application(self.environ, self.start_response)
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/core/servers/basehttp.py", line 674, in __call__
    return self.application(environ, start_response)
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/core/handlers/wsgi.py", line 245, in __call__
    response = middleware_method(request, response)
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/contrib/sessions/middleware.py", line 36, in process_response
    request.session.save()
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/contrib/sessions/backends/db.py", line 63, in save
    obj.save(force_insert=must_create)
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/db/models/base.py", line 435, in save
    self.save_base(using=using, force_insert=force_insert, force_update=force_update)
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/db/models/base.py", line 497, in save_base
    manager.using(using).filter(pk=pk_val).exists())):
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/db/models/query.py", line 491, in exists
    return self.query.has_results(using=self.db)
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/db/models/sql/query.py", line 386, in has_results
    return bool(compiler.execute_sql(SINGLE))
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/db/models/sql/compiler.py", line 733, in execute_sql
    return cursor.fetchone()[:-len(self.query.ordering_aliases)]
TypeError: 'NoneType' object is unsubscriptable

[anonymous]

Some more info:

After applying the patch things work for me.

It appears it is something related to authentication (I'm using django.contrib.auth) because when I try to access after applying the patch, the first thing I see is the login page. After logging in everything works fine.

I'm not saying that the patch is the right thing to do - the patch is a useful workaround at this point. I don't know what querying the auth app is doing that would result in compiler.py failing this way.

[Karen Tracey]

What database engine are you specifying in settings.py?

[anonymous]

I'm using django_pyodbc to access SQL Server 2005. So settings.py has sql_server.pyodbc.

I just tested with sqlite3 (which is the DB I do have available other than MSSQL) and it works fine with without the patch above.

If no one else manages to replicate the issue with other databases, I suppose I have to go talk to the django_pyodbc guys, even though the stack trace shows no trace of their code being called?

[Russell Keith-Magee]

Again, closing invalid. Unless you can provide a query that invokes this line of code *and* demonstrate that isn't caused by a flaw in either the PyODBC DBAPI or the django_pyodbc stack, this isn't a flaw in Django.

Regarding the first part of this requirement: "Access /admin" isn't a reproduction instruction for an ORM flaw.

Regarding the second part: Looking at the proposed patch, I'm inclined to suggest that the problem lies with django_pyodbc using ordering aliases when it shouldn't. If you can narrow down a specific query and show that django_pyodbc *requires* an ordering alias that isn't required by other backends, I'll accept that as Django's problem.

[Karen Tracey]

Note given the posted stack trace, it's not any of the app's queries that is triggering this. It is the session middleware calling request.session.save(), which is ultimately calling exists()...so it's the query that is generated by an exists() call, which is internal to the ORM and not something the app can influence.

[PyMan]

I'm not the original submitter. I'm using django_pyodbc to accesso SQL Server 2005. So settings.py has sql_server.pyodbc

I have the same problem described above with a similar but different stack trace (identical last steps). I think I understood better when the problems occurs.

class Test(models.Model):
    name = models.CharField(max_length=25)

class Test2(models.Model):
    id = models.CharField(max_length = 32, primary_key = True)
    name = models.CharField(max_length=25)

Working with Test (a classic django model) there's no problem. When working with Test2 (see the "id" field as charfield) the problem occurs when working in some way.

#WAY1
Test.objects.create(name = "pippo") #It's ok
#----
Test2.objects.create(id = "1", name = "pippo") #Works fine

#WAY2
t = Test.objects.create(name = "pippo")
t.save() #It's ok
#----
t = Test2(id = "2", name = "pippo")
t.save() #Exception as above on "return cursor.fetchone()[:-len(self.query.ordering_aliases)]"

I can't avoid using the model and the way of working that causes the problem (an example is when using a form for model when creating one with some initial values).

I migrated from Django 1.1.1 to Django 1.2 some weeks ago and only now I found this problem. I'm sure this thing was working before.

[Russell Keith-Magee]

Again, this isn't a problem that exists for any of the officially supported backends. I strongly suspect that it's a problem with django_pyodbc. On IRC, Ramiro (The Django PyODBC author) agreed that this was likely.

Closing until someone can demonstrate that this is actually a problem with Django itself.

[PyMan]

I am not that sure it's just a django-pyodbc problem, maybe it uses the self.query_ordering_aliases in a way slightly different from other backends but it gives back a correct query to be executed by django (and so it is) and problem happens when the query gives no results.

Coming back to my previous example I could see that even doing this the problem happens:

Test.objects.create(name="pippo", id=12345) #At least when 12345 doesn't exists

That is it happens when an explicit id is given (because now it's doing a select, not an insert!)

So django/db/models/sql/compiler.py with the "execute_sql" function executes the query (the following query)

SELECT * FROM (
       SELECT (1) AS [a], (ROW_NUMBER() OVER (ORDER BY RAND() )) AS [rn] 
       FROM [myapp_test] 
       WHERE [myapp_test].[id] = %s
       ) AS X 
WHERE X.rn BETWEEN 1 AND 1

Query Parameters: (12345,)

When this happens the self.query.ordering_aliases has the value "(ROW_NUMBER() OVER (ORDER BY RAND() )) AS [rn]"

After executing the query the same function executes this piece of code

        if result_type == SINGLE:
            if self.query.ordering_aliases:
                #res = cursor.fetchone()
                #if res : return res[:-len(self.query.ordering_aliases)]
                #return res
                return cursor.fetchone()[:-len(self.query.ordering_aliases)] #HERE'S THE EXCEPTION!
            return cursor.fetchone()

The exception comes because cursor.fetchone() returns None at least when the "id" doesn't exists in the table. I added the commented lines that if used things work.

Can this help?

[David Fischer]

I created a ticket in django-pyodbc's ticketing system for this:
​http://code.google.com/p/django-pyodbc/issues/detail?id=79

Let's take the discussion there because it seems pretty clear that this only affects the django-pyodbc backend and not the supported backends. I am running into this issue as well and it is affecting me both when I serve my app with mod_wsgi as well as manage.py runserver. I'll take a more in-depth look at the problem in the next few days.

[dberansky]

I agree with the above assessments that the problem happens when ordering_aliases is not empty and the query returns no results. The real question is why ordering_aliases is not empty when sql clearly has no order by clause. What sets the field to a non-empty list? I've traced through the code and couldn't find anything that would cause the field to be set.

Please take a look at this video of a debug session (<​http://drop.io/7lpcvje>; you may want to download it and watch full screen) stepping through Query.has_results(). As the video runs, pay close attention to the first watch item in the lower right corner (q.ordering_aliases). Through most of the execution, the field stays as an empty list. However, right after the call to set_limits(1), its value changes to "(ROW_NUMBER() OVER (ORDER BY RAND() )) AS [rn]". Why? There is nothing in set_limits that would cause this behavior? Any ideas? Also notice, that this is all Django code.

[Russell Keith-Magee]

@dberansky -- Firstly: Seriously? A WMV of a debugging session? You don't tell us what query you're executing, or how your project is configured, or how far into the debugging session you are... even if we *could* read the incredibly small text on a video in a format that isn't universally readable. I appreciate the effort, but there's a reason that our documentation asks for sample code and sample projects, rather than videos.

Secondly, nothing you have presented alters the reason that I have closed this ticket twice before -- it is a problem that only appears under django-pyodbc. It can't be reproduced under any of the official backends. Until someone can clearly point to something that Django is doing wrong -- which means either demonstrating a case using Django's builtin backends that raises an error, or pointing to some code in one of Django's official backends that masks an error that shouldn't be masked -- then I'm calling this a problem with django-pyodbc.

[anonymous]

1. Yes, a video. It's an effective communication tool. Use it to work with the QA folks every day and everyone loves it. Of cause it sounds like you just want to find any excuse not to deal with this particular problem, like that the text is too small even though I pointed out in the original post that one needs to download the video and run it full screen.

2. What I'm presenting here is evidence that the problem shows up in Django's code, not in pyodbc or django-pyodbc. This very well may be a red herring, but so far you haven't said anything relevant to refute it.

[Russell Keith-Magee]

You have *not* provided anything remotely resembling *evidence* that this is a problem in Django's code. You have provided a video of a debug session. You haven't told me what query you are running to generate that debug session. You haven't told me how your database is configured. You haven't even named the database backend that you are using.

I'm glad that your QA people are happy with videos. I'm telling you that I'm not.

Firstly, playing WMV is a pain in the ass. The video you provided doesn't play for me using Microsoft's own MacOS WMV player. I had to dig up an old version of VLC and coax it into life so that I could even view your video.

Secondly, a video *by itself* doesn't give me any ability to reproduce a problem and investigate further. You say the problem is with has_results. That's fine. Maybe it is. But I'd like to see one frame higher in the stack to see how we got there. I'd like to see the consequences afterwards. I'd like to see what the SQLite backend does under comparable circumstances. But... oh... sorry.... I can't... because you haven't given me any details other than a video.

If you want me to take you seriously, you need to prove you're serious. That means giving me the power to reproduce what you are seeing. A video doesn't achieve that, unless it's a *comprehensive* walkthrough, and that's not what you have provided.