Please wait. This can take some minutes ...
Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.facebook.presto.tests.AbstractTestIndexedQueries Maven / Gradle / Ivy
package com.facebook.presto.tests;
import com.facebook.presto.testing.MaterializedResult;
import com.facebook.presto.testing.QueryRunner;
import com.facebook.presto.tests.tpch.TpchIndexSpec;
import com.facebook.presto.tests.tpch.TpchIndexSpec.Builder;
import com.facebook.presto.tpch.TpchMetadata;
import com.google.common.collect.ImmutableSet;
import org.testng.annotations.Test;
import static org.testng.Assert.assertEquals;
import static org.testng.Assert.assertTrue;
public abstract class AbstractTestIndexedQueries
extends AbstractTestQueryFramework
{
public static final TpchIndexSpec INDEX_SPEC = new Builder()
.addIndex("orders" , TpchMetadata.TINY_SCALE_FACTOR, ImmutableSet.of("orderkey" ))
.addIndex("orders" , TpchMetadata.TINY_SCALE_FACTOR, ImmutableSet.of("orderkey" , "orderstatus" ))
.addIndex("orders" , TpchMetadata.TINY_SCALE_FACTOR, ImmutableSet.of("orderkey" , "custkey" ))
.addIndex("orders" , TpchMetadata.TINY_SCALE_FACTOR, ImmutableSet.of("orderstatus" , "shippriority" ))
.build();
protected AbstractTestIndexedQueries (QueryRunner queryRunner)
{
super (queryRunner);
}
@Test
public void testExampleSystemTable ()
throws Exception
{
assertQuery("SELECT name FROM sys.example" , "SELECT 'test' AS name" );
MaterializedResult result = computeActual("SHOW SCHEMAS" );
assertTrue(result.getOnlyColumnAsSet().containsAll(ImmutableSet.of("sf100" , "tiny" , "sys" )));
result = computeActual("SHOW TABLES FROM sys" );
assertEquals(result.getOnlyColumnAsSet(), ImmutableSet.of("example" ));
}
@Test
public void testBasicIndexJoin ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testBasicIndexJoinReverseCandidates ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM orders o " +
"JOIN (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
" ON o.orderkey = l.orderkey" );
}
@Test
public void testBasicIndexJoinWithNullKeys ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT CASE WHEN suppkey % 2 = 0 THEN orderkey ELSE NULL END AS orderkey\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testMultiKeyIndexJoinAligned ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT orderkey, CASE WHEN suppkey % 2 = 0 THEN 'F' ELSE 'O' END AS orderstatus\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderkey = o.orderkey AND l.orderstatus = o.orderstatus" );
}
@Test
public void testMultiKeyIndexJoinUnaligned ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT orderkey, CASE WHEN suppkey % 2 = 0 THEN 'F' ELSE 'O' END AS orderstatus\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderstatus = o.orderstatus AND l.orderkey = o.orderkey" );
}
@Test
public void testPredicateDerivedKey ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT orderkey\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderkey = o.orderkey\n" +
"WHERE o.orderstatus = 'F'" );
}
@Test
public void testCompoundPredicateDerivedKey ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT orderkey\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderkey = o.orderkey\n" +
"WHERE o.orderstatus = 'F'\n" +
" AND o.custkey % 2 = 0" );
}
@Test
public void testChainedIndexJoin ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT orderkey, CASE WHEN suppkey % 2 = 0 THEN 'F' ELSE 'O' END AS orderstatus\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o1\n" +
" ON l.orderkey = o1.orderkey AND l.orderstatus = o1.orderstatus\n" +
"JOIN orders o2\n" +
" ON o1.custkey % 1024 = o2.orderkey" );
}
@Test
public void testBasicLeftIndexJoin ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"LEFT JOIN orders o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testNonIndexLeftJoin ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM orders o " +
"LEFT JOIN (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
" ON o.orderkey = l.orderkey" );
}
@Test
public void testBasicRightIndexJoin ()
throws Exception
{
assertQuery("" +
"SELECT COUNT(*)\n" +
"FROM orders o " +
"RIGHT JOIN (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
" ON o.orderkey = l.orderkey" );
}
@Test
public void testNonIndexRightJoin ()
throws Exception
{
assertQuery("" +
"SELECT COUNT(*)\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"RIGHT JOIN orders o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testIndexJoinThroughAggregation ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN (\n" +
" SELECT orderkey, COUNT(*)\n" +
" FROM orders\n" +
" WHERE custkey % 8 = 0\n" +
" GROUP BY orderkey\n" +
" ORDER BY orderkey) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testIndexJoinThroughMultiKeyAggregation ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN (\n" +
" SELECT shippriority, orderkey, COUNT(*)\n" +
" FROM orders\n" +
" WHERE custkey % 8 = 0\n" +
" GROUP BY shippriority, orderkey\n" +
" ORDER BY orderkey) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testNonIndexableKeys ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN (\n" +
" SELECT orderkey % 2 as orderkey\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testComposableIndexJoins ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) x\n" +
"JOIN (\n" +
" SELECT o1.orderkey as orderkey, o2.custkey as custkey\n" +
" FROM orders o1\n" +
" JOIN orders o2\n" +
" ON o1.orderkey = o2.orderkey) y\n" +
" ON x.orderkey = y.orderkey\n" );
}
@Test
public void testNonComposableIndexJoins ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) x\n" +
"JOIN (\n" +
" SELECT l.orderkey as orderkey, o.custkey as custkey\n" +
" FROM lineitem l\n" +
" JOIN orders o\n" +
" ON l.orderkey = o.orderkey) y\n" +
" ON x.orderkey = y.orderkey\n" );
}
@Test
public void testOverlappingIndexJoinLookupSymbol ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderkey % 1024 = o.orderkey AND l.partkey % 1024 = o.orderkey" );
}
@Test
public void testOverlappingSourceOuterIndexJoinLookupSymbol ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"LEFT JOIN orders o\n" +
" ON l.orderkey % 1024 = o.orderkey AND l.partkey % 1024 = o.orderkey" );
}
@Test
public void testOverlappingIndexJoinProbeSymbol ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderkey = o.orderkey AND l.orderkey = o.custkey" );
}
@Test
public void testOverlappingSourceOuterIndexJoinProbeSymbol ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"LEFT JOIN orders o\n" +
" ON l.orderkey = o.orderkey AND l.orderkey = o.custkey" );
}
@Test
public void testRepeatedIndexJoinClause ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN orders o\n" +
" ON l.orderkey = o.orderkey AND l.orderkey = o.orderkey" );
}
@Test
public void testProbeNullInReadahead ()
throws Exception
{
assertQuery(
"select count(*) from (values (1), (cast(null as bigint))) x(orderkey) join orders using (orderkey)" ,
"select count(*) from orders where orderkey = 1" );
}
@Test
public void testHighCardinalityIndexJoinResult ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM orders\n" +
" WHERE orderkey % 10000 = 0) o1\n" +
"JOIN (\n" +
" SELECT *\n" +
" FROM orders\n" +
" WHERE orderkey % 4 = 0) o2\n" +
" ON o1.orderstatus = o2.orderstatus AND o1.shippriority = o2.shippriority" );
}
@Test
public void testReducedIndexProbeKey ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT orderkey % 64 AS a, suppkey % 2 AS b\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN (\n" +
" SELECT orderkey AS a, SUM(LENGTH(comment)) % 2 AS b\n" +
" FROM orders\n" +
" GROUP BY orderkey) o\n" +
" ON l.a = o.a AND l.b = o.b" );
}
@Test
public void testReducedIndexProbeKeyNegativeCaching ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT orderkey % 64 AS a, (suppkey % 2) + 1 AS b\n" +
" FROM lineitem\n" +
" WHERE partkey % 8 = 0) l\n" +
"JOIN (\n" +
" SELECT orderkey AS a, SUM(LENGTH(comment)) % 2 AS b\n" +
" FROM orders\n" +
" GROUP BY orderkey) o\n" +
" ON l.a = o.a AND l.b = o.b" );
}
@Test
public void testHighCardinalityReducedIndexProbeKey ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *, custkey % 4 AS x, custkey % 2 AS y\n" +
" FROM orders\n" +
" WHERE orderkey % 10000 = 0) o1\n" +
"JOIN (\n" +
" SELECT *, custkey % 5 AS x, custkey % 3 AS y\n" +
" FROM orders\n" +
" WHERE orderkey % 4 = 0) o2\n" +
" ON o1.orderstatus = o2.orderstatus AND o1.shippriority = o2.shippriority AND o1.x = o2.x AND o1.y = o2.y" );
}
@Test
public void testReducedIndexProbeKeyComplexQueryShapes ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT orderkey % 64 AS a, suppkey % 2 AS b, orderkey AS c, linenumber % 2 AS d\n" +
" FROM lineitem\n" +
" WHERE partkey % 7 = 0) l\n" +
"JOIN (\n" +
" SELECT t1.a AS a, t1.b AS b, t2.orderkey AS c, SUM(LENGTH(t2.comment)) % 2 AS d\n" +
" FROM (\n" +
" SELECT orderkey AS a, custkey % 3 AS b\n" +
" FROM orders\n" +
" ) t1\n" +
" JOIN orders t2 ON t1.a = (t2.orderkey % 1000)\n" +
" WHERE t1.a % 1000 = 0\n" +
" GROUP BY t1.a, t1.b, t2.orderkey) o\n" +
" ON l.a = o.a AND l.b = o.b AND l.c = o.c AND l.d = o.d" );
}
@Test
public void testIndexJoinConstantPropagation ()
throws Exception
{
assertQuery("" +
"SELECT x, y, COUNT(*)\n" +
"FROM (SELECT orderkey, 0 AS x FROM orders) a \n" +
"JOIN (SELECT orderkey, 1 AS y FROM orders) b \n" +
"ON a.orderkey = b.orderkey\n" +
"GROUP BY 1, 2" );
}
@Test
public void testIndexJoinThroughWindow ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, COUNT(*) OVER (PARTITION BY orderkey)\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" ,
"" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, 1\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testIndexJoinThroughWindowDoubleAggregation ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, COUNT(*) OVER (PARTITION BY orderkey), SUM(orderkey) OVER (PARTITION BY orderkey)\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" ,
"" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, 1, orderkey as o\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testIndexJoinThroughWindowPartialPartition ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, COUNT(*) OVER (PARTITION BY orderkey, custkey)\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" ,
"" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, 1\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testNoIndexJoinThroughWindowWithRowNumberFunction ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, row_number() OVER (PARTITION BY orderkey)\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" ,
"" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, 1\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testNoIndexJoinThroughWindowWithOrderBy ()
throws Exception
{
assertQuery("" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, COUNT(*) OVER (PARTITION BY orderkey ORDER BY custkey)\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" ,
"" +
"SELECT *\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, 1\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testNoIndexJoinThroughWindowWithRowFrame ()
throws Exception
{
assertQuery("" +
"SELECT l.orderkey, o.c\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, COUNT(*) OVER (PARTITION BY orderkey ROWS 1 PRECEDING) as c\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" ,
"" +
"SELECT l.orderkey, o.c\n" +
"FROM (\n" +
" SELECT *\n" +
" FROM lineitem\n" +
" WHERE partkey % 16 = 0) l\n" +
"JOIN (\n" +
" SELECT *, 1 as c\n" +
" FROM orders) o\n" +
" ON l.orderkey = o.orderkey" );
}
@Test
public void testOuterNonEquiJoins ()
throws Exception
{
assertQuery("SELECT COUNT(*) FROM lineitem LEFT OUTER JOIN orders ON lineitem.orderkey = orders.orderkey AND lineitem.quantity > 5 WHERE orders.orderkey IS NULL" );
assertQuery("SELECT COUNT(*) FROM orders RIGHT OUTER JOIN lineitem ON lineitem.orderkey = orders.orderkey AND lineitem.quantity > 5 WHERE orders.orderkey IS NULL" );
}
}