Java tutorial
/* * 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 com.facebook.presto; import com.facebook.presto.connector.dual.DualMetadata; import com.facebook.presto.importer.MockPeriodicImportManager; import com.facebook.presto.metadata.MetadataManager; import com.facebook.presto.spi.ColumnMetadata; import com.facebook.presto.spi.RecordCursor; import com.facebook.presto.spi.RecordSet; import com.facebook.presto.spi.TableMetadata; import com.facebook.presto.sql.analyzer.QueryExplainer; import com.facebook.presto.sql.analyzer.Session; import com.facebook.presto.sql.parser.SqlParser; import com.facebook.presto.sql.planner.PlanOptimizersFactory; import com.facebook.presto.sql.planner.optimizations.PlanOptimizer; import com.facebook.presto.sql.tree.ExplainType; import com.facebook.presto.sql.tree.Query; import com.facebook.presto.storage.MockStorageManager; import com.facebook.presto.tpch.TpchMetadata; import com.facebook.presto.tuple.Tuple; import com.facebook.presto.tuple.TupleInfo; import com.facebook.presto.util.MaterializedResult; import com.facebook.presto.util.MaterializedTuple; import com.google.common.base.Function; import com.google.common.base.Joiner; import com.google.common.collect.ArrayListMultimap; import com.google.common.collect.ImmutableMultimap; import com.google.common.collect.ImmutableMultiset; import com.google.common.collect.ImmutableSet; import com.google.common.collect.Iterables; import com.google.common.collect.Multimap; import com.google.common.collect.Multimaps; import com.google.common.collect.Ordering; import io.airlift.log.Logger; import io.airlift.log.Logging; import io.airlift.slice.Slices; import io.airlift.units.Duration; import org.apache.commons.math.stat.descriptive.DescriptiveStatistics; import org.intellij.lang.annotations.Language; import org.skife.jdbi.v2.DBI; import org.skife.jdbi.v2.Handle; import org.skife.jdbi.v2.PreparedBatch; import org.skife.jdbi.v2.PreparedBatchPart; import org.skife.jdbi.v2.StatementContext; import org.skife.jdbi.v2.tweak.ResultSetMapper; import org.testng.Assert; import org.testng.annotations.AfterClass; import org.testng.annotations.BeforeClass; import org.testng.annotations.Test; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import static com.facebook.presto.connector.informationSchema.InformationSchemaMetadata.INFORMATION_SCHEMA; import static com.facebook.presto.sql.analyzer.Session.DEFAULT_CATALOG; import static com.facebook.presto.sql.analyzer.Session.DEFAULT_SCHEMA; import static com.facebook.presto.sql.tree.ExplainType.Type.DISTRIBUTED; import static com.facebook.presto.sql.tree.ExplainType.Type.LOGICAL; import static com.facebook.presto.tpch.TpchMetadata.TPCH_LINEITEM_METADATA; import static com.facebook.presto.tpch.TpchMetadata.TPCH_LINEITEM_NAME; import static com.facebook.presto.tpch.TpchMetadata.TPCH_ORDERS_METADATA; import static com.facebook.presto.tpch.TpchMetadata.TPCH_ORDERS_NAME; import static com.facebook.presto.tpch.TpchMetadata.TPCH_SCHEMA_NAME; import static com.facebook.presto.tuple.TupleInfo.Type.BOOLEAN; import static com.facebook.presto.tuple.TupleInfo.Type.DOUBLE; import static com.facebook.presto.tuple.TupleInfo.Type.FIXED_INT_64; import static com.facebook.presto.tuple.TupleInfo.Type.VARIABLE_BINARY; import static com.facebook.presto.util.InMemoryTpchBlocksProvider.readTpchRecords; import static com.facebook.presto.util.MaterializedResult.resultBuilder; import static com.google.common.base.Charsets.UTF_8; import static com.google.common.base.Preconditions.checkArgument; import static com.google.common.collect.Iterables.transform; import static java.lang.String.format; import static java.util.Collections.nCopies; import static org.testng.Assert.assertEquals; import static org.testng.Assert.assertNotNull; import static org.testng.Assert.assertTrue; import static org.testng.Assert.fail; public abstract class AbstractTestQueries { private Handle handle; @Test void testSpecialFloatingPointValues() throws Exception { MaterializedResult actual = computeActual("SELECT nan(), infinity(), -infinity()"); MaterializedTuple tuple = Iterables.getOnlyElement(actual.getMaterializedTuples()); assertEquals(tuple.getField(0), Double.NaN); assertEquals(tuple.getField(1), Double.POSITIVE_INFINITY); assertEquals(tuple.getField(2), Double.NEGATIVE_INFINITY); } @Test public void testMaxMinStringWithNulls() throws Exception { assertQuery( "SELECT custkey, MAX(NULLIF(orderstatus, 'O')), MIN(NULLIF(orderstatus, 'O')) FROM orders GROUP BY custkey"); } @Test public void testApproxPercentile() throws Exception { MaterializedResult raw = computeActual("SELECT orderstatus, orderkey, totalprice FROM ORDERS"); Multimap<String, Long> orderKeyByStatus = ArrayListMultimap.create(); Multimap<String, Double> totalPriceByStatus = ArrayListMultimap.create(); for (MaterializedTuple tuple : raw.getMaterializedTuples()) { orderKeyByStatus.put((String) tuple.getField(0), (Long) tuple.getField(1)); totalPriceByStatus.put((String) tuple.getField(0), (Double) tuple.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)\n" + "FROM ORDERS\n" + "GROUP BY orderstatus"); for (MaterializedTuple tuple : actual.getMaterializedTuples()) { String status = (String) tuple.getField(0); Long orderKey = (Long) tuple.getField(1); Double totalPrice = (Double) tuple.getField(2); Long orderKeyWeighted = (Long) tuple.getField(3); Double totalPriceWeighted = (Double) tuple.getField(4); List<Long> orderKeys = Ordering.natural().sortedCopy(orderKeyByStatus.get(status)); List<Double> 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(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()))); } } @Test public void testComplexQuery() throws Exception { MaterializedResult actual = computeActual("SELECT sum(orderkey), row_number() OVER (ORDER BY orderkey)\n" + "FROM orders\n" + "WHERE orderkey <= 10\n" + "GROUP BY orderkey\n" + "HAVING sum(orderkey) >= 3\n" + "ORDER BY orderkey DESC\n" + "LIMIT 3"); MaterializedResult expected = resultBuilder(FIXED_INT_64, FIXED_INT_64).row(7, 5).row(6, 4).row(5, 3) .build(); assertEquals(actual, expected); } @Test public void testWhereNull() throws Exception { // 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 testSumOfNulls() throws Exception { assertQuery("SELECT orderstatus, sum(CAST(NULL AS BIGINT)) FROM orders GROUP BY orderstatus"); } @Test public void testApproximateCountDistinct() throws Exception { MaterializedResult actual = computeActual("SELECT approx_distinct(custkey) FROM orders"); MaterializedResult expected = resultBuilder(FIXED_INT_64).row(971).build(); assertEqualsIgnoreOrder(actual.getMaterializedTuples(), expected.getMaterializedTuples()); } @Test public void testApproximateCountDistinctGroupBy() throws Exception { MaterializedResult actual = computeActual( "SELECT orderstatus, approx_distinct(custkey) FROM orders GROUP BY orderstatus"); MaterializedResult expected = resultBuilder(actual.getTupleInfo()).row("O", 969).row("F", 964).row("P", 301) .build(); assertEqualsIgnoreOrder(actual.getMaterializedTuples(), expected.getMaterializedTuples()); } @Test public void testCountBoolean() throws Exception { assertQuery("SELECT COUNT(true) FROM orders"); } @Test public void testJoinWithMultiFieldGroupBy() throws Exception { assertQuery( "SELECT orderstatus FROM lineitem JOIN (SELECT DISTINCT orderkey, orderstatus FROM ORDERS) T on lineitem.orderkey = T.orderkey"); } @Test public void testGroupByRepeatedField() throws Exception { assertQuery("SELECT sum(custkey) FROM orders GROUP BY orderstatus, orderstatus"); } @Test public void testGroupByRepeatedField2() throws Exception { assertQuery("SELECT count(*) FROM (select orderstatus a, orderstatus b FROM orders) GROUP BY a, b"); } @Test public void testGroupByMultipleFieldsWithPredicateOnAggregationArgument() throws Exception { assertQuery( "SELECT custkey, orderstatus, MAX(orderkey) FROM ORDERS WHERE orderkey = 1 GROUP BY custkey, orderstatus"); } @Test public void testReorderOutputsOfGroupByAggregation() throws Exception { assertQuery( "SELECT orderstatus, a, custkey, b FROM (SELECT custkey, orderstatus, -COUNT(*) a, MAX(orderkey) b FROM ORDERS WHERE orderkey = 1 GROUP BY custkey, orderstatus) T"); } @Test public void testGroupAggregationOverNestedGroupByAggregation() throws Exception { assertQuery( "SELECT sum(custkey), max(orderstatus), min(c) FROM (SELECT orderstatus, custkey, COUNT(*) c FROM ORDERS GROUP BY orderstatus, custkey) T"); } @Test public void testDistinctMultipleFields() throws Exception { assertQuery("SELECT DISTINCT custkey, orderstatus FROM ORDERS"); } @Test public void testArithmeticNegation() throws Exception { assertQuery("SELECT -custkey FROM orders"); } @Test public void testDistinct() throws Exception { assertQuery("SELECT DISTINCT custkey FROM orders"); } // TODO: we need to properly propagate exceptions with their actual classes @Test(expectedExceptions = Exception.class, expectedExceptionsMessageRegExp = "DISTINCT in aggregation parameters not yet supported") public void testCountDistinct() throws Exception { assertQuery("SELECT COUNT(DISTINCT custkey) FROM orders"); } @Test public void testDistinctWithOrderBy() throws Exception { assertQueryOrdered("SELECT DISTINCT custkey FROM orders ORDER BY custkey LIMIT 10"); } @Test(expectedExceptions = Exception.class, expectedExceptionsMessageRegExp = "For SELECT DISTINCT, ORDER BY expressions must appear in select list") public void testDistinctWithOrderByNotInSelect() throws Exception { assertQueryOrdered("SELECT DISTINCT custkey FROM orders ORDER BY orderkey LIMIT 10"); } @Test public void testOrderByLimit() throws Exception { assertQueryOrdered("SELECT custkey, orderstatus FROM ORDERS ORDER BY orderkey DESC LIMIT 10"); } @Test public void testOrderByExpressionWithLimit() throws Exception { assertQueryOrdered("SELECT custkey, orderstatus FROM ORDERS ORDER BY orderkey + 1 DESC LIMIT 10"); } @Test public void testGroupByOrderByLimit() throws Exception { assertQueryOrdered( "SELECT custkey, SUM(totalprice) FROM ORDERS GROUP BY custkey ORDER BY SUM(totalprice) DESC LIMIT 10"); } @Test public void testLimitZero() throws Exception { assertQuery("SELECT custkey, totalprice FROM orders LIMIT 0"); } @Test public void testRepeatedAggregations() throws Exception { assertQuery("SELECT SUM(orderkey), SUM(orderkey) FROM ORDERS"); } @Test public void testRepeatedOutputs() throws Exception { assertQuery("SELECT orderkey a, orderkey b FROM ORDERS WHERE orderstatus = 'F'"); } @Test public void testLimit() throws Exception { MaterializedResult actual = computeActual("SELECT orderkey FROM ORDERS LIMIT 10"); MaterializedResult all = computeExpected("SELECT orderkey FROM ORDERS", actual.getTupleInfo()); assertEquals(actual.getMaterializedTuples().size(), 10); assertTrue(all.getMaterializedTuples().containsAll(actual.getMaterializedTuples())); } @Test public void testAggregationWithLimit() throws Exception { MaterializedResult actual = computeActual( "SELECT custkey, SUM(totalprice) FROM ORDERS GROUP BY custkey LIMIT 10"); MaterializedResult all = computeExpected("SELECT custkey, SUM(totalprice) FROM ORDERS GROUP BY custkey", actual.getTupleInfo()); assertEquals(actual.getMaterializedTuples().size(), 10); assertTrue(all.getMaterializedTuples().containsAll(actual.getMaterializedTuples())); } @Test public void testLimitInInlineView() throws Exception { MaterializedResult actual = computeActual( "SELECT orderkey FROM (SELECT orderkey FROM ORDERS LIMIT 100) T LIMIT 10"); MaterializedResult all = computeExpected("SELECT orderkey FROM ORDERS", actual.getTupleInfo()); assertEquals(actual.getMaterializedTuples().size(), 10); assertTrue(all.getMaterializedTuples().containsAll(actual.getMaterializedTuples())); } @Test public void testCountAll() throws Exception { assertQuery("SELECT COUNT(*) FROM ORDERS"); } @Test public void testCountColumn() throws Exception { 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() throws Exception { assertQuery("SELECT * FROM ORDERS"); } @Test public void testMultipleWildcards() throws Exception { assertQuery("SELECT *, 123, * FROM ORDERS"); } @Test public void testMixedWildcards() throws Exception { assertQuery("SELECT *, orders.*, orderkey FROM orders"); } @Test public void testQualifiedWildcardFromAlias() throws Exception { assertQuery("SELECT T.* FROM ORDERS T"); } @Test public void testQualifiedWildcardFromInlineView() throws Exception { assertQuery("SELECT T.* FROM (SELECT orderkey + custkey FROM ORDERS) T"); } @Test public void testQualifiedWildcard() throws Exception { assertQuery("SELECT ORDERS.* FROM ORDERS"); } @Test public void testAverageAll() throws Exception { assertQuery("SELECT AVG(totalprice) FROM ORDERS"); } @Test public void testVariance() throws Exception { // 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() throws Exception { // 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() throws Exception { // 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() throws Exception { // 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 testCountAllWithPredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM ORDERS WHERE orderstatus = 'F'"); } @Test public void testGroupByNoAggregations() throws Exception { assertQuery("SELECT custkey FROM ORDERS GROUP BY custkey"); } @Test public void testGroupByCount() throws Exception { assertQuery("SELECT orderstatus, COUNT(*) FROM ORDERS GROUP BY orderstatus", "SELECT orderstatus, CAST(COUNT(*) AS INTEGER) FROM orders GROUP BY orderstatus"); } @Test public void testGroupByMultipleFields() throws Exception { assertQuery("SELECT custkey, orderstatus, COUNT(*) FROM ORDERS GROUP BY custkey, orderstatus"); } @Test public void testGroupByWithAlias() throws Exception { assertQuery("SELECT orderdate x, COUNT(*) FROM orders GROUP BY orderdate", "SELECT orderdate x, CAST(COUNT(*) AS INTEGER) FROM orders GROUP BY orderdate"); } @Test public void testGroupBySum() throws Exception { assertQuery("SELECT orderstatus, SUM(totalprice) FROM ORDERS GROUP BY orderstatus"); } @Test public void testGroupByWithWildcard() throws Exception { assertQuery("SELECT * FROM (SELECT orderkey FROM orders) t GROUP BY orderkey"); } @Test public void testCountAllWithComparison() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem WHERE tax < discount"); } @Test public void testSelectWithComparison() throws Exception { assertQuery("SELECT orderkey FROM lineitem WHERE tax < discount"); } @Test public void testCountWithNotPredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem WHERE NOT tax < discount"); } @Test public void testCountWithNullPredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem WHERE NULL"); } @Test public void testCountWithIsNullPredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM orders WHERE NULLIF(orderstatus, 'F') IS NULL", "SELECT COUNT(*) FROM orders WHERE orderstatus = 'F' "); } @Test public void testCountWithIsNotNullPredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM orders WHERE NULLIF(orderstatus, 'F') IS NOT NULL", "SELECT COUNT(*) FROM orders WHERE orderstatus <> 'F' "); } @Test public void testCountWithNullIfPredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM orders WHERE NULLIF(orderstatus, 'F') = orderstatus "); } @Test public void testCountWithCoalescePredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM orders WHERE COALESCE(NULLIF(orderstatus, 'F'), 'bar') = 'bar'", "SELECT COUNT(*) FROM orders WHERE orderstatus = 'F'"); } @Test public void testCountWithAndPredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem WHERE tax < discount AND tax > 0.01 AND discount < 0.05"); } @Test public void testCountWithOrPredicate() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem WHERE tax < 0.01 OR discount > 0.05"); } @Test public void testCountWithInlineView() throws Exception { assertQuery("SELECT COUNT(*) FROM (SELECT orderkey FROM lineitem) x"); } @Test public void testNestedCount() throws Exception { assertQuery("SELECT COUNT(*) FROM (SELECT orderkey, COUNT(*) FROM lineitem GROUP BY orderkey) x"); } @Test public void testAggregationWithProjection() throws Exception { assertQuery("SELECT sum(totalprice * 2) - sum(totalprice) FROM orders"); } @Test public void testAggregationWithProjection2() throws Exception { assertQuery("SELECT sum(totalprice * 2) + sum(totalprice * 2) FROM orders"); } @Test public void testInlineView() throws Exception { assertQuery("SELECT orderkey, custkey FROM (SELECT orderkey, custkey FROM ORDERS) U"); } @Test public void testAliasedInInlineView() throws Exception { assertQuery("SELECT x, y FROM (SELECT orderkey x, custkey y FROM ORDERS) U"); } @Test public void testInlineViewWithProjections() throws Exception { assertQuery("SELECT x + 1, y FROM (SELECT orderkey * 10 x, custkey y FROM ORDERS) u"); } @Test public void testGroupByWithoutAggregation() throws Exception { assertQuery("SELECT orderstatus FROM orders GROUP BY orderstatus"); } @Test public void testHistogram() throws Exception { assertQuery( "SELECT lines, COUNT(*) FROM (SELECT orderkey, COUNT(*) lines FROM lineitem GROUP BY orderkey) U GROUP BY lines"); } @Test public void testSimpleJoin() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey"); } @Test public void testJoinWithRightConstantEquality() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = 2"); } @Test public void testJoinWithLeftConstantEquality() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem JOIN orders ON orders.orderkey = 2"); } @Test public void testSimpleJoinWithLeftConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey AND orders.orderkey = 2"); } @Test public void testSimpleJoinWithRightConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.orderkey = 2"); } @Test public void testJoinDoubleClauseWithLeftOverlap() throws Exception { // Checks to make sure that we properly handle duplicate field references in join clauses assertQuery( "SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.orderkey = orders.custkey"); } @Test public void testJoinDoubleClauseWithRightOverlap() throws Exception { // Checks to make sure that we properly handle duplicate field references in join clauses assertQuery( "SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey AND orders.orderkey = lineitem.partkey"); } @Test public void testJoinWithAlias() throws Exception { assertQuery("SELECT * FROM (lineitem JOIN orders ON lineitem.orderkey = orders.orderkey) x"); } @Test public void testJoinWithConstantExpression() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey AND 123 = 123"); } @Test(expectedExceptions = Exception.class, expectedExceptionsMessageRegExp = ".*not supported.*") public void testJoinOnConstantExpression() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem JOIN orders ON 123 = 123"); } @Test public void testJoinUsing() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem join orders using (orderkey)", "SELECT COUNT(*) FROM lineitem join orders on lineitem.orderkey = orders.orderkey"); } @Test public void testJoinWithReversedComparison() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem JOIN orders ON orders.orderkey = lineitem.orderkey"); } @Test public void testJoinWithComplexExpressions() throws Exception { assertQuery( "SELECT SUM(custkey) FROM lineitem JOIN orders ON lineitem.orderkey = CAST(orders.orderkey AS BIGINT)"); } @Test public void testJoinWithComplexExpressions2() throws Exception { assertQuery( "SELECT SUM(custkey) FROM lineitem JOIN orders ON lineitem.orderkey = CASE WHEN orders.custkey = 1 and orders.orderstatus = 'F' THEN orders.orderkey ELSE NULL END"); } @Test public void testJoinWithComplexExpressions3() throws Exception { assertQuery("SELECT SUM(custkey) FROM lineitem JOIN orders ON lineitem.orderkey + 1 = orders.orderkey + 1", "SELECT SUM(custkey) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey " // H2 takes a million years because it can't join efficiently on a non-indexed field/expression ); } @Test public void testSelfJoin() throws Exception { assertQuery("SELECT COUNT(*) FROM orders a JOIN orders b on a.orderkey = b.orderkey"); } @Test public void testWildcardFromJoin() throws Exception { assertQuery( "SELECT * FROM (select orderkey, partkey from lineitem) a join (select orderkey, custkey from orders) b using (orderkey)", "SELECT * FROM (select orderkey, partkey from lineitem) a join (select orderkey, custkey from orders) b on a.orderkey = b.orderkey"); } @Test public void testQualifiedWildcardFromJoin() throws Exception { assertQuery( "SELECT a.*, b.* FROM (select orderkey, partkey from lineitem) a join (select orderkey, custkey from orders) b using (orderkey)", "SELECT a.*, b.* FROM (select orderkey, partkey from lineitem) a join (select orderkey, custkey from orders) b on a.orderkey = b.orderkey"); } @Test public void testJoinAggregations() throws Exception { assertQuery("SELECT x + y FROM (" + " SELECT orderdate, COUNT(*) x FROM orders GROUP BY orderdate) a JOIN (" + " SELECT orderdate, COUNT(*) y FROM orders GROUP BY orderdate) b ON a.orderdate = b.orderdate"); } @Test public void testJoinOnMultipleFields() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.shipdate = orders.orderdate"); } @Test public void testJoinUsingMultipleFields() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem JOIN (SELECT orderkey, orderdate shipdate FROM ORDERS) T USING (orderkey, shipdate)", "SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.shipdate = orders.orderdate"); } @Test public void testJoinWithNonJoinExpression() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem JOIN orders ON lineitem.orderkey = orders.orderkey AND orders.custkey = 1"); } @Test public void testJoinWithNullValues() throws Exception { assertQuery("" + "SELECT *\n" + "FROM (\n" + " SELECT CASE WHEN orderkey % 3 = 0 THEN NULL ELSE orderkey END AS orderkey\n" + " FROM lineitem\n" + " WHERE partkey % 512 = 0\n" + ") AS lineitem \n" + "JOIN (\n" + " SELECT CASE WHEN orderkey % 2 = 0 THEN NULL ELSE orderkey END AS orderkey\n" + " FROM orders\n" + " WHERE custkey % 512 = 0\n" + ") AS orders\n" + "ON lineitem.orderkey = orders.orderkey"); } @Test public void testLeftFilteredJoin() throws Exception { // Test predicate move around assertQuery( "SELECT custkey, linestatus, tax, totalprice, orderstatus FROM (SELECT * FROM lineitem WHERE orderkey % 2 = 0) a JOIN orders ON a.orderkey = orders.orderkey"); } @Test public void testRightFilteredJoin() throws Exception { // Test predicate move around assertQuery( "SELECT custkey, linestatus, tax, totalprice, orderstatus FROM lineitem JOIN (SELECT * FROM orders WHERE orderkey % 2 = 0) a ON lineitem.orderkey = a.orderkey"); } @Test public void testJoinWithFullyPushedDownJoinClause() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem JOIN orders ON orders.custkey = 1 AND lineitem.orderkey = 1"); } @Test public void testJoinPredicateMoveAround() throws Exception { assertQuery("SELECT COUNT(*)\n" + "FROM (SELECT * FROM lineitem WHERE orderkey % 16 = 0 AND partkey % 2 = 0) lineitem\n" + "JOIN (SELECT * FROM orders WHERE orderkey % 16 = 0 AND custkey % 2 = 0) orders\n" + "ON lineitem.orderkey % 8 = orders.orderkey % 8 AND lineitem.linenumber % 2 = 0\n" + "WHERE orders.custkey % 8 < 7 AND orders.custkey % 8 = lineitem.orderkey % 8 AND lineitem.suppkey % 7 > orders.custkey % 7"); } @Test public void testSimpleLeftJoin() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem LEFT JOIN orders ON lineitem.orderkey = orders.orderkey"); assertQuery("SELECT COUNT(*) FROM lineitem LEFT OUTER JOIN orders ON lineitem.orderkey = orders.orderkey"); } @Test public void testLeftJoinNormalizedToInner() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem LEFT JOIN orders ON lineitem.orderkey = orders.orderkey WHERE orders.orderkey IS NOT NULL"); } @Test public void testLeftJoinWithRightConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM (SELECT * FROM lineitem WHERE orderkey % 1024 = 0) lineitem LEFT JOIN orders ON lineitem.orderkey = 1024"); } @Test public void testLeftJoinWithLeftConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM (SELECT * FROM lineitem WHERE orderkey % 1024 = 0) lineitem LEFT JOIN orders ON orders.orderkey = 1024"); } @Test public void testSimpleLeftJoinWithLeftConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem LEFT JOIN orders ON lineitem.orderkey = orders.orderkey AND orders.orderkey = 2"); } @Test public void testSimpleLeftJoinWithRightConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem LEFT JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.orderkey = 2"); } @Test public void testDoubleFilteredLeftJoinWithRightConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM (SELECT * FROM lineitem WHERE orderkey % 1024 = 0) lineitem LEFT JOIN (SELECT * FROM orders WHERE orderkey % 1024 = 0) orders ON orders.orderkey = 1024"); } @Test public void testDoubleFilteredLeftJoinWithLeftConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM (SELECT * FROM lineitem WHERE orderkey % 1024 = 0) lineitem LEFT JOIN (SELECT * FROM orders WHERE orderkey % 1024 = 0) orders ON lineitem.orderkey = 1024"); } @Test public void testLeftJoinDoubleClauseWithLeftOverlap() throws Exception { // Checks to make sure that we properly handle duplicate field references in join clauses assertQuery( "SELECT COUNT(*) FROM lineitem LEFT JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.orderkey = orders.custkey"); } @Test public void testLeftJoinDoubleClauseWithRightOverlap() throws Exception { // Checks to make sure that we properly handle duplicate field references in join clauses assertQuery( "SELECT COUNT(*) FROM lineitem LEFT JOIN orders ON lineitem.orderkey = orders.orderkey AND orders.orderkey = lineitem.partkey"); } @Test public void testBuildFilteredLeftJoin() throws Exception { assertQuery( "SELECT * FROM lineitem LEFT JOIN (SELECT * FROM orders WHERE orderkey % 2 = 0) a ON lineitem.orderkey = a.orderkey"); } @Test public void testProbeFilteredLeftJoin() throws Exception { assertQuery( "SELECT * FROM (SELECT * FROM lineitem WHERE orderkey % 2 = 0) a LEFT JOIN orders ON a.orderkey = orders.orderkey"); } @Test public void testLeftJoinPredicateMoveAround() throws Exception { assertQuery("SELECT COUNT(*)\n" + "FROM (SELECT * FROM lineitem WHERE orderkey % 16 = 0 AND partkey % 2 = 0) lineitem\n" + "LEFT JOIN (SELECT * FROM orders WHERE orderkey % 16 = 0 AND custkey % 2 = 0) orders\n" + "ON lineitem.orderkey % 8 = orders.orderkey % 8\n" + "WHERE (orders.custkey % 8 < 7 OR orders.custkey % 8 IS NULL) AND orders.custkey % 8 = lineitem.orderkey % 8"); } @Test public void testLeftJoinEqualityInference() throws Exception { // Test that we can infer orders.orderkey % 4 = orders.custkey % 3 on the inner side assertQuery("SELECT COUNT(*)\n" + "FROM (SELECT * FROM lineitem WHERE orderkey % 4 = 0 AND suppkey % 2 = partkey % 2 AND linenumber % 3 = orderkey % 3) lineitem\n" + "LEFT JOIN (SELECT * FROM orders WHERE orderkey % 4 = 0) orders\n" + "ON lineitem.linenumber % 3 = orders.orderkey % 4 AND lineitem.orderkey % 3 = orders.custkey % 3\n" + "WHERE lineitem.suppkey % 2 = lineitem.linenumber % 3"); } @Test public void testLeftJoinWithNullValues() throws Exception { assertQuery("" + "SELECT *\n" + "FROM (\n" + " SELECT CASE WHEN orderkey % 3 = 0 THEN NULL ELSE orderkey END AS orderkey\n" + " FROM lineitem\n" + " WHERE partkey % 512 = 0\n" + ") AS lineitem \n" + "LEFT JOIN (\n" + " SELECT CASE WHEN orderkey % 2 = 0 THEN NULL ELSE orderkey END AS orderkey\n" + " FROM orders\n" + " WHERE custkey % 512 = 0\n" + ") AS orders\n" + "ON lineitem.orderkey = orders.orderkey"); } @Test public void testSimpleRightJoin() throws Exception { assertQuery("SELECT COUNT(*) FROM lineitem RIGHT JOIN orders ON lineitem.orderkey = orders.orderkey"); assertQuery("SELECT COUNT(*) FROM lineitem RIGHT OUTER JOIN orders ON lineitem.orderkey = orders.orderkey"); } @Test public void testRightJoinNormalizedToInner() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem RIGHT JOIN orders ON lineitem.orderkey = orders.orderkey WHERE lineitem.orderkey IS NOT NULL"); } @Test public void testRightJoinWithRightConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM (SELECT * FROM lineitem WHERE orderkey % 1024 = 0) lineitem RIGHT JOIN orders ON lineitem.orderkey = 1024"); } @Test public void testRightJoinWithLeftConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM (SELECT * FROM lineitem WHERE orderkey % 1024 = 0) lineitem RIGHT JOIN orders ON orders.orderkey = 1024"); } @Test public void testDoubleFilteredRightJoinWithRightConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM (SELECT * FROM lineitem WHERE orderkey % 1024 = 0) lineitem RIGHT JOIN (SELECT * FROM orders WHERE orderkey % 1024 = 0) orders ON orders.orderkey = 1024"); } @Test public void testDoubleFilteredRightJoinWithLeftConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM (SELECT * FROM lineitem WHERE orderkey % 1024 = 0) lineitem RIGHT JOIN (SELECT * FROM orders WHERE orderkey % 1024 = 0) orders ON lineitem.orderkey = 1024"); } @Test public void testSimpleRightJoinWithLeftConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem RIGHT JOIN orders ON lineitem.orderkey = orders.orderkey AND orders.orderkey = 2"); } @Test public void testSimpleRightJoinWithRightConstantEquality() throws Exception { assertQuery( "SELECT COUNT(*) FROM lineitem RIGHT JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.orderkey = 2"); } @Test public void testRightJoinDoubleClauseWithLeftOverlap() throws Exception { // Checks to make sure that we properly handle duplicate field references in join clauses assertQuery( "SELECT COUNT(*) FROM lineitem RIGHT JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.orderkey = orders.custkey"); } @Test public void testRightJoinDoubleClauseWithRightOverlap() throws Exception { // Checks to make sure that we properly handle duplicate field references in join clauses assertQuery( "SELECT COUNT(*) FROM lineitem RIGHT JOIN orders ON lineitem.orderkey = orders.orderkey AND orders.orderkey = lineitem.partkey"); } @Test public void testBuildFilteredRightJoin() throws Exception { assertQuery( "SELECT custkey, linestatus, tax, totalprice, orderstatus FROM (SELECT * FROM lineitem WHERE orderkey % 2 = 0) a RIGHT JOIN orders ON a.orderkey = orders.orderkey"); } @Test public void testProbeFilteredRightJoin() throws Exception { assertQuery( "SELECT custkey, linestatus, tax, totalprice, orderstatus FROM lineitem RIGHT JOIN (SELECT * FROM orders WHERE orderkey % 2 = 0) a ON lineitem.orderkey = a.orderkey"); } @Test public void testRightJoinPredicateMoveAround() throws Exception { assertQuery("SELECT COUNT(*)\n" + "FROM (SELECT * FROM orders WHERE orderkey % 16 = 0 AND custkey % 2 = 0) orders\n" + "RIGHT JOIN (SELECT * FROM lineitem WHERE orderkey % 16 = 0 AND partkey % 2 = 0) lineitem\n" + "ON lineitem.orderkey % 8 = orders.orderkey % 8\n" + "WHERE (orders.custkey % 8 < 7 OR orders.custkey % 8 IS NULL) AND orders.custkey % 8 = lineitem.orderkey % 8"); } @Test public void testRightJoinEqualityInference() throws Exception { // Test that we can infer orders.orderkey % 4 = orders.custkey % 3 on the inner side assertQuery("SELECT COUNT(*)\n" + "FROM (SELECT * FROM orders WHERE orderkey % 4 = 0) orders\n" + "RIGHT JOIN (SELECT * FROM lineitem WHERE orderkey % 4 = 0 AND suppkey % 2 = partkey % 2 AND linenumber % 3 = orderkey % 3) lineitem\n" + "ON lineitem.linenumber % 3 = orders.orderkey % 4 AND lineitem.orderkey % 3 = orders.custkey % 3\n" + "WHERE lineitem.suppkey % 2 = lineitem.linenumber % 3"); } @Test public void testRightJoinWithNullValues() throws Exception { assertQuery("" + "SELECT lineitem.orderkey, orders.orderkey\n" + "FROM (\n" + " SELECT CASE WHEN orderkey % 3 = 0 THEN NULL ELSE orderkey END AS orderkey\n" + " FROM lineitem\n" + " WHERE partkey % 512 = 0\n" + ") AS lineitem \n" + "RIGHT JOIN (\n" + " SELECT CASE WHEN orderkey % 2 = 0 THEN NULL ELSE orderkey END AS orderkey\n" + " FROM orders\n" + " WHERE custkey % 512 = 0\n" + ") AS orders\n" + "ON lineitem.orderkey = orders.orderkey"); } @Test public void testOrderBy() throws Exception { assertQueryOrdered("SELECT orderstatus FROM orders ORDER BY orderstatus"); } @Test public void testOrderBy2() throws Exception { assertQueryOrdered("SELECT orderstatus FROM orders ORDER BY orderkey DESC"); } @Test public void testOrderByMultipleFields() throws Exception { assertQueryOrdered("SELECT custkey, orderstatus FROM orders ORDER BY custkey DESC, orderstatus"); } @Test public void testOrderByAlias() throws Exception { assertQueryOrdered("SELECT orderstatus x FROM orders ORDER BY x ASC"); } @Test public void testOrderByAliasWithSameNameAsUnselectedColumn() throws Exception { assertQueryOrdered("SELECT orderstatus orderdate FROM orders ORDER BY orderdate ASC"); } @Test public void testOrderByOrdinal() throws Exception { assertQueryOrdered("SELECT orderstatus, orderdate FROM orders ORDER BY 2, 1"); } @Test public void testOrderByOrdinalWithWildcard() throws Exception { assertQueryOrdered("SELECT * FROM orders ORDER BY 1"); } @Test public void testGroupByOrdinal() throws Exception { assertQuery("SELECT orderstatus, sum(totalprice) FROM orders GROUP BY 1", "SELECT orderstatus, sum(totalprice) FROM orders GROUP BY orderstatus"); } @Test public void testGroupBySearchedCase() throws Exception { assertQuery("SELECT CASE WHEN orderstatus = 'O' THEN 'a' ELSE 'b' END, count(*)\n" + "FROM orders\n" + "GROUP BY CASE WHEN orderstatus = 'O' THEN 'a' ELSE 'b' END"); assertQuery( "SELECT CASE WHEN orderstatus = 'O' THEN 'a' ELSE 'b' END, count(*)\n" + "FROM orders\n" + "GROUP BY 1", "SELECT CASE WHEN orderstatus = 'O' THEN 'a' ELSE 'b' END, count(*)\n" + "FROM orders\n" + "GROUP BY CASE WHEN orderstatus = 'O' THEN 'a' ELSE 'b' END"); } @Test public void testGroupBySearchedCaseNoElse() throws Exception { // whole CASE in group by clause assertQuery("SELECT CASE WHEN orderstatus = 'O' THEN 'a' END, count(*)\n" + "FROM orders\n" + "GROUP BY CASE WHEN orderstatus = 'O' THEN 'a' END"); assertQuery("SELECT CASE WHEN orderstatus = 'O' THEN 'a' END, count(*)\n" + "FROM orders\n" + "GROUP BY 1", "SELECT CASE WHEN orderstatus = 'O' THEN 'a' END, count(*)\n" + "FROM orders\n" + "GROUP BY CASE WHEN orderstatus = 'O' THEN 'a' END"); assertQuery("SELECT CASE WHEN true THEN orderstatus END, count(*)\n" + "FROM orders\n" + "GROUP BY orderstatus"); } @Test public void testGroupByCase() throws Exception { // whole CASE in group by clause assertQuery("SELECT CASE orderstatus WHEN 'O' THEN 'a' ELSE 'b' END, count(*)\n" + "FROM orders\n" + "GROUP BY CASE orderstatus WHEN 'O' THEN 'a' ELSE 'b' END"); assertQuery( "SELECT CASE orderstatus WHEN 'O' THEN 'a' ELSE 'b' END, count(*)\n" + "FROM orders\n" + "GROUP BY 1", "SELECT CASE orderstatus WHEN 'O' THEN 'a' ELSE 'b' END, count(*)\n" + "FROM orders\n" + "GROUP BY CASE orderstatus WHEN 'O' THEN 'a' ELSE 'b' END"); // operand in group by clause assertQuery("SELECT CASE orderstatus WHEN 'O' THEN 'a' ELSE 'b' END, count(*)\n" + "FROM orders\n" + "GROUP BY orderstatus"); // condition in group by clause assertQuery("SELECT CASE 'O' WHEN orderstatus THEN 'a' ELSE 'b' END, count(*)\n" + "FROM orders\n" + "GROUP BY orderstatus"); // 'then' in group by clause assertQuery("SELECT CASE 1 WHEN 1 THEN orderstatus ELSE 'x' END, count(*)\n" + "FROM orders\n" + "GROUP BY orderstatus"); // 'else' in group by clause assertQuery("SELECT CASE 1 WHEN 1 THEN 'x' ELSE orderstatus END, count(*)\n" + "FROM orders\n" + "GROUP BY orderstatus"); } @Test public void testGroupByCaseNoElse() throws Exception { // whole CASE in group by clause assertQuery("SELECT CASE orderstatus WHEN 'O' THEN 'a' END, count(*)\n" + "FROM orders\n" + "GROUP BY CASE orderstatus WHEN 'O' THEN 'a' END"); // operand in group by clause assertQuery("SELECT CASE orderstatus WHEN 'O' THEN 'a' END, count(*)\n" + "FROM orders\n" + "GROUP BY orderstatus"); // condition in group by clause assertQuery("SELECT CASE 'O' WHEN orderstatus THEN 'a' END, count(*)\n" + "FROM orders\n" + "GROUP BY orderstatus"); // 'then' in group by clause assertQuery( "SELECT CASE 1 WHEN 1 THEN orderstatus END, count(*)\n" + "FROM orders\n" + "GROUP BY orderstatus"); } @Test public void testGroupByCast() throws Exception { // whole CAST in group by expression assertQuery("SELECT CAST(orderkey AS VARCHAR), count(*) FROM orders GROUP BY CAST(orderkey AS VARCHAR)"); assertQuery("SELECT CAST(orderkey AS VARCHAR), count(*) FROM orders GROUP BY 1", "SELECT CAST(orderkey AS VARCHAR), count(*) FROM orders GROUP BY CAST(orderkey AS VARCHAR)"); // argument in group by expression assertQuery("SELECT CAST(orderkey AS VARCHAR), count(*) FROM orders GROUP BY orderkey"); } @Test public void testGroupByCoalesce() throws Exception { // whole COALESCE in group by assertQuery( "SELECT COALESCE(orderkey, custkey), count(*) FROM orders GROUP BY COALESCE(orderkey, custkey)"); assertQuery("SELECT COALESCE(orderkey, custkey), count(*) FROM orders GROUP BY 1", "SELECT COALESCE(orderkey, custkey), count(*) FROM orders GROUP BY COALESCE(orderkey, custkey)"); // operands in group by assertQuery("SELECT COALESCE(orderkey, 1), count(*) FROM orders GROUP BY orderkey"); // operands in group by assertQuery("SELECT COALESCE(1, orderkey), count(*) FROM orders GROUP BY orderkey"); } @Test public void testGroupByNullIf() throws Exception { // whole NULLIF in group by assertQuery("SELECT NULLIF(orderkey, custkey), count(*) FROM orders GROUP BY NULLIF(orderkey, custkey)"); assertQuery("SELECT NULLIF(orderkey, custkey), count(*) FROM orders GROUP BY 1", "SELECT NULLIF(orderkey, custkey), count(*) FROM orders GROUP BY NULLIF(orderkey, custkey)"); // first operand in group by assertQuery("SELECT NULLIF(orderkey, 1), count(*) FROM orders GROUP BY orderkey"); // second operand in group by assertQuery("SELECT NULLIF(1, orderkey), count(*) FROM orders GROUP BY orderkey"); } @Test public void testGroupByExtract() throws Exception { // whole expression in group by assertQuery("SELECT EXTRACT(YEAR FROM now()), count(*) FROM orders GROUP BY EXTRACT(YEAR FROM now())"); assertQuery("SELECT EXTRACT(YEAR FROM now()), count(*) FROM orders GROUP BY 1", "SELECT EXTRACT(YEAR FROM now()), count(*) FROM orders GROUP BY EXTRACT(YEAR FROM now())"); // argument in group by assertQuery("SELECT EXTRACT(YEAR FROM now()), count(*) FROM orders GROUP BY now()"); } @Test public void testGroupByBetween() throws Exception { // whole expression in group by assertQuery("SELECT orderkey BETWEEN 1 AND 100 FROM orders GROUP BY orderkey BETWEEN 1 AND 100 "); // expression in group by assertQuery("SELECT CAST(orderkey BETWEEN 1 AND 100 AS BIGINT) FROM orders GROUP BY orderkey"); // min in group by assertQuery("SELECT CAST(50 BETWEEN orderkey AND 100 AS BIGINT) FROM orders GROUP BY orderkey"); // max in group by assertQuery("SELECT CAST(50 BETWEEN 1 AND orderkey AS BIGINT) FROM orders GROUP BY orderkey"); } @Test public void testHaving() throws Exception { assertQuery( "SELECT orderstatus, sum(totalprice) FROM orders GROUP BY orderstatus HAVING orderstatus = 'O'"); } @Test public void testHaving2() throws Exception { assertQuery("SELECT custkey, sum(orderkey) FROM orders GROUP BY custkey HAVING sum(orderkey) > 400000"); } @Test public void testHaving3() throws Exception { assertQuery( "SELECT custkey, sum(totalprice) * 2 FROM orders GROUP BY custkey HAVING avg(totalprice + 5) > 10"); } @Test public void testColumnAliases() throws Exception { 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 testSameInputToAggregates() throws Exception { assertQuery("SELECT max(a), max(b) FROM (SELECT custkey a, custkey b FROM orders) x"); } @SuppressWarnings("PointlessArithmeticExpression") @Test public void testWindowFunctionsExpressions() { MaterializedResult actual = computeActual("" + "SELECT orderkey, orderstatus\n" + ", row_number() OVER (ORDER BY orderkey * 2) *\n" + " row_number() OVER (ORDER BY orderkey DESC) + 100\n" + "FROM (SELECT * FROM orders ORDER BY orderkey LIMIT 10) x\n" + "ORDER BY orderkey LIMIT 5"); MaterializedResult expected = resultBuilder(FIXED_INT_64, VARIABLE_BINARY, FIXED_INT_64) .row(1, "O", (1 * 10) + 100).row(2, "O", (2 * 9) + 100).row(3, "F", (3 * 8) + 100) .row(4, "O", (4 * 7) + 100).row(5, "F", (5 * 6) + 100).build(); assertEquals(actual, expected); } @Test public void testWindowFunctionsFromAggregate() throws Exception { MaterializedResult actual = computeActual("" + "SELECT * FROM (\n" + " SELECT orderstatus, clerk, sales\n" + " , rank() OVER (PARTITION BY x.orderstatus ORDER BY sales DESC) rnk\n" + " FROM (\n" + " SELECT orderstatus, clerk, sum(totalprice) sales\n" + " FROM orders\n" + " GROUP BY orderstatus, clerk\n" + " ) x\n" + ") x\n" + "WHERE rnk <= 2\n" + "ORDER BY orderstatus, rnk"); MaterializedResult expected = resultBuilder(VARIABLE_BINARY, VARIABLE_BINARY, DOUBLE, FIXED_INT_64) .row("F", "Clerk#000000090", 2784836.61, 1).row("F", "Clerk#000000084", 2674447.15, 2) .row("O", "Clerk#000000500", 2569878.29, 1).row("O", "Clerk#000000050", 2500162.92, 2) .row("P", "Clerk#000000071", 841820.99, 1).row("P", "Clerk#000001000", 643679.49, 2).build(); assertEquals(actual, expected); } @Test public void testOrderByWindowFunction() throws Exception { MaterializedResult actual = computeActual("" + "SELECT orderkey, row_number() OVER (ORDER BY orderkey)\n" + "FROM (SELECT * FROM orders ORDER BY orderkey LIMIT 10)\n" + "ORDER BY 2 DESC\n" + "LIMIT 5"); MaterializedResult expected = resultBuilder(FIXED_INT_64, FIXED_INT_64).row(34, 10).row(33, 9).row(32, 8) .row(7, 7).row(6, 6).build(); assertEquals(actual, expected); } @Test public void testScalarFunction() throws Exception { assertQuery("SELECT SUBSTR('Quadratically', 5, 6) FROM orders LIMIT 1"); } @Test public void testCast() throws Exception { assertQuery("SELECT CAST('1' AS BIGINT) FROM orders"); 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"); } @Test public void testConcatOperator() throws Exception { assertQuery("SELECT '12' || '34' FROM orders LIMIT 1"); } @Test public void testQuotedIdentifiers() throws Exception { assertQuery("SELECT \"TOTALPRICE\" \"my price\" FROM \"ORDERS\""); } @Test(expectedExceptions = Exception.class, expectedExceptionsMessageRegExp = ".*orderkey_1.*") public void testInvalidColumn() throws Exception { computeActual( "select * from lineitem l join (select orderkey_1, custkey from orders) o on l.orderkey = o.orderkey_1"); } @Test public void testUnaliasedSubqueries() throws Exception { assertQuery("SELECT orderkey FROM (SELECT orderkey FROM orders)"); } @Test public void testUnaliasedSubqueries1() throws Exception { assertQuery("SELECT a FROM (SELECT orderkey a FROM orders)"); } @Test public void testJoinUnaliasedSubqueries() throws Exception { assertQuery("SELECT COUNT(*) FROM (SELECT * FROM lineitem) join (SELECT * FROM orders) using (orderkey)", "SELECT COUNT(*) FROM lineitem join orders on lineitem.orderkey = orders.orderkey"); } @Test public void testWith() throws Exception { assertQuery("" + "WITH a AS (SELECT * FROM orders) " + "SELECT * FROM a", "SELECT * FROM orders"); } @Test public void testWithQualifiedPrefix() throws Exception { assertQuery("" + "WITH a AS (SELECT 123 FROM orders LIMIT 1)" + "SELECT a.* FROM a", "SELECT 123 FROM orders LIMIT 1"); } @Test public void testWithAliased() throws Exception { assertQuery("" + "WITH a AS (SELECT * FROM orders) " + "SELECT * FROM a x", "SELECT * FROM orders"); } @Test public void testReferenceToWithQueryInFromClause() throws Exception { assertQuery("WITH a AS (SELECT * FROM orders)" + "SELECT * FROM (" + " SELECT * FROM a" + ")", "SELECT * FROM orders"); } @Test public void testWithChaining() throws Exception { 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 testWithSelfJoin() throws Exception { assertQuery( "" + "WITH x AS (SELECT DISTINCT orderkey FROM orders ORDER BY orderkey LIMIT 10)\n" + "SELECT count(*) FROM x a JOIN x b USING (orderkey)", "" + "SELECT count(*)\n" + "FROM (SELECT DISTINCT orderkey FROM orders ORDER BY orderkey LIMIT 10) a\n" + "JOIN (SELECT DISTINCT orderkey FROM orders ORDER BY orderkey LIMIT 10) b ON a.orderkey = b.orderkey"); } @Test public void testWithNestedSubqueries() throws Exception { 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(enabled = false) public void testWithColumnAliasing() throws Exception { assertQuery("WITH a (id) AS (SELECT 123 FROM orders LIMIT 1) SELECT * FROM a", "SELECT 123 FROM orders LIMIT 1"); } @Test public void testWithHiding() throws Exception { assertQuery("" + "WITH a AS (SELECT custkey FROM orders), " + " b AS (" + " WITH a AS (SELECT orderkey FROM orders)" + " SELECT * FROM a" + // should refer to inner 'a' " )" + "SELECT * FROM b", "SELECT orderkey FROM orders"); } @Test(expectedExceptions = RuntimeException.class, expectedExceptionsMessageRegExp = "Recursive WITH queries are not supported") public void testWithRecursive() throws Exception { computeActual("WITH RECURSIVE a AS (SELECT 123 FROM dual) SELECT * FROM a"); } @Test public void testCaseNoElse() throws Exception { assertQuery("SELECT orderkey, CASE orderstatus WHEN 'O' THEN 'a' END FROM orders"); } @Test public void testIfExpression() throws Exception { 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"); } @Test public void testIn() throws Exception { assertQuery("SELECT orderkey FROM orders WHERE orderkey IN (1, 2, 3)"); assertQuery("SELECT orderkey FROM orders WHERE orderkey IN (1.5, 2.3)"); assertQuery("SELECT orderkey FROM orders WHERE totalprice IN (1, 2, 3)"); } @Test public void testGroupByIf() throws Exception { assertQuery( "SELECT IF(orderkey between 1 and 5, 'orders', 'others'), sum(totalprice) FROM orders GROUP BY 1", "SELECT CASE WHEN orderkey BETWEEN 1 AND 5 THEN 'orders' ELSE 'others' END, sum(totalprice)\n" + "FROM orders\n" + "GROUP BY CASE WHEN orderkey BETWEEN 1 AND 5 THEN 'orders' ELSE 'others' END"); } @Test public void testDuplicateFields() throws Exception { assertQuery("SELECT * FROM (SELECT orderkey, orderkey FROM orders)", "SELECT orderkey, orderkey FROM orders"); } @Test public void testWildcardFromSubquery() throws Exception { assertQuery("SELECT * FROM (SELECT orderkey X FROM orders)"); } @Test public void testCaseInsensitiveOutputAliasInOrderBy() throws Exception { assertQueryOrdered("SELECT orderkey X FROM orders ORDER BY x"); } @Test public void testCaseInsensitiveAttribute() throws Exception { assertQuery("SELECT x FROM (SELECT orderkey X FROM orders)"); } @Test public void testCaseInsensitiveAliasedRelation() throws Exception { assertQuery("SELECT A.* FROM orders a"); } @Test public void testSubqueryBody() throws Exception { assertQuery("(SELECT orderkey, custkey FROM ORDERS)"); } @Test public void testSubqueryBodyOrderLimit() throws Exception { assertQueryOrdered("(SELECT orderkey AS a, custkey AS b FROM ORDERS) ORDER BY a LIMIT 1"); } @Test public void testSubqueryBodyProjectedOrderby() throws Exception { assertQueryOrdered("(SELECT orderkey, custkey FROM ORDERS) ORDER BY orderkey * -1"); } @Test public void testSubqueryBodyDoubleOrderby() throws Exception { assertQueryOrdered("(SELECT orderkey, custkey FROM ORDERS ORDER BY custkey) ORDER BY orderkey"); } @Test public void testNodeRoster() throws Exception { List<MaterializedTuple> result = computeActual("SELECT * FROM sys.node").getMaterializedTuples(); assertEquals(result.size(), getNodeCount()); } @Test public void testDual() throws Exception { MaterializedResult result = computeActual("SELECT * FROM dual"); List<MaterializedTuple> tuples = result.getMaterializedTuples(); assertEquals(tuples.size(), 1); } @Test public void testDefaultExplainTextFormat() { String query = "SELECT 123 FROM dual"; MaterializedResult result = computeActual("EXPLAIN " + query); String actual = Iterables.getOnlyElement(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(actual, getExplainPlan(query, LOGICAL)); } @Test public void testDefaultExplainGraphvizFormat() { String query = "SELECT 123 FROM dual"; MaterializedResult result = computeActual("EXPLAIN (FORMAT GRAPHVIZ) " + query); String actual = Iterables.getOnlyElement(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(actual, getGraphvizExplainPlan(query, LOGICAL)); } @Test public void testLogicalExplain() { String query = "SELECT 123 FROM dual"; MaterializedResult result = computeActual("EXPLAIN (TYPE LOGICAL) " + query); String actual = Iterables.getOnlyElement(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(actual, getExplainPlan(query, LOGICAL)); } @Test public void testLogicalExplainTextFormat() { String query = "SELECT 123 FROM dual"; MaterializedResult result = computeActual("EXPLAIN (TYPE LOGICAL, FORMAT TEXT) " + query); String actual = Iterables.getOnlyElement(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(actual, getExplainPlan(query, LOGICAL)); } @Test public void testLogicalExplainGraphvizFormat() { String query = "SELECT 123 FROM dual"; MaterializedResult result = computeActual("EXPLAIN (TYPE LOGICAL, FORMAT GRAPHVIZ) " + query); String actual = Iterables.getOnlyElement(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(actual, getGraphvizExplainPlan(query, LOGICAL)); } @Test public void testDistributedExplain() { String query = "SELECT 123 FROM dual"; MaterializedResult result = computeActual("EXPLAIN (TYPE DISTRIBUTED) " + query); String actual = Iterables.getOnlyElement(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(actual, getExplainPlan(query, DISTRIBUTED)); } @Test public void testDistributedExplainTextFormat() { String query = "SELECT 123 FROM dual"; MaterializedResult result = computeActual("EXPLAIN (TYPE DISTRIBUTED, FORMAT TEXT) " + query); String actual = Iterables.getOnlyElement(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(actual, getExplainPlan(query, DISTRIBUTED)); } @Test public void testDistributedExplainGraphvizFormat() { String query = "SELECT 123 FROM dual"; MaterializedResult result = computeActual("EXPLAIN (TYPE DISTRIBUTED, FORMAT GRAPHVIZ) " + query); String actual = Iterables.getOnlyElement(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(actual, getGraphvizExplainPlan(query, DISTRIBUTED)); } @Test public void testShowSchemas() throws Exception { MaterializedResult result = computeActual("SHOW SCHEMAS"); ImmutableSet<String> schemaNames = ImmutableSet .copyOf(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(schemaNames, ImmutableSet.of(TPCH_SCHEMA_NAME, INFORMATION_SCHEMA, "sys")); } @Test public void testShowTables() throws Exception { MaterializedResult result = computeActual("SHOW TABLES"); ImmutableSet<String> tableNames = ImmutableSet .copyOf(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(tableNames, ImmutableSet.of(TPCH_ORDERS_NAME, TPCH_LINEITEM_NAME)); } @Test public void testShowTablesFrom() throws Exception { MaterializedResult result = computeActual("SHOW TABLES FROM DEFAULT"); ImmutableSet<String> tableNames = ImmutableSet .copyOf(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(tableNames, ImmutableSet.of(TPCH_ORDERS_NAME, TPCH_LINEITEM_NAME)); result = computeActual("SHOW TABLES FROM TPCH.DEFAULT"); tableNames = ImmutableSet.copyOf(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(tableNames, ImmutableSet.of(TPCH_ORDERS_NAME, TPCH_LINEITEM_NAME)); result = computeActual("SHOW TABLES FROM UNKNOWN"); tableNames = ImmutableSet.copyOf(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(tableNames, ImmutableSet.of()); } @Test public void testShowTablesLike() throws Exception { MaterializedResult result = computeActual("SHOW TABLES LIKE 'or%'"); ImmutableSet<String> tableNames = ImmutableSet .copyOf(transform(result.getMaterializedTuples(), onlyColumnGetter())); assertEquals(tableNames, ImmutableSet.of(TPCH_ORDERS_NAME)); } @Test public void testShowColumns() throws Exception { MaterializedResult actual = computeActual("SHOW COLUMNS FROM orders"); MaterializedResult expected = resultBuilder(VARIABLE_BINARY, VARIABLE_BINARY, BOOLEAN, BOOLEAN) .row("orderkey", "bigint", true, false).row("custkey", "bigint", true, false) .row("orderstatus", "varchar", true, false).row("totalprice", "double", true, false) .row("orderdate", "varchar", true, false).row("orderpriority", "varchar", true, false) .row("clerk", "varchar", true, false).row("shippriority", "bigint", true, false) .row("comment", "varchar", true, false).build(); assertEquals(actual, expected); } @Test public void testShowPartitions() throws Exception { MaterializedResult result = computeActual("SHOW PARTITIONS FROM orders"); // table is not partitioned // TODO: add a partitioned table for tests and test where/order/limit assertEquals(result.getMaterializedTuples().size(), 0); } @Test public void testShowFunctions() throws Exception { MaterializedResult result = computeActual("SHOW FUNCTIONS"); ImmutableMultimap<String, MaterializedTuple> functions = Multimaps.index(result.getMaterializedTuples(), new Function<MaterializedTuple, String>() { @Override public String apply(MaterializedTuple input) { assertEquals(input.getFieldCount(), 5); return (String) input.getField(0); } }); assertTrue(functions.containsKey("avg"), "Expected function names " + functions + " to contain 'avg'"); assertEquals(functions.get("avg").asList().size(), 2); assertEquals(functions.get("avg").asList().get(0).getField(1), "double"); assertEquals(functions.get("avg").asList().get(0).getField(2), "bigint"); assertEquals(functions.get("avg").asList().get(0).getField(3), "aggregate"); assertEquals(functions.get("avg").asList().get(1).getField(1), "double"); assertEquals(functions.get("avg").asList().get(1).getField(2), "double"); assertEquals(functions.get("avg").asList().get(0).getField(3), "aggregate"); assertTrue(functions.containsKey("abs"), "Expected function names " + functions + " to contain 'abs'"); assertEquals(functions.get("abs").asList().get(0).getField(3), "scalar"); assertTrue(functions.containsKey("rand"), "Expected function names " + functions + " to contain 'rand'"); assertEquals(functions.get("rand").asList().get(0).getField(3), "scalar (non-deterministic)"); assertTrue(functions.containsKey("rank"), "Expected function names " + functions + " to contain 'rank'"); assertEquals(functions.get("rank").asList().get(0).getField(3), "window"); assertTrue(functions.containsKey("rank"), "Expected function names " + functions + " to contain 'split_part'"); assertEquals(functions.get("split_part").asList().get(0).getField(1), "varchar"); assertEquals(functions.get("split_part").asList().get(0).getField(2), "varchar, varchar, bigint"); assertEquals(functions.get("split_part").asList().get(0).getField(3), "scalar"); } @Test public void testNoFrom() throws Exception { assertQuery("SELECT 1 + 2, 3 + 4", "SELECT 1 + 2, 3 + 4 FROM orders LIMIT 1"); } @Test public void testTopNByMultipleFields() throws Exception { assertQueryOrdered( "SELECT orderkey, custkey, orderstatus FROM orders ORDER BY orderkey ASC, custkey ASC LIMIT 10"); assertQueryOrdered( "SELECT orderkey, custkey, orderstatus FROM orders ORDER BY orderkey ASC, custkey DESC LIMIT 10"); assertQueryOrdered( "SELECT orderkey, custkey, orderstatus FROM orders ORDER BY orderkey DESC, custkey ASC LIMIT 10"); assertQueryOrdered( "SELECT orderkey, custkey, orderstatus FROM orders ORDER BY orderkey DESC, custkey DESC LIMIT 10"); // now try with order by fields swapped assertQueryOrdered( "SELECT orderkey, custkey, orderstatus FROM orders ORDER BY custkey ASC, orderkey ASC LIMIT 10"); assertQueryOrdered( "SELECT orderkey, custkey, orderstatus FROM orders ORDER BY custkey ASC, orderkey DESC LIMIT 10"); assertQueryOrdered( "SELECT orderkey, custkey, orderstatus FROM orders ORDER BY custkey DESC, orderkey ASC LIMIT 10"); assertQueryOrdered( "SELECT orderkey, custkey, orderstatus FROM orders ORDER BY custkey DESC, orderkey DESC LIMIT 10"); } @Test public void testUnion() throws Exception { assertQuery("SELECT orderkey FROM orders UNION SELECT custkey FROM orders"); } @Test public void testUnionDistinct() throws Exception { assertQuery("SELECT orderkey FROM orders UNION DISTINCT SELECT custkey FROM orders"); } @Test public void testUnionAll() throws Exception { assertQuery("SELECT orderkey FROM orders UNION ALL SELECT custkey FROM orders"); } @Test public void testChainedUnionsWithOrder() throws Exception { assertQueryOrdered( "SELECT orderkey FROM orders UNION (SELECT custkey FROM orders UNION SELECT linenumber FROM lineitem) UNION ALL SELECT orderkey FROM lineitem ORDER BY orderkey"); } @Test public void testSubqueryUnion() throws Exception { assertQueryOrdered( "SELECT * FROM (SELECT orderkey FROM orders UNION SELECT custkey FROM orders UNION SELECT orderkey FROM orders) ORDER BY orderkey LIMIT 1000"); } @Test public void testSelectOnlyUnion() throws Exception { assertQuery("SELECT 123, 'foo' UNION ALL SELECT 999, 'bar'"); } @Test public void testMultiColumnUnionAll() throws Exception { assertQuery("SELECT * FROM orders UNION ALL SELECT * FROM orders"); } @Test public void testTableQuery() throws Exception { assertQuery("TABLE orders", "SELECT * FROM orders"); } @Test public void testTableQueryOrderLimit() throws Exception { assertQuery("TABLE orders ORDER BY orderkey LIMIT 10", "SELECT * FROM orders ORDER BY orderkey LIMIT 10", true); } @Test public void testTableQueryInUnion() throws Exception { assertQuery("(SELECT * FROM orders ORDER BY orderkey LIMIT 10) UNION ALL TABLE orders", "(SELECT * FROM orders ORDER BY orderkey LIMIT 10) UNION ALL SELECT * FROM orders"); } @Test public void testTableAsSubquery() throws Exception { assertQuery("(TABLE orders) ORDER BY orderkey", "(SELECT * FROM orders) ORDER BY orderkey", true); } @Test public void testLimitPushDown() throws Exception { MaterializedResult actual = computeActual("(TABLE orders ORDER BY orderkey) UNION ALL " + "SELECT * FROM orders WHERE orderstatus = 'F' UNION ALL " + "(TABLE orders ORDER BY orderkey LIMIT 20) UNION ALL " + "(TABLE orders LIMIT 5) UNION ALL " + "TABLE orders LIMIT 10"); MaterializedResult all = computeExpected("SELECT * FROM ORDERS", actual.getTupleInfo()); assertEquals(actual.getMaterializedTuples().size(), 10); assertTrue(all.getMaterializedTuples().containsAll(actual.getMaterializedTuples())); } @Test public void testOrderLimitCompaction() throws Exception { assertQueryOrdered("SELECT * FROM (SELECT * FROM orders ORDER BY orderkey) LIMIT 10"); } @Test public void testUnaliasSymbolReferencesWithUnion() throws Exception { assertQuery("SELECT 1, 1, 'a', 'a' UNION ALL SELECT 1, 2, 'a', 'b'"); } @Test public void testSemiJoin() throws Exception { // Throw in a bunch of IN subquery predicates assertQuery("" + "SELECT *, o2.custkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE orderkey % 5 = 0)\n" + "FROM (SELECT * FROM orders WHERE custkey % 256 = 0) o1\n" + "JOIN (SELECT * FROM orders WHERE custkey % 256 = 0) o2\n" + " ON (o1.orderkey IN (SELECT orderkey FROM lineitem WHERE orderkey % 4 = 0)) = (o2.orderkey IN (SELECT orderkey FROM lineitem WHERE orderkey % 4 = 0))\n" + "WHERE o1.orderkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE orderkey % 4 = 0)\n" + "ORDER BY o1.orderkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE orderkey % 7 = 0)"); assertQuery("" + "SELECT orderkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE partkey % 4 = 0),\n" + " SUM(\n" + " CASE\n" + " WHEN orderkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE suppkey % 4 = 0)\n" + " THEN 1\n" + " ELSE 0\n" + " END)\n" + "FROM orders\n" + "GROUP BY orderkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE partkey % 4 = 0)\n" + "HAVING SUM(\n" + " CASE\n" + " WHEN orderkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE suppkey % 4 = 0)\n" + " THEN 1\n" + " ELSE 0\n" + " END) > 1"); } @Test public void testAntiJoin() throws Exception { assertQuery("" + "SELECT *, orderkey\n" + " NOT IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE orderkey % 3 = 0)\n" + "FROM orders"); } @Test public void testSemiJoinLimitPushDown() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM (\n" + " SELECT orderkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem\n" + " WHERE orderkey % 2 = 0)\n" + " FROM orders\n" + " LIMIT 10)"); } @Test public void testSemiJoinNullHandling() throws Exception { assertQuery("" + "SELECT orderkey\n" + " IN (\n" + " SELECT CASE WHEN orderkey % 3 = 0 THEN NULL ELSE orderkey END\n" + " FROM lineitem)\n" + "FROM orders"); assertQuery("" + "SELECT orderkey\n" + " IN (\n" + " SELECT orderkey\n" + " FROM lineitem)\n" + "FROM (\n" + " SELECT CASE WHEN orderkey % 3 = 0 THEN NULL ELSE orderkey END AS orderkey\n" + " FROM orders)"); assertQuery("" + "SELECT orderkey\n" + " IN (\n" + " SELECT CASE WHEN orderkey % 3 = 0 THEN NULL ELSE orderkey END\n" + " FROM lineitem)\n" + "FROM (\n" + " SELECT CASE WHEN orderkey % 4 = 0 THEN NULL ELSE orderkey END AS orderkey\n" + " FROM orders)"); } @Test public void testPredicatePushdown() throws Exception { assertQuery("" + "SELECT *\n" + "FROM (\n" + " SELECT orderkey+1 as a FROM orders WHERE orderstatus = 'F' UNION ALL \n" + " SELECT orderkey FROM orders WHERE orderkey % 2 = 0 UNION ALL \n" + " (SELECT orderkey+custkey FROM orders ORDER BY orderkey LIMIT 10)\n" + ") \n" + "WHERE a < 20 OR a > 100 \n" + "ORDER BY a"); } @Test public void testJoinPredicatePushdown() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM lineitem \n" + "JOIN (\n" + " SELECT * FROM orders\n" + ") orders \n" + "ON lineitem.orderkey = orders.orderkey \n" + "WHERE orders.orderkey % 4 = 0\n" + " AND lineitem.suppkey > orders.orderkey"); } @Test public void testLeftJoinAsInnerPredicatePushdown() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM lineitem \n" + "LEFT JOIN (\n" + " SELECT * FROM orders WHERE orders.orderkey % 2 = 0\n" + ") orders \n" + "ON lineitem.orderkey = orders.orderkey \n" + "WHERE orders.orderkey % 4 = 0\n" + " AND (lineitem.suppkey % 2 = orders.orderkey % 2 OR orders.custkey IS NULL)"); } @Test public void testPlainLeftJoinPredicatePushdown() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM lineitem \n" + "LEFT JOIN (\n" + " SELECT * FROM orders WHERE orders.orderkey % 2 = 0\n" + ") orders \n" + "ON lineitem.orderkey = orders.orderkey \n" + "WHERE lineitem.orderkey % 4 = 0\n" + " AND (lineitem.suppkey % 2 = orders.orderkey % 2 OR orders.orderkey IS NULL)"); } @Test public void testLeftJoinPredicatePushdownWithSelfEquality() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM lineitem \n" + "LEFT JOIN (\n" + " SELECT * FROM orders WHERE orders.orderkey % 2 = 0\n" + ") orders \n" + "ON lineitem.orderkey = orders.orderkey \n" + "WHERE orders.orderkey = orders.orderkey\n" + " AND lineitem.orderkey % 4 = 0\n" + " AND (lineitem.suppkey % 2 = orders.orderkey % 2 OR orders.orderkey IS NULL)"); } @Test public void testRightJoinAsInnerPredicatePushdown() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM (\n" + " SELECT * FROM orders WHERE orders.orderkey % 2 = 0\n" + ") orders\n" + "RIGHT JOIN lineitem\n" + "ON lineitem.orderkey = orders.orderkey \n" + "WHERE orders.orderkey % 4 = 0\n" + " AND (lineitem.suppkey % 2 = orders.orderkey % 2 OR orders.custkey IS NULL)"); } @Test public void testPlainRightJoinPredicatePushdown() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM (\n" + " SELECT * FROM orders WHERE orders.orderkey % 2 = 0\n" + ") orders \n" + "RIGHT JOIN lineitem\n" + "ON lineitem.orderkey = orders.orderkey \n" + "WHERE lineitem.orderkey % 4 = 0\n" + " AND (lineitem.suppkey % 2 = orders.orderkey % 2 OR orders.orderkey IS NULL)"); } @Test public void testRightJoinPredicatePushdownWithSelfEquality() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM (\n" + " SELECT * FROM orders WHERE orders.orderkey % 2 = 0\n" + ") orders \n" + "RIGHT JOIN lineitem\n" + "ON lineitem.orderkey = orders.orderkey \n" + "WHERE orders.orderkey = orders.orderkey\n" + " AND lineitem.orderkey % 4 = 0\n" + " AND (lineitem.suppkey % 2 = orders.orderkey % 2 OR orders.orderkey IS NULL)"); } @Test public void testPredicatePushdownJoinEqualityGroups() throws Exception { assertQuery("" + "SELECT *\n" + "FROM (\n" + " SELECT custkey custkey1, custkey%4 custkey1a, custkey%8 custkey1b, custkey%16 custkey1c\n" + " FROM orders\n" + ") orders1 \n" + "JOIN (\n" + " SELECT custkey custkey2, custkey%4 custkey2a, custkey%8 custkey2b\n" + " FROM orders\n" + ") orders2 ON orders1.custkey1 = orders2.custkey2\n" + "WHERE custkey2a = custkey2b\n" + " AND custkey1 = custkey1a\n" + " AND custkey2 = custkey2a\n" + " AND custkey1a = custkey1c\n" + " AND custkey1b = custkey1c\n" + " AND custkey1b % 2 = 0"); } @Test public void testGroupByKeyPredicatePushdown() throws Exception { assertQuery("" + "SELECT *\n" + "FROM (\n" + " SELECT custkey1, orderstatus1, SUM(totalprice1) totalprice, MAX(custkey2) maxcustkey\n" + " FROM (\n" + " SELECT *\n" + " FROM (\n" + " SELECT custkey custkey1, orderstatus orderstatus1, CAST(totalprice AS BIGINT) totalprice1, orderkey orderkey1\n" + " FROM orders\n" + " ) orders1 \n" + " JOIN (\n" + " SELECT custkey custkey2, orderstatus orderstatus2, CAST(totalprice AS BIGINT) totalprice2, orderkey orderkey2\n" + " FROM orders\n" + " ) orders2 ON orders1.orderkey1 = orders2.orderkey2\n" + " ) \n" + " GROUP BY custkey1, orderstatus1\n" + ")\n" + "WHERE custkey1 = maxcustkey\n" + "AND maxcustkey % 2 = 0 \n" + "AND orderstatus1 = 'F'\n" + "AND totalprice > 10000\n" + "ORDER BY custkey1, orderstatus1, totalprice, maxcustkey"); } @Test public void testNonDeterministicJoinPredicatePushdown() throws Exception { MaterializedResult materializedResult = computeActual( "" + "SELECT COUNT(*)\n" + "FROM (\n" + " SELECT DISTINCT *\n" + " FROM (\n" + " SELECT 'abc' as col1a, 500 as col1b FROM lineitem limit 1\n" + " ) table1\n" + " JOIN (\n" + " SELECT 'abc' as col2a FROM lineitem limit 1000000\n" + " ) table2\n" + " ON table1.col1a = table2.col2a\n" + " WHERE rand() * 1000 > table1.col1b\n" + ")"); MaterializedTuple tuple = Iterables.getOnlyElement(materializedResult.getMaterializedTuples()); Assert.assertEquals(tuple.getFieldCount(), 1); long count = (Long) tuple.getField(0); // Technically non-deterministic unit test but has essentially a next to impossible chance of a false positive Assert.assertTrue(count > 0 && count < 1000000); } @Test public void testTrivialNonDeterministicPredicatePushdown() throws Exception { assertQuery("SELECT COUNT(*) FROM dual WHERE rand() >= 0"); } @Test public void testNonDeterministicTableScanPredicatePushdown() throws Exception { MaterializedResult materializedResult = computeActual("" + "SELECT COUNT(*)\n" + "FROM (\n" + " SELECT *\n" + " FROM lineitem\n" + " LIMIT 1000\n" + ")\n" + "WHERE rand() > 0.5"); MaterializedTuple tuple = Iterables.getOnlyElement(materializedResult.getMaterializedTuples()); Assert.assertEquals(tuple.getFieldCount(), 1); long count = (Long) tuple.getField(0); // Technically non-deterministic unit test but has essentially a next to impossible chance of a false positive Assert.assertTrue(count > 0 && count < 1000); } @Test public void testNonDeterministicAggregationPredicatePushdown() throws Exception { MaterializedResult materializedResult = computeActual( "" + "SELECT COUNT(*)\n" + "FROM (\n" + " SELECT orderkey, COUNT(*)\n" + " FROM lineitem\n" + " GROUP BY orderkey\n" + " LIMIT 1000\n" + ")\n" + "WHERE rand() > 0.5"); MaterializedTuple tuple = Iterables.getOnlyElement(materializedResult.getMaterializedTuples()); Assert.assertEquals(tuple.getFieldCount(), 1); long count = (Long) tuple.getField(0); // Technically non-deterministic unit test but has essentially a next to impossible chance of a false positive Assert.assertTrue(count > 0 && count < 1000); } @Test public void testSemiJoinPredicateMoveAround() throws Exception { assertQuery("" + "SELECT COUNT(*)\n" + "FROM (SELECT * FROM orders WHERE custkey % 2 = 0 AND orderkey % 3 = 0)\n" + "WHERE orderkey\n" + " IN (\n" + " SELECT CASE WHEN orderkey % 7 = 0 THEN NULL ELSE orderkey END\n" + " FROM lineitem\n" + " WHERE partkey % 2 = 0)\n" + " AND\n" + " orderkey % 2 = 0"); } @Test public void testTableSampleBernoulliBoundaryValues() throws Exception { MaterializedResult fullSample = computeActual("SELECT orderkey FROM orders TABLESAMPLE BERNOULLI (100)"); MaterializedResult emptySample = computeActual("SELECT orderkey FROM orders TABLESAMPLE BERNOULLI (0)"); MaterializedResult all = computeExpected("SELECT orderkey FROM orders", fullSample.getTupleInfo()); assertTrue(all.getMaterializedTuples().containsAll(fullSample.getMaterializedTuples())); assertEquals(emptySample.getMaterializedTuples().size(), 0); } @Test public void testTableSampleBernoulli() throws Exception { DescriptiveStatistics stats = new DescriptiveStatistics(); int total = computeExpected("SELECT orderkey FROM orders", TupleInfo.SINGLE_LONG).getMaterializedTuples() .size(); for (int i = 0; i < 100; i++) { List<MaterializedTuple> values = computeActual("SELECT orderkey FROM ORDERS TABLESAMPLE BERNOULLI (50)") .getMaterializedTuples(); assertEquals(values.size(), ImmutableSet.copyOf(values).size(), "TABLESAMPLE produced duplicate rows"); stats.addValue(values.size() * 1.0 / total); } double mean = stats.getGeometricMean(); assertTrue(mean > 0.45 && mean < 0.55, String.format("Expected mean sampling rate to be ~0.5, but was %s", mean)); } @Test(expectedExceptions = RuntimeException.class, expectedExceptionsMessageRegExp = "\\QUnexpected parameters (bigint) for function length. Expected: length(varchar)\\E") public void testFunctionNotRegistered() { computeActual("SELECT length(1)"); } @Test(expectedExceptions = RuntimeException.class, expectedExceptionsMessageRegExp = "Types are not comparable with '<>': bigint vs varchar") public void testTypeMismatch() { computeActual("SELECT 1 <> 'x'"); } @BeforeClass(alwaysRun = true) public void setupDatabase() throws Exception { Logging.initialize(); handle = DBI.open("jdbc:h2:mem:test" + System.nanoTime()); RecordSet ordersRecords = readTpchRecords(TPCH_ORDERS_METADATA); handle.execute("CREATE TABLE orders (\n" + " orderkey BIGINT PRIMARY KEY,\n" + " custkey BIGINT NOT NULL,\n" + " orderstatus CHAR(1) NOT NULL,\n" + " totalprice DOUBLE NOT NULL,\n" + " orderdate CHAR(10) NOT NULL,\n" + " orderpriority CHAR(15) NOT NULL,\n" + " clerk CHAR(15) NOT NULL,\n" + " shippriority BIGINT NOT NULL,\n" + " comment VARCHAR(79) NOT NULL\n" + ")"); insertRows(TPCH_ORDERS_METADATA, handle, ordersRecords); RecordSet lineItemRecords = readTpchRecords(TPCH_LINEITEM_METADATA); handle.execute("CREATE TABLE lineitem (\n" + " orderkey BIGINT,\n" + " partkey BIGINT NOT NULL,\n" + " suppkey BIGINT NOT NULL,\n" + " linenumber BIGINT,\n" + " quantity BIGINT NOT NULL,\n" + " extendedprice DOUBLE NOT NULL,\n" + " discount DOUBLE NOT NULL,\n" + " tax DOUBLE NOT NULL,\n" + " returnflag CHAR(1) NOT NULL,\n" + " linestatus CHAR(1) NOT NULL,\n" + " shipdate CHAR(10) NOT NULL,\n" + " commitdate CHAR(10) NOT NULL,\n" + " receiptdate CHAR(10) NOT NULL,\n" + " shipinstruct VARCHAR(25) NOT NULL,\n" + " shipmode VARCHAR(10) NOT NULL,\n" + " comment VARCHAR(44) NOT NULL,\n" + " PRIMARY KEY (orderkey, linenumber)" + ")"); insertRows(TPCH_LINEITEM_METADATA, handle, lineItemRecords); setUpQueryFramework(TpchMetadata.TPCH_CATALOG_NAME, TpchMetadata.TPCH_SCHEMA_NAME); } @AfterClass(alwaysRun = true) public void cleanupDatabase() throws Exception { tearDownQueryFramework(); handle.close(); } protected abstract int getNodeCount(); protected abstract void setUpQueryFramework(String catalog, String schema) throws Exception; protected void tearDownQueryFramework() throws Exception { } protected abstract MaterializedResult computeActual(@Language("SQL") String sql); protected void assertQuery(@Language("SQL") String sql) throws Exception { assertQuery(sql, sql, false); } private void assertQueryOrdered(@Language("SQL") String sql) throws Exception { assertQuery(sql, sql, true); } protected void assertQuery(@Language("SQL") String actual, @Language("SQL") String expected) throws Exception { assertQuery(actual, expected, false); } private static final Logger log = Logger.get(AbstractTestQueries.class); private void assertQuery(@Language("SQL") String actual, @Language("SQL") String expected, boolean ensureOrdering) throws Exception { long start = System.nanoTime(); MaterializedResult actualResults = computeActual(actual); log.info("FINISHED in %s", Duration.nanosSince(start)); MaterializedResult expectedResults = computeExpected(expected, actualResults.getTupleInfo()); if (ensureOrdering) { assertEquals(actualResults.getMaterializedTuples(), expectedResults.getMaterializedTuples()); } else { assertEqualsIgnoreOrder(actualResults.getMaterializedTuples(), expectedResults.getMaterializedTuples()); } } public static void assertEqualsIgnoreOrder(Iterable<?> actual, Iterable<?> expected) { assertNotNull(actual, "actual is null"); assertNotNull(expected, "expected is null"); ImmutableMultiset<?> actualSet = ImmutableMultiset.copyOf(actual); ImmutableMultiset<?> expectedSet = ImmutableMultiset.copyOf(expected); if (!actualSet.equals(expectedSet)) { fail(format("not equal\nActual %s rows:\n %s\nExpected %s rows:\n %s\n", actualSet.size(), Joiner.on("\n ").join(Iterables.limit(actualSet, 100)), expectedSet.size(), Joiner.on("\n ").join(Iterables.limit(expectedSet, 100)))); } } private MaterializedResult computeExpected(@Language("SQL") final String sql, TupleInfo resultTupleInfo) { return new MaterializedResult(handle.createQuery(sql).map(tupleMapper(resultTupleInfo)).list(), resultTupleInfo); } private static ResultSetMapper<Tuple> tupleMapper(final TupleInfo tupleInfo) { return new ResultSetMapper<Tuple>() { @Override public Tuple map(int index, ResultSet resultSet, StatementContext ctx) throws SQLException { List<TupleInfo.Type> types = tupleInfo.getTypes(); int count = resultSet.getMetaData().getColumnCount(); checkArgument(types.size() == count, "tuple info does not match result"); TupleInfo.Builder builder = tupleInfo.builder(); for (int i = 1; i <= count; i++) { TupleInfo.Type type = types.get(i - 1); switch (type) { case BOOLEAN: boolean booleanValue = resultSet.getBoolean(i); if (resultSet.wasNull()) { builder.appendNull(); } else { builder.append(booleanValue); } break; case FIXED_INT_64: long longValue = resultSet.getLong(i); if (resultSet.wasNull()) { builder.appendNull(); } else { builder.append(longValue); } break; case DOUBLE: double doubleValue = resultSet.getDouble(i); if (resultSet.wasNull()) { builder.appendNull(); } else { builder.append(doubleValue); } break; case VARIABLE_BINARY: String value = resultSet.getString(i); if (resultSet.wasNull()) { builder.appendNull(); } else { builder.append(Slices.wrappedBuffer(value.getBytes(UTF_8))); } break; default: throw new AssertionError("unhandled type: " + type); } } return builder.build(); } }; } private static void insertRows(TableMetadata tableMetadata, Handle handle, RecordSet data) { String vars = Joiner.on(',').join(nCopies(tableMetadata.getColumns().size(), "?")); String sql = format("INSERT INTO %s VALUES (%s)", tableMetadata.getTable().getTableName(), vars); RecordCursor cursor = data.cursor(); while (true) { // insert 1000 rows at a time PreparedBatch batch = handle.prepareBatch(sql); for (int row = 0; row < 1000; row++) { if (!cursor.advanceNextPosition()) { batch.execute(); return; } PreparedBatchPart part = batch.add(); for (int column = 0; column < tableMetadata.getColumns().size(); column++) { ColumnMetadata columnMetadata = tableMetadata.getColumns().get(column); switch (columnMetadata.getType()) { case BOOLEAN: part.bind(column, cursor.getBoolean(column)); break; case LONG: part.bind(column, cursor.getLong(column)); break; case DOUBLE: part.bind(column, cursor.getDouble(column)); break; case STRING: part.bind(column, new String(cursor.getString(column), UTF_8)); break; } } } batch.execute(); } } private Function<MaterializedTuple, String> onlyColumnGetter() { return new Function<MaterializedTuple, String>() { @Override public String apply(MaterializedTuple input) { assertEquals(input.getFieldCount(), 1); return (String) input.getField(0); } }; } private static String getExplainPlan(String query, ExplainType.Type planType) { QueryExplainer explainer = getQueryExplainer(); return explainer.getPlan((Query) SqlParser.createStatement(query), planType); } private static String getGraphvizExplainPlan(String query, ExplainType.Type planType) { QueryExplainer explainer = getQueryExplainer(); return explainer.getGraphvizPlan((Query) SqlParser.createStatement(query), planType); } private static QueryExplainer getQueryExplainer() { Session session = new Session("user", "test", DEFAULT_CATALOG, DEFAULT_SCHEMA, null, null); MetadataManager metadata = new MetadataManager(); metadata.addInternalSchemaMetadata(new DualMetadata()); List<PlanOptimizer> optimizers = new PlanOptimizersFactory(metadata).get(); return new QueryExplainer(session, optimizers, metadata, new MockPeriodicImportManager(), new MockStorageManager()); } }