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

Steven Hawkins commented on TEIID-5339:
---------------------------------------

> TEIID-4129 has then effectively broken this kind of join queries for vertica, right? 

For joins that contain null values on the affected types, yes this is preventing the successful completion.  I can either look to expand the check that is being performed to be tolerant of nulls as high/last nulls won't change the results regardless.

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