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

[Jan Stastny (JIRA)]

Jan Stastny created TEIID-5339:
----------------------------------

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