[teiid-issues] [JBoss JIRA] (TEIID-5858) Orcale collation issue with Teiid 9.1.1

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)