6 Replies Latest reply on Sep 30, 2010 4:07 PM by shawkins

    Passing WHERE expression to translator (col1 + col2) > value

    chazware

      I can't figure out how to get Teiid to pass a WHERE expression to my translator when it contains any arithmetic.

       

      The example I am trying is a single table with two numeric columns and my client program wants to ask for rows like the following:

       

         select col1, col2 from table where (col1 + col2) > 1000;

       

      I cannot get Teiid to pass this WHERE clause to my translator.  What does my ExecutionFactory need to say it supports.  I already say I support the numeric operators and it also returns true for:

         supportsCompareCriteriaEquals()

         supportsCompareCriteriaOrdered()

         supportsIsNullCriteria()

         supportsNotCriteria()

       

      What am I missing?

        • 1. Re: Passing WHERE expression to translator (col1 + col2) > value
          shawkins

          Hi Charles,

           

          Just as you indicate, you would at least need supportsCompareCriteriaOrdered(), and function support for "+".  What may be happening is if col1 and col2 are of different numeric types then an implicit convert function is being used to make them the same type.  In that case you would also need to have support for the "convert" function.

           

          I'll make sure to add more detail logging to 7.2 alpha2 so that it's easier to see what is failing pushdown.

           

          Steve

          • 2. Re: Passing WHERE expression to translator (col1 + col2) > value
            chazware

            Thanks for the "convert" tip.  I can see how that might be necessary in some cases, but in my specific case the 2 columns were Integer (4 bytes) and SmallInt (2 bytes) and I would expect Teiid to assume that would be an implicit conversion that should be supported by any translator that supports arithmetic.

             

            In researching CONVERT() I found a lot of variations of what is supported by different SQL engines.  Is there some definition of what Teiid expects from a translator that says it supports the CONVERT() function?

             

            P.S., You mentioned adding more tracing in 7.2, but is there tracing in 7.0 of the parsing and pushdown processing and how do I turn on the detailed tracing?

            • 3. Re: Passing WHERE expression to translator (col1 + col2) > value
              shawkins

              Teiid's functions are generally modeled after the Open Group CLI functions, which defines convert as a type conversion function.  Teiid expects translator function support to match Teiid's definition.  Even if a database has a different form of the convert function, it's still fine to have support and translate the Teiid convert into cast functions or whatever is appropriate for the source.

               

              For 7.0 there isn't really a good way to see what is going on.  If you get the debug log for the query plan you'll see that the access node is not being raised above the criteria node, but there isn't really enough information to tell you why.  Implicit converts remain hidden until they are pushed down and then, as you would expect, in many instances are then just dropped from the source query.  A debugger may be the best bet.

               

              There is definitely some room for improvement regarding implicit converts.  They are more for internal canonicallization than something we are expecting explicit source support for.  But they do ensure that the source is aware of the exact type expectations and we generally cannot assume that every source supports all implicit conversions.

               

              Steve

              • 4. Re: Passing WHERE expression to translator (col1 + col2) > value
                chazware

                Thanks again for the quick response.

                 

                I guess I will have to implement convert() in my Translator based on the various docs and play around in the debugger with what I get.  No doubt some CYA code will be in order.

                • 5. Re: Passing WHERE expression to translator (col1 + col2) > value
                  chazware

                  I had to where a different hat for while but I'm back now, at least part time.

                   

                  I now realize that the CONVERT function you are talking about is NOT the CONVERT function that appears in SQL syntax, but is an internal function defined by the Open Group CLI as defined at: http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Database/Open_Group_CLI_Conversion_Functions.

                   

                  Unfortunately that is a not a Teiid definition.  My guess is that in Teiid the "value" would be a language object that could be an expression or column reference and the "SQLtype" would a Java Class reference.  Yes?

                  • 6. Re: Passing WHERE expression to translator (col1 + col2) > value
                    shawkins

                    Convert, due to legacy, is a little coerced.  The actual pushdown function will have a string argument representing the type keyword.  The return type of the function will then be the Class matching the expected type.  See the http://docs.jboss.org/teiid/7.1.0.Final/apidocs/org/teiid/translator/jdbc/ConvertModifier.html and associated source to see our approach to convert handling in JDBC.