2 Replies Latest reply on Jun 20, 2017 9:24 AM by shawkins

    Usage of 'Order by' and 'Offset' in teiid query together with offset other than 0

    cm.kumar
    cm.kumar Jun 20, 2017 5:30 AM

      Issue: Accessing VDB view with order by and offset, with offset other than zero, throws an exception. The query is not getting translated properly or the translated query is not correct.

       

      Details:

      1. Query formed is like SELECT * FROM PDM.FACILITY ORDER BY FACILITY_NAME OFFSET  1 ROWS FETCH NEXT 1000 ROWS ONLY, where PDM is the vdb name and FACILITY is the view defined in the VDB, FACILITY_NAME is the column.

      2. Teiid translator involved 9.1.1

      3. VDB is connected to SQL server 2008 R2, meaning the VDB view gets the data from SQL table.

      4. The same query mentioned in details section, works with offset 0.

      5. Used Squirrel client 3.7.1 to execute these queries connecting to the VDB, via JDBC.

      6. Without using 'Order by', the same query works even if offset is set to other numbers. But then 'Order by' has to be set to fetch 1000 or odd records in an order for a run.

      7. Same query with offset 0 or other number with order by, works with teiid translator 8.7.

       

      Question:

      1. What are all the causes of failure, when 'offset' is set to other number than 0?

      2. Is there a work around on usage of 'ORDER BY and OFFSET' together?