4 Replies Latest reply on Aug 18, 2015 1:57 PM by Steven Hawkins

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

    Michael Kitchin Newbie

      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.)?