[JBoss JIRA] (TEIIDSB-51) Define expectations for runtime metadata
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-51?page=com.atlassian.jira.plugi... ]
Ramesh Reddy updated TEIIDSB-51:
--------------------------------
Sprint: DV Sprint 61
> Define expectations for runtime metadata
> ----------------------------------------
>
> Key: TEIIDSB-51
> URL: https://issues.redhat.com/browse/TEIIDSB-51
> Project: Teiid Spring Boot
> Issue Type: Feature Request
> Components: documentation
> Reporter: Steven Hawkins
> Priority: Major
> Fix For: 1.4.0
>
>
> At a high level there are potentially three modes for our ddl:
> 1. Interactive - there was some work toward this, but it needed to only be a "developer mode" as it would reload the vdb on each metadata change. There is a lot about the transactionality of DDL updates that our current simplistic metadata model does not support, which makes this hard to fully implement. We haven't talked about this much in relation to Teiid Spring Boot, but ideally there should be a way for developers to test incremental changes without full rebuilds.
> 2. Dynamic - the term is a bit dated, but this refers to any vdb that performs import from a metadata repository that has runtime dependencies. By design this allows for a simple vdb definition and options for caching or re-importing on reload. In a container environment metadata caching no longer makes sense unless we use a volume. Depending upon your viewpoint allowing for runtime import of metadata is potentially a point of mutability that is also not needed in a container environment.
> 3. Static - much like our old .index designer vdbs there are benefits to having the full metadata already available to the vdb/image. There is a lot of work that we do lazily at runtime that can be moved to an earlier phase - primarily generating the rest and odata layers, but it would also include the pg system table materializations. For Teiid Spring Boot or usage in containers in general what this would look like is a build phase for the vdb such that it could obtain appropriate metadata and initialize as much as possible statically (eventually for quarkus vm snapshotting). The only metadata that would not be fresh potentially would be the costing metadata - however that is problematic as is and will eventually need a progressive optimization strategy employing a persistent store.
> As of now we are currently focused just on #2. As we look toward operators, we should start thinking about #3.
> #1 would come into play if/when we start looking at local development options using vs code.
> We can treat this issue as an epic and a place to centralize discussion.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 9 months
[JBoss JIRA] (TEIID-5858) Orcale collation issue with Teiid 9.1.1
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5858?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-5858.
-----------------------------------
Resolution: Out of Date
> Orcale collation issue with Teiid 9.1.1
> ---------------------------------------
>
> Key: TEIID-5858
> URL: https://issues.redhat.com/browse/TEIID-5858
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 9.1.1
> Reporter: Ivan Chan
> Assignee: Steven Hawkins
> Priority: Major
>
> One of customers run into this following error:
> org.teiid.core.TeiidComponentException: TEIID31202 Detected that an already sorted set of values was not in the expected order (typically UTF-16 / UCS-2). Please check the translator settings to ensure character columns used for joining are sorted as expected.
> And they were trying to join multiple oracle databases together. And they are running Teiid 9.1.1
> It seems like I need to change the collation. And I tried to change those values, but it didn't seem to do anything.
> org.teiid.requireTeiidCollation
> org.teiid.collationLocale
> org.teiid.assumeMatchingCollation
> Here is how we create the tables in Oracle:
> CREATE TABLE "DWH_MSFL"."MW_CATEGORY" CREATE TABLE "DWH_MSFL"."MW_CATEGORY" ( "CAT_ID" NUMBER(*,0), "CAT_TITLE" VARCHAR2(255 CHAR), "CAT_TITLE_ORIGIN" VARCHAR2(255 CHAR), "CAT_PAGES" NUMBER(*,0), "CAT_SUBCATS" NUMBER(*,0), "CAT_FILES" NUMBER(*,0) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DWH_MSFL" ;
>
>
> --------------------------
>
> CREATE TABLE "DWH_MSFL"."MW_CATEGORYLINKS" CREATE TABLE "DWH_MSFL"."MW_CATEGORYLINKS" ( "CL_FROM" NUMBER(*,0), "CL_TO" VARCHAR2(255 CHAR), "CL_SORTKEY" VARCHAR2(230 CHAR), "CL_TIMESTAMP" TIMESTAMP (0), "CL_SORTKEY_PREFIX" VARCHAR2(255 CHAR), "CL_COLLATION" VARCHAR2(32 CHAR), "CL_TYPE" VARCHAR2(10 CHAR) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DWH_MSFL" ;
> Notwithstanding, can't the issue somehow be related to categorylinks>category relation or Column-Level Collation (CI_COLLATION)which is set to Uppercase(see insert to categorylinks table in DWH_MSFL.sql). We have an issue exactly with a category and title column(CAT_TITLE)
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 9 months
[JBoss JIRA] (TEIIDSB-23) Document image generation options
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-23?page=com.atlassian.jira.plugi... ]
Ramesh Reddy updated TEIIDSB-23:
--------------------------------
Fix Version/s: (was: 1.4.0)
> Document image generation options
> ---------------------------------
>
> Key: TEIIDSB-23
> URL: https://issues.redhat.com/browse/TEIIDSB-23
> Project: Teiid Spring Boot
> Issue Type: Task
> Reporter: Steven Hawkins
> Priority: Major
>
> We need to document / validate all relevant image options:
> - inclusion of agent bond or other mechanism for jmx exposure to prometheus. There may also be related service annotations
> - annotations for 3scale for rest and openapi exposure of odata
> - any common config options - disk buffer memory, max active plans / engine threads / connection pool sizes. Ideally the buffer manager heap should auto-configure and we should probably always use off-heap for the fixed memory buffer.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 9 months
[JBoss JIRA] (TEIID-5622) Capture missing features/issues from Teiid JDBC with the pg protocol
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5622?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-5622.
-----------------------------------
Fix Version/s: (was: 13.x)
Resolution: Done
Marking this issue as done. This was mostly to be a task / parent to high priority missing functionality. Those issues have been spawned. We can reference this later should more need to be created.
> Capture missing features/issues from Teiid JDBC with the pg protocol
> --------------------------------------------------------------------
>
> Key: TEIID-5622
> URL: https://issues.redhat.com/browse/TEIID-5622
> Project: Teiid
> Issue Type: Quality Risk
> Components: JDBC Driver
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
>
> We need to start spawning issues for missing features and known compatibility issues between Teiid JDBC and using the pg protocol - primarily with pg JDBC.
> This includes:
> * lob support - lo is not yet implemented in pg, just inlining TEIID-1163
> * xa support - TEIID-4012
> * query plan handling - TEIID-5527
> * determine if reauthentication is supported
> * domain type reporting - TEIID-4909
> * typing differences in general - should there be a mode where we can report teiid type names? that's likely not possible as many pg clients have builtin logic around the standard types
> * multi-dimensional arrays
> * like escape and other pg behavior that have workarounds in Teiid
> * generated keys - we only return a 0 oid for inserts - what about upserts?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 9 months
[JBoss JIRA] (TEIID-5918) SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons
by Dmitrii Pogorelov (Jira)
[ https://issues.redhat.com/browse/TEIID-5918?page=com.atlassian.jira.plugi... ]
Dmitrii Pogorelov updated TEIID-5918:
-------------------------------------
Priority: Blocker (was: Critical)
> 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.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 9 months
[JBoss JIRA] (TEIID-5918) SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons
by Dmitrii Pogorelov (Jira)
[ https://issues.redhat.com/browse/TEIID-5918?page=com.atlassian.jira.plugi... ]
Dmitrii Pogorelov updated TEIID-5918:
-------------------------------------
Description:
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.
was:
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:noformat}
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:noformat}
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.
> 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: Critical
>
> 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)
4 years, 9 months
[JBoss JIRA] (TEIID-5918) SQL Server uniqueidentifier field type: Conversion and concat errors, incorrect lower case comparisons
by Dmitrii Pogorelov (Jira)
Dmitrii Pogorelov created TEIID-5918:
----------------------------------------
Summary: 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
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:noformat}
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:noformat}
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)
4 years, 9 months