/*
* 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
* <p/>
* http://www.apache.org/licenses/LICENSE-2.0
* <p/>
* 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.exec.physical.impl.limit;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.tuple.Pair;
import org.apache.drill.BaseTestQuery;
import org.apache.drill.PlanTestBase;
import org.apache.drill.common.expression.SchemaPath;
import org.apache.drill.common.types.TypeProtos;
import org.apache.drill.common.types.Types;
import org.apache.drill.exec.ExecConstants;
import org.joda.time.DateTime;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.sql.Date;
import java.util.List;
public class TestEarlyLimit0Optimization extends BaseTestQuery {
private static final String viewName = "limitZeroEmployeeView";
private static String wrapLimit0(final String query) {
return "SELECT * FROM (" + query + ") LZT LIMIT 0";
}
@BeforeClass
public static void createView() throws Exception {
test("USE dfs_test.tmp");
test(String.format("CREATE OR REPLACE VIEW %s AS SELECT " +
"CAST(employee_id AS INT) AS employee_id, " +
"CAST(full_name AS VARCHAR(25)) AS full_name, " +
"CAST(position_id AS INTEGER) AS position_id, " +
"CAST(department_id AS BIGINT) AS department_id," +
"CAST(birth_date AS DATE) AS birth_date, " +
"CAST(hire_date AS TIMESTAMP) AS hire_date, " +
"CAST(salary AS DOUBLE) AS salary, " +
"CAST(salary AS FLOAT) AS fsalary, " +
"CAST((CASE WHEN marital_status = 'S' THEN true ELSE false END) AS BOOLEAN) AS single, " +
"CAST(education_level AS VARCHAR(60)) AS education_level," +
"CAST(gender AS CHAR(1)) AS gender " +
"FROM cp.`employee.json` " +
"ORDER BY employee_id " +
"LIMIT 1;", viewName));
// { "employee_id":1,"full_name":"Sheri Nowmer","first_name":"Sheri","last_name":"Nowmer","position_id":1,
// "position_title":"President","store_id":0,"department_id":1,"birth_date":"1961-08-26",
// "hire_date":"1994-12-01 00:00:00.0","end_date":null,"salary":80000.0000,"supervisor_id":0,
// "education_level":"Graduate Degree","marital_status":"S","gender":"F","management_role":"Senior Management" }
}
@AfterClass
public static void tearDownView() throws Exception {
test("DROP VIEW " + viewName + ";");
}
@Before
public void setOption() throws Exception {
test("SET `%s` = true;", ExecConstants.EARLY_LIMIT0_OPT_KEY);
}
@After
public void resetOption() throws Exception {
test("RESET `%s`;", ExecConstants.EARLY_LIMIT0_OPT_KEY);
}
// -------------------- SIMPLE QUERIES --------------------
@Test
public void infoSchema() throws Exception {
testBuilder()
.sqlQuery(String.format("DESCRIBE %s", viewName))
.unOrdered()
.baselineColumns("COLUMN_NAME", "DATA_TYPE", "IS_NULLABLE")
.baselineValues("employee_id", "INTEGER", "YES")
.baselineValues("full_name", "CHARACTER VARYING", "YES")
.baselineValues("position_id", "INTEGER", "YES")
.baselineValues("department_id", "BIGINT", "YES")
.baselineValues("birth_date", "DATE", "YES")
.baselineValues("hire_date", "TIMESTAMP", "YES")
.baselineValues("salary", "DOUBLE", "YES")
.baselineValues("fsalary", "FLOAT", "YES")
.baselineValues("single", "BOOLEAN", "NO")
.baselineValues("education_level", "CHARACTER VARYING", "YES")
.baselineValues("gender", "CHARACTER", "YES")
.go();
}
@Test
public void simpleSelect() throws Exception {
testBuilder()
.sqlQuery(String.format("SELECT * FROM %s", viewName))
.ordered()
.baselineColumns("employee_id", "full_name", "position_id", "department_id", "birth_date", "hire_date",
"salary", "fsalary", "single", "education_level", "gender")
.baselineValues(1, "Sheri Nowmer", 1, 1L, new DateTime(Date.valueOf("1961-08-26").getTime()),
new DateTime(Date.valueOf("1994-12-01").getTime()), 80000.0D, 80000.0F, true, "Graduate Degree", "F")
.go();
}
@Test
public void simpleSelectLimit0() throws Exception {
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("employee_id"), Types.optional(TypeProtos.MinorType.INT)),
Pair.of(SchemaPath.getSimplePath("full_name"), Types.withPrecision(TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL, 25)),
Pair.of(SchemaPath.getSimplePath("position_id"), Types.optional(TypeProtos.MinorType.INT)),
Pair.of(SchemaPath.getSimplePath("department_id"), Types.optional(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("birth_date"), Types.optional(TypeProtos.MinorType.DATE)),
Pair.of(SchemaPath.getSimplePath("hire_date"), Types.optional(TypeProtos.MinorType.TIMESTAMP)),
Pair.of(SchemaPath.getSimplePath("salary"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("fsalary"), Types.optional(TypeProtos.MinorType.FLOAT4)),
Pair.of(SchemaPath.getSimplePath("single"), Types.required(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("education_level"), Types.withPrecision(TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL, 60)),
Pair.of(SchemaPath.getSimplePath("gender"), Types.withPrecision(TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL, 1)));
testBuilder()
.sqlQuery(wrapLimit0(String.format("SELECT * FROM %s", viewName)))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized("SELECT * FROM " + viewName);
}
private static void checkThatQueryPlanIsOptimized(final String query) throws Exception {
PlanTestBase.testPlanMatchingPatterns(
wrapLimit0(query),
new String[]{
".*Project.*\n" +
".*Scan.*RelDataTypeReader.*"
},
new String[]{});
}
// -------------------- AGGREGATE FUNC. QUERIES --------------------
private static String getAggQuery(final String functionName) {
return "SELECT " +
functionName + "(employee_id) AS e, " +
functionName + "(position_id) AS p, " +
functionName + "(department_id) AS d, " +
functionName + "(salary) AS s, " +
functionName + "(fsalary) AS f " +
"FROM " + viewName;
}
@Test
public void sums() throws Exception {
final String query = getAggQuery("SUM");
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("e"), Types.optional(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("p"), Types.optional(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("d"), Types.optional(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("s"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("f"), Types.optional(TypeProtos.MinorType.FLOAT8)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("e", "p", "d", "s", "f")
.baselineValues(1L, 1L, 1L, 80000D, 80000D)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void counts() throws Exception {
final String query = getAggQuery("COUNT");
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("e"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("p"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("d"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("s"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("f"), Types.required(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.baselineColumns("e", "p", "d", "s", "f")
.ordered()
.baselineValues(1L, 1L, 1L, 1L, 1L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
private void minAndMaxTest(final String functionName) throws Exception {
final String query = getAggQuery(functionName);
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("e"), Types.optional(TypeProtos.MinorType.INT)),
Pair.of(SchemaPath.getSimplePath("p"), Types.optional(TypeProtos.MinorType.INT)),
Pair.of(SchemaPath.getSimplePath("d"), Types.optional(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("s"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("f"), Types.optional(TypeProtos.MinorType.FLOAT4)));
testBuilder()
.sqlQuery(query)
.baselineColumns("e", "p", "d", "s", "f")
.ordered()
.baselineValues(1, 1, 1L, 80_000D, 80_000F)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void mins() throws Exception {
minAndMaxTest("MIN");
}
@Test
public void maxs() throws Exception {
minAndMaxTest("MAX");
}
@Test
public void avgs() throws Exception {
final String query = getAggQuery("AVG");
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("e"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("p"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("d"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("s"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("f"), Types.optional(TypeProtos.MinorType.FLOAT8)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("e", "p", "d", "s", "f")
.baselineValues(1D, 1D, 1D, 80_000D, 80_000D)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void measures() throws Exception {
final String query = "SELECT " +
"STDDEV_SAMP(employee_id) AS s, " +
"STDDEV_POP(position_id) AS p, " +
"AVG(position_id) AS a, " +
"COUNT(position_id) AS c " +
"FROM " + viewName;
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("s"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("p"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("a"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("c"), Types.required(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("s", "p", "a", "c")
.baselineValues(null, 0.0D, 1.0D, 1L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void nullableCount() throws Exception {
final String query = "SELECT " +
"COUNT(CASE WHEN position_id = 1 THEN NULL ELSE position_id END) AS c FROM " + viewName;
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("c"), Types.required(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("c")
.baselineValues(0L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void nullableSumAndCount() throws Exception {
final String query = "SELECT " +
"COUNT(position_id) AS c, " +
"SUM(CAST((CASE WHEN position_id = 1 THEN NULL ELSE position_id END) AS INT)) AS p " +
"FROM " + viewName;
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("c"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("p"), Types.optional(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("c", "p")
.baselineValues(1L, null)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void castSum() throws Exception {
final String query = "SELECT CAST(SUM(position_id) AS INT) AS s FROM cp.`employee.json`";
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("s"), Types.optional(TypeProtos.MinorType.INT)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("s")
.baselineValues(18422)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void sumCast() throws Exception {
final String query = "SELECT SUM(CAST(position_id AS INT)) AS s FROM cp.`employee.json`";
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("s"), Types.optional(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("s")
.baselineValues(18422L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void sumsAndCounts1() throws Exception {
final String query = "SELECT " +
"COUNT(*) as cs, " +
"COUNT(1) as c1, " +
"COUNT(employee_id) as cc, " +
"SUM(1) as s1," +
"department_id " +
" FROM " + viewName + " GROUP BY department_id";
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("cs"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("c1"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("cc"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("s1"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("department_id"), Types.optional(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("cs", "c1", "cc", "s1", "department_id")
.baselineValues(1L, 1L, 1L, 1L, 1L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void sumsAndCounts2() throws Exception {
final String query = "SELECT " +
"SUM(1) as s1, " +
"COUNT(1) as c1, " +
"COUNT(*) as cs, " +
"COUNT(CAST(n_regionkey AS INT)) as cc " +
"FROM cp.`tpch/nation.parquet` " +
"GROUP BY CAST(n_regionkey AS INT)";
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("s1"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("c1"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("cs"), Types.required(TypeProtos.MinorType.BIGINT)),
Pair.of(SchemaPath.getSimplePath("cc"), Types.required(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("s1", "c1", "cs", "cc")
.baselineValues(5L, 5L, 5L, 5L)
.baselineValues(5L, 5L, 5L, 5L)
.baselineValues(5L, 5L, 5L, 5L)
.baselineValues(5L, 5L, 5L, 5L)
.baselineValues(5L, 5L, 5L, 5L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void rank() throws Exception {
final String query = "SELECT RANK() OVER(PARTITION BY employee_id ORDER BY employee_id) AS r FROM " + viewName;
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("r"), Types.required(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.ordered()
.baselineColumns("r")
.baselineValues(1L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
// -------------------- SCALAR FUNC. QUERIES --------------------
@Test
public void cast() throws Exception {
final String query = "SELECT CAST(fsalary AS DOUBLE) AS d," +
"CAST(employee_id AS BIGINT) AS e FROM " + viewName;
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("d"), Types.optional(TypeProtos.MinorType.FLOAT8)),
Pair.of(SchemaPath.getSimplePath("e"), Types.optional(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.baselineColumns("d", "e")
.ordered()
.baselineValues(80_000D, 1L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
public void concatTest(final String query, int precision, boolean isNullable) throws Exception {
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema =
Lists.newArrayList(Pair.of(SchemaPath.getSimplePath("c"), Types.withPrecision(TypeProtos.MinorType.VARCHAR,
isNullable ? TypeProtos.DataMode.OPTIONAL : TypeProtos.DataMode.REQUIRED, precision)));
testBuilder()
.sqlQuery(query)
.baselineColumns("c")
.ordered()
.baselineValues("Sheri NowmerGraduate Degree")
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void concat() throws Exception {
concatTest("SELECT CONCAT(full_name, education_level) AS c FROM " + viewName, 85, false);
}
@Test
public void concatOp() throws Exception {
concatTest("SELECT full_name || education_level AS c FROM " + viewName, 85, true);
}
@Test
public void extract() throws Exception {
final String query = "SELECT EXTRACT(YEAR FROM hire_date) AS e FROM " + viewName;
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("e"), Types.optional(TypeProtos.MinorType.BIGINT)));
testBuilder()
.sqlQuery(query)
.baselineColumns("e")
.ordered()
.baselineValues(1994L)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void binary() throws Exception {
final String query = "SELECT " +
"single AND true AS b, " +
"full_name || education_level AS c, " +
"position_id / position_id AS d, " +
"position_id = position_id AS e, " +
"position_id > position_id AS g, " +
"position_id >= position_id AS ge, " +
"position_id IN (0, 1) AS i, +" +
"position_id < position_id AS l, " +
"position_id <= position_id AS le, " +
"position_id - position_id AS m, " +
"position_id * position_id AS mu, " +
"position_id <> position_id AS n, " +
"single OR false AS o, " +
"position_id + position_id AS p FROM " + viewName;
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("b"), Types.required(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("c"), Types.withPrecision(TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL, 85)),
Pair.of(SchemaPath.getSimplePath("d"), Types.optional(TypeProtos.MinorType.INT)),
Pair.of(SchemaPath.getSimplePath("e"), Types.optional(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("g"), Types.optional(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("ge"), Types.optional(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("i"), Types.optional(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("l"), Types.optional(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("le"), Types.optional(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("m"), Types.optional(TypeProtos.MinorType.INT)),
Pair.of(SchemaPath.getSimplePath("mu"), Types.optional(TypeProtos.MinorType.INT)),
Pair.of(SchemaPath.getSimplePath("n"), Types.optional(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("o"), Types.required(TypeProtos.MinorType.BIT)),
Pair.of(SchemaPath.getSimplePath("p"), Types.optional(TypeProtos.MinorType.INT)));
testBuilder()
.sqlQuery(query)
.baselineColumns("b", "c", "d", "e", "g", "ge", "i", "l", "le", "m", "mu", "n", "o", "p")
.ordered()
.baselineValues(true, "Sheri NowmerGraduate Degree", 1, true, false, true, true, false, true,
0, 1, false, true, 2)
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
public void substringTest(final String query, int precision) throws Exception {
@SuppressWarnings("unchecked")
final List<Pair<SchemaPath, TypeProtos.MajorType>> expectedSchema = Lists.newArrayList(
Pair.of(SchemaPath.getSimplePath("s"), Types.withPrecision(TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL, precision)));
testBuilder()
.sqlQuery(query)
.baselineColumns("s")
.ordered()
.baselineValues("Sheri")
.go();
testBuilder()
.sqlQuery(wrapLimit0(query))
.schemaBaseLine(expectedSchema)
.go();
checkThatQueryPlanIsOptimized(query);
}
@Test
public void substring() throws Exception {
substringTest("SELECT SUBSTRING(full_name, 1, 5) AS s FROM " + viewName, Types.MAX_VARCHAR_LENGTH);
}
@Test
public void substr() throws Exception {
substringTest("SELECT SUBSTR(full_name, 1, 5) AS s FROM " + viewName, Types.MAX_VARCHAR_LENGTH);
}
}