1 2 Previous Next 21 Replies Latest reply: Oct 25, 2010 11:15 PM by Ramesh Reddy RSS

Select on XML returns duplicate rows

Balaji Seshadri Newbie

When i select on xml and store the result in html or csv i get duplicate results but when i query for that particular record i get only one,because of this i get unqiue constrant error when i try to insert to database.

 

SELECT QUERY.

 

SELECT beam_table.* FROM (EXEC GetXml.getTextFiles('beam.xml')) AS f, XMLTABLE('$d/beams/beam' PASSING XMLPARSE(DOCUMENT F.file) AS d COLUMNS guid string PATH '@guid', name string PATH 'name', lowDb string PATH 'lowDb', highDb string PATH 'highDb', satellite_guid string PATH 'satellite/@guid', launchDate timestamp PATH 'launchDate') AS beam_table

 

Attaching the beam.xml and the result in html which contains GUID(f97d4c7b58ea2245b5261a43c8fa7d4d) appearing two times in SQL results which is not the case  in XML.

  • 1. Re: Select on XML returns duplicate rows
    Ramesh Reddy Master

    Balaji,

     

    I have reproduced the error. Trying to debug this, I will let you know what I find out. Looks like a bug.

     

    Thanks

     

    Ramesh..

  • 2. Re: Select on XML returns duplicate rows
    Balaji Seshadri Newbie

    Ramesh,

     

    Please let me know how long will it take to have it fixed and will you be giving me a patch to deploy and test.

     

    Its critical for us.

     

    Thanks,

     

    Balaji

  • 3. Re: Select on XML returns duplicate rows
    Balaji Seshadri Newbie

    Its happening again for another set of data(70 MB file i was talking about earlier).

     

    Please let us know how soon can we get this fixed.

  • 4. Re: Select on XML returns duplicate rows
    Ramesh Reddy Master

    Actively looking at this right now. I put in a fix today.

  • 5. Re: Select on XML returns duplicate rows
    Balaji Seshadri Newbie

    Thanks Ramesh,Please let me know the instructions for the patch once done.

  • 6. Re: Select on XML returns duplicate rows
    Ramesh Reddy Master

    Balaji,

     

    https://jira.jboss.org/browse/TEIID-1313 has been logged and fixed now in 7.2 line which is the next release and represents 'trunk' in the Teiid SVN.

     

    Teiid 7.2-Final is going to be released towards end of October 2010, however you can get the source from

     

    http://anonsvn.jboss.org/repos/teiid/trunk/

     

    and build it locally using Maven. To build issue command like

     

    mvn clean install -P release

     

    Once build is done, look in the "build/target" directory for the "teiid-7.2.0.CR1-SNAPSHOT-jboss-dist.zip" file that you can use to replace the old install.

     

    Hope this helps.


    Ramesh..

  • 7. Re: Select on XML returns duplicate rows
    Balaji Seshadri Newbie

    Thanks a lot for the quick fix.I couldnt download from the SVN URL you have mentioned here,do you have any other repo.

  • 8. Re: Select on XML returns duplicate rows
    Ramesh Reddy Master

    You need to have SVN client to retrieve the source from the SVN repo. See here for some instructions.

     

    http://community.jboss.org/docs/DOC-12955

     

    See the pre-requisites. You need Subversion and Maven.  We do bundle the source code when we do a release, for that you may have to wait another week for that.

     

    You can also get the source code for the version you are using from sourceforge, then manually do the same edits as in the JIRA (look under subversion commits tab) above and build using the Maven.

     

    Ramesh..

  • 9. Re: Select on XML returns duplicate rows
    Balaji Seshadri Newbie

    Is it possible for you to build the zip and copy it in some shared directory.

     

    Im having tough time buidling it.

     

     

    See the error below.Im not sure which maven repo to point to.

     

    E:\java_projects\svn\teiid>mvn clean install -P release
    [INFO] Scanning for projects...
    [INFO] Reactor build order:
    [INFO]   Teiid
    [INFO]   Common Core
    [INFO]   Teiid Translator API
    [INFO]   Client
    [INFO]   Engine
    [INFO]   Metadata
    [INFO]   Connectors
    [INFO]   JDBC Translator
    [INFO]   LDAP Translator
    [INFO]   Loopback Translator
    [INFO]   File Translator
    [INFO]   Salesforce API
    [INFO]   Salesforce Translator
    [INFO]   File Connector
    [INFO]   Salesforce Connector
    [INFO]   LDAP Connector
    [INFO]   Web Service Connector
    [INFO]   Sandbox
    [INFO]   Yahoo Translator
    [INFO]   Web service Translator
    [INFO]   Console
    [INFO]   Runtime Engine
    [INFO]   Adminshell
    [INFO]   JBoss Cache
    [INFO]   Hibernate Dialect
    [INFO]   teiid-jboss-integration
    [INFO]   Integration Tests
    [INFO]   Common Integration Tests
    [INFO]   DB Dependent Integration Tests
    [INFO]   Documents
    [INFO]   Admin Guide
    [INFO]   Teiid Reference
    [INFO]   Developer's Guide
    [INFO]   Quick Start Guide
    [INFO]   Teiid Client Developers Guide
    [INFO]   Teiid Caching Guide
    [INFO]   Build
    [INFO] ------------------------------------------------------------------------
    [INFO] Building Teiid
    [INFO]    task-segment: [clean, install]
    [INFO] ------------------------------------------------------------------------
    Downloading: http://repository.jboss.org/nexus/content/groups/public//org/apache
    /maven/plugins/maven-source-plugin/2.0.4/maven-source-plugin-2.0.4.pom
    Downloading: http://repository.jboss.org/nexus/content/groups/public//org/apache
    /maven/plugins/maven-source-plugin/2.0.4/maven-source-plugin-2.0.4.pom
    Downloading: http://repository.jboss.org/nexus/content/groups/public//org/apache
    /maven/plugins/maven-source-plugin/2.0.4/maven-source-plugin-2.0.4.pom
    [INFO] ------------------------------------------------------------------------
    [ERROR] BUILD ERROR
    [INFO] ------------------------------------------------------------------------
    [INFO] Error building POM (may not be this project's POM).

     


    Project ID: org.apache.maven.plugins:maven-source-plugin

     

    Reason: POM 'org.apache.maven.plugins:maven-source-plugin' not found in reposito
    ry: Unable to download the artifact from any repository

     

      org.apache.maven.plugins:maven-source-plugin:pom:2.0.4

     

    from the specified remote repositories:
      devm2repo (http://repository.jboss.org/nexus/content/groups/public/)
    for project org.apache.maven.plugins:maven-source-plugin

  • 10. Re: Select on XML returns duplicate rows
    Steven Hawkins Master

    Our wiki page may need some updates.  JBoss switched over to a nexus repository a while ago.  You'll need to use a ~/.m2/settings.xml file like the attached.  See also http://community.jboss.org/wiki/MavenGettingStarted-Developers.

     

    If that still doesn't work we'll go ahead and cut a 7.2 Beta 2 or CR 1 by Monday.

     

    Steve

  • 11. Re: Select on XML returns duplicate rows
    Balaji Seshadri Newbie

    Im not able to compile successfully,please provide me a zip file.

  • 12. Re: Select on XML returns duplicate rows
    Ramesh Reddy Master

    Balaji,

     

    We usally do not like to give patches out, as that would counter productive for us, but we can make exception, as this may be faster route this time. Just place this jar file in the "<jboss-as>server/default/lib" directory. Also, I built this from lastest code on "trunk", so use the "teiid-7.2-beta1" if you can. You can get that from Teiid web site.

     

    PS: SteveH also logged and fixed https://jira.jboss.org/browse/TEIID-1316  This will be 7.2 Final which is coming up soon.

     

    Thanks.

     

    Ramesh..

  • 13. Re: Select on XML returns duplicate rows
    Balaji Seshadri Newbie

    Ramesh,

     

    Thanks for the patch.

     

    I get the below error when i try using your patch,i did update to 7.2 Beta 1.

     

    2010-10-22 11:24:22,708 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue12) [Ljava.lang.Object;@1753a96
    java.lang.IllegalAccessError: tried to access method org.teiid.query.processor.relational.RelationalNode.getDataManager()Lorg/teiid/query/processor/ProcessorDataManager; from class org.teiid.query.processor.relational.NestedTableJoinStrategy
        at org.teiid.query.processor.relational.NestedTableJoinStrategy.initialize(NestedTableJoinStrategy.java:60)
        at org.teiid.query.processor.relational.JoinNode.open(JoinNode.java:136)
        at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:250)
        at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:250)
        at org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:152)
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:134)
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
        at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:70)
        at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:69)
        at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:82)
        at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:91)
        at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:447)
        at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68)
        at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:301)
        at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:240)
        at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:274)
        at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:162)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:274)
        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:161)
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:150)
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:115)
        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:249)
        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:183)
        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:189)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
    2010-10-22 11:24:22,708 ERROR [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue12) Unexpected exception for request abfYQtQ2PmVQ.5
    java.lang.IllegalAccessError: tried to access method org.teiid.query.processor.relational.RelationalNode.getDataManager()Lorg/teiid/query/processor/ProcessorDataManager; from class org.teiid.query.processor.relational.NestedTableJoinStrategy
        at org.teiid.query.processor.relational.NestedTableJoinStrategy.initialize(NestedTableJoinStrategy.java:60)
        at org.teiid.query.processor.relational.JoinNode.open(JoinNode.java:136)
        at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:250)
        at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:250)
        at org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:152)
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:134)
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
        at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:70)
        at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:69)
        at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:82)
        at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:91)
        at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:447)
        at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68)
        at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:301)
        at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:240)
        at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:274)
        at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:162)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:274)
        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:161)
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:150)
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:115)
        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:249)
        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:183)
        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:189)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
    2010-10-22 11:24:22,708 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue12) QueryProcessor: closing processor
    2010-10-22 11:24:22,708 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue12) Removing tuplesource for the request abfYQtQ2PmVQ.5
    2010-10-22 11:24:22,708 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue12) [Ljava.lang.Object;@15979af
    java.lang.IllegalAccessError: tried to access method org.teiid.query.processor.relational.RelationalNode.getDataManager()Lorg/teiid/query/processor/ProcessorDataManager; from class org.teiid.query.processor.relational.NestedTableJoinStrategy
        at org.teiid.query.processor.relational.NestedTableJoinStrategy.initialize(NestedTableJoinStrategy.java:60)
        at org.teiid.query.processor.relational.JoinNode.open(JoinNode.java:136)
        at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:250)
        at org.teiid.query.processor.relational.RelationalNode.open(RelationalNode.java:250)
        at org.teiid.query.processor.relational.RelationalPlan.open(RelationalPlan.java:152)
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:134)
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
        at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:70)
        at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:69)
        at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:82)
        at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:91)
        at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:447)
        at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68)
        at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:301)
        at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:240)
        at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:274)
        at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:162)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:274)
        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:161)
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:150)
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:115)
        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:249)
        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:183)
        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:189)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
    2010-10-22 11:24:22,708 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue12) abfYQtQ2PmVQ.5 end processing WORKING

  • 14. Re: Select on XML returns duplicate rows
    Ramesh Reddy Master

    You need to be working with the latest 7.2-beta1 release. As I said the patch is from my workspace, which is trunk. I am not sure what are other changes went in. That is reason it is it wold be best if you build it from source, as you would get a consistent build.

     

    Ramesh..

1 2 Previous Next