It is possible that given TEIID-5821 there could be a need for procedure parameter pushdown with an expression needs to be supported as well. This is a long standing issue that was only partially addressed previously. To fully address we need a new capability and a breaking method change to SQLConversionVisitor - it's possible to backport without the breaking change though.

Sources (such as SQL Server) and individual procedures (JDBC direct execution assumes ? parameter sql that requires literal binding) expect only literal parameters. We cannot generally push non-literals unless we add a capability and possibly extension metadata on procedures - alternatively all procedure handling on sources that do support non-literal arguments would need modified.

At this point it seems sufficient to just allow this at a translator language level and mark the issue as partially completed. This can be revisited if critical for performance or if it's the only way to address a particular case.

A little of the work for this was started by TEIID-2572

partial patch

Looked at implementing this. It is more involved than it seems since the planner currently doesn't check pushdown for parameters and quite a few paths assume that parameters will be evaluated. The workaround is to use a scalar subquery that selects the desired value, which is less than ideal since it needs to reference a source table and issues a separate source query for each argument. This can also be implemented in a non-breaking fashion (at least from an api perspective - there would still be cases where custom translators would expect literals, but get Expressions) by just adding a get/setValue to Argument that use Expressions. Because of the workaround and that this is not necessarily a breaking change pulling out of 8.0

In instances where a pushdown only expression is needed as a procedure parameter, this can be seen as a defect. In some circumstances that can be worked around using nested or common table expressions.