[teiid-issues] [JBoss JIRA] (TEIID-5918) SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons
Steven Hawkins (Jira)
issues at jboss.org
Tue Mar 17 17:42:50 EDT 2020
[ https://issues.redhat.com/browse/TEIID-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14001422#comment-14001422 ]
Steven Hawkins commented on TEIID-5918:
---------------------------------------
There was a change a while back to make the conversion to string logic conditional. This was prompted by a customer case. The salesforce link is no longer valid, but my guess is that the cast was causing an index to not be used. In that case they were likely insensitive to the column behaving more as it would on sql server.
> 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: JDBC Connector
> Affects Versions: 13.1
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 14.0
>
> Original Estimate: 4 hours
> Remaining Estimate: 4 hours
>
> 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.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
More information about the teiid-issues
mailing list