Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
/*
* Licensed 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 io.prestosql.tests;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.ImmutableMultimap;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Maps;
import com.google.common.collect.Multimap;
import com.google.common.collect.Multimaps;
import com.google.common.collect.Ordering;
import io.airlift.tpch.TpchTable;
import io.prestosql.Session;
import io.prestosql.SystemSessionProperties;
import io.prestosql.metadata.FunctionListBuilder;
import io.prestosql.metadata.SqlFunction;
import io.prestosql.spi.session.PropertyMetadata;
import io.prestosql.spi.type.SqlTimestampWithTimeZone;
import io.prestosql.testing.MaterializedResult;
import io.prestosql.testing.MaterializedRow;
import io.prestosql.type.SqlIntervalDayTime;
import io.prestosql.type.SqlIntervalYearMonth;
import org.apache.commons.math3.stat.descriptive.DescriptiveStatistics;
import org.intellij.lang.annotations.Language;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import java.math.BigDecimal;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.HashSet;
import java.util.List;
import java.util.Optional;
import java.util.Set;
import java.util.stream.IntStream;
import static com.google.common.collect.ImmutableSet.toImmutableSet;
import static com.google.common.collect.Iterables.getOnlyElement;
import static io.prestosql.connector.informationschema.InformationSchemaTable.INFORMATION_SCHEMA;
import static io.prestosql.operator.scalar.ApplyFunction.APPLY_FUNCTION;
import static io.prestosql.operator.scalar.InvokeFunction.INVOKE_FUNCTION;
import static io.prestosql.spi.type.BigintType.BIGINT;
import static io.prestosql.spi.type.BooleanType.BOOLEAN;
import static io.prestosql.spi.type.DecimalType.createDecimalType;
import static io.prestosql.spi.type.DoubleType.DOUBLE;
import static io.prestosql.spi.type.VarcharType.VARCHAR;
import static io.prestosql.sql.tree.ExplainType.Type.DISTRIBUTED;
import static io.prestosql.sql.tree.ExplainType.Type.IO;
import static io.prestosql.sql.tree.ExplainType.Type.LOGICAL;
import static io.prestosql.testing.MaterializedResult.resultBuilder;
import static io.prestosql.testing.TestingAccessControlManager.TestingPrivilegeType.CREATE_TABLE;
import static io.prestosql.testing.TestingAccessControlManager.TestingPrivilegeType.DELETE_TABLE;
import static io.prestosql.testing.TestingAccessControlManager.TestingPrivilegeType.INSERT_TABLE;
import static io.prestosql.testing.TestingAccessControlManager.TestingPrivilegeType.SELECT_COLUMN;
import static io.prestosql.testing.TestingAccessControlManager.privilege;
import static io.prestosql.testing.TestingSession.TESTING_CATALOG;
import static io.prestosql.testing.TestngUtils.toDataProvider;
import static io.prestosql.testing.assertions.Assert.assertEquals;
import static io.prestosql.tests.QueryAssertions.assertContains;
import static io.prestosql.tests.QueryAssertions.assertEqualsIgnoreOrder;
import static io.prestosql.tests.QueryTemplate.parameter;
import static io.prestosql.tests.QueryTemplate.queryTemplate;
import static io.prestosql.tests.StatefulSleepingSum.STATEFUL_SLEEPING_SUM;
import static io.prestosql.type.UnknownType.UNKNOWN;
import static java.lang.String.format;
import static java.util.stream.Collectors.joining;
import static java.util.stream.Collectors.toList;
import static java.util.stream.IntStream.range;
import static org.assertj.core.api.Assertions.assertThat;
import static org.testng.Assert.assertFalse;
import static org.testng.Assert.assertNotEquals;
import static org.testng.Assert.assertTrue;
public abstract class AbstractTestQueries
extends AbstractTestQueryFramework
{
// We can just use the default type registry, since we don't use any parametric types
protected static final List CUSTOM_FUNCTIONS = new FunctionListBuilder()
.aggregates(CustomSum.class)
.window(CustomRank.class)
.scalars(CustomAdd.class)
.scalars(CreateHll.class)
.functions(APPLY_FUNCTION, INVOKE_FUNCTION, STATEFUL_SLEEPING_SUM)
.getFunctions();
public static final List> TEST_SYSTEM_PROPERTIES = ImmutableList.of(
PropertyMetadata.stringProperty(
"test_string",
"test string property",
"test default",
false),
PropertyMetadata.longProperty(
"test_long",
"test long property",
42L,
false));
public static final List> TEST_CATALOG_PROPERTIES = ImmutableList.of(
PropertyMetadata.stringProperty(
"connector_string",
"connector string property",
"connector default",
false),
PropertyMetadata.longProperty(
"connector_long",
"connector long property",
33L,
false),
PropertyMetadata.booleanProperty(
"connector_boolean",
"connector boolean property",
true,
false),
PropertyMetadata.doubleProperty(
"connector_double",
"connector double property",
99.0,
false));
private static final DateTimeFormatter ZONED_DATE_TIME_FORMAT = DateTimeFormatter.ofPattern(SqlTimestampWithTimeZone.JSON_FORMAT);
private static final String UNSUPPORTED_CORRELATED_SUBQUERY_ERROR_MSG = "line .*: Given correlated subquery is not supported";
protected AbstractTestQueries(QueryRunnerSupplier supplier)
{
super(supplier);
}
@Test
public void testParsingError()
{
assertQueryFails("SELECT foo FROM", "line 1:16: mismatched input ''. Expecting: .*");
}
@Test
public void selectLargeInterval()
{
MaterializedResult result = computeActual("SELECT INTERVAL '30' DAY");
assertEquals(result.getRowCount(), 1);
assertEquals(result.getMaterializedRows().get(0).getField(0), new SqlIntervalDayTime(30, 0, 0, 0, 0));
result = computeActual("SELECT INTERVAL '" + Short.MAX_VALUE + "' YEAR");
assertEquals(result.getRowCount(), 1);
assertEquals(result.getMaterializedRows().get(0).getField(0), new SqlIntervalYearMonth(Short.MAX_VALUE, 0));
}
@Test
public void selectNull()
{
assertQuery("SELECT NULL");
}
@Test
public void testAggregationOverUnknown()
{
assertQuery("SELECT clerk, min(totalprice), max(totalprice), min(nullvalue), max(nullvalue) " +
"FROM (SELECT clerk, totalprice, null AS nullvalue FROM orders) " +
"GROUP BY clerk");
}
@Test
public void testLimitIntMax()
{
assertQuery("SELECT orderkey FROM orders LIMIT " + Integer.MAX_VALUE);
assertQuery("SELECT orderkey FROM orders ORDER BY orderkey LIMIT " + Integer.MAX_VALUE);
}
@Test
public void testNonDeterministic()
{
MaterializedResult materializedResult = computeActual("SELECT rand() FROM orders LIMIT 10");
long distinctCount = materializedResult.getMaterializedRows().stream()
.map(row -> row.getField(0))
.distinct()
.count();
assertTrue(distinctCount >= 8, "rand() must produce different rows");
materializedResult = computeActual("SELECT apply(1, x -> x + rand()) FROM orders LIMIT 10");
distinctCount = materializedResult.getMaterializedRows().stream()
.map(row -> row.getField(0))
.distinct()
.count();
assertTrue(distinctCount >= 8, "rand() must produce different rows");
}
@Test
public void testLambdaCapture()
{
// Test for lambda expression without capture can be found in TestLambdaExpression
assertQuery("SELECT apply(0, x -> x + c1) FROM (VALUES 1) t(c1)", "VALUES 1");
assertQuery("SELECT apply(0, x -> x + t.c1) FROM (VALUES 1) t(c1)", "VALUES 1");
assertQuery("SELECT apply(c1, x -> x + c2) FROM (VALUES (1, 2), (3, 4), (5, 6)) t(c1, c2)", "VALUES 3, 7, 11");
assertQuery("SELECT apply(c1 + 10, x -> apply(x + 100, y -> c1)) FROM (VALUES 1) t(c1)", "VALUES 1");
assertQuery("SELECT apply(c1 + 10, x -> apply(x + 100, y -> t.c1)) FROM (VALUES 1) t(c1)", "VALUES 1");
assertQuery("SELECT apply(CAST(ROW(10) AS ROW(x INTEGER)), r -> r.x)", "VALUES 10");
assertQuery("SELECT apply(CAST(ROW(10) AS ROW(x INTEGER)), r -> r.x) FROM (VALUES 1) u(x)", "VALUES 10");
assertQuery("SELECT apply(CAST(ROW(10) AS ROW(x INTEGER)), r -> r.x) FROM (VALUES 1) r(x)", "VALUES 10");
assertQuery("SELECT apply(CAST(ROW(10) AS ROW(x INTEGER)), r -> apply(3, y -> y + r.x)) FROM (VALUES 1) u(x)", "VALUES 13");
assertQuery("SELECT apply(CAST(ROW(10) AS ROW(x INTEGER)), r -> apply(3, y -> y + r.x)) FROM (VALUES 1) r(x)", "VALUES 13");
assertQuery("SELECT apply(CAST(ROW(10) AS ROW(x INTEGER)), r -> apply(3, y -> y + r.x)) FROM (VALUES 'a') r(x)", "VALUES 13");
assertQuery("SELECT apply(CAST(ROW(10) AS ROW(x INTEGER)), z -> apply(3, y -> y + r.x)) FROM (VALUES 1) r(x)", "VALUES 4");
// reference lambda variable of the not-immediately-enclosing lambda
assertQuery("SELECT apply(1, x -> apply(10, y -> x)) FROM (VALUES 1000) t(x)", "VALUES 1");
assertQuery("SELECT apply(1, x -> apply(10, y -> x)) FROM (VALUES 'abc') t(x)", "VALUES 1");
assertQuery("SELECT apply(1, x -> apply(10, y -> apply(100, z -> x))) FROM (VALUES 1000) t(x)", "VALUES 1");
assertQuery("SELECT apply(1, x -> apply(10, y -> apply(100, z -> x))) FROM (VALUES 'abc') t(x)", "VALUES 1");
// in join post-filter
assertQuery("SELECT * FROM (VALUES true) t(x) left JOIN (VALUES 1001) t2(y) ON (apply(false, z -> apply(false, y -> x)))", "SELECT true, 1001");
}
@Test
public void testLambdaInAggregationContext()
{
assertQuery("SELECT apply(sum(x), i -> i * i) FROM (VALUES 1, 2, 3, 4, 5) t(x)", "SELECT 225");
assertQuery("SELECT apply(x, i -> i - 1), sum(y) FROM (VALUES (1, 10), (1, 20), (2, 50)) t(x,y) GROUP BY x", "VALUES (0, 30), (1, 50)");
assertQuery("SELECT x, apply(sum(y), i -> i * 10) FROM (VALUES (1, 10), (1, 20), (2, 50)) t(x,y) GROUP BY x", "VALUES (1, 300), (2, 500)");
assertQuery("SELECT apply(8, x -> x + 1) FROM (VALUES (1, 2)) t(x,y) GROUP BY y", "SELECT 9");
assertQuery("SELECT apply(CAST(ROW(1) AS ROW(someField BIGINT)), x -> x.someField) FROM (VALUES (1,2)) t(x,y) GROUP BY y", "SELECT 1");
assertQuery("SELECT apply(sum(x), x -> x * x) FROM (VALUES 1, 2, 3, 4, 5) t(x)", "SELECT 225");
// nested lambda expression uses the same variable name
assertQuery("SELECT apply(sum(x), x -> apply(x, x -> x * x)) FROM (VALUES 1, 2, 3, 4, 5) t(x)", "SELECT 225");
}
@Test
public void testLambdaInSubqueryContext()
{
assertQuery("SELECT apply(x, i -> i * i) FROM (SELECT 10 x)", "SELECT 100");
assertQuery("SELECT apply((SELECT 10), i -> i * i)", "SELECT 100");
// with capture
assertQuery("SELECT apply(x, i -> i * x) FROM (SELECT 10 x)", "SELECT 100");
assertQuery("SELECT apply(x, y -> y * x) FROM (SELECT 10 x, 3 y)", "SELECT 100");
assertQuery("SELECT apply(x, z -> y * x) FROM (SELECT 10 x, 3 y)", "SELECT 30");
}
@Test
public void testLambdaInValuesAndUnnest()
{
assertQuery("SELECT * FROM UNNEST(transform(sequence(1, 5), x -> x * x))", "SELECT * FROM (VALUES 1, 4, 9, 16, 25)");
assertQuery("SELECT x[5] FROM (VALUES transform(sequence(1, 5), x -> x * x)) t(x)", "SELECT 25");
}
@Test
public void testTryLambdaRepeated()
{
assertQuery("SELECT x + x FROM (SELECT apply(a, i -> i * i) x FROM (VALUES 3) t(a))", "SELECT 18");
assertQuery("SELECT apply(a, i -> i * i) + apply(a, i -> i * i) FROM (VALUES 3) t(a)", "SELECT 18");
assertQuery("SELECT apply(a, i -> i * i), apply(a, i -> i * i) FROM (VALUES 3) t(a)", "SELECT 9, 9");
assertQuery("SELECT try(10 / a) + try(10 / a) FROM (VALUES 5) t(a)", "SELECT 4");
assertQuery("SELECT try(10 / a), try(10 / a) FROM (VALUES 5) t(a)", "SELECT 2, 2");
}
@Test
public void testNonDeterministicFilter()
{
MaterializedResult materializedResult = computeActual("SELECT u FROM ( SELECT if(rand() > 0.5, 0, 1) AS u ) WHERE u <> u");
assertEquals(materializedResult.getRowCount(), 0);
materializedResult = computeActual("SELECT u, v FROM ( SELECT if(rand() > 0.5, 0, 1) AS u, 4*4 AS v ) WHERE u <> u and v > 10");
assertEquals(materializedResult.getRowCount(), 0);
materializedResult = computeActual("SELECT u, v, w FROM ( SELECT if(rand() > 0.5, 0, 1) AS u, 4*4 AS v, 'abc' AS w ) WHERE v > 10");
assertEquals(materializedResult.getRowCount(), 1);
}
@Test
public void testNonDeterministicProjection()
{
MaterializedResult materializedResult = computeActual("SELECT r, r + 1 FROM (SELECT rand(100) r FROM orders) LIMIT 10");
assertEquals(materializedResult.getRowCount(), 10);
for (MaterializedRow materializedRow : materializedResult) {
assertEquals(materializedRow.getFieldCount(), 2);
assertEquals(((Number) materializedRow.getField(0)).intValue() + 1, materializedRow.getField(1));
}
}
@Test
public void testMapSubscript()
{
assertQuery("SELECT map(array[1], array['aa'])[1]", "SELECT 'aa'");
assertQuery("SELECT map(array['a'], array['aa'])['a']", "SELECT 'aa'");
assertQuery("SELECT map(array[array[1,1]], array['a'])[array[1,1]]", "SELECT 'a'");
assertQuery("SELECT map(array[(1,2)], array['a'])[(1,2)]", "SELECT 'a'");
}
@Test
public void testRowSubscript()
{
// Subscript on Row with unnamed fields
assertQuery("SELECT ROW (1, 'a', true)[2]", "SELECT 'a'");
assertQuery("SELECT r[2] FROM (VALUES (ROW (ROW (1, 'a', true)))) AS v(r)", "SELECT 'a'");
assertQuery("SELECT r[1], r[2] FROM (SELECT ROW (name, regionkey) FROM nation ORDER BY name LIMIT 1) t(r)", "VALUES ('ALGERIA', 0)");
// Subscript on Row with named fields
assertQuery("SELECT (CAST (ROW (1, 'a', 2 ) AS ROW (field1 bigint, field2 varchar(1), field3 bigint)))[2]", "SELECT 'a'");
// Subscript on nested Row
assertQuery("SELECT ROW (1, 'a', ROW (false, 2, 'b'))[3][3]", "SELECT 'b'");
// Row subscript in filter condition
assertQuery("SELECT orderstatus FROM orders WHERE ROW (orderkey, custkey)[1] = 100", "SELECT 'O'");
// Row subscript in join condition
assertQuery("SELECT n.name, r.name FROM nation n JOIN region r ON ROW (n.name, n.regionkey)[2] = ROW (r.name, r.regionkey)[2] ORDER BY n.name LIMIT 1", "VALUES ('ALGERIA', 'AFRICA')");
}
@Test
public void testVarbinary()
{
assertQuery("SELECT LENGTH(x) FROM (SELECT from_base64('gw==') AS x)", "SELECT 1");
assertQuery("SELECT LENGTH(from_base64('gw=='))", "SELECT 1");
}
@Test
public void testRowFieldAccessor()
{
//Dereference only
assertQuery("SELECT a.col0 FROM (VALUES ROW (CAST(ROW(1, 2) AS ROW(col0 integer, col1 integer)))) AS t (a)", "SELECT 1");
assertQuery("SELECT a.col0 FROM (VALUES ROW (CAST(ROW(1.0E0, 2.0E0) AS ROW(col0 integer, col1 integer)))) AS t (a)", "SELECT 1.0");
assertQuery("SELECT a.col0 FROM (VALUES ROW (CAST(ROW(TRUE, FALSE) AS ROW(col0 boolean, col1 boolean)))) AS t (a)", "SELECT TRUE");
assertQuery("SELECT a.col1 FROM (VALUES ROW (CAST(ROW(1.0, 'kittens') AS ROW(col0 varchar, col1 varchar)))) AS t (a)", "SELECT 'kittens'");
assertQuery("SELECT a.col2.col1 FROM (VALUES ROW(CAST(ROW(1.0, ARRAY[2], row(3, 4.0)) AS ROW(col0 double, col1 array(int), col2 row(col0 integer, col1 double))))) t(a)", "SELECT 4.0");
// mixture of row field reference and table field reference
assertQuery("SELECT CAST(row(1, t.x) AS row(col0 bigint, col1 bigint)).col1 FROM (VALUES 1, 2, 3) t(x)", "SELECT * FROM (VALUES 1, 2, 3)");
assertQuery("SELECT Y.col1 FROM (SELECT CAST(row(1, t.x) AS row(col0 bigint, col1 bigint)) AS Y FROM (VALUES 1, 2, 3) t(x)) test_t", "SELECT * FROM (VALUES 1, 2, 3)");
// Subscript + Dereference
assertQuery("SELECT a.col1[2] FROM (VALUES ROW(CAST(ROW(1.0, ARRAY[22, 33, 44, 55], row(3, 4.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double))))) t(a)", "SELECT 33");
assertQuery("SELECT a.col1[2].col0, a.col1[2].col1 FROM (VALUES ROW(cast(row(1.0, ARRAY[row(31, 4.1E0), row(32, 4.2E0)], row(3, 4.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double))))) t(a)", "SELECT 32, 4.2");
assertQuery("SELECT CAST(row(11, 12) AS row(col0 bigint, col1 bigint)).col0", "SELECT 11");
}
@Test
public void testRowFieldAccessorInAggregate()
{
assertQuery("SELECT a.col0, SUM(a.col1[2]), SUM(a.col2.col0), SUM(a.col2.col1) FROM " +
"(VALUES " +
"ROW(CAST(ROW(1.0, ARRAY[2, 13, 4], row(11, 4.1E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(2.0, ARRAY[2, 23, 4], row(12, 14.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(1.0, ARRAY[22, 33, 44], row(13, 5.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double))))) t(a) " +
"GROUP BY a.col0",
"SELECT * FROM VALUES (1.0, 46, 24, 9.1), (2.0, 23, 12, 14.0)");
assertQuery("SELECT a.col2.col0, SUM(a.col0), SUM(a.col1[2]), SUM(a.col2.col1) FROM " +
"(VALUES " +
"ROW(CAST(ROW(1.0, ARRAY[2, 13, 4], row(11, 4.1E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(2.0, ARRAY[2, 23, 4], row(11, 14.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(7.0, ARRAY[22, 33, 44], row(13, 5.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double))))) t(a) " +
"GROUP BY a.col2.col0",
"SELECT * FROM VALUES (11, 3.0, 36, 18.1), (13, 7.0, 33, 5.0)");
assertQuery("SELECT a.col1[1].col0, SUM(a.col0), SUM(a.col1[1].col1), SUM(a.col1[2].col0), SUM(a.col2.col1) FROM " +
"(VALUES " +
"ROW(CAST(ROW(1.0, ARRAY[row(31, 4.5E0), row(12, 4.2E0)], row(3, 4.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(3.1, ARRAY[row(41, 3.1E0), row(32, 4.2E0)], row(6, 6.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(2.2, ARRAY[row(31, 4.2E0), row(22, 4.2E0)], row(5, 4.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double))))) t(a) " +
"GROUP BY a.col1[1].col0",
"SELECT * FROM VALUES (31, 3.2, 8.7, 34, 8.0), (41, 3.1, 3.1, 32, 6.0)");
assertQuery("SELECT a.col1[1].col0, SUM(a.col0), SUM(a.col1[1].col1), SUM(a.col1[2].col0), SUM(a.col2.col1) FROM " +
"(VALUES " +
"ROW(CAST(ROW(2.2, ARRAY[row(31, 4.2E0), row(22, 4.2E0)], row(5, 4.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(1.0, ARRAY[row(31, 4.5E0), row(12, 4.2E0)], row(3, 4.1E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(3.1, ARRAY[row(41, 3.1E0), row(32, 4.2E0)], row(6, 6.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(3.3, ARRAY[row(41, 3.1E0), row(32, 4.2E0)], row(6, 6.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))) " +
") t(a) " +
"GROUP BY a.col1[1]",
"SELECT * FROM VALUES (31, 2.2, 4.2, 22, 4.0), (31, 1.0, 4.5, 12, 4.1), (41, 6.4, 6.2, 64, 12.0)");
assertQuery("SELECT a.col1[2], SUM(a.col0), SUM(a.col1[1]), SUM(a.col2.col1) FROM " +
"(VALUES " +
"ROW(CAST(ROW(1.0, ARRAY[2, 13, 4], row(11, 4.1E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(2.0, ARRAY[2, 13, 4], row(12, 14.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(7.0, ARRAY[22, 33, 44], row(13, 5.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double))))) t(a) " +
"GROUP BY a.col1[2]",
"SELECT * FROM VALUES (13, 3.0, 4, 18.1), (33, 7.0, 22, 5.0)");
assertQuery("SELECT a.col2.col0, SUM(a.col2.col1) FROM " +
"(VALUES " +
"ROW(CAST(ROW(2.2, ARRAY[row(31, 4.2E0), row(22, 4.2E0)], row(5, 4.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(1.0, ARRAY[row(31, 4.5E0), row(12, 4.2E0)], row(3, 4.1E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(3.1, ARRAY[row(41, 3.1E0), row(32, 4.2E0)], row(6, 6.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(3.3, ARRAY[row(41, 3.1E0), row(32, 4.2E0)], row(6, 6.0E0)) AS ROW(col0 double, col1 array(row(col0 integer, col1 double)), col2 row(col0 integer, col1 double)))) " +
") t(a) " +
"GROUP BY a.col2",
"SELECT * FROM VALUES (5, 4.0), (3, 4.1), (6, 12.0)");
assertQuery("SELECT a.col2.col0, a.col0, SUM(a.col2.col1) FROM " +
"(VALUES " +
"ROW(CAST(ROW(1.0, ARRAY[2, 13, 4], row(11, 4.1E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(2.0, ARRAY[2, 23, 4], row(11, 14.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(1.5, ARRAY[2, 13, 4], row(11, 4.1E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(1.5, ARRAY[2, 13, 4], row(11, 4.1E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double)))), " +
"ROW(CAST(ROW(7.0, ARRAY[22, 33, 44], row(13, 5.0E0)) AS ROW(col0 double, col1 array(integer), col2 row(col0 integer, col1 double))))) t(a) " +
"WHERE a.col1[2] < 30 " +
"GROUP BY 1, 2 ORDER BY 1",
"SELECT * FROM VALUES (11, 1.0, 4.1), (11, 1.5, 8.2), (11, 2.0, 14.0)");
assertQuery("SELECT a[1].col0, COUNT(1) FROM " +
"(VALUES " +
"(ROW(CAST(ARRAY[row(31, 4.2E0), row(22, 4.2E0)] AS ARRAY(ROW(col0 integer, col1 double))))), " +
"(ROW(CAST(ARRAY[row(31, 4.5E0), row(12, 4.2E0)] AS ARRAY(ROW(col0 integer, col1 double))))), " +
"(ROW(CAST(ARRAY[row(41, 3.1E0), row(32, 4.2E0)] AS ARRAY(ROW(col0 integer, col1 double))))), " +
"(ROW(CAST(ARRAY[row(31, 3.1E0), row(32, 4.2E0)] AS ARRAY(ROW(col0 integer, col1 double))))) " +
") t(a) " +
"GROUP BY 1 " +
"ORDER BY 2 DESC",
"SELECT * FROM VALUES (31, 3), (41, 1)");
}
@Test
public void testRowFieldAccessorInJoin()
{
assertQuery("" +
"SELECT t.a.col1, custkey, orderkey FROM " +
"(VALUES " +
"ROW(CAST(ROW(1, 11) AS ROW(col0 integer, col1 integer))), " +
"ROW(CAST(ROW(2, 22) AS ROW(col0 integer, col1 integer))), " +
"ROW(CAST(ROW(3, 33) AS ROW(col0 integer, col1 integer)))) t(a) " +
"INNER JOIN orders " +
"ON t.a.col0 = orders.orderkey",
"SELECT * FROM VALUES (11, 370, 1), (22, 781, 2), (33, 1234, 3)");
}
@Test
public void testRowCast()
{
assertQuery("SELECT CAST(row(1, 2) AS row(aa bigint, bb boolean)).aa", "SELECT 1");
assertQuery("SELECT CAST(row(1, 2) AS row(aa bigint, bb boolean)).bb", "SELECT true");
assertQuery("SELECT CAST(row(1, 2) AS row(aa bigint, bb varchar)).bb", "SELECT '2'");
assertQuery("SELECT CAST(row(true, array[0, 2]) AS row(aa boolean, bb array(boolean))).bb[1]", "SELECT false");
assertQuery("SELECT CAST(row(0.1, array[0, 2], row(1, 0.5)) AS row(aa bigint, bb array(boolean), cc row(dd varchar, ee varchar))).cc.ee", "SELECT '0.5'");
assertQuery("SELECT CAST(array[row(0.1, array[0, 2], row(1, 0.5))] AS array)[1].cc.ee", "SELECT '0.5'");
}
@Test
public void testDereferenceInSubquery()
{
assertQuery("" +
"SELECT x " +
"FROM (" +
" SELECT a.x" +
" FROM (VALUES 1, 2, 3) a(x)" +
") " +
"GROUP BY x",
"SELECT * FROM VALUES 1, 2, 3");
assertQuery("" +
"SELECT t2.*, max(t1.b) AS max_b " +
"FROM (VALUES (1, 'a'), (2, 'b'), (1, 'c'), (3, 'd')) t1(a, b) " +
"INNER JOIN " +
"(VALUES 1, 2, 3, 4) t2(a) " +
"ON t1.a = t2.a " +
"GROUP BY t2.a",
"SELECT * FROM VALUES (1, 'c'), (2, 'b'), (3, 'd')");
assertQuery("" +
"SELECT t2.*, max(t1.b1) AS max_b1 " +
"FROM (VALUES (1, 'a'), (2, 'b'), (1, 'c'), (3, 'd')) t1(a1, b1) " +
"INNER JOIN " +
"(VALUES (1, 11, 111), (2, 22, 222), (3, 33, 333), (4, 44, 444)) t2(a2, b2, c2) " +
"ON t1.a1 = t2.a2 " +
"GROUP BY t2.a2, t2.b2, t2.c2",
"SELECT * FROM VALUES (1, 11, 111, 'c'), (2, 22, 222, 'b'), (3, 33, 333, 'd')");
assertQuery("" +
"SELECT custkey, orders2 " +
"FROM (" +
" SELECT x.custkey, SUM(x.orders) + 1 orders2 " +
" FROM ( " +
" SELECT x.custkey, COUNT(x.orderkey) orders " +
" FROM orders x " +
" WHERE x.custkey < 100 " +
" GROUP BY x.custkey " +
" ) x " +
" GROUP BY x.custkey" +
") " +
"ORDER BY custkey");
}
@Test
public void testDereferenceInFunctionCall()
{
assertQuery("" +
"SELECT COUNT(DISTINCT custkey) " +
"FROM ( " +
" SELECT x.custkey " +
" FROM orders x " +
" WHERE custkey < 100 " +
") t");
}
@Test
public void testDereferenceInComparison()
{
assertQuery("" +
"SELECT orders.custkey, orders.orderkey " +
"FROM orders " +
"WHERE orders.custkey > orders.orderkey AND orders.custkey < 200.3");
}
@Test
public void testMissingRowFieldInGroupBy()
{
assertQueryFails(
"SELECT a.col0, count(*) FROM (VALUES ROW(cast(ROW(1, 1) AS ROW(col0 integer, col1 integer)))) t(a)",
"line 1:8: 'a.col0' must be an aggregate expression or appear in GROUP BY clause");
}
@Test
public void testWhereWithRowField()
{
assertQuery("SELECT a.col0 FROM (VALUES ROW(CAST(ROW(1, 2) AS ROW(col0 integer, col1 integer)))) AS t (a) WHERE a.col0 > 0", "SELECT 1");
assertQuery("SELECT SUM(a.col0) FROM (VALUES ROW(CAST(ROW(1, 2) AS ROW(col0 integer, col1 integer)))) AS t (a) WHERE a.col0 <= 0", "SELECT null");
assertQuery("SELECT a.col0 FROM (VALUES ROW(CAST(ROW(1, 2) AS ROW(col0 integer, col1 integer)))) AS t (a) WHERE a.col0 < a.col1", "SELECT 1");
assertQuery("SELECT SUM(a.col0) FROM (VALUES ROW(CAST(ROW(1, 2) AS ROW(col0 integer, col1 integer)))) AS t (a) WHERE a.col0 < a.col1", "SELECT 1");
assertQuery("SELECT SUM(a.col0) FROM (VALUES ROW(CAST(ROW(1, 2) AS ROW(col0 integer, col1 integer)))) AS t (a) WHERE a.col0 > a.col1", "SELECT null");
}
@Test
public void testUnnest()
{
assertQuery("SELECT 1 FROM (VALUES (ARRAY[1])) AS t (a) CROSS JOIN UNNEST(a)", "SELECT 1");
assertQuery("SELECT x[1] FROM UNNEST(ARRAY[ARRAY[1, 2, 3]]) t(x)", "SELECT 1");
assertQuery("SELECT x[1][2] FROM UNNEST(ARRAY[ARRAY[ARRAY[1, 2, 3]]]) t(x)", "SELECT 2");
assertQuery("SELECT x[2] FROM UNNEST(ARRAY[MAP(ARRAY[1,2], ARRAY['hello', 'hi'])]) t(x)", "SELECT 'hi'");
assertQuery("SELECT * FROM UNNEST(ARRAY[1, 2, 3])", "SELECT * FROM VALUES (1), (2), (3)");
assertQuery("SELECT a FROM UNNEST(ARRAY[1, 2, 3]) t(a)", "SELECT * FROM VALUES (1), (2), (3)");
assertQuery("SELECT a, b FROM UNNEST(ARRAY[1, 2], ARRAY[3, 4]) t(a, b)", "SELECT * FROM VALUES (1, 3), (2, 4)");
assertQuery("SELECT a, b FROM UNNEST(ARRAY[1, 2, 3], ARRAY[4, 5]) t(a, b)", "SELECT * FROM VALUES (1, 4), (2, 5), (3, NULL)");
assertQuery("SELECT a FROM UNNEST(ARRAY[1, 2, 3], ARRAY[4, 5]) t(a, b)", "SELECT * FROM VALUES 1, 2, 3");
assertQuery("SELECT b FROM UNNEST(ARRAY[1, 2, 3], ARRAY[4, 5]) t(a, b)", "SELECT * FROM VALUES 4, 5, NULL");
assertQuery("SELECT count(*) FROM UNNEST(ARRAY[1, 2, 3], ARRAY[4, 5])", "SELECT 3");
assertQuery("SELECT a FROM UNNEST(ARRAY['kittens', 'puppies']) t(a)", "SELECT * FROM VALUES ('kittens'), ('puppies')");
assertQuery("" +
"SELECT c " +
"FROM UNNEST(ARRAY[1, 2, 3], ARRAY[4, 5]) t(a, b) " +
"CROSS JOIN (values (8), (9)) t2(c)",
"SELECT * FROM VALUES 8, 8, 8, 9, 9, 9");
assertQuery("" +
"SELECT a.custkey, t.e " +
"FROM (SELECT custkey, ARRAY[1, 2, 3] AS my_array FROM orders ORDER BY orderkey LIMIT 1) a " +
"CROSS JOIN UNNEST(my_array) t(e)",
"SELECT * FROM (SELECT custkey FROM orders ORDER BY orderkey LIMIT 1) CROSS JOIN (VALUES (1), (2), (3))");
assertQuery("" +
"SELECT a.custkey, t.e " +
"FROM (SELECT custkey, ARRAY[1, 2, 3] AS my_array FROM orders ORDER BY orderkey LIMIT 1) a, " +
"UNNEST(my_array) t(e)",
"SELECT * FROM (SELECT custkey FROM orders ORDER BY orderkey LIMIT 1) CROSS JOIN (VALUES (1), (2), (3))");
assertQuery("SELECT * FROM UNNEST(ARRAY[0, 1]) CROSS JOIN UNNEST(ARRAY[0, 1]) CROSS JOIN UNNEST(ARRAY[0, 1])",
"SELECT * FROM VALUES (0, 0, 0), (0, 0, 1), (0, 1, 0), (0, 1, 1), (1, 0, 0), (1, 0, 1), (1, 1, 0), (1, 1, 1)");
assertQuery("SELECT * FROM UNNEST(ARRAY[0, 1]), UNNEST(ARRAY[0, 1]), UNNEST(ARRAY[0, 1])",
"SELECT * FROM VALUES (0, 0, 0), (0, 0, 1), (0, 1, 0), (0, 1, 1), (1, 0, 0), (1, 0, 1), (1, 1, 0), (1, 1, 1)");
assertQuery("SELECT a, b FROM UNNEST(MAP(ARRAY[1,2], ARRAY['cat', 'dog'])) t(a, b)", "SELECT * FROM VALUES (1, 'cat'), (2, 'dog')");
assertQuery("SELECT a, b FROM UNNEST(MAP(ARRAY[1,2], ARRAY['cat', NULL])) t(a, b)", "SELECT * FROM VALUES (1, 'cat'), (2, NULL)");
assertQuery("SELECT 1 FROM (VALUES (ARRAY[1])) AS t (a) CROSS JOIN UNNEST(a) WITH ORDINALITY", "SELECT 1");
assertQuery("SELECT * FROM UNNEST(ARRAY[1, 2, 3]) WITH ORDINALITY", "SELECT * FROM VALUES (1, 1), (2, 2), (3, 3)");
assertQuery("SELECT b FROM UNNEST(ARRAY[10, 20, 30]) WITH ORDINALITY t(a, b)", "SELECT * FROM VALUES (1), (2), (3)");
assertQuery("SELECT a, b, c FROM UNNEST(ARRAY[10, 20, 30], ARRAY[4, 5]) WITH ORDINALITY t(a, b, c)", "SELECT * FROM VALUES (10, 4, 1), (20, 5, 2), (30, NULL, 3)");
assertQuery("SELECT a, b FROM UNNEST(ARRAY['kittens', 'puppies']) WITH ORDINALITY t(a, b)", "SELECT * FROM VALUES ('kittens', 1), ('puppies', 2)");
assertQuery("" +
"SELECT c " +
"FROM UNNEST(ARRAY[1, 2, 3], ARRAY[4, 5]) WITH ORDINALITY t(a, b, c) " +
"CROSS JOIN (values (8), (9)) t2(d)",
"SELECT * FROM VALUES 1, 1, 2, 2, 3, 3");
assertQuery("" +
"SELECT a.custkey, t.e, t.f " +
"FROM (SELECT custkey, ARRAY[10, 20, 30] AS my_array FROM orders ORDER BY orderkey LIMIT 1) a " +
"CROSS JOIN UNNEST(my_array) WITH ORDINALITY t(e, f)",
"SELECT * FROM (SELECT custkey FROM orders ORDER BY orderkey LIMIT 1) CROSS JOIN (VALUES (10, 1), (20, 2), (30, 3))");
assertQuery("" +
"SELECT a.custkey, t.e, t.f " +
"FROM (SELECT custkey, ARRAY[10, 20, 30] AS my_array FROM orders ORDER BY orderkey LIMIT 1) a, " +
"UNNEST(my_array) WITH ORDINALITY t(e, f)",
"SELECT * FROM (SELECT custkey FROM orders ORDER BY orderkey LIMIT 1) CROSS JOIN (VALUES (10, 1), (20, 2), (30, 3))");
assertQuery("SELECT * FROM orders, UNNEST(ARRAY[1])", "SELECT orders.*, 1 FROM orders");
}
@Test
public void testArrays()
{
assertQuery("SELECT a[1] FROM (SELECT ARRAY[orderkey] AS a FROM orders ORDER BY orderkey) t", "SELECT orderkey FROM orders");
assertQuery("SELECT a[1 + CAST(round(rand()) AS BIGINT)] FROM (SELECT ARRAY[orderkey, orderkey] AS a FROM orders ORDER BY orderkey) t", "SELECT orderkey FROM orders");
assertQuery("SELECT a[1] + 1 FROM (SELECT ARRAY[orderkey] AS a FROM orders ORDER BY orderkey) t", "SELECT orderkey + 1 FROM orders");
assertQuery("SELECT a[1] FROM (SELECT ARRAY[orderkey + 1] AS a FROM orders ORDER BY orderkey) t", "SELECT orderkey + 1 FROM orders");
assertQuery("SELECT a[1][1] FROM (SELECT ARRAY[ARRAY[orderkey + 1]] AS a FROM orders ORDER BY orderkey) t", "SELECT orderkey + 1 FROM orders");
assertQuery("SELECT CARDINALITY(a) FROM (SELECT ARRAY[orderkey, orderkey + 1] AS a FROM orders ORDER BY orderkey) t", "SELECT 2 FROM orders");
}
@Test
public void testArrayAgg()
{
assertQuery("SELECT clerk, cardinality(array_agg(orderkey)) FROM orders GROUP BY clerk", "SELECT clerk, count(*) FROM orders GROUP BY clerk");
}
@Test
public void testReduceAgg()
{
assertQuery(
"SELECT x, reduce_agg(y, 1, (a, b) -> a * b, (a, b) -> a * b) " +
"FROM (VALUES (1, 5), (1, 6), (1, 7), (2, 8), (2, 9), (3, 10)) AS t(x, y) " +
"GROUP BY x",
"VALUES (1, 5 * 6 * 7), (2, 8 * 9), (3, 10)");
assertQuery(
"SELECT x, reduce_agg(y, 0, (a, b) -> a + b, (a, b) -> a + b) " +
"FROM (VALUES (1, 5), (1, 6), (1, 7), (2, 8), (2, 9), (3, 10)) AS t(x, y) " +
"GROUP BY x",
"VALUES (1, 5 + 6 + 7), (2, 8 + 9), (3, 10)");
assertQuery(
"SELECT x, reduce_agg(y, 1, (a, b) -> a * b, (a, b) -> a * b) " +
"FROM (VALUES (1, CAST(5 AS DOUBLE)), (1, 6), (1, 7), (2, 8), (2, 9), (3, 10)) AS t(x, y) " +
"GROUP BY x",
"VALUES (1, CAST(5 AS DOUBLE) * 6 * 7), (2, 8 * 9), (3, 10)");
assertQuery(
"SELECT x, reduce_agg(y, 0, (a, b) -> a + b, (a, b) -> a + b) " +
"FROM (VALUES (1, CAST(5 AS DOUBLE)), (1, 6), (1, 7), (2, 8), (2, 9), (3, 10)) AS t(x, y) " +
"GROUP BY x",
"VALUES (1, CAST(5 AS DOUBLE) + 6 + 7), (2, 8 + 9), (3, 10)");
}
@Test
public void testRows()
{
// Using JSON_FORMAT(CAST(_ AS JSON)) because H2 does not support ROW type
assertQuery("SELECT JSON_FORMAT(CAST(ROW(1 + 2, CONCAT('a', 'b')) AS JSON))", "SELECT '[3,\"ab\"]'");
assertQuery("SELECT JSON_FORMAT(CAST(ROW(a + b) AS JSON)) FROM (VALUES (1, 2)) AS t(a, b)", "SELECT '[3]'");
assertQuery("SELECT JSON_FORMAT(CAST(ROW(1, ROW(9, a, ARRAY[], NULL), ROW(1, 2)) AS JSON)) FROM (VALUES ('a')) t(a)", "SELECT '[1,[9,\"a\",[],null],[1,2]]'");
assertQuery("SELECT JSON_FORMAT(CAST(ROW(ROW(ROW(ROW(ROW(a, b), c), d), e), f) AS JSON)) FROM (VALUES (ROW(0, 1), 2, '3', NULL, ARRAY[5], ARRAY[])) t(a, b, c, d, e, f)",
"SELECT '[[[[[[0,1],2],\"3\"],null],[5]],[]]'");
assertQuery("SELECT JSON_FORMAT(CAST(ARRAY_AGG(ROW(a, b)) AS JSON)) FROM (VALUES (1, 2), (3, 4), (5, 6)) t(a, b)", "SELECT '[[1,2],[3,4],[5,6]]'");
assertQuery("SELECT CONTAINS(ARRAY_AGG(ROW(a, b)), ROW(1, 2)) FROM (VALUES (1, 2), (3, 4), (5, 6)) t(a, b)", "SELECT TRUE");
assertQuery("SELECT JSON_FORMAT(CAST(ARRAY_AGG(ROW(c, d)) AS JSON)) FROM (VALUES (ARRAY[1, 3, 5], ARRAY[2, 4, 6])) AS t(a, b) CROSS JOIN UNNEST(a, b) AS u(c, d)",
"SELECT '[[1,2],[3,4],[5,6]]'");
assertQuery("SELECT JSON_FORMAT(CAST(ROW(x, y, z) AS JSON)) FROM (VALUES ROW(1, NULL, '3')) t(x,y,z)", "SELECT '[1,null,\"3\"]'");
assertQuery("SELECT JSON_FORMAT(CAST(ROW(x, y, z) AS JSON)) FROM (VALUES ROW(1, CAST(NULL AS INTEGER), '3')) t(x,y,z)", "SELECT '[1,null,\"3\"]'");
}
@Test
public void testMaps()
{
assertQuery("SELECT m[max_key] FROM (SELECT map_agg(orderkey, orderkey) m, max(orderkey) max_key FROM orders)", "SELECT max(orderkey) FROM orders");
// Make sure that even if the map constructor throws with the NULL key the block builders are left in a consistent state
// and the TRY() call eventually succeeds and return NULL values.
assertQuery("SELECT JSON_FORMAT(CAST(TRY(MAP(ARRAY[NULL], ARRAY[x])) AS JSON)) FROM (VALUES 1, 2) t(x)", "SELECT * FROM (VALUES NULL, NULL)");
}
@Test
public void testValues()
{
assertQuery("VALUES 1, 2, 3, 4");
assertQuery("VALUES 1, 3, 2, 4 ORDER BY 1", "SELECT * FROM (VALUES 1, 3, 2, 4) ORDER BY 1");
assertQuery("VALUES (1.1, 2, 'foo'), (sin(3.3), 2+2, 'bar')");
assertQuery("VALUES (1.1, 2), (sin(3.3), 2+2) ORDER BY 1", "VALUES (sin(3.3), 2+2), (1.1, 2)");
assertQuery("VALUES (1.1, 2), (sin(3.3), 2+2) LIMIT 1", "VALUES (1.1, 2)");
assertQuery("SELECT * FROM (VALUES (1.1, 2), (sin(3.3), 2+2))");
assertQuery("SELECT 1.1 in (VALUES (1.1), (2.2))", "VALUES (TRUE)");
assertQuery("" +
"WITH a AS (VALUES (1.1, 2), (sin(3.3), 2+2)) " +
"SELECT * FROM a",
"VALUES (1.1, 2), (sin(3.3), 2+2)");
// implicit coersions
assertQuery("VALUES 1, 2.2, 3, 4.4");
assertQuery("VALUES (1, 2), (3.3, 4.4)");
assertQuery("VALUES true, 1.0 in (1, 2, 3)");
}
@Test
public void testSpecialFloatingPointValues()
{
MaterializedResult actual = computeActual("SELECT nan(), infinity(), -infinity()");
MaterializedRow row = getOnlyElement(actual.getMaterializedRows());
assertEquals(row.getField(0), Double.NaN);
assertEquals(row.getField(1), Double.POSITIVE_INFINITY);
assertEquals(row.getField(2), Double.NEGATIVE_INFINITY);
}
@Test
public void testMaxMinStringWithNulls()
{
assertQuery("SELECT custkey, MAX(NULLIF(orderstatus, 'O')), MIN(NULLIF(orderstatus, 'O')) FROM orders GROUP BY custkey");
}
@Test
public void testApproxPercentile()
{
MaterializedResult raw = computeActual("SELECT orderstatus, orderkey, totalprice FROM orders");
Multimap orderKeyByStatus = ArrayListMultimap.create();
Multimap totalPriceByStatus = ArrayListMultimap.create();
for (MaterializedRow row : raw.getMaterializedRows()) {
orderKeyByStatus.put((String) row.getField(0), ((Number) row.getField(1)).longValue());
totalPriceByStatus.put((String) row.getField(0), (Double) row.getField(2));
}
MaterializedResult actual = computeActual("" +
"SELECT orderstatus, " +
" approx_percentile(orderkey, 0.5), " +
" approx_percentile(totalprice, 0.5)," +
" approx_percentile(orderkey, 2, 0.5)," +
" approx_percentile(totalprice, 2, 0.5)," +
" approx_percentile(orderkey, .2, 0.5)," +
" approx_percentile(totalprice, .2, 0.5)\n" +
"FROM orders\n" +
"GROUP BY orderstatus");
for (MaterializedRow row : actual.getMaterializedRows()) {
String status = (String) row.getField(0);
Long orderKey = ((Number) row.getField(1)).longValue();
Double totalPrice = (Double) row.getField(2);
Long orderKeyWeighted = ((Number) row.getField(3)).longValue();
Double totalPriceWeighted = (Double) row.getField(4);
Long orderKeyFractionalWeighted = ((Number) row.getField(5)).longValue();
Double totalPriceFractionalWeighted = (Double) row.getField(6);
List orderKeys = Ordering.natural().sortedCopy(orderKeyByStatus.get(status));
List totalPrices = Ordering.natural().sortedCopy(totalPriceByStatus.get(status));
// verify real rank of returned value is within 1% of requested rank
assertTrue(orderKey >= orderKeys.get((int) (0.49 * orderKeys.size())));
assertTrue(orderKey <= orderKeys.get((int) (0.51 * orderKeys.size())));
assertTrue(orderKeyWeighted >= orderKeys.get((int) (0.49 * orderKeys.size())));
assertTrue(orderKeyWeighted <= orderKeys.get((int) (0.51 * orderKeys.size())));
assertTrue(orderKeyFractionalWeighted >= orderKeys.get((int) (0.49 * orderKeys.size())));
assertTrue(orderKeyFractionalWeighted <= orderKeys.get((int) (0.51 * orderKeys.size())));
assertTrue(totalPrice >= totalPrices.get((int) (0.49 * totalPrices.size())));
assertTrue(totalPrice <= totalPrices.get((int) (0.51 * totalPrices.size())));
assertTrue(totalPriceWeighted >= totalPrices.get((int) (0.49 * totalPrices.size())));
assertTrue(totalPriceWeighted <= totalPrices.get((int) (0.51 * totalPrices.size())));
assertTrue(totalPriceFractionalWeighted >= totalPrices.get((int) (0.49 * totalPrices.size())));
assertTrue(totalPriceFractionalWeighted <= totalPrices.get((int) (0.51 * totalPrices.size())));
}
}
@Test
public void testComplexQuery()
{
assertQueryOrdered(
"SELECT sum(orderkey), row_number() OVER (ORDER BY orderkey) " +
"FROM orders " +
"WHERE orderkey <= 10 " +
"GROUP BY orderkey " +
"HAVING sum(orderkey) >= 3 " +
"ORDER BY orderkey DESC " +
"LIMIT 3",
"VALUES (7, 5), (6, 4), (5, 3)");
}
@Test
public void testWhereNull()
{
// This query is has this strange shape to force the compiler to leave a true on the stack
// with the null flag set so if the filter method is not handling nulls correctly, this
// query will fail
assertQuery("SELECT custkey FROM orders WHERE custkey = custkey AND CAST(nullif(custkey, custkey) AS boolean) AND CAST(nullif(custkey, custkey) AS boolean)");
}
@Test
public void testDistinctMultipleFields()
{
assertQuery("SELECT DISTINCT custkey, orderstatus FROM orders");
}
@Test
public void testArithmeticNegation()
{
assertQuery("SELECT -custkey FROM orders");
}
@Test
public void testDistinct()
{
assertQuery("SELECT DISTINCT custkey FROM orders");
}
@Test
public void testDistinctHaving()
{
assertQuery("SELECT COUNT(DISTINCT clerk) AS count " +
"FROM orders " +
"GROUP BY orderdate " +
"HAVING COUNT(DISTINCT clerk) > 1");
}
@Test
public void testDistinctLimit()
{
assertQuery("" +
"SELECT DISTINCT orderstatus, custkey " +
"FROM (SELECT orderstatus, custkey FROM orders ORDER BY orderkey LIMIT 10) " +
"LIMIT 10");
assertQuery("SELECT COUNT(*) FROM (SELECT DISTINCT orderstatus, custkey FROM orders LIMIT 10)");
assertQuery("SELECT DISTINCT custkey, orderstatus FROM orders WHERE custkey = 1268 LIMIT 2");
assertQuery("" +
"SELECT DISTINCT x " +
"FROM (VALUES 1) t(x) JOIN (VALUES 10, 20) u(a) ON t.x < u.a " +
"LIMIT 100",
"SELECT 1");
}
@Test
public void testDistinctWithOrderBy()
{
assertQueryOrdered("SELECT DISTINCT custkey FROM orders ORDER BY custkey LIMIT 10");
}
@Test
public void testDistinctWithOrderByNotInSelect()
{
assertQueryFails(
"SELECT DISTINCT custkey FROM orders ORDER BY orderkey LIMIT 10",
"line 1:1: For SELECT DISTINCT, ORDER BY expressions must appear in select list");
}
@Test
public void testGroupByOrderByLimit()
{
assertQueryOrdered("SELECT custkey, SUM(totalprice) FROM orders GROUP BY custkey ORDER BY SUM(totalprice) DESC LIMIT 10");
}
@Test
public void testLimitZero()
{
assertQuery("SELECT custkey, totalprice FROM orders LIMIT 0");
}
@Test
public void testLimitAll()
{
assertQuery("SELECT custkey, totalprice FROM orders LIMIT ALL", "SELECT custkey, totalprice FROM orders");
}
@Test
public void testOffset()
{
String values = "(VALUES ('A', 3), ('D', 2), ('C', 1), ('B', 4)) AS t(x, y)";
MaterializedResult actual = computeActual("SELECT x FROM " + values + " OFFSET 2 ROWS");
MaterializedResult all = computeExpected("SELECT x FROM " + values, actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 2);
assertNotEquals(actual.getMaterializedRows().get(0), actual.getMaterializedRows().get(1));
assertContains(all, actual);
}
@Test
public void testOffsetWithFetch()
{
String values = "(VALUES ('A', 3), ('D', 2), ('C', 1), ('B', 4)) AS t(x, y)";
MaterializedResult actual = computeActual("SELECT x FROM " + values + " OFFSET 2 ROWS FETCH NEXT ROW ONLY");
MaterializedResult all = computeExpected("SELECT x FROM " + values, actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 1);
assertContains(all, actual);
}
@Test
public void testOffsetWithOrderBy()
{
String values = "(VALUES ('A', 3), ('D', 2), ('C', 1), ('B', 4)) AS t(x, y)";
assertQuery("SELECT x FROM " + values + " ORDER BY y OFFSET 2 ROWS", "VALUES 'A', 'B'");
assertQuery("SELECT x FROM " + values + " ORDER BY y OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY", "VALUES 'A'");
}
@Test
public void testOffsetEmptyResult()
{
assertQueryReturnsEmptyResult("SELECT name FROM nation OFFSET 100 ROWS");
assertQueryReturnsEmptyResult("SELECT name FROM nation ORDER BY regionkey OFFSET 100 ROWS");
assertQueryReturnsEmptyResult("SELECT name FROM nation OFFSET 100 ROWS LIMIT 20");
assertQueryReturnsEmptyResult("SELECT name FROM nation ORDER BY regionkey OFFSET 100 ROWS LIMIT 20");
}
@Test
public void testFetchFirstWithTies()
{
String values = "(VALUES 1, 1, 1, 0, 0, 0, 2, 2, 2) AS t(x)";
assertQuery("SELECT x FROM " + values + " ORDER BY x FETCH FIRST 4 ROWS WITH TIES", "VALUES 0, 0, 0, 1, 1, 1");
assertQuery("SELECT x FROM " + values + " ORDER BY x FETCH FIRST ROW WITH TIES", "VALUES 0, 0, 0");
assertQuery("SELECT x FROM " + values + " ORDER BY x FETCH FIRST 20 ROWS WITH TIES", "VALUES 0, 0, 0, 1, 1, 1, 2, 2, 2");
assertQuery("SELECT x FROM " + values + " ORDER BY x OFFSET 2 ROWS FETCH NEXT 2 ROWS WITH TIES", "VALUES 0, 1, 1, 1");
assertQueryReturnsEmptyResult("SELECT x FROM " + values + " ORDER BY x OFFSET 20 ROWS FETCH NEXT 2 ROWS WITH TIES");
assertQueryFails("SELECT x FROM " + values + " FETCH FIRST 4 ROWS WITH TIES", "line 1:58: FETCH FIRST WITH TIES clause requires ORDER BY");
assertQueryFails(
"SELECT x FROM (SELECT a FROM (VALUES 3, 2, 1, 1, 0) t(a) ORDER BY a) t1(x) FETCH FIRST 2 ROWS WITH TIES",
"line 1:76: FETCH FIRST WITH TIES clause requires ORDER BY");
String valuesMultiColumn = "(VALUES ('b', 0), ('b', 0), ('a', 1), ('a', 0), ('b', 1)) AS t(x, y)";
// if ORDER BY uses multiple symbols, then TIES are resolved basing on multiple symbols too
assertQuery("SELECT x, y FROM " + valuesMultiColumn + " ORDER BY x, y FETCH FIRST 3 ROWS WITH TIES", "VALUES ('a', 0), ('a', 1), ('b', 0), ('b', 0)");
assertQuery("SELECT x, y FROM " + valuesMultiColumn + " ORDER BY x DESC, y FETCH FIRST ROW WITH TIES", "VALUES ('b', 0), ('b', 0)");
}
@Test
public void testRepeatedAggregations()
{
assertQuery("SELECT SUM(orderkey), SUM(orderkey) FROM orders");
}
@Test
public void testRepeatedOutputs()
{
assertQuery("SELECT orderkey a, orderkey b FROM orders WHERE orderstatus = 'F'");
}
@Test
public void testRepeatedOutputs2()
{
// this test exposed a bug that wasn't caught by other tests that resulted in the execution engine
// trying to read orderkey as the second field, causing a type mismatch
assertQuery("SELECT orderdate, orderdate, orderkey FROM orders");
}
@Test
public void testLimit()
{
MaterializedResult actual = computeActual("SELECT orderkey FROM orders LIMIT 10");
MaterializedResult all = computeExpected("SELECT orderkey FROM orders", actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 10);
assertContains(all, actual);
}
@Test
public void testLimitWithAggregation()
{
MaterializedResult actual = computeActual("SELECT custkey, SUM(CAST(totalprice * 100 AS BIGINT)) FROM orders GROUP BY custkey LIMIT 10");
MaterializedResult all = computeExpected("SELECT custkey, SUM(CAST(totalprice * 100 AS BIGINT)) FROM orders GROUP BY custkey", actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 10);
assertContains(all, actual);
}
@Test
public void testLimitInInlineView()
{
MaterializedResult actual = computeActual("SELECT orderkey FROM (SELECT orderkey FROM orders LIMIT 100) T LIMIT 10");
MaterializedResult all = computeExpected("SELECT orderkey FROM orders", actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 10);
assertContains(all, actual);
}
@Test
public void testCountAll()
{
assertQuery("SELECT COUNT(*) FROM orders");
assertQuery("SELECT COUNT(42) FROM orders", "SELECT COUNT(*) FROM orders");
assertQuery("SELECT COUNT(42 + 42) FROM orders", "SELECT COUNT(*) FROM orders");
assertQuery("SELECT COUNT(null) FROM orders", "SELECT 0");
}
@Test
public void testCountColumn()
{
assertQuery("SELECT COUNT(orderkey) FROM orders");
assertQuery("SELECT COUNT(orderstatus) FROM orders");
assertQuery("SELECT COUNT(orderdate) FROM orders");
assertQuery("SELECT COUNT(1) FROM orders");
assertQuery("SELECT COUNT(NULLIF(orderstatus, 'F')) FROM orders");
assertQuery("SELECT COUNT(CAST(NULL AS BIGINT)) FROM orders"); // todo: make COUNT(null) work
}
@Test
public void testWildcard()
{
assertQuery("SELECT * FROM orders");
}
@Test
public void testMultipleWildcards()
{
assertQuery("SELECT *, 123, * FROM orders");
}
@Test
public void testMixedWildcards()
{
assertQuery("SELECT *, orders.*, orderkey FROM orders");
}
@Test
public void testQualifiedWildcardFromAlias()
{
assertQuery("SELECT T.* FROM orders T");
}
@Test
public void testQualifiedWildcardFromInlineView()
{
assertQuery("SELECT T.* FROM (SELECT orderkey + custkey FROM orders) T");
}
@Test
public void testQualifiedWildcard()
{
assertQuery("SELECT orders.* FROM orders");
}
@Test
public void testAverageAll()
{
assertQuery("SELECT AVG(totalprice) FROM orders");
}
@Test
public void testVariance()
{
// int64
assertQuery("SELECT VAR_SAMP(custkey) FROM orders");
assertQuery("SELECT VAR_SAMP(custkey) FROM (SELECT custkey FROM orders ORDER BY custkey LIMIT 2) T");
assertQuery("SELECT VAR_SAMP(custkey) FROM (SELECT custkey FROM orders ORDER BY custkey LIMIT 1) T");
assertQuery("SELECT VAR_SAMP(custkey) FROM (SELECT custkey FROM orders LIMIT 0) T");
// double
assertQuery("SELECT VAR_SAMP(totalprice) FROM orders");
assertQuery("SELECT VAR_SAMP(totalprice) FROM (SELECT totalprice FROM orders ORDER BY totalprice LIMIT 2) T");
assertQuery("SELECT VAR_SAMP(totalprice) FROM (SELECT totalprice FROM orders ORDER BY totalprice LIMIT 1) T");
assertQuery("SELECT VAR_SAMP(totalprice) FROM (SELECT totalprice FROM orders LIMIT 0) T");
}
@Test
public void testVariancePop()
{
// int64
assertQuery("SELECT VAR_POP(custkey) FROM orders");
assertQuery("SELECT VAR_POP(custkey) FROM (SELECT custkey FROM orders ORDER BY custkey LIMIT 2) T");
assertQuery("SELECT VAR_POP(custkey) FROM (SELECT custkey FROM orders ORDER BY custkey LIMIT 1) T");
assertQuery("SELECT VAR_POP(custkey) FROM (SELECT custkey FROM orders LIMIT 0) T");
// double
assertQuery("SELECT VAR_POP(totalprice) FROM orders");
assertQuery("SELECT VAR_POP(totalprice) FROM (SELECT totalprice FROM orders ORDER BY totalprice LIMIT 2) T");
assertQuery("SELECT VAR_POP(totalprice) FROM (SELECT totalprice FROM orders ORDER BY totalprice LIMIT 1) T");
assertQuery("SELECT VAR_POP(totalprice) FROM (SELECT totalprice FROM orders LIMIT 0) T");
}
@Test
public void testStdDev()
{
// int64
assertQuery("SELECT STDDEV_SAMP(custkey) FROM orders");
assertQuery("SELECT STDDEV_SAMP(custkey) FROM (SELECT custkey FROM orders ORDER BY custkey LIMIT 2) T");
assertQuery("SELECT STDDEV_SAMP(custkey) FROM (SELECT custkey FROM orders ORDER BY custkey LIMIT 1) T");
assertQuery("SELECT STDDEV_SAMP(custkey) FROM (SELECT custkey FROM orders LIMIT 0) T");
// double
assertQuery("SELECT STDDEV_SAMP(totalprice) FROM orders");
assertQuery("SELECT STDDEV_SAMP(totalprice) FROM (SELECT totalprice FROM orders ORDER BY totalprice LIMIT 2) T");
assertQuery("SELECT STDDEV_SAMP(totalprice) FROM (SELECT totalprice FROM orders ORDER BY totalprice LIMIT 1) T");
assertQuery("SELECT STDDEV_SAMP(totalprice) FROM (SELECT totalprice FROM orders LIMIT 0) T");
}
@Test
public void testStdDevPop()
{
// int64
assertQuery("SELECT STDDEV_POP(custkey) FROM orders");
assertQuery("SELECT STDDEV_POP(custkey) FROM (SELECT custkey FROM orders ORDER BY custkey LIMIT 2) T");
assertQuery("SELECT STDDEV_POP(custkey) FROM (SELECT custkey FROM orders ORDER BY custkey LIMIT 1) T");
assertQuery("SELECT STDDEV_POP(custkey) FROM (SELECT custkey FROM orders LIMIT 0) T");
// double
assertQuery("SELECT STDDEV_POP(totalprice) FROM orders");
assertQuery("SELECT STDDEV_POP(totalprice) FROM (SELECT totalprice FROM orders ORDER BY totalprice LIMIT 2) T");
assertQuery("SELECT STDDEV_POP(totalprice) FROM (SELECT totalprice FROM orders ORDER BY totalprice LIMIT 1) T");
assertQuery("SELECT STDDEV_POP(totalprice) FROM (SELECT totalprice FROM orders LIMIT 0) T");
}
@Test
public void testRollupOverUnion()
{
assertQuery("" +
"SELECT orderstatus, sum(orderkey)\n" +
"FROM (SELECT orderkey, orderstatus\n" +
" FROM orders\n" +
" UNION ALL\n" +
" SELECT orderkey, orderstatus\n" +
" FROM orders) x\n" +
"GROUP BY ROLLUP (orderstatus)",
"VALUES ('P', 21470000),\n" +
"('O', 439774330),\n" +
"('F', 438500670),\n" +
"(NULL, 899745000)");
assertQuery(
"SELECT regionkey, count(*) FROM (" +
" SELECT regionkey FROM nation " +
" UNION ALL " +
" SELECT * FROM (VALUES 2, 100) t(regionkey)) " +
"GROUP BY ROLLUP (regionkey)",
"SELECT * FROM (VALUES (0, 5), (1, 5), (2, 6), (3, 5), (4, 5), (100, 1), (NULL, 27))");
}
@Test
public void testGrouping()
{
assertQuery(
"SELECT a, b AS t, sum(c), grouping(a, b) + grouping(a) " +
"FROM (VALUES ('h', 'j', 11), ('k', 'l', 7)) AS t (a, b, c) " +
"GROUP BY GROUPING SETS ( (a), (b)) " +
"ORDER BY grouping(b) ASC",
"VALUES (NULL, 'j', 11, 3), (NULL, 'l', 7, 3), ('h', NULL, 11, 1), ('k', NULL, 7, 1)");
assertQuery(
"SELECT a, sum(b), grouping(a) FROM (VALUES ('h', 11, 0), ('k', 7, 0)) AS t (a, b, c) GROUP BY GROUPING SETS (a)",
"VALUES ('h', 11, 0), ('k', 7, 0)");
assertQuery(
"SELECT a, b, sum(c), grouping(a, b) FROM (VALUES ('h', 'j', 11), ('k', 'l', 7) ) AS t (a, b, c) GROUP BY GROUPING SETS ( (a), (b)) HAVING grouping(a, b) > 1 ",
"VALUES (NULL, 'j', 11, 2), (NULL, 'l', 7, 2)");
assertQuery("SELECT a, grouping(a) * 1.0 FROM (VALUES (1) ) AS t (a) GROUP BY a",
"VALUES (1, 0.0)");
assertQuery("SELECT a, grouping(a), grouping(a) FROM (VALUES (1) ) AS t (a) GROUP BY a",
"VALUES (1, 0, 0)");
assertQuery("SELECT grouping(a) FROM (VALUES ('h', 'j', 11), ('k', 'l', 7)) AS t (a, b, c) GROUP BY GROUPING SETS (a,c), c*2",
"VALUES (0), (1), (0), (1)");
}
@Test
public void testGroupingWithFortyArguments()
{
// This test ensures we correctly pick the bigint implementation version of the grouping
// function which supports up to 62 columns. Semantically it is exactly the same as
// TestGroupingOperationFunction#testMoreThanThirtyTwoArguments. That test is a little easier to
// understand and verify.
String fortyLetterSequence = "aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, ao, ap, aq, ar, asa, at, au, av, aw, ax, ay, az, " +
"ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn";
String fortyIntegers = "1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, " +
"31, 32, 33, 34, 35, 36, 37, 38, 39, 40";
// 20, 2, 13, 33, 40, 9 , 14 (corresponding indices from Left to right in the above fortyLetterSequence)
String groupingSet1 = "at, ab, am, bg, bn, ai, an";
// 28, 4, 5, 29, 31, 10 (corresponding indices from left to right in the above fortyLetterSequence)
String groupingSet2 = "bb, ad, ae, bc, be, aj";
String query = format(
"SELECT grouping(%s) FROM (VALUES (%s)) AS t(%s) GROUP BY GROUPING SETS ((%s), (%s), (%s))",
fortyLetterSequence,
fortyIntegers,
fortyLetterSequence,
fortyLetterSequence,
groupingSet1,
groupingSet2);
assertQuery(query, "VALUES (0), (822283861886), (995358664191)");
}
@Test
public void testGroupingInTableSubquery()
{
// In addition to testing grouping() in subqueries, the following tests also
// ensure correct behavior in the case of alternating GROUPING SETS and GROUP BY
// clauses in the same plan. This is significant because grouping() with GROUP BY
// works only with a special re-write that should not happen in the presence of
// GROUPING SETS.
// Inner query has a single GROUP BY and outer query has GROUPING SETS
assertQuery(
"SELECT orderkey, custkey, sum(agg_price) AS outer_sum, grouping(orderkey, custkey), g " +
"FROM " +
" (SELECT orderkey, custkey, sum(totalprice) AS agg_price, grouping(custkey, orderkey) AS g " +
" FROM orders " +
" GROUP BY orderkey, custkey " +
" ORDER BY agg_price ASC " +
" LIMIT 5) AS t " +
"GROUP BY GROUPING SETS ((orderkey, custkey), g) " +
"ORDER BY outer_sum",
"VALUES (35271, 334, 874.89, 0, NULL), " +
" (28647, 1351, 924.33, 0, NULL), " +
" (58145, 862, 929.03, 0, NULL), " +
" (8354, 634, 974.04, 0, NULL), " +
" (37415, 301, 986.63, 0, NULL), " +
" (NULL, NULL, 4688.92, 3, 0)");
// Inner query has GROUPING SETS and outer query has GROUP BY
assertQuery(
"SELECT orderkey, custkey, g, sum(agg_price) AS outer_sum, grouping(orderkey, custkey) " +
"FROM " +
" (SELECT orderkey, custkey, sum(totalprice) AS agg_price, grouping(custkey, orderkey) AS g " +
" FROM orders " +
" GROUP BY GROUPING SETS ((custkey), (orderkey)) " +
" ORDER BY agg_price ASC " +
" LIMIT 5) AS t " +
"GROUP BY orderkey, custkey, g",
"VALUES (28647, NULL, 2, 924.33, 0), " +
" (8354, NULL, 2, 974.04, 0), " +
" (37415, NULL, 2, 986.63, 0), " +
" (58145, NULL, 2, 929.03, 0), " +
" (35271, NULL, 2, 874.89, 0)");
// Inner query has GROUPING SETS but no grouping and outer query has a simple GROUP BY
assertQuery(
"SELECT orderkey, custkey, sum(agg_price) AS outer_sum, grouping(orderkey, custkey) " +
"FROM " +
" (SELECT orderkey, custkey, sum(totalprice) AS agg_price " +
" FROM orders " +
" GROUP BY GROUPING SETS ((custkey), (orderkey)) " +
" ORDER BY agg_price ASC NULLS FIRST) AS t " +
"GROUP BY orderkey, custkey " +
"ORDER BY outer_sum ASC NULLS FIRST " +
"LIMIT 5",
"VALUES (35271, NULL, 874.89, 0), " +
" (28647, NULL, 924.33, 0), " +
" (58145, NULL, 929.03, 0), " +
" (8354, NULL, 974.04, 0), " +
" (37415, NULL, 986.63, 0)");
}
@Test
public void testIntersect()
{
assertQuery(
"SELECT regionkey FROM nation WHERE nationkey < 7 " +
"INTERSECT SELECT regionkey FROM nation WHERE nationkey > 21");
assertQuery(
"SELECT regionkey FROM nation WHERE nationkey < 7 " +
"INTERSECT DISTINCT SELECT regionkey FROM nation WHERE nationkey > 21",
"VALUES 1, 3");
assertQuery(
"WITH wnation AS (SELECT nationkey, regionkey FROM nation) " +
"SELECT regionkey FROM wnation WHERE nationkey < 7 " +
"INTERSECT SELECT regionkey FROM wnation WHERE nationkey > 21", "VALUES 1, 3");
assertQuery(
"SELECT num FROM (SELECT 1 AS num FROM nation WHERE nationkey=10 " +
"INTERSECT SELECT 1 FROM nation WHERE nationkey=20) T");
assertQuery(
"SELECT nationkey, nationkey / 2 FROM (SELECT nationkey FROM nation WHERE nationkey < 10 " +
"INTERSECT SELECT nationkey FROM nation WHERE nationkey > 4) T WHERE nationkey % 2 = 0");
assertQuery(
"SELECT regionkey FROM (SELECT regionkey FROM nation WHERE nationkey < 7 " +
"INTERSECT SELECT regionkey FROM nation WHERE nationkey > 21) " +
"UNION SELECT 4");
assertQuery(
"SELECT regionkey FROM (SELECT regionkey FROM nation WHERE nationkey < 7 " +
"UNION SELECT regionkey FROM nation WHERE nationkey > 21) " +
"INTERSECT SELECT 1");
assertQuery(
"SELECT regionkey FROM (SELECT regionkey FROM nation WHERE nationkey < 7 " +
"INTERSECT SELECT regionkey FROM nation WHERE nationkey > 21) " +
"UNION ALL SELECT 3");
assertQuery(
"SELECT regionkey FROM (SELECT regionkey FROM nation WHERE nationkey < 7 " +
"INTERSECT SELECT regionkey FROM nation WHERE nationkey > 21) " +
"UNION ALL SELECT 3");
assertQuery(
"SELECT * FROM (VALUES 1, 2) " +
"INTERSECT SELECT * FROM (VALUES 1.0, 2)",
"VALUES 1.0, 2.0");
assertQuery("SELECT NULL, NULL INTERSECT SELECT NULL, NULL FROM nation");
MaterializedResult emptyResult = computeActual("SELECT 100 INTERSECT (SELECT regionkey FROM nation WHERE nationkey <10)");
assertEquals(emptyResult.getMaterializedRows().size(), 0);
}
@Test
public void testIntersectWithAggregation()
{
assertQuery("SELECT COUNT(*) FROM nation INTERSECT SELECT COUNT(regionkey) FROM nation HAVING SUM(regionkey) IS NOT NULL");
assertQuery("SELECT SUM(nationkey), COUNT(name) FROM (SELECT nationkey,name FROM nation INTERSECT SELECT regionkey, name FROM nation) n");
assertQuery("SELECT COUNT(*) * 2 FROM nation INTERSECT (SELECT SUM(nationkey) FROM nation GROUP BY regionkey ORDER BY 1 LIMIT 2)");
assertQuery("SELECT COUNT(a) FROM (SELECT nationkey AS a FROM (SELECT nationkey FROM nation INTERSECT SELECT regionkey FROM nation) n1 INTERSECT SELECT regionkey FROM nation) n2");
assertQuery("SELECT COUNT(*), SUM(2), regionkey FROM (SELECT nationkey, regionkey FROM nation INTERSECT SELECT regionkey, regionkey FROM nation) n GROUP BY regionkey");
assertQuery("SELECT COUNT(*) FROM (SELECT nationkey FROM nation INTERSECT SELECT 2) n1 INTERSECT SELECT regionkey FROM nation");
}
@Test
public void testIntersectAllFails()
{
assertQueryFails("SELECT * FROM (VALUES 1, 2, 3, 4) INTERSECT ALL SELECT * FROM (VALUES 3, 4)", "line 1:35: INTERSECT ALL not yet implemented");
}
@Test
public void testExcept()
{
assertQuery(
"SELECT regionkey FROM nation WHERE nationkey < 7 " +
"EXCEPT SELECT regionkey FROM nation WHERE nationkey > 21");
assertQuery(
"SELECT regionkey FROM nation WHERE nationkey < 7 " +
"EXCEPT DISTINCT SELECT regionkey FROM nation WHERE nationkey > 21",
"VALUES 0, 4");
assertQuery(
"WITH wnation AS (SELECT nationkey, regionkey FROM nation) " +
"SELECT regionkey FROM wnation WHERE nationkey < 7 " +
"EXCEPT SELECT regionkey FROM wnation WHERE nationkey > 21",
"VALUES 0, 4");
assertQuery(
"SELECT num FROM (SELECT 1 AS num FROM nation WHERE nationkey=10 " +
"EXCEPT SELECT 2 FROM nation WHERE nationkey=20) T");
assertQuery(
"SELECT nationkey, nationkey / 2 FROM (SELECT nationkey FROM nation WHERE nationkey < 10 " +
"EXCEPT SELECT nationkey FROM nation WHERE nationkey > 4) T WHERE nationkey % 2 = 0");
assertQuery(
"SELECT regionkey FROM (SELECT regionkey FROM nation WHERE nationkey < 7 " +
"EXCEPT SELECT regionkey FROM nation WHERE nationkey > 21) " +
"UNION SELECT 3");
assertQuery(
"SELECT regionkey FROM (SELECT regionkey FROM nation WHERE nationkey < 7 " +
"UNION SELECT regionkey FROM nation WHERE nationkey > 21) " +
"EXCEPT SELECT 1");
assertQuery(
"SELECT regionkey FROM (SELECT regionkey FROM nation WHERE nationkey < 7 " +
"EXCEPT SELECT regionkey FROM nation WHERE nationkey > 21) " +
"UNION ALL SELECT 4");
assertQuery(
"SELECT * FROM (VALUES 1, 2) " +
"EXCEPT SELECT * FROM (VALUES 3.0, 2)");
assertQuery("SELECT NULL, NULL EXCEPT SELECT NULL, NULL FROM nation");
assertQuery(
"(SELECT * FROM (VALUES 1) EXCEPT SELECT * FROM (VALUES 0))" +
"EXCEPT (SELECT * FROM (VALUES 1) EXCEPT SELECT * FROM (VALUES 1))");
MaterializedResult emptyResult = computeActual("SELECT 0 EXCEPT (SELECT regionkey FROM nation WHERE nationkey <10)");
assertEquals(emptyResult.getMaterializedRows().size(), 0);
}
@Test
public void testExceptWithAggregation()
{
assertQuery("SELECT COUNT(*) FROM nation EXCEPT SELECT COUNT(regionkey) FROM nation WHERE regionkey < 3 HAVING SUM(regionkey) IS NOT NULL");
assertQuery("SELECT SUM(nationkey), COUNT(name) FROM (SELECT nationkey, name FROM nation WHERE nationkey < 6 EXCEPT SELECT regionkey, name FROM nation) n");
assertQuery("(SELECT SUM(nationkey) FROM nation GROUP BY regionkey ORDER BY 1 LIMIT 2) EXCEPT SELECT COUNT(*) * 2 FROM nation");
assertQuery("SELECT COUNT(a) FROM (SELECT nationkey AS a FROM (SELECT nationkey FROM nation EXCEPT SELECT regionkey FROM nation) n1 EXCEPT SELECT regionkey FROM nation) n2");
assertQuery("SELECT COUNT(*), SUM(2), regionkey FROM (SELECT nationkey, regionkey FROM nation EXCEPT SELECT regionkey, regionkey FROM nation) n GROUP BY regionkey HAVING regionkey < 3");
assertQuery("SELECT COUNT(*) FROM (SELECT nationkey FROM nation EXCEPT SELECT 10) n1 EXCEPT SELECT regionkey FROM nation");
}
@Test
public void testExceptAllFails()
{
assertQueryFails("SELECT * FROM (VALUES 1, 2, 3, 4) EXCEPT ALL SELECT * FROM (VALUES 3, 4)", "line 1:35: EXCEPT ALL not yet implemented");
}
@Test
public void testSelectWithComparison()
{
assertQuery("SELECT orderkey FROM lineitem WHERE tax < discount");
}
@Test
public void testInlineView()
{
assertQuery("SELECT orderkey, custkey FROM (SELECT orderkey, custkey FROM orders) U");
}
@Test
public void testAliasedInInlineView()
{
assertQuery("SELECT x, y FROM (SELECT orderkey x, custkey y FROM orders) U");
}
@Test
public void testInlineViewWithProjections()
{
assertQuery("SELECT x + 1, y FROM (SELECT orderkey * 10 x, custkey y FROM orders) u");
}
@Test
public void testInUncorrelatedSubquery()
{
assertQuery(
"SELECT CASE WHEN false THEN 1 IN (VALUES 2) END",
"SELECT NULL");
assertQuery(
"SELECT x FROM (VALUES 2) t(x) WHERE MAP(ARRAY[8589934592], ARRAY[x]) IN (VALUES MAP(ARRAY[8589934592],ARRAY[2]))",
"SELECT 2");
assertQuery(
"SELECT a IN (VALUES 2), a FROM (VALUES (2)) t(a)",
"SELECT TRUE, 2");
}
@Test
public void testChecksum()
{
assertQuery("SELECT to_hex(checksum(0))", "SELECT '0000000000000000'");
}
@Test
public void testMaxBy()
{
assertQuery("SELECT MAX_BY(orderkey, totalprice) FROM orders", "SELECT orderkey FROM orders ORDER BY totalprice DESC LIMIT 1");
}
@Test
public void testMaxByN()
{
assertQuery("SELECT y FROM (SELECT MAX_BY(orderkey, totalprice, 2) mx FROM orders) CROSS JOIN UNNEST(mx) u(y)",
"SELECT orderkey FROM orders ORDER BY totalprice DESC LIMIT 2");
}
@Test
public void testMinBy()
{
assertQuery("SELECT MIN_BY(orderkey, totalprice) FROM orders", "SELECT orderkey FROM orders ORDER BY totalprice ASC LIMIT 1");
assertQuery("SELECT MIN_BY(a, ROW(b, c)) FROM (VALUES (1, 2, 3), (2, 2, 1)) AS t(a, b, c)", "SELECT 2");
}
@Test
public void testMinByN()
{
assertQuery("SELECT y FROM (SELECT MIN_BY(orderkey, totalprice, 2) mx FROM orders) CROSS JOIN UNNEST(mx) u(y)",
"SELECT orderkey FROM orders ORDER BY totalprice ASC LIMIT 2");
}
@Test
public void testHaving()
{
assertQuery("SELECT orderstatus, sum(totalprice) FROM orders GROUP BY orderstatus HAVING orderstatus = 'O'");
}
@Test
public void testHaving2()
{
assertQuery("SELECT custkey, sum(orderkey) FROM orders GROUP BY custkey HAVING sum(orderkey) > 400000");
}
@Test
public void testHaving3()
{
assertQuery("SELECT custkey, sum(totalprice) * 2 FROM orders GROUP BY custkey");
assertQuery("SELECT custkey, avg(totalprice + 5) FROM orders GROUP BY custkey");
assertQuery("SELECT custkey, sum(totalprice) * 2 FROM orders GROUP BY custkey HAVING avg(totalprice + 5) > 10");
}
@Test
public void testHavingWithoutGroupBy()
{
assertQuery("SELECT sum(orderkey) FROM orders HAVING sum(orderkey) > 400000");
}
@Test
public void testColumnAliases()
{
assertQuery(
"SELECT x, T.y, z + 1 FROM (SELECT custkey, orderstatus, totalprice FROM orders) T (x, y, z)",
"SELECT custkey, orderstatus, totalprice + 1 FROM orders");
}
@Test
public void testRowNumberNoOptimization()
{
MaterializedResult actual = computeActual("" +
"SELECT orderkey, orderstatus FROM (\n" +
" SELECT row_number() OVER () rn, orderkey, orderstatus\n" +
" FROM orders\n" +
") WHERE NOT rn <= 10");
MaterializedResult all = computeExpected("SELECT orderkey, orderstatus FROM orders", actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), all.getMaterializedRows().size() - 10);
assertContains(all, actual);
actual = computeActual("" +
"SELECT orderkey, orderstatus FROM (\n" +
" SELECT row_number() OVER () rn, orderkey, orderstatus\n" +
" FROM orders\n" +
") WHERE rn - 5 <= 10");
all = computeExpected("SELECT orderkey, orderstatus FROM orders", actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 15);
assertContains(all, actual);
}
@Test
public void testRowNumberLimit()
{
MaterializedResult actual = computeActual("" +
"SELECT row_number() OVER (PARTITION BY orderstatus) rn, orderstatus\n" +
"FROM orders\n" +
"LIMIT 10");
assertEquals(actual.getMaterializedRows().size(), 10);
actual = computeActual("" +
"SELECT row_number() OVER (PARTITION BY orderstatus ORDER BY orderkey) rn\n" +
"FROM orders\n" +
"LIMIT 10");
assertEquals(actual.getMaterializedRows().size(), 10);
actual = computeActual("" +
"SELECT row_number() OVER () rn, orderstatus\n" +
"FROM orders\n" +
"LIMIT 10");
assertEquals(actual.getMaterializedRows().size(), 10);
actual = computeActual("" +
"SELECT row_number() OVER (ORDER BY orderkey) rn\n" +
"FROM orders\n" +
"LIMIT 10");
assertEquals(actual.getMaterializedRows().size(), 10);
}
@Test
public void testRowNumberMultipleFilters()
{
MaterializedResult actual = computeActual("" +
"SELECT * FROM (" +
" SELECT a, row_number() OVER (PARTITION BY a ORDER BY a) rn\n" +
" FROM (VALUES (1), (1), (1), (2), (2), (3)) t (a)) t " +
"WHERE rn < 3 AND rn % 2 = 0 AND a = 2 LIMIT 2");
MaterializedResult expected = resultBuilder(getSession(), BIGINT, BIGINT)
.row(2, 2L)
.build();
assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows());
}
@Test
public void testRowNumberSpecialFilters()
{
// Test "row_number() = negative number" filter with ORDER BY. This should create a Window Node with a Filter Node on top and return 0 rows.
assertQueryReturnsEmptyResult("" +
"SELECT * FROM (" +
" SELECT a, row_number() OVER (PARTITION BY a ORDER BY a) rn\n" +
" FROM (VALUES (1), (1), (1), (2), (2), (3)) t (a)) t " +
"WHERE rn = -1");
// Test "row_number() <= negative number" filter with ORDER BY. This should create a Window Node with a Filter Node on top and return 0 rows.
assertQueryReturnsEmptyResult("" +
"SELECT * FROM (" +
" SELECT a, row_number() OVER (PARTITION BY a ORDER BY a) rn\n" +
" FROM (VALUES (1), (1), (1), (2), (2), (3)) t (a)) t " +
"WHERE rn <= -1");
// Test "row_number() = 0" filter with ORDER BY. This should create a Window Node with a Filter Node on top and return 0 rows.
assertQueryReturnsEmptyResult("" +
"SELECT * FROM (" +
" SELECT a, row_number() OVER (PARTITION BY a ORDER BY a) rn\n" +
" FROM (VALUES (1), (1), (1), (2), (2), (3)) t (a)) t " +
"WHERE rn = 0");
// Test "row_number() = negative number" filter without ORDER BY. This should create a RowNumber Node with a Filter Node on top and return 0 rows.
assertQueryReturnsEmptyResult("" +
"SELECT * FROM (" +
" SELECT a, row_number() OVER (PARTITION BY a) rn\n" +
" FROM (VALUES (1), (1), (1), (2), (2), (3)) t (a)) t " +
"WHERE rn = -1");
// Test "row_number() <= negative number" filter without ORDER BY. This should create a RowNumber Node with a Filter Node on top and return 0 rows.
assertQueryReturnsEmptyResult("" +
"SELECT * FROM (" +
" SELECT a, row_number() OVER (PARTITION BY a) rn\n" +
" FROM (VALUES (1), (1), (1), (2), (2), (3)) t (a)) t " +
"WHERE rn <= -1");
// Test "row_number() = 0" filter without ORDER BY. This should create a RowNumber Node with a Filter Node on top and return 0 rows.
assertQueryReturnsEmptyResult("" +
"SELECT * FROM (" +
" SELECT a, row_number() OVER (PARTITION BY a) rn\n" +
" FROM (VALUES (1), (1), (1), (2), (2), (3)) t (a)) t " +
"WHERE rn = 0");
}
@Test
public void testRowNumberFilterAndLimit()
{
MaterializedResult actual = computeActual("" +
"SELECT * FROM (" +
"SELECT a, row_number() OVER (PARTITION BY a ORDER BY a) rn\n" +
"FROM (VALUES (1), (2), (1), (2)) t (a)) t WHERE rn < 2 LIMIT 2");
MaterializedResult expected = resultBuilder(getSession(), BIGINT, BIGINT)
.row(1, 1L)
.row(2, 1L)
.build();
assertEqualsIgnoreOrder(actual.getMaterializedRows(), expected.getMaterializedRows());
actual = computeActual("" +
"SELECT * FROM (" +
"SELECT a, row_number() OVER (PARTITION BY a) rn\n" +
"FROM (VALUES (1), (2), (1), (2), (1)) t (a)) t WHERE rn < 3 LIMIT 2");
expected = resultBuilder(getSession(), BIGINT, BIGINT)
.row(1, 1L)
.row(1, 2L)
.row(2, 1L)
.row(2, 2L)
.build();
assertEquals(actual.getMaterializedRows().size(), 2);
assertContains(expected, actual);
}
@Test
public void testRowNumberUnpartitionedFilter()
{
MaterializedResult actual = computeActual("" +
"SELECT orderkey, orderstatus FROM (\n" +
" SELECT row_number() OVER () rn, orderkey, orderstatus\n" +
" FROM orders\n" +
") WHERE rn <= 5 AND orderstatus != 'Z'");
MaterializedResult all = computeExpected("SELECT orderkey, orderstatus FROM orders", actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 5);
assertContains(all, actual);
actual = computeActual("" +
"SELECT orderkey, orderstatus FROM (\n" +
" SELECT row_number() OVER () rn, orderkey, orderstatus\n" +
" FROM orders\n" +
") WHERE rn < 5");
all = computeExpected("SELECT orderkey, orderstatus FROM orders", actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 4);
assertContains(all, actual);
actual = computeActual("" +
"SELECT orderkey, orderstatus FROM (\n" +
" SELECT row_number() OVER () rn, orderkey, orderstatus\n" +
" FROM orders\n" +
") LIMIT 5");
all = computeExpected("SELECT orderkey, orderstatus FROM orders", actual.getTypes());
assertEquals(actual.getMaterializedRows().size(), 5);
assertContains(all, actual);
}
@Test
public void testRowNumberPartitionedFilter()
{
MaterializedResult actual = computeActual("" +
"SELECT orderkey, orderstatus FROM (\n" +
" SELECT row_number() OVER (PARTITION BY orderstatus) rn, orderkey, orderstatus\n" +
" FROM orders\n" +
") WHERE rn <= 5");
MaterializedResult all = computeExpected("SELECT orderkey, orderstatus FROM orders", actual.getTypes());
// there are 3 DISTINCT orderstatus, so expect 15 rows.
assertEquals(actual.getMaterializedRows().size(), 15);
assertContains(all, actual);
// Test for unreferenced outputs
actual = computeActual("" +
"SELECT orderkey FROM (\n" +
" SELECT row_number() OVER (PARTITION BY orderstatus) rn, orderkey\n" +
" FROM orders\n" +
") WHERE rn <= 5");
all = computeExpected("SELECT orderkey FROM orders", actual.getTypes());
// there are 3 distinct orderstatus, so expect 15 rows.
assertEquals(actual.getMaterializedRows().size(), 15);
assertContains(all, actual);
}
@Test
public void testRowNumberUnpartitionedFilterLimit()
{
assertQuery("" +
"SELECT row_number() OVER ()\n" +
"FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey\n" +
"WHERE orders.orderkey = 10000\n" +
"LIMIT 20");
}
@Test
public void testRowNumberPropertyDerivation()
{
assertQuery(
"SELECT orderkey, orderstatus, SUM(rn) OVER (PARTITION BY orderstatus) c " +
"FROM ( " +
" SELECT orderkey, orderstatus, row_number() OVER (PARTITION BY orderstatus) rn " +
" FROM ( " +
" SELECT * FROM orders ORDER BY orderkey LIMIT 10 " +
" ) " +
")",
"VALUES " +
"(1, 'O', 21), " +
"(2, 'O', 21), " +
"(3, 'F', 10), " +
"(4, 'O', 21), " +
"(5, 'F', 10), " +
"(6, 'F', 10), " +
"(7, 'O', 21), " +
"(32, 'O', 21), " +
"(33, 'F', 10), " +
"(34, 'O', 21)");
}
@Test
public void testTopNUnpartitionedWindow()
{
MaterializedResult actual = computeActual("" +
"SELECT * FROM (\n" +
" SELECT row_number() OVER (ORDER BY orderkey) rn, orderkey, orderstatus\n" +
" FROM orders\n" +
") WHERE rn <= 5");
String sql = "SELECT row_number() OVER (), orderkey, orderstatus FROM orders ORDER BY orderkey LIMIT 5";
MaterializedResult expected = computeExpected(sql, actual.getTypes());
assertEquals(actual, expected);
}
@Test
public void testTopNUnpartitionedLargeWindow()
{
MaterializedResult actual = computeActual("" +
"SELECT * FROM (\n" +
" SELECT row_number() OVER (ORDER BY orderkey) rn, orderkey, orderstatus\n" +
" FROM orders\n" +
") WHERE rn <= 10000");
String sql = "SELECT row_number() OVER (), orderkey, orderstatus FROM orders ORDER BY orderkey LIMIT 10000";
MaterializedResult expected = computeExpected(sql, actual.getTypes());
assertEqualsIgnoreOrder(actual, expected);
}
@Test
public void testTopNPartitionedWindow()
{
assertQuery(
"SELECT * FROM ( " +
" SELECT row_number() OVER (PARTITION BY orderstatus ORDER BY orderkey) rn, orderkey, orderstatus " +
" FROM orders " +
") WHERE rn <= 2",
"VALUES " +
"(1, 1, 'O'), " +
"(2, 2, 'O'), " +
"(1, 3, 'F'), " +
"(2, 5, 'F'), " +
"(1, 65, 'P'), " +
"(2, 197, 'P')");
// Test for unreferenced outputs
assertQuery(
"SELECT * FROM ( " +
" SELECT row_number() OVER (PARTITION BY orderstatus ORDER BY orderkey) rn, orderkey " +
" FROM orders " +
") WHERE rn <= 2",
"VALUES " +
"(1, 1), " +
"(2, 2), " +
"(1, 3), " +
"(2, 5), " +
"(1, 65), " +
"(2, 197)");
assertQuery(
"SELECT * FROM ( " +
" SELECT row_number() OVER (PARTITION BY orderstatus ORDER BY orderkey) rn, orderstatus " +
" FROM orders " +
") WHERE rn <= 2",
"VALUES " +
"(1, 'O'), " +
"(2, 'O'), " +
"(1, 'F'), " +
"(2, 'F'), " +
"(1, 'P'), " +
"(2, 'P')");
}
@Test
public void testTopNUnpartitionedWindowWithEqualityFilter()
{
assertQuery(
"SELECT * FROM ( " +
" SELECT row_number() OVER (ORDER BY orderkey) rn, orderkey, orderstatus " +
" FROM orders " +
") WHERE rn = 2",
"VALUES (2, 2, 'O')");
}
@Test
public void testTopNUnpartitionedWindowWithCompositeFilter()
{
assertQuery(
"SELECT * FROM ( " +
" SELECT row_number() OVER (ORDER BY orderkey) rn, orderkey, orderstatus " +
" FROM orders " +
") WHERE rn = 1 OR rn IN (3, 4) OR rn BETWEEN 6 AND 7",
"VALUES " +
"(1, 1, 'O'), " +
"(3, 3, 'F'), " +
"(4, 4, 'O'), " +
"(6, 6, 'F'), " +
"(7, 7, 'O')");
}
@Test
public void testTopNPartitionedWindowWithEqualityFilter()
{
assertQuery(
"SELECT * FROM ( " +
" SELECT row_number() OVER (PARTITION BY orderstatus ORDER BY orderkey) rn, orderkey, orderstatus " +
" FROM orders " +
") WHERE rn = 2",
"VALUES " +
"(2, 2, 'O'), " +
"(2, 5, 'F'), " +
"(2, 197, 'P')");
// Test for unreferenced outputs
assertQuery(
"SELECT * FROM ( " +
" SELECT row_number() OVER (PARTITION BY orderstatus ORDER BY orderkey) rn, orderkey " +
" FROM orders " +
") WHERE rn = 2",
"VALUES (2, 2), (2, 5), (2, 197)");
assertQuery(
"SELECT * FROM ( " +
" SELECT row_number() OVER (PARTITION BY orderstatus ORDER BY orderkey) rn, orderstatus " +
" FROM orders " +
") WHERE rn = 2",
"VALUES (2, 'O'), (2, 'F'), (2, 'P')");
}
@Test
public void testScalarFunction()
{
assertQuery("SELECT SUBSTR('Quadratically', 5, 6)");
}
@Test
public void testCast()
{
assertQuery("SELECT CAST('1' AS BIGINT)");
assertQuery("SELECT CAST(totalprice AS BIGINT) FROM orders");
assertQuery("SELECT CAST(orderkey AS DOUBLE) FROM orders");
assertQuery("SELECT CAST(orderkey AS VARCHAR) FROM orders");
assertQuery("SELECT CAST(orderkey AS BOOLEAN) FROM orders");
assertQuery("SELECT try_cast('1' AS BIGINT)", "SELECT CAST('1' AS BIGINT)");
assertQuery("SELECT try_cast(totalprice AS BIGINT) FROM orders", "SELECT CAST(totalprice AS BIGINT) FROM orders");
assertQuery("SELECT try_cast(orderkey AS DOUBLE) FROM orders", "SELECT CAST(orderkey AS DOUBLE) FROM orders");
assertQuery("SELECT try_cast(orderkey AS VARCHAR) FROM orders", "SELECT CAST(orderkey AS VARCHAR) FROM orders");
assertQuery("SELECT try_cast(orderkey AS BOOLEAN) FROM orders", "SELECT CAST(orderkey AS BOOLEAN) FROM orders");
assertQuery("SELECT try_cast('foo' AS BIGINT)", "SELECT CAST(null AS BIGINT)");
assertQuery("SELECT try_cast(clerk AS BIGINT) FROM orders", "SELECT CAST(null AS BIGINT) FROM orders");
assertQuery("SELECT try_cast(orderkey * orderkey AS VARCHAR) FROM orders", "SELECT CAST(orderkey * orderkey AS VARCHAR) FROM orders");
assertQuery("SELECT try_cast(try_cast(orderkey AS VARCHAR) AS BIGINT) FROM orders", "SELECT orderkey FROM orders");
assertQuery("SELECT try_cast(clerk AS VARCHAR) || try_cast(clerk AS VARCHAR) FROM orders", "SELECT clerk || clerk FROM orders");
assertQuery("SELECT coalesce(try_cast('foo' AS BIGINT), 456)", "SELECT 456");
assertQuery("SELECT coalesce(try_cast(clerk AS BIGINT), 456) FROM orders", "SELECT 456 FROM orders");
assertQuery("SELECT CAST(x AS BIGINT) FROM (VALUES 1, 2, 3, NULL) t (x)", "VALUES 1, 2, 3, NULL");
assertQuery("SELECT try_cast(x AS BIGINT) FROM (VALUES 1, 2, 3, NULL) t (x)", "VALUES 1, 2, 3, NULL");
}
@Test
public void testInvalidCast()
{
assertQueryFails(
"SELECT CAST(1 AS DATE)",
"line 1:8: Cannot cast integer to date");
}
@Test
public void testInvalidCastInMultilineQuery()
{
assertQueryFails(
"SELECT CAST(totalprice AS BIGINT),\n" +
"CAST(2015 AS DATE),\n" +
"CAST(orderkey AS DOUBLE) FROM orders",
"line 2:1: Cannot cast integer to date");
}
@Test
public void testTryInvalidCast()
{
assertQuery("SELECT TRY(CAST('a' AS BIGINT))",
"SELECT NULL");
}
@Test
public void testConcatOperator()
{
assertQuery("SELECT '12' || '34'");
}
@Test
public void testQuotedIdentifiers()
{
assertQuery("SELECT \"TOTALPRICE\" \"my price\" FROM \"ORDERS\"");
}
@Test
public void testInvalidColumn()
{
assertQueryFails(
"SELECT * FROM lineitem l JOIN (SELECT orderkey_1, custkey FROM orders) o on l.orderkey = o.orderkey_1",
"line 1:39: Column 'orderkey_1' cannot be resolved");
}
@Test
public void testUnaliasedSubqueries()
{
assertQuery("SELECT orderkey FROM (SELECT orderkey FROM orders)");
}
@Test
public void testUnaliasedSubqueries1()
{
assertQuery("SELECT a FROM (SELECT orderkey a FROM orders)");
}
@Test
public void testWith()
{
assertQuery("" +
"WITH a AS (SELECT * FROM orders) " +
"SELECT * FROM a",
"SELECT * FROM orders");
}
@Test
public void testWithQualifiedPrefix()
{
assertQuery("WITH a AS (SELECT 123) SELECT a.* FROM a", "SELECT 123");
}
@Test
public void testWithAliased()
{
assertQuery("WITH a AS (SELECT * FROM orders) SELECT * FROM a x", "SELECT * FROM orders");
}
@Test
public void testReferenceToWithQueryInFromClause()
{
assertQuery(
"WITH a AS (SELECT * FROM orders)" +
"SELECT * FROM (" +
" SELECT * FROM a" +
")",
"SELECT * FROM orders");
}
@Test
public void testWithChaining()
{
assertQuery("" +
"WITH a AS (SELECT orderkey n FROM orders)\n" +
", b AS (SELECT n + 1 n FROM a)\n" +
", c AS (SELECT n + 1 n FROM b)\n" +
"SELECT n + 1 FROM c",
"SELECT orderkey + 3 FROM orders");
}
@Test
public void testWithNestedSubqueries()
{
assertQuery("" +
"WITH a AS (\n" +
" WITH aa AS (SELECT 123 x FROM orders LIMIT 1)\n" +
" SELECT x y FROM aa\n" +
"), b AS (\n" +
" WITH bb AS (\n" +
" WITH bbb AS (SELECT y FROM a)\n" +
" SELECT bbb.* FROM bbb\n" +
" )\n" +
" SELECT y z FROM bb\n" +
")\n" +
"SELECT *\n" +
"FROM (\n" +
" WITH q AS (SELECT z w FROM b)\n" +
" SELECT j.*, k.*\n" +
" FROM a j\n" +
" JOIN q k ON (j.y = k.w)\n" +
") t", "" +
"SELECT 123, 123 FROM orders LIMIT 1");
}
@Test
public void testWithColumnAliasing()
{
assertQuery("WITH a (id) AS (SELECT 123) SELECT id FROM a", "SELECT 123");
assertQuery(
"WITH t (a, b, c) AS (SELECT 1, custkey x, orderkey FROM orders) SELECT c, b, a FROM t",
"SELECT orderkey, custkey, 1 FROM orders");
}
@Test
public void testWithHiding()
{
assertQuery("" +
"WITH a AS (SELECT 1), " +
" b AS (" +
" WITH a AS (SELECT 2)" +
" SELECT * FROM a" +
" )" +
"SELECT * FROM b",
"SELECT 2");
assertQueryFails(
"WITH a AS (VALUES 1), " +
" a AS (VALUES 2)" +
"SELECT * FROM a",
"line 1:28: WITH query name 'a' specified more than once");
}
@Test
public void testWithRecursive()
{
assertQueryFails(
"WITH RECURSIVE a AS (SELECT 123) SELECT * FROM a",
"line 1:1: Recursive WITH queries are not supported");
}
@Test
public void testCaseNoElse()
{
assertQuery("SELECT orderkey, CASE orderstatus WHEN 'O' THEN 'a' END FROM orders");
}
@Test
public void testCaseNoElseInconsistentResultType()
{
assertQueryFails(
"SELECT orderkey, CASE orderstatus WHEN 'O' THEN 'a' WHEN '1' THEN 2 END FROM orders",
"\\Qline 1:67: All CASE results must be the same type: varchar(1)\\E");
}
@Test
public void testCaseWithSupertypeCast()
{
assertQuery(" SELECT CASE x WHEN 1 THEN CAST(1 AS decimal(4,1)) WHEN 2 THEN CAST(1 AS decimal(4,2)) ELSE CAST(1 AS decimal(4,3)) END FROM (values 1) t(x)", "SELECT 1.000");
}
@Test
public void testIfExpression()
{
assertQuery(
"SELECT sum(IF(orderstatus = 'F', totalprice, 0.0)) FROM orders",
"SELECT sum(CASE WHEN orderstatus = 'F' THEN totalprice ELSE 0.0 END) FROM orders");
assertQuery(
"SELECT sum(IF(orderstatus = 'Z', totalprice)) FROM orders",
"SELECT sum(CASE WHEN orderstatus = 'Z' THEN totalprice END) FROM orders");
assertQuery(
"SELECT sum(IF(orderstatus = 'F', NULL, totalprice)) FROM orders",
"SELECT sum(CASE WHEN orderstatus = 'F' THEN NULL ELSE totalprice END) FROM orders");
assertQuery(
"SELECT IF(orderstatus = 'Z', orderkey / 0, orderkey) FROM orders",
"SELECT CASE WHEN orderstatus = 'Z' THEN orderkey / 0 ELSE orderkey END FROM orders");
assertQuery(
"SELECT sum(IF(NULLIF(orderstatus, 'F') <> 'F', totalprice, 5.1)) FROM orders",
"SELECT sum(CASE WHEN NULLIF(orderstatus, 'F') <> 'F' THEN totalprice ELSE 5.1 END) FROM orders");
// coercions to supertype
assertQuery("SELECT if(true, CAST(1 AS decimal(2,1)), 1)", "SELECT 1.0");
}
@Test
public void testIn()
{
assertQuery("SELECT orderkey FROM orders WHERE orderkey IN (1, 2, 3)");
assertQuery("SELECT orderkey FROM orders WHERE orderkey IN (1.5, 2.3)", "SELECT orderkey FROM orders LIMIT 0"); // H2 incorrectly matches rows
assertQuery("SELECT orderkey FROM orders WHERE orderkey IN (1, 2E0, 3)");
assertQuery("SELECT orderkey FROM orders WHERE totalprice IN (1, 2, 3)");
assertQuery("SELECT x FROM (values 3, 100) t(x) WHERE x IN (2147483649)", "SELECT * WHERE false");
assertQuery("SELECT x FROM (values 3, 100, 2147483648, 2147483649, 2147483650) t(x) WHERE x IN (2147483648, 2147483650)", "values 2147483648, 2147483650");
assertQuery("SELECT x FROM (values 3, 100, 2147483648, 2147483649, 2147483650) t(x) WHERE x IN (3, 4, 2147483648, 2147483650)", "values 3, 2147483648, 2147483650");
assertQuery("SELECT x FROM (values 1, 2, 3) t(x) WHERE x IN (1 + CAST(rand() < 0 AS bigint), 2 + CAST(rand() < 0 AS bigint))", "values 1, 2");
assertQuery("SELECT x FROM (values 1, 2, 3, 4) t(x) WHERE x IN (1 + CAST(rand() < 0 AS bigint), 2 + CAST(rand() < 0 AS bigint), 4)", "values 1, 2, 4");
assertQuery("SELECT x FROM (values 1, 2, 3, 4) t(x) WHERE x IN (4, 2, 1)", "values 1, 2, 4");
assertQuery("SELECT x FROM (values 1, 2, 3, 2147483648) t(x) WHERE x IN (1 + CAST(rand() < 0 AS bigint), 2 + CAST(rand() < 0 AS bigint), 2147483648)", "values 1, 2, 2147483648");
assertQuery("SELECT x IN (0) FROM (values 4294967296) t(x)", "values false");
assertQuery("SELECT x IN (0, 4294967297 + CAST(rand() < 0 AS bigint)) FROM (values 4294967296, 4294967297) t(x)", "values false, true");
assertQuery("SELECT NULL in (1, 2, 3)", "values null");
assertQuery("SELECT 1 in (1, NULL, 3)", "values true");
assertQuery("SELECT 2 in (1, NULL, 3)", "values null");
assertQuery("SELECT x FROM (values DATE '1970-01-01', DATE '1970-01-03') t(x) WHERE x IN (DATE '1970-01-01')", "values DATE '1970-01-01'");
assertEquals(
computeActual("SELECT x FROM (values TIMESTAMP '1970-01-01 00:01:00+00:00', TIMESTAMP '1970-01-01 08:01:00+08:00', TIMESTAMP '1970-01-01 00:01:00+08:00') t(x) WHERE x IN (TIMESTAMP '1970-01-01 00:01:00+00:00')")
.getOnlyColumn().collect(toList()),
ImmutableList.of(zonedDateTime("1970-01-01 00:01:00.000 UTC"), zonedDateTime("1970-01-01 08:01:00.000 +08:00")));
assertQuery("SELECT COUNT(*) FROM (values 1) t(x) WHERE x IN (null, 0)", "SELECT 0");
assertQuery("SELECT d IN (DECIMAL '2.0', DECIMAL '30.0') FROM (VALUES (2.0E0)) t(d)", "SELECT true"); // coercion with type only coercion inside IN list
}
@Test
public void testLargeIn()
{
String longValues = range(0, 5000)
.mapToObj(Integer::toString)
.collect(joining(", "));
assertQuery("SELECT orderkey FROM orders WHERE orderkey IN (" + longValues + ")");
assertQuery("SELECT orderkey FROM orders WHERE orderkey NOT IN (" + longValues + ")");
assertQuery("SELECT orderkey FROM orders WHERE orderkey IN (mod(1000, orderkey), " + longValues + ")");
assertQuery("SELECT orderkey FROM orders WHERE orderkey NOT IN (mod(1000, orderkey), " + longValues + ")");
String arrayValues = range(0, 5000)
.mapToObj(i -> format("ARRAY[%s, %s, %s]", i, i + 1, i + 2))
.collect(joining(", "));
assertQuery("SELECT ARRAY[0, 0, 0] in (ARRAY[0, 0, 0], " + arrayValues + ")", "values true");
assertQuery("SELECT ARRAY[0, 0, 0] in (" + arrayValues + ")", "values false");
}
@Test
public void testNullOnLhsOfInPredicateAllowed()
{
assertQuery("SELECT NULL IN (1, 2, 3)", "SELECT NULL");
assertQuery("SELECT NULL IN (SELECT 1)", "SELECT NULL");
assertQuery("SELECT NULL IN (SELECT 1 WHERE FALSE)", "SELECT FALSE");
assertQuery("SELECT x FROM (VALUES NULL) t(x) WHERE x IN (SELECT 1)", "SELECT 33 WHERE FALSE");
assertQuery("SELECT NULL IN (SELECT CAST(NULL AS BIGINT))", "SELECT NULL");
assertQuery("SELECT NULL IN (SELECT NULL WHERE FALSE)", "SELECT FALSE");
assertQuery("SELECT NULL IN ((SELECT 1) UNION ALL (SELECT NULL))", "SELECT NULL");
assertQuery("SELECT x IN (SELECT TRUE) FROM (SELECT * FROM (VALUES CAST(NULL AS BOOLEAN)) t(x) WHERE (x OR NULL) IS NULL)", "SELECT NULL");
assertQuery("SELECT x IN (SELECT 1) FROM (SELECT * FROM (VALUES CAST(NULL AS INTEGER)) t(x) WHERE (x + 10 IS NULL) OR X = 2)", "SELECT NULL");
assertQuery("SELECT x IN (SELECT 1 WHERE FALSE) FROM (SELECT * FROM (VALUES CAST(NULL AS INTEGER)) t(x) WHERE (x + 10 IS NULL) OR X = 2)", "SELECT FALSE");
}
@Test
public void testInSubqueryWithCrossJoin()
{
assertQuery("SELECT a FROM (VALUES (1),(2)) t(a) WHERE a IN " +
"(SELECT b FROM (VALUES (ARRAY[2])) AS t1 (a) CROSS JOIN UNNEST(a) AS t2(b))", "SELECT 2");
}
@Test
public void testDuplicateFields()
{
assertQuery(
"SELECT * FROM (SELECT orderkey, orderkey FROM orders)",
"SELECT orderkey, orderkey FROM orders");
}
@Test
public void testWildcardFromSubquery()
{
assertQuery("SELECT * FROM (SELECT orderkey X FROM orders)");
}
@Test
public void testCaseInsensitiveAttribute()
{
assertQuery("SELECT x FROM (SELECT orderkey X FROM orders)");
}
@Test
public void testCaseInsensitiveAliasedRelation()
{
assertQuery("SELECT A.* FROM orders a");
}
@Test
public void testCaseInsensitiveRowFieldReference()
{
assertQuery("SELECT a.Col0 FROM (VALUES row(cast(ROW(1,2) AS ROW(col0 integer, col1 integer)))) AS t (a)", "SELECT 1");
}
@Test
public void testSubqueryBody()
{
assertQuery("(SELECT orderkey, custkey FROM orders)");
}
@Test
public void testSubqueryBodyOrderLimit()
{
assertQueryOrdered("(SELECT orderkey AS a, custkey AS b FROM orders) ORDER BY a LIMIT 1");
}
@Test
public void testSubqueryBodyProjectedOrderby()
{
assertQueryOrdered("(SELECT orderkey, custkey FROM orders) ORDER BY orderkey * -1");
}
@Test
public void testSubqueryBodyDoubleOrderby()
{
assertQueryOrdered("(SELECT orderkey, custkey FROM orders ORDER BY custkey) ORDER BY orderkey");
}
@Test
public void testNodeRoster()
{
List result = computeActual("SELECT * FROM system.runtime.nodes").getMaterializedRows();
assertEquals(result.size(), getNodeCount());
}
@Test
public void testCountOnInternalTables()
{
List rows = computeActual("SELECT count(*) FROM system.runtime.nodes").getMaterializedRows();
assertEquals(((Long) rows.get(0).getField(0)).longValue(), getNodeCount());
}
@Test
public void testTransactionsTable()
{
List result = computeActual("SELECT * FROM system.runtime.transactions").getMaterializedRows();
assertTrue(result.size() >= 1); // At least one row for the current transaction.
}
@Test
public void testDefaultExplainTextFormat()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL));
}
@Test
public void testDefaultExplainGraphvizFormat()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN (FORMAT GRAPHVIZ) " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getGraphvizExplainPlan(query, LOGICAL));
}
@Test
public void testLogicalExplain()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN (TYPE LOGICAL) " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL));
}
@Test
public void testIOExplain()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN (TYPE IO) " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, IO));
}
@Test
public void testLogicalExplainTextFormat()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN (TYPE LOGICAL, FORMAT TEXT) " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL));
}
@Test
public void testLogicalExplainGraphvizFormat()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN (TYPE LOGICAL, FORMAT GRAPHVIZ) " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getGraphvizExplainPlan(query, LOGICAL));
}
@Test
public void testDistributedExplain()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN (TYPE DISTRIBUTED) " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, DISTRIBUTED));
}
@Test
public void testDistributedExplainTextFormat()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN (TYPE DISTRIBUTED, FORMAT TEXT) " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, DISTRIBUTED));
}
@Test
public void testDistributedExplainGraphvizFormat()
{
String query = "SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN (TYPE DISTRIBUTED, FORMAT GRAPHVIZ) " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getGraphvizExplainPlan(query, DISTRIBUTED));
}
@Test
public void testExplainValidate()
{
MaterializedResult result = computeActual("EXPLAIN (TYPE VALIDATE) SELECT 1");
assertEquals(result.getOnlyValue(), true);
}
@Test(expectedExceptions = Exception.class, expectedExceptionsMessageRegExp = "line 1:32: Column 'x' cannot be resolved")
public void testExplainValidateThrows()
{
computeActual("EXPLAIN (TYPE VALIDATE) SELECT x");
}
@Test
public void testExplainOfExplain()
{
String query = "EXPLAIN SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL));
}
@Test
public void testExplainOfExplainAnalyze()
{
String query = "EXPLAIN ANALYZE SELECT * FROM orders";
MaterializedResult result = computeActual("EXPLAIN " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan(query, LOGICAL));
}
@Test
public void testExplainDdl()
{
assertExplainDdl("CREATE TABLE foo (pk bigint)", "CREATE TABLE foo");
assertExplainDdl("CREATE VIEW foo AS SELECT * FROM orders", "CREATE VIEW foo");
assertExplainDdl("DROP TABLE orders");
assertExplainDdl("DROP VIEW view");
assertExplainDdl("ALTER TABLE orders RENAME TO new_name");
assertExplainDdl("ALTER TABLE orders RENAME COLUMN orderkey TO new_column_name");
assertExplainDdl("SET SESSION foo = 'bar'");
assertExplainDdl("PREPARE my_query FROM SELECT * FROM orders", "PREPARE my_query");
assertExplainDdl("DEALLOCATE PREPARE my_query");
assertExplainDdl("RESET SESSION foo");
assertExplainDdl("START TRANSACTION");
assertExplainDdl("COMMIT");
assertExplainDdl("ROLLBACK");
}
private void assertExplainDdl(String query)
{
assertExplainDdl(query, query);
}
private void assertExplainDdl(String query, String expected)
{
MaterializedResult result = computeActual("EXPLAIN " + query);
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), expected);
}
@Test
public void testExplainExecute()
{
Session session = Session.builder(getSession())
.addPreparedStatement("my_query", "SELECT * FROM orders")
.build();
MaterializedResult result = computeActual(session, "EXPLAIN (TYPE LOGICAL) EXECUTE my_query");
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan("SELECT * FROM orders", LOGICAL));
}
@Test
public void testExplainExecuteWithUsing()
{
Session session = Session.builder(getSession())
.addPreparedStatement("my_query", "SELECT * FROM orders WHERE orderkey < ?")
.build();
MaterializedResult result = computeActual(session, "EXPLAIN (TYPE LOGICAL) EXECUTE my_query USING 7");
assertEquals(getOnlyElement(result.getOnlyColumnAsSet()), getExplainPlan("SELECT * FROM orders WHERE orderkey < 7", LOGICAL));
}
@Test
public void testExplainSetSessionWithUsing()
{
Session session = Session.builder(getSession())
.addPreparedStatement("my_query", "SET SESSION foo = ?")
.build();
MaterializedResult result = computeActual(session, "EXPLAIN (TYPE LOGICAL) EXECUTE my_query USING 7");
assertEquals(
getOnlyElement(result.getOnlyColumnAsSet()),
"SET SESSION foo = ?\n" +
"Parameters: [7]");
}
@Test
public void testShowCatalogs()
{
MaterializedResult result = computeActual("SHOW CATALOGS");
assertTrue(result.getOnlyColumnAsSet().contains(getSession().getCatalog().get()));
}
@Test
public void testShowCatalogsLike()
{
MaterializedResult result = computeActual(format("SHOW CATALOGS LIKE '%s'", getSession().getCatalog().get()));
assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of(getSession().getCatalog().get()));
}
@Test
public void testShowSchemas()
{
MaterializedResult result = computeActual("SHOW SCHEMAS");
assertTrue(result.getOnlyColumnAsSet().containsAll(ImmutableSet.of(getSession().getSchema().get(), INFORMATION_SCHEMA)));
}
@Test
public void testShowSchemasFrom()
{
MaterializedResult result = computeActual(format("SHOW SCHEMAS FROM %s", getSession().getCatalog().get()));
assertTrue(result.getOnlyColumnAsSet().containsAll(ImmutableSet.of(getSession().getSchema().get(), INFORMATION_SCHEMA)));
}
@Test
public void testShowSchemasLike()
{
MaterializedResult result = computeActual(format("SHOW SCHEMAS LIKE '%s'", getSession().getSchema().get()));
assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of(getSession().getSchema().get()));
}
@Test
public void testShowSchemasLikeWithEscape()
{
assertQueryFails("SHOW SCHEMAS IN foo LIKE '%$_%' ESCAPE", "line 1:39: mismatched input ''. Expecting: ");
assertQueryFails("SHOW SCHEMAS LIKE 't$_%' ESCAPE ''", "Escape string must be a single character");
assertQueryFails("SHOW SCHEMAS LIKE 't$_%' ESCAPE '$$'", "Escape string must be a single character");
Set
