7 Replies Latest reply on Aug 17, 2016 4:52 PM by Dave Nicodemus

    Function in ORDER BY clause not being passed through to datamanager

    Dave Nicodemus Newbie

      Good day,

       

      We are using the Teiid embedded server with a VDB XML file. I have a HSQL DB with two custom functions defined. My VDB file defines the functions as follows:

       

      CREATE FOREIGN FUNCTION XSCORE()           RETURNS FLOAT  OPTIONS (NAMEINSOURCE '"app1".XSCORE');

      CREATE FOREIGN FUNCTION XSEARCH(P  string) RETURNS string OPTIONS (NAMEINSOURCE '"app1".XSEARCH');

       

      When I execute the following query :

      SELECT xscore(), name, comment FROM customer WHERE comment=xsearch('ferocious)') ORDER BY xscore() ASC;

       

      The records are returned un-sorted. I have debugged this to the point where I can see that Teiid is not passing the order by through to HSQL. But, if I change the query so that the score function is used in a simple expression that involves a field from the table :

       

      SELECT (xscore() + custkey - custkey) as xs, name, comment FROM  customer WHERE comment=xsearch('ferocious)') ORDER BY xs ASC;

       

      then the expression is passed through to HSQL and sorted correctly.

       

      This looks like a  bug to me. I don't see why the order by express is not being passed through to the datamanager. in the first case.

       

      Thanks for any help anyone can provide,

      Dave N.