/** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query and columns selected are subset of columns in materialized view. */ @Test public void testFilterQueryOnFilterView6() { checkMaterialize( "select \"name\", \"deptno\", \"salary\" from \"emps\" " + "where \"salary\" > 2000.5", "select \"name\" from \"emps\" where \"deptno\" > 30 and \"salary\" > 3000"); }
/** Aggregation query at same level of aggregation as aggregation * materialization. */ @Test public void testAggregate() { checkMaterialize( "select \"deptno\", count(*) as c, sum(\"empid\") as s from \"emps\" group by \"deptno\"", "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\""); }
@Test public void testAggregateMaterializationAggregateFuncs10() { checkMaterialize( "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month)", "select floor(cast('1997-01-20 12:34:56' as timestamp) to year), sum(\"empid\") + 1 as s\n" + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to year)"); }
@Test public void testAggregateMaterializationAggregateFuncs12() { checkMaterialize( "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to second), count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to second)", "select floor(cast('1997-01-20 12:34:56' as timestamp) to month), sum(\"empid\") as s\n" + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to month)"); }
/** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query and columns selected are subset of columns in materialized view. * Condition here is complex. */ @Test public void testFilterQueryOnFilterView7() { checkMaterialize( "select * from \"emps\" where " + "((\"salary\" < 1111.9 and \"deptno\" > 10)" + "or (\"empid\" > 400 and \"salary\" > 5000) " + "or \"salary\" > 500)", "select \"name\" from \"emps\" where (\"salary\" > 1000 " + "or (\"deptno\" >= 30 and \"salary\" <= 500))"); }
/** As {@link #testFilterQueryOnFilterView13()} but using alias * and condition of query is stronger. */ @Test public void testAlias() { checkMaterialize( "select * from \"emps\" as em where " + "(em.\"salary\" < 1111.9 and em.\"deptno\" > 10)" + "or (em.\"empid\" > 400 and em.\"salary\" > 5000)", "select \"name\" as n from \"emps\" as e where " + "(e.\"empid\" > 500 and e.\"salary\" > 6000)"); }
@Test public void testJoinMaterialization3() { String q = "select \"empid\" \"deptno\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"empid\" = 1"; final String m = "select \"empid\" \"deptno\" from \"emps\"\n" + "join \"depts\" using (\"deptno\")"; checkMaterialize(m, q); }
@Test public void testAggregateMaterializationAggregateFuncs14() { checkMaterialize( "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month)", "select floor(cast('1997-01-20 12:34:56' as timestamp) to hour), sum(\"empid\") as s\n" + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to hour)"); }
@Test public void testAggregateMaterializationAggregateFuncs16() { checkMaterialize( "select \"eventid\", cast(\"ts\" as timestamp), count(*) + 1 as c, sum(\"eventid\") as s\n" + "from \"events\" group by \"eventid\", cast(\"ts\" as timestamp)", "select floor(cast(\"ts\" as timestamp) to year), sum(\"eventid\") as s\n" + "from \"events\" group by floor(cast(\"ts\" as timestamp) to year)"); }
@Test public void testAggregateMaterializationAggregateFuncs19() { checkMaterialize( "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", \"deptno\"", "select \"empid\" + 10, count(*) + 1 as c\n" + "from \"emps\" group by \"empid\" + 10"); }
@Test public void testAggregateMaterializationAggregateFuncs14() { checkMaterialize( "select \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", floor(cast('1997-01-20 12:34:56' as timestamp) to month)", "select floor(cast('1997-01-20 12:34:56' as timestamp) to hour), sum(\"empid\") as s\n" + "from \"emps\" group by floor(cast('1997-01-20 12:34:56' as timestamp) to hour)"); }
@Test public void testAggregateMaterializationNoAggregateFuncs2() { checkMaterialize( "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\" from \"emps\" group by \"deptno\"", HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}])\n" + " EnumerableTableScan(table=[[hr, m0]])")); }
@Test public void testAggregateMaterializationAggregateFuncs3() { checkMaterialize( "select \"empid\", \"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\", \"empid\", sum(\"empid\") as s, count(*) as c\n" + "from \"emps\" group by \"empid\", \"deptno\"", HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t1], empid=[$t0], " + "S=[$t3], C=[$t2])\n" + " EnumerableTableScan(table=[[hr, m0]])")); }
/** As {@link #testFilterQueryOnFilterView()} but condition is stronger in * query. */ @Ignore @Test public void testFilterQueryOnFilterView2() { checkMaterialize( "select \"deptno\", \"empid\", \"name\" from \"emps\" where \"deptno\" = 10", "select \"empid\" + 1 as x, \"name\" from \"emps\" " + "where \"deptno\" = 10 and \"empid\" < 150"); }
@Test public void testAggregateMaterializationNoAggregateFuncs2() { checkMaterialize( "select \"empid\", \"deptno\" from \"emps\" group by \"empid\", \"deptno\"", "select \"deptno\" from \"emps\" group by \"deptno\"", HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}])\n" + " EnumerableTableScan(table=[[hr, m0]])")); }
@Test public void testJoinAggregateMaterializationAggregateFuncs1() { // This test relies on FK-UK relationship checkMaterialize( "select \"empid\", \"depts\".\"deptno\", count(*) as c, sum(\"empid\") as s\n" + "from \"emps\" join \"depts\" using (\"deptno\")\n" + "group by \"empid\", \"depts\".\"deptno\"", "select \"deptno\" from \"emps\" group by \"deptno\"", HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableAggregate(group=[{1}])\n" + " EnumerableTableScan(table=[[hr, m0]])")); }
@Test public void testUnionAll() { String q = "select * from \"emps\" where \"empid\" > 300\n" + "union all select * from \"emps\" where \"empid\" < 200"; String m = "select * from \"emps\" where \"empid\" < 500"; checkMaterialize(m, q, HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableTableScan(table=[[hr, m0]])", 1)); }
@Test public void testJoinMaterialization5() { checkMaterialize( "select cast(\"empid\" as BIGINT) from \"emps\"\n" + "join \"depts\" using (\"deptno\")", "select \"empid\" \"deptno\" from \"emps\"\n" + "join \"depts\" using (\"deptno\") where \"empid\" > 1", HR_FKUK_MODEL, CalciteAssert.checkResultContains( "EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):JavaType(int) NOT NULL], " + "expr#2=[1], expr#3=[>($t1, $t2)], EXPR$0=[$t1], $condition=[$t3])\n" + " EnumerableTableScan(table=[[hr, m0]])")); }
/** Aggregation materialization with a project. */ @Ignore("work in progress") @Test public void testAggregateProject() { // Note that materialization does not start with the GROUP BY columns. // Not a smart way to design a materialization, but people may do it. checkMaterialize( "select \"deptno\", count(*) as c, \"empid\" + 2, sum(\"empid\") as s from \"emps\" group by \"empid\", \"deptno\"", "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\"", HR_FKUK_MODEL, CalciteAssert.checkResultContains( "xxx")); }
/** Aggregation materialization with a project. */ @Ignore("work in progress") @Test public void testAggregateProject() { // Note that materialization does not start with the GROUP BY columns. // Not a smart way to design a materialization, but people may do it. checkMaterialize( "select \"deptno\", count(*) as c, \"empid\" + 2, sum(\"empid\") as s from \"emps\" group by \"empid\", \"deptno\"", "select count(*) + 1 as c, \"deptno\" from \"emps\" group by \"deptno\"", HR_FKUK_MODEL, CalciteAssert.checkResultContains( "xxx")); }