[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=13192598#comment-13192598 ] 

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

For future reference, it would best just to provide a vdb.xml or vdb zip and the ddl for the h2 sources.  And a query plan would also be helpful.  Providing low level artifacts would be best accompanied by a java class that demonstrates the reproduction using embedded.

In this case what I can infer here is that the order bys are being pushed to the source and the sort order is based upon the case insensitive ordering and not the expected default utf-8 ordering.  The workarounds are to either disable order by pushdown for h2, or you can set the system property org.teiid.requireTeiidCollation=true and set the translator property CollationLocale to any value that doesn't match org.teiid.collationLocale.  The latter is more targeted as only string orderings will not be pushed.  There is currently not a mechanism to do this on a per table basis.



> 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)