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

[Steven Hawkins (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5858?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13819963#comment-13819963 ] 

Steven Hawkins commented on TEIID-5858:
---------------------------------------

Yes, there can be issues with sorting expectations and columns that have specific collations.  

If assumeMatchingCollation is set to false that is typically enough to inhibit pushdown of sorts for merge sort join processing.  If it is false and the collationLocale matches what is reported by the translator collectionLocale, then the sort will still be pushed down.

Can you provide the final plan that shows what sort is being pushed?

> Orcale collation issue with Teiid 9.1.1
> ---------------------------------------
>
>                 Key: TEIID-5858
>                 URL: https://issues.jboss.org/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)