[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:all-tabpanel ]

Steven Hawkins resolved TEIID-5339.
-----------------------------------
    Fix Version/s: 10.3
                   10.1.4
                   10.2.2
       Resolution: Done


corrected the vertica reported default null sorting behavior to unknown - there doesn't seem to be null sorting on the general order by clause though.  The engine was corrected so that the ordering check ignores situations involving nulls.  IS NOT NULL predicates were not yet added in RuleImplementJoinStrategy, but is another option.

> 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
>             Fix For: 10.3, 10.1.4, 10.2.2
>
>
> 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)