2 Replies Latest reply on Sep 13, 2012 2:52 PM by Steven Hawkins

    Element "SYSID" is ambiguous, it exists in two or more groups.

    Vineela Gampa Novice

      Hi Steve/Ramesh,

       

      I have a physical view SECURITY.BASEUSER_TRIGGER which iam using to create another view SECURITY.BASEUSER which is virtual , has updates set to true and iam creting a update trigger plan for it. My select transformation for SECURITY.BASEUSER is (SELECT * FROM SECURITY.BASEUSER_TRIGGER). My selects from SECURITY.BASEUSER are fine.

       

      But when i issue UPDATE "SECURITY.BASEUSER" SET "NAME"=CHORUS USER test' WHERE "SYSID"='CIAS' AND "USERID"='ALUVE01' iam getting the below error. Why am i getting it ? Below is the code used to create SECURITY,BASEUSER. Do i need to set something else ?

      Trigger plan thats set was : FOR EACH ROW BEGIN update security_ldap.baseuser set NAME=new.name WHERE DN=new.userid and SYSID=new.sysid; END

       

      if(isTriggerSet){

                              if(oldViewName.endsWith("_trigger")){

                                  oldViewName = oldViewName.substring(0, oldViewName.length()-("_trigger".length()));

                              }

                              Table newTable = metadataFactory.addTable(oldViewName);

                              newTable.setTableType(Type.View);

       

                              if(viewDefinition.isUpdatable()){

       

                                  newTable.setVirtual(true);

                                  newTable.setSupportsUpdate(true);

                              }

                              newTable.setUpdatePlan(viewDefinition.getTriggerDefinition());

                              newTable.setSelectTransformation("select * from "+viewDefinition.modelName+"."+viewDefinition.viewName);

                               for (final String columnName : viewDefinition.column2type.keySet())

                               {

                                   try

                                   {

                                       Column column = metadataFactory.addColumn(columnName,

                                           TypeFacility.getDataTypeName(ClassLoader.getSystemClassLoader()

                                                                                   .loadClass(viewDefinition.column2type.get(columnName))),

                                           newTable);

                                       if(viewDefinition.isUpdatable()){

                                           column.setUpdatable(true);

                                       }

                                   } catch (final ClassNotFoundException e)

                                   {

                                       Column column = metadataFactory.addColumn(columnName, viewDefinition.column2type.get(columnName), newTable);

                                       if(viewDefinition.isUpdatable()){

                                           column.setUpdatable(true);

                                       }

                                   }

                               }

                          }

       

       

      07 Sep 2012 11:38:30,031 PDT DEBUG [org.teiid.SECURITY] (New I/O server worker #1-3) Logon successful, created session: sessionid=lFHKzyoeTxmD; userName=gamvi01@chorus-teiid-security; vdbName=Chorus; vdbVersion=1; createdTime=Fri Sep 07 11:38:30 PDT 2012; applicationName=JDBC; clientHostName=GAMVI01-WIN7.ca.com; clientHardwareAddress=00FFB05B640C; IPAddress=10.132.88.26; securityDomain=chorus-teiid-security; lastPingTime=Fri Sep 07 11:38:30 PDT 2012

      07 Sep 2012 11:38:30,031 PDT DEBUG [org.teiid.TRANSPORT] (New I/O server worker #1-3)  message: MessageHolder: contents=org.teiid.client.security.LogonResult@d57fdd4 for message:36

      07 Sep 2012 11:38:30,176 PDT DEBUG [org.teiid.TRANSPORT] (New I/O server worker #1-3) processing message:MessageHolder: contents=org.teiid.net.socket.ServiceInvocationStruct@24ed4b59

      07 Sep 2012 11:38:30,176 PDT TRACE [org.teiid.PROCESSOR] (New I/O server worker #1-3) before executeRequest:org.teiid.dqp.internal.process.DQPCore@387b1564(0,org.teiid.client.RequestMessage@56edd2ff)

      07 Sep 2012 11:38:30,176 PDT DEBUG [org.teiid.COMMAND_LOG] (New I/O server worker #1-3)     START USER COMMAND:    startTime=2012-09-07 11:38:30.176    requestID=lFHKzyoeTxmD.0    txID=null    sessionID=lFHKzyoeTxmD    applicationName=JDBC    principal=gamvi01@chorus-teiid-security    vdbName=Chorus    vdbVersion=1    sql=UPDATE "SECURITY.BASEUSER" SET "SECURITY.BASEUSER.NAME"='CHORUS USER test' WHERE  "SECURITY.BASEUSER.USERID"='ALUVE01'

      07 Sep 2012 11:38:30,177 PDT TRACE [org.teiid.PROCESSOR] (New I/O server worker #1-3) after executeRequest : org.teiid.client.util.ResultsFuture@17b78fef

      07 Sep 2012 11:38:30,177 PDT TRACE [org.teiid.RUNTIME] (Worker22_QueryProcessorQueue1284) Beginning work with virtual worker Worker22_QueryProcessorQueue1284

      07 Sep 2012 11:38:30,177 PDT TRACE [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) lFHKzyoeTxmD.0 start processing MORE_WORK

      07 Sep 2012 11:38:30,177 PDT DEBUG [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) Request Thread lFHKzyoeTxmD.0 with state NEW

      07 Sep 2012 11:38:30,177 PDT DEBUG [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) lFHKzyoeTxmD.0 No cache directive

      07 Sep 2012 11:38:30,177 PDT DEBUG [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) lFHKzyoeTxmD.0 executing  UPDATE "SECURITY.BASEUSER" SET "SECURITY.BASEUSER.NAME"='CHORUS USER test' WHERE  "SECURITY.BASEUSER.USERID"='ALUVE01'

      07 Sep 2012 11:38:30,180 PDT TRACE [org.teiid.PLANNER.RESOLVER] (Worker22_QueryProcessorQueue1284) Resolving command UPDATE SECURITY.BASEUSER SET NAME = 'CHORUS USER test' WHERE SECURITY.BASEUSER.USERID = 'ALUVE01'

      07 Sep 2012 11:38:30,187 PDT TRACE [org.teiid.PLANNER.RESOLVER] (Worker22_QueryProcessorQueue1284) Resolving command FOR EACH ROW

      BEGIN ATOMIC

      UPDATE security_ldap.baseuser SET NAME = "new".name WHERE (DN = "new".userid) AND (SYSID = "new".sysid);

      END

      07 Sep 2012 11:38:30,188 PDT TRACE [org.teiid.PLANNER.RESOLVER] (Worker22_QueryProcessorQueue1284) Resolving block BEGIN ATOMIC

      UPDATE security_ldap.baseuser SET NAME = "new".name WHERE (DN = "new".userid) AND (SYSID = "new".sysid);

      END

      07 Sep 2012 11:38:30,188 PDT TRACE [org.teiid.PLANNER.RESOLVER] (Worker22_QueryProcessorQueue1284) Resolving statement UPDATE security_ldap.baseuser SET NAME = "new".name WHERE (DN = "new".userid) AND (SYSID = "new".sysid);

      07 Sep 2012 11:38:30,188 PDT TRACE [org.teiid.PLANNER.RESOLVER] (Worker22_QueryProcessorQueue1284) Resolving command UPDATE security_ldap.baseuser SET NAME = "new".name WHERE (DN = "new".userid) AND (SYSID = "new".sysid)

      07 Sep 2012 11:38:30,191 PDT INFO  [org.teiid.PLANNER] (Worker22_QueryProcessorQueue1284)

      ============================================================================

      USER COMMAND:

      UPDATE SECURITY.BASEUSER SET NAME = 'CHORUS USER test' WHERE SECURITY.BASEUSER.USERID = 'ALUVE01'

       

      ----------------------------------------------------------------------------

      OPTIMIZE:

      UPDATE SECURITY.BASEUSER SET NAME = 'CHORUS USER test' WHERE SECURITY.BASEUSER.USERID = 'ALUVE01'

       

      ----------------------------------------------------------------------------

      GENERATE CANONICAL:

      UPDATE SECURITY.BASEUSER SET NAME = 'CHORUS USER test' WHERE SECURITY.BASEUSER.USERID = 'ALUVE01'

       

      07 Sep 2012 11:38:30,191 PDT DEBUG [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) Request Thread lFHKzyoeTxmD.0 - error occurred

      [QueryPlannerException]Element "SYSID" is ambiguous, it exists in two or more groups.

      1 [QueryResolverException]Element "SYSID" is ambiguous, it exists in two or more groups.

          at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:182)

          at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:190)

          at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:436)

          at org.teiid.dqp.internal.process.Request.processRequest(Request.java:459)

          at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:516)

          at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:276)

          at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)

          at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:215)

          at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:232)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)

          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

          at java.lang.Thread.run(Thread.java:722)

      Caused by: [QueryResolverException]Element "SYSID" is ambiguous, it exists in two or more groups.

          at org.teiid.query.resolver.util.ResolverVisitor.handleUnresolvedElement(ResolverVisitor.java:140)

          at org.teiid.query.resolver.util.ResolverVisitor.internalResolveElementSymbol(ResolverVisitor.java:229)

          at org.teiid.query.resolver.util.ResolverVisitor.resolveElementSymbol(ResolverVisitor.java:173)

          at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:128)

          at org.teiid.query.sql.symbol.ElementSymbol.acceptVisitor(ElementSymbol.java:240)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:52)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:140)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:249)

          at org.teiid.query.sql.symbol.ElementSymbol.acceptVisitor(ElementSymbol.java:240)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:206)

          at org.teiid.query.sql.lang.CompareCriteria.acceptVisitor(CompareCriteria.java:123)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitNodes(AbstractNavigator.java:72)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:212)

          at org.teiid.query.sql.lang.CompoundCriteria.acceptVisitor(CompoundCriteria.java:235)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:497)

          at org.teiid.query.sql.lang.Update.acceptVisitor(Update.java:155)

          at org.teiid.query.sql.navigator.PostOrderNavigator.doVisit(PostOrderNavigator.java:40)

          at org.teiid.query.resolver.util.ResolverVisitor.resolveLanguageObject(ResolverVisitor.java:1012)

          at org.teiid.query.resolver.command.UpdateResolver.resolveProceduralCommand(UpdateResolver.java:74)

          at org.teiid.query.resolver.ProcedureContainerResolver.resolveCommand(ProcedureContainerResolver.java:140)

          at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:294)

          at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:147)

          at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveEmbeddedCommand(UpdateProcedureResolver.java:333)

          at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveStatement(UpdateProcedureResolver.java:189)

          at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveBlock(UpdateProcedureResolver.java:164)

          at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveCommand(UpdateProcedureResolver.java:119)

          at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:294)

          at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:168)

          at org.teiid.query.resolver.QueryResolver.expandCommand(QueryResolver.java:128)

          at org.teiid.query.optimizer.relational.RelationalPlanner.addNestedProcedure(RelationalPlanner.java:577)

          at org.teiid.query.optimizer.relational.RelationalPlanner.createUpdatePlan(RelationalPlanner.java:533)

          at org.teiid.query.optimizer.relational.RelationalPlanner.generatePlan(RelationalPlanner.java:497)

          at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:180)

          ... 13 more

      07 Sep 2012 11:38:30,192 PDT DEBUG [org.teiid.COMMAND_LOG] (Worker22_QueryProcessorQueue1284)     ERROR USER COMMAND:    endTime=2012-09-07 11:38:30.192    requestID=lFHKzyoeTxmD.0    txID=null    sessionID=lFHKzyoeTxmD    principal=gamvi01@chorus-teiid-security    vdbName=Chorus    vdbVersion=1    finalRowCount=null

      07 Sep 2012 11:38:30,192 PDT WARN  [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) Processing exception 'Element "SYSID" is ambiguous, it exists in two or more groups.' for request lFHKzyoeTxmD.0.  Exception type org.teiid.api.exception.query.QueryPlannerException thrown from org.teiid.query.resolver.util.ResolverVisitor.handleUnresolvedElement(ResolverVisitor.java:140). Enable more detailed logging to see the entire stacktrace.

      07 Sep 2012 11:38:30,192 PDT DEBUG [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) Sending error to client lFHKzyoeTxmD.0

      [QueryPlannerException]Element "SYSID" is ambiguous, it exists in two or more groups.

      1 [QueryResolverException]Element "SYSID" is ambiguous, it exists in two or more groups.

          at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:182)

          at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:190)

          at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:436)

          at org.teiid.dqp.internal.process.Request.processRequest(Request.java:459)

          at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:516)

          at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:276)

          at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)

          at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:215)

          at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:232)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:118)

          at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:288)

          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

          at java.lang.Thread.run(Thread.java:722)

      Caused by: [QueryResolverException]Element "SYSID" is ambiguous, it exists in two or more groups.

          at org.teiid.query.resolver.util.ResolverVisitor.handleUnresolvedElement(ResolverVisitor.java:140)

          at org.teiid.query.resolver.util.ResolverVisitor.internalResolveElementSymbol(ResolverVisitor.java:229)

          at org.teiid.query.resolver.util.ResolverVisitor.resolveElementSymbol(ResolverVisitor.java:173)

          at org.teiid.query.resolver.util.ResolverVisitor.visit(ResolverVisitor.java:128)

          at org.teiid.query.sql.symbol.ElementSymbol.acceptVisitor(ElementSymbol.java:240)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitVisitor(AbstractNavigator.java:52)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.postVisitVisitor(PreOrPostOrderNavigator.java:140)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:249)

          at org.teiid.query.sql.symbol.ElementSymbol.acceptVisitor(ElementSymbol.java:240)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:206)

          at org.teiid.query.sql.lang.CompareCriteria.acceptVisitor(CompareCriteria.java:123)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitNodes(AbstractNavigator.java:72)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:212)

          at org.teiid.query.sql.lang.CompoundCriteria.acceptVisitor(CompoundCriteria.java:235)

          at org.teiid.query.sql.navigator.AbstractNavigator.visitNode(AbstractNavigator.java:61)

          at org.teiid.query.sql.navigator.PreOrPostOrderNavigator.visit(PreOrPostOrderNavigator.java:497)

          at org.teiid.query.sql.lang.Update.acceptVisitor(Update.java:155)

          at org.teiid.query.sql.navigator.PostOrderNavigator.doVisit(PostOrderNavigator.java:40)

          at org.teiid.query.resolver.util.ResolverVisitor.resolveLanguageObject(ResolverVisitor.java:1012)

          at org.teiid.query.resolver.command.UpdateResolver.resolveProceduralCommand(UpdateResolver.java:74)

          at org.teiid.query.resolver.ProcedureContainerResolver.resolveCommand(ProcedureContainerResolver.java:140)

          at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:294)

          at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:147)

          at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveEmbeddedCommand(UpdateProcedureResolver.java:333)

          at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveStatement(UpdateProcedureResolver.java:189)

          at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveBlock(UpdateProcedureResolver.java:164)

          at org.teiid.query.resolver.command.UpdateProcedureResolver.resolveCommand(UpdateProcedureResolver.java:119)

          at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:294)

          at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:168)

          at org.teiid.query.resolver.QueryResolver.expandCommand(QueryResolver.java:128)

          at org.teiid.query.optimizer.relational.RelationalPlanner.addNestedProcedure(RelationalPlanner.java:577)

          at org.teiid.query.optimizer.relational.RelationalPlanner.createUpdatePlan(RelationalPlanner.java:533)

          at org.teiid.query.optimizer.relational.RelationalPlanner.generatePlan(RelationalPlanner.java:497)

          at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:180)

          ... 13 more

      07 Sep 2012 11:38:30,192 PDT DEBUG [org.teiid.TRANSPORT] (Worker22_QueryProcessorQueue1284)  message: MessageHolder: contents=ResultsMessage rowCount=0 finalRow=-1 for message:37

      07 Sep 2012 11:38:30,193 PDT TRACE [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) lFHKzyoeTxmD.0 end processing WORKING

      07 Sep 2012 11:38:30,193 PDT TRACE [org.teiid.PROCESSOR] (Worker22_QueryProcessorQueue1284) lFHKzyoeTxmD.0 done processing WORKING

      07 Sep 2012 11:38:30,705 PDT DEBUG [org.teiid.TRANSPORT] (New I/O server worker #1-3) processing message:MessageHolder: contents=org.teiid.net.socket.ServiceInvocationStruct@30393ceb

      07 Sep 2012 11:38:30,705 PDT DEBUG [org.teiid.SECURITY] (New I/O server worker #1-3) closeSession lFHKzyoeTxmD

      07 Sep 2012 11:38:30,705 PDT DEBUG [org.teiid.TXN_LOG] (New I/O server worker #1-3) before cancelTransactions:org.teiid.dqp.internal.process.TransactionServerImpl@6ad8b066(lFHKzyoeTxmD,false)

      07 Sep 2012 11:38:30,705 PDT DEBUG [org.teiid.TXN_LOG] (New I/O server worker #1-3) after cancelTransactions : null

      07 Sep 2012 11:38:30,705 PDT DEBUG [org.teiid.TRANSPORT] (New I/O server worker #1-3)  message: MessageHolder: contents=null for message:38