[teiid-issues] [JBoss JIRA] (TEIID-5681) Error when trying to sort an expanded data set

[Steven Hawkins (Jira)]

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

Steven Hawkins commented on TEIID-5681:
---------------------------------------

Locally the plan I see produced only has the fkIdProductCode ordering pushed to the database:

SELECT g_0.fkIdProductCode AS c_0, g_0.idDiaryEntry AS c_1, g_0.MealNumber AS c_2 FROM my_nutri_diary.Diary AS g_0 WHERE (g_0.fkProfile IN (SELECT g_1.idProfile FROM my_nutri_diary.Account AS g_1 WHERE g_1.uuidUser = 'anonymous at teiid-security')) AND (g_0.AddedDateTime >= ?) AND (g_0.AddedDateTime <= ?) ORDER BY c_0

In the metadata it's defined as:

fkIdProductCode long NOT NULL OPTIONS(NAMEINSOURCE '`fkIdProductCode`', NATIVE_TYPE 'BIGINT UNSIGNED')

A Teiid long/bigint is signed, but the source has unsigned longs.  Is it possible that there are values greater tha 2^63-1?  If so then that would be an issue as the driver would likely supply them as negative values and thus violate the expected ordering.  If that's the case, then use a Teiid datatype of biginteger instead.

 

> Error when trying to sort an expanded data set
> ----------------------------------------------
>
>                 Key: TEIID-5681
>                 URL: https://issues.jboss.org/browse/TEIID-5681
>             Project: Teiid
>          Issue Type: Bug
>          Components: OData
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Major
>
> Hello together,
> when using orderby together with an odata expand  operation I get and error. The statement I am trying looks as follows:
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=idDiaryEntry,MealNumber&$expand=fkDiaryToFDBProducts($select=brands,idCode,product_name)&$filter=AddedDateTime%20ge%202019-03-06T00:00:00%2B01:00%20and%20AddedDateTime%20le%202019-03-07T00:00:00%2B01:00&$orderby=MealNumber%20desc&$skip=0&$top=100
> The error is:
> <error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
> <code>null</code>
> <message>
> 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.
> </message>
> </error>
> Please note, when using orderby without expand, the orderby is working as expected. The attribute which shall be used for ordering has the following type:
> <Property Name="MealNumber" Type="Edm.String" Nullable="false" MaxLength="1">
> In the underlaying database it is defined as an ENUM('1','2','3','4','5')



--
This message was sent by Atlassian Jira
(v7.12.1#712002)