12 Replies Latest reply on Nov 5, 2013 8:55 AM by Mark Addleman

    Function pushdown in WHERE clause

    Mark Addleman Master

      We have a translator that must conditionally make a data source API call depending on whether a query argument is for current data or historical data.  If the client query looks like SELECT * FROM t WHERE ts BETWEEN timestampadd(now(), -30 minutes) AND now(), it should go after current data but if the client query looks like SELECT * FROM t WHERE ts BETWEEN 1/10/2013 AND 1/10/2013, it should go after historical data.  We were hoping that we could push down the now() function to the translator so it could use that as a sentinel to determine current vs historical.  It looks like functions are only pushed down in select expressions and aren't pushed down in where clauses.  Is that right or am I missing something in the execution factory?

       

      Message was edited by: Mark Addleman - Posting test case

       

      Message was edited by: Mark Addleman added new sample code

        • 1. Re: Function pushdown in WHERE clause
          Ramesh Reddy Master

          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
            Mark Addleman Master

            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
              Steven Hawkins Master

              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
                Mark Addleman Master

                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
                  Steven Hawkins Master

                  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
                    Mark Addleman Master

                    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
                      Steven Hawkins Master

                      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
                        Mark Addleman Master

                        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
                          Steven Hawkins Master

                          > 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
                            Mark Addleman Master

                            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
                              Steven Hawkins Master

                              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
                                Mark Addleman Master

                                I'm not going to log anything.  We'll address this using hints probably.  Thanks, Steven