"cannot be pushed to source" error when calling source function, with parameters referred to table columns.

[cshong]

Teiid version: 12.0

Source database: Microsoft SQL Server 2014

Translator: sqlserver

Lets assume, in Microsoft SQL server, I have a source function dbo.examplefunc(uniqueidentifier, date, bit). The data type of first parameter is uniqueidentifier (https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-2014), the data type of second parameter is date, and the data type of third parameter is bit for boolean value. "dbo" is the schema name. The function return a data type, which the java equivalent is bigdecimal.

Lets assume, in my VDB, I have the following DDL command:

CREATE FOREIGN FUNCTION "dbo.examplefunc"(col1 string, col2 date, col3 boolean) returns bigdecimal;

I tried to run the following queries:

Query 1 (success without issue):

SELECT MODEL1."dbo.examplefunc"('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx', '2019-04-02', TRUE)

Query 2 (Failed with error):

SELECT MODEL1."dbo.examplefunc"(TAB1.STRINGCOL, TAB1.DATE, TRUE) FROM MODEL2.TABLE1 TAB1

Query 1, which the function parameter was directly typed in by me and never refer to column of another table, run successfully without issue.

In query 2, I passed values of columns of another table as function parameters, and I am sure that the table column data type match the function parameter data type. But, the query failed with error "cannot be pushed to source".

Any solution?

Note: The above model names, table names, column names, function names, and parameter names are not real names.

[shawkins]

> In query 2, I passed values of columns of another table as function parameters, and I am sure that the table column data type match the function parameter data type. But, the query failed with error "cannot be pushed to source".

This is expected to work.  Can you log an issue for that?

> Any solution?

An ugly workaround would be to use a procedure:

begin
  create local temporary table t (c bigdecimal);
  loop on (SELECT TAB1.STRINGCOL, TAB1.DATE FROM MODEL2.TABLE1 TAB1) as x
  begin
     insert into t values (MODEL1."dbo.examplefunc"(STRINGCOL, DATE, TRUE) );
  end
   select * from t;
end

If there is another source table on MODEL1 another slightly less ugly workaround would be to use a subquery associated with MODEL1:

SELECT (SELECT MODEL1."dbo.examplefunc"(TAB1.STRINGCOL, TAB1.DATE, TRUE) FROM MODEL1.TABLEX LIMIT 1) MODEL2.TABLE1 TAB1

[rareddy]

When you create a CREATE FOREIGN FUNCTION is a function exposed by the source, by currently not defined in Teiid's translator. By defining the function you are creating an alias for the source function, so it needs to exactly match to that of the source in this case SQLServer. By default, this function is set to pushdown. It typically should not matter whether you used the literals or expressions in the parameters. Can you provide full exception stack?

[cshong]

This issue exist, even on a freshly installed version 12.1. I will try to log an issue.

[shawkins]

I've already got the code ready, so this was captured as [TEIID-5714] Can't push cross source function - JBoss Issue Tracker