Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#12634 closed (invalid)

sql compiler execute: fetchone empty result with ordering_aliases fail.

Reported by: vcc@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


fetchone return None where no more data.

Attachments (1)

sql.patch (615 bytes) - added by anonymous 10 years ago.

Download all attachments as: .zip

Change History (18)

Changed 10 years ago by anonymous

Attachment: sql.patch added

comment:1 Changed 10 years ago by 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?

comment:2 Changed 10 years ago by anonymous

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

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

TypeError: 'NoneType' object is unsubscriptable

comment:3 Changed 10 years ago by Russell Keith-Magee

Resolution: invalid
Status: newclosed

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.

comment:4 Changed 10 years ago by anonymous

Resolution: invalid
Status: closedreopened
Version: 1.2-alpha1.2-beta

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:

./ runserver

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

If I try:

./ runserver

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/", 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/", line 674, in __call__
    return self.application(environ, start_response)
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/core/handlers/", line 245, in __call__
    response = middleware_method(request, response)
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/contrib/sessions/", line 36, in process_response
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/contrib/sessions/backends/", line 63, in save
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/db/models/", 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/", line 497, in save_base
  File "/Users/dev/workspace/myapp/lib/python2.6/site-packages/django/db/models/", 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/", 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/", line 733, in execute_sql
    return cursor.fetchone()[:-len(self.query.ordering_aliases)]
TypeError: 'NoneType' object is unsubscriptable

comment:5 Changed 10 years ago by 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 failing this way.

comment:6 Changed 10 years ago by Karen Tracey

What database engine are you specifying in

comment:7 Changed 10 years ago by anonymous

I'm using django_pyodbc to access SQL Server 2005. So 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?

comment:8 Changed 10 years ago by Russell Keith-Magee

Resolution: invalid
Status: reopenedclosed

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.

comment:9 Changed 10 years ago by 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, which is ultimately calling exists() it's the query that is generated by an exists() call, which is internal to the ORM and not something the app can influence.

comment:10 Changed 10 years ago by PyMan

Resolution: invalid
Status: closedreopened

I'm not the original submitter. I'm using django_pyodbc to accesso SQL Server 2005. So 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.

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

t = Test.objects.create(name = "pippo") #It's ok
t = Test2(id = "2", name = "pippo") #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.

comment:11 Changed 10 years ago by Russell Keith-Magee

milestone: 1.2
Resolution: invalid
Status: reopenedclosed

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.

comment:12 Changed 10 years ago by 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/ with the "execute_sql" function executes the query (the following query)

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

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?

comment:13 Changed 10 years ago by David Fischer

I created a ticket in django-pyodbc's ticketing system for this:

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 runserver. I'll take a more in-depth look at the problem in the next few days.

comment:14 Changed 10 years ago by dberansky

Resolution: invalid
Status: closedreopened
Version: 1.2-beta1.2

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 (<>; 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.

comment:15 Changed 10 years ago by Russell Keith-Magee

Resolution: invalid
Status: reopenedclosed

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

comment:16 Changed 10 years ago by 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.
  1. 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.

comment:17 Changed 10 years ago by 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.

Note: See TracTickets for help on using tickets.
Back to Top