How to create pushdown function for DIGSTRING using ("teiid_rel:native-query" '$1') in VDB ddl xml file?

[thiru28]

How to create pushdown function for DIGSTRING using ("teiid_rel:native-query" '$1') in VDB ddl xml file?

Its working fine in PI OLEDB Tester,

SELECT Tag, Time, DIGSTRING(CAST(value AS Int32)) State

FROM piarchive..picomp2

WHERE

tag = 'BA:ACTIVE.1'  AND

time BETWEEN '2018-02-25'  AND '2018-03-01'

But not working in Squirrel using Teiid JDBC,

SELECT PI.Tag, PI."Time", DIGSTRING(Convert(PI.Value,integer)),PI.Value

FROM "piarchive"."picomp2" PI

WHERE

PI.tag = 'BA:ACTIVE.1'  AND

PI.time BETWEEN '2018-02-25' AND '2018-03-01'

Error: TEIID30258 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30258 Expression(s) DIGSTRING(Convert(PI."value", integer)) cannot be pushed to source.

SQLState:  50000

ErrorCode: 30258

Thanks

Thiru

[shawkins]

Have you repeated what was successful from Unable to get result on querying piavg or pitotal or pimax views of Pi server via Teiid ?

You would update the DDL section to include an additional function:

CREATE FOREIGN FUNCTION TIMEINTERVAL (arg1 string) RETURNS timestamp OPTIONS ("teiid_rel:native-query" '$1');

CREATE FOREIGN FUNCTION DIGSTRING (arg1 integer) RETURNS string;

You do not need to include an option clause as you want the function to just render in the default way in the source query.

[thiru28]

<metadata type="NATIVE"/> 

        <metadata type="DDL"> 

            <![CDATA[

                         CREATE FOREIGN FUNCTION DIGSTRING(arg1 integer) RETURNS STRING;

            ]]> 

        </metadata>

In Squirrel using Teiid JDBC,

SELECT PI.Tag, PI."Time", PI.Value ,DIGSTRING(convert(PI.Value,integer)))

FROM "piarchive"."picomp2" PI

WHERE

PI.tag = 'BA:ACTIVE.1'  AND

PI.time BETWEEN '2018-02-25' AND '2018-03-01'

I got an error below,

Error: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "integer))[*])[*] FROM \"piarchive\".\"picomp2\"" at line 1, column 73.

Was expecting: <EOF> | "and" | "as" | "between" | "except" | "fetch" | "filter" | "from" | "in" | "intersect" ...

SQLState:  50000

ErrorCode: 0

[shawkins]

The message is complaining about an extra closing paren ) - you should have DIGSTRING(convert(PI.Value,integer))

[thiru28]

Error: TEIID30258 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30258 Expression(s) DIGSTRING(convert(PI."value", integer)) cannot be pushed to source.

SQLState:  50000

ErrorCode: 30258

[sahithi.r]

SELECT convert(PI.Value , integer)

,DIGSTRING(-65536), DIGSTRING(-65537) FROM "piarchive"."picomp2" PI is working.

But including DIGSTRING(cast(PI.Value as integer)) in select is throwing the error.

Error: TEIID30258 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30258 Expression(s) DIGSTRING(convert(PI."value", integer)) cannot be pushed to source.

SQLState:  50000

ErrorCode: 30258

Please suggest a solution .

Thanks,

Sahithi

[shawkins]

That is probably implying that the conversion of value to integer is not supported, thus the function as whole cannot be pushed.  You can confirm that looking at the planning debug log.  What is the Teiid type for the value column and what version of Teiid are you using? 

[cm.kumar]

Teiid type for "value" column is 'object' and teiid version is 9.1.1.

Actually sending the value directly returns the result like DIGSTRING(-65536) but DIGSTRING(cast("value" as integer)) is not getting through. Even forming a sub query, making the field explicitly as integer did not help. But if the data type from PI server itself is integer, it converts. Like there is one column "status", which is of type integer. so doing DIGSTRING(status), gets results.

[shawkins]

> Actually sending the value directly returns the result like DIGSTRING(-65536) but DIGSTRING(cast("value" as integer)) is not getting through

Yes, it is the conversion that is inhibiting pushdown.  Generally the conversion from the object type is not pushed down.  What is the actual source type of value?

If for whatever reason you need it to be an object type, a workaround is to change the function signature:

CREATE FOREIGN FUNCTION DIGSTRING (arg1 object) RETURNS string;

[sahithi.r]

Tried changing the function signature too.but no luck.

still facing the same issue.

[shawkins]

You should always check the planning debug log in instances like this as it should provide an exact message.

You'll need to post the vdb before I can make any more suggestions.  Also can you provide the source type of value?

[thiru28]

Here below query getting an error,

SELECT PI.Tag, PI."Time",PI.Value ,DIGSTRING(CONVERT(PI.Value,INTEGER)) STATUS

FROM "piarchive"."picomp2" PI

WHERE

PI.tag = 'BA:ACTIVE.1'  AND

PI.time BETWEEN '2018-02-28' AND '2018-03-01'

AND PI.Value IS NOT NULL

I am able to get the result of digital state from  "picomp" instead of "picomp2",

SELECT PI.Tag, PI."Time",DIGSTRING(CONVERT(PI.status,INTEGER)) STATUS

FROM "piarchive"."picomp" PI

WHERE

PI.tag = 'BA:ACTIVE.1'  AND

PI.time BETWEEN '2018-02-28' AND '2018-03-01'

AND PI.status IS NOT NULL

[ragava28]

Hi Steven ,

I am also having the same issue.

The query which i am trying is below,  error message is "TEIID30258 Expression(s) DIGSTRING(CONVERT(PI."value", INTEGER)) cannot be pushed to source."

SELECT

PI.tag AS TAG,

CAST(PI."Time" as timestamp) AS MEAS_DATE,

CAST(PI."value" as double) AS MEAS_VALUE,

DIGSTRING(CONVERT(PI."value",INTEGER)) AS MEAS_VALUE_TEXT,

DIGSTRING(CONVERT(PI.status,INTEGER)) AS STATUS_VALUE_TEXT

FROM

PI.PIArchive.pisnapshot PI

I also created a function which excepts object.  but this also didn't work , as in PI server we don't have any function with this signature. Pi only recognizes argument with integer as input.

CREATE FOREIGN FUNCTION DIGSTRING (arg1 object) RETURNS string;

Below are the details of the value column when accessed from PI native JDBC drive & Teiid . attached the screen shots also.

Native Jdbc driver : Variant (java.lang.Object)

Teiid : object (java.lang.Object)

I have attached the trimmed version of the vdb also for reference.

Can you please help on this issue.

[shawkins]

Raghu,

The base translator logic assumes that conversion from object is not supported.  It looks like pi has variant support, so this should be allowed - can you log an issue?

> as in PI server we don't have any function with this signature. Pi only recognizes argument with integer as input.

If an explicit cast is needed in the Pi SQL, you can change/add to your teiid ddl as a workaround:

CREATE FOREIGN FUNCTION DIGSTRING (arg1 object) RETURNS string OPTIONS ("teiid_rel:native-query" 'DIGSTRING(cast($1 as Int32)')

[shawkins]

> can you log an issue?

I inadvertently already made the change, so I went ahead and captured this as:  [TEIID-5458] Allow pi to convert from object/variant type - JBoss Issue Tracker