[JBoss JIRA] (TEIID-5124) Osisoft translator - Syntax error on datasource for queries with LOCATE
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-5124:
-------------------------------------
Summary: 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: Steven Hawkins
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, 2 months
[JBoss JIRA] (TEIID-5122) Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5122?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5122:
-------------------------------------
All conversions from Boolean to any other type will be not pushed with the fix.
> Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
> --------------------------------------------------------------------
>
> Key: TEIID-5122
> URL: https://issues.jboss.org/browse/TEIID-5122
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.0
>
>
> Osisoft PI does not support conversion from Boolean to Single or Double, however, query such as
> {code:sql}
> select cast(booleanvalue as float) from Source.smalla
> {code}
> is pushed as
> {code:sql}
> SELECT cast(g_0.BooleanValue AS Single) AS c_0 FROM dvqe..SmallA AS g_0
> {code}
> which results in
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 1:14] Cannot cast 'Boolean' to 'Single'.
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5122) Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5122?page=com.atlassian.jira.plugin... ]
Ramesh Reddy reassigned TEIID-5122:
-----------------------------------
Assignee: Ramesh Reddy (was: Steven Hawkins)
> Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
> --------------------------------------------------------------------
>
> Key: TEIID-5122
> URL: https://issues.jboss.org/browse/TEIID-5122
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.0
>
>
> Osisoft PI does not support conversion from Boolean to Single or Double, however, query such as
> {code:sql}
> select cast(booleanvalue as float) from Source.smalla
> {code}
> is pushed as
> {code:sql}
> SELECT cast(g_0.BooleanValue AS Single) AS c_0 FROM dvqe..SmallA AS g_0
> {code}
> which results in
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 1:14] Cannot cast 'Boolean' to 'Single'.
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5122) Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5122?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-5122:
--------------------------------
Fix Version/s: 10.0
> Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
> --------------------------------------------------------------------
>
> Key: TEIID-5122
> URL: https://issues.jboss.org/browse/TEIID-5122
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.0
>
>
> Osisoft PI does not support conversion from Boolean to Single or Double, however, query such as
> {code:sql}
> select cast(booleanvalue as float) from Source.smalla
> {code}
> is pushed as
> {code:sql}
> SELECT cast(g_0.BooleanValue AS Single) AS c_0 FROM dvqe..SmallA AS g_0
> {code}
> which results in
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 1:14] Cannot cast 'Boolean' to 'Single'.
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5122) Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5122?page=com.atlassian.jira.plugin... ]
Work on TEIID-5122 started by Ramesh Reddy.
-------------------------------------------
> Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
> --------------------------------------------------------------------
>
> Key: TEIID-5122
> URL: https://issues.jboss.org/browse/TEIID-5122
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Fix For: 10.0
>
>
> Osisoft PI does not support conversion from Boolean to Single or Double, however, query such as
> {code:sql}
> select cast(booleanvalue as float) from Source.smalla
> {code}
> is pushed as
> {code:sql}
> SELECT cast(g_0.BooleanValue AS Single) AS c_0 FROM dvqe..SmallA AS g_0
> {code}
> which results in
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 1:14] Cannot cast 'Boolean' to 'Single'.
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5123) Osisoft translator - Cannot cast String to Float if value is negative
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-5123:
-------------------------------------
Summary: 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: Steven Hawkins
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, 2 months
[JBoss JIRA] (TEIID-5122) Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-5122:
-------------------------------------
Summary: Osisoft Translator - Teiid pushes cast from Boolean to Single/Double
Key: TEIID-5122
URL: https://issues.jboss.org/browse/TEIID-5122
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.12.x-6.4
Reporter: Andrej Šmigala
Assignee: Steven Hawkins
Osisoft PI does not support conversion from Boolean to Single or Double, however, query such as
{code:sql}
select cast(booleanvalue as float) from Source.smalla
{code}
is pushed as
{code:sql}
SELECT cast(g_0.BooleanValue AS Single) AS c_0 FROM dvqe..SmallA AS g_0
{code}
which results in
{noformat}
com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 1:14] Cannot cast 'Boolean' to 'Single'.
{noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5109) Set operations and parenthesis
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-5109?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-5109:
------------------------------------------------
Van Halbert <vhalbert(a)redhat.com> changed the Status of [bug 1504707|https://bugzilla.redhat.com/show_bug.cgi?id=1504707] from ASSIGNED to MODIFIED
> Set operations and parenthesis
> ------------------------------
>
> Key: TEIID-5109
> URL: https://issues.jboss.org/browse/TEIID-5109
> Project: Teiid
> Issue Type: Bug
> Components: Connector API, Query Engine
> Reporter: Mike Higgins
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 10.0, 8.12.x-6.4, 9.3.4, 9.2.7
>
>
> Using Teiid 9.2.2 with postgresql, I issue a query structured like this:
>
> (a
> UNION ALL
> b
> UNION ALL
> c)
> INTERSECT
> d
>
> All queries are from a single database.
> This query does not give back the expected result.
> In the plan, I see the query printed with no parenthesis around the unions:
> a UNION ALL b UNION ALL c INTERSECT d
> This gives the same result as the query submitted with no parens in it (the intersect of c and d is unioned with a union b.
> The expepeted result is to preserve the paren so that the intersect is performed against the union of all three tables.
> As a note, using UNION instead of UNION ALL did not preserve the parens, either.
>
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5120) Osisoft Translator - Wrong pushdown of LENGTH function
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5120?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-5120.
---------------------------------
Resolution: Done
Labels: CR2 (was: )
> Osisoft Translator - Wrong pushdown of LENGTH function
> ------------------------------------------------------
>
> Key: TEIID-5120
> URL: https://issues.jboss.org/browse/TEIID-5120
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Ramesh Reddy
> Labels: CR2
> Fix For: 10.0
>
>
> The LENGTH function is pushed down to Osisoft PI as-is, but fails on the datasource with
> {noformat}
> com.osisoft.rdsa.RdsaException: [PIOLEDBENT] [SQL Analyzer] [Line 0:4294967295] Function name 'length' is invalid.
> {noformat}
> This seems to be a mistake in PI OLEDB Enterprise 2016 User Guide, as this document mentions on page 32 that LENGTH is an alias for LEN.
> However, since LEN actually works, I suggest changing the pushdown in teiid as a workaround.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months