Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4120

Teiid on Oracle Spatial generates incorrect SQL

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 9.0, 8.12.5, 8.13.4
    • 8.13.3
    • JDBC Driver
    • None
    • Hide

      Set up a standard Teiid 8.13.3 installation, pointing to an Oracle database, containing a table FOO that has a column named ID (any type) and an SDO_GEOMETRY column named FOOTPRINT.

      Using Squirrel to connect to the Teiid instance, run the query:

      SELECT ID
FROM FOO
WHERE st_contains(footprint, footprint);
      Show
      Set up a standard Teiid 8.13.3 installation, pointing to an Oracle database, containing a table FOO that has a column named ID (any type) and an SDO_GEOMETRY column named FOOTPRINT. Using Squirrel to connect to the Teiid instance, run the query: SELECT ID FROM FOO WHERE st_contains(footprint, footprint);

    Description

      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:

      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

      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:

      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

      Running the generated SQL in SQLDeveloper results in the error: 

      ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

      Altering the generated SQL in SQLDeveloper to be:

      SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';

      returns the expected results.

      Attachments

        Issue Links

          causes

          Bug - A problem that impairs or prevents the functions of the product. TEIID-5045 Rewriter allows "true" criteria to be modified

          • Blocker - To be worked above all other priorities. This term is chosen when the severity of the issue is very high, has no workaround, or the effort for the change is comparatively low. Issues that may be very publicly visible and could generate significant media attention may also drive a higher priority.
          • Resolved

          Activity

            People

              rhn-engineering-shawkins Steven Hawkins
              mhouwers_jira Mike Houwers (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: