Bug 616634

Summary: possible issues with CriteriaQueryGenerator ordering results by nullable, path expression
Product: [Other] RHQ Project Reporter: Joseph Marques <jmarques>
Component: Core UIAssignee: Joseph Marques <jmarques>
Status: CLOSED CURRENTRELEASE QA Contact: Corey Welton <cwelton>
Severity: medium Docs Contact:
Priority: high    
Version: 3.0.0CC: jsefler
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: Linux   
Whiteboard:
Fixed In Version: 2.4 Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2010-08-12 16:44:30 UTC Type: ---
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: 601949    

Description Joseph Marques 2010-07-21 03:01:45 UTC
The scenario here is that the CQG is told to sort the results using a path expression that causes an implicit join to another table (when hibernate does the SQL query generation).  Issues arise if that path expression may be null, because the sort would cause an implicit filtering to take place.

Example query:

SELECT rg
From ResourceGroup rg

Which will generate the corresponding count-query:

SELECT count(rg)
From ResourceGroup rg

If the user then sorts these groups by their resource type:

SELECT rg
From ResourceGroup rg
ORDER BY rg.resourceType.name

This cause an implicit **INNER** join to the ResourceType entity, in effect removing any mixed groups from the results, and only returning the compatible groups to the user.

However, the CQG will generate the following count-query:

SELECT count(rg)
From ResourceGroup rg

Because the 'ORDER BY' clause is not taken into consideration when generating count queries.  Thus, the cardinality of the result set from the data-query is not the same as the number of items returned from the count-query.

-----

This is currently being seen by QA automation.  They have 9 compatible groups, 2 mixed groups.  When you sort by type or plugin columns, the data-query returns 9 items (implicitly filtering out the mixed groups) but the count-query returns 2 items.

The PagedListDataModel detects this abnormality when it tries to access the 10th position of the result set.  It presumes that the PageControl passed to it from the UI must have been bad, throws it away, and creates a new one mapped to the 1st page with a size of 15 items, re-executes the data-query again, and this time retrieves 11 rows (so now the 10th and 11th items exist).

However, the JSF framework has already accessed the data for the first 9 positions, and so it only accesses the newly queried result set for its last 2 data items.  Because of this, the first 9 will appear in sorted order, but the last 2 won't.  In fact, one or both of the last two may be row items already rendered as part of the first 9.

Comment 1 Joseph Marques 2010-07-21 03:37:45 UTC
This issue is known to occur in the following cases for Criteria objects:

ResourceCriteria
   parentResourceName (sortOverrides)

ResourceGroupCriteria
   resourceTypeName (sortOverrides)

-----

However, since the QCG allows arbitrary control of the generated PageControl object which then is used to construct the 'ORDER BY' clause, we need to check UI paths that use criteria objects with explicitly overridden PCs to see if they would exploit this issue in more ways.

As seen by QA, and as discussed above, this definitely happens when sorting on the group browser.

/rhq/inventory/browseGroups.xhtml
   "Group Type" column maps to resourceType.name   path filter
   "Plugin"     column maps to resourceType.plugin path filter

Comment 2 Joseph Marques 2010-07-21 03:39:40 UTC
My recommendation would be to remove the ability to sort groups by type/plugin for the JON 2.4 release.  After release, we can look into fixing the query generation strategy in the CQG, such that it always generates a count-query whose integer result represents the exact cardinality of the corresponding data-query.

Comment 3 Joseph Marques 2010-07-21 03:43:12 UTC
With a much more powerful SearchBar for groups in JON 2.4 the necessity to sort is significantly diminished.  Considering users can filter by type/plugin, using substring, case-insensitive matching on each of those names, complex AND/OR expressions, I don't think we're taking much away at the cost of ensuring users won't see oddities when using a very prominent feature.

Comment 4 Joseph Marques 2010-07-22 05:26:18 UTC
commit 26c62395c00fd7917693a3a103298428f2f1c86e (master)
Author: Joseph Marques <joseph>
Date:   Thu Jul 22 01:23:18 2010 -0400

BZ-616634: support criteria-based sorting by nullable path expressions
    
* for each ordering token, decide whether it's a path expression
* if it is, determine if it has at least 3 parts (criteriaAlias + path + leaf)
* if it does, add left join for part that precedes the leaf, and generate an alias for this path
** only add the join clause if it doesn't already exist in the generated query
* finally, add the order by clause, with the generated path aliases as necessary

Comment 5 Charles Crouch 2010-07-26 19:28:45 UTC
For 2.4, the change is to test that you can't sort groups by type/plugin on the resource browser page

Comment 6 Corey Welton 2010-07-26 19:58:08 UTC
QA Verified.

Comment 7 Corey Welton 2010-08-12 16:44:30 UTC
Mass-closure of verified bugs against JON.