[JBoss JIRA] (TEIID-5141) Osisoft translator - ANY and SOME expressions cause syntax errors on datasource
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5141?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5141.
---------------------------------
Resolution: Done
> Osisoft translator - ANY and SOME expressions cause syntax errors on datasource
> -------------------------------------------------------------------------------
>
> Key: TEIID-5141
> URL: https://issues.jboss.org/browse/TEIID-5141
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
>
> Running a query such as
> {code:sql}
> SELECT INTKEY, STRINGKEY, FLOATNUM FROM BQT1.SMALLA WHERE FLOATNUM <> SOME (SELECT FLOATNUM FROM BQT1.SMALLA WHERE STRINGKEY = 10)
> {code}
> gets translated as
> {code:sql}
> SELECT g_0.IntKey, g_0.StringKey, g_0.FloatNum FROM dvqe..SmallA AS g_0 WHERE g_0.FloatNum <> SOME (SELECT g_1.FloatNum FROM dvqe..SmallA AS g_1 WHERE g_1.StringKey = '10')
> {code}
> and fails with
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Parser] [Line 1:100] Missing ) at 'SELECT'
> [Line 1:171] Extraneous ')'
> {noformat}
> Same thing when the SOME keyword is replaced with ANY (as in, ANY is translated to the exact same query, with 'SOME', which is understandable, since they _should_ be equivalent).
> This appears to be a bug in Osisoft PI, since issuing the same query with ANY against the datasource (without teiid involvement) works, so I suggest translating SOME as ANY as a workaround.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 1 month
[JBoss JIRA] (TEIID-5127) Osisoft translator - MOD fails with non-integer arguments
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5127?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5127.
---------------------------------
Resolution: Done
> Osisoft translator - MOD fails with non-integer arguments
> ---------------------------------------------------------
>
> Key: TEIID-5127
> URL: https://issues.jboss.org/browse/TEIID-5127
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.1
>
>
> Query such as
> {code:sql}
> SELECT FloatNum, 11, MOD(FloatNum, 11) FROM BQT1.SmallA
> {code}
> fails on the PI server with
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 1:34] [%] Overload resolution failed for (Single, Single) argument(s).
> {noformat}
> Similarly for Double arguments. It appears that the modulo operator % is only defined when both arguments are integers (contrary to PI OLEDB Enterprise 2016 User Guide, which states that it is defined for all numeric data types)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 1 month
[JBoss JIRA] (TEIID-5141) Osisoft translator - ANY and SOME expressions cause syntax errors on datasource
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5141?page=com.atlassian.jira.plugin... ]
Work on TEIID-5141 started by Ramesh Reddy.
-------------------------------------------
> Osisoft translator - ANY and SOME expressions cause syntax errors on datasource
> -------------------------------------------------------------------------------
>
> Key: TEIID-5141
> URL: https://issues.jboss.org/browse/TEIID-5141
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
>
> Running a query such as
> {code:sql}
> SELECT INTKEY, STRINGKEY, FLOATNUM FROM BQT1.SMALLA WHERE FLOATNUM <> SOME (SELECT FLOATNUM FROM BQT1.SMALLA WHERE STRINGKEY = 10)
> {code}
> gets translated as
> {code:sql}
> SELECT g_0.IntKey, g_0.StringKey, g_0.FloatNum FROM dvqe..SmallA AS g_0 WHERE g_0.FloatNum <> SOME (SELECT g_1.FloatNum FROM dvqe..SmallA AS g_1 WHERE g_1.StringKey = '10')
> {code}
> and fails with
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Parser] [Line 1:100] Missing ) at 'SELECT'
> [Line 1:171] Extraneous ')'
> {noformat}
> Same thing when the SOME keyword is replaced with ANY (as in, ANY is translated to the exact same query, with 'SOME', which is understandable, since they _should_ be equivalent).
> This appears to be a bug in Osisoft PI, since issuing the same query with ANY against the datasource (without teiid involvement) works, so I suggest translating SOME as ANY as a workaround.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 1 month
[JBoss JIRA] (TEIID-5136) Osisoft translator - NPE when running query with LATERAL JOIN
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5136?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5136:
-------------------------------------
[~asmigala] LATERAL JOIN applies to "CROSS APPLY" in PI and it does with restrictions as to where this applies. So, the above query may not be correct. Please see the documentation and see if above applies. Also for LATERAL, there are additional metadata requirements. If you already did those, I need the VDB, I remember the JOIN to be with the procedure, not a table. Can you check to make sure you are done those steps?
> Osisoft translator - NPE when running query with LATERAL JOIN
> -------------------------------------------------------------
>
> Key: TEIID-5136
> URL: https://issues.jboss.org/browse/TEIID-5136
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.1
>
>
> Running the following query:
> {code:sql}
> SELECT bqt2.smalla.intkey, g2.intkey, bqt2.smalla.bytenum FROM bqt2.smalla LEFT JOIN LATERAL (SELECT intkey FROM bqt2.mediuma WHERE bqt2.smalla.bytenum = bqt2.mediuma.bytenum) AS g2 ON true
> {code}
> results in a NPE in teiid (before sending source src command).
> Stacktrace:
> {noformat}
> Connector worker process failed for atomic-request=vEbs99yd+srV.1.0.1: java.lang.NullPointerException
> at org.teiid.translator.jdbc.pi.PIExecutionFactory.translate(PIExecutionFactory.java:273) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
> at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:111) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
> at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:106) [teiid-api-8.12.11.6_4-redhat-64-7.jar:8.12.11.6_4-redhat-64-7]
> at org.teiid.language.visitor.SQLStringVisitor.visit(SQLStringVisitor.java:767) [teiid-api-8.12.11.6_4-redhat-64-7.jar:8.12.11.6_4-redhat-64-7]
> at org.teiid.language.Select.acceptVisitor(Select.java:110) [teiid-api-8.12.11.6_4-redhat-64-7.jar:8.12.11.6_4-redhat-64-7]
> at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-7.jar:8.12.11.6_4-redhat-64-7]
> at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:91) [teiid-api-8.12.11.6_4-redhat-64-7.jar:8.12.11.6_4-redhat-64-7]
> at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:130) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
> at org.teiid.translator.jdbc.TranslatedCommand.translateCommand(TranslatedCommand.java:76) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
> at org.teiid.translator.jdbc.JDBCBaseExecution.translateCommand(JDBCBaseExecution.java:120) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:114) [translator-jdbc-8.12.11.6_4.jar:8.12.11.6_4]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:363)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_141]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_141]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_141]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_141]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_141]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_141]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_141]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_141]
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 1 month
[JBoss JIRA] (TEIID-5124) Osisoft translator - Syntax error on datasource for queries with LOCATE
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5124?page=com.atlassian.jira.plugin... ]
Work on TEIID-5124 started by Ramesh Reddy.
-------------------------------------------
> Osisoft translator - Syntax error on datasource for queries with LOCATE
> -----------------------------------------------------------------------
>
> Key: TEIID-5124
> URL: https://issues.jboss.org/browse/TEIID-5124
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Driver
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.1
>
>
> Running a query with LOCATE function causes a syntax error on the PI server:
> {code:sql}
> SELECT INTKEY FROM BQT1.SmallA WHERE LOCATE(2, INTKEY, 1) = 1
> {code}
> is pushed as
> {code:sql}
> SELECT g_0.IntKey AS c_0 FROM dvqe..SmallA AS g_0 WHERE cast(g_0.IntKey AS String)'2'1 = 1
> {code}
> and fails with
> {noformat}
> org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT TOP 100 g_0.IntKey AS c_0 FROM dvqe..SmallA AS g_0 WHERE cast(g_0.IntKey AS String)'2'1 = 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:363)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_141]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_141]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_141]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_141]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_141]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_141]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_141]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_141]
> Caused by: java.sql.SQLException: [PIOLEDBENT] [SQL Parser] [Line 1:90] Syntax error near '2'
> at com.osisoft.jdbc.StatementImpl.executeQuery(StatementImpl.java:360)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> ... 18 more
> Caused by: com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Parser] [Line 1:90] Syntax error near '2'
> at com.osisoft.rdsa.Command.ExecuteReader(Native Method)
> at com.osisoft.rdsa.Command.executeReader(Command.java:79)
> at com.osisoft.jdbc.StatementImpl.executeQuery(StatementImpl.java:356)
> ... 20 more
> {noformat}
> Furthermore, query such as
> {code:sql}
> SELECT INTKEY, LOCATE(1, STRINGNUM) FROM BQT1.SmallA
> {code}
> fails on teiid (before push down) with
> {noformat}
> java.lang.IndexOutOfBoundsException: Index: 2, Size: 2
> at java.util.ArrayList.rangeCheck(ArrayList.java:653) [rt.jar:1.8.0_141]
> at java.util.ArrayList.get(ArrayList.java:429) [rt.jar:1.8.0_141]
> at org.teiid.translator.jdbc.pi.PIExecutionFactory$1.translate(PIExecutionFactory.java:89) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.JDBCExecutionFactory.translate(JDBCExecutionFactory.java:539) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.pi.PIExecutionFactory.translate(PIExecutionFactory.java:260) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:111) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.visitor.SQLStringVisitor.visit(SQLStringVisitor.java:834) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.SQLConversionVisitor.visit(SQLConversionVisitor.java:306) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.DerivedColumn.acceptVisitor(DerivedColumn.java:47) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:91) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:130) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:106) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.visitor.SQLStringVisitor.visit(SQLStringVisitor.java:767) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.Select.acceptVisitor(Select.java:110) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:91) [teiid-api-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.SQLConversionVisitor.append(SQLConversionVisitor.java:130) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.TranslatedCommand.translateCommand(TranslatedCommand.java:76) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.JDBCBaseExecution.translateCommand(JDBCBaseExecution.java:120) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:114) [translator-jdbc-8.12.11.6_4-redhat-64-6.jar:8.12.11.6_4-redhat-64-6]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:363)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_141]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_141]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_141]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_141]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_141]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_141]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_141]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_141]
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 1 month
[JBoss JIRA] (TEIID-5127) Osisoft translator - MOD fails with non-integer arguments
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5127?page=com.atlassian.jira.plugin... ]
Work on TEIID-5127 started by Ramesh Reddy.
-------------------------------------------
> Osisoft translator - MOD fails with non-integer arguments
> ---------------------------------------------------------
>
> Key: TEIID-5127
> URL: https://issues.jboss.org/browse/TEIID-5127
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.1
>
>
> Query such as
> {code:sql}
> SELECT FloatNum, 11, MOD(FloatNum, 11) FROM BQT1.SmallA
> {code}
> fails on the PI server with
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 1:34] [%] Overload resolution failed for (Single, Single) argument(s).
> {noformat}
> Similarly for Double arguments. It appears that the modulo operator % is only defined when both arguments are integers (contrary to PI OLEDB Enterprise 2016 User Guide, which states that it is defined for all numeric data types)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 1 month
[JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5097?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5097:
-------------------------------------
Since this not specific to PI, I am putting this on hold
> 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)
7 years, 1 month
[JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5097?page=com.atlassian.jira.plugin... ]
Ramesh Reddy reassigned TEIID-5097:
-----------------------------------
Assignee: (was: Ramesh Reddy)
> 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)
7 years, 1 month
[JBoss JIRA] (TEIID-5127) Osisoft translator - MOD fails with non-integer arguments
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5127?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5127:
-------------------------------------
How about rewriting this as
{code}
SELECT SmallA.FloatNum, 11, cast(SmallA.FloatNum as int64)%cast(11.0 as int64) FROM SmallA
{code}
> Osisoft translator - MOD fails with non-integer arguments
> ---------------------------------------------------------
>
> Key: TEIID-5127
> URL: https://issues.jboss.org/browse/TEIID-5127
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.1
>
>
> Query such as
> {code:sql}
> SELECT FloatNum, 11, MOD(FloatNum, 11) FROM BQT1.SmallA
> {code}
> fails on the PI server with
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 1:34] [%] Overload resolution failed for (Single, Single) argument(s).
> {noformat}
> Similarly for Double arguments. It appears that the modulo operator % is only defined when both arguments are integers (contrary to PI OLEDB Enterprise 2016 User Guide, which states that it is defined for all numeric data types)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 1 month
[JBoss JIRA] (TEIID-5123) Osisoft translator - Cannot cast String to Float if value is negative
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5123?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5123:
-------------------------------------
Added to known issues to translator documentation.
> Osisoft translator - Cannot cast String to Float if value is negative
> ---------------------------------------------------------------------
>
> Key: TEIID-5123
> URL: https://issues.jboss.org/browse/TEIID-5123
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.1
>
>
> Casting a string containing a negative value (e.g. '-24') to float fails with
> {noformat}com.osisoft.rdsa.RdsaException: [PIOLEDBENT] Cannot convert -24 from String to Single.{noformat}
> This seems to be a bug in Osisoft PI SQL DAS (server-side not the JDBC driver), as connecting to the PI server using Osisoft's SQL Commander and running
> {code:sql}select cast(N'-24' as single){code}
> results in the same error.
> I think the best course of action would be to document this behaviour of the PI server as known issue and NOT work around it.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 1 month