content: /*
   * Licensed to the Apache Software Foundation (ASF) under one or more
   * contributor license agreements.  See the NOTICE file distributed with
   * this work for additional information regarding copyright ownership.
   * The ASF licenses this file to you under the Apache License, Version 2.0
   * (the "License"); you may not use this file except in compliance with
   * the License.  You may obtain a copy of the License at
   *
   * http://www.apache.org/licenses/LICENSE-2.0
   *
   * Unless required by applicable law or agreed to in writing, software
   * distributed under the License is distributed on an "AS IS" BASIS,
   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   * See the License for the specific language governing permissions and
   * limitations under the License.
   */
  package org.apache.calcite.test.enumerable;
  
  import org.apache.calcite.adapter.enumerable.EnumerableRules;
  import org.apache.calcite.adapter.java.ReflectiveSchema;
  import org.apache.calcite.config.CalciteConnectionProperty;
  import org.apache.calcite.config.Lex;
  import org.apache.calcite.plan.RelOptPlanner;
  import org.apache.calcite.runtime.Hook;
  import org.apache.calcite.test.CalciteAssert;
  import org.apache.calcite.test.schemata.hr.HrSchemaBig;
  
  import org.junit.jupiter.api.Test;
  
  import java.util.function.Consumer;
  
  /**
   * Unit test for
   * {@link org.apache.calcite.adapter.enumerable.EnumerableMergeUnion}.
   */
  class EnumerableMergeUnionTest {
  
    @Test void mergeUnionAllOrderByEmpid() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select empid, name from emps where name like 'G%' union all select empid, name from emps where name like '%l') order by empid")
          .explainContains("EnumerableMergeUnion(all=[true])\n"
              + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['G%'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%l'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "empid=1; name=Bill",
              "empid=6; name=Guy",
              "empid=10; name=Gabriel",
              "empid=10; name=Gabriel",
              "empid=12; name=Paul",
              "empid=29; name=Anibal",
              "empid=40; name=Emmanuel",
              "empid=45; name=Pascal");
    }
  
    @Test void mergeUnionOrderByEmpid() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select empid, name from emps where name like 'G%' union select empid, name from emps where name like '%l') order by empid")
          .explainContains("EnumerableMergeUnion(all=[false])\n"
              + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['G%'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%l'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "empid=1; name=Bill",
              "empid=6; name=Guy",
              "empid=10; name=Gabriel",
              "empid=12; name=Paul",
              "empid=29; name=Anibal",
              "empid=40; name=Emmanuel",
              "empid=45; name=Pascal");
    }
  
    @Test void mergeUnionAllOrderByName() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select empid, name from emps where name like 'G%' union all select empid, name from emps where name like '%l') order by name")
          .explainContains("EnumerableMergeUnion(all=[true])\n"
              + "  EnumerableSort(sort0=[$1], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['G%'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$1], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%l'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "empid=29; name=Anibal",
              "empid=1; name=Bill",
              "empid=40; name=Emmanuel",
              "empid=10; name=Gabriel",
              "empid=10; name=Gabriel",
              "empid=6; name=Guy",
              "empid=45; name=Pascal",
              "empid=12; name=Paul");
    }
  
    @Test void mergeUnionOrderByName() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select empid, name from emps where name like 'G%' union select empid, name from emps where name like '%l') order by name")
          .explainContains("EnumerableMergeUnion(all=[false])\n"
              + "  EnumerableSort(sort0=[$1], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['G%'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$1], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%l'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "empid=29; name=Anibal",
              "empid=1; name=Bill",
              "empid=40; name=Emmanuel",
              "empid=10; name=Gabriel",
              "empid=6; name=Guy",
              "empid=45; name=Pascal",
              "empid=12; name=Paul");
    }
  
    @Test void mergeUnionSingleColumnOrderByName() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select name from emps where name like 'G%' union select name from emps where name like '%l') order by name")
          .explainContains("EnumerableMergeUnion(all=[false])\n"
              + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['G%'], expr#6=[LIKE($t2, $t5)], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%l'], expr#6=[LIKE($t2, $t5)], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "name=Anibal",
              "name=Bill",
              "name=Emmanuel",
              "name=Gabriel",
              "name=Guy",
              "name=Pascal",
              "name=Paul");
    }
  
    @Test void mergeUnionOrderByNameWithLimit() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select empid, name from emps where name like 'G%' union select empid, name from emps where name like '%l') order by name limit 3")
          .explainContains("EnumerableLimit(fetch=[3])\n"
              + "  EnumerableMergeUnion(all=[false])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t2])\n"
              + "      EnumerableLimitSort(sort0=[$2], dir0=[ASC], fetch=[3])\n"
              + "        EnumerableCalc(expr#0..4=[{inputs}], expr#5=['G%'], expr#6=[LIKE($t2, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
              + "          EnumerableTableScan(table=[[s, emps]])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t2])\n"
              + "      EnumerableLimitSort(sort0=[$2], dir0=[ASC], fetch=[3])\n"
              + "        EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%l'], expr#6=[LIKE($t2, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
              + "          EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "empid=29; name=Anibal",
              "empid=1; name=Bill",
              "empid=40; name=Emmanuel");
    }
  
    @Test void mergeUnionOrderByNameWithOffset() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select empid, name from emps where name like 'G%' union select empid, name from emps where name like '%l') order by name offset 2")
          .explainContains("EnumerableLimit(offset=[2])\n"
              + "  EnumerableMergeUnion(all=[false])\n"
              + "    EnumerableSort(sort0=[$1], dir0=[ASC])\n"
              + "      EnumerableCalc(expr#0..4=[{inputs}], expr#5=['G%'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "        EnumerableTableScan(table=[[s, emps]])\n"
              + "    EnumerableSort(sort0=[$1], dir0=[ASC])\n"
              + "      EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%l'], expr#6=[LIKE($t2, $t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n"
              + "        EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "empid=40; name=Emmanuel",
              "empid=10; name=Gabriel",
              "empid=6; name=Guy",
              "empid=45; name=Pascal",
              "empid=12; name=Paul");
    }
  
    @Test void mergeUnionOrderByNameWithLimitAndOffset() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select empid, name from emps where name like 'G%' union select empid, name from emps where name like '%l') order by name limit 3 offset 2")
          .explainContains("EnumerableLimit(offset=[2], fetch=[3])\n"
              + "  EnumerableMergeUnion(all=[false])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t2])\n"
              + "      EnumerableLimitSort(sort0=[$2], dir0=[ASC], fetch=[5])\n"
              + "        EnumerableCalc(expr#0..4=[{inputs}], expr#5=['G%'], expr#6=[LIKE($t2, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
              + "          EnumerableTableScan(table=[[s, emps]])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t2])\n"
              + "      EnumerableLimitSort(sort0=[$2], dir0=[ASC], fetch=[5])\n"
              + "        EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%l'], expr#6=[LIKE($t2, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
              + "          EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "empid=40; name=Emmanuel",
              "empid=10; name=Gabriel",
              "empid=6; name=Guy");
    }
  
    @Test void mergeUnionAllOrderByCommissionAscNullsFirstAndNameDesc() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select commission, name from emps where name like 'R%' union all select commission, name from emps where name like '%y%') order by commission asc nulls first, name desc")
          .explainContains("EnumerableMergeUnion(all=[true])\n"
              + "  EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC-nulls-first], dir1=[DESC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['R%'], expr#6=[LIKE($t2, $t5)], commission=[$t4], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC-nulls-first], dir1=[DESC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%y%'], expr#6=[LIKE($t2, $t5)], commission=[$t4], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "commission=null; name=Taylor",
              "commission=null; name=Riyad",
              "commission=null; name=Riyad",
              "commission=null; name=Ralf",
              "commission=250; name=Seohyun",
              "commission=250; name=Hyuna",
              "commission=250; name=Andy",
              "commission=500; name=Kylie",
              "commission=500; name=Guy");
    }
  
    @Test void mergeUnionOrderByCommissionAscNullsFirstAndNameDesc() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select commission, name from emps where name like 'R%' union select commission, name from emps where name like '%y%') order by commission asc nulls first, name desc")
          .explainContains("EnumerableMergeUnion(all=[false])\n"
              + "  EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC-nulls-first], dir1=[DESC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['R%'], expr#6=[LIKE($t2, $t5)], commission=[$t4], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC-nulls-first], dir1=[DESC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%y%'], expr#6=[LIKE($t2, $t5)], commission=[$t4], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "commission=null; name=Taylor",
              "commission=null; name=Riyad",
              "commission=null; name=Ralf",
              "commission=250; name=Seohyun",
              "commission=250; name=Hyuna",
              "commission=250; name=Andy",
              "commission=500; name=Kylie",
              "commission=500; name=Guy");
    }
  
    @Test void mergeUnionAllOrderByCommissionAscNullsLastAndNameDesc() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select commission, name from emps where name like 'R%' union all select commission, name from emps where name like '%y%') order by commission asc nulls last, name desc")
          .explainContains("EnumerableMergeUnion(all=[true])\n"
              + "  EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[DESC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['R%'], expr#6=[LIKE($t2, $t5)], commission=[$t4], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[DESC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%y%'], expr#6=[LIKE($t2, $t5)], commission=[$t4], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "commission=250; name=Seohyun",
              "commission=250; name=Hyuna",
              "commission=250; name=Andy",
              "commission=500; name=Kylie",
              "commission=500; name=Guy",
              "commission=null; name=Taylor",
              "commission=null; name=Riyad",
              "commission=null; name=Riyad",
              "commission=null; name=Ralf");
    }
  
    @Test void mergeUnionOrderByCommissionAscNullsLastAndNameDesc() {
      tester(false,
          new HrSchemaBig(),
          "select * from (select commission, name from emps where name like 'R%' union select commission, name from emps where name like '%y%') order by commission asc nulls last, name desc")
          .explainContains("EnumerableMergeUnion(all=[false])\n"
              + "  EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[DESC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['R%'], expr#6=[LIKE($t2, $t5)], commission=[$t4], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n"
              + "  EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[DESC])\n"
              + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=['%y%'], expr#6=[LIKE($t2, $t5)], commission=[$t4], name=[$t2], $condition=[$t6])\n"
              + "      EnumerableTableScan(table=[[s, emps]])\n")
          .returnsOrdered(
              "commission=250; name=Seohyun",
              "commission=250; name=Hyuna",
              "commission=250; name=Andy",
              "commission=500; name=Kylie",
              "commission=500; name=Guy",
              "commission=null; name=Taylor",
              "commission=null; name=Riyad",
              "commission=null; name=Ralf");
    }
  
    private CalciteAssert.AssertQuery tester(boolean forceDecorrelate,
        Object schema, String sqlQuery) {
      return CalciteAssert.that()
          .with(CalciteConnectionProperty.LEX, Lex.JAVA)
          .with(CalciteConnectionProperty.FORCE_DECORRELATE, forceDecorrelate)
          .withSchema("s", new ReflectiveSchema(schema))
          .query(sqlQuery)
          .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
            // Force UNION to be implemented via EnumerableMergeUnion
            planner.removeRule(EnumerableRules.ENUMERABLE_UNION_RULE);
            // Allow EnumerableLimitSort optimization
            planner.addRule(EnumerableRules.ENUMERABLE_LIMIT_SORT_RULE);
          });
    }
  }
  