sql error (1054, "Unknown column 'scripts_script.id' in 'on clause'")

[sergo]

I'm using django-tagging and django-multilingual applications in my project. I don't know if the problem is in these packages or it is more general. Google gives this ​http://bugs.mysql.com/bug.php?id=16190 , so I thought it is more general problem.

Error page says:

Environment:

Request Method: GET
Request URL: http://127.0.0.1:8000/scripts/tag/material/
Django Version: 1.1 pre-alpha SVN-9285
Python Version: 2.5.2
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.sites',
 'django.contrib.admin',
 'django.contrib.markup',
 'sergepogosyan.mytags',
 'sergepogosyan.articles',
 'sergepogosyan.scripts',
 'sergepogosyan.about',
 'sergepogosyan.gallery',
 'django.contrib.comments',
 'tagging',
 'multilingual',
 'multilingual.flatpages',
 'localeurl']
Installed Middleware:
('django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'localeurl.middleware.LocaleURLMiddleware',
 'multilingual.middleware.DefaultLanguageMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.middleware.doc.XViewMiddleware',
 'multilingual.flatpages.middleware.FlatpageFallbackMiddleware')


Traceback:
File "C:\Python25\lib\site-packages\django\core\handlers\base.py" in get_response
  86.                 response = callback(request, *callback_args, **callback_kwargs)
File "C:\Python25\lib\site-packages\tagging\views.py" in tagged_object_list
  52.     return object_list(request, queryset, **kwargs)
File "C:\Python25\lib\site-packages\django\views\generic\list_detail.py" in object_list
  60.             page_obj = paginator.page(page_number)
File "C:\Python25\lib\site-packages\django\core\paginator.py" in page
  37.         number = self.validate_number(number)
File "C:\Python25\lib\site-packages\django\core\paginator.py" in validate_number
  28.         if number > self.num_pages:
File "C:\Python25\lib\site-packages\django\core\paginator.py" in _get_num_pages
  60.             if self.count == 0 and not self.allow_empty_first_page:
File "C:\Python25\lib\site-packages\django\core\paginator.py" in _get_count
  48.                 self._count = self.object_list.count()
File "C:\Python25\lib\site-packages\django\db\models\query.py" in count
  296.         return self.query.get_count()
File "C:\Python25\lib\site-packages\django\db\models\sql\query.py" in get_count
  237.         data = obj.execute_sql(SINGLE)
File "C:\Python25\lib\site-packages\django\db\models\sql\query.py" in execute_sql
  1734.         cursor.execute(sql, params)
File "C:\Python25\lib\site-packages\django\db\backends\util.py" in execute
  19.             return self.cursor.execute(sql, params)
File "C:\Python25\lib\site-packages\django\db\backends\mysql\base.py" in execute
  83.             return self.cursor.execute(query, args)
File "C:\Python25\lib\site-packages\MySQLdb\cursors.py" in execute
  166.             self.errorhandler(self, exc, value)
File "C:\Python25\lib\site-packages\MySQLdb\connections.py" in defaulterrorhandler
  35.     raise errorclass, errorvalue

Exception Type: OperationalError at /scripts/tag/material/
Exception Value: (1054, "Unknown column 'scripts_script.id' in 'on clause'")

[Ramiro Morales]

(reformatted description)

[Karen Tracey]

I see you put more info in the issue you filed over at django-multilingual: ​http://code.google.com/p/django-multilingual/issues/detail?id=72

specifically the query that causes the error. It is related to that MySQL bug you referenced (which is closed not a bug) -- the problem is that MySQL does not accept a query like this (wrapped for some measure of readability):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT COUNT(*) FROM `proj_project` , `tagging_taggeditem`
       LEFT JOIN `proj_project_translation` AS `proj_project_translation_en`
       ON ((`proj_project_translation_en`.master_id = `proj_project`.`id`)
       AND (`proj_project_translation_en`.language_id = 1))
       LEFT JOIN `proj_project_translation` AS `proj_project_translation_fr`
       ON ((`proj_project_translation_fr`.master_id = `proj_project`.`id`)
       AND (`proj_project_translation_fr`.language_id = 2))
       WHERE `tagging_taggeditem`.content_type_id = 32
       AND `tagging_taggeditem`.tag_id = 1
       AND `proj_project`.`id` = `tagging_taggeditem`.object_id;
ERROR 1054 (42S22): Unknown column 'proj_project.id' in 'on clause'

rather it requires the first tables after the FROM to be enclosed in ():

mysql> SELECT COUNT(*) FROM (`proj_project` , `tagging_taggeditem`)
       LEFT JOIN `proj_project_translation` AS `proj_project_translation_en`
       ON ((`proj_project_translation_en`.master_id = `proj_project`.`id`)
       AND (`proj_project_translation_en`.language_id = 1))
       LEFT JOIN `proj_project_translation` AS `proj_project_translation_fr`
       ON ((`proj_project_translation_fr`.master_id = `proj_project`.`id`)
       AND (`proj_project_translation_fr`.language_id = 2))
       WHERE `tagging_taggeditem`.content_type_id = 32
       AND `tagging_taggeditem`.tag_id = 1
       AND `proj_project`.`id` = `tagging_taggeditem`.object_id;
+----------+
| COUNT(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

I believe it is django-multilingual that is adding all the LEFT JOIN stuff that is causing the problem, based on this note from the MySQL doc referenced in the MySQL bug:

"Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are."

Whether django-multilingual has control wrapping parens around the first tables specified in the query is something I don't know, so I can't say whether this is strictly a django-multilingual problem or a Django one. I also don't know if this is just a MySQL issue or affects other DBs -- I ran into trouble duplicating the setup on a different DB (it appears you cannot create a new tag once you've added django-multilingual into the mix), and I've run out of time to spend on this now. My initial impression is the combo of these two apps is pretty fragile.

[sergo]

After some debugging I've found the exact place where "FROM proj_project , tagging_taggeditem ..." part is composing.
It is in the django/db/models/sql/query.py file, get_from_clause(self) function, line 562.

I've changed:
connector = not first and ', ' or
to:
connector = not first and 'JOIN ' or
so "FROM proj_project , tagging_taggeditem" piece became "FROM proj_project JOIN tagging_taggeditem" and tagging started to work.

This patch resolve my particular problem with tagging+multilingual applications, so I don't know if it's a bug in Django, or it fixes somehow tagging/multilingual's problem.