[JBoss JIRA] (TEIID-4631) IN clause causes an error when used within a function in SELECT clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4631?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4631.
-----------------------------------
Fix Version/s: 9.2
9.1.2
Resolution: Done
Updated sources with numeric/bit boolean value type mappings and parsing restrictions to use a case expression instead of the boolean expression directly:
isnull(case when g_0."a" IN (8, 9) then 1 else when not(g_0."a" IN (8, 9)) then 0 end, 0)
> IN clause causes an error when used within a function in SELECT clause
> ----------------------------------------------------------------------
>
> Key: TEIID-4631
> URL: https://issues.jboss.org/browse/TEIID-4631
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector, Query Engine
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 9.2, 9.1.2
>
>
> The following query is wrongly rewritten when it is pushed down to MS SQL Server and Oracle but it works correctly in other databases like MySQL or PostgreSQL:
> {code:sql}
> select COALESCE(t.a in (8,9), FALSE)
> from ms.test_a t;
> {code}
> This is the exception thrown in MS SQL Server:
> {code}
> 19:05:41,253 WARN [org.teiid.CONNECTOR] (Worker8_QueryProcessorQueue52) amQzqFjFfV09 Connector worker process failed for atomic-request=amQzqFjFfV09.15.0.11: org.teiid.translator.jdbc.JDBCExecutionException: 102 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT isnull(g_0."a" IN (8, 9), 0) AS c_0 FROM "test"."dbo"."test_a" g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy29.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:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> 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:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.
> at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
> at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 18 more
> {code}
> and this is the exception thrown by Oracle:
> {code}
> 19:09:15,307 WARN [org.teiid.CONNECTOR] (Worker10_QueryProcessorQueue58) amQzqFjFfV09 Connector worker process failed for atomic-request=amQzqFjFfV09.17.0.13: org.teiid.translator.jdbc.JDBCExecutionException: 909 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT nvl(g_0."A" IN (8, 9), 0) FROM "TEST"."TEST_A" g_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy29.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:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> 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:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of arguments
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 18 more
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4635) Mongo translator - NAMEINSOURCE option is ignored
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-4635?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-4635.
---------------------------------
Resolution: Duplicate Issue
yes, this is intentional choice from initial coding.
Duplicate of TEIID-4217
> Mongo translator - NAMEINSOURCE option is ignored
> -------------------------------------------------
>
> Key: TEIID-4635
> URL: https://issues.jboss.org/browse/TEIID-4635
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> Mongo translator ignores NAMEINSOURCE table/column option.
> For table \[1\], no data is returned. For column \[2\], all values in column are NULL.
> {code:sql|title=\[1\] Sample DDL - table}
> CREATE FOREIGN TABLE MyNewName (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', NAMEINSOURCE 'RealCollectionName');
> {code}
> {code:sql|title=\[2\] Sample DDL - column}
> CREATE FOREIGN TABLE MyCollection (
> id integer PRIMARY KEY,
> myColName varchar(25) OPTIONS(NAMEINSOURCE 'realName')
> ) OPTIONS(UPDATABLE 'TRUE');
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4636) In the SWARM readme's, need to indicate the URL to connect
by Van Halbert (JIRA)
Van Halbert created TEIID-4636:
----------------------------------
Summary: In the SWARM readme's, need to indicate the URL to connect
Key: TEIID-4636
URL: https://issues.jboss.org/browse/TEIID-4636
Project: Teiid
Issue Type: Enhancement
Components: Quick Starts
Affects Versions: 9.2
Reporter: Van Halbert
Assignee: Van Halbert
Priority: Minor
The quickstarts needs to include the information (i.e, URL, username, password, etc.) needed to connect to the swarm example.
Per Kylin:
By default 0.0.0.0 be bound as IP, 31000 as port. To bind a specific IP use system properties
---
-Dswarm.bind.address=127.0.0.1
---
Some Fractions use a system properties to change the port number, but teiid not support it at moment.
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4633) Odata $expand over Oracle tables generates bad query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4633?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4633:
---------------------------------------
The logic is trying to fetch the values as an array/row value, but oracle does not directly allow that in the select clause. We'll need to refine the array type handling pushdown for oracle.
> Odata $expand over Oracle tables generates bad query
> ----------------------------------------------------
>
> Key: TEIID-4633
> URL: https://issues.jboss.org/browse/TEIID-4633
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector, OData
> Affects Versions: 9.1.1
> Reporter: Sergio Samayoa
> Assignee: Steven Hawkins
> Priority: Critical
>
> Query generated to obtain referenced records ($expand many to one) is generated with parentheses,
> Generated query for Oracle*:
> SELECT g_0."CATEGORY_ID", (g_0."CATEGORY_ID", g_0."NAME", g_0."LAST_UPDATE") FROM "SAKILA"."CATEGORY" g_0
> Generated query for MySQL is:
> SELECT g_0.category_id, g_0.name, g_0.last_update FROM Sakila.category AS g_0
> * Note the parentheses enclosing all columns but PK, this raises "ORA-00907: missing right parenthesis".
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4634) TEIID30003 - wrong order of arguments
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4634?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4634.
-----------------------------------
Fix Version/s: 9.2
Resolution: Done
Thanks Juraj for catching this.
> TEIID30003 - wrong order of arguments
> -------------------------------------
>
> Key: TEIID-4634
> URL: https://issues.jboss.org/browse/TEIID-4634
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.1
> Reporter: Juraj Duráni
> Assignee: Juraj Duráni
> Priority: Trivial
> Fix For: 9.2
>
>
> TEIID30003 text:
> {quote}
> Without required support property \{0\}, pushdown will not be enabled for \{1\} on translator \{2\}.
> {quote}
> However, in code, it's called:
> {code:java}
> private static void setSupports(Object connectorID, BasicSourceCapabilities tgtCaps, Capability cap, boolean supports, Capability... required) {
> ...
> for (Capability capability : required) {
> ...
> LogManager.logWarning(LogConstants.CTX_CONNECTOR, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30003, cap, capability, connectorID));
> ...
> }
> ...
> }
> {code}
> I.e. required capability is second ('1') attribute, not first ('0').
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4634) TEIID30003 - wrong order of arguments
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4634?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4634:
----------------------------------
Component/s: Query Engine
> TEIID30003 - wrong order of arguments
> -------------------------------------
>
> Key: TEIID-4634
> URL: https://issues.jboss.org/browse/TEIID-4634
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.1
> Reporter: Juraj Duráni
> Assignee: Juraj Duráni
> Priority: Trivial
>
> TEIID30003 text:
> {quote}
> Without required support property \{0\}, pushdown will not be enabled for \{1\} on translator \{2\}.
> {quote}
> However, in code, it's called:
> {code:java}
> private static void setSupports(Object connectorID, BasicSourceCapabilities tgtCaps, Capability cap, boolean supports, Capability... required) {
> ...
> for (Capability capability : required) {
> ...
> LogManager.logWarning(LogConstants.CTX_CONNECTOR, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30003, cap, capability, connectorID));
> ...
> }
> ...
> }
> {code}
> I.e. required capability is second ('1') attribute, not first ('0').
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4635) Mongo translator - NAMEINSOURCE option is ignored
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4635?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4635:
---------------------------------------
[~rareddy] was this an intentional design choice?
> Mongo translator - NAMEINSOURCE option is ignored
> -------------------------------------------------
>
> Key: TEIID-4635
> URL: https://issues.jboss.org/browse/TEIID-4635
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.8.6_3
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> Mongo translator ignores NAMEINSOURCE table/column option.
> For table \[1\], no data is returned. For column \[2\], all values in column are NULL.
> {code:sql|title=\[1\] Sample DDL - table}
> CREATE FOREIGN TABLE MyNewName (
> id integer PRIMARY KEY,
> name varchar(25)
> ) OPTIONS(UPDATABLE 'TRUE', NAMEINSOURCE 'RealCollectionName');
> {code}
> {code:sql|title=\[2\] Sample DDL - column}
> CREATE FOREIGN TABLE MyCollection (
> id integer PRIMARY KEY,
> myColName varchar(25) OPTIONS(NAMEINSOURCE 'realName')
> ) OPTIONS(UPDATABLE 'TRUE');
> {code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4635) Mongo translator - NAMEINSOURCE option is ignored
by Juraj Duráni (JIRA)
Juraj Duráni created TEIID-4635:
-----------------------------------
Summary: Mongo translator - NAMEINSOURCE option is ignored
Key: TEIID-4635
URL: https://issues.jboss.org/browse/TEIID-4635
Project: Teiid
Issue Type: Bug
Components: Misc. Connectors
Affects Versions: 8.12.8.6_3
Reporter: Juraj Duráni
Assignee: Steven Hawkins
Mongo translator ignores NAMEINSOURCE table/column option.
For table \[1\], no data is returned. For column \[2\], all values in column are NULL.
{code:sql|title=\[1\] Sample DDL - table}
CREATE FOREIGN TABLE MyNewName (
id integer PRIMARY KEY,
name varchar(25)
) OPTIONS(UPDATABLE 'TRUE', NAMEINSOURCE 'RealCollectionName');
{code}
{code:sql|title=\[2\] Sample DDL - column}
CREATE FOREIGN TABLE MyCollection (
id integer PRIMARY KEY,
myColName varchar(25) OPTIONS(NAMEINSOURCE 'realName')
) OPTIONS(UPDATABLE 'TRUE');
{code}
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4634) TEIID30003 - wrong order of arguments
by Juraj Duráni (JIRA)
Juraj Duráni created TEIID-4634:
-----------------------------------
Summary: TEIID30003 - wrong order of arguments
Key: TEIID-4634
URL: https://issues.jboss.org/browse/TEIID-4634
Project: Teiid
Issue Type: Bug
Affects Versions: 9.1
Reporter: Juraj Duráni
Assignee: Juraj Duráni
Priority: Trivial
TEIID30003 text:
{quote}
Without required support property \{0\}, pushdown will not be enabled for \{1\} on translator \{2\}.
{quote}
However, in code, it's called:
{code:java}
private static void setSupports(Object connectorID, BasicSourceCapabilities tgtCaps, Capability cap, boolean supports, Capability... required) {
...
for (Capability capability : required) {
...
LogManager.logWarning(LogConstants.CTX_CONNECTOR, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30003, cap, capability, connectorID));
...
}
...
}
{code}
I.e. required capability is second ('1') attribute, not first ('0').
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months
[JBoss JIRA] (TEIID-4633) Odata $expand over Oracle tables generates bad query
by Sergio Samayoa (JIRA)
Sergio Samayoa created TEIID-4633:
-------------------------------------
Summary: Odata $expand over Oracle tables generates bad query
Key: TEIID-4633
URL: https://issues.jboss.org/browse/TEIID-4633
Project: Teiid
Issue Type: Bug
Components: JDBC Connector, OData
Affects Versions: 9.1.1
Reporter: Sergio Samayoa
Assignee: Steven Hawkins
Priority: Critical
Query generated to obtain referenced records ($expand many to one) is generated with parentheses,
Generated query for Oracle*:
SELECT g_0."CATEGORY_ID", (g_0."CATEGORY_ID", g_0."NAME", g_0."LAST_UPDATE") FROM "SAKILA"."CATEGORY" g_0
Generated query for MySQL is:
SELECT g_0.category_id, g_0.name, g_0.last_update FROM Sakila.category AS g_0
* Note the parentheses enclosing all columns but PK, this raises "ORA-00907: missing right parenthesis".
--
This message was sent by Atlassian JIRA
(v7.2.3#72005)
9 years, 3 months