Function in ORDER BY clause not being passed through to datamanager

[dnicodemus]

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.

[shawkins]

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

The default for functions is to be deterministic, so without an argument only a single value is assumed for xscore() and the order by is effectively removed.  However I do seen an issue because even if you use:

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

The rewrite will still remove the order by because it doesn't consider the function as pushed at that stage.  Can you log something for that?

[dnicodemus]

Sure, I'll log a ticket for you.

Are you saying that if the function had a parameter it would not be removed from the order by ?

[shawkins]

> Are you saying that if the function had a parameter it would not be removed from the order by ?

As long as it wasn't a static value, yes.

[dnicodemus]

So, it would have to be column from the row then, yes ?

[shawkins]

Yes.  Just a literal would still be seen as a deterministic expression.

[dnicodemus]

Yup, that make sense, as always, thanks for the quick and coherent reply !

Dave

[dnicodemus]

Ticket is here [TEIID-4391] NONDETERMINISTIC functions incorrectly optimized out of ORDER BY - JBoss Issue Tracker