teiid_rel:native-query

[tom9729]

I'm trying to do something like this in the DDL section of my VDB. Basically the idea is to have my code use the simplified functions, and then have a different VDB for each database I'm sitting on. For example, Postgres would implement ANYINTERACT with ST_Intersects() instead.

create foreign function "RECT"(

  "X1" double,

  "Y1" double,

  "X2" double,

  "Y2" double

) returns object options (

  "teiid_rel:native-query" 'sdo_geometry(2003, 8307, null, sdo_elem_info_array(1, 1003, 3), sdo_ordinate_array($1, $2, $3, $4))'

);

create foreign function "ANYINTERACT"(

  "G1" object,

  "G2" object

) returns boolean options (

  "teiid_rel:native-query" 'sdo_anyinteract($1, $2) = \'TRUE\''

);

First off, I can't get this to work because I can't figure out how to escape the quotes around TRUE. As a workaround I tried this instead, opting to just do the "= 'TRUE'" in the query.

create foreign function "ANYINTERACT"(

  "G1" object,

  "G2" object

) returns string options (

  "teiid_rel:native-query" 'sdo_anyinteract($1, $2)''

);

select foo_id from foo_spatial where anyinteract(footprint,rect(-20,-20,20,20)) = 'TRUE';

Unfortunately that fails with an exception...

20:21:39,581 ERROR [org.teiid.PROCESSOR] (Worker17_QueryProcessorQueue8155) KptxrAvVK+yw TEIID30019 Unexpected exception for request KptxrAvVK+yw.11: java.lang.reflect.UndeclaredThrowableException

    at com.sun.proxy.$Proxy46.execute(Unknown Source)

    at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_71]

    at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_71]

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_71]

    at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_71]

Caused by: java.lang.reflect.InvocationTargetException

    at sun.reflect.GeneratedMethodAccessor128.invoke(Unknown Source) [:1.7.0_71]

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_71]

    at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_71]

    at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:208) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    ... 12 more

Caused by: java.lang.ClassCastException: org.teiid.language.Argument cannot be cast to org.teiid.language.Parameter

    at org.teiid.translator.jdbc.JDBCBaseExecution.bind(JDBCBaseExecution.java:94)

    at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:122)

    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:327) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    ... 16 more

Any ideas? I can provide more details if necessary.

[shawkins]

There is an issue when using prepared statements and a literal argument value.  The logic should be adding the literal value to the bindings, instead it is adding the whole argument.  Can you convert this into a jira?  If you disable bind variables it should work.

[tom9729]

Thanks, JIRAed: [TEIID-3207] JDBC native query with literals - JBoss Issue Tracker

Is there a way to escape single quotes (for string literals) in DDL? I would really like to be able to wrap the Oracle spatial functions in a foreign function that returns boolean, but need a way to do 'TRUE' in the native query.

Tom

[shawkins]

> Is there a way to escape single quotes (for string literals) in DDL?

A single quote is escaped by another single quote.

Thanks for catching TEIID-3207, it will be addressed in 8.9 final and beyond.

[tom9729]

I'm a little bit closer now, but it looks like some additional SQL is being appended that is breaking things. Notice the " = 1" that is appended at the end. If I change the return of the function to be non-boolean, then it no longer does this.

create foreign function "RECT"(

  "X1" double,

  "Y1" double,

  "X2" double,

  "Y2" double

) returns object options (

  "teiid_rel:native-query" 'sdo_geometry(2003, 8307, null, sdo_elem_info_array(1, 1003, 3), sdo_ordinate_array($1, $2, $3, $4))'

);

create foreign function "ANYINTERACT"(

  "G1" object,

  "G2" object

) returns boolean options (

  "teiid_rel:native-query" '(sdo_anyinteract($1, $2) = ''TRUE'')'

);

select foo_id from foo_spatial where anyinteract(footprint, rect(-20,-20,20,20));

Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 933 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [-20.0, -20.0, 20.0, 20.0] SQL: SELECT g_0."FOO_ID" FROM "FOO"."FOO_SPATIAL" g_0 WHERE (sdo_anyinteract(g_0."FOOTPRINT", sdo_geometry(2003, 8307, null, sdo_elem_info_array(1, 1003, 3), sdo_ordinate_array(?, ?, ?, ?))) = 'TRUE') = 1]

    at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)

    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:327) [teiid-engine-8.10.0.Alpha1-SNAPSHOT.jar:8.10.0.Alpha1-SNAPSHOT]

    ... 18 more

Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

Thanks!

Tom

[shawkins]

The engine when it sees a boolean expression in a condition, such as WHERE FOO, will automatically convert into the comparison form - WHERE FOO = TRUE.  Then the translator will substitute the literal 1 for TRUE.

[tom9729]

That seems wrong. I'm currently working around this behaviour by changing my native query to this.

sdo_anyinteract($1, $2) = ''TRUE'' and 1

Am I missing something?

Thanks!

Tom

[shawkins]

> That seems wrong.

What seems wrong?

> Am I missing something?

Based upon what you have in your comments and comment #5, I think what you want is just '(sdo_anyinteract($1, $2)'.

[tom9729]

I think I'm conflating boolean expressions with boolean return values. "= 'TRUE'" is part of the function signature for the Oracle spatial functions. It's an error to test against anything else. For comparison, the PostGIS and MySQL functions return boolean 1/0.

This seems to work for me:

create foreign function "ANYINTERACT"(

  "G1" object,

  "G2" object

) returns boolean options (

  "teiid_rel:native-query" 'case when sdo_anyinteract($1, $2) = ''TRUE'' then 1 else 0 end'

);

Sorry for the misunderstanding!

Tom

[shawkins]

Yes, since there isn't a true boolean type in oracle sql, you would need it to return 1/0.  Otherwise it would need to return a string and you'd do the true/false check in Teiid.