Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5339

Vertica join query fails due to unexpected ordering of intermediate results

    Details

      Description

      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:

      SELECT BQT1.SmallA.IntKey, BQT2.SmallB.DoubleNum FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.DoubleNum
      

      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.

      SELECT g_0."doublenum" AS c_0 FROM "dvqe"."public"."smallb" AS g_0 ORDER BY c_0
      

      2.

      SELECT g_0."intkey" FROM "dvqe"."public"."smalla" AS g_0
      

      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

      ----------

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jstastny Jan Stastny
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: