-
1. Re: Function pushdown in WHERE clause
rareddy Oct 29, 2013 6:40 PM (in response to markaddleman)That is not true, if the translator supports criteria and supports the given function (system or user defined) then it is eligible for pushdown. See getSupportedFunctions method on the ExecutionFactory
-
2. Re: Function pushdown in WHERE clause
markaddleman Oct 30, 2013 3:11 PM (in response to rareddy)Hi, Ramesh -
I have attached some sample code to my original post (it doesn't look like the new forum software allows attaching to replies). It executes two queries. First is SELECT * FROM t.t WHERE t.ts=now() and the second is SELECT now(), ts FROM t.t. With the execution factory's supports* and getSupportedFunction(), now() is not pushed in either case. What am I doing wrong?
One more piece of info: I'm running against 8.6-Alpha2
-
3. Re: Function pushdown in WHERE clause
shawkins Oct 31, 2013 8:43 AM (in response to markaddleman)Mark,
The handling of non-deterministic functions is somewhat complicated. In this case the now function is considered command deterministic meaning that it should be evaluated to the same value for the whole command execution. Teiid feels free then to substitute in the evaluated form as a final potential optimization in case the command can be further simplified. A fully non-deterministic function (rand or a non-deterministic udf) on the other hand would get pushed down.
Steve
-
4. Re: Function pushdown in WHERE clause
markaddleman Nov 1, 2013 2:02 PM (in response to shawkins)I still can't get it work the way I want. Attached is my new sample code. I've created a virtual model containing reference to a Java function for my own, non-deterministic custom_now() function. The target translator supports custom_now but it's not being pushed down. Does there need to be a new DDL option for PUSHDOWN MUST, CAN, and CANNOT?
-
5. Re: Function pushdown in WHERE clause
shawkins Nov 1, 2013 3:07 PM (in response to markaddleman)Mark,
Since it's not a system function, you need to use the fully qualified name chorusTimestampModel.custom_now in the translator supports. I'll have to double check where that is specified in the docs - at the least I'll update the Javadoc.
Steve
-
6. Re: Function pushdown in WHERE clause
markaddleman Nov 1, 2013 3:11 PM (in response to shawkins)That did it. Thanks!
I didn't bother reading the docs carefully before posting so if it's there, I would have missed it anyway
-
7. Re: Function pushdown in WHERE clause
shawkins Nov 4, 2013 9:41 AM (in response to markaddleman)We're missing some usability when it comes to virtual UDFs. One thing that we could add is a comma separated translator property of supported user defined functions so that you don't have to create a translator extension. The assumption has been that the function may need some custom handling in its syntax, types, etc. so an extension wasn't too unwarranted. But there is definitely an extra step when you just want the default handling.
With the introduction of native-query metadata for override syntax directly on a source function, we may also need a concept of mapping the virtual udf to a source function - this may generally supersede the approach above depending upon how it would be implemented (for example an extension metadata property that is a list of fully qualified source function names).
-
8. Re: Function pushdown in WHERE clause
markaddleman Nov 4, 2013 11:21 AM (in response to shawkins)While you've got your requirements analysis hat on, I'll throw this one at you: The current behavior isn't exactly what I want. Although I can declare the UDF as non-deterministic and supported by the translator, thus it gets pushed down, I also want Teiid to evaluate the function for translators that do not support it. In effect, translators that support custom_now() should see SELECT blah FROM blah WHERE ts=custom_now(). Translators that don't support custom_now() should see SELECT blah FROM blah WHERE ts={ts '...'}. Currently, translators that don't support custom_now() see SELECT blah FROM blah.
You may be alluding to the same situation:
One thing that we could add is a comma separated translator property of supported user defined functions so that you don't have to create a translator extension. The assumption has been that the function may need some custom handling in its syntax, types, etc. so an extension wasn't too unwarranted. But there is definitely an extra step when you just want the default handling.
With the introduction of native-query metadata for override syntax directly on a source function, we may also need a concept of mapping the virtual udf to a source function - this may generally supersede the approach above depending upon how it would be implemented
I can see that although, in our case, we're trying to (ab)use the function as a sentinel. I'm thinking we're just trying to push it too far and we should take another problem like a source-hint or a marker in the payload.
-
9. Re: Function pushdown in WHERE clause
shawkins Nov 4, 2013 11:42 AM (in response to markaddleman)> Translators that don't support custom_now() should see SELECT blah FROM blah WHERE ts={ts '...'}. Currently, translators that don't support custom_now() see SELECT blah FROM blah.
Currently we require fully non-deterministic functions to be pushed down to be pre-evaluated. Otherwise we assume that a row-by-row value is needed.
There is some variation in handling between databases and users have requested specific behavior of Teiid. For example many databases (sql server) simply pre-evaluate all functions when possible, while others (such as oracle) use a similar per-row behave. Users have specifically requested push-down support to preserve the db behavior and per-row evaluation in Teiid.
So that leaves getting the exact handling you would want as something that isn't covered.
-
10. Re: Function pushdown in WHERE clause
markaddleman Nov 4, 2013 3:00 PM (in response to shawkins)There is some variation in handling between databases and users have requested specific behavior of Teiid. For example many databases (sql server) simply pre-evaluate all functions when possible, while others (such as oracle) use a similar per-row behave. Users have specifically requested push-down support to preserve the db behavior and per-row evaluation in Teiid.
Something like a source deterministic option? I'll write up a JIRA if you're interested.
-
11. Re: Function pushdown in WHERE clause
shawkins Nov 4, 2013 3:32 PM (in response to markaddleman)You can log something, but adding another determinism level between command deterministic and non-determinisitic would seem to just complicate things further and fairly specific to this usage scenario. Other workarounds would be to add support to the sources that lack now() support. Or to force push down using a column argument - now(col) - but call with a null against sources that don't support - now(null).
-
12. Re: Function pushdown in WHERE clause
markaddleman Nov 5, 2013 8:55 AM (in response to shawkins)I'm not going to log anything. We'll address this using hints probably. Thanks, Steven