3 Replies Latest reply on Feb 9, 2015 10:27 AM by Steven Hawkins

    Syntax error at or near "." running an INSERT INTO query in PostgreSQL

    Salvatore R Newbie

      Hi,

       

      I defined these two tables in PostgreSQL:

      CREATE TABLE public.source (a integer);
      CREATE TABLE public.target (a integer);
      

       

      and then I defined the following two views in the VDB, in a virtual model called "test_views":

      create view v1 as select a from pg.source group by a;
      create view v2 as select a from test_views.v1 group by a;
      

       

      When I run this INSERT INTO query:

      insert into pg.target
      SELECT * FROM test_views.v2;
      

       

      I get this exception:

      14:40:23,702 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) ouLv6EaK1uoo Connector worker process failed for atomic-request=ouLv6EaK1uoo.0.2.0: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."target" ("a") SELECT v1.a AS a FROM (SELECT "public"."source"."a" AS a FROM "public"."source" GROUP BY "public"."source"."a") AS test_views.v1 GROUP BY v1.a]
              at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:252) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
              at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:80) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:368)
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:335)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_51]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_51]
              at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_51]
              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:209)
              at com.sun.proxy.$Proxy47.execute(Unknown Source)
              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
              at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_51]
              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
              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) [rt.jar:1.7.0_51]
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
              at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
      Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
        Position: 162
              at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
              at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
              at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
              at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:223) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
              ... 20 more
      14:40:23,736 WARN  [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) ouLv6EaK1uoo TEIID30020 Processing exception for request ouLv6EaK1uoo.0 'TEIID30504 test_tables_pg
      : 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."target" ("a") SELECT v1.a AS a FROM (SELECT "public"."source"."a"
      AS a FROM "public"."source" GROUP BY "public"."source"."a") AS test_views.v1 GROUP BY v1.a]'. Originally TeiidProcessingException 'ERROR: syntax error at or near "."
        Position: 162' QueryExecutorImpl.java:2157. Enable more detailed logging to see the entire stacktrace.
      

       

      It seems that the error occurs only when fully qualified names are used in the view definitions. For example, defining v2 as follows (using the simple name for v1 instead of its fully qualified name):

       

      create view v2 as select a from v1 group by a;

       

      the INSERT query is correctly executed.

       

      This behavior can be reproduced in Teiid-8.10.0-Beta2.