[teiid-issues] [JBoss JIRA] (TEIID-5918) SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons

SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons ------------------------------------------------------------------------------------------------------ Key: TEIID-5918 URL: https://issues.redhat.com/browse/TEIID-5918 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 13.1 Reporter: Dmitrii Pogorelov Assignee: Steven Hawkins Priority: Blocker 1. when running the following queries: {code:sql} -- Check that the following queries don't fail and return correct results: -- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 test_tables_ms: S0002 Unexpected exception while translating results: Conversion failed when converting from a character string to uniqueidentifier. select * from ms.test_uid where test_id != content ;; -- Should return all 16 rows -- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 test_tables_ms: S0002 Unexpected exception while translating results: Conversion failed when converting from a character string to uniqueidentifier. select * from ms.test_uid t1 join ms.test_uid t2 on t1.test_id != t2.content ;; -- Should return 1 row -- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 test_tables_ms: S0002 Unexpected exception while translating results: Conversion failed when converting from a character string to uniqueidentifier. select * from ms.test_uid where test_id in ('B279DD00-1B57-4567-ACA8-22D2693124F2', 'a', 'b') ;; {code} Teiid throws out the following error: {code} 2020-03-16 14:57:08,314 WARN [org.teiid.CONNECTOR] (Worker6_QueryProcessorQueue68) B9oE6Jwiki7f Connector worker process failed for atomic-request=B9oE6Jwiki7f.23.0.12: org.teiid.translator.TranslatorException: S0002 Unexpected exception while translating results: Conversion failed when converting from a character string to uniqueidentifier. at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:348) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:475) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:261) 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:498) at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228) at com.sun.proxy.$Proxy43.more(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:305) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) 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) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting from a character string to uniqueidentifier. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4853) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1034) at org.jboss.jca.adapters.jdbc.WrappedResultSet.next(WrappedResultSet.java:2689) at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:334) ... 19 more {code} 2. when running the following queries: {code:sql} -- Check if concatenation of strings works correctly with uniqueidentifier column. -- Should return 4 rows anding with lowercased 'a' -- Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 test_tables_ms: 402 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT (g_0."test_id" + 'a') AS c_0 FROM "test_tables"."dbo"."test_uid" g_0 ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY] select test_id || 'a' from ms.test_uid ;; {code} Teiid throws out the following error: {code} 2020-03-16 14:59:57,516 WARN [org.teiid.CONNECTOR] (Worker8_QueryProcessorQueue80) B9oE6Jwiki7f Connector worker process failed for atomic-request=B9oE6Jwiki7f.27.0.16: org.teiid.translator.jdbc.JDBCExecutionException: 402 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT (g_0."test_id" + 'a') AS c_0 FROM "test_tables"."dbo"."test_uid" g_0 ORDER BY @@version OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY] at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:402) at sun.reflect.GeneratedMethodAccessor106.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:228) at com.sun.proxy.$Proxy43.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:59) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:124) at org.teiid.dqp.internal.process.ThreadReuseExecutor$2.run(ThreadReuseExecutor.java:212) 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) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The data types uniqueidentifier and varchar are incompatible in the add operator. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504) at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) ... 17 more {code} 3. when running the following queries: {code:sql} -- Should return 0 rows -- FAILURE(result 1 result, lower case comparison) select * from ms.test_uid t1 join ms.test_uid t2 on t1.test_id = t2.test_id where t2.test_id = 'b279dd00-1b57-4567-aca8-22d2693124f2' ;; -- Should return 0 rows -- FAILURE(result 1 result, lower case comparison) select * from ms.test_uid where test_id = 'b279dd00-1b57-4567-aca8-22d2693124f2' ;; -- Should return 0 rows -- CORRECT select * from ms.test_uid where cast(cast(test_id as clob) as string) = 'b279dd00-1b57-4567-aca8-22d2693124f2' ;; {code} Teiid returns incorrect results.