Red Hat Bugzilla – Bug 972397
sorting by certain combinations of data grid columns generates invalid SQL: Not unique table/alias: 'osmajor'
Last modified: 2014-12-07 20:15:30 EST
Description of problem:
'500 Internal error' happened when clicking some titles
Version-Release number of selected component (if applicable):
devel env Version - 0.12.1
Steps to Reproduce:
1. Distros->All, clicking 'OS Major Version' or 'OS Minor Version' met '500 Internal error.
2. Distros->Trees, clicking 'Arch', 'Distro', 'OS Minor Version' met '500 Internal error'
3. Admin->OS Versions, clicking title 'Alias' return 500 error
4. Systems->All/Available, select all columns, clicking title 'CPU-Model' or 'CPU-Vendor', and 'System-User' met '500 Internal error'.
4. Systems->Free, select all columns, clicking title 'CPU-Model' or 'System-PowerType', and 'System-User' met '500 Internal error'.
500 Internal error
The server encountered an unexpected condition which prevented it from fulfilling the request.
This is because the TG paginate widget joins the sortable column without aliasing it nor checking if the table is already joined.
So there are a couple of problems.
First one is that turbogears.paginate.py does a list expansion of the class mapped attributes. So query.outerjoin(System.cpu, System.user), which is incorrect.
It should be query.outerjoin(System.cpu).outerjoin(System.user).
sqlalchemy seems to be smart enough to determine when a table has already been joined, and perform a noop if it tries to join the same table again and this alone will fix the problem with the above 'Systems' scenario, and
However, from what I can tell this is only true if they are joined in the same manner. For example:
>>>queri2 = session.query(Distro).join(OSVersion, OSMajor)
>>>print queri2.join(Distro.osversion, OSVersion.osmajor)
SELECT ... FROM distro INNER JOIN osversion ON osversion.id = distro.osversion_id INNER JOIN osmajor ON osmajor.id = osversion.osmajor_id INNER JOIN osversion ON osversion.id = distro.osversion_id INNER JOIN osmajor ON osmajor.id = osversion.osmajor_id
>>> queri2 = session.query(Distro).join(Distro.osversion, OSVersion.osmajor)
>>> print queri2.join(Distro.osversion, OSVersion.osmajor)
FROM distro INNER JOIN osversion ON osversion.id = distro.osversion_id INNER JOIN osmajor ON osmajor.id = osversion.osmajor_id
Also what is interesting, is that if, in the second example, the second call is an outerjoin() it is similarly ignored, even though the first was an inner join.
The fix to TG will work in any cases where we are doing joins on a class mapped attribute, but not where we are joining the mapped classes directly.
So just to be clear, I think we should make the TG fix, and we should change the way we do joins to class mapped attribute where appropriate.
I'm assuming this problem has existed since we upgraded to SQLAlchemy 0.6 in Beaker 0.8.0.
test on beaker-devel Version - 0.13.1(20130626)-->fail
on such pages, clicking these column headers still cause 500 error:
1. 'Admin->OS Versions', clicking 'Alias'.
2. 'Distros->All', clicking 'OS Major Version'
3. 'Distros->Trees', clicking 'OS Minor Version'
4. 'Systems->All', clicking 'System-User' and 'CPU-Model'
5. 'Systems->Available', clicking 'CPU-Vendor' and 'System-User'
6. 'Systems->Free', clicking 'System-User' and 'System-PowerType' 'CPU-Model'
Sorry for wasting your time, TG was not upgraded properly. It now is.
verified on beaker-devel Version - 0.13.1(20130627)-->pass
steps as comment0.
Beaker 0.13.2 has been released. (http://beaker-project.org/docs/whats-new/release-0.13.html#beaker-0-13-2).
*** Bug 963825 has been marked as a duplicate of this bug. ***