XMLTABLE empty element parsed as integer causes invalid format -------------------------------------------------------------- Key: TEIID-4116 URL: https://issues.jboss.org/browse/TEIID-4116 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.12.5 Reporter: Jan Stastny Assignee: Steven Hawkins 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: {code:sql} 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; {code} Exception occured: {code:plain} 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) {code} The problem applies also for invocation with path expression specified like this: {code:sql} 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; {code} But not for an invocation, where the xpath is specified as 'b/text()': {code:sql} 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; {code} 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.