Understanding deterministic vs non-deterministic functions

[markaddleman]

We have a user defined function that is, in fact, deterministic but was incorrectly marked as non-deterministic.  It takes multiple columns of data and concatenates them in some app-specific way.  When the function was marked as non-deterministic, we found that it was being executed many hundreds of thousands of times in a query that, ultimately, we cancelled because it ran too long.  When it is correctly marked as deterministic, the function is only called a few thousand times and the query returns in a timely manner. My question is, how does Teiid processing differ for deterministic vs non-deterministic functions?  Eyeballing the query plans for deterministic vs non-deterministic, it appears that they're the same but I could be missing something.

Our query is something like this:

SELECT *

FROM t1 JOIN t2 ON t2.c=fn(t1.c1, t1.c2)

(I've simplified the actual query quite a bit for this example but I think it captures what we're trying to do).

[shawkins]

See https://docs.jboss.org/author/display/TEIID/Nondeterministic+Function+Handling

When a function is non-deterministic we make a best effort to evaluate it for every possible row given its place in the plan.  This is typically expected if the non-deterministic function is used in the select clause to project a value, but the behavior does vary by database.  I'd have to compare the full query plan to see if there is an issue.