#930 closed defect (duplicate)
[patch] Leaving out ¨ordering¨ in OneToOne relationship causes bad SQL statement
Reported by: | EABinGA | Owned by: | Russell Keith-Magee |
---|---|---|---|
Component: | Metasystem | Version: | |
Severity: | normal | Keywords: | |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
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 (2)
Change History (12)
comment:1 by , 19 years ago
by , 19 years ago
Attachment: | 930_patch.diff added |
---|
comment:2 by , 19 years ago
Summary: | Leaving out ¨ordering¨ in OneToOne relationship causes bad SQL statement → [patch] Leaving out ¨ordering¨ in OneToOne relationship causes bad SQL statement |
---|
by , 19 years ago
Attachment: | ordering_foreignkey.diff added |
---|
comment:3 by , 19 years ago
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.
comment:4 by , 19 years ago
Owner: | changed from | to
---|
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.
comment:5 by , 19 years ago
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.
comment:7 by , 19 years ago
This issue affects the magic_removal branch as well. (I'm coding against revision 2123 tonight.)
comment:9 by , 19 years ago
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.
comment:10 by , 19 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Duplicate of #1245. The patch here forces an implicit select_related which is well... not explicit enough ;) It can and should be avoided.
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.