/*
* 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 com.google.common.collect.Lists;
import org.apache.commons.lang3.tuple.Pair;
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.common.util.FileUtils;
import org.apache.drill.exec.work.foreman.SqlUnsupportedException;
import org.apache.drill.exec.work.foreman.UnsupportedRelOperatorException;
import org.junit.Test;
import java.util.List;
public class TestUnionAll extends BaseTestQuery{
// private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(TestUnionAll.class);
private static final String sliceTargetSmall = "alter session set `planner.slice_target` = 1";
private static final String sliceTargetDefault = "alter session reset `planner.slice_target`";
private static final String enableDistribute = "alter session set `planner.enable_unionall_distribute` = true";
private static final String defaultDistribute = "alter session reset `planner.enable_unionall_distribute`";
@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
public void testUnionAll9() throws Exception {
String file0 = FileUtils.getResourceAsFile("/multilevel/json/1994/Q1/orders_94_q1.json").toURI().toString();
String file1 = FileUtils.getResourceAsFile("/multilevel/json/1995/Q1/orders_95_q1.json").toURI().toString();
String query = String.format("select o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, o_orderkey from dfs_test.`%s` union all " +
"select o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, o_orderkey from dfs_test.`%s`", file0, file1);
testBuilder()
.sqlQuery(query)
.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
public void testUnionAllViewExpandableStar() throws Exception {
test("use dfs_test.tmp");
test("create view nation_view_testunionall as select n_name, n_nationkey from cp.`tpch/nation.parquet`;");
test("create view region_view_testunionall as select r_name, r_regionkey from cp.`tpch/region.parquet`;");
String query1 = "(select * from dfs_test.tmp.`nation_view_testunionall`) " +
"union all " +
"(select * from dfs_test.tmp.`region_view_testunionall`) ";
String query2 = "(select r_name, r_regionkey from cp.`tpch/region.parquet`) " +
"union all " +
"(select * from dfs_test.tmp.`nation_view_testunionall`)";
try {
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 {
test("drop view nation_view_testunionall");
test("drop view region_view_testunionall");
}
}
@Test(expected = UnsupportedRelOperatorException.class) // see DRILL-2002
public void testUnionAllViewUnExpandableStar() throws Exception {
test("use dfs_test.tmp");
test("create view nation_view_testunionall as select * from cp.`tpch/nation.parquet`;");
try {
String query = "(select * from dfs_test.tmp.`nation_view_testunionall`) " +
"union all (select * from cp.`tpch/region.parquet`)";
test(query);
} catch(UserException ex) {
SqlUnsupportedException.errorClassNameToException(ex.getOrCreatePBError(false).getException().getExceptionClass());
throw ex;
} finally {
test("drop view nation_view_testunionall");
}
}
@Test
public void testDiffDataTypesAndModes() throws Exception {
test("use dfs_test.tmp");
test("create view nation_view_testunionall as select n_name, n_nationkey from cp.`tpch/nation.parquet`;");
test("create view region_view_testunionall 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 limit 5)";
String t3 = "(select full_name, store_id from cp.`employee.json` limit 5)";
String t4 = "(select * from region_view_testunionall limit 5)";
String query1 = t1 + " union all " + t2 + " union all " + t3 + " union all " + t4;
try {
testBuilder()
.sqlQuery(query1)
.unOrdered()
.csvBaselineFile("testframework/testUnionAllQueries/q13.tsv")
.baselineTypes(TypeProtos.MinorType.VARCHAR, TypeProtos.MinorType.BIGINT)
.baselineColumns("n_comment", "n_regionkey")
.build().run();
} finally {
test("drop view nation_view_testunionall");
test("drop view region_view_testunionall");
}
}
@Test // see DRILL-2203
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
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
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
public void testAggregationOnUnionAllOperator() throws Exception {
String root = FileUtils.getResourceAsFile("/store/text/data/t.json").toURI().toString();
String query1 = String.format(
"(select calc1, max(b1) as `max`, min(b1) as `min`, count(c1) as `count` " +
"from (select a1 + 10 as calc1, b1, c1 from dfs_test.`%s` " +
"union all " +
"select a1 + 100 as diff1, b1 as diff2, c1 as diff3 from dfs_test.`%s`) " +
"group by calc1 order by calc1)", root, root);
String query2 = String.format(
"(select calc1, min(b1) as `min`, max(b1) as `max`, count(c1) as `count` " +
"from (select a1 + 10 as calc1, b1, c1 from dfs_test.`%s` " +
"union all " +
"select a1 + 100 as diff1, b1 as diff2, c1 as diff3 from dfs_test.`%s`) " +
"group by calc1 order by calc1)", root, root);
testBuilder()
.sqlQuery(query1)
.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(query2)
.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 = FileUtils.getResourceAsFile("/store/json/intData.json").toURI().toString();
String rootBoolean = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString();
String query = String.format(
"(select key from dfs_test.`%s` " +
"union all " +
"select key from dfs_test.`%s` )", rootInt, rootBoolean);
test(query);
}
@Test // see DRILL-2591
public void testDateAndTimestampJson() throws Exception {
String rootDate = FileUtils.getResourceAsFile("/store/json/dateData.json").toURI().toString();
String rootTimpStmp = FileUtils.getResourceAsFile("/store/json/timeStmpData.json").toURI().toString();
String query1 = String.format(
"(select max(key) as key from dfs_test.`%s` " +
"union all " +
"select key from dfs_test.`%s`)", rootDate, rootTimpStmp);
String query2 = String.format(
"select key from dfs_test.`%s` " +
"union all " +
"select max(key) as key from dfs_test.`%s`", rootDate, rootTimpStmp);
String query3 = String.format(
"select key from dfs_test.`%s` " +
"union all " +
"select max(key) as key from dfs_test.`%s`", rootDate, rootTimpStmp);
testBuilder()
.sqlQuery(query1)
.unOrdered()
.csvBaselineFile("testframework/testUnionAllQueries/q18_1.tsv")
.baselineTypes(TypeProtos.MinorType.VARCHAR)
.baselineColumns("key")
.build().run();
testBuilder()
.sqlQuery(query2)
.unOrdered()
.csvBaselineFile("testframework/testUnionAllQueries/q18_2.tsv")
.baselineTypes(TypeProtos.MinorType.VARCHAR)
.baselineColumns("key")
.build().run();
testBuilder()
.sqlQuery(query3)
.unOrdered()
.csvBaselineFile("testframework/testUnionAllQueries/q18_3.tsv")
.baselineTypes(TypeProtos.MinorType.VARCHAR)
.baselineColumns("key")
.build().run();
}
@Test // see DRILL-2637
public void testUnionAllOneInputContainsAggFunction() throws Exception {
String root = FileUtils.getResourceAsFile("/multilevel/csv/1994/Q1/orders_94_q1.csv").toURI().toString();
String query1 = String.format("select * from ((select count(c1) as ct from (select columns[0] c1 from dfs.`%s`)) \n" +
"union all \n" +
"(select columns[0] c2 from dfs.`%s`)) order by ct limit 3", root, root);
String query2 = String.format("select * from ((select columns[0] ct from dfs.`%s`)\n" +
"union all \n" +
"(select count(c1) as c2 from (select columns[0] c1 from dfs.`%s`))) order by ct limit 3", root, root);
String query3 = String.format("select * from ((select count(c1) as ct from (select columns[0] c1 from dfs.`%s`))\n" +
"union all \n" +
"(select count(c1) as c2 from (select columns[0] c1 from dfs.`%s`))) order by ct", root, root);
testBuilder()
.sqlQuery(query1)
.ordered()
.baselineColumns("ct")
.baselineValues((long) 10)
.baselineValues((long) 66)
.baselineValues((long) 99)
.build().run();
testBuilder()
.sqlQuery(query2)
.ordered()
.baselineColumns("ct")
.baselineValues((long) 10)
.baselineValues((long) 66)
.baselineValues((long) 99)
.build().run();
testBuilder()
.sqlQuery(query3)
.ordered()
.baselineColumns("ct")
.baselineValues((long) 10)
.baselineValues((long) 10)
.build().run();
}
@Test // see DRILL-2717
public void testUnionInputsGroupByOnCSV() throws Exception {
String root = FileUtils.getResourceAsFile("/multilevel/csv/1994/Q1/orders_94_q1.csv").toURI().toString();
String query = String.format("select * from \n" +
"((select columns[0] as col0 from dfs.`%s` t1 \n" +
"where t1.columns[0] = 66) \n" +
"union all \n" +
"(select columns[0] c2 from dfs.`%s` t2 \n" +
"where t2.columns[0] is not null \n" +
"group by columns[0])) \n" +
"group by col0"
, root, root);
testBuilder()
.sqlQuery(query)
.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
public void testUnionAllDiffTypesAtPlanning() throws Exception {
String query = "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`)";
testBuilder()
.sqlQuery(query)
.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
public void testUnionAllRightEmptyJson() throws Exception {
String rootEmpty = FileUtils.getResourceAsFile("/project/pushdown/empty.json").toURI().toString();
String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString();
String queryRightEmpty = String.format(
"select key from dfs_test.`%s` " +
"union all " +
"select key from dfs_test.`%s`",
rootSimple,
rootEmpty);
testBuilder()
.sqlQuery(queryRightEmpty)
.unOrdered()
.baselineColumns("key")
.baselineValues(true)
.baselineValues(false)
.build().run();
}
@Test
public void testUnionAllLeftEmptyJson() throws Exception {
final String rootEmpty = FileUtils.getResourceAsFile("/project/pushdown/empty.json").toURI().toString();
final String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString();
final String queryLeftEmpty = String.format(
"select key from dfs_test.`%s` " +
"union all " +
"select key from dfs_test.`%s`",
rootEmpty,
rootSimple);
testBuilder()
.sqlQuery(queryLeftEmpty)
.unOrdered()
.baselineColumns("key")
.baselineValues(true)
.baselineValues(false)
.build()
.run();
}
@Test
public void testUnionAllBothEmptyJson() throws Exception {
final String rootEmpty = FileUtils.getResourceAsFile("/project/pushdown/empty.json").toURI().toString();
final String query = String.format(
"select key from dfs_test.`%s` " +
"union all " +
"select key from dfs_test.`%s`",
rootEmpty,
rootEmpty);
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(query)
.schemaBaseLine(expectedSchema)
.build()
.run();
}
@Test
public void testUnionAllRightEmptyBatch() throws Exception {
String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString();
String queryRightEmptyBatch = String.format(
"select key from dfs_test.`%s` " +
"union all " +
"select key from dfs_test.`%s` where 1 = 0",
rootSimple,
rootSimple);
testBuilder()
.sqlQuery(queryRightEmptyBatch)
.unOrdered()
.baselineColumns("key")
.baselineValues(true)
.baselineValues(false)
.build().run();
}
@Test
public void testUnionAllLeftEmptyBatch() throws Exception {
String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString();
final String queryLeftBatch = String.format(
"select key from dfs_test.`%s` where 1 = 0 " +
"union all " +
"select key from dfs_test.`%s`",
rootSimple,
rootSimple);
testBuilder()
.sqlQuery(queryLeftBatch)
.unOrdered()
.baselineColumns("key")
.baselineValues(true)
.baselineValues(false)
.build()
.run();
}
@Test
public void testUnionAllBothEmptyBatch() throws Exception {
String rootSimple = FileUtils.getResourceAsFile("/store/json/booleanData.json").toURI().toString();
final String query = String.format(
"select key from dfs_test.`%s` where 1 = 0 " +
"union all " +
"select key from dfs_test.`%s` where 1 = 0",
rootSimple,
rootSimple);
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(query)
.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 = {".*Filter.*\n" +
".*UnionAll.*\n" +
".*Scan.*columns=\\[`n_regionkey`\\].*\n" +
".*Scan.*columns=\\[`r_regionkey`\\].*"};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
// 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
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 = {"Filter.*\n" +
".*UnionAll.*\n" +
".*Project.*\n" +
".*HashJoin.*\n" +
".*Scan.*columns=\\[`n_regionkey`, `n_nationkey`\\].*\n" +
".*Scan.*columns=\\[`r_regionkey`\\].*\n" +
".*Project.*\n" +
".*HashJoin.*\n" +
".*Scan.*columns=\\[`n_regionkey`, `n_nationkey`\\].*\n" +
".*Scan.*columns=\\[`r_regionkey`\\].*"};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
// 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 = FileUtils.getResourceAsFile("/multilevel/csv/1994/Q1/orders_94_q1.csv").toURI().toString();
String query = String.format("select ct \n" +
"from ((select count(c1) as ct from (select columns[0] c1 from dfs.`%s`)) \n" +
"union all \n" +
"(select columns[0] c2 from dfs.`%s`)) \n" +
"where ct < 100", root, root);
// Validate the plan
final String[] expectedPlan = {"Filter.*\n" +
".*UnionAll.*\n" +
".*StreamAgg.*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`columns`\\[0\\]\\].*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`columns`\\[0\\]\\].*"};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
// 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_comment`\\].*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`r_regionkey`, `r_name`, `r_comment`\\].*"
};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
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" +
".*Project.*\n" +
".*Scan.*columns=\\[`n_nationkey`, `n_name`, `n_comment`\\].*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`r_regionkey`, `r_name`, `r_comment`\\].*"};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
// 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.*\n" +
".*Scan.*columns=\\[`n_nationkey`, `n_name`, `n_comment`\\].*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`r_regionkey`, `r_name`, `r_comment`\\].*"};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
// 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 = FileUtils.getResourceAsFile("/store/text/data/nations.csv").toURI().toString();
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 dfs.`%s`) \n" +
"order by col limit 10", root);
// Validate the plan
final String[] expectedPlan = {"UnionAll.*\n." +
".*Project.*\n" +
".*Scan.*columns=\\[`n_nationkey`, `n_name`, `n_comment`\\].*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`columns`\\[0\\], `columns`\\[1\\], `columns`\\[2\\]\\]"};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
// 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_nationkey`, `n_name`, `n_comment`\\].*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`r_regionkey`, `r_name`, `r_comment`\\]"};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
// 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 = {"Filter.*\n" +
".*UnionAll.*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`n_nationkey`, `n_name`, `n_comment`\\].*\n" +
".*Project.*\n" +
".*Scan.*columns=\\[`r_regionkey`, `r_name`, `r_comment`\\]"};
final String[] excludedPlan = {};
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
// 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)
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
public void testDrill4147_1() throws Exception {
final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString();
final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString();
final String query = String.format("SELECT o_custkey FROM dfs_test.`%s` \n" +
"Union All SELECT o_custkey FROM dfs_test.`%s`", l, r);
// Validate the plan
final String[] expectedPlan = {"UnionExchange.*\n",
".*Project.*\n" +
".*UnionAll"};
final String[] excludedPlan = {};
try {
test(sliceTargetSmall);
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
testBuilder()
.optionSettingQueriesForTestQuery(sliceTargetSmall)
.optionSettingQueriesForBaseline(sliceTargetDefault)
.unOrdered()
.sqlQuery(query)
.sqlBaselineQuery(query)
.build()
.run();
} finally {
test(sliceTargetDefault);
}
}
@Test // DRILL-4147 // group-by on top of union-all
public void testDrill4147_2() throws Exception {
final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString();
final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString();
final String query = String.format("Select o_custkey, count(*) as cnt from \n" +
" (SELECT o_custkey FROM dfs_test.`%s` \n" +
"Union All SELECT o_custkey FROM dfs_test.`%s`) \n" +
"group by o_custkey", l, r);
// Validate the plan
final String[] expectedPlan = {"(?s)UnionExchange.*HashAgg.*HashToRandomExchange.*UnionAll.*"};
final String[] excludedPlan = {};
try {
test(sliceTargetSmall);
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
testBuilder()
.optionSettingQueriesForTestQuery(sliceTargetSmall)
.optionSettingQueriesForBaseline(sliceTargetDefault)
.unOrdered()
.sqlQuery(query)
.sqlBaselineQuery(query)
.build()
.run();
} finally {
test(sliceTargetDefault);
}
}
@Test // DRILL-4147 // union-all above a hash join
public void testDrill4147_3() throws Exception {
final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString();
final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString();
final String query = String.format("SELECT o_custkey FROM \n" +
" (select o1.o_custkey from dfs_test.`%s` o1 inner join dfs_test.`%s` o2 on o1.o_orderkey = o2.o_custkey) \n" +
" Union All SELECT o_custkey FROM dfs_test.`%s` where o_custkey > 10", l, r, l);
// Validate the plan
final String[] expectedPlan = {"(?s)UnionExchange.*UnionAll.*HashJoin.*"};
final String[] excludedPlan = {};
try {
test(sliceTargetSmall);
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
testBuilder()
.optionSettingQueriesForTestQuery(sliceTargetSmall)
.optionSettingQueriesForBaseline(sliceTargetDefault)
.unOrdered()
.sqlQuery(query)
.sqlBaselineQuery(query)
.build()
.run();
} finally {
test(sliceTargetDefault);
}
}
@Test // DRILL-4833 // limit 1 is on RHS of union-all
public void testDrill4833_1() throws Exception {
final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString();
final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString();
final String query = String.format("SELECT o_custkey FROM \n" +
" ((select o1.o_custkey from dfs_test.`%s` o1 inner join dfs_test.`%s` o2 on o1.o_orderkey = o2.o_custkey) \n" +
" Union All (SELECT o_custkey FROM dfs_test.`%s` limit 1))", l, r, l);
// Validate the plan
final String[] expectedPlan = {"(?s)UnionExchange.*UnionAll.*HashJoin.*"};
final String[] excludedPlan = {};
try {
test(sliceTargetSmall);
test(enableDistribute);
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
testBuilder()
.optionSettingQueriesForTestQuery(sliceTargetSmall)
.optionSettingQueriesForBaseline(sliceTargetDefault)
.unOrdered()
.sqlQuery(query)
.sqlBaselineQuery(query)
.build()
.run();
} finally {
test(sliceTargetDefault);
test(defaultDistribute);
}
}
@Test // DRILL-4833 // limit 1 is on LHS of union-all
public void testDrill4833_2() throws Exception {
final String l = FileUtils.getResourceAsFile("/multilevel/parquet/1994").toURI().toString();
final String r = FileUtils.getResourceAsFile("/multilevel/parquet/1995").toURI().toString();
final String query = String.format("SELECT o_custkey FROM \n" +
" ((SELECT o_custkey FROM dfs_test.`%s` limit 1) \n" +
" union all \n" +
" (select o1.o_custkey from dfs_test.`%s` o1 inner join dfs_test.`%s` o2 on o1.o_orderkey = o2.o_custkey))", l, r, l);
// Validate the plan
final String[] expectedPlan = {"(?s)UnionExchange.*UnionAll.*HashJoin.*"};
final String[] excludedPlan = {};
try {
test(sliceTargetSmall);
test(enableDistribute);
PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPlan);
testBuilder()
.optionSettingQueriesForTestQuery(sliceTargetSmall)
.optionSettingQueriesForBaseline(sliceTargetDefault)
.unOrdered()
.sqlQuery(query)
.sqlBaselineQuery(query)
.build()
.run();
} finally {
test(sliceTargetDefault);
test(defaultDistribute);
}
}
}