Bug 1252914 - JDG translator has disabled capabilities for GT and LT
JDG translator has disabled capabilities for GT and LT
Status: CLOSED ERRATA
Product: JBoss Data Virtualization 6
Classification: JBoss
Component: Teiid (Show other bugs)
6.2.0
Unspecified Unspecified
unspecified Severity unspecified
: CR1
: 6.2.0
Assigned To: David Le Sage
Filip Elias
: Documentation
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2015-08-12 09:48 EDT by Filip Elias
Modified: 2016-01-27 16:25 EST (History)
4 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
When issuing a query to the JDG data source that contains GT or LT criteria, the GT/LT criteria will not be pushed down to the source for execution. The reason the GT/LT criteria is not pushed down is because JDG handles comparing "null" values different than how SQL is expected to compare. JDG resolves this to TRUE and would include that object in the result set, but SQL would indicate FALSE and excluded the object. As a consequence, pPerformance maybe be slow. If this is the only criteria specified in the query, then Teiid must read all the objects from the cache in order to perform the GT/LT filtering.
Story Points: ---
Clone Of:
Environment:
Last Closed: 2015-10-29 20:40:08 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)


External Trackers
Tracker ID Priority Status Summary Last Updated
JBoss Issue Tracker TEIID-3573 Major Closed Infinispan-dsl-cache translator: Operator <> incorrectly handles NULL values 2016-08-09 01:12 EDT
JBoss Issue Tracker TEIID-3625 Major Closed JDG translator has disabled capabilities for GT and LT 2016-08-09 01:12 EDT

  None (edit)
Description Filip Elias 2015-08-12 09:48:04 EDT
Operations GT and LT are not pushed down to JDG. Indexes are enabled for all columns. Operations GE and LE are pushed down correctly.

Example:

Query:
select  intkey from smalla where intNum >= 5 order by intkey

Plan: 
PROCESSOR PLAN:
AccessNode(0) output=[c.intKey AS IntKey] SELECT g_0.intKey FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.intNum >= 5 ORDER BY g_0.intKey

------------------------------------------------------------------------------

Query:
select  intkey from smalla where intNum > 5 order by intkey

Plan:
SortNode(0) output=[c.intKey AS IntKey] [SORT] [IntKey]
  ProjectNode(1) output=[c.intKey AS IntKey] [c.intKey AS IntKey]
    SelectNode(2) output=[c.intKey] c.intNum > 5
      AccessNode(3) output=[c.intNum, c.intKey] SELECT g_0.intNum, g_0.intKey FROM SmallAs.smallARemotecache AS g_0
Comment 1 JBoss JIRA Server 2015-08-12 13:45:21 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3625 to Resolved
Comment 2 JBoss JIRA Server 2015-08-12 13:46:30 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3625 to Reopened
Comment 3 JBoss JIRA Server 2015-08-12 13:50:05 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3625 to Resolved
Comment 4 Van Halbert 2015-08-13 08:45:28 EDT
I wouldn't consider this a blocker for the release.
Comment 5 Filip Elias 2015-08-13 16:16:19 EDT
Van,

I consider it as a blocker. It is a great perfomance issue. It takes really long to process the request which contains GT or LT operators. It is a regression. GT and LT operators were pushed down correctly in the previous build.

I understand that you would have to backport TEIID-3210, which would probably bring more bugs in the product right now. Is there any other possiblity to fix this issue?
Comment 6 Van Halbert 2015-08-13 16:43:13 EDT
No, its too risky at this point in the cycle to backport teiid engine changes.  Expecially for only 1 translator.
Comment 7 Anne-Louise Tangring 2015-08-17 09:18:34 EDT
We'll see if we can get JDG team to fix this sooner. We'll need to change the default and provide guidance in the docs on how to enable it and what the risks are.
Comment 8 Van Halbert 2015-08-18 11:01:23 EDT
Ok, added the following to the factory:
@TranslatorProperty(display="CompareCriteriaOrdered", description="If true, translator can support comparison criteria with the operator '=>' or '<=' ",advanced=true)
@Override
public boolean supportsCompareCriteriaOrdered()
{ return supportsCompareCriteriaOrdered; }
public boolean setSupportsCompareCriteriaOrdered(boolean supports)
{ return supportsCompareCriteriaOrdered = supports;
}


And tested it by adding translator override to see the GE and LE pushed down:
<translator name="infinispan-cache-dsl1" type="infinispan-cache-dsl">
<property name="supportsCompareCriteriaOrdered" value="true"/>
</translator>

See also:  https://bugzilla.redhat.com/show_bug.cgi?id=1253660
Comment 10 Filip Elias 2015-09-02 10:13:05 EDT
Van,

your fix/workaround is for bug BZ-1253660. It doesn't fix anything for this bug so I set it back to assigned.
Comment 11 Van Halbert 2015-09-02 10:53:00 EDT
So your saying, when the override is applied to the translator, you do not see a GE or LE criteria being pushed down?

Remember, this is still only going to work properly on returning the correct results when the criteria is used on column(s) where it doesn't contain null's.
Comment 12 Filip Elias 2015-09-03 06:44:33 EDT
The override property works as expected, GE and LE operations are pushed down. However this bug is about GT and LT operations which are still not pushed down so I can't verify this bug.
Comment 13 Van Halbert 2015-09-03 12:59:21 EDT
As indicated in TEIID-3573,  the support for NOT had to be turned off because of how JDG handles null's differently than SQL.   So this operation is being handled by Teiid, and unfortunately, the GT and LT do not get pushed down.

Could change the translator to add an override for supportsNotCriteria so that the user could enable push down of the operators.
Comment 14 Filip Elias 2015-09-07 04:34:57 EDT
That's a good idea because I think it is a significant performance issue.
Comment 15 JBoss JIRA Server 2015-10-11 15:49:09 EDT
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3625 to Closed
Comment 16 JBoss JIRA Server 2016-01-27 16:25:05 EST
Steven Hawkins <shawkins@redhat.com> updated the status of jira TEIID-3573 to Closed

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