com.facebook.presto.AbstractTestQueries.java Source code

Java tutorial

Introduction

Here is the source code for com.facebook.presto.AbstractTestQueries.java

Source

/*
 * 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());
    }
}