I have a VDB containing a view with a UUID column with this definition
<vdb name="_GENERATED_users_info" version="6">
<import-vdb name="_INTERNAL_internal" version="1"/>
<model name="GEN_view" type="VIRTUAL">
<metadata type="DDL">
<![CDATA[ CREATE VIEW "_view_workflow_data" ("uuid" VARCHAR, "email" VARCHAR) OPTIONS (UPDATABLE 'true') AS SELECT "public"."security_users_info"."uuid" AS "uuid", "public"."security_users_info"."email" AS "email" FROM "public"."security_users_info" ]]></metadata>
</model>
</vdb>
Column security_users_info.uuid
is of type UUID
.
When I try to execute a query
SELECT "uuid", "email" FROM "_view_workflow_data" WHERE ("uuid" IN (?''718e52dd-058a-4306-9213-c999181163c7') AND 1 = 1)
I get an error org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying. The error doesn't come from the query I sent but from a query generated by TEIID executed on the physical table
TEIID11004 Error executing statement(s): [Prepared Values: ['718e52dd-058a-4306-9213-c999181163c7'] SQL: SELECT cast(g_0."uuid" AS varchar), g_0."email" FROM "public"."security_users_info" AS g_0 WHERE g_0."uuid" = ?]
I tried to convert the column in the view definition to varchar like this CONVERT("public"."security_users_info"."uuid", VARCHAR) AS "uuid"
but I get the same error.
The failing query works fine directly in postgresql as it implicitly converts the uuid text into a uuid type.
I'm aware of the section about UID types in http://teiid.github.io/teiid-documents/master/content/reference/as_jdbc-translators.html but as the implicit conversion seems to work when I execute the query directly in postgresql, I would expect that section not to apply in this situation.
I'm using TEIID 16 with postgresql 9.5 and postgres JDBC driver 42.2.
Am I doing something in a wrong way or is this not supposed to work?
Whole stacktrace:
ERROR# - org.jooq.exception.DataAccessException: SQL [select "uuid", "email" from "_view_workflow_data" where ("uuid" in (?) and 1 = 1)]; TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 _SOURCE_7772c4bdad42f2468b3ecdc71d89634bd9b80ff2ca87900862ab740105049694: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['718e52dd-058a-4306-9213-c999181163c7'] SQL: SELECT cast(g_0."uuid" AS varchar), g_0."email" FROM "public"."security_users_info" AS g_0 WHERE g_0."uuid" = ?] (c.m.w.c.d.DataObjectVerticle#?)org.jooq.exception.DataAccessException: SQL [select "uuid", "email" from "_view_workflow_data" where ("uuid" in (?) and 1 = 1)]; TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 _SOURCE_7772c4bdad42f2468b3ecdc71d89634bd9b80ff2ca87900862ab740105049694: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['718e52dd-058a-4306-9213-c999181163c7'] SQL: SELECT cast(g_0."uuid" AS varchar), g_0."email" FROM "public"."security_users_info" AS g_0 WHERE g_0."uuid" = ?]
at org.jooq_3.13.0-SNAPSHOT.DEFAULT.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:2717)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:382)
at com.mathesio.workflow.core.dataObject.DataObjectService.lambda$select$16(DataObjectService.java:365)
at com.mathesio.teiid.Connections.ConnectionManager.lambda$getConnectionJOOQ$0(ConnectionManager.java:187)
at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:609)
at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:606)
at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:537)
at org.jooq.impl.Tools$12$1.block(Tools.java:4847)
at java.base/java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3128)
at org.jooq.impl.Tools$12.get(Tools.java:4844)
at org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:589)
at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:506)
at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:606)
at com.mathesio.teiid.Connections.ConnectionManager.lambda$getConnectionJOOQ$1(ConnectionManager.java:186)
at com.mathesio.workflow.core.database.transaction.TransactionJPA.lambda$new$42(TransactionJPA.java:1813)
at com.mathesio.workflow.core.database.transaction.TransactionJPA.lambda$new$19(TransactionJPA.java:388)
at com.mathesio.workflow.core.database.transaction.TransactionJPA.runCore(TransactionJPA.java:993)
at com.mathesio.workflow.core.database.transaction.TransactionJPA.lambda$run$25(TransactionJPA.java:1306)
at com.mathesio.workflow.core.database.transaction.TransactionJPA$1.call(TransactionJPA.java:755)
at com.mathesio.workflow.core.database.transaction.TransactionJPA$1.call(TransactionJPA.java:738)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at com.mathesio.workflow.core.database.transaction.PriorityFuture.run(PriorityFuture.java:58)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 _SOURCE_7772c4bdad42f2468b3ecdc71d89634bd9b80ff2ca87900862ab740105049694: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['718e52dd-058a-4306-9213-c999181163c7'] SQL: SELECT cast(g_0."uuid" AS varchar), g_0."email" FROM "public"."security_users_info" AS g_0 WHERE g_0."uuid" = ?]
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:131)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:67)
at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:783)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:62)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:581)
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:131)
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:36)
at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75)
at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:281)
at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:333)
at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:423)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:590)
at org.teiid.jdbc.PreparedStatementImpl.execute(PreparedStatementImpl.java:240)
at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209)
at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:3935)
at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:294)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:368)
... 23 common frames omitted
Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 _SOURCE_7772c4bdad42f2468b3ecdc71d89634bd9b80ff2ca87900862ab740105049694: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['718e52dd-058a-4306-9213-c999181163c7'] SQL: SELECT cast(g_0."uuid" AS varchar), g_0."email" FROM "public"."security_users_info" AS g_0 WHERE g_0."uuid" = ?]
at [email protected]//org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:397)
at [email protected]//org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:157)
at [email protected]//org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
at [email protected]//org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at [email protected]//org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:139)
at [email protected]//org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:147)
at [email protected]//org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:110)
at [email protected]//org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at [email protected]//org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at [email protected]//org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:491)
at [email protected]//org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:361)
at [email protected]//org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:43)
at [email protected]//org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:284)
at [email protected]//org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:283)
at [email protected]//org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124)
at [email protected]//org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.teiid.translator.TranslatorException: 0 Remote org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['718e52dd-058a-4306-9213-c999181163c7'] SQL: SELECT cast(g_0."uuid" AS varchar), g_0."email" FROM "public"."security_users_info" AS g_0 WHERE g_0."uuid" = ?]
at [email protected]//org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at [email protected]//org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:405)
at jdk.internal.reflect.GeneratedMethodAccessor106.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at [email protected]//org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228)
at [email protected]//com.sun.proxy.$Proxy45.execute(Unknown Source)
at [email protected]//org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at [email protected]//org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
... 17 common frames omitted
Caused by: java.sql.SQLException: Remote org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 109
at [email protected]//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
at [email protected]//org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
at [email protected]//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
at [email protected]//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at [email protected]//org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at [email protected]//org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
at [email protected]//org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)
at [email protected]//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at [email protected]//org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
... 25 common frames omitted