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

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

tanmoypalit Mar 27, 2014 5:06 PM

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:

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

rareddy Mar 31, 2014 9:40 AM (in response to tanmoypalit)

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..
Actions
2. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

tanmoypalit Mar 31, 2014 3:52 PM (in response to rareddy)

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
Actions
3. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

rareddy Mar 31, 2014 5:48 PM (in response to tanmoypalit)

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.
Actions
4. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

tanmoypalit Apr 1, 2014 8:28 AM (in response to rareddy)

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
Actions
5. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

rareddy Apr 1, 2014 8:40 AM (in response to tanmoypalit)

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..
Actions
6. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

shawkins Apr 1, 2014 2:36 PM (in response to rareddy)

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.
Actions
7. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

tanmoypalit Apr 1, 2014 2:49 PM (in response to shawkins)

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
Actions
8. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

shawkins Apr 1, 2014 2:59 PM (in response to tanmoypalit)

> 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?
Actions
9. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

tanmoypalit Apr 1, 2014 3:06 PM (in response to tanmoypalit)

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
Actions
10. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

rareddy Apr 1, 2014 3:11 PM (in response to tanmoypalit)

Try refactor -> rename, it may update all references automatically. Otherwise after rename run rebuild imports in the projects menu.
Actions
11. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

blafond Apr 1, 2014 3:21 PM (in response to rareddy)

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
Actions
12. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

tanmoypalit Apr 1, 2014 3:25 PM (in response to rareddy)

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
Actions
13. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

tanmoypalit Apr 1, 2014 3:34 PM (in response to blafond)

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
Actions
14. Re: IN Clause SubQuery results in an unknown error in Teiid Designer 8.3.2

blafond Apr 1, 2014 5:32 PM (in response to tanmoypalit)

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
Actions

Go to original post