[teiid-issues] [JBoss JIRA] (TEIID-4120) Teiid on Oracle Spatial generates incorrect SQL

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4120?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13240874#comment-13240874 ] 

Steven Hawkins commented on TEIID-4120:
---------------------------------------

Ideally we should be done with the 8.12 line.  We're also disappointed as well with the lack of progress on TEIID-3834.  Even the latest versions of EAP 6.4 exhibit the same behavior, so you would also be locked into an older EAP version as well.  Can you describe how the behavior blocks your usage so that we have that captured toward a better resolution?

> Teiid on Oracle Spatial generates incorrect SQL
> -----------------------------------------------
>
>                 Key: TEIID-4120
>                 URL: https://issues.jboss.org/browse/TEIID-4120
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Driver
>    Affects Versions: 8.13.3
>            Reporter: Mike Houwers
>            Assignee: Steven Hawkins
>             Fix For: 9.0, 8.12.5, 8.13.4
>
>
> Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
> {code}
> SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
> {code}
> Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
> {code}
> 14:37:11,934 WARN  [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> 	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
> 	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.$Proxy56.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-01722: invalid number
> 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> 	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
> 	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
> 	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
> 	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
> 	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
> 	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
> 	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
> 	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
> 	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
> 	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
> 	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
> 	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> 	... 18 more
> {code}
> Running the generated SQL in SQLDeveloper results in the error: 
> {code}
> ORA-01722: invalid number
> 01722. 00000 -  "invalid number"
> *Cause:    
> *Action:
> {code}
> Altering the generated SQL in SQLDeveloper to be:
> {code}
> SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
> {code}
> returns the expected results.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)