How to get LIMIT to work as desired w/virtual procedure?

[mkitchin]

Hi there,

Environment:

• JBoss EAP 6.1.0 (bundled with Jboss DV)
• Teiid Version 8.7.1
• MSSQL
• Java 1.7.0/75
• Windows 7/x64

We're trying to query from a RESTful service through to a database, limiting the number of result rows to a given max (e.g., 10). Everything's working, except we can't figure out how/where to inject the LIMIT predicate (translated to TOP by the underlying drivers, I suspect). I've enabled JDBC spying in EAP and included the underlying queries for comparison.

(1) When I have a virtual procedure like this:

---

BEGIN

  SELECT XMLELEMENT(NAME bars, XMLAGG(XMLELEMENT(NAME bar,

  XMLFOREST(my_view.foo.id, my_view.foo.x, my_view.foo.y, my_view.foo.z)

  ))) AS result FROM my_view.foo WHERE my_view.foo.id LIKE my_view.my_proc.id LIMIT 10;

END

...based on the following view:

SELECT

  my_source.foo.id, my_source.foo.x,

  my_source.foo.y, my_source.foo.z

FROM

  my_source.foo;

...we get the following, generated SQL:

SELECT g_0.id, g_0.x, g_0.y, g_0.z

FROM my_source.foo AS g_0

WHERE g_0.id LIKE 'my_input'

...and no apparent maximum conveyed or enforced in the output.

My guess is in this case the LIMIT predicate is applied to the net output of XMLAGG which is always a single row.

(2) If we change the virtual procedure to this:

---

BEGIN

  SELECT XMLELEMENT(NAME bars, XMLAGG(XMLELEMENT(NAME bar,

  XMLFOREST(my_view.foo.id, my_view.foo.x, my_view.foo.y, my_view.foo.z)

  ))) AS result FROM my_view.foo WHERE my_view.foo.id LIKE my_view.my_proc.id;

END

...and the view to this:

SELECT

  my_source.foo.id, my_source.foo.x,

  my_source.foo.y, my_source.foo.z

FROM

  my_source.foo

LIMIT 10;

...we get this:

SELECT v_0.c_0, v_0.c_1, v_0.c_2, v_0.c_3 FROM

  (SELECT g_0.id AS c_0, g_0.x AS c_1, g_0.y AS c_2, g_0.z AS c_3

  FROM my_source.foo AS g_0 LIMIT 10) AS v_0

WHERE v_0.c_0 LIKE 'my_input'

...meaning we're selecting from an arbitrary subset of 10, vs a max of ten matches.

So, what's the best way to convey LIMIT to the underlying query along with the WHERE predicate, short of embedding the whole thing in the virtual procedure (I'd like to map several procedures to common views for caching, etc.)?

[rareddy]

So want to send the LIMIT value dynamically? Definitely you do not want use it on the XMLELEMENT statement. The only thing I can think of now is Dynamic SQL Command Procedure Language - Teiid 8.12 (draft) - Project Documentation Editor

[shawkins]

> My guess is in this case the LIMIT predicate is applied to the net output of XMLAGG which is always a single row.

That is correct.

So you would actually need an inline view to associate the limit after the where, but before the projection:

SELECT XMLELEMENT(NAME bars, XMLAGG(XMLELEMENT(NAME bar,

  XMLFOREST(my_view.foo.id, my_view.foo.x, my_view.foo.y, my_view.foo.z)

  ))) AS result FROM (select * from my_view.foo WHERE my_view.foo.id LIKE my_view.my_proc.id limit 10) as x;

You should also be able use a parameterized limit expression if needed.

[mkitchin]

Hi there,

@steven: Thanks for getting back to me so quickly.

That's what we suspected. Not the end of the world, however -- we've been able to use inline views, as described.

It would be good to be able to cascade such queries more conveniently.

[shawkins]

> It would be good to be able to cascade such queries more conveniently.

Teiid will push limits as much as possible, but it has to respect that the limit operation logically happens last.   So it does make a lot of difference where you place the limit as they all have different intents that we must respect.