[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:all-tabpanel ]

Steven Hawkins reassigned TEIID-5097:
-------------------------------------

    Assignee: Steven Hawkins


I'm going to add the function described in the last comment as a pushdown function and update the docs with respect to its usage.

> 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
>            Assignee: Steven Hawkins
>             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)