IndirectReferenceSet.find().something() produces non-working queries

Bug #181905 reported by Bozo Dragojevic on 2008-01-11
10
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Storm
Medium
Thomas Herve

Bug Description

Index: tests/store/base.py
===================================================================
--- tests/store/base.py (revision 116417)
+++ tests/store/base.py (working copy)
@@ -3111,6 +3111,26 @@
                           (200, "Title 200"),
                          ])

+
+ def test_indirect_reference_set_find_set(self):
+ foo = self.store.get(FooIndRefSet, 20)
+ bar = self.store.get(Bar, 300)
+
+ foo.bars.add(bar)
+ foo.bars.find().set(title=u"hoho")
+
+ items = []
+ for bar in foo.bars:
+ items.append((bar.id, bar.title))
+ items.sort()
+
+ self.assertEquals(items, [
+ (100, "hoho"),
+ (200, "hoho"),
+ (300, "hoho"),
+ ])
+
+
     def test_indirect_reference_set_add_remove_with_wrapper(self):
         foo = self.store.get(FooIndRefSet, 20)
         bar300 = self.store.get(Bar, 300)

Related branches

Setting database.DEBUG=True I get (sqlite backend, fails the same way with mysql, too):

SELECT foo.id, foo.title FROM foo WHERE foo.id = ? LIMIT 1 (20,)
SELECT bar.foo_id, bar.id, bar.title FROM bar WHERE bar.id = ? LIMIT 1 (300,)
INSERT INTO link (bar_id, foo_id) VALUES (?, ?) (300, 20)
UPDATE bar SET title=? WHERE link.foo_id = ? AND bar.id = link.bar_id (u'hoho', 20)

doing a:

   foo.bars.find().remove()

fails too.

James Henstridge (jamesh) wrote :

For databases like PostgreSQL (at least), we should be able to generate working queries.

For foo.bars.find().set(title=u"hoho"), the following is valid in PG:

    UPDATE bar SET title='hoho' FROM link
      WHERE link.foo_id = 20 AND bar.id = link.bar_id;

Similarly, for foo.bars.find().remove(), the following works:

    DELETE FROM bar USING link
      WHERE link.foo_id = 20 AND bar.id = link.bar_id;

It looks like MySQL supports "DELETE ... USING", but has a different syntax for the UPDATE:

    UPDATE bar, link SET bar.title='hoho' FROM link
      WHERE link.foo_id = 20 AND bar.id = link.bar_id;

It doesn't look like SQLite supports this type of feature for either UPDATE or DELETE. So it will probably be necessary to do per-backend statement compilation to solve this bug.

James Henstridge (jamesh) wrote :

Oops. The MySQL example should have been:

    UPDATE bar, link SET bar.title='hoho'
      WHERE link.foo_id = 20 AND bar.id = link.bar_id;

Thomas Herve (therve) on 2010-11-13
Changed in storm:
assignee: nobody → Thomas Herve (therve)
importance: Undecided → Medium
milestone: none → 0.19
status: New → In Progress
Thomas Herve (therve) on 2011-09-26
Changed in storm:
milestone: 0.19 → 0.20
Changed in storm:
milestone: 0.20 → 0.21
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers