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

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

    fox123
    fox123 Feb 9, 2015 9:18 AM

      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.

      • 2534 Views
      • Tags: