14 Replies Latest reply on Apr 1, 2014 5:32 PM by Barry LaFond

    IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

    tanmoypalit Newbie

      Hi

      I am migrating projects in Teiid Designer from v 7.7 to v 8.3.2

      There is an existing query in a Teiid procedure which has a sub query in IN clause. The query works fine in Teiid Designer 7.7 but in 8.3.2, I am getting an unknown error.

      If the select query is removed from the IN clause then it works fine.

      Following is the query:

       

      CREATE VIRTUAL PROCEDURE
      BEGIN
        SELECT * FROM VQL_V1.TRANSACTIONDocument WHERE VQL_V1.TRANSACTIONDocument.TRANSACTION.Item_COLLECTION.@Unique_Identifier IN /*+ DJ */ (SELECT DISTINCT VBL_CD.Items.unique_identifier FROM VBL_CD.Items WHERE VBL_CD.Items.field_name = v1.getItemsByField.FIELDNAME);
      END
      

       

      Error in the designer:

      3-27-2014 3-58-14 PM.png

      I am not getting any information about the error. Currently migrated project could not be compiled in designer as there are many queries like this in the project which are resulting in same error.

      Any help will be appreciated.

       

      Thanks

      Tanmoy

        • 1. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
          Ramesh Reddy Master

          Tanmoy,

           

          Try deploying the old VDB as is to the new Teiid server and see if you encounter any errors. Also, see if you see any errors executing the SQL in the transformation directly against the Teiid server.

           

          You can also open JIRA with a sample VDB that exhibits the issue.

           

          Ramesh..

          • 2. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
            tanmoypalit Newbie

            Hi Ramesh,

            I tried to deploy the VDB as is (from 7.7 to 8.3) but got the following error:

            19:34:46,559 ERROR [org.jboss.msc.service.fail] (MSC service thread 1-4) MSC000001: Failed to start service jboss.deployment.unit."ITEMSVDB.vdb".PARSE: org.jboss.msc.service.StartException in service jboss.deployment.unit."ITEMSVDB.vdb".PARSE: JBAS018733: Failed to process phase PARSE of deployment "ITEMSVDB.vdb"

              at org.jboss.as.server.deployment.DeploymentUnitPhaseService.start(DeploymentUnitPhaseService.java:127) [jboss-as-server-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]

              at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1811) [jboss-msc-1.0.4.GA-redhat-1.jar:1.0.4.GA-redhat-1]

              at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1746) [jboss-msc-1.0.4.GA-redhat-1.jar:1.0.4.GA-redhat-1]

              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.jboss.as.server.deployment.DeploymentUnitProcessingException: TEIID50017

              at org.teiid.jboss.VDBParserDeployer.parseVDBXML(VDBParserDeployer.java:119)

              at org.teiid.jboss.VDBParserDeployer.scanVDB(VDBParserDeployer.java:88)

              at org.teiid.jboss.VDBParserDeployer.scanVDB(VDBParserDeployer.java:83)

              at org.teiid.jboss.VDBParserDeployer.deploy(VDBParserDeployer.java:72)

              at org.jboss.as.server.deployment.DeploymentUnitPhaseService.start(DeploymentUnitPhaseService.java:120) [jboss-as-server-7.2.1.Final-redhat-10.jar:7.2.1.Final-redhat-10]

              ... 5 more

            Caused by: org.xml.sax.SAXParseException; lineNumber: 61; columnNumber: 168; cvc-identity-constraint.4.1: Duplicate unique value [ITEM_VALUES] declared for identity constraint "modelNameUnique" of element "vdb".

              at org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source)

              at org.apache.xerces.util.ErrorHandlerWrapper.error(Unknown Source)

              at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)

              at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)

              at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)

              at org.apache.xerces.impl.xs.XMLSchemaValidator$XSIErrorReporter.reportError(Unknown Source)

              at org.apache.xerces.impl.xs.XMLSchemaValidator.reportSchemaError(Unknown Source)

              at org.apache.xerces.impl.xs.XMLSchemaValidator$UniqueValueStore.checkDuplicateValues(Unknown Source)

              at org.apache.xerces.impl.xs.XMLSchemaValidator$ValueStoreBase.addValue(Unknown Source)

              at org.apache.xerces.impl.xs.identity.Field$Matcher.matched(Unknown Source)

              at org.apache.xerces.impl.xs.identity.XPathMatcher.startElement(Unknown Source)

              at org.apache.xerces.impl.xs.identity.Selector$Matcher.startElement(Unknown Source)

              at org.apache.xerces.impl.xs.XMLSchemaValidator.handleStartElement(Unknown Source)

              at org.apache.xerces.impl.xs.XMLSchemaValidator.startElement(Unknown Source)

              at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source)

              at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)

              at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)

              at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)

              at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)

              at org.apache.xerces.jaxp.validation.StreamValidatorHelper.validate(Unknown Source)

              at org.apache.xerces.jaxp.validation.ValidatorImpl.validate(Unknown Source)

              at javax.xml.validation.Validator.validate(Validator.java:124) [rt.jar:1.7.0_51]

              at org.teiid.adminapi.impl.VDBMetadataParser.validate(VDBMetadataParser.java:94)

              at org.teiid.jboss.VDBParserDeployer.parseVDBXML(VDBParserDeployer.java:103)

              ... 9 more

            Although the same VDB is currently running under Teiid 7.7

             

            Regards

            Tanmoy

            • 3. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
              Ramesh Reddy Master

              Later versions of the Teiid added vbd.xml file validation, there seems to be an issue with duplication of unique key name.  Try creating a new VDB in the 8.3.2 from your old models.

              • 4. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                tanmoypalit Newbie

                Thanks Ramesh for the reply.

                But creating VDB with the old models already has issue with the Designer 8.3.2. That is the first thing I tried and got the "ERROR:" in the designer without any description (see the actual issue).

                 

                Regards

                Tanmoy

                • 5. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                  Ramesh Reddy Master

                  Tanmoy,

                   

                  Yes, that is an issue. Seems like those are un-related issues. We would need to see the VDB file to figure out what is the error. If the VDB is sensitive in nature, you can contact Redhat support.

                   

                  Ramesh..

                  • 6. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                    Steven Hawkins Master

                    Just to add a little more, Designer should no longer allow you to have two models with the same name in the same vdb.  If you can, you could start deleting model from your vdb (and transformations from your workspace) until you get back to a good state.  Then work your way back incrementally.  That may help to isolate what you are seeing.

                    • 7. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                      tanmoypalit Newbie

                      I don't have 2 models with the same name but I have XSD files with same name. These XSDs which are refering to Root transaction element are same for different data type but created in different folders.

                      Each root transaction elements have the same name but child elements are different.

                      If this does not work anymore, then all Document models which are created from these XSD will now have to be re-created and mapped again which is almost like creating everything from the start.

                       

                      Regards

                      Tanmoy

                      • 8. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                        Steven Hawkins Master

                        > If this does not work anymore, then all Document models which are created from these XSD will now have to be re-created and mapped again which is almost like creating everything from the start.

                         

                        Unfortunately Designer creates a model entry in the vdb.xml to represent an xsd.  However these entries are quite meaningless to Teiid and we've requested that they don't add them.  Can you rename one of the xsds? 

                        • 9. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                          tanmoypalit Newbie

                          If I rename the XSDs then also all the references in the XML Models ("XSD Component" in the Properties of the element) will be broken and the only to fix that reference is to create the XML model from the latest file.

                          Also I am not very sure if that will cause any other problem.

                           

                          Regards

                          Tanmoy

                          • 10. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                            Ramesh Reddy Master

                            Try refactor -> rename, it may update all references automatically. Otherwise after rename run rebuild imports in the projects menu.

                            • 11. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                              Barry LaFond Master

                              Teiid Designer 8.4 included a feature to prevent users from adding models (xmi/xsd) with same name to a VDB. It's not in 8.3.2

                               

                              see:  [TEIIDDES-1945] Unable to deploy and test a VDB with same name models with different extensions - JBoss Issue Tracker

                              • 12. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                                tanmoypalit Newbie

                                Actually I tried refactor->rename for the XSD, but got the following error:

                                java.util.regex.PatternSyntaxException: Unexpected internal error near index 1

                                \

                                ^

                                at java.util.regex.Pattern.error(Pattern.java:1924)

                                at java.util.regex.Pattern.compile(Pattern.java:1671)

                                at java.util.regex.Pattern.<init>(Pattern.java:1337)

                                at java.util.regex.Pattern.compile(Pattern.java:1022)

                                at java.lang.String.split(String.java:2313)

                                at java.lang.String.split(String.java:2355)

                                at org.teiid.designer.ui.refactor.RefactorResourcesUtils.getRelativePath(RefactorResourcesUtils.java:221)

                                at org.teiid.designer.ui.refactor.rename.RenameResourceRefactoring$RelatedResourceCallback.indexFile(RenameResourceRefactoring.java:65)

                                at org.teiid.designer.ui.refactor.RefactorResourcesUtils.calculateRelatedResources(RefactorResourcesUtils.java:980)

                                at org.teiid.designer.ui.refactor.rename.RenameResourceRefactoring.checkFinalConditions(RenameResourceRefactoring.java:246)

                                at org.eclipse.ltk.core.refactoring.CheckConditionsOperation.run(CheckConditionsOperation.java:85)

                                at org.eclipse.ltk.core.refactoring.CreateChangeOperation.run(CreateChangeOperation.java:121)

                                at org.eclipse.ltk.core.refactoring.PerformChangeOperation.run(PerformChangeOperation.java:209)

                                at org.eclipse.core.internal.resources.Workspace.run(Workspace.java:2345)

                                at org.eclipse.ltk.internal.ui.refactoring.WorkbenchRunnableAdapter.run(WorkbenchRunnableAdapter.java:87)

                                at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)

                                Also I tried to rename the files outside Teiid Designer and then tried Rebuild imports but it does not pick the right reference. Anyway I will look into this a little more.

                                Regards

                                Tanmoy

                                • 13. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                                  tanmoypalit Newbie

                                  Hi Barry,

                                  In my dev Studio, Installation details, I see the following:

                                    Teiid Designer 8.3.2.Final-v20140319-1907-B41 org.teiid.designer.feature.feature.group JBoss by Red Hat, Inc.

                                   

                                  If this is the case then I should not have problem with duplicate names but I am getting the following error while saving VDB after adding the models (in the Designer):

                                  Save Failed

                                  javax.xml.bind.MarshalException

                                  - with linked exception:

                                  [org.xml.sax.SAXParseException; lineNumber: 0; columnNumber: 0; Duplicate unique value [ITEM_TRANSACTION] declared for identity constraint of element "vdb".]

                                   

                                  Regards

                                  Tanmoy

                                  • 14. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2
                                    Barry LaFond Master

                                    Tanmoy,

                                     

                                    The Marshalling exception is because the 8.3.2 definition for the vdb.xml uses the model name (minus the extension) as the "name" value for the <model> element. The inclusion of XSD files (models) in VDBs is still a standard design requirement.  We'd have to look at that requirement and the possible use-cases. Though Teiid runtime does not use the XSD files, the VDB artifact is still a mechanism to encapsulate the models that make up your VDB. Any XSD used in creating your XML/WS models will end up being a dependency to those models (imports). We have use cases where users create their own complex types and use those types for column datatypes.

                                     

                                    The fix I'm referring to was to prevent users from creating/managing models with duplicate names (minus the extension).  Teiid Deisgner 8.4.0 introduces a project validation warning and validation checks in the VDB Editor to prevent adding a model with duplicate name.

                                     

                                    Barry