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