Loading...

Details

Type: Bug
Resolution: Done
Priority: Critical
Fix Version/s: 14.0, 13.0.3, 13.1.1
Affects Version/s: 13.1
Component/s: JDBC Connector
Labels:
None

Sprint:
DV Sprint 61
Story Points:
0.5

Steps to Reproduce:

Hide

1. create in MSSQL test_uid table:

CREATE TABLE [dbo].[test_uid](
[test_id] [uniqueidentifier] NOT NULL DEFAULT (NEWID()),
[content] [varchar](50) NOT NULL,
CONSTRAINT [PK_test_uid] PRIMARY KEY CLUSTERED
(
[test_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

INSERT INTO [dbo].[test_uid] (content, test_id) VALUES ('a', '1279DD00-1B57-4567-ACA8-22D2693124F2') ;
INSERT INTO [dbo].[test_uid] (content, test_id) VALUES ('b', '2279DD00-1B57-4567-ACA8-22D2693124F2') ;
INSERT INTO [dbo].[test_uid] (content, test_id) VALUES ('c', 'A279DD00-1B57-4567-ACA8-22D2693124F2') ;
INSERT INTO [dbo].[test_uid] (content, test_id) VALUES ('d', 'B279DD00-1B57-4567-ACA8-22D2693124F2') ;

2. add MSSQL as a source to Teiid:

                 <datasource jndi-name="java:/mssql-test-tables" pool-name="mssql-test-tables" enabled="true" use-java-context="true">
                    <connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_tables</connection-url>
                    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
                    <driver>com.microsoft.sqlserver</driver>
                    <new-connection-sql>SET ANSI_WARNINGS OFF</new-connection-sql>
                    <pool>
                        <min-pool-size>2</min-pool-size>
                        <max-pool-size>70</max-pool-size>
                    </pool>
                    <security>
                        <user-name>****</user-name>
                        <password>****</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>select 1</check-valid-connection-sql>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>120000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                </datasource>

3. add ms model:

<model name="ms">
        <property name="importer.useFullSchemaName" value="false"/>
        <property name="importer.tableTypes" value="TABLE,VIEW"/>
        <property name="importer.importKeys" value="false"/>
        <property name="importer.schemaPattern" value="dbo"/>
        <source name="test_tables_ms" translator-name="sqlserver" connection-jndi-name="java:/mssql-test-tables"/>
    </model>

4. run the following queries:

-- 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') ;;

-- Check if concatenation of strings works correctly with uniqueidentifier column.
-- Should return 4 rows anding with lowercased 'a'
select test_id || 'a' from ms.test_uid ;;

-- 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' ;;

Show

1. create in MSSQL test_uid table: CREATE TABLE [dbo].[test_uid]( [test_id] [uniqueidentifier] NOT NULL DEFAULT (NEWID()), [content] [ varchar ](50) NOT NULL , CONSTRAINT [PK_test_uid] PRIMARY KEY CLUSTERED ( [test_id] ASC ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ]; INSERT INTO [dbo].[test_uid] (content, test_id) VALUES ( ' a ' , '1279DD00-1B57-4567-ACA8-22D2693124F2' ) ; INSERT INTO [dbo].[test_uid] (content, test_id) VALUES ( 'b' , '2279DD00-1B57-4567-ACA8-22D2693124F2' ) ; INSERT INTO [dbo].[test_uid] (content, test_id) VALUES ( ' c ' , 'A279DD00-1B57-4567-ACA8-22D2693124F2' ) ; INSERT INTO [dbo].[test_uid] (content, test_id) VALUES ( 'd' , 'B279DD00-1B57-4567-ACA8-22D2693124F2' ) ; 2. add MSSQL as a source to Teiid: <datasource jndi-name= "java:/mssql-test-tables" pool-name= "mssql-test-tables" enabled= "true" use-java-context= "true" > <connection-url> jdbc:sqlserver://localhost:1433;databaseName=test_tables </connection-url> <driver-class> com.microsoft.sqlserver.jdbc.SQLServerDriver </driver-class> <driver> com.microsoft.sqlserver </driver> <new-connection-sql> SET ANSI_WARNINGS OFF </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> **** </user-name> <password> **** </password> </security> <validation> <check-valid-connection-sql> select 1 </check-valid-connection-sql> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 3. add ms model: <model name= "ms" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <property name= "importer.schemaPattern" value= "dbo" /> <source name= "test_tables_ms" translator-name= "sqlserver" connection-jndi-name= "java:/mssql-test-tables" /> </model> 4. run the following queries: -- 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' ) ;; -- Check if concatenation of strings works correctly with uniqueidentifier column . -- Should return 4 rows anding with lowercased ' a ' select test_id || ' a ' from ms.test_uid ;; -- 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' ;;

Workaround:

Workaround Exists
Workaround Description:

Hide

Alter the column to not be searchable:

alter table tbl alter column col options (searchable unsearchable);

Show
Alter the column to not be searchable: alter table tbl alter column col options (searchable unsearchable);

Description

1. when running the following queries:

-- 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') ;;

Teiid throws out the following error:

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

2. when running the following queries:

-- 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 ;;

Teiid throws out the following error:

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

3. when running the following queries:

-- 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' ;;

Teiid returns incorrect results.

Attachments

Issue Links

is related to

TEIID-4491 Refine uniqueidentifier support for SQL Server

Closed

SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons

Details

Description

Attachments

Issue Links

Activity

People

Dates

Time Tracking