Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL. (was: Using Annotation As Update Parameter Generates Invalid SQL)

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL.
-+-
 Reporter:  David Sanders|Owner:  PREMANAND
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  master
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  duplicate
 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 felixxm):

 * status:  assigned => closed
 * resolution:   => duplicate


Comment:

 We decided to return error message in such cases, so I'm closing this as a
 duplicate of #28408.

-- 
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/068.5ab8e8eeaa1dd9b620d56c2789409a92%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
-+-
 Reporter:  David Sanders|Owner:  PREMANAND
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (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 Paolo Melchiorre):

 This bug is still present for me with Django 2.0 and PostgreSQL 9.6.

 I solved using Subquery in a way similar as suggested in this my
 StackOverflow answer: https://stackoverflow.com/a/50134728/755343

-- 
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/068.a86853f14ea7b74d19436d50355c2d39%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
-+-
 Reporter:  David Sanders|Owner:  PREMANAND
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (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 David Sanders):

 Looks like the query in the description was wrong, I've updated it.

 In your example PREMANAND, you aren't using the annotation in the
 `UPDATE`. You have the annotation named `bname` and you don't use that in
 the `UPDATE`. I realize that's because my original query was wrong, but
 pointing out why you didn't see the issue.

 Using the two models in the description, here's a minimal reproduce case
 including creating objects:

 {{{
 >>> bar = Bar.objects.create(name="Test Name")
 >>> Foo.objects.create(related_bar=bar, bar_name="Replace Me")
 >>>
 
Foo.objects.annotate(related_bar_name=F('related_bar__name')).update(bar_name=F('related_bar_name'))
 }}}

 Note that the annotation name is used as the update value. If you look at
 the SQL that is generated that you provided, you can see that on MySQL it
 breaks it into two queries, one `SELECT` then an `UPDATE`. Unless it
 carries the result from the `SELECT` over to the `UPDATE` that isn't going
 to work. In my testing the `UPDATE` tries to use the annotation name
 directly, which fails because it is an unknown column name in the `UPDATE`
 query.

 Replying to [comment:5 PREMANAND]:
 > Need more clarifications on this.
 >
 > SQLLite and Mysql both passes the test. The queries generated are
 different(sqllite has SUBSELECT) but the result of the subselect is same
 as MYSQL.
 >
 > Here are the 2 queries thats generated. In MYSQL the select statement is
 generated first and then the results are applied to the update but in
 sqllite the select statement is applied directly as a subselect. Not sure
 what is expected in this ticket?
 >
 > MYSQL results:-
 >
 >
 > {{{
 > >>>
 
Foo.objects.annotate(bname=F('related_bar__name')).update(bar_name=F('bar_name'))
 > SELECT `polls_foo`.`id`
 > FROM `polls_foo`
 > INNER JOIN `polls_bar` ON (`polls_foo`.`related_bar_id` =
 `polls_bar`.`id`) [3.62ms]
 > UPDATE `polls_foo`
 > SET `bar_name` = `polls_foo`.`bar_name`
 > WHERE `polls_foo`.`id` IN (6,7)
 >
 > }}}
 >
 > SQLLite:-
 >
 >
 >
 > {{{
 > >>> Foo.objects.annotate(bname=F('related_bar__name'))
 > SELECT "polls_foo"."id",
 >"polls_foo"."related_bar_id",
 >"polls_foo"."bar_name",
 >"polls_bar"."name" AS "bname"
 > FROM "polls_foo"
 > INNER JOIN "polls_bar" ON ("polls_foo"."related_bar_id" =
 "polls_bar"."id") LIMIT 21 [0.21ms]
 > , ]>
 > >>>
 
Foo.objects.annotate(bname=F('related_bar__name')).update(bar_name=F('bar_name'))
 > BEGIN [0.03ms]
 > UPDATE "polls_foo"
 > SET "bar_name" = "polls_foo"."bar_name"
 > WHERE "polls_foo"."id" IN
 > (SELECT U0."id" AS Col1
 >  FROM "polls_foo" U0
 >  INNER JOIN "polls_bar" U1 ON (U0."related_bar_id" = U1."id"))
 [0.29ms]
 > #7) [0.22ms]
 > }}}

--
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/068.4481c55e3b86baf0db79fd94668553c8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
-+-
 Reporter:  David Sanders|Owner:  PREMANAND
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (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
-+-
Description changed by David Sanders:

Old description:

> If you try to use an annotation which spans tables in an update,
> everything will resolve and attempt to execute but the SQL will fail
> because for MySQL the query is broken into a `SELECT` and then an
> `UPDATE`, but the necessary info is not available in the UPDATE. On
> sqlite3 it generates an `UPDATE` that looks similar but with the `SELECT`
> as a subquery.
>
> Example:
>
> {{{
> class Bar(models.Model):
> name = models.CharField(max_length=32)
>
> class Foo(models.Model):
> related_bar = models.ForeignKey(Bar)
> bar_name = models.CharField(max_length=32)
>
> Foo.objects.annotate(bar_name=F('related_bar__name')).update(name=F('bar_name'))
> }}}
>
> On MySQL produces:
>
> {{{
> SELECT `test_foo`.`id`, `test_bar`.`name` AS `bar_name` FROM `test_foo`
> LEFT OUTER JOIN `test_bar` ON ( `test_foo`.`related_bar` =
> `test_bar`.`id` );
>
> UPDATE `test_foo` SET `bar_name` = `test_bar`.`name` WHERE
> `test_foo`.`id` IN (1);
> }}}
>
> I don't think there's a trivial fix for this. Doing an `UPDATE` with a
> `JOIN` appears to have different syntax in the various implementations
> and isn't possible at all on sqlite3, so it would need to be multiple
> queries.
>
> Seems that until it can be made to work correctly an error should occur
> if attempted, similar to the
> [https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L1098
> error for aggregates] in an `UPDATE`.

New description:

 If you try to use an annotation which spans tables in an update,
 everything will resolve and attempt to execute but the SQL will fail
 because for MySQL the query is broken into a `SELECT` and then an
 `UPDATE`, but the necessary info is not available in the UPDATE. On
 sqlite3 it generates an `UPDATE` that looks similar but with the `SELECT`
 as a subquery.

 Example:

 {{{
 class Bar(models.Model):
 name = models.CharField(max_length=32)

 class Foo(models.Model):
 related_bar = models.ForeignKey(Bar)
 bar_name = models.CharField(max_length=32)

 
Foo.objects.annotate(related_bar_name=F('related_bar__name')).update(bar_name=F('related_bar_name'))
 }}}

 On MySQL produces:

 {{{
 SELECT `test_foo`.`id`, `test_bar`.`name` AS `bar_name` FROM `test_foo`
 LEFT OUTER JOIN `test_bar` ON ( `test_foo`.`related_bar` = `test_bar`.`id`
 );

 UPDATE `test_foo` SET `bar_name` = `test_bar`.`name` WHERE `test_foo`.`id`
 IN (1);
 }}}

 I don't think there's a trivial fix for this. Doing an `UPDATE` with a
 `JOIN` appears to have different syntax in the various implementations and
 isn't possible at all on sqlite3, so it would need to be multiple queries.

 Seems that until it can be made to work correctly an error should occur if
 attempted, similar to the
 
[https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L1098
 error for aggregates] in an `UPDATE`.

--

--
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/068.b9d67a4b9c76b641c436c6d0f9abcca1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
-+-
 Reporter:  dsanders11   |Owner:  PREM1980
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (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 PREM1980):

 Need more clarifications on this.

 SQLLite and Mysql both passes the test. The queries generated are
 different(sqllite has SUBSELECT) but the result of the subselect is same
 as MYSQL.

 Here are the 2 queries thats generated. In MYSQL the select statement is
 generated first and then the results are applied to the update but in
 sqllite is select statement is applied directly as a subselect.

 MYSQL results:-

 >>>
 
Foo.objects.annotate(bname=F('related_bar__name')).update(bar_name=F('bar_name'))
 SELECT `polls_foo`.`id`
 FROM `polls_foo`
 INNER JOIN `polls_bar` ON (`polls_foo`.`related_bar_id` =
 `polls_bar`.`id`) [3.62ms]
 UPDATE `polls_foo`
 SET `bar_name` = `polls_foo`.`bar_name`
 WHERE `polls_foo`.`id` IN (6,7)

 SQLLite:-


 >>> Foo.objects.annotate(bname=F('related_bar__name'))
 SELECT "polls_foo"."id",
"polls_foo"."related_bar_id",
"polls_foo"."bar_name",
"polls_bar"."name" AS "bname"
 FROM "polls_foo"
 INNER JOIN "polls_bar" ON ("polls_foo"."related_bar_id" =
 "polls_bar"."id") LIMIT 21 [0.21ms]
 , ]>
 >>>
 
Foo.objects.annotate(bname=F('related_bar__name')).update(bar_name=F('bar_name'))
 BEGIN [0.03ms]
 UPDATE "polls_foo"
 SET "bar_name" = "polls_foo"."bar_name"
 WHERE "polls_foo"."id" IN
 (SELECT U0."id" AS Col1
  FROM "polls_foo" U0
  INNER JOIN "polls_bar" U1 ON (U0."related_bar_id" = U1."id"))
 [0.29ms]
 #7) [0.22ms]

--
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/068.f020b96f4a9de40b572203f3094061ea%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
-+-
 Reporter:  dsanders11   |Owner:  PREM1980
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (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):

 The idea of this ticket is to fix (or provide a helpful error message if
 the query is impossible) for
 
[https://github.com/django/django/blob/4bc6b939944183533ae74791d21282e613f63a96/tests/update/tests.py#L174-L176
 an existing test that's commented out].

--
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/068.6ae0b865c771f13bddf37d03d3115fd7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
-+-
 Reporter:  dsanders11   |Owner:  PREM1980
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (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 PREM1980):

 I ran against the dev version but the queries generated are something
 different.

 models.py

 {{{
 class Bar(models.Model):
 name = models.CharField(max_length=32)

 class Foo(models.Model):
 related_bar = models.ForeignKey(Bar)
 bar_name = models.CharField(max_length=32)

 }}}

 If you notice the sql that is generated in inner join and not outer join
 as you mentioned.

 {{{
 >>> Foo.objects.annotate(b_name=F('related_bar__name'))
 SELECT `polls_foo`.`id`,
`polls_foo`.`related_bar_id`,
`polls_foo`.`bar_name`,
`polls_bar`.`name` AS `b_name`
 FROM `polls_foo`
 INNER JOIN `polls_bar` ON (`polls_foo`.`related_bar_id` =
 `polls_bar`.`id`) LIMIT 21 [0.34ms]
 }}}



 Can you please clarify on it?

--
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/068.e9644889aa353c7ad8e2d2726f202941%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
-+-
 Reporter:  dsanders11   |Owner:  PREM1980
 Type:  Bug  |   Status:  assigned
Component:  Database layer   |  Version:  master
  (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
-+-
Changes (by PREM1980):

 * owner:  nobody => PREM1980
 * status:  new => assigned


--
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/068.02f0eb5b14b594cd15f51b973cf385b3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
-+-
 Reporter:  dsanders11   |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  master
  (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
-+-
Changes (by timgraham):

 * needs_better_patch:   => 0
 * needs_docs:   => 0
 * needs_tests:   => 0
 * stage:  Unreviewed => Accepted


--
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/068.0abb6aa4bc6b247ecef0970c0dea8967%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL

[Django]

#26539: Using Annotation As Update Parameter Generates Invalid SQL
--+
 Reporter:  dsanders11|  Owner:  nobody
 Type:  Bug   | Status:  new
Component:  Database layer (models, ORM)  |Version:  master
 Severity:  Normal|   Keywords:
 Triage Stage:  Unreviewed|  Has patch:  0
Easy pickings:  0 |  UI/UX:  0
--+
 If you try to use an annotation which spans tables in an update,
 everything will resolve and attempt to execute but the SQL will fail
 because for MySQL the query is broken into a `SELECT` and then an
 `UPDATE`, but the necessary info is not available in the UPDATE. On
 sqlite3 it generates an `UPDATE` that looks similar but with the `SELECT`
 as a subquery.

 Example:

 {{{
 class Bar(models.Model):
 name = models.CharField(max_length=32)

 class Foo(models.Model):
 related_bar = models.ForeignKey(Bar)
 bar_name = models.CharField(max_length=32)

 
Foo.objects.annotate(bar_name=F('related_bar__name')).update(name=F('bar_name'))
 }}}

 On MySQL produces:

 {{{
 SELECT `test_foo`.`id`, `test_bar`.`name` AS `bar_name` FROM `test_foo`
 LEFT OUTER JOIN `test_bar` ON ( `test_foo`.`related_bar` = `test_bar`.`id`
 );

 UPDATE `test_foo` SET `bar_name` = `test_bar`.`name` WHERE `test_foo`.`id`
 IN (1);
 }}}

 I don't think there's a trivial fix for this. Doing an `UPDATE` with a
 `JOIN` appears to have different syntax in the various implementations and
 isn't possible at all on sqlite3, so it would need to be multiple queries.

 Seems that until it can be made to work correctly an error should occur if
 attempted, similar to the
 
[https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L1098
 error for aggregates] in an `UPDATE`.

--
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/053.4470fe623e7973ee1158e0e04b332f1d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.