[CALCITE-5003] MergeUnion on types with different collators produces wrong result

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.27.0
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Ruben Q L
• Created: 08/Feb/22 12:06
• Updated: 03/Aug/22 16:21
• Resolved: 05/May/22 08:30

Description

MergeUnion on types with different collators produces wrong result.

Problem can be reproduced with the following test (in EnumerableStringComparisonTest):

  @Test void testMergeUnionOnStringDifferentCollation() {
    tester()
        .query("?")
        .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
            planner.removeRule(EnumerableRules.ENUMERABLE_UNION_RULE))
        .withRel(b -> {
          final RelBuilder builder = b.transform(c -> c.withSimplifyValues(false));
          return builder
              .values(builder.getTypeFactory().builder()
                      .add("name",
                          builder.getTypeFactory().createSqlType(SqlTypeName.VARCHAR)).build(),
                  "facilities", "HR", "administration", "Marketing")
              .values(createRecordVarcharSpecialCollation(builder),
                  "Marketing", "administration", "presales", "HR")
              .union(false)
              .sort(0)
              .build();
        })
        .explainHookMatches("" // It is important that we have MergeUnion in the plan
            + "EnumerableMergeUnion(all=[false])\n"
            + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
            + "    EnumerableValues(tuples=[[{ 'facilities' }, { 'HR' }, { 'administration' }, { 'Marketing' }]])\n"
            + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
            + "    EnumerableValues(tuples=[[{ 'Marketing' }, { 'administration' }, { 'presales' }, { 'HR' }]])\n")
        .returnsOrdered("name=administration\n"
            + "name=facilities\n"
            + "name=HR\n"
            + "name=Marketing\n"
            + "name=presales");
  }

which fails with:

java.lang.AssertionError: 
Expected: "name=administration\nname=facilities\nname=HR\nname=Marketing\nname=presales"
     but: was "name=administration\nname=HR\nname=Marketing\nname=administration\nname=facilities\nname=Marketing\nname=presales"

The problem is that, in case of different collators, the pre-requisite of the the MergeUnion (inputs sorted) is not fulfilled, since inputs are technically sorted, but not using the same sorting collator, so they are not comparable by the MergeUnion algorithm.

A possible solution could be not applying EnumerableMergeUnionRule in this case.
A more clever solution could be achieved if the rule pushes a Sort + Cast + input (and not just Sort + input) in case the input's key type differs collation-wise with the union's result type.

Issue Links

• relates to: CALCITE-4195 Cast between types with different collators must be evaluated as not monotonic
• relates to: CALCITE-3221 Add MergeUnion operator in Enumerable convention
• links to: GitHub Pull Request #2786