[teiid-issues] [JBoss JIRA] (TEIID-5339) Vertica join query fails due to unexpected ordering of intermediate results

[Steven Hawkins (JIRA)]

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

Steven Hawkins edited comment on TEIID-5339 at 5/11/18 2:51 PM:
----------------------------------------------------------------

In this case the exception makes no difference in behavior as it's related to nulls appearing as high.  So the issue here is that the vertica translator is reported the null order as low (the jdbc execution factory default), when it's actually high.  It appears that vertica is more complicated that other sources as the ordering is type dependent:  https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AnalyzingData/Optimizations/NULLPlacementByAnalyticFunctions.htm
https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SELECT/ORDERBYClause.htm


was (Author: shawkins):
In this case the exception makes no difference in behavior as it's related to nulls appearing as high.  So the issue here is that the vertica translator is reported the null order as low (the jdbc execution factory default), when it's actually high.  It appears that vertica is more complicated that other sources as the ordering is type dependent:  https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AnalyzingData/Optimizations/NULLPlacementByAnalyticFunctions.htm

> Vertica join query fails due to unexpected ordering of intermediate results
> ---------------------------------------------------------------------------
>
>                 Key: TEIID-5339
>                 URL: https://issues.jboss.org/browse/TEIID-5339
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.12.13.6_4
>            Reporter: Jan Stastny
>            Assignee: Steven Hawkins
>
> Description of problem:
> There is unexpected error caused by TEIID-4129 fix.
> Error:
> 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.
> Query:
> {code:sql}
> SELECT BQT1.SmallA.IntKey, BQT2.SmallB.DoubleNum FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.DoubleNum
> {code}
> The property 'org.teiid.assumeMatchingCollation' is not set, thus default value is false. Teiid should resolve the issue with different ordering.
> Pushed source commands:
> 1.
> {code:sql}
> SELECT g_0."doublenum" AS c_0 FROM "dvqe"."public"."smallb" AS g_0 ORDER BY c_0
> {code}
> 2.
> {code:sql}
> SELECT g_0."intkey" FROM "dvqe"."public"."smalla" AS g_0
> {code}
> When I run the query 1 against actual vertica instance I get result as:
> ----------
> | c_0    |
> | ------ |
> | -24.0  |
> | -23.0  |
> | -22.0  |
> | -21.0  |
> | -19.0  |
> | -18.0  |
> | -17.0  |
> | -16.0  |
> | -15.0  |
> | -14.0  |
> | -13.0  |
> | -12.0  |
> | -11.0  |
> | -10.0  |
> | -9.0   |
> | -8.0   |
> | -7.0   |
> | -6.0   |
> | -5.0   |
> | -3.0   |
> | -2.0   |
> | -1.0   |
> | 0.0    |
> | 1.0    |
> | 2.0    |
> | 3.0    |
> | 4.0    |
> | 5.0    |
> | 6.0    |
> | 7.0    |
> | 8.0    |
> | 9.0    |
> | 10.0   |
> | 11.0   |
> | 13.0   |
> | 14.0   |
> | 15.0   |
> | 16.0   |
> | 17.0   |
> | 18.0   |
> | 19.0   |
> | 20.0   |
> | 21.0   |
> | 22.0   |
> | 23.0   |
> | 24.0   |
> | 25.0   |
> | <null> |
> | <null> |
> | <null> |
> ----------
> Driver states the type is Float.
> Query 2:
> ----------
> | intkey |
> | ------ |
> | 0      |
> | 1      |
> | 2      |
> | 3      |
> | 4      |
> | 5      |
> | 6      |
> | 7      |
> | 8      |
> | 9      |
> | 10     |
> | 11     |
> | 12     |
> | 13     |
> | 14     |
> | 15     |
> | 16     |
> | 17     |
> | 18     |
> | 19     |
> | 20     |
> | 21     |
> | 22     |
> | 23     |
> | 24     |
> | 25     |
> | 26     |
> | 27     |
> | 28     |
> | 29     |
> | 30     |
> | 31     |
> | 32     |
> | 33     |
> | 34     |
> | 35     |
> | 36     |
> | 37     |
> | 38     |
> | 39     |
> | 40     |
> | 41     |
> | 42     |
> | 43     |
> | 44     |
> | 45     |
> | 46     |
> | 47     |
> | 48     |
> | 49     |
> ----------



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)