[teiid-issues] [JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5097?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13503446#comment-13503446 ] 

Steven Hawkins commented on TEIID-5097:
---------------------------------------

So for now the workaround is to add the source / pushdown function to the pi source model:

create function to_interval(param string) returns timestamp options ("teiid_rel:native-query" $1)

> Cannot run time-based queries against Osisoft PI
> ------------------------------------------------
>
>                 Key: TEIID-5097
>                 URL: https://issues.jboss.org/browse/TEIID-5097
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: JDBC Connector
>    Affects Versions: 8.12.x-6.4
>            Reporter: Andrej Šmigala
>             Fix For: 10.1
>
>
> Osisoft PI supports a relative time literals syntax, e.g. 
> {code:sql}
> select * from dvqe.Data.Archive a where a.time between '*-14d' and '*'
> {code}
> will select all data between right now and 14 days ago, and 
> {code:sql}
> select * from dvqe.Data.Archive a where a.time > 'y'
> {code}
> will select all data after yesterday midnight.
> The string literals are converted to time values in the PI Server
> Running the same queries through teiid however returns incorrect results, because teiid pushes a cast to string on the Time column, which results in string comparison on the datasource:
> {code:sql|title=Pushed query}
> SELECT TOP 100 cast(g_0.[ElementAttributeID] as String), g_0.[Time] AS c_1, g_0.[Value] AS c_2, g_0.[ValueInt] AS c_3, g_0.
> [ValueDbl] AS c_4, g_0.[ValueStr] AS c_5, cast(g_0.[ValueGuid] as String), g_0.[ValueDateTime] AS c_7, g_0.[Status] AS c_8, g_0.[Annotated] AS c_9, g_0.[IsGood] A
> S c_10, g_0.[Questionable] AS c_11, g_0.[Substituted] AS c_12 FROM [dvqe].[Data].[Archive] AS g_0 WHERE cast(g_0.[Time] AS String) > 'y'
> {code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)