This service will be undergoing maintenance at 00:00 UTC, 2017-10-23 It is expected to last about 30 minutes
Bug 972397 - sorting by certain combinations of data grid columns generates invalid SQL: Not unique table/alias: 'osmajor'
sorting by certain combinations of data grid columns generates invalid SQL: N...
Status: CLOSED CURRENTRELEASE
Product: Beaker
Classification: Community
Component: web UI (Show other bugs)
0.8
Unspecified Unspecified
unspecified Severity high (vote)
: 0.13.x
: ---
Assigned To: Raymond Mancy
wangjing
:
: 963825 (view as bug list)
Depends On:
Blocks: 990952
  Show dependency treegraph
 
Reported: 2013-06-09 03:02 EDT by wangjing
Modified: 2014-12-07 20:15 EST (History)
9 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
: 990952 (view as bug list)
Environment:
Last Closed: 2013-07-10 22:44:28 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description wangjing 2013-06-09 03:02:14 EDT
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 

How reproducible:
always

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'.

Actual results:
500 Internal error

The server encountered an unexpected condition which prevented it from fulfilling the request.

Expected results:
no errors.

Additional info:
Comment 2 Raymond Mancy 2013-06-10 19:26:01 EDT
This is because the TG paginate widget joins the sortable column without aliasing it nor checking if the table is already joined.
Comment 4 Raymond Mancy 2013-06-12 00:49:30 EDT
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   


VS:

    >>> queri2 = session.query(Distro).join(Distro.osversion, 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

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.
Comment 5 Raymond Mancy 2013-06-12 00:51:09 EDT
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.
Comment 6 Dan Callaghan 2013-06-12 18:32:16 EDT
I'm assuming this problem has existed since we upgraded to SQLAlchemy 0.6 in Beaker 0.8.0.
Comment 9 Nick Coghlan 2013-06-18 01:18:07 EDT
Beaker patch,
http://gerrit.beaker-project.org/#/c/2037/
Comment 12 wangjing 2013-06-26 06:19:04 EDT
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'
Comment 13 Raymond Mancy 2013-06-26 23:02:46 EDT
Sorry for wasting your time, TG was not upgraded properly. It now is.
Comment 14 wangjing 2013-06-27 01:49:06 EDT
verified on beaker-devel Version - 0.13.1(20130627)-->pass

steps as comment0.
Comment 15 Amit Saha 2013-07-10 22:44:28 EDT
Beaker 0.13.2 has been released. (http://beaker-project.org/docs/whats-new/release-0.13.html#beaker-0-13-2).
Comment 16 Dan Callaghan 2013-08-02 02:25:15 EDT
*** Bug 963825 has been marked as a duplicate of this bug. ***

Note You need to log in before you can comment on or make changes to this bug.