Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5918

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

    Details

    • 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.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  shawkins Steven Hawkins
                  Reporter:
                  dalex005 Dmitrii Pogorelov
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - 4 hours
                    4h
                    Remaining:
                    Time Spent - 2 hours Remaining Estimate - 2 hours
                    2h
                    Logged:
                    Time Spent - 2 hours Remaining Estimate - 2 hours
                    2h