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