-
Bug
-
Resolution: Won't Do
-
Major
-
None
-
8.12.5
-
None
-
Workaround Exists
-
- in the PATH expression match the text() child node directly
Function XMLTABLE provides ability to create a relational table from xml document, it also supports parsing various datatypes.
When the xml element is parsed as integer, and the matched element is empty, invalid format error occurs.
Invocation of XMLTABLE:
CREATE VIEW EMPTY_ELEMENT_INTEGER_NO_PATH(x integer) AS select * from xmltable('/a' passing convert('<a><b></b></a>', xml) columns b integer) as x;
Exception occured:
Unable to find source-code formatter for language: plain. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
org.teiid.jdbc.TeiidSQLException: TEIID10072 Invalid integer format in String: at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135) at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71) at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703) at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64) at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542) at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135) at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40) at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79) at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:268) at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98) at com.sun.proxy.$Proxy6.read(Unknown Source) at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405) at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:551) at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:404) at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:338) at org.jboss.qe.functions.xml.XmlTableTest.testXmlTableEmptyElementIntegerNoPath(XmlTableTest.java:192) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80) at org.testng.internal.Invoker.invokeMethod(Invoker.java:714) at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901) at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231) at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127) at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111) at org.testng.TestRunner.privateRun(TestRunner.java:767) at org.testng.TestRunner.run(TestRunner.java:617) at org.testng.SuiteRunner.runTest(SuiteRunner.java:334) at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329) at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291) at org.testng.SuiteRunner.run(SuiteRunner.java:240) at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52) at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86) at org.testng.TestNG.runSuitesSequentially(TestNG.java:1198) at org.testng.TestNG.runSuitesLocally(TestNG.java:1123) at org.testng.TestNG.run(TestNG.java:1031) at org.apache.maven.surefire.testng.TestNGExecutor.run(TestNGExecutor.java:70) at org.apache.maven.surefire.testng.TestNGDirectoryTestSuite.execute(TestNGDirectoryTestSuite.java:108) at org.apache.maven.surefire.testng.TestNGProvider.invoke(TestNGProvider.java:111) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164) at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110) at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:172) at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:104) at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:70) Caused by: org.teiid.core.types.TransformationException: TEIID10072 Invalid integer format in String: at org.teiid.core.types.basic.StringToIntegerTransform.transformDirect(StringToIntegerTransform.java:43) at org.teiid.core.types.Transform.transform(Transform.java:47) at org.teiid.core.types.DataTypeManager.transformValue(DataTypeManager.java:942) at org.teiid.core.types.DataTypeManager.transformValue(DataTypeManager.java:928) at org.teiid.query.function.FunctionDescriptor.importValue(FunctionDescriptor.java:322) at org.teiid.query.processor.relational.XMLTableNode.getValue(XMLTableNode.java:376) at org.teiid.query.processor.relational.XMLTableNode.processRow(XMLTableNode.java:341) at org.teiid.query.processor.relational.XMLTableNode.nextBatchDirect(XMLTableNode.java:225) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282) at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150) at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282) at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145) at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:470) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745)
The problem applies also for invocation with path expression specified like this:
CREATE VIEW EMPTY_ELEMENT_INTEGER(x integer) AS select * from xmltable('/a' passing convert('<a><b></b></a>', xml) columns x integer path 'b') as x;
But not for an invocation, where the xpath is specified as 'b/text()':
CREATE VIEW EMPTY_ELEMENT_INTEGER(b integer) AS select * from xmltable('/a' passing convert('<a><b></b></a>', xml) columns b integer path 'b/text()') as x;
Root of this issue is in different string result of XMLTABLE invocation with 'b' vs. 'b/text()' paths.
For empty element <b></b>:
- XMLTABLE(.... columns x string PATH 'b') returns empty string ''
- XMLTABLE(.... columns x string PATH 'b/text()') returns null
I think Teiid should be able to handle casting value of an empty element to any type without any extra precaution required. The contents retreived from an xml element can't be agnostic of the fact, that Teiid parses them as String first and then tries to cast them to appropriate type.
