9 Replies Latest reply on Nov 12, 2014 11:27 AM by Steven Hawkins

    teiid_rel:native-query

    Tom Arnold Novice

      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.

        • 1. Re: teiid_rel:native-query
          Steven Hawkins Master

          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.

          • 2. Re: teiid_rel:native-query
            Tom Arnold Novice

            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

            • 3. Re: teiid_rel:native-query
              Steven Hawkins Master

              > 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.

              1 of 1 people found this helpful
              • 4. Re: teiid_rel:native-query
                Tom Arnold Novice

                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

                • 5. Re: teiid_rel:native-query
                  Steven Hawkins Master

                  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.

                  • 6. Re: teiid_rel:native-query
                    Tom Arnold Novice

                    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

                    • 7. Re: teiid_rel:native-query
                      Steven Hawkins Master

                      > 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)'.

                      1 of 1 people found this helpful
                      • 8. Re: teiid_rel:native-query
                        Tom Arnold Novice

                        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

                        • 9. Re: teiid_rel:native-query
                          Steven Hawkins Master

                          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.