exec/java-exec/src/test/java/org/apache/drill/TestUnionAll.java

/*
 * 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.drill;

import org.apache.drill.exec.record.BatchSchemaBuilder;
import com.google.common.collect.Lists;

import org.apache.commons.lang3.tuple.Pair;
import org.apache.drill.categories.OperatorTest;
import org.apache.drill.categories.SqlTest;
import org.apache.drill.categories.UnlikelyTest;
import org.apache.drill.common.exceptions.UserException;
import org.apache.drill.common.expression.SchemaPath;
import org.apache.drill.common.types.TypeProtos;
import org.apache.drill.exec.ExecConstants;
import org.apache.drill.exec.planner.physical.PlannerSettings;
import org.apache.drill.exec.record.BatchSchema;
import org.apache.drill.exec.record.metadata.SchemaBuilder;
import org.apache.drill.exec.util.StoragePluginTestUtils;
import org.apache.drill.exec.work.foreman.SqlUnsupportedException;
import org.apache.drill.exec.work.foreman.UnsupportedRelOperatorException;
import org.apache.drill.test.ClusterFixture;
import org.apache.drill.test.ClusterTest;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.experimental.categories.Category;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.nio.file.Paths;
import java.util.List;

@Category({SqlTest.class, OperatorTest.class})
public class TestUnionAll extends ClusterTest {

  private static final String SLICE_TARGET_DEFAULT = "alter session reset `planner.slice_target`";
  private static final String EMPTY_DIR_NAME = "empty_directory";

  @BeforeClass
  public static void setupTestFiles() throws Exception {
    startCluster(ClusterFixture.builder(dirTestWatcher));
    dirTestWatcher.copyResourceToRoot(Paths.get("multilevel", "parquet"));
    dirTestWatcher.makeTestTmpSubDir(Paths.get(EMPTY_DIR_NAME));

    // A tmp workspace with a default format defined for tests that need to
    // query empty directories without encountering an error.
    cluster.defineWorkspace(
        StoragePluginTestUtils.DFS_PLUGIN_NAME,
        "tmp_default_format",
        dirTestWatcher.getDfsTestTmpDir().getAbsolutePath(),
        "csvh"
    );
  }

  @Test  // Simple Union-All over two scans
  public void testUnionAll1() throws Exception {
    String query = "(select n_regionkey from cp.`tpch/nation.parquet`) union all (select r_regionkey from cp.`tpch/region.parquet`)";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q1.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("n_regionkey")
        .build().run();
  }

  @Test  // Union-All over inner joins
  public void testUnionAll2() throws Exception {
    String query =
         "select n1.n_nationkey from cp.`tpch/nation.parquet` n1 inner join cp.`tpch/region.parquet` r1 on n1.n_regionkey = r1.r_regionkey where n1.n_nationkey in (1, 2) " +
         "union all " +
         "select n2.n_nationkey from cp.`tpch/nation.parquet` n2 inner join cp.`tpch/region.parquet` r2 on n2.n_regionkey = r2.r_regionkey where n2.n_nationkey in (3, 4)";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q2.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("n_nationkey")
        .build().run();
  }

  @Test  // Union-All over grouped aggregates
  public void testUnionAll3() throws Exception {
    String query = "select n1.n_nationkey from cp.`tpch/nation.parquet` n1 where n1.n_nationkey in (1, 2) group by n1.n_nationkey union all select r1.r_regionkey from cp.`tpch/region.parquet` r1 group by r1.r_regionkey";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q3.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("n_nationkey")
        .build().run();
  }

  @Test    // Chain of Union-Alls
  public void testUnionAll4() throws Exception {
    String query = "select n_regionkey from cp.`tpch/nation.parquet` union all select r_regionkey from cp.`tpch/region.parquet` union all select n_nationkey from cp.`tpch/nation.parquet` union all select c_custkey from cp.`tpch/customer.parquet` where c_custkey < 5";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q4.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("n_regionkey")
        .build().run();
  }

  @Test  // Union-All of all columns in the table
  public void testUnionAll5() throws Exception {
    String query = "select r_name, r_comment, r_regionkey from cp.`tpch/region.parquet` r1 " +
                     "union all " +
                     "select r_name, r_comment, r_regionkey from cp.`tpch/region.parquet` r2";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q5.tsv")
        .baselineTypes(TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.INT)
        .baselineColumns("r_name", "r_comment", "r_regionkey")
        .build().run();
  }

  @Test // Union-All where same column is projected twice in right child
  public void testUnionAll6() throws Exception {
    String query = "select n_nationkey, n_regionkey from cp.`tpch/nation.parquet` where n_regionkey = 1 union all select r_regionkey, r_regionkey from cp.`tpch/region.parquet` where r_regionkey = 2";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q6.tsv")
        .baselineTypes(TypeProtos.MinorType.INT, TypeProtos.MinorType.INT)
        .baselineColumns("n_nationkey", "n_regionkey")
        .build().run();
  }

  @Test // Union-All where same column is projected twice in left and right child
  public void testUnionAll6_1() throws Exception {
    String query = "select n_nationkey, n_nationkey from cp.`tpch/nation.parquet` union all select r_regionkey, r_regionkey from cp.`tpch/region.parquet`";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q6_1.tsv")
        .baselineTypes(TypeProtos.MinorType.INT, TypeProtos.MinorType.INT)
        .baselineColumns("n_nationkey", "n_nationkey0")
        .build().run();
  }

  @Test  // Union-all of two string literals of different lengths
  public void testUnionAll7() throws Exception {
    String query = "select 'abc' from cp.`tpch/region.parquet` union all select 'abcdefgh' from cp.`tpch/region.parquet`";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q7.tsv")
        .baselineTypes(TypeProtos.MinorType.VARCHAR)
        .baselineColumns("EXPR$0")
        .build().run();
  }

  @Test  // Union-all of two character columns of different lengths
  public void testUnionAll8() throws Exception {
    String query = "select n_name, n_nationkey from cp.`tpch/nation.parquet` union all select r_comment, r_regionkey  from cp.`tpch/region.parquet`";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q8.tsv")
        .baselineTypes(TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.INT)
        .baselineColumns("n_name", "n_nationkey")
        .build().run();
  }

  @Test // DRILL-1905: Union-all of * column from JSON files in different directories
  @Category(UnlikelyTest.class)
  public void testUnionAll9() throws Exception {
    String file0 = "/multilevel/json/1994/Q1/orders_94_q1.json";
    String file1 = "/multilevel/json/1995/Q1/orders_95_q1.json";

    testBuilder()
        .sqlQuery("select o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, o_orderkey from cp.`%s` union " +
          "all select o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, o_orderkey from cp.`%s`", file0, file1)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q9.tsv")
        .baselineTypes(TypeProtos.MinorType.BIGINT, TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.FLOAT8, TypeProtos.MinorType.VARCHAR,
                       TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.BIGINT,TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.BIGINT)
        .baselineColumns("o_custkey", "o_orderstatus", "o_totalprice", "o_orderdate",
                         "o_orderpriority", "o_clerk", "o_shippriority", "o_comment", "o_orderkey")
        .build().run();
  }

  @Test // Union All constant literals
  public void testUnionAll10() throws Exception {
    String query = "(select n_name, 'LEFT' as LiteralConstant, n_nationkey, '1' as NumberConstant from cp.`tpch/nation.parquet`) " +
              "union all " +
              "(select 'RIGHT', r_name, '2', r_regionkey from cp.`tpch/region.parquet`)";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q10.tsv")
        .baselineTypes(TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.INT, TypeProtos.MinorType.INT)
        .baselineColumns("n_name", "LiteralConstant", "n_nationkey", "NumberConstant")
        .build().run();
  }

  @Test
  @Category(UnlikelyTest.class)
  public void testUnionAllViewExpandableStar() throws Exception {
    try {
      run("use dfs.tmp");
      run("create view nation_view_testunionall_expandable_star as select n_name, n_nationkey from cp.`tpch/nation.parquet`");
      run("create view region_view_testunionall_expandable_star as select r_name, r_regionkey from cp.`tpch/region.parquet`");

      String query1 = "(select * from dfs.tmp.`nation_view_testunionall_expandable_star`) " +
          "union all " +
          "(select * from dfs.tmp.`region_view_testunionall_expandable_star`) ";

      String query2 =  "(select r_name, r_regionkey from cp.`tpch/region.parquet`) " +
          "union all " +
          "(select * from dfs.tmp.`nation_view_testunionall_expandable_star`)";

      testBuilder()
          .sqlQuery(query1)
          .unOrdered()
          .csvBaselineFile("testframework/testUnionAllQueries/q11.tsv")
          .baselineTypes(TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.INT)
          .baselineColumns("n_name", "n_nationkey")
          .build().run();

      testBuilder()
          .sqlQuery(query2)
          .unOrdered()
          .csvBaselineFile("testframework/testUnionAllQueries/q12.tsv")
          .baselineTypes(TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.INT)
          .baselineColumns("r_name", "r_regionkey")
          .build().run();
    } finally {
      run("drop view if exists nation_view_testunionall_expandable_star");
      run("drop view if exists region_view_testunionall_expandable_star");
    }
  }

  @Test(expected = UnsupportedRelOperatorException.class) // see DRILL-2002
  public void testUnionAllViewUnExpandableStar() throws Exception {
    try {
      run("use dfs.tmp");
      run("create view nation_view_testunionall_expandable_star as select * from cp.`tpch/nation.parquet`");

      String query = "(select * from dfs.tmp.`nation_view_testunionall_expandable_star`) " +
                     "union all (select * from cp.`tpch/region.parquet`)";
      run(query);
    } catch(UserException ex) {
      SqlUnsupportedException.errorClassNameToException(ex.getOrCreatePBError(false).getException().getExceptionClass());
      throw ex;
    } finally {
      run("drop view if exists nation_view_testunionall_expandable_star");
    }
  }

  @Test
  public void testDiffDataTypesAndModes() throws Exception {
    try {
      run("use dfs.tmp");
      run("create view nation_view_testunionall_expandable_star as select n_name, n_nationkey from cp.`tpch/nation.parquet`");
      run("create view region_view_testunionall_expandable_star as select r_name, r_regionkey from cp.`tpch/region.parquet`");

      String t1 = "(select n_comment, n_regionkey from cp.`tpch/nation.parquet` limit 5)";
      String t2 = "(select * from nation_view_testunionall_expandable_star  limit 5)";
      String t3 = "(select full_name, store_id from cp.`employee.json` limit 5)";
      String t4 = "(select * from region_view_testunionall_expandable_star  limit 5)";

      String query1 = t1 + " union all " + t2 + " union all " + t3 + " union all " + t4;

      testBuilder()
          .sqlQuery(query1)
          .unOrdered()
          .csvBaselineFile("testframework/testUnionAllQueries/q13.tsv")
          .baselineTypes(TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.BIGINT)
          .baselineColumns("n_comment", "n_regionkey")
          .build().run();
    } finally {
      run("drop view if exists nation_view_testunionall_expandable_star");
      run("drop view if exists region_view_testunionall_expandable_star");
    }
  }

  @Test // see DRILL-2203
  @Category(UnlikelyTest.class)
  public void testDistinctOverUnionAllwithFullyQualifiedColumnNames() throws Exception {
    String query = "select distinct sq.x1, sq.x2 " +
        "from " +
        "((select n_regionkey as a1, n_name as b1 from cp.`tpch/nation.parquet`) " +
        "union all " +
        "(select r_regionkey as a2, r_name as b2 from cp.`tpch/region.parquet`)) as sq(x1,x2)";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q14.tsv")
        .baselineTypes(TypeProtos.MinorType.INT, TypeProtos.MinorType.VARCHAR)
        .baselineColumns("x1", "x2")
        .build().run();
  }

  @Test // see DRILL-1923
  @Category(UnlikelyTest.class)
  public void testUnionAllContainsColumnANumericConstant() throws Exception {
    String query = "(select n_nationkey, n_regionkey, n_name from cp.`tpch/nation.parquet`  limit 5) " +
        "union all " +
        "(select 1, n_regionkey, 'abc' from cp.`tpch/nation.parquet` limit 5)";

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q15.tsv")
        .baselineTypes(TypeProtos.MinorType.INT, TypeProtos.MinorType.INT, TypeProtos.MinorType.VARCHAR)
        .baselineColumns("n_nationkey", "n_regionkey", "n_name")
        .build().run();
  }

  @Test // see DRILL-2207
  @Category(UnlikelyTest.class)
  public void testUnionAllEmptySides() throws Exception {
    String query1 = "(select n_nationkey, n_regionkey, n_name from cp.`tpch/nation.parquet`  limit 0) " +
        "union all " +
        "(select 1, n_regionkey, 'abc' from cp.`tpch/nation.parquet` limit 5)";

    String query2 = "(select n_nationkey, n_regionkey, n_name from cp.`tpch/nation.parquet`  limit 5) " +
        "union all " +
        "(select 1, n_regionkey, 'abc' from cp.`tpch/nation.parquet` limit 0)";

    testBuilder()
        .sqlQuery(query1)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q16.tsv")
        .baselineTypes(TypeProtos.MinorType.INT, TypeProtos.MinorType.INT, TypeProtos.MinorType.VARCHAR)
        .baselineColumns("n_nationkey", "n_regionkey", "n_name")
        .build().run();

    testBuilder()
        .sqlQuery(query2)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q17.tsv")
        .baselineTypes(TypeProtos.MinorType.INT, TypeProtos.MinorType.INT, TypeProtos.MinorType.VARCHAR)
        .baselineColumns("n_nationkey", "n_regionkey", "n_name")
        .build().run();
  }

  @Test // see DRILL-1977, DRILL-2376, DRILL-2377, DRILL-2378, DRILL-2379
  @Category(UnlikelyTest.class)
  public void testAggregationOnUnionAllOperator() throws Exception {
    String root = "/store/text/data/t.json";

    testBuilder()
        .sqlQuery("(select calc1, max(b1) as `max`, min(b1) as `min`, count(c1) as `count` " +
          "from (select a1 + 10 as calc1, b1, c1 from cp.`%s` " +
          "union all select a1 + 100 as diff1, b1 as diff2, c1 as diff3 from cp.`%s`) " +
          "group by calc1 order by calc1)", root, root)
        .ordered()
        .csvBaselineFile("testframework/testExampleQueries/testAggregationOnUnionAllOperator/q1.tsv")
        .baselineTypes(TypeProtos.MinorType.BIGINT, TypeProtos.MinorType.BIGINT, TypeProtos.MinorType.BIGINT, TypeProtos.MinorType.BIGINT)
        .baselineColumns("calc1", "max", "min", "count")
        .build().run();

    testBuilder()
        .sqlQuery("(select calc1, min(b1) as `min`, max(b1) as `max`, count(c1) as `count` " +
          "from (select a1 + 10 as calc1, b1, c1 from cp.`%s` " +
          "union all select a1 + 100 as diff1, b1 as diff2, c1 as diff3 from cp.`%s`) " +
          "group by calc1 order by calc1)", root, root)
        .ordered()
        .csvBaselineFile("testframework/testExampleQueries/testAggregationOnUnionAllOperator/q2.tsv")
        .baselineTypes(TypeProtos.MinorType.BIGINT, TypeProtos.MinorType.BIGINT, TypeProtos.MinorType.BIGINT, TypeProtos.MinorType.BIGINT)
        .baselineColumns("calc1", "min", "max", "count")
        .build().run();
  }

  @Test(expected = UserException.class) // see DRILL-2590
  public void testUnionAllImplicitCastingFailure() throws Exception {
    String rootInt = "/store/json/intData.json";
    String rootBoolean = "/store/json/booleanData.json";

    run("(select key from cp.`%s` " +
        "union all " +
        "select key from cp.`%s` )", rootInt, rootBoolean);
  }

  @Test // see DRILL-2591
  @Category(UnlikelyTest.class)
  public void testDateAndTimestampJson() throws Exception {
    String rootDate = "/store/json/dateData.json";
    String rootTimpStmp = "/store/json/timeStmpData.json";

    testBuilder()
        .sqlQuery("(select max(key) as key from cp.`%s` " +
          "union all select key from cp.`%s`)", rootDate, rootTimpStmp)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q18_1.tsv")
        .baselineTypes(TypeProtos.MinorType.VARCHAR)
        .baselineColumns("key")
        .build().run();

    testBuilder()
        .sqlQuery("select key from cp.`%s` " +
          "union all select max(key) as key from cp.`%s`", rootDate, rootTimpStmp)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q18_2.tsv")
        .baselineTypes(TypeProtos.MinorType.VARCHAR)
        .baselineColumns("key")
        .build().run();

    testBuilder()
        .sqlQuery("select key from cp.`%s` " +
          "union all select max(key) as key from cp.`%s`", rootDate, rootTimpStmp)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q18_3.tsv")
        .baselineTypes(TypeProtos.MinorType.VARCHAR)
        .baselineColumns("key")
        .build().run();
  }

  @Test // see DRILL-2637
  @Category(UnlikelyTest.class)
  public void testUnionAllOneInputContainsAggFunction() throws Exception {
    String root = "/multilevel/csv/1994/Q1/orders_94_q1.csv";

    testBuilder()
        .sqlQuery("select * from ((select count(c1) as ct from (select columns[0] c1 from cp.`%s`)) \n" +
          "union all (select columns[0] c2 from cp.`%s`)) order by ct limit 3", root, root)
        .ordered()
        .baselineColumns("ct")
        .baselineValues((long) 10)
        .baselineValues((long) 66)
        .baselineValues((long) 99)
        .build().run();

    testBuilder()
        .sqlQuery("select * from ((select columns[0] ct from cp.`%s`)\n" +
          "union all (select count(c1) as c2 from (select columns[0] c1 from cp.`%s`))) order by ct limit 3", root, root)
        .ordered()
        .baselineColumns("ct")
        .baselineValues((long) 10)
        .baselineValues((long) 66)
        .baselineValues((long) 99)
        .build().run();

    testBuilder()
        .sqlQuery("select * from ((select count(c1) as ct from (select columns[0] c1 from cp.`%s`))\n" +
          "union all (select count(c1) as c2 from (select columns[0] c1 from cp.`%s`))) order by ct", root, root)
         .ordered()
         .baselineColumns("ct")
         .baselineValues((long) 10)
         .baselineValues((long) 10)
         .build().run();
  }

  @Test // see DRILL-2717
  @Category(UnlikelyTest.class)
  public void testUnionInputsGroupByOnCSV() throws Exception {
    String root = "/multilevel/csv/1994/Q1/orders_94_q1.csv";

    testBuilder()
        .sqlQuery("select * from \n" +
            "((select columns[0] as col0 from cp.`%s` t1 \n" +
            "where t1.columns[0] = 66) \n" +
            "union all \n" +
            "(select columns[0] c2 from cp.`%s` t2 \n" +
            "where t2.columns[0] is not null \n" +
            "group by columns[0])) \n" +
            "group by col0",
            root, root)
        .unOrdered()
        .baselineColumns("col0")
        .baselineValues("290")
        .baselineValues("291")
        .baselineValues("323")
        .baselineValues("352")
        .baselineValues("389")
        .baselineValues("417")
        .baselineValues("66")
        .baselineValues("673")
        .baselineValues("833")
        .baselineValues("99")
        .build().run();
  }

  @Test // see DRILL-2639
  @Category(UnlikelyTest.class)
  public void testUnionAllDiffTypesAtPlanning() throws Exception {
    testBuilder()
        .sqlQuery("select count(c1) as ct from (select cast(r_regionkey as int) c1 from cp.`tpch/region.parquet`) " +
          "union all (select cast(r_regionkey as int) c2 from cp.`tpch/region.parquet`)")
        .ordered()
        .baselineColumns("ct")
        .baselineValues((long) 5)
        .baselineValues((long) 0)
        .baselineValues((long) 1)
        .baselineValues((long) 2)
        .baselineValues((long) 3)
        .baselineValues((long) 4)
        .build().run();
  }

  @Test // see DRILL-2612
  @Category(UnlikelyTest.class)
  public void testUnionAllRightEmptyJson() throws Exception {
    String rootEmpty = "/project/pushdown/empty.json";
    String rootSimple = "/store/json/booleanData.json";

    testBuilder()
      .sqlQuery("select key from cp.`%s` " +
          "union all " +
          "select key from cp.`%s`",
        rootSimple,
        rootEmpty)
      .unOrdered()
      .baselineColumns("key")
      .baselineValues(true)
      .baselineValues(false)
      .build().run();
  }

  @Test
  public void testUnionAllLeftEmptyJson() throws Exception {
    final String rootEmpty = "/project/pushdown/empty.json";
    final String rootSimple = "/store/json/booleanData.json";

    testBuilder()
        .sqlQuery("select key from cp.`%s` " +
            "union all " +
            "select key from cp.`%s`",
          rootEmpty,
          rootSimple)
        .unOrdered()
        .baselineColumns("key")
        .baselineValues(true)
        .baselineValues(false)
        .build()
        .run();
  }

  @Test
  public void testUnionAllBothEmptyJson() throws Exception {
    final String rootEmpty = "/project/pushdown/empty.json";

    final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList();
    final TypeProtos.MajorType majorType = TypeProtos.MajorType.newBuilder()
        .setMinorType(TypeProtos.MinorType.INT)
        .setMode(TypeProtos.DataMode.OPTIONAL)
        .build();
    expectedSchema.add(Pair.of(SchemaPath.getSimplePath("key"), majorType));

    testBuilder()
        .sqlQuery("select key from cp.`%s` " +
            "union all " +
            "select key from cp.`%s`",
          rootEmpty,
          rootEmpty)
        .schemaBaseLine(expectedSchema)
        .build()
        .run();
  }

  @Test
  public void testUnionAllRightEmptyDataBatch() throws Exception {
    String rootSimple = "/store/json/booleanData.json";

    testBuilder()
        .sqlQuery("select key from cp.`%s` " +
            "union all " +
            "select key from cp.`%s` where 1 = 0",
          rootSimple,
          rootSimple)
        .unOrdered()
        .baselineColumns("key")
        .baselineValues(true)
        .baselineValues(false)
        .build().run();
  }

  @Test
  public void testUnionAllLeftEmptyDataBatch() throws Exception {
    String rootSimple = "/store/json/booleanData.json";

    testBuilder()
        .sqlQuery("select key from cp.`%s` where 1 = 0 " +
            "union all " +
            "select key from cp.`%s`",
          rootSimple,
          rootSimple)
        .unOrdered()
        .baselineColumns("key")
        .baselineValues(true)
        .baselineValues(false)
        .build()
        .run();
  }

  @Test
  public void testUnionAllBothEmptyDataBatch() throws Exception {
    String rootSimple = "/store/json/booleanData.json";

    final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList();
    final TypeProtos.MajorType majorType = TypeProtos.MajorType.newBuilder()
        .setMinorType(TypeProtos.MinorType.BIT) // field "key" is boolean type
        .setMode(TypeProtos.DataMode.OPTIONAL)
        .build();
    expectedSchema.add(Pair.of(SchemaPath.getSimplePath("key"), majorType));

    testBuilder()
        .sqlQuery("select key from cp.`%s` where 1 = 0 " +
            "union all " +
            "select key from cp.`%s` where 1 = 0",
          rootSimple,
          rootSimple)
        .schemaBaseLine(expectedSchema)
        .build()
        .run();
  }

  @Test // see DRILL-2746
  public void testFilterPushDownOverUnionAll() throws Exception {
    String query = "select n_regionkey from \n"
        + "(select n_regionkey from cp.`tpch/nation.parquet` union all select r_regionkey from cp.`tpch/region.parquet`) \n"
        + "where n_regionkey > 0 and n_regionkey < 2 \n"
        + "order by n_regionkey";

    // Validate the plan
    final String[] expectedPlan = {"Sort.*\n" +
        ".*UnionAll.*\n" +
            ".*SelectionVectorRemover.*\n" +
                ".*Filter.*\n" +
                    ".*Scan.*columns=\\[`n_regionkey`\\].*\n" +
            ".*SelectionVectorRemover.*\n" +
                ".*Filter.*\n" +
                    ".*Scan.*columns=\\[`r_regionkey`\\].*"};
    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    // Validate the result
    testBuilder()
        .sqlQuery(query)
        .ordered()
        .baselineColumns("n_regionkey")
        .baselineValues(1)
        .baselineValues(1)
        .baselineValues(1)
        .baselineValues(1)
        .baselineValues(1)
        .baselineValues(1)
        .build()
        .run();
  }

  @Test // see DRILL-2746
  @Category(UnlikelyTest.class)
  public void testInListOnUnionAll() throws Exception {
    String query = "select n_nationkey \n" +
        "from (select n1.n_nationkey from cp.`tpch/nation.parquet` n1 inner join cp.`tpch/region.parquet` r1 on n1.n_regionkey = r1.r_regionkey \n" +
        "union all \n" +
        "select n2.n_nationkey from cp.`tpch/nation.parquet` n2 inner join cp.`tpch/region.parquet` r2 on n2.n_regionkey = r2.r_regionkey) \n" +
        "where n_nationkey in (1, 2)";

    // Validate the plan
    final String[] expectedPlan = {"Project.*\n" +
        ".*UnionAll.*\n" +
            ".*Project.*\n" +
                ".*HashJoin.*\n" +
                    ".*SelectionVectorRemover.*\n" +
                        ".*Filter.*\n" +
                            ".*Scan.*columns=\\[`n_regionkey`, `n_nationkey`\\].*\n" +
                        ".*Scan.*columns=\\[`r_regionkey`\\].*\n" +
            ".*Project.*\n" +
                ".*HashJoin.*\n" +
                    ".*SelectionVectorRemover.*\n" +
                        ".*Filter.*\n" +
                            ".*Scan.*columns=\\[`n_regionkey`, `n_nationkey`\\].*\n" +
                        ".*Scan.*columns=\\[`r_regionkey`\\].*"};
    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    // Validate the result
    testBuilder()
        .sqlQuery(query)
        .ordered()
        .baselineColumns("n_nationkey")
        .baselineValues(1)
        .baselineValues(2)
        .baselineValues(1)
        .baselineValues(2)
        .build()
        .run();
  }

  @Test // see DRILL-2746
  public void testFilterPushDownOverUnionAllCSV() throws Exception {
    String root = "/multilevel/csv/1994/Q1/orders_94_q1.csv";

    String query = String.format("select ct \n" +
        "from ((select count(c1) as ct from (select columns[0] c1 from cp.`%s`)) \n" +
        "union all \n" +
        "(select columns[0] c2 from cp.`%s`)) \n" +
        "where ct < 100", root, root);

    // Validate the plan
    final String[] expectedPlan = {"Project.*\n" +
        ".*UnionAll.*\n" +
            ".*SelectionVectorRemover.*\n" +
                ".*Filter.*\n" +
                    ".*StreamAgg.*\n" +
                        ".*Project.*\n" +
                            ".*Scan.*columns=\\[`columns`\\[0\\]\\].*\n" +
            ".*SelectionVectorRemover.*\n" +
                ".*Filter.*\n" +
                    ".*Project.*\n" +
                        ".*Scan.*columns=\\[`columns`\\[0\\]\\].*"};

    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    // Validate the result
    testBuilder()
        .sqlQuery(query)
        .ordered()
        .baselineColumns("ct")
        .baselineValues((long) 10)
        .baselineValues((long) 66)
        .baselineValues((long) 99)
        .build().run();
  }

  @Test // see DRILL-3130
  public void testProjectPushDownOverUnionAllWithProject() throws Exception {
    String query = "select n_nationkey, n_name from \n" +
        "(select n_nationkey, n_name, n_comment from cp.`tpch/nation.parquet` \n" +
        "union all select r_regionkey, r_name, r_comment  from cp.`tpch/region.parquet`)";

    // Validate the plan
    final String[] expectedPlan = {"Project\\(n_nationkey=\\[\\$0\\], n_name=\\[\\$1\\]\\).*\n" +
        ".*UnionAll.*\n" +
            ".*Project.*\n" +
                ".*Scan.*columns=\\[`n_nationkey`, `n_name`\\].*\n" +
            ".*Project.*\n" +
                ".*Scan.*columns=\\[`r_regionkey`, `r_name`\\].*"
    };

    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/testProjectPushDownOverUnionAllWithProject.tsv")
        .baselineTypes(TypeProtos.MinorType.INT, TypeProtos.MinorType.VARCHAR)
        .baselineColumns("n_nationkey", "n_name")
        .build()
        .run();
  }

  @Test // see DRILL-3130
  public void testProjectPushDownOverUnionAllWithoutProject() throws Exception {
    String query = "select n_nationkey from \n" +
        "(select n_nationkey, n_name, n_comment from cp.`tpch/nation.parquet` \n" +
        "union all select r_regionkey, r_name, r_comment  from cp.`tpch/region.parquet`)";

    // Validate the plan
    final String[] expectedPlan = {"Project\\(n_nationkey=\\[\\$0\\]\\).*\n" +
        ".*UnionAll.*\n" +
            ".*Scan.*columns=\\[`n_nationkey`\\].*\n" +
            ".*Scan.*columns=\\[`r_regionkey`\\].*"};

    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    // Validate the result
    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/testProjectPushDownOverUnionAllWithoutProject.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("n_nationkey")
        .build()
        .run();
  }

  @Test // see DRILL-3130
  public void testProjectWithExpressionPushDownOverUnionAll() throws Exception {
    String query = "select 2 * n_nationkey as col from \n" +
        "(select n_nationkey, n_name, n_comment from cp.`tpch/nation.parquet` \n" +
        "union all select r_regionkey, r_name, r_comment  from cp.`tpch/region.parquet`)";

    // Validate the plan
    final String[] expectedPlan = {"UnionAll.*\n" +
        ".*Project\\(col=\\[\\*\\(2, \\$0\\)\\]\\).*\n" +
            ".*Scan.*columns=\\[`n_nationkey`\\].*\n" +
        ".*Project\\(col=\\[\\*\\(2, \\$0\\)\\]\\).*\n" +
            ".*Scan.*columns=\\[`r_regionkey`\\].*"};

    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    // Validate the result
    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/testProjectWithExpressionPushDownOverUnionAll.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("col")
        .build()
        .run();
  }

  @Test // see DRILL-3130
  public void testProjectDownOverUnionAllImplicitCasting() throws Exception {
    String root = "/store/text/data/nations.csv";
    String query = String.format("select 2 * n_nationkey as col from \n" +
        "(select n_nationkey, n_name, n_comment from cp.`tpch/nation.parquet` \n" +
        "union all select columns[0], columns[1], columns[2] from cp.`%s`) \n" +
        "order by col limit 10", root);

    // Validate the plan
    final String[] expectedPlan = {"UnionAll.*\n" +
        ".*Project\\(col=\\[\\*\\(2, \\$0\\)\\]\\).*\n" +
            ".*Scan.*columns=\\[`n_nationkey`\\].*\n" +
        ".*Project\\(col=\\[\\*\\(2, ITEM\\(\\$0, 0\\)\\)\\]\\).*\n" +
            ".*Scan.*columns=\\[`columns`\\[0\\]\\]"};

    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    // Validate the result
    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/testProjectDownOverUnionAllImplicitCasting.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("col")
        .build()
        .run();
  }

  @Test // see DRILL-3130
  public void testProjectPushDownProjectColumnReorderingAndAlias() throws Exception {
    String query = "select n_comment as col1, n_nationkey as col2, n_name as col3 from \n" +
        "(select n_nationkey, n_name, n_comment from cp.`tpch/nation.parquet` \n" +
        "union all select r_regionkey, r_name, r_comment  from cp.`tpch/region.parquet`)";

    // Validate the plan
    final String[] expectedPlan = {"UnionAll.*\n." +
        "*Project.*\n" +
            ".*Scan.*columns=\\[`n_comment`, `n_nationkey`, `n_name`\\].*\n" +
        ".*Project.*\n" +
            ".*Scan.*columns=\\[`r_comment`, `r_regionkey`, `r_name`\\]"};

    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    // Validate the result
    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/testProjectPushDownProjectColumnReorderingAndAlias.tsv")
        .baselineTypes(TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.INT, TypeProtos.MinorType.VARCHAR)
        .baselineColumns("col1", "col2", "col3")
        .build()
        .run();
  }

  @Test // see DRILL-2746, DRILL-3130
  public void testProjectFiltertPushDownOverUnionAll() throws Exception {
    String query = "select n_nationkey from \n" +
        "(select n_nationkey, n_name, n_comment from cp.`tpch/nation.parquet` \n" +
        "union all select r_regionkey, r_name, r_comment  from cp.`tpch/region.parquet`) \n" +
        "where n_nationkey > 0 and n_nationkey < 4";

    // Validate the plan
    final String[] expectedPlan = {"Project.*\n" +
        ".*UnionAll.*\n" +
            ".*SelectionVectorRemover.*\n" +
                ".*Filter.*\n" +
                    ".*Scan.*columns=\\[`n_nationkey`\\].*\n" +
        ".*SelectionVectorRemover.*\n" +
            ".*Filter.*\n" +
                ".*Scan.*columns=\\[`r_regionkey`\\]"
    };

    queryBuilder()
      .sql(query)
      .planMatcher()
      .include(expectedPlan)
      .match();

    // Validate the result
    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/testProjectFiltertPushDownOverUnionAll.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("n_nationkey")
        .build()
        .run();
  }

  @Test // DRILL-3257 (Simplified Query from TPC-DS query 74)
  @Category(UnlikelyTest.class)
  public void testUnionAllInWith() throws Exception {
    final String query1 = "WITH year_total \n" +
        "     AS (SELECT c.r_regionkey    customer_id,\n" +
        "                1 year_total\n" +
        "         FROM   cp.`tpch/region.parquet` c\n" +
        "         UNION ALL \n" +
        "         SELECT c.r_regionkey    customer_id, \n" +
        "                1 year_total\n" +
        "         FROM   cp.`tpch/region.parquet` c) \n" +
        "SELECT count(t_s_secyear.customer_id) as ct \n" +
        "FROM   year_total t_s_firstyear, \n" +
        "       year_total t_s_secyear, \n" +
        "       year_total t_w_firstyear, \n" +
        "       year_total t_w_secyear \n" +
        "WHERE  t_s_secyear.customer_id = t_s_firstyear.customer_id \n" +
        "       AND t_s_firstyear.customer_id = t_w_secyear.customer_id \n" +
        "       AND t_s_firstyear.customer_id = t_w_firstyear.customer_id \n" +
        "       AND CASE \n" +
        "             WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total \n" +
        "             ELSE NULL \n" +
        "           END > -1";

    final String query2 = "WITH year_total \n" +
        "     AS (SELECT c.r_regionkey    customer_id,\n" +
        "                1 year_total\n" +
        "         FROM   cp.`tpch/region.parquet` c\n" +
        "         UNION ALL \n" +
        "         SELECT c.r_regionkey    customer_id, \n" +
        "                1 year_total\n" +
        "         FROM   cp.`tpch/region.parquet` c) \n" +
        "SELECT count(t_w_firstyear.customer_id) as ct \n" +
        "FROM   year_total t_w_firstyear, \n" +
        "       year_total t_w_secyear \n" +
        "WHERE  t_w_firstyear.year_total = t_w_secyear.year_total \n" +
        " AND t_w_firstyear.year_total > 0 and t_w_secyear.year_total > 0";

    final String query3 = "WITH year_total_1\n" +
        "             AS (SELECT c.r_regionkey    customer_id,\n" +
        "                        1 year_total\n" +
        "                 FROM   cp.`tpch/region.parquet` c\n" +
        "                 UNION ALL \n" +
        "                 SELECT c.r_regionkey    customer_id, \n" +
        "                        1 year_total\n" +
        "                 FROM   cp.`tpch/region.parquet` c) \n" +
        "             , year_total_2\n" +
        "             AS (SELECT c.n_nationkey    customer_id,\n" +
        "                        1 year_total\n" +
        "                 FROM   cp.`tpch/nation.parquet` c\n" +
        "                 UNION ALL \n" +
        "                 SELECT c.n_nationkey    customer_id, \n" +
        "                        1 year_total\n" +
        "                 FROM   cp.`tpch/nation.parquet` c) \n" +
        "        SELECT count(t_w_firstyear.customer_id) as ct\n" +
        "        FROM   year_total_1 t_w_firstyear,\n" +
        "               year_total_2 t_w_secyear\n" +
        "        WHERE  t_w_firstyear.year_total = t_w_secyear.year_total\n" +
        "           AND t_w_firstyear.year_total > 0 and t_w_secyear.year_total > 0";

    final String query4 = "WITH year_total_1\n" +
        "             AS (SELECT c.r_regionkey    customer_id,\n" +
        "                        1 year_total\n" +
        "                 FROM   cp.`tpch/region.parquet` c\n" +
        "                 UNION ALL \n" +
        "                 SELECT c.n_nationkey    customer_id, \n" +
        "                        1 year_total\n" +
        "                 FROM   cp.`tpch/nation.parquet` c), \n" +
        "             year_total_2\n" +
        "             AS (SELECT c.r_regionkey    customer_id,\n" +
        "                        1 year_total\n" +
        "                 FROM   cp.`tpch/region.parquet` c\n" +
        "                 UNION ALL \n" +
        "                 SELECT c.n_nationkey    customer_id, \n" +
        "                        1 year_total\n" +
        "                 FROM   cp.`tpch/nation.parquet` c) \n" +
        "        SELECT count(t_w_firstyear.customer_id) as ct \n" +
        "        FROM   year_total_1 t_w_firstyear,\n" +
        "               year_total_2 t_w_secyear\n" +
        "        WHERE  t_w_firstyear.year_total = t_w_secyear.year_total\n" +
        "         AND t_w_firstyear.year_total > 0 and t_w_secyear.year_total > 0";

    testBuilder()
        .sqlQuery(query1)
        .ordered()
        .baselineColumns("ct")
        .baselineValues((long) 80)
        .build()
        .run();

    testBuilder()
        .sqlQuery(query2)
        .ordered()
        .baselineColumns("ct")
        .baselineValues((long) 100)
        .build()
        .run();

    testBuilder()
        .sqlQuery(query3)
        .ordered()
        .baselineColumns("ct")
        .baselineValues((long) 500)
        .build()
        .run();

    testBuilder()
        .sqlQuery(query4)
        .ordered()
        .baselineColumns("ct")
        .baselineValues((long) 900)
        .build()
        .run();
  }

  @Test // DRILL-4147 // base case
  @Category(UnlikelyTest.class)
  public void testDrill4147_1() throws Exception {
    final String l = "/multilevel/parquet/1994";
    final String r = "/multilevel/parquet/1995";

    final String query = String.format("SELECT o_custkey FROM dfs.`%s` \n" +
        "Union All SELECT o_custkey FROM dfs.`%s`", l, r);

    // Validate the plan
    final String[] expectedPlan = {"UnionExchange.*\n",
        ".*UnionAll"};

    try {
      client.alterSession(ExecConstants.SLICE_TARGET, 1);
      queryBuilder()
        .sql(query)
        .planMatcher()
        .include(expectedPlan)
        .match();

      testBuilder()
        .optionSettingQueriesForBaseline(SLICE_TARGET_DEFAULT)
        .unOrdered()
        .sqlQuery(query)
        .sqlBaselineQuery(query)
        .build()
        .run();
    } finally {
      client.resetSession(ExecConstants.SLICE_TARGET);
    }
  }

  @Test // DRILL-4147  // group-by on top of union-all
  public void testDrill4147_2() throws Exception {
    final String l = "/multilevel/parquet/1994";
    final String r = "/multilevel/parquet/1995";

    final String query = String.format("Select o_custkey, count(*) as cnt from \n" +
        " (SELECT o_custkey FROM dfs.`%s` \n" +
        "Union All SELECT o_custkey FROM dfs.`%s`) \n" +
        "group by o_custkey", l, r);

    // Validate the plan
    final String[] expectedPlan = {"(?s)UnionExchange.*HashAgg.*HashToRandomExchange.*UnionAll.*"};

    try {
      client.alterSession(ExecConstants.SLICE_TARGET, 1);
      queryBuilder()
        .sql(query)
        .planMatcher()
        .include(expectedPlan)
        .match();

      testBuilder()
        .optionSettingQueriesForBaseline(SLICE_TARGET_DEFAULT)
        .unOrdered()
        .sqlQuery(query)
        .sqlBaselineQuery(query)
        .build()
        .run();
    } finally {
      client.resetSession(ExecConstants.SLICE_TARGET);
    }
  }

  @Test // DRILL-4147 // union-all above a hash join
  public void testDrill4147_3() throws Exception {
    final String l = "/multilevel/parquet/1994";
    final String r = "/multilevel/parquet/1995";

    final String query = String.format("SELECT o_custkey FROM \n" +
        " (select o1.o_custkey from dfs.`%s` o1 inner join dfs.`%s` o2 on o1.o_orderkey = o2.o_custkey) \n" +
        " Union All SELECT o_custkey FROM dfs.`%s` where o_custkey > 10", l, r, l);

    // Validate the plan
    final String[] expectedPlan = {"(?s)UnionExchange.*UnionAll.*HashJoin.*"};

    try {
      client.alterSession(ExecConstants.SLICE_TARGET, 1);
      queryBuilder()
        .sql(query)
        .planMatcher()
        .include(expectedPlan)
        .match();

      testBuilder()
        .optionSettingQueriesForBaseline(SLICE_TARGET_DEFAULT)
        .unOrdered()
        .sqlQuery(query)
        .sqlBaselineQuery(query)
        .build()
        .run();
    } finally {
      client.resetSession(ExecConstants.SLICE_TARGET);
    }
  }

  @Test // DRILL-4833  // limit 1 is on RHS of union-all
  @Category(UnlikelyTest.class)
  public void testDrill4833_1() throws Exception {
    final String l = "/multilevel/parquet/1994";
    final String r = "/multilevel/parquet/1995";

    final String query = String.format("SELECT o_custkey FROM \n" +
        " ((select o1.o_custkey from dfs.`%s` o1 inner join dfs.`%s` o2 on o1.o_orderkey = o2.o_custkey) \n" +
        " Union All (SELECT o_custkey FROM dfs.`%s` limit 1))", l, r, l);

    // Validate the plan
    final String[] expectedPlan = {"(?s)UnionExchange.*UnionAll.*HashJoin.*"};

    try {
      client.alterSession(ExecConstants.SLICE_TARGET, 1);
      client.alterSession(PlannerSettings.UNIONALL_DISTRIBUTE_KEY, true);
      queryBuilder()
        .sql(query)
        .planMatcher()
        .include(expectedPlan)
        .match();

      testBuilder()
        .optionSettingQueriesForBaseline(SLICE_TARGET_DEFAULT)
        .unOrdered()
        .sqlQuery(query)
        .sqlBaselineQuery(query)
        .build()
        .run();
    } finally {
      client.resetSession(ExecConstants.SLICE_TARGET);
      client.resetSession(PlannerSettings.UNIONALL_DISTRIBUTE_KEY);
    }
  }

  @Test // DRILL-4833  // limit 1 is on LHS of union-all
  @Category(UnlikelyTest.class)
  public void testDrill4833_2() throws Exception {
    final String l = "/multilevel/parquet/1994";
    final String r = "/multilevel/parquet/1995";

    final String query = String.format("SELECT o_custkey FROM \n" +
        " ((SELECT o_custkey FROM dfs.`%s` limit 1) \n" +
        " union all \n" +
        " (select o1.o_custkey from dfs.`%s` o1 inner join dfs.`%s` o2 on o1.o_orderkey = o2.o_custkey))", l, r, l);

    // Validate the plan
    final String[] expectedPlan = {"(?s)UnionExchange.*UnionAll.*HashJoin.*"};

    try {
      client.alterSession(ExecConstants.SLICE_TARGET, 1);
      client.alterSession(PlannerSettings.UNIONALL_DISTRIBUTE_KEY, true);
      queryBuilder()
        .sql(query)
        .planMatcher()
        .include(expectedPlan)
        .match();

      testBuilder()
        .optionSettingQueriesForBaseline(SLICE_TARGET_DEFAULT)
        .unOrdered()
        .sqlQuery(query)
        .sqlBaselineQuery(query)
        .build()
        .run();
    } finally {
      client.resetSession(ExecConstants.SLICE_TARGET);
      client.resetSession(PlannerSettings.UNIONALL_DISTRIBUTE_KEY);
    }
  }

  @Test // DRILL-5130
  public void testUnionAllWithValues() throws Exception {
    testBuilder()
        .sqlQuery("values('A') union all values('B')")
        .unOrdered()
        .baselineColumns("EXPR$0")
        .baselineValues("A")
        .baselineValues("B")
        .go();
  }

  @Test // DRILL-4264
  @Category(UnlikelyTest.class)
  public void testFieldWithDots() throws Exception {
    String fileName = "table.json";
    try (BufferedWriter writer = new BufferedWriter(new FileWriter(new File(dirTestWatcher.getRootDir(), fileName)))) {
      writer.write("{\"rk.q\": \"a\", \"m\": {\"a.b\":\"1\", \"a\":{\"b\":\"2\"}, \"c\":\"3\"}}");
    }

    testBuilder()
      .sqlQuery("select * from (" +
        "(select t.m.`a.b` as a,\n" +
        "t.m.a.b as b,\n" +
        "t.m['a.b'] as c,\n" +
        "t.rk.q as d,\n" +
        "t.`rk.q` as e\n" +
        "from dfs.`%1$s` t)\n" +
        "union all\n" +
        "(select t.m.`a.b` as a,\n" +
        "t.m.a.b as b,\n" +
        "t.m['a.b'] as c,\n" +
        "t.rk.q as d,\n" +
        "t.`rk.q` as e\n" +
        "from dfs.`%1$s` t))", fileName)
      .unOrdered()
      .baselineColumns("a", "b", "c", "d", "e")
      .baselineValues("1", "2", "1", null, "a")
      .baselineValues("1", "2", "1", null, "a")
      .go();
  }

  @Test
  public void testUnionAllRightEmptyDir() throws Exception {
    String rootSimple = "/store/json/booleanData.json";

    testBuilder()
        .sqlQuery("SELECT key FROM cp.`%s` UNION ALL SELECT key FROM dfs.tmp_default_format.`%s`",
            rootSimple, EMPTY_DIR_NAME)
        .unOrdered()
        .baselineColumns("key")
        .baselineValues(true)
        .baselineValues(false)
        .build()
        .run();
  }

  @Test
  public void testUnionAllLeftEmptyDir() throws Exception {
    final String rootSimple = "/store/json/booleanData.json";

    testBuilder()
        .sqlQuery("SELECT key FROM dfs.tmp_default_format.`%s` UNION ALL SELECT key FROM cp.`%s`",
            EMPTY_DIR_NAME, rootSimple)
        .unOrdered()
        .baselineColumns("key")
        .baselineValues(true)
        .baselineValues(false)
        .build()
        .run();
  }

  @Test
  public void testUnionAllBothEmptyDirs() throws Exception {
    SchemaBuilder schemaBuilder = new SchemaBuilder()
        .addNullable("key", TypeProtos.MinorType.INT);
    BatchSchema expectedSchema = new BatchSchemaBuilder()
        .withSchemaBuilder(schemaBuilder)
        .build();

    testBuilder()
        .sqlQuery(
            "SELECT key FROM dfs.tmp_default_format.`%1$s` UNION ALL SELECT key FROM " +
            "dfs.tmp_default_format.`%1$s`",
            EMPTY_DIR_NAME
        )
        .schemaBaseLine(expectedSchema)
        .build()
        .run();
  }

  @Test
  public void testUnionAllMiddleEmptyDir() throws Exception {
    final String query = "SELECT n_regionkey FROM cp.`tpch/nation.parquet` UNION ALL " +
        "SELECT missing_key FROM dfs.tmp_default_format.`%s` UNION ALL SELECT r_regionkey " +
        "FROM cp.`tpch/region.parquet`";

    testBuilder()
        .sqlQuery(query, EMPTY_DIR_NAME)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/q1.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("n_regionkey")
        .build()
        .run();
  }

  @Test
  public void testComplexQueryWithUnionAllAndEmptyDir() throws Exception {
    final String rootSimple = "/store/json/booleanData.json";

    testBuilder()
        .sqlQuery("SELECT key FROM dfs.tmp_default_format.`%1$s` UNION ALL SELECT key FROM " +
            "(SELECT key FROM dfs.tmp_default_format.`%1$s` UNION ALL SELECT key FROM cp.`%2$s`)",
            EMPTY_DIR_NAME, rootSimple)
        .unOrdered()
        .baselineColumns("key")
        .baselineValues(true)
        .baselineValues(false)
        .build()
        .run();
  }

  @Test // DRILL-3855
  public void testEmptyResultAfterProjectPushDownOverUnionAll() throws Exception {
    String query = "select n_nationkey from " +
        "(select n_nationkey, n_name, n_comment from cp.`tpch/nation.parquet` " +
        "union all select r_regionkey, r_name, r_comment  from cp.`tpch/region.parquet`) " +
        "where n_nationkey > 4";

    // Validate the plan
    final String[] expectedPlan = {"Project.*\n" +
        ".*UnionAll.*\n" +
            ".*SelectionVectorRemover.*\n" +
                ".*Filter.*\n" +
                    ".*Scan.*columns=\\[`n_nationkey`\\].*\n" +
            ".*SelectionVectorRemover.*\n" +
                ".*Filter.*\n" +
                    ".*Scan.*columns=\\[`r_regionkey`\\]"};

      queryBuilder()
        .sql(query)
        .planMatcher()
        .include(expectedPlan)
        .match();


    testBuilder()
        .sqlQuery(query)
        .unOrdered()
        .csvBaselineFile("testframework/testUnionAllQueries/testEmptyResultAfterProjectPushDownOverUnionAll.tsv")
        .baselineTypes(TypeProtos.MinorType.INT)
        .baselineColumns("n_nationkey")
        .build()
        .run();
  }

  @Test // DRILL-8137
  public void testUnionCancellation() throws Exception {
    String query = "WITH foo AS\n" +
      "  (SELECT 1 AS a FROM cp.`/tpch/nation.parquet`\n" +
      "   UNION ALL\n" +
      "   SELECT 1 AS a FROM cp.`/tpch/nation.parquet`\n" +
      "   WHERE n_nationkey > (SELECT 1) )\n" +
      "SELECT * FROM foo\n" +
      "LIMIT 1";

    testBuilder()
      .sqlQuery(query)
      .unOrdered()
      .baselineColumns("a")
      .baselineValues(1)
      .build()
      .run();
  }
}