[JBoss JIRA] (TEIID-5191) Osisoft translator - AVG on integral column returns NULL
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5191?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5191.
---------------------------------
Resolution: Done
The JDBC Driver does not like when on resultset the columns are retrieved using specific methods such as getInt, getDouble etc. Opted to use getObject always
> Osisoft translator - AVG on integral column returns NULL
> --------------------------------------------------------
>
> Key: TEIID-5191
> URL: https://issues.jboss.org/browse/TEIID-5191
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> Running a query such as
> {code:sql}
> SELECT AVG(intnum) FROM BQT1.SmallA
> {code}
> returns just a NULL value.
> Running an equivalent query directly against the PI datasource returns the correct result.
> This happens for any column type except for single and double.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5192) Osisoft Translator - selecting ABS on a long value causes NPE
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5192?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-5192:
--------------------------------
Fix Version/s: 8.12.x-6.4
10.1
> Osisoft Translator - selecting ABS on a long value causes NPE
> -------------------------------------------------------------
>
> Key: TEIID-5192
> URL: https://issues.jboss.org/browse/TEIID-5192
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> Running a query such as
> {code:sql}
> SELECT LongNum, ABS(LongNum) AS ABSLongNum FROM BQT2.SmallB
> {code}
> results in a NullPointerException while translating the results. The type of LongNum column on the datasource is int64.
> This does not happen with other column types (integer, float, double).
> When running an equivalent query directly against the Osisoft PI datasource, the correct results are returned and the type of the ABS column is Single (i.e. float). It appears the translator tries to get the ABS value as long, see stacktrace.
> Stacktrace:
> {noformat}
> [org.jboss.jca.core.connectionmanager.listener.TxConnectionListener] (Worker15_QueryProcessorQueue84) IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@4b1e4616[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@51027396 connection handles=1 lastUse=1514883684240 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@39789e04 pool internal context=SemaphoreArrayListManagedConnectionPool@6732a357[pool=osisoft] xaResource=LocalXAResourceImpl@52da36f6[connectionListener=4b1e4616 connectionManager=63a083c7 warned=false currentXid=null productName=PI SQL Data Access Server 1.5.16302.2 using PIOLEDBENT productVersion=PIOLEDBENT: 1.4.2.95 jndiName=java:/osisoft] txSync=null]: java.lang.NullPointerException
> at com.osisoft.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:478)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.getLong(WrappedResultSet.java:1094)
> at org.teiid.translator.jdbc.JDBCExecutionFactory.retrieveValue(JDBCExecutionFactory.java:1004) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.translator.jdbc.pi.PIExecutionFactory.retrieveValue(PIExecutionFactory.java:322) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:344) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:431)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:234)
> at sun.reflect.GeneratedMethodAccessor170.invoke(Unknown Source) [: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.more(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:309)
> 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]
> 10:01:31,699 WARN [org.teiid.CONNECTOR] (Worker15_QueryProcessorQueue84) Connector worker process failed for atomic-request=xcwUXrxLs9We.26.0.18: org.teiid.translator.TranslatorException: Unexpected exception while translating results: Error
> at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:351) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:431)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:234)
> at sun.reflect.GeneratedMethodAccessor170.invoke(Unknown Source) [: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.more(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:309)
> 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]
> Caused by: java.sql.SQLException: Error
> at org.jboss.jca.adapters.jdbc.WrappedConnection.checkException(WrappedConnection.java:1687)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.checkException(WrappedStatement.java:1267)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.checkException(WrappedResultSet.java:4063)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.getLong(WrappedResultSet.java:1098)
> at org.teiid.translator.jdbc.JDBCExecutionFactory.retrieveValue(JDBCExecutionFactory.java:1004) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.translator.jdbc.pi.PIExecutionFactory.retrieveValue(PIExecutionFactory.java:322) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:344) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> ... 18 more
> Caused by: java.lang.NullPointerException
> at com.osisoft.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:478)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.getLong(WrappedResultSet.java:1094)
> ... 21 more
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5192) Osisoft Translator - selecting ABS on a long value causes NPE
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5192?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5192.
---------------------------------
Resolution: Done
The JDBC Driver does not like when on resultset the columns are retrieved using specific methods such as getInt, getDouble etc. Opted to use getObject always
> Osisoft Translator - selecting ABS on a long value causes NPE
> -------------------------------------------------------------
>
> Key: TEIID-5192
> URL: https://issues.jboss.org/browse/TEIID-5192
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> Running a query such as
> {code:sql}
> SELECT LongNum, ABS(LongNum) AS ABSLongNum FROM BQT2.SmallB
> {code}
> results in a NullPointerException while translating the results. The type of LongNum column on the datasource is int64.
> This does not happen with other column types (integer, float, double).
> When running an equivalent query directly against the Osisoft PI datasource, the correct results are returned and the type of the ABS column is Single (i.e. float). It appears the translator tries to get the ABS value as long, see stacktrace.
> Stacktrace:
> {noformat}
> [org.jboss.jca.core.connectionmanager.listener.TxConnectionListener] (Worker15_QueryProcessorQueue84) IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@4b1e4616[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@51027396 connection handles=1 lastUse=1514883684240 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@39789e04 pool internal context=SemaphoreArrayListManagedConnectionPool@6732a357[pool=osisoft] xaResource=LocalXAResourceImpl@52da36f6[connectionListener=4b1e4616 connectionManager=63a083c7 warned=false currentXid=null productName=PI SQL Data Access Server 1.5.16302.2 using PIOLEDBENT productVersion=PIOLEDBENT: 1.4.2.95 jndiName=java:/osisoft] txSync=null]: java.lang.NullPointerException
> at com.osisoft.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:478)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.getLong(WrappedResultSet.java:1094)
> at org.teiid.translator.jdbc.JDBCExecutionFactory.retrieveValue(JDBCExecutionFactory.java:1004) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.translator.jdbc.pi.PIExecutionFactory.retrieveValue(PIExecutionFactory.java:322) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:344) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:431)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:234)
> at sun.reflect.GeneratedMethodAccessor170.invoke(Unknown Source) [: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.more(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:309)
> 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]
> 10:01:31,699 WARN [org.teiid.CONNECTOR] (Worker15_QueryProcessorQueue84) Connector worker process failed for atomic-request=xcwUXrxLs9We.26.0.18: org.teiid.translator.TranslatorException: Unexpected exception while translating results: Error
> at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:351) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:431)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:234)
> at sun.reflect.GeneratedMethodAccessor170.invoke(Unknown Source) [: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.more(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:309)
> 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]
> Caused by: java.sql.SQLException: Error
> at org.jboss.jca.adapters.jdbc.WrappedConnection.checkException(WrappedConnection.java:1687)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.checkException(WrappedStatement.java:1267)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.checkException(WrappedResultSet.java:4063)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.getLong(WrappedResultSet.java:1098)
> at org.teiid.translator.jdbc.JDBCExecutionFactory.retrieveValue(JDBCExecutionFactory.java:1004) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.translator.jdbc.pi.PIExecutionFactory.retrieveValue(PIExecutionFactory.java:322) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:344) [translator-jdbc-8.12.11.6_4-redhat-64-9.jar:8.12.11.6_4-redhat-64-9]
> ... 18 more
> Caused by: java.lang.NullPointerException
> at com.osisoft.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:478)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.getLong(WrappedResultSet.java:1094)
> ... 21 more
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5194) Osisoft translator - MAX and MIN on boolean column cause an error
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5194?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5194.
---------------------------------
Resolution: Done
The JDBC Driver does not like when on resultset the columns are retrieved using specific methods such as getInt, getDouble etc. Opted to use getObject always
> Osisoft translator - MAX and MIN on boolean column cause an error
> -----------------------------------------------------------------
>
> Key: TEIID-5194
> URL: https://issues.jboss.org/browse/TEIID-5194
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> Running a query such as
> {code:sql}
> SELECT MAX(BooleanValue) FROM BQT1.SmallA
> {code}
> fails with the following exception:
> {noformat}
> java.lang.ClassCastException: java.lang.Byte cannot be cast to java.lang.Boolean
> at com.osisoft.jdbc.ResultSetImpl.getBoolean(ResultSetImpl.java:407)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.getBoolean(WrappedResultSet.java:594)
> ... 22 more
> {noformat}
> Same issue occurs when using MIN.
> Running an equivalent query directly against the Osisoft PI datasource returns the correct result, with the return type being int8 (i.e. byte)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5194) Osisoft translator - MAX and MIN on boolean column cause an error
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5194?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-5194:
--------------------------------
Fix Version/s: 8.12.x-6.4
10.1
> Osisoft translator - MAX and MIN on boolean column cause an error
> -----------------------------------------------------------------
>
> Key: TEIID-5194
> URL: https://issues.jboss.org/browse/TEIID-5194
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> Running a query such as
> {code:sql}
> SELECT MAX(BooleanValue) FROM BQT1.SmallA
> {code}
> fails with the following exception:
> {noformat}
> java.lang.ClassCastException: java.lang.Byte cannot be cast to java.lang.Boolean
> at com.osisoft.jdbc.ResultSetImpl.getBoolean(ResultSetImpl.java:407)
> at org.jboss.jca.adapters.jdbc.WrappedResultSet.getBoolean(WrappedResultSet.java:594)
> ... 22 more
> {noformat}
> Same issue occurs when using MIN.
> Running an equivalent query directly against the Osisoft PI datasource returns the correct result, with the return type being int8 (i.e. byte)
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5195) Osisoft translator - applying LIMIT on a UNION query causes syntax error
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5195?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-5195:
--------------------------------
Fix Version/s: 8.12.x-6.4
10.1
> Osisoft translator - applying LIMIT on a UNION query causes syntax error
> ------------------------------------------------------------------------
>
> Key: TEIID-5195
> URL: https://issues.jboss.org/browse/TEIID-5195
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> Query such as
> {code:sql}
> SELECT g_0.intkey FROM bqt1.smalla g_0 UNION SELECT g_1.intkey FROM bqt1.smallb g_1 LIMIT 100
> {code}
> fails with
> {noformat}
> [PIOLEDBENT] [SQL Parser] [Line 1:106] Syntax error at 'TOP'
> {noformat}
> because it is translated as
> {code:sql}
> SELECT g_1.IntKey AS c_0 FROM dvqe..SmallA AS g_1 UNION SELECT g_0.IntKey AS c_0 FROM dvqe..SmallB AS g_0 TOP 100
> {code}
> notice the TOP 100 at the end, instead of right after the SELECT.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5196) Osisoft Translator - Wrong data returned for some JOIN queries when integer and float columns are compared
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5196?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5196:
-------------------------------------
yes, I opted to remove the cast on the Integer and Float comparison scenarios
> Osisoft Translator - Wrong data returned for some JOIN queries when integer and float columns are compared
> ----------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5196
> URL: https://issues.jboss.org/browse/TEIID-5196
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> When using equality comparison between an integer and float columns in a WHERE or ON clause, Teiid pushes it to Osisoft PI with both columns cast as double.
> There seems to be a bug in Osisoft PI which causes the non-positive float values in the result for such a query to be returned as NULL (or not at all).
> E.g.
> {code:sql|title=Teiid query}
> SELECT BQT1.MediumA.IntNum, BQT1.MediumB.FloatNum
> FROM BQT1.MediumA
> FULL JOIN BQT1.MediumB
> ON BQT1.MediumA.IntNum = BQT1.MediumB.FloatNum
> WHERE
> BQT1.MediumA.IntNum >= -10
> AND BQT1.MediumA.IntNum < 5
> AND (BQT1.MediumB.FloatNum >= -10
> AND BQT1.MediumB.FloatNum < 5)
> {code}
> is translated to
> {code:sql|title=Pushed Osisoft PI query}
> SELECT g_0.IntNum, g_1.FloatNum
> FROM dvqe..MediumA AS g_0, dvqe..MediumB AS g_1
> WHERE
> cast(g_0.IntNum AS Double) = cast(g_1.FloatNum AS Double)
> AND g_0.IntNum < 5
> AND g_1.FloatNum >= -10.0
> AND g_1.FloatNum < 5.0
> AND g_0.IntNum >= -10
> {code}
> which seems to be correct, but returns the wrong result:
> ||IntNum||FloatNum||
> |1|1|
> |2|2|
> |3|3|
> |4|4|
> Note that the only the positive values are returned, even though the criteria match also negative values (which are present in the source table).
> If the cast to double in the source query is removed (or replaced with cast to single), the query returns the expected results.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5195) Osisoft translator - applying LIMIT on a UNION query causes syntax error
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5195?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5195.
---------------------------------
Resolution: Done
> Osisoft translator - applying LIMIT on a UNION query causes syntax error
> ------------------------------------------------------------------------
>
> Key: TEIID-5195
> URL: https://issues.jboss.org/browse/TEIID-5195
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> Query such as
> {code:sql}
> SELECT g_0.intkey FROM bqt1.smalla g_0 UNION SELECT g_1.intkey FROM bqt1.smallb g_1 LIMIT 100
> {code}
> fails with
> {noformat}
> [PIOLEDBENT] [SQL Parser] [Line 1:106] Syntax error at 'TOP'
> {noformat}
> because it is translated as
> {code:sql}
> SELECT g_1.IntKey AS c_0 FROM dvqe..SmallA AS g_1 UNION SELECT g_0.IntKey AS c_0 FROM dvqe..SmallB AS g_0 TOP 100
> {code}
> notice the TOP 100 at the end, instead of right after the SELECT.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5196) Osisoft Translator - Wrong data returned for some JOIN queries when integer and float columns are compared
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5196?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-5196:
--------------------------------
Fix Version/s: 8.12.x-6.4
10.1
> Osisoft Translator - Wrong data returned for some JOIN queries when integer and float columns are compared
> ----------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5196
> URL: https://issues.jboss.org/browse/TEIID-5196
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> When using equality comparison between an integer and float columns in a WHERE or ON clause, Teiid pushes it to Osisoft PI with both columns cast as double.
> There seems to be a bug in Osisoft PI which causes the non-positive float values in the result for such a query to be returned as NULL (or not at all).
> E.g.
> {code:sql|title=Teiid query}
> SELECT BQT1.MediumA.IntNum, BQT1.MediumB.FloatNum
> FROM BQT1.MediumA
> FULL JOIN BQT1.MediumB
> ON BQT1.MediumA.IntNum = BQT1.MediumB.FloatNum
> WHERE
> BQT1.MediumA.IntNum >= -10
> AND BQT1.MediumA.IntNum < 5
> AND (BQT1.MediumB.FloatNum >= -10
> AND BQT1.MediumB.FloatNum < 5)
> {code}
> is translated to
> {code:sql|title=Pushed Osisoft PI query}
> SELECT g_0.IntNum, g_1.FloatNum
> FROM dvqe..MediumA AS g_0, dvqe..MediumB AS g_1
> WHERE
> cast(g_0.IntNum AS Double) = cast(g_1.FloatNum AS Double)
> AND g_0.IntNum < 5
> AND g_1.FloatNum >= -10.0
> AND g_1.FloatNum < 5.0
> AND g_0.IntNum >= -10
> {code}
> which seems to be correct, but returns the wrong result:
> ||IntNum||FloatNum||
> |1|1|
> |2|2|
> |3|3|
> |4|4|
> Note that the only the positive values are returned, even though the criteria match also negative values (which are present in the source table).
> If the cast to double in the source query is removed (or replaced with cast to single), the query returns the expected results.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months
[JBoss JIRA] (TEIID-5196) Osisoft Translator - Wrong data returned for some JOIN queries when integer and float columns are compared
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5196?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5196.
---------------------------------
Resolution: Done
> Osisoft Translator - Wrong data returned for some JOIN queries when integer and float columns are compared
> ----------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5196
> URL: https://issues.jboss.org/browse/TEIID-5196
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 8.12.x-6.4, 10.1
>
>
> When using equality comparison between an integer and float columns in a WHERE or ON clause, Teiid pushes it to Osisoft PI with both columns cast as double.
> There seems to be a bug in Osisoft PI which causes the non-positive float values in the result for such a query to be returned as NULL (or not at all).
> E.g.
> {code:sql|title=Teiid query}
> SELECT BQT1.MediumA.IntNum, BQT1.MediumB.FloatNum
> FROM BQT1.MediumA
> FULL JOIN BQT1.MediumB
> ON BQT1.MediumA.IntNum = BQT1.MediumB.FloatNum
> WHERE
> BQT1.MediumA.IntNum >= -10
> AND BQT1.MediumA.IntNum < 5
> AND (BQT1.MediumB.FloatNum >= -10
> AND BQT1.MediumB.FloatNum < 5)
> {code}
> is translated to
> {code:sql|title=Pushed Osisoft PI query}
> SELECT g_0.IntNum, g_1.FloatNum
> FROM dvqe..MediumA AS g_0, dvqe..MediumB AS g_1
> WHERE
> cast(g_0.IntNum AS Double) = cast(g_1.FloatNum AS Double)
> AND g_0.IntNum < 5
> AND g_1.FloatNum >= -10.0
> AND g_1.FloatNum < 5.0
> AND g_0.IntNum >= -10
> {code}
> which seems to be correct, but returns the wrong result:
> ||IntNum||FloatNum||
> |1|1|
> |2|2|
> |3|3|
> |4|4|
> Note that the only the positive values are returned, even though the criteria match also negative values (which are present in the source table).
> If the cast to double in the source query is removed (or replaced with cast to single), the query returns the expected results.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
8 years, 2 months