Bug 972397
| Summary: | sorting by certain combinations of data grid columns generates invalid SQL: Not unique table/alias: 'osmajor' | |||
|---|---|---|---|---|
| Product: | [Retired] Beaker | Reporter: | wangjing <jingwang> | |
| Component: | web UI | Assignee: | Raymond Mancy <rmancy> | |
| Status: | CLOSED CURRENTRELEASE | QA Contact: | wangjing <jingwang> | |
| Severity: | high | Docs Contact: | ||
| Priority: | unspecified | |||
| Version: | 0.8 | CC: | asaha, dcallagh, ebaak, jkortus, llim, qwan, rglasz, rmancy | |
| Target Milestone: | 0.13.x | |||
| Target Release: | --- | |||
| Hardware: | Unspecified | |||
| OS: | Unspecified | |||
| Whiteboard: | ||||
| Fixed In Version: | Doc Type: | Bug Fix | ||
| Doc Text: | Story Points: | --- | ||
| Clone Of: | ||||
| : | 990952 (view as bug list) | Environment: | ||
| Last Closed: | 2013-07-11 02:44:28 UTC | Type: | Bug | |
| Regression: | --- | Mount Type: | --- | |
| Documentation: | --- | CRM: | ||
| Verified Versions: | Category: | --- | ||
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | ||
| Cloudforms Team: | --- | Target Upstream Version: | ||
| Embargoed: | ||||
| Bug Depends On: | ||||
| Bug Blocks: | 990952 | |||
|
Description
wangjing
2013-06-09 07:02:14 UTC
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
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.
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. Beaker patch, http://gerrit.beaker-project.org/#/c/2037/ 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. *** |