Django

Code

Ticket #930 (closed: duplicate)

Opened 3 years ago

Last modified 2 years ago

[patch] Leaving out ¨ordering¨ in OneToOne relationship causes bad SQL statement

Reported by: EABinGA Assigned to: russellm
Milestone: Component: Metasystem
Version: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

Description

Using Trunk rev-1461:

Given the Model Foo:

from django.core import meta

# Create your models here.

class Bar(meta.Model):
    class META:
        admin = meta.Admin()
        
    bardata = meta.CharField(maxlength=200)
    
    def __repr__(self):
        return self.bardata
        
class Baz(meta.Model):
    class META:
        admin = meta.Admin()
        
    bazdata = meta.CharField(maxlength=200)
    bar = meta.OneToOneField(Bar)

    def __repr__(self):
        return self.bazdata

When you select ¨baz¨ from the main admin interface:

You are greeted by the following:

OperationalError at /admin/foo/bazs/
no such column: foo_bars.id
Request Method: 	GET
Request URL: 	http://127.0.0.1:8080/admin/foo/bazs/
Exception Type: 	OperationalError
Exception Value: 	no such column: foo_bars.id
Exception Location: 	/usr/lib/python2.4/site-packages/Django-0.90-py2.4.egg/django/core/db/backends/sqlite3.py in execute, line 71

The offending SQL statement seems to be:

SELECT "foo_bazs"."bazdata","foo_bazs"."bar_id" FROM "foo_bazs" ORDER BY "foo_bars"."id" DESC

Explicitly including an ¨ordering¨ in the ¨baz¨ class fixes this problem, but it should work properly without one.

ALSO: Upon creation of a ¨baz¨, the admin interface of ¨baz¨ shows a label of ¨ID¨ folowed by a dropdown list containing bars followed by what seems to be the PK of said bar. What is the purpose of that number? Is it supposed to be there.

Additionally, once a baz is saved, the dropdown list suggests, that corresponding bar for this baz can be changed, but saving the baz with a different bar does not seem to have any effect.

Attachments

930_patch.diff (1.1 kB) - added by django@illuminosity.net on 12/13/05 20:20:01.
ordering_foreignkey.diff (0.7 kB) - added by dodysw@gmail.com on 01/06/06 09:16:15.

Change History

12/12/05 23:44:31 changed by django@illuminosity.net

I'm having the same problem, using a OneToOne? to auth.User. Adding the order by table name to the sql statement removes the error.

12/13/05 20:20:01 changed by django@illuminosity.net

  • attachment 930_patch.diff added.

12/14/05 00:38:47 changed by anonymous

  • summary changed from Leaving out ¨ordering¨ in OneToOne relationship causes bad SQL statement to [patch] Leaving out ¨ordering¨ in OneToOne relationship causes bad SQL statement.

01/06/06 09:16:15 changed by dodysw@gmail.com

  • attachment ordering_foreignkey.diff added.

01/18/06 16:16:41 changed by anonymous

The patch (930_patch.diff) has a problem that creates other SQL errors at times.

From IRC:

<bruce> Anyone around that can give advice on how to fix a problem in core/meta/init.py in

function_get_sql_clause?

<bruce> It generates invalid SQL if you have an order_by and select_related = True due to how it

handles not having duplicated table names in the list of tables.

<bruce> The reason for that is that it does the order_by, adds those tables to the list .. but doesn't

pass them in the list as the final parameter to _fill_table_cache when handling the select_related, so _fill_table_cache thinks that they're not in the list and adds them again.

<bruce> I think that the way around that is to do a tables_seen = [] before going over the order_by,

add the order_by tables to that, add opts.db_table and then pass that list rather than [opts.db_table] to _fill_table_cache

I did that fix and it worked, but then some things were getting returned multiple times I think. I ended up just backing out the patch in 930_patch.diff.

01/18/06 17:39:42 changed by russellm

  • owner changed from adrian to russellm.

The _get_sql_clause stuff has been reorganised a bit in magic_removal - I'll check to make sure that this problem doesn't still exist.

01/20/06 14:42:41 changed by Brian Ray <bray@sent.com>

I do not know about the magic_removal branch but I am experiencing the same problem on svn TIP (2075).

I am trying to extend auth.Users with a OneToOneField?.

class nwmuser(meta.Model):

user = meta.OneToOneField?(auth.User) first = meta.DateTimeField?("First Appearance") class META:

admin = meta.Admin()

ERROR: missing FROM-clause entry for table "auth_users" SELECT

"abc_nwmusers"."user_id", "abc_nwmusers"."first" FROM "abc_nwmusers"

ORDER BY "auth_users"."username" DESC;

If I punch the same query into psql I get the same thing.

01/20/06 15:01:16 changed by anonymous

  • priority changed from normal to high.

just apply this patch for now.

01/24/06 22:02:20 changed by WorldMaker

This issue affects the magic_removal branch as well. (I'm coding against revision 2123 tonight.)

03/06/06 06:50:47 changed by bruno@modulix.org

See also #1245 (same problem)

04/20/06 11:59:25 changed by Jorge Gajon <gajon@gajon.org>

A patch was submitted in ticket #1245. Instead of including the related table with lookup_paramsselect_related? = True, that patch constructs the lookup_order_field differently when the ordering field is a OneToOne? field.

It is not necessary to specify an ordering attribute.

04/21/06 11:42:52 changed by jkocherhans

  • status changed from new to closed.
  • resolution set to duplicate.

Duplicate of #1245. The patch here forces an implicit select_related which is well... not explicit enough ;) It can and should be avoided.


Add/Change #930 ([patch] Leaving out ¨ordering¨ in OneToOne relationship causes bad SQL statement)




Change Properties
Action