Limit and Offset not passed into the translator

[rakeshsagar]

Hi all,

I have the following SQL Query which contains Limit in it. But the Limit and Offset values are not passed into the translator.

SELECT * FROM "DBMzDB2.IDB2_DSQDSETS_Rbase_Vbase" as CHORUS_B  WHERE (CHORUS_B."NUMBER-EXTENTS" = '1' AND CHORUS_B."dsSSID" = 'DF1G' AND CHORUS_B."dsSystem" = 'CA11' AND CHORUS_B."dsGroup" = 'DFG' AND CHORUS_B."dsConf" = 'DEFAULT')   LIMIT 100, 100

I am using Teiid 7.3.

My translator only supports supportsRowLimit() and supportsRowOffset()

Also if I add the supportsCompareCriteriaEquals() then the Limit and Offset is passed to the translator. But I do not want my translator to support the Equal criteria and want teiid to do the filtering.

Please let me know how to solve this problem.

Thanks

Rakesh.

[rareddy]

Rakesh,

I believe pushing Limit to source would yield to wrong results, because the Limit is on the filtered resultset, not over entire data. May be you can use some custom pushdown function that specifies the number of rows to fetch, and you convert the function into Limit clause for source at translator level.

Ramesh..

[shawkins]

The functional approach Ramesh is suggesting could work, but it would have to have a column value as an input so that we would know where to push it.  The straight sql workaround is to use an inline view:

SELECT * FROM (SELECT * FROM "DBMzDB2.IDB2_DSQDSETS_Rbase_Vbase" LIMIT 100, 100) AS CHORUS_B WHERE (CHORUS_B."NUMBER-EXTENTS" = '1' AND CHORUS_B."dsSSID" = 'DF1G' AND CHORUS_B."dsSystem" = 'CA11' AND CHORUS_B."dsGroup" = 'DFG' AND CHORUS_B."dsConf" = 'DEFAULT')  

Here the limit will be applied first.  In 7.6 https://issues.jboss.org/browse/TEIID-1726 we'll introduce a feature for source hints that may have some applicability here as well.

Steve

[rareddy]

Steve,

I was under the impression that the in-line view might re-written by the optimizer, thus back to the original query, is that not the case when a LIMIT is used? or may be when "supports" for criteria is not available?

Ramesh..

[shawkins]

Ramesh,

Yes inline views can be rewritten by the optimizer, but that logic has considerations for limits.  You are partially correct though https://issues.jboss.org/browse/TEIID-1422 prevents this from working the way that I expect.  We allow ourselves to push the criteria below the source node, but then we won't push the limit under the criteria.  That change was really only needed by the crazy rowlimit exception logic in xml, so we could revisit it.

Steve

[shawkins]

To follow up on this, Teiid 7.6 has a new hint and baseline behavior that is applicable to this situation.  https://issues.jboss.org/browse/TEIID-1806 added a NON_STRICT limit hint and ensured our limit handling is consistent.  In 7.6 the inline view approach above would work by default as the criteria will not be allowed to be pushed below the limit node.  You could also write the query as:

SELECT * FROM "DBMzDB2.IDB2_DSQDSETS_Rbase_Vbase" as CHORUS_B  WHERE (CHORUS_B."NUMBER-EXTENTS" = '1' AND CHORUS_B."dsSSID" = 'DF1G' AND CHORUS_B."dsSystem" = 'CA11' AND CHORUS_B."dsGroup" = 'DFG' AND CHORUS_B."dsConf" = 'DEFAULT') /*+ NON_STRICT */ LIMIT 100, 100

to instruct the optimizer that the limit is pushable even if there is interviening dup removal or selection.

Steve