For FLOAT, BOOLEAN, CHAR, and VARCHAR, NULL appears last (largest) in ascending order.

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