Re: [Django] #31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"

[Django]

#31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"
-+-
 Reporter:  StefanosChaliasos|Owner:  nobody
 Type:  Uncategorized|   Status:  closed
Component:  Database layer   |  Version:
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Adam (Chainz) Johnson):

 I agree, this is a MySQL/MariaDB bug, and there's not much we can do about
 it. Any workaround we'd introduce would be invalidated if/when either of
 them fix it, and would be more complex than the user-level workaround of
 adding the field to the `values()` selection.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/075.7d015e17645ced6b12b69d17be47959e%40djangoproject.com.

Re: [Django] #31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"

[Django]

#31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"
-+-
 Reporter:  StefanosChaliasos|Owner:  nobody
 Type:  Uncategorized|   Status:  closed
Component:  Database layer   |  Version:
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * cc: Adam (Chainz) Johnson (added)


Comment:

 > Django produces a query that is invalid according to SQL standard.

 I'm sorry but I don't see this. IMO the main issue is that MySQL doesn't
 recognize that `f1` is included in the `GROUP BY` clause.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/075.1eb4f7e23bcb33f9ddc94a7fec101476%40djangoproject.com.

Re: [Django] #31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"

[Django]

#31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"
-+-
 Reporter:  StefanosChaliasos|Owner:  nobody
 Type:  Uncategorized|   Status:  closed
Component:  Database layer   |  Version:
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by StefanosChaliasos):

 Hello again,

 Base on this MySQL report [https://bugs.mysql.com/bug.php?id=100094],
 Django produces a query that is invalid according to SQL standard.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/075.b18fd5c32dc463a2f652f611cc73b8d5%40djangoproject.com.

Re: [Django] #31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"

[Django]

#31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"
-+-
 Reporter:  StefanosChaliasos|Owner:  nobody
 Type:  Uncategorized|   Status:  closed
Component:  Database layer   |  Version:
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  invalid
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by felixxm):

 * status:  new => closed
 * resolution:   => invalid
 * component:  Uncategorized => Database layer (models, ORM)


Comment:

 IMO it's an issue in MySQL not in Django itself, see
 [https://bugs.mysql.com/bug.php?id=78395 Bug #78395]. Expression is
 included in the `GROUP BY` clause so it should work properly according to
 MySQL docs:

 > The SQL standard requires that HAVING must reference only columns in the
 GROUP BY clause or columns used in aggregate functions.

 As a workaround you can add `f1` to the selected columns `.values('f1',
 'expr')`.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/075.26107b531b1b1693f0adc2a82d741851%40djangoproject.com.

[Django] #31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"

[Django]

#31917: MySQL Operational Error: "Unknown column 'x' in 'having clause'"
-+
   Reporter:  StefanosChaliasos  |  Owner:  nobody
   Type:  Uncategorized  | Status:  new
  Component:  Uncategorized  |Version:
   Severity:  Normal |   Keywords:
   Triage Stage:  Unreviewed |  Has patch:  0
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+
 I have the following model.


 {{{
 class Example(models.Model):
 id = models.AutoField(primary_key=True,blank=True, null=True)
 f1 = models.IntegerField(blank=True, null=True)
 f2 = models.IntegerField(blank=True, null=True)

 class Meta:
 managed = False
 db_table = 'example'
 }}}

 Based on this model, I perform the following query in a MySQL backend.


 {{{
 x = 5
 expr = Sum(F('f2'))
 o = Example.objects.using('mysql')
 q = o.values('id',
 'f1').annotate(expr=expr).order_by('id','expr','-f1',).filter(expr__gt=(F('f1')
 + Value(x))).values('expr').first()
 }}}

 Unfortunately, this query crashes with an OperationalError exception. The
 track trace is


 {{{
 Traceback (most recent call last):
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/backends/utils.py", line 84, in
 _execute
 return self.cursor.execute(sql, params)
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/backends/mysql/base.py", line 73,
 in execute
 return self.cursor.execute(query, args)
   File "/home/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line
 206, in execute
 res = self._query(query)
   File "/home/.env/lib/python3.6/site-packages/MySQLdb/cursors.py", line
 319, in _query
 db.query(q)
   File "/home/.env/lib/python3.6/site-packages/MySQLdb/connections.py",
 line 259, in query
 _mysql.connection.query(self, query)
 MySQLdb._exceptions.OperationalError: (1054, "Unknown column 'example.f1'
 in 'having clause'")

 The above exception was the direct cause of the following exception:

 Traceback (most recent call last):
   File "example.py", line 25, in 
 q = o.values('id',
 'f1').annotate(expr=expr).order_by('id','expr','-f1',).filter(expr__gt=(F('f1')
 + Value(x))).values('expr').first()
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/models/query.py", line 678, in
 first
 for obj in (self if self.ordered else self.order_by('pk'))[:1]:
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/models/query.py", line 287, in
 __iter__
 self._fetch_all()
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/models/query.py", line 1316, in
 _fetch_all
 self._result_cache = list(self._iterable_class(self))
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/models/query.py", line 111, in
 __iter__
 for row in compiler.results_iter(chunked_fetch=self.chunked_fetch,
 chunk_size=self.chunk_size):
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/models/sql/compiler.py", line
 1115, in results_iter
 results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch,
 chunk_size=chunk_size)
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/models/sql/compiler.py", line
 1160, in execute_sql
 cursor.execute(sql, params)
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/backends/utils.py", line 98, in
 execute
 return super().execute(sql, params)
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/backends/utils.py", line 66, in
 execute
 return self._execute_with_wrappers(sql, params, many=False,
 executor=self._execute)
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/backends/utils.py", line 75, in
 _execute_with_wrappers
 return executor(sql, params, many, context)
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/backends/utils.py", line 84, in
 _execute
 return self.cursor.execute(sql, params)
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/utils.py", line 90, in __exit__
 raise dj_exc_value.with_traceback(traceback) from exc_value
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/backends/utils.py", line 84, in
 _execute
 return self.cursor.execute(sql, params)
   File "/home/.env/lib/python3.6/site-
 packages/Django-3.2-py3.6.egg/django/db/backends/mysql/base.py", line 73,
 in execute
 return self.cursor.execute(query, args)
   File "/home/.env/lib/python3.6/site-packages/MySQLdb/cursors.py",