Element "SYSID" is ambiguous, it exists in two or more groups.
gamvi01 Sep 7, 2012 3:38 PMHi 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