3 Replies Latest reply on Nov 7, 2018 2:56 PM by shawkins

    Teiid array inserts incorrectly translated to PostgreSQL?

    woko
    woko Nov 6, 2018 9:11 AM

      Hi, I've created a dynamic VDB with a BOOLEAN[] column that is backed by PostgreSQL (the DDL is something like

      CREATE VIEW "_view_data" ("id" SERIAL, 
         "booltest" BOOLEAN[], 
         PRIMARY KEY ("id")) 
OPTIONS (UPDATABLE 'true') 
      AS SELECT "public"."form_formname"."id" AS "id", "public"."form_formname"."booltest" AS "booltest" 
         FROM "public"."form_formname").

      I'm executing a simple insert like so:

      INSERT INTO "form_formname" ("booltest") VALUES ( (true, false, true, false) )

      However, I'm getting the following error:

      14:58:38,451 WARN  [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue110) vCO+auSpiNon  Connector worker process failed for atomic-request=vCO+auSpiNon.0.0.0: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013: TEIID11004 Error executing statement(s): [Prepared Values: [TRUE, FALSE, FALSE, TRUE] SQL:  INSERT INTO "public"."form_formname" ("booltest") VALUES ((?, ?, ?, ?))]
     at org.jboss.teiid.translator.jdbc//org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:345)
     at org.jboss.teiid.translator.jdbc//org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:82)
     at org.jboss.teiid//org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:434)
     at org.jboss.teiid//org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:393)
     at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
     at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.base/java.lang.reflect.Method.invoke(Method.java:564)
     at org.jboss.teiid//org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
     at org.jboss.teiid//com.sun.proxy.$Proxy36.execute(Unknown Source)
     at org.jboss.teiid//org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
     at org.jboss.teiid//org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
     at org.jboss.teiid//org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
     at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
     at org.jboss.teiid//org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
     at org.jboss.teiid//org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:278)
     at org.jboss.teiid//org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
     at org.jboss.teiid//org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
     at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135)
     at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
     at java.base/java.lang.Thread.run(Thread.java:844)
Caused by: org.postgresql.util.PSQLException: ERROR: column "booltest" is of type boolean[] but expression is of type record
  Hint: You will need to rewrite or cast the expression.
  Position: 63
     at org.postgresql//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
     at org.postgresql//org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
     at org.postgresql//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
     at org.postgresql//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
     at org.postgresql//org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
     at org.postgresql//org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
     at org.postgresql//org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
     at org.jboss.ironjacamar.jdbcadapters//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
     at org.jboss.teiid.translator.jdbc//org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:315)
     ... 20 more

14:58:38,455 WARN  [org.teiid.PROCESSOR] (Worker19_QueryProcessorQueue111) vCO+auSpiNon TEIID30020 Processing exception for request vCO+auSpiNon.0 ' TEIID30504 _SOURCE_fd70ec61e958504f3eeae6a244e65fbead03c5ad36c08f675e82e396109a7180: 0 TEIID11013:TEIID11004  Error executing statement(s): [Prepared Values: [TRUE, FALSE, FALSE, TRUE] SQL: INSERT INTO "public"."form_formname" ("booltest") VALUES ((?, ?, ?, ?))]'.  Originally TeiidProcessingException 'ERROR: column "booltest" is of type boolean[] but expression is of type record
  Hint: You will need to rewrite or cast the expression.
  Position: 63' QueryExecutorImpl.java:2433. Enable more detailed logging to see the entire stacktrace.

      It seems like the query is not being translated corretly; PostgreSQL has a different array syntax:

      [CURRENT SQL] INSERT INTO "public"."form_formname" ("booltest") VALUES ((true, false, true, false))
[POSTGRESQL] INSERT INTO "public"."form_formname" ("booltest") VALUES ('{true, false, true, false}')

      I'm using Teiid 11.2.0 with postgresql translator and postgresql-42.2.2.jar driver. Is there anything I need to do to enable correct translation of array literals? I looked through the postgresql translator source and couldn't find anything pertaining to arrays.
      Many thanks,
      Vojta