Re: [Django] #18580: Avoiding SQLite update+annotations "only a single result allowed for a SELECT that is part of an expression"

[Django]

#18580: Avoiding SQLite update+annotations "only a single result allowed for a
SELECT that is part of an expression"
-+-
 Reporter:  jonash   |Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  1.4
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  fixed
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham ):

 * status:  new => closed
 * resolution:   => fixed


Comment:

 In [changeset:"a84344bc539c66589c8d4fe30c6ceaecf8ba1af3" a84344bc]:
 {{{
 #!CommitTicketReference repository=""
 revision="a84344bc539c66589c8d4fe30c6ceaecf8ba1af3"
 Fixed #19513, #18580 -- Fixed crash on QuerySet.update() after annotate().
 }}}

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.1b3e64fae6955f9790556c716c714ed8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #18580: Avoiding SQLite update+annotations "only a single result allowed for a SELECT that is part of an expression"

[Django]

#18580: Avoiding SQLite update+annotations "only a single result allowed for a
SELECT that is part of an expression"
-+-
 Reporter:  jonash   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.4
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by timgraham):

 All accepted tickets are good candidates to be fixed once we have a
 working patch.

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.e46612897cc6e89491c1aecf9a437457%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #18580: Avoiding SQLite update+annotations "only a single result allowed for a SELECT that is part of an expression"

[Django]

#18580: Avoiding SQLite update+annotations "only a single result allowed for a
SELECT that is part of an expression"
-+-
 Reporter:  jonash   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  1.4
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by chris-mccabe1988):

 I had the exact same issue, thanks jonash for the work around. I'm a
 little surprised that this hasn't been fixed in the 3 years since it's
 been raised, the work around is simple but not entirely obvious from the
 error message.

 Out of curiosity, will this be fixed in a future version?

--
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/064.45985247c75c07363c81aab1e454d7cb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #18580: Avoiding SQLite update+annotations "only a single result allowed for a SELECT that is part of an expression"

[Django]

#18580: Avoiding SQLite update+annotations "only a single result allowed for a
SELECT that is part of an expression"
-+
 Reporter:  jonash   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  ORM aggregation  |  Version:  1.4
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+

Comment (by akaariai):

 I created an evil little patch to debug this further: patch attached, the
 problematic SQL is this:
 {{{
 UPDATE "update_datapoint" SET "value" = foo WHERE "update_datapoint"."id"
 IN
 (SELECT U0."id", COUNT(U1."name") AS "relatedpoint__name__count"
  FROM "update_datapoint" U0 LEFT OUTER JOIN "update_relatedpoint" U1
ON (U0."id" = U1."data_id")
 GROUP BY U0."id", U0."name", U0."value", U0."another_value")
 }}}

 I don't see any reason to keep the aggregation in the inner query set.
 Maybe just getting rid of the annotation in the inner qs will solve this
 ticket?

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

Re: [Django] #18580: Avoiding SQLite update+annotations "only a single result allowed for a SELECT that is part of an expression"

[Django]

#18580: Avoiding SQLite update+annotations "only a single result allowed for a
SELECT that is part of an expression"
-+
 Reporter:  jonash   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  ORM aggregation  |  Version:  1.4
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:  Accepted
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+
Changes (by aaugustin):

 * needs_better_patch:   => 0
 * component:  Database layer (models, ORM) => ORM aggregation
 * needs_tests:   => 0
 * needs_docs:   => 0
 * type:  Uncategorized => Bug
 * stage:  Unreviewed => Accepted


Comment:

 With these models:
 {{{
 class Foo(models.Model):
 name = models.CharField(max_length=10)

 class Bar(models.Model):
 foo = models.ForeignKey(Foo)
 }}}
 I could reproduce the problem:
 {{{

 >>> Foo.objects.annotate(bar_count=Count('bar')).update(name='blah')
 Traceback (most recent call last):
   File "", line 1, in 
   File "/Users/myk/Documents/dev/django-trunk/django/db/models/query.py",
 line 544, in update
 rows = query.get_compiler(self.db).execute_sql(None)
   File "/Users/myk/Documents/dev/django-
 trunk/django/db/models/sql/compiler.py", line 990, in execute_sql
 cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
   File "/Users/myk/Documents/dev/django-
 trunk/django/db/models/sql/compiler.py", line 822, in execute_sql
 cursor.execute(sql, params)
   File "/Users/myk/Documents/dev/django-trunk/django/db/backends/util.py",
 line 42, in execute
 return self.cursor.execute(sql, params)
   File "/Users/myk/Documents/dev/django-
 trunk/django/db/backends/sqlite3/base.py", line 342, in execute
 return Database.Cursor.execute(self, query, params)
 DatabaseError: only a single result allowed for a SELECT that is part of
 an expression
 >>>
 Foo.objects.annotate(bar_count=Count('bar')).values('id').update(name='blah')
 0
 }}}

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.

[Django] #18580: Avoiding SQLite update+annotations "only a single result allowed for a SELECT that is part of an expression"

[Django]

#18580: Avoiding SQLite update+annotations "only a single result allowed for a
SELECT that is part of an expression"
--+
 Reporter:  jonash|  Owner:  nobody
 Type:  Uncategorized | Status:  new
Component:  Database layer (models, ORM)  |Version:  1.4
 Severity:  Normal|   Keywords:
 Triage Stage:  Unreviewed|  Has patch:  0
Easy pickings:  0 |  UI/UX:  0
--+
 When executing something like

 {{{
 Foo.objects.annotate(bar_count=Count('bars')) \
.update(spam=42)
 }}}

 SQLite throws an `only a single result allowed for a SELECT that is part
 of an expression` error.

 This can be worked around using `.values('id')`:


 {{{
 Foo.objects.annotate(bar_count=Count('bars')) \
.values('id') \
.update(spam=42)
 }}}

 Looks like SQLite can't cope with multiple fields in the `SELECT` clause,
 so I suggest not `SELECT`ing all the fields if we don't need them anyways
 (as in this case).

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to 
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-updates?hl=en.