[teiid-issues] [JBoss JIRA] (TEIID-4129) Wrong results with cross model join on 2 varchar fields

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-4129:
---------------------------------------

Added another system property org.teiid.assumeMatchingCollation with the release note:

In order to prevent seemingly invalid results from a sort/merge join, the sort operation will undergo additional checks.  If org.teiid.assumeMatchingCollation is false (the default) and a translator does not specify a collationLocale, then the sort for a sort/merge join will not be pushed.  Teiid defaults to the Java UCS-2 collation, which may not match the default collation for sources, particular tables, or columns.  You may set the system property org.teiid.assumeMatchingCollation true to restore the old default behavior or selectively update the translators to report a collationLocale matching org.teiid.collationLocale (UCS-2 if unset).

So we'll effectively default to not pushing down sorts for join processing that involve character data.

> Wrong results with cross model join on 2 varchar fields
> -------------------------------------------------------
>
>                 Key: TEIID-4129
>                 URL: https://issues.jboss.org/browse/TEIID-4129
>             Project: Teiid
>          Issue Type: Bug
>            Reporter: Mark Tawk
>            Assignee: Steven Hawkins
>         Attachments: H2 DBs.zip, MetaDataStores.zip
>
>
> I'm using Teiid 8.11.3 with H2 translator.
> I'm joining 2 h2 tables from 2 different teiid models linked on varchar fields.
> the query result is retuning empty values from the joined table even though the joined values are identical.
> You find attached a zip file containing 2 h2 DBs for a simplified example
> Here is the query used:
> _select "Customer"."City" as "Customer_City","Customer"."CustomerID" as "Customer_CustomerID","City"."City" as "City_City","City"."CityID" as "City_CityID" 
> from "db2"."Customer" "Customer" 
> LEFT JOIN "db1"."City" "City" ON "Customer"."City" = "City"."City"_
> Note that if the 2 tables are in the same model, the query return correct results.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)