Repository: calcite Updated Branches: refs/heads/master 98f3704ea -> 5acf84f30 (forced update)
http://git-wip-us.apache.org/repos/asf/calcite/blob/5acf84f3/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java ---------------------------------------------------------------------- diff --git a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java index 9d3191c..7c7ba4a 100644 --- a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java +++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java @@ -35,7 +35,6 @@ import com.google.common.collect.ImmutableList; import com.google.common.collect.ImmutableMap; import com.google.common.collect.Multimap; -import org.junit.Ignore; import org.junit.Test; import java.net.URL; @@ -101,6 +100,8 @@ public class DruidAdapterIT { private static final String VARCHAR_TYPE = "VARCHAR CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\""; + private static final String FOODMART_TABLE = "\"foodmart\""; + /** Whether to run this test. */ protected boolean enabled() { return ENABLED; @@ -197,8 +198,9 @@ public class DruidAdapterIT { + "intervals=[[1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z]], " + "filter=[=($17, 'Jeremy Corbyn')], groups=[{7}], aggs=[[]])\n"; final String druidQuery = "{'queryType':'groupBy'," - + "'dataSource':'wikiticker','granularity':{'type':'all'}," - + "'dimensions':[{'type':'default','dimension':'countryName'}],'limitSpec':{'type':'default'}," + + "'dataSource':'wikiticker','granularity':'all'," + + "'dimensions':[{'type':'default','dimension':'countryName','outputName':'countryName'," + + "'outputType':'STRING'}],'limitSpec':{'type':'default'}," + "'filter':{'type':'selector','dimension':'page','value':'Jeremy Corbyn'}," + "'aggregations':[]," + "'intervals':['1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z']}"; @@ -239,17 +241,18 @@ public class DruidAdapterIT { + "from \"wikiticker\"\n" + "limit 1\n"; final String explain = - "DruidQuery(table=[[wiki, wikiticker]], intervals=[[1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z]], projects=[[$0]], fetch=[1])\n"; + "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[wiki, wikiticker]], intervals=[[1900-01-01T00:00:00.000Z/" + + "3000-01-01T00:00:00.000Z]], projects=[[CAST($0):TIMESTAMP(0) NOT NULL]], fetch=[1])"; final String druidQuery = "{'queryType':'scan'," + "'dataSource':'wikiticker'," + "'intervals':['1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z']," - + "'columns':['__time'],'granularity':{'type':'all'}," + + "'columns':['__time'],'granularity':'all'," + "'resultFormat':'compactedList','limit':1}"; sql(sql, WIKI_AUTO2) .returnsUnordered("__time=2015-09-12 00:46:58") - .explainContains(explain) - .queryContains(druidChecker(druidQuery)); + .explainContains(explain); } @Test public void testSelectTimestampColumnNoTables3() { @@ -260,7 +263,8 @@ public class DruidAdapterIT { + "from \"wikiticker\"\n" + "group by floor(\"__time\" to DAY)"; final String explain = - "DruidQuery(table=[[wiki, wikiticker]], intervals=[[1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z]], projects=[[FLOOR($0, FLAG(DAY)), $1]], groups=[{0}], aggs=[[SUM($1)]])\n"; + "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[wiki, wikiticker]], intervals=[[1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z]], projects=[[FLOOR($0, FLAG(DAY)), $1]], groups=[{0}], aggs=[[SUM($1)]], post_projects=[[CAST($0):TIMESTAMP(0) NOT NULL, $1]])"; final String druidQuery = "{'queryType':'timeseries'," + "'dataSource':'wikiticker','descending':false,'granularity':{'type':'period','period':'P1D','timeZone':'UTC'}," + "'aggregations':[{'type':'longSum','name':'EXPR$1','fieldName':'added'}]," @@ -281,10 +285,7 @@ public class DruidAdapterIT { + "group by \"page\", floor(\"__time\" to DAY)\n" + "order by \"s\" desc"; final String explain = "PLAN=EnumerableInterpreter\n" - + " BindableProject(s=[$2], page=[$0], day=[CAST($1):TIMESTAMP(0) NOT NULL])\n" - + " DruidQuery(table=[[wiki, wikiticker]], " - + "intervals=[[1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z]], projects=[[$17, FLOOR" - + "($0, FLAG(DAY)), $1]], groups=[{0, 1}], aggs=[[SUM($2)]], sort0=[2], dir0=[DESC])"; + + " DruidQuery(table=[[wiki, wikiticker]], intervals=[[1900-01-01T00:00:00.000Z/3000-01-01T00:00:00.000Z]], projects=[[$17, FLOOR($0, FLAG(DAY)), $1]], groups=[{0, 1}], aggs=[[SUM($2)]], post_projects=[[$2, $0, CAST($1):TIMESTAMP(0) NOT NULL]], sort0=[0], dir0=[DESC])"; sql(sql, WIKI_AUTO2) .limit(1) .returnsUnordered("s=199818; page=User:QuackGuru/Electronic cigarettes 1; " @@ -319,8 +320,9 @@ public class DruidAdapterIT { + "from \"" + tableName + "\"\n" + "where \"page\" = 'Jeremy Corbyn'"; final String druidQuery = "{'queryType':'groupBy'," - + "'dataSource':'wikiticker','granularity':{'type':'all'}," - + "'dimensions':[{'type':'default','dimension':'countryName'}],'limitSpec':{'type':'default'}," + + "'dataSource':'wikiticker','granularity':'all'," + + "'dimensions':[{'type':'default','dimension':'countryName','outputName':'countryName'," + + "'outputType':'STRING'}],'limitSpec':{'type':'default'}," + "'filter':{'type':'selector','dimension':'page','value':'Jeremy Corbyn'}," + "'aggregations':[]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; @@ -338,13 +340,14 @@ public class DruidAdapterIT { final String sql = "select cast(\"__time\" as timestamp) as \"__time\"\n" + "from \"wikiticker\"\n" + "where \"__time\" < '2015-10-12 00:00:00 UTC'"; - final String explain = "\n DruidQuery(table=[[wiki, wikiticker]], " - + "intervals=[[1900-01-01T00:00:00.000Z/2015-10-12T00:00:00.000Z]], " - + "projects=[[$0]])\n"; + final String explain = "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[wiki, wikiticker]]," + + " intervals=[[1900-01-01T00:00:00.000Z/2015-10-12T00:00:00.000Z]], " + + "projects=[[CAST($0):TIMESTAMP(0) NOT NULL]])"; final String druidQuery = "{'queryType':'scan'," + "'dataSource':'wikiticker'," + "'intervals':['1900-01-01T00:00:00.000Z/2015-10-12T00:00:00.000Z']," - + "'columns':['__time'],'granularity':{'type':'all'}," + + "'virtualColumns':[{'type':'expression','name':'vc','expression':'\\'__time\\'','outputType':'LONG'}],'columns':['vc']," + "'resultFormat':'compactedList'"; sql(sql, WIKI_AUTO2) .limit(2) @@ -355,26 +358,24 @@ public class DruidAdapterIT { } @Test public void testFilterTimeDistinct() { - final String sql = "select CAST(\"c1\" AS timestamp) as \"__time\" from\n" + final String sql = "select CAST(\"c1\" AS timestamp) as \"time\" from\n" + "(select distinct \"__time\" as \"c1\"\n" + "from \"wikiticker\"\n" + "where \"__time\" < '2015-10-12 00:00:00 UTC')"; - final String explain = "PLAN=" - + "EnumerableInterpreter\n" - + " BindableProject(__time=[CAST($0):TIMESTAMP(0) NOT NULL])\n" - + " DruidQuery(table=[[wiki, wikiticker]], " - + "intervals=[[1900-01-01T00:00:00.000Z/2015-10-12T00:00:00.000Z]], " - + "groups=[{0}], aggs=[[]])\n"; + final String explain = "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[wiki, wikiticker]], intervals=[[1900-01-01T00:00:00.000Z/" + + "3000-01-01T00:00:00.000Z]], projects=[[$0]], groups=[{0}], aggs=[[]], " + + "filter=[<($0, 2015-10-12 00:00:00)], projects=[[CAST($0):TIMESTAMP(0) NOT NULL]])\n"; final String subDruidQuery = "{'queryType':'groupBy','dataSource':'wikiticker'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'extraction'," + + "'granularity':'all','dimensions':[{'type':'extraction'," + "'dimension':'__time','outputName':'extract'," + "'extractionFn':{'type':'timeFormat'"; sql(sql, WIKI_AUTO2) .limit(2) + .returnsUnordered("time=2015-09-12 00:46:58", + "time=2015-09-12 00:47:00") .explainContains(explain) - .queryContains(druidChecker(subDruidQuery)) - .returnsUnordered("__time=2015-09-12 00:46:58", - "__time=2015-09-12 00:47:00"); + .queryContains(druidChecker(subDruidQuery)); } @Test public void testMetadataColumns() throws Exception { @@ -411,9 +412,9 @@ public class DruidAdapterIT { + "EnumerableInterpreter\n" + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], groups=[{30}], aggs=[[]])"; final String sql = "select distinct \"state_province\" from \"foodmart\""; - final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'}," - + "'dimensions':[{'type':'default','dimension':'state_province'}],'limitSpec':{'type':'default'}," + final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'," + + "'dimensions':[{'type':'default','dimension':'state_province','outputName':'state_province'" + + ",'outputType':'STRING'}],'limitSpec':{'type':'default'}," + "'aggregations':[]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; sql(sql) @@ -426,11 +427,9 @@ public class DruidAdapterIT { @Test public void testSelectGroupBySum() { final String explain = "PLAN=EnumerableInterpreter\n" - + " BindableAggregate(group=[{0}], U=[SUM($1)])\n" - + " BindableProject(state_province=[$0], $f1=[CAST($1):INTEGER])\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]]," - + " projects=[[$30, $89]])"; + + " DruidQuery(table=[[foodmart, foodmart]], " + + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " + + "projects=[[$30, CAST($89):INTEGER]], groups=[{0}], aggs=[[SUM($1)]])"; final String sql = "select \"state_province\", sum(cast(\"unit_sales\" as integer)) as u\n" + "from \"foodmart\"\n" + "group by \"state_province\""; @@ -448,12 +447,12 @@ public class DruidAdapterIT { + " DruidQuery(table=[[foodmart, foodmart]], " + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], filter=[=($1, 1020)]," + " projects=[[$90, $1]], groups=[{0, 1}], aggs=[[]])"; - final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'}," - + "'dimensions':[{'type':'default','dimension':'store_sales'}," - + "{'type':'default','dimension':'product_id'}],'limitSpec':{'type':'default'},'" - + "filter':{'type':'selector','dimension':'product_id','value':'1020'}," - + "'aggregations':[]," + final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'," + + "'dimensions':[{'type':'default','dimension':'store_sales',\"outputName\":\"store_sales\"," + + "'outputType':'DOUBLE'},{'type':'default','dimension':'product_id','outputName':" + + "'product_id','outputType':'STRING'}],'limitSpec':{'type':'default'}," + + "'filter':{'type':'bound','dimension':'product_id','lower':'1020','lowerStrict':false," + + "'upper':'1020','upperStrict':false,'ordering':'numeric'},'aggregations':[]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; sql(sql) .explainContains(plan) @@ -469,13 +468,13 @@ public class DruidAdapterIT { final String sql = "select \"product_id\" from \"foodmart\" where " + "\"product_id\" = 1020 group by \"product_id\""; final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'product_id'}]," - + "'limitSpec':{'type':'default'},'filter':{'type':'selector'," - + "'dimension':'product_id','value':'1020'}," - + "'aggregations':[]," - + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; - sql(sql).queryContains(druidChecker(druidQuery)).returnsUnordered("product_id=1020"); + + "'granularity':'all','dimensions':[{'type':'default'," + + "'dimension':'product_id','outputName':'product_id','outputType':'STRING'}]," + + "'limitSpec':{'type':'default'},'filter':{'type':'bound','dimension':'product_id'," + + "'lower':'1020','lowerStrict':false,'upper':'1020','upperStrict':false," + + "'ordering':'numeric'},'aggregations':[]," + + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; + sql(sql).returnsUnordered("product_id=1020").queryContains(druidChecker(druidQuery)); } @Test public void testComplexPushGroupBy() { @@ -483,12 +482,12 @@ public class DruidAdapterIT { + "\"product_id\" = 1020"; final String sql = "select \"id\" from (" + innerQuery + ") group by \"id\""; final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'}," - + "'dimensions':[{'type':'default','dimension':'product_id'}]," - + "'limitSpec':{'type':'default'}," - + "'filter':{'type':'selector','dimension':'product_id','value':'1020'}," - + "'aggregations':[]," - + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; + + "'granularity':'all'," + + "'dimensions':[{'type':'default','dimension':'product_id','outputName':'product_id'," + + "'outputType':'STRING'}],'limitSpec':{'type':'default'}," + + "'filter':{'type':'bound','dimension':'product_id','lower':'1020','lowerStrict':false," + + "'upper':'1020','upperStrict':false,'ordering':'numeric'},'aggregations':[]," + + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; sql(sql) .returnsUnordered("id=1020") .queryContains(druidChecker(druidQuery)); @@ -532,14 +531,13 @@ public class DruidAdapterIT { "gender=M; state_province=WA", "gender=F; state_province=WA") .queryContains( - druidChecker("{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'gender'},{'type':'default'," - + "'dimension':'state_province'}],'limitSpec':{'type':'default'," - + "'columns':[{'dimension':'state_province','direction':'ascending'," - + "'dimensionOrder':'alphanumeric'},{'dimension':'gender'," - + "'direction':'descending','dimensionOrder':'alphanumeric'}]}," - + "'aggregations':[]," + druidChecker("{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'," + + "'dimensions':[{'type':'default','dimension':'gender','outputName':'gender'," + + "'outputType':'STRING'},{'type':'default','dimension':'state_province'," + + "'outputName':'state_province','outputType':'STRING'}],'limitSpec':" + + "{'type':'default','columns':[{'dimension':'state_province','direction':'ascending'" + + ",'dimensionOrder':'lexicographic'},{'dimension':'gender','direction':'descending'," + + "'dimensionOrder':'lexicographic'}]},'aggregations':[]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}")) .explainContains(explain); } @@ -568,7 +566,7 @@ public class DruidAdapterIT { + "offset 2 fetch next 3 rows only"; final String druidQuery = "{'queryType':'scan','dataSource':'foodmart'," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']," - + "'columns':['state_province','product_name'],'granularity':{'type':'all'}," + + "'columns':['state_province','product_name']," + "'resultFormat':'compactedList'}"; sql(sql) .runs() @@ -580,7 +578,7 @@ public class DruidAdapterIT { + "from \"foodmart\" fetch next 3 rows only"; final String druidQuery = "{'queryType':'scan','dataSource':'foodmart'," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']," - + "'columns':['gender','state_province'],'granularity':{'type':'all'}," + + "'columns':['gender','state_province']," + "'resultFormat':'compactedList','limit':3"; sql(sql) .runs() @@ -591,8 +589,10 @@ public class DruidAdapterIT { final String sql = "select distinct \"gender\", \"state_province\"\n" + "from \"foodmart\" fetch next 3 rows only"; final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default','dimension':'gender'}," - + "{'type':'default','dimension':'state_province'}],'limitSpec':{'type':'default'," + + "'granularity':'all','dimensions':[{'type':'default','dimension':'gender'," + + "'outputName':'gender','outputType':'STRING'}," + + "{'type':'default','dimension':'state_province','outputName':'state_province'," + + "'outputType':'STRING'}],'limitSpec':{'type':'default'," + "'limit':3,'columns':[]}," + "'aggregations':[]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; @@ -617,16 +617,17 @@ public class DruidAdapterIT { + "group by \"brand_name\", \"gender\"\n" + "order by s desc limit 3"; final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'brand_name'},{'type':'default','dimension':'gender'}]," + + "'granularity':'all','dimensions':[{'type':'default'," + + "'dimension':'brand_name','outputName':'brand_name','outputType':'STRING'}," + + "{'type':'default','dimension':'gender','outputName':'gender','outputType':'STRING'}]," + "'limitSpec':{'type':'default','limit':3,'columns':[{'dimension':'S'," + "'direction':'descending','dimensionOrder':'numeric'}]}," + "'aggregations':[{'type':'longSum','name':'S','fieldName':'unit_sales'}]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; final String explain = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " - + "groups=[{2, 39}], aggs=[[SUM($89)]], sort0=[2], dir0=[DESC], fetch=[3])\n"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$2, $39, $89]], groups=[{0, 1}], " + + "aggs=[[SUM($2)]], sort0=[2], dir0=[DESC], fetch=[3])"; sql(sql) .runs() .returnsOrdered("brand_name=Hermanos; gender=M; S=4286", @@ -655,24 +656,22 @@ public class DruidAdapterIT { + "from \"foodmart\"\n" + "group by \"brand_name\"\n" + "order by s desc limit 3"; - final String approxDruid = "{'queryType':'topN','dataSource':'foodmart'," - + "'granularity':{'type':'all'}," - + "'dimension':{'type':'default','dimension':'brand_name'},'metric':'S'," + final String approxDruid = "{'queryType':'topN','dataSource':'foodmart','granularity':'all'," + + "'dimension':{'type':'default','dimension':'brand_name','outputName':'brand_name','outputType':'STRING'},'metric':'S'," + "'aggregations':[{'type':'longSum','name':'S','fieldName':'unit_sales'}]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']," + "'threshold':3}"; - final String exactDruid = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'brand_name'}],'limitSpec':{'type':'default','limit':3," - + "'columns':[{'dimension':'S','direction':'descending'," - + "'dimensionOrder':'numeric'}]},'aggregations':[{'type':'longSum'," - + "'name':'S','fieldName':'unit_sales'}]," + final String exactDruid = "{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'," + + "'dimensions':[{'type':'default','dimension':'brand_name','outputName':'brand_name'," + + "'outputType':'STRING'}],'limitSpec':{'type':'default','limit':3,'columns':" + + "[{'dimension':'S','direction':'descending','dimensionOrder':'numeric'}]},'aggregations':" + + "[{'type':'longSum','name':'S','fieldName':'unit_sales'}]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; final String druidQuery = approx ? approxDruid : exactDruid; final String explain = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " - + "groups=[{2}], aggs=[[SUM($89)]], sort0=[1], dir0=[DESC], fetch=[3])\n"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$2, $89]], groups=[{0}], " + + "aggs=[[SUM($1)]], sort0=[1], dir0=[DESC], fetch=[3])"; CalciteAssert.that() .enable(enabled()) .with(ImmutableMap.of("model", FOODMART.getPath())) @@ -700,10 +699,11 @@ public class DruidAdapterIT { + "group by \"brand_name\", floor(\"timestamp\" to DAY)\n" + "order by s desc limit 30"; final String explain = - " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[$2, FLOOR" - + "($0, FLAG(DAY)), $89]], groups=[{0, 1}], aggs=[[SUM($2)]], sort0=[2], dir0=[DESC], " - + "fetch=[30])"; + "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$2, FLOOR($0, FLAG(DAY)), $89]], " + + "groups=[{0, 1}], aggs=[[SUM($2)]], post_projects=[[$0, " + + "CAST($1):TIMESTAMP(0) NOT NULL, $2]], sort0=[2], dir0=[DESC], fetch=[30])"; sql(sql) .runs() .returnsStartingWith("brand_name=Ebony; D=1997-07-27 00:00:00; S=135", @@ -711,7 +711,7 @@ public class DruidAdapterIT { "brand_name=Hermanos; D=1997-05-09 00:00:00; S=115") .explainContains(explain) .queryContains( - druidChecker("'queryType':'groupBy'", "'granularity':{'type':'all'}", "'limitSpec" + druidChecker("'queryType':'groupBy'", "'granularity':'all'", "'limitSpec" + "':{'type':'default','limit':30,'columns':[{'dimension':'S'," + "'direction':'descending','dimensionOrder':'numeric'}]}")); } @@ -731,19 +731,17 @@ public class DruidAdapterIT { + "from \"foodmart\"\n" + "group by \"brand_name\", floor(\"timestamp\" to DAY)\n" + "order by s desc limit 30"; - final String druidQueryPart1 = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'brand_name'},{'type':'extraction','dimension':'__time'," - + "'outputName':'floor_day','extractionFn':{'type':'timeFormat'"; + final String druidQueryPart1 = "{'queryType':'groupBy','dataSource':'foodmart'"; final String druidQueryPart2 = "'limitSpec':{'type':'default','limit':30," + "'columns':[{'dimension':'S','direction':'descending'," + "'dimensionOrder':'numeric'}]},'aggregations':[{'type':'longSum'," + "'name':'S','fieldName':'unit_sales'}]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; - final String explain = "DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[$2, FLOOR" - + "($0, FLAG(DAY)), $89]], groups=[{0, 1}], aggs=[[SUM($2)]], sort0=[2], dir0=[DESC], " - + "fetch=[30])"; + final String explain = "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$2, FLOOR($0, FLAG(DAY)), $89]], groups=[{0, 1}], " + + "aggs=[[SUM($2)]], post_projects=[[$0, CAST($1):TIMESTAMP(0) NOT NULL, $2]], " + + "sort0=[2], dir0=[DESC], fetch=[30])"; sql(sql) .runs() .returnsStartingWith("brand_name=Ebony; D=1997-07-27 00:00:00; S=135", @@ -764,12 +762,15 @@ public class DruidAdapterIT { + "group by \"brand_name\", floor(\"timestamp\" to DAY)\n" + "order by \"brand_name\""; final String subDruidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'brand_name'},{'type':'extraction','dimension':'__time'," + + "'granularity':'all','dimensions':[{'type':'default'," + + "'dimension':'brand_name','outputName':'brand_name','outputType':'STRING'}," + + "{'type':'extraction','dimension':'__time'," + "'outputName':'floor_day','extractionFn':{'type':'timeFormat'"; - final String explain = " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[$2, FLOOR" - + "($0, FLAG(DAY)), $89]], groups=[{0, 1}], aggs=[[SUM($2)]], sort0=[0], dir0=[ASC])"; + final String explain = "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$2, FLOOR($0, FLAG(DAY)), $89]], groups=[{0, 1}]," + + " aggs=[[SUM($2)]], post_projects=[[$0, CAST($1):TIMESTAMP(0) NOT NULL, $2]], " + + "sort0=[0], dir0=[ASC])"; sql(sql) .runs() .returnsStartingWith("brand_name=ADJ; D=1997-01-11 00:00:00; S=2", @@ -790,7 +791,7 @@ public class DruidAdapterIT { + "'filter':{'type':'and','fields':[" + "{'type':'bound','dimension':'product_id','lower':'1500','lowerStrict':false,'ordering':'lexicographic'}," + "{'type':'bound','dimension':'product_id','upper':'1502','upperStrict':false,'ordering':'lexicographic'}]}," - + "'columns':['product_name','state_province','product_id'],'granularity':{'type':'all'}," + + "'columns':['product_name','state_province','product_id']," + "'resultFormat':'compactedList'"; sql(sql) .limit(4) @@ -823,7 +824,7 @@ public class DruidAdapterIT { + "'filter':{'type':'and','fields':[" + "{'type':'bound','dimension':'product_id','lower':'1500','lowerStrict':false,'ordering':'numeric'}," + "{'type':'bound','dimension':'product_id','upper':'1502','upperStrict':false,'ordering':'numeric'}]}," - + "'columns':['product_name','state_province','product_id'],'granularity':{'type':'all'}," + + "'columns':['product_name','state_province','product_id']," + "'resultFormat':'compactedList'"; sql(sql) .limit(4) @@ -852,8 +853,9 @@ public class DruidAdapterIT { + "where \"product_id\" = -1"; final String druidQuery = "{'queryType':'scan','dataSource':'foodmart'," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']," - + "'filter':{'type':'selector','dimension':'product_id','value':'-1'}," - + "'columns':['product_name'],'granularity':{'type':'all'}," + + "'filter':{'type':'bound','dimension':'product_id','lower':'-1','lowerStrict':false," + + "'upper':'-1','upperStrict':false,'ordering':'numeric'}," + + "'columns':['product_name']," + "'resultFormat':'compactedList'}"; sql(sql) .limit(4) @@ -868,9 +870,12 @@ public class DruidAdapterIT { + "where cast(\"product_id\" as integer) - 1500 BETWEEN 0 AND 2\n" + "order by \"state_province\" desc, \"product_id\""; final String druidQuery = "{'queryType':'scan','dataSource':'foodmart'," - + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']," - + "'columns':['product_id','product_name','state_province'],'granularity':{'type':'all'}," + + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z'],"; + final String druidFilter = "\"filter\":{\"type\":\"and\"," + + "\"fields\":[{\"type\":\"expression\",\"expression\":\"((CAST(\\\"product_id\\\""; + final String druidQuery2 = "'columns':['product_name','state_province','product_id']," + "'resultFormat':'compactedList'}"; + sql(sql) .limit(4) .returns( @@ -889,7 +894,7 @@ public class DruidAdapterIT { } } }) - .queryContains(druidChecker(druidQuery)); + .queryContains(druidChecker(druidQuery, druidFilter, druidQuery2)); } @Test public void testUnionPlan() { @@ -930,12 +935,13 @@ public class DruidAdapterIT { @Test public void testCountGroupByEmpty() { final String druidQuery = "{'queryType':'timeseries','dataSource':'foodmart'," - + "'descending':false,'granularity':{'type':'all'}," + + "'descending':false,'granularity':'all'," + "'aggregations':[{'type':'count','name':'EXPR$0'}]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']," + "'context':{'skipEmptyBuckets':false}}"; final String explain = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[]], groups=[{}], aggs=[[COUNT()]])"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[0]], groups=[{}], aggs=[[COUNT()]])"; final String sql = "select count(*) from \"foodmart\""; sql(sql) .returns("EXPR$0=86829\n") @@ -978,8 +984,7 @@ public class DruidAdapterIT { + "order by \"c\" desc limit 3"; sql(sql).returnsOrdered("c=494; month=1997-11-01 00:00:00; SALES=5.0", "c=475; month=1997-12-01 00:00:00; SALES=5.0", - "c=468; month=1997-03-01 00:00:00; SALES=5.0" - ).queryContains(druidChecker("'queryType':'scan'")); + "c=468; month=1997-03-01 00:00:00; SALES=5.0").queryContains(druidChecker("'queryType':'groupBy'")); } @Test public void testGroupByTimeAndOneColumnNotProjected() { @@ -1011,8 +1016,8 @@ public class DruidAdapterIT { + "group by \"state_province\"\n" + "order by \"state_province\""; String explain = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], groups=[{30}], " + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$30]], groups=[{0}], " + "aggs=[[COUNT()]], sort0=[0], dir0=[ASC])"; sql(sql) .limit(2) @@ -1045,7 +1050,25 @@ public class DruidAdapterIT { .limit(2) .returnsUnordered("state_province=CA; A=3; S=74748; C=16347; C0=24441", "state_province=OR; A=3; S=67659; C=21610; C0=21610") - .queryContains(druidChecker("'queryType':'scan'")); + .explainContains("PLAN=EnumerableInterpreter\n" + + " BindableProject(state_province=[$0], A=[CAST(/(CASE(=($2, 0), null, $1), $2)):BIGINT]," + + " S=[CASE(=($2, 0), null, $1)], C=[$3], C0=[$4])\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$30, $89, $71]], groups=[{0}], " + + "aggs=[[$SUM0($1), COUNT($1), COUNT($2), COUNT()]], sort0=[0], dir0=[ASC])") + .queryContains( + druidChecker("{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'" + + ",'dimensions':[{'type':'default','dimension':'state_province','outputName':'state_province'" + + ",'outputType':'STRING'}],'limitSpec':" + + "{'type':'default','columns':[{'dimension':'state_province'," + + "'direction':'ascending','dimensionOrder':'lexicographic'}]},'aggregations':" + + "[{'type':'longSum','name':'$f1','fieldName':'unit_sales'},{'type':'filtered'," + + "'filter':{'type':'not','field':{'type':'selector','dimension':'unit_sales'," + + "'value':null}},'aggregator':{'type':'count','name':'$f2','fieldName':'unit_sales'}}" + + ",{'type':'filtered','filter':{'type':'not','field':{'type':'selector'," + + "'dimension':'store_sqft','value':null}},'aggregator':{'type':'count','name':'C'," + + "'fieldName':'store_sqft'}},{'type':'count','name':'C0'}]," + + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}")); } @Test public void testGroupByMonthGranularity() { @@ -1053,15 +1076,14 @@ public class DruidAdapterIT { + " count(\"store_sqft\") as c\n" + "from \"foodmart\"\n" + "group by floor(\"timestamp\" to MONTH) order by s"; - String druidQuery = "{'queryType':'timeseries','dataSource':'foodmart'"; + String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'"; sql(sql) .limit(3) .explainContains("PLAN=EnumerableInterpreter\n" - + " BindableSort(sort0=[$0], dir0=[ASC])\n" - + " BindableProject(S=[$1], C=[$2])\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[FLOOR" - + "($0, FLAG(MONTH)), $89, $71]], groups=[{0}], aggs=[[SUM($1), COUNT($2)]])") + + " BindableProject(S=[$1], C=[$2])\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[FLOOR($0, FLAG(MONTH)), $89, $71]], " + + "groups=[{0}], aggs=[[SUM($1), COUNT($2)]], sort0=[1], dir0=[ASC])") .returnsOrdered("S=19958; C=5606", "S=20179; C=5523", "S=20388; C=5591") .queryContains(druidChecker(druidQuery)); } @@ -1105,12 +1127,10 @@ public class DruidAdapterIT { + "group by floor(\"timestamp\" to MONTH)\n" + "order by floor(\"timestamp\" to MONTH) limit 3"; final String explain = "PLAN=EnumerableInterpreter\n" - + " BindableProject(M=[CAST($0):TIMESTAMP(0) NOT NULL], S=[$1], C=[$2], EXPR$3=[$0])\n" - + " BindableSort(sort0=[$0], dir0=[ASC], fetch=[3])\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[FLOOR($0, " - + "FLAG(MONTH)), $89, $71]], groups=[{0}], aggs=[[SUM($1), COUNT($2)]], sort0=[0], " - + "dir0=[ASC])"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[FLOOR($0, FLAG(MONTH)), $89, $71]], groups=[{0}], " + + "aggs=[[SUM($1), COUNT($2)]], post_projects=[[CAST($0):TIMESTAMP(0) NOT NULL, $1, $2, $0]]" + + ", sort0=[3], dir0=[ASC], fetch=[3])"; sql(sql) .returnsOrdered("M=1997-01-01 00:00:00; S=21628; C=5957", "M=1997-02-01 00:00:00; S=20957; C=5842", @@ -1123,7 +1143,7 @@ public class DruidAdapterIT { + " count(\"store_sqft\") as c\n" + "from \"foodmart\"\n" + "group by floor(\"timestamp\" to DAY) order by c desc"; - String druidQuery = "{'queryType':'timeseries','dataSource':'foodmart'"; + String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'"; sql(sql) .limit(3) .queryContains(druidChecker(druidQuery)) @@ -1137,7 +1157,7 @@ public class DruidAdapterIT { + "where \"timestamp\" >= '1996-01-01 00:00:00 UTC' and " + " \"timestamp\" < '1998-01-01 00:00:00 UTC'\n" + "group by floor(\"timestamp\" to MONTH) order by s asc"; - String druidQuery = "{'queryType':'timeseries','dataSource':'foodmart'"; + String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'"; sql(sql) .limit(3) @@ -1162,8 +1182,9 @@ public class DruidAdapterIT { + "($0, FLAG(MONTH)), $89]], groups=[{0, 1}], aggs=[[SUM($2), MAX($2)]], sort0=[2], " + "dir0=[DESC], fetch=[3])"; final String druidQueryPart1 = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'state_province'},{'type':'extraction','dimension':'__time'," + + "'granularity':'all','dimensions':[{'type':'default'," + + "'dimension':'state_province',\"outputName\":\"state_province\",\"outputType\":\"STRING\"}," + + "{'type':'extraction','dimension':'__time'," + "'outputName':'floor_month','extractionFn':{'type':'timeFormat','format'"; final String druidQueryPart2 = "'limitSpec':{'type':'default','limit':3," + "'columns':[{'dimension':'S','direction':'descending'," @@ -1195,7 +1216,7 @@ public class DruidAdapterIT { + "($0, FLAG(DAY)), $89]], groups=[{0, 1}], aggs=[[SUM($2), MAX($2)]], sort0=[2], " + "dir0=[DESC], fetch=[6])"; final String druidQueryType = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions'"; + + "'granularity':'all','dimensions'"; final String limitSpec = "'limitSpec':{'type':'default','limit':6," + "'columns':[{'dimension':'S','direction':'descending','dimensionOrder':'numeric'}]}"; sql(sql) @@ -1210,15 +1231,13 @@ public class DruidAdapterIT { } @Test public void testGroupByHaving() { - // Note: We don't push down HAVING yet final String sql = "select \"state_province\" as s, count(*) as c\n" + "from \"foodmart\"\n" + "group by \"state_province\" having count(*) > 23000 order by 1"; - final String explain = "PLAN=" - + "EnumerableInterpreter\n" - + " BindableSort(sort0=[$0], dir0=[ASC])\n" - + " BindableFilter(condition=[>($1, 23000)])\n" - + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], groups=[{30}], aggs=[[COUNT()]])"; + final String explain = "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$30]], groups=[{0}], aggs=[[COUNT()]], " + + "filter=[>($1, 23000)], sort0=[0], dir0=[ASC])"; sql(sql) .returnsOrdered("S=CA; C=24441", "S=WA; C=40778") @@ -1231,10 +1250,8 @@ public class DruidAdapterIT { + "from \"foodmart\"\n" + "group by \"state_province\", \"city\"\n" + "order by c desc limit 2"; - final String explain = "PLAN=" - + "EnumerableInterpreter\n" - + " BindableProject(C=[$2], state_province=[$1], city=[$0])\n" - + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], groups=[{29, 30}], aggs=[[COUNT()]], sort0=[2], dir0=[DESC], fetch=[2])"; + final String explain = "BindableProject(C=[$2], state_province=[$0], city=[$1])\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[$30, $29]], groups=[{0, 1}], aggs=[[COUNT()]], sort0=[2], dir0=[DESC], fetch=[2])"; sql(sql) .returnsOrdered("C=7394; state_province=WA; city=Spokane", "C=3958; state_province=WA; city=Olympia") @@ -1258,8 +1275,9 @@ public class DruidAdapterIT { + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], groups=[{29, 30}], " + "aggs=[[]])"; final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default','dimension':'city'}," - + "{'type':'default','dimension':'state_province'}]," + + "'granularity':'all','dimensions':[{'type':'default','dimension':'city','outputName':'city'" + + ",'outputType':'STRING'}," + + "{'type':'default','dimension':'state_province','outputName':'state_province','outputType':'STRING'}]," + "'limitSpec':{'type':'default'},'aggregations':[]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; sql(sql) @@ -1276,16 +1294,15 @@ public class DruidAdapterIT { final String sql = "select \"product_name\", 0 as zero\n" + "from \"foodmart\"\n" + "order by \"product_name\""; - final String explain = "PLAN=" - + "EnumerableInterpreter\n" - + " BindableProject(product_name=[$0], ZERO=[0])\n" - + " BindableSort(sort0=[$0], dir0=[ASC])\n" - + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[$3]])"; + final String explain = "PLAN=EnumerableInterpreter\n" + + " BindableSort(sort0=[$0], dir0=[ASC])\n" + + " DruidQuery(table=[[foodmart, foodmart]], " + + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[$3, 0]])"; sql(sql) .limit(2) - .explainContains(explain) .returnsUnordered("product_name=ADJ Rosy Sunglasses; ZERO=0", - "product_name=ADJ Rosy Sunglasses; ZERO=0"); + "product_name=ADJ Rosy Sunglasses; ZERO=0") + .explainContains(explain); } @Test public void testFilterDistinct() { @@ -1295,26 +1312,23 @@ public class DruidAdapterIT { + "where \"product_name\" = 'High Top Dried Mushrooms'\n" + "and \"quarter\" in ('Q2', 'Q3')\n" + "and \"state_province\" = 'WA'"; - final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'}," - + "'dimensions':[{'type':'default','dimension':'state_province'}," - + "{'type':'default','dimension':'city'}," - + "{'type':'default','dimension':'product_name'}],'limitSpec':{'type':'default'}," - + "'filter':{'type':'and','fields':[{'type':'selector','dimension':'product_name'," - + "'value':'High Top Dried Mushrooms'},{'type':'or','fields':[{'type':'selector'," + final String druidQuery1 = "{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'"; + final String druidQuery2 = "'filter':{'type':'and','fields':[{'type':'selector','dimension':" + + "'product_name','value':'High Top Dried Mushrooms'},{'type':'or','fields':[{'type':'selector'," + "'dimension':'quarter','value':'Q2'},{'type':'selector','dimension':'quarter'," + "'value':'Q3'}]},{'type':'selector','dimension':'state_province','value':'WA'}]}," + "'aggregations':[]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; final String explain = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]]," + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]]," + " filter=[AND(=($3, 'High Top Dried Mushrooms')," + " OR(=($87, 'Q2')," + " =($87, 'Q3'))," + " =($30, 'WA'))]," + " projects=[[$30, $29, $3]], groups=[{0, 1, 2}], aggs=[[]])\n"; sql(sql) - .queryContains(druidChecker(druidQuery)) + .queryContains(druidChecker(druidQuery1, druidQuery2)) .explainContains(explain) .returnsUnordered( "state_province=WA; city=Bremerton; product_name=High Top Dried Mushrooms", @@ -1346,7 +1360,6 @@ public class DruidAdapterIT { + "{'type':'selector','dimension':'quarter','value':'Q3'}]}," + "{'type':'selector','dimension':'state_province','value':'WA'}]}," + "'columns':['state_province','city','product_name']," - + "'granularity':{'type':'all'}," + "'resultFormat':'compactedList'}"; final String explain = "PLAN=EnumerableInterpreter\n" + " DruidQuery(table=[[foodmart, foodmart]], " @@ -1386,13 +1399,13 @@ public class DruidAdapterIT { + "from \"foodmart\"\n" + "where extract(year from \"timestamp\") = 1997\n" + "and extract(month from \"timestamp\") in (4, 6)\n"; - final String explain = "DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1997-04-01T00:00:00.000Z/1997-05-01T00:00:00.000Z," - + " 1997-06-01T00:00:00.000Z/1997-07-01T00:00:00.000Z]], projects=[[]]," - + " groups=[{}], aggs=[[COUNT()]])"; + final String explain = "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1997-04-01T00:00:00.000Z/" + + "1997-05-01T00:00:00.000Z, 1997-06-01T00:00:00.000Z/1997-07-01T00:00:00.000Z]]," + + " projects=[[0]], groups=[{}], aggs=[[COUNT()]])"; sql(sql) - .explainContains(explain) - .returnsUnordered("C=13500"); + .returnsUnordered("C=13500") + .explainContains(explain); } @Test public void testFilterSwapped() { @@ -1425,40 +1438,6 @@ public class DruidAdapterIT { .returnsCount(9); } - - /** Test case for - * <a href="https://issues.apache.org/jira/browse/CALCITE-1656";>[CALCITE-1656] - * Improve cost function in DruidQuery to encourage early column - * pruning</a>. */ - @Test public void testFieldBasedCostColumnPruning() { - // A query where filter cannot be pushed to Druid but - // the project can still be pushed in order to prune extra columns. - String sql = "select \"countryName\", ceil(CAST(\"time\" AS TIMESTAMP) to DAY),\n" - + " cast(count(*) as integer) as c\n" - + "from \"wiki\"\n" - + "where ceil(\"time\" to DAY) >= '1997-01-01 00:00:00 UTC'\n" - + "and ceil(\"time\" to DAY) < '1997-09-01 00:00:00 UTC'\n" - + "and \"time\" + INTERVAL '1' DAY > '1997-01-01'\n" - + "group by \"countryName\", ceil(CAST(\"time\" AS TIMESTAMP) TO DAY)\n" - + "order by c limit 5"; - String plan = "BindableProject(countryName=[$0], EXPR$1=[$1], C=[CAST($2):INTEGER NOT NULL])\n" - + " BindableSort(sort0=[$2], dir0=[ASC], fetch=[5])\n" - + " BindableAggregate(group=[{0, 1}], agg#0=[COUNT()])\n" - + " BindableProject(countryName=[$1], EXPR$1=[CEIL(CAST($0):TIMESTAMP(0) NOT NULL, FLAG(DAY))])\n" - + " BindableFilter(condition=[>(+($0, 86400000), CAST('1997-01-01'):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL)])\n" - + " DruidQuery(table=[[wiki, wiki]], intervals=[[1996-12-31T00:00:00.001Z/1997-08-31T00:00:00.001Z]], projects=[[$0, $5]])"; - // NOTE: Druid query only has countryName as the dimension - // being queried after project is pushed to druid query. - String druidQuery = "{'queryType':'scan'," - + "'dataSource':'wikiticker'," - + "'intervals':['1996-12-31T00:00:00.001Z/1997-08-31T00:00:00.001Z']," - + "'columns':['__time','countryName']," - + "'granularity':{'type':'all'}," - + "'resultFormat':'compactedList'"; - sql(sql, WIKI).explainContains(plan); - sql(sql, WIKI).queryContains(druidChecker(druidQuery)); - } - @Test public void testGroupByMetricAndExtractTime() { final String sql = "SELECT count(*), cast(floor(\"timestamp\" to DAY) as timestamp), \"store_sales\" " @@ -1484,12 +1463,12 @@ public class DruidAdapterIT { + "and \"timestamp\" > '1990-01-01 00:00:00 UTC' " + "group by \"timestamp\", \"product_id\" "; String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'extraction'," + + "'granularity':'all','dimensions':[{'type':'extraction'," + "'dimension':'__time','outputName':'extract'," + "'extractionFn':{'type':'timeFormat','format':'yyyy-MM-dd"; sql(sql) - .queryContains(druidChecker(druidQuery)) - .returnsUnordered("product_id=1016; time=1997-01-02 00:00:00"); + .returnsUnordered("product_id=1016; time=1997-01-02 00:00:00") + .queryContains(druidChecker(druidQuery)); } @Test public void testPushAggregateOnTimeWithExtractYear() { @@ -1501,11 +1480,11 @@ public class DruidAdapterIT { sql(sql) .queryContains( druidChecker( - ",'granularity':{'type':'all'}", + ",'granularity':'all'", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_year'," + "'extractionFn':{'type':'timeFormat','format':'yyyy'," - + "'timeZone':'UTC','locale':'und'}}")) + + "'timeZone':'UTC','locale':'en-US'}}")) .returnsUnordered("year=1997; product_id=1016"); } @@ -1518,11 +1497,11 @@ public class DruidAdapterIT { sql(sql) .queryContains( druidChecker( - ",'granularity':{'type':'all'}", + ",'granularity':'all'", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_month'," + "'extractionFn':{'type':'timeFormat','format':'M'," - + "'timeZone':'UTC','locale':'und'}}")) + + "'timeZone':'UTC','locale':'en-US'}}")) .returnsUnordered("month=1; product_id=1016", "month=2; product_id=1016", "month=3; product_id=1016", "month=4; product_id=1016", "month=5; product_id=1016"); } @@ -1537,20 +1516,17 @@ public class DruidAdapterIT { sql(sql) .queryContains( druidChecker( - ",'granularity':{'type':'all'}", + ",'granularity':'all'", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_day'," + "'extractionFn':{'type':'timeFormat','format':'d'," - + "'timeZone':'UTC','locale':'und'}}")) + + "'timeZone':'UTC','locale':'en-US'}}")) .returnsUnordered("day=2; product_id=1016", "day=10; product_id=1016", "day=13; product_id=1016", "day=16; product_id=1016"); } - // Calcite rewrite the extract function in the query as: - // rel#85:BindableProject.BINDABLE.[](input=rel#69:Subset#1.BINDABLE.[], - // hourOfDay=/INT(MOD(Reinterpret($0), 86400000), 3600000),product_id=$1). - // Currently 'EXTRACT( hour from \"timestamp\")' is not pushed to Druid. - @Ignore @Test public void testPushAggregateOnTimeWithExtractHourOfDay() { + @Test + public void testPushAggregateOnTimeWithExtractHourOfDay() { String sql = "select EXTRACT( hour from \"timestamp\") as \"hourOfDay\",\"product_id\" from " + "\"foodmart\" where \"product_id\" = 1016 and " @@ -1558,15 +1534,8 @@ public class DruidAdapterIT { + "('1997-01-01' as timestamp)" + " group by " + " EXTRACT( hour from \"timestamp\"), \"product_id\" "; sql(sql) - .queryContains( - druidChecker( - ",'granularity':{'type':'all'}", - "{'type':'extraction'," - + "'dimension':'__time','outputName':'extract_0'," - + "'extractionFn':{'type':'timeFormat','format':'H'," - + "'timeZone':'UTC'}}")) - .returnsUnordered("month=01; product_id=1016", "month=02; product_id=1016", - "month=03; product_id=1016", "month=04; product_id=1016", "month=05; product_id=1016"); + .queryContains(druidChecker("'queryType':'groupBy'")) + .returnsUnordered("hourOfDay=0; product_id=1016"); } @Test public void testPushAggregateOnTimeWithExtractYearMonthDay() { @@ -1581,17 +1550,17 @@ public class DruidAdapterIT { sql(sql) .queryContains( druidChecker( - ",'granularity':{'type':'all'}", + ",'granularity':'all'", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_day'," + "'extractionFn':{'type':'timeFormat','format':'d'," - + "'timeZone':'UTC','locale':'und'}}", "{'type':'extraction'," + + "'timeZone':'UTC','locale':'en-US'}}", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_month'," + "'extractionFn':{'type':'timeFormat','format':'M'," - + "'timeZone':'UTC','locale':'und'}}", "{'type':'extraction'," + + "'timeZone':'UTC','locale':'en-US'}}", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_year'," + "'extractionFn':{'type':'timeFormat','format':'yyyy'," - + "'timeZone':'UTC','locale':'und'}}")) + + "'timeZone':'UTC','locale':'en-US'}}")) .explainContains("PLAN=EnumerableInterpreter\n" + " DruidQuery(table=[[foodmart, foodmart]], " + "intervals=[[1997-01-01T00:00:00.001Z/1997-01-20T00:00:00.000Z]], " @@ -1615,16 +1584,16 @@ public class DruidAdapterIT { sql(sql) .queryContains( druidChecker( - ",'granularity':{'type':'all'}", "{'type':'extraction'," + ",'granularity':'all'", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_day'," + "'extractionFn':{'type':'timeFormat','format':'d'," - + "'timeZone':'UTC','locale':'und'}}", "{'type':'extraction'," + + "'timeZone':'UTC','locale':'en-US'}}", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_month'," + "'extractionFn':{'type':'timeFormat','format':'M'," - + "'timeZone':'UTC','locale':'und'}}", "{'type':'extraction'," + + "'timeZone':'UTC','locale':'en-US'}}", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_year'," + "'extractionFn':{'type':'timeFormat','format':'yyyy'," - + "'timeZone':'UTC','locale':'und'}}")) + + "'timeZone':'UTC','locale':'en-US'}}")) .explainContains("PLAN=EnumerableInterpreter\n" + " DruidQuery(table=[[foodmart, foodmart]], " + "intervals=[[1997-01-01T00:00:00.001Z/1997-01-20T00:00:00.000Z]], " @@ -1647,10 +1616,10 @@ public class DruidAdapterIT { sql(sql) .queryContains( druidChecker( - ",'granularity':{'type':'all'}", "{'type':'extraction'," + ",'granularity':'all'", "{'type':'extraction'," + "'dimension':'__time','outputName':'extract_day'," + "'extractionFn':{'type':'timeFormat','format':'d'," - + "'timeZone':'UTC','locale':'und'}}")) + + "'timeZone':'UTC','locale':'en-US'}}")) .explainContains("PLAN=EnumerableInterpreter\n" + " DruidQuery(table=[[foodmart, foodmart]], " + "intervals=[[1997-01-01T00:00:00.001Z/1997-01-20T00:00:00.000Z]], " @@ -1665,17 +1634,14 @@ public class DruidAdapterIT { + "where EXTRACT( year from \"timestamp\") = 1997 and " + "\"cases_per_pallet\" >= 8 and \"cases_per_pallet\" <= 10 and " + "\"units_per_case\" < 15 "; - String druidQuery = "{'queryType':'timeseries','dataSource':'foodmart'," - + "'descending':false,'granularity':{'type':'all'},'filter':{'type':'and'," - + "'fields':[{'type':'bound','dimension':'cases_per_pallet','lower':'8'," - + "'lowerStrict':false,'ordering':'numeric'},{'type':'bound'," - + "'dimension':'cases_per_pallet','upper':'10','upperStrict':false," - + "'ordering':'numeric'},{'type':'bound','dimension':'units_per_case'," - + "'upper':'15','upperStrict':true,'ordering':'numeric'}]}," - + "'aggregations':[{'type':'doubleSum'," - + "'name':'EXPR$0','fieldName':'store_sales'}]," - + "'intervals':['1997-01-01T00:00:00.000Z/1998-01-01T00:00:00.000Z']," - + "'context':{'skipEmptyBuckets':true}}"; + String druidQuery = "{'queryType':'timeseries','dataSource':'foodmart','descending':false," + + "'granularity':'all','filter':{'type':'and','fields':[{'type':'bound','dimension':" + + "'cases_per_pallet','lower':'8','lowerStrict':false,'ordering':'numeric'}," + + "{'type':'bound','dimension':'cases_per_pallet','upper':'10','upperStrict':false," + + "'ordering':'numeric'},{'type':'bound','dimension':'units_per_case','upper':'15'," + + "'upperStrict':true,'ordering':'numeric'}]},'aggregations':[{'type':'doubleSum'," + + "'name':'EXPR$0','fieldName':'store_sales'}],'intervals':['1997-01-01T00:00:00.000Z/" + + "1998-01-01T00:00:00.000Z'],'context':{'skipEmptyBuckets':true}}"; sql(sql) .explainContains("PLAN=EnumerableInterpreter\n" + " DruidQuery(table=[[foodmart, foodmart]], " @@ -1683,8 +1649,8 @@ public class DruidAdapterIT { + "filter=[AND(>=(CAST($11):BIGINT, 8), <=(CAST($11):BIGINT, 10), " + "<(CAST($10):BIGINT, 15))], groups=[{}], " + "aggs=[[SUM($90)]])\n") - .queryContains(druidChecker(druidQuery)) - .returnsUnordered("EXPR$0=75364.1"); + .returnsUnordered("EXPR$0=75364.1") + .queryContains(druidChecker(druidQuery)); } @Test public void testPushOfFilterExtractionOnDayAndMonth() { @@ -1694,24 +1660,6 @@ public class DruidAdapterIT { + "AND \"product_id\" >= 1549 group by \"product_id\", EXTRACT(day from " + "\"timestamp\"), EXTRACT(month from \"timestamp\")"; sql(sql) - .queryContains( - druidChecker("{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'product_id'},{'type':'extraction','dimension':'__time'," - + "'outputName':'extract_day','extractionFn':{'type':'timeFormat'," - + "'format':'d','timeZone':'UTC','locale':'und'}},{'type':'extraction'," - + "'dimension':'__time','outputName':'extract_month'," - + "'extractionFn':{'type':'timeFormat','format':'M','timeZone':'UTC'," - + "'locale':'und'}}],'limitSpec':{'type':'default'}," - + "'filter':{'type':'and','fields':[{'type':'bound'," - + "'dimension':'product_id','lower':'1549','lowerStrict':false," - + "'ordering':'numeric'},{'type':'bound','dimension':'__time'," - + "'lower':'30','lowerStrict':false,'ordering':'numeric'," - + "'extractionFn':{'type':'timeFormat','format':'d','timeZone':'UTC'," - + "'locale':'und'}},{'type':'selector','dimension':'__time'," - + "'value':'11','extractionFn':{'type':'timeFormat','format':'M'," - + "'timeZone':'UTC','locale':'und'}}]},'aggregations':[]," - + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}")) .returnsUnordered("product_id=1549; EXPR$1=30; EXPR$2=11", "product_id=1553; EXPR$1=30; EXPR$2=11"); } @@ -1724,45 +1672,17 @@ public class DruidAdapterIT { + "group by \"product_id\", EXTRACT(day from \"timestamp\"), " + "EXTRACT(month from \"timestamp\"), EXTRACT(year from \"timestamp\")"; sql(sql) - .queryContains( - druidChecker("{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'product_id'},{'type':'extraction','dimension':'__time'," - + "'outputName':'extract_day','extractionFn':{'type':'timeFormat'," - + "'format':'d','timeZone':'UTC','locale':'und'}},{'type':'extraction'," - + "'dimension':'__time','outputName':'extract_month'," - + "'extractionFn':{'type':'timeFormat','format':'M','timeZone':'UTC'," - + "'locale':'und'}},{'type':'extraction','dimension':'__time'," - + "'outputName':'extract_year','extractionFn':{'type':'timeFormat'," - + "'format':'yyyy','timeZone':'UTC','locale':'und'}}]," - + "'limitSpec':{'type':'default'},'filter':{" - + "'type':'bound','dimension':'product_id','lower':'1549'," - + "'lowerStrict':false,'ordering':'numeric'}," - + "'aggregations':[]," - + "'intervals':['1997-11-30T00:00:00.000Z/1997-12-01T00:00:00.000Z']}")) .returnsUnordered("product_id=1549; EXPR$1=30; EXPR$2=11; EXPR$3=1997", - "product_id=1553; EXPR$1=30; EXPR$2=11; EXPR$3=1997"); + "product_id=1553; EXPR$1=30; EXPR$2=11; EXPR$3=1997") + .queryContains( + druidChecker("{'queryType':'groupBy','dataSource':'foodmart','granularity':'all'")); } @Test public void testFilterExtractionOnMonthWithBetween() { String sqlQuery = "SELECT \"product_id\", EXTRACT(month from \"timestamp\") FROM \"foodmart\"" + " WHERE EXTRACT(month from \"timestamp\") BETWEEN 10 AND 11 AND \"product_id\" >= 1558" + " GROUP BY \"product_id\", EXTRACT(month from \"timestamp\")"; - String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'default'," - + "'dimension':'product_id'},{'type':'extraction','dimension':'__time'," - + "'outputName':'extract_month','extractionFn':{'type':'timeFormat'," - + "'format':'M','timeZone':'UTC','locale':'und'}}]," - + "'limitSpec':{'type':'default'},'filter':{'type':'and'," - + "'fields':[{'type':'bound','dimension':'product_id','lower':'1558'," - + "'lowerStrict':false,'ordering':'numeric'},{'type':'bound'," - + "'dimension':'__time','lower':'10','lowerStrict':false," - + "'ordering':'numeric','extractionFn':{'type':'timeFormat','format':'M'," - + "'timeZone':'UTC','locale':'und'}},{'type':'bound'," - + "'dimension':'__time','upper':'11','upperStrict':false," - + "'ordering':'numeric','extractionFn':{'type':'timeFormat','format':'M'," - + "'timeZone':'UTC','locale':'und'}}]},'aggregations':[]," - + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; + String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'"; sql(sqlQuery) .returnsUnordered("product_id=1558; EXPR$1=10", "product_id=1558; EXPR$1=11", "product_id=1559; EXPR$1=11") @@ -1774,27 +1694,29 @@ public class DruidAdapterIT { + " WHERE EXTRACT(month from \"timestamp\") IN (10, 11) AND \"product_id\" >= 1558" + " GROUP BY \"product_id\", EXTRACT(month from \"timestamp\")"; sql(sqlQuery) + .returnsUnordered("product_id=1558; EXPR$1=10", "product_id=1558; EXPR$1=11", + "product_id=1559; EXPR$1=11") .queryContains( druidChecker("{'queryType':'groupBy'," - + "'dataSource':'foodmart','granularity':{'type':'all'}," - + "'dimensions':[{'type':'default','dimension':'product_id'}," + + "'dataSource':'foodmart','granularity':'all'," + + "'dimensions':[{'type':'default','dimension':'product_id','outputName':'product_id','outputType':'STRING'}," + "{'type':'extraction','dimension':'__time','outputName':'extract_month'," + "'extractionFn':{'type':'timeFormat','format':'M','timeZone':'UTC'," - + "'locale':'und'}}],'limitSpec':{'type':'default'}," + + "'locale':'en-US'}}],'limitSpec':{'type':'default'}," + "'filter':{'type':'and','fields':[{'type':'bound'," + "'dimension':'product_id','lower':'1558','lowerStrict':false," - + "'ordering':'numeric'},{'type':'or','fields':[{'type':'selector'," - + "'dimension':'__time','value':'10','extractionFn':{'type':'timeFormat'," - + "'format':'M','timeZone':'UTC','locale':'und'}},{'type':'selector'," - + "'dimension':'__time','value':'11','extractionFn':{'type':'timeFormat'," - + "'format':'M','timeZone':'UTC','locale':'und'}}]}]}," + + "'ordering':'numeric'},{'type':'or','fields':[{'type':'bound','dimension':'__time'" + + ",'lower':'10','lowerStrict':false,'upper':'10','upperStrict':false," + + "'ordering':'numeric','extractionFn':{'type':'timeFormat'," + + "'format':'M','timeZone':'UTC','locale':'en-US'}},{'type':'bound'," + + "'dimension':'__time','lower':'11','lowerStrict':false,'upper':'11'," + + "'upperStrict':false,'ordering':'numeric','extractionFn':{'type':'timeFormat'," + + "'format':'M','timeZone':'UTC','locale':'en-US'}}]}]}," + "'aggregations':[]," - + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}")) - .returnsUnordered("product_id=1558; EXPR$1=10", "product_id=1558; EXPR$1=11", - "product_id=1559; EXPR$1=11"); + + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}")); } - @Test public void testPushofOrderByWithMonthExtract() { + @Test public void testPushOfOrderByWithMonthExtract() { String sqlQuery = "SELECT extract(month from \"timestamp\") as m , \"product_id\", SUM" + "(\"unit_sales\") as s FROM \"foodmart\"" + " WHERE \"product_id\" >= 1558" @@ -1802,15 +1724,16 @@ public class DruidAdapterIT { + "\"product_id\""; sql(sqlQuery).queryContains( druidChecker("{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'extraction'," + + "'granularity':'all','dimensions':[{'type':'extraction'," + "'dimension':'__time','outputName':'extract_month'," + "'extractionFn':{'type':'timeFormat','format':'M','timeZone':'UTC'," - + "'locale':'und'}},{'type':'default','dimension':'product_id'}]," + + "'locale':'en-US'}},{'type':'default','dimension':'product_id','outputName':" + + "'product_id','outputType':'STRING'}]," + "'limitSpec':{'type':'default','columns':[{'dimension':'extract_month'," + "'direction':'ascending','dimensionOrder':'numeric'},{'dimension':'S'," + "'direction':'ascending','dimensionOrder':'numeric'}," + "{'dimension':'product_id','direction':'ascending'," - + "'dimensionOrder':'alphanumeric'}]},'filter':{'type':'bound'," + + "'dimensionOrder':'lexicographic'}]},'filter':{'type':'bound'," + "'dimension':'product_id','lower':'1558','lowerStrict':false," + "'ordering':'numeric'},'aggregations':[{'type':'longSum','name':'S'," + "'fieldName':'unit_sales'}]," @@ -1830,28 +1753,30 @@ public class DruidAdapterIT { + "group by floor(\"timestamp\" to MONTH)\n" + "order by \"month\" DESC"; sql(sql) - .explainContains("DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[FLOOR($0, " - + "FLAG(MONTH))]], groups=[{0}], aggs=[[]], sort0=[0], dir0=[DESC])") - .queryContains(druidChecker("'queryType':'timeseries'", "'descending':true")); + .queryContains(druidChecker("'queryType':'timeseries'", "'descending':true")) + .explainContains("PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z" + + "/2992-01-10T00:00:00.000Z]], projects=[[FLOOR($0, FLAG(MONTH))]], groups=[{0}], " + + "aggs=[[]], post_projects=[[CAST($0):TIMESTAMP(0) NOT NULL]], sort0=[0], dir0=[DESC])"); + } @Test public void testGroupByFloorTimeWithLimit() { final String sql = - "select cast(floor(\"timestamp\" to MONTH) as timestamp) as \"floor_month\"\n" + "select cast(floor(\"timestamp\" to MONTH) as timestamp) as \"floorOfMonth\"\n" + "from \"foodmart\"\n" + "group by floor(\"timestamp\" to MONTH)\n" - + "order by \"floor_month\" DESC LIMIT 3"; + + "order by \"floorOfMonth\" DESC LIMIT 3"; final String explain = - " BindableSort(sort0=[$0], dir0=[DESC], fetch=[3])\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " - + "projects=[[FLOOR($0, FLAG(MONTH))]], groups=[{0}], aggs=[[]], " - + "sort0=[0], dir0=[DESC])"; - sql(sql).explainContains(explain) - .queryContains(druidChecker("'queryType':'timeseries'", "'descending':true")) - .returnsOrdered("floor_month=1997-12-01 00:00:00", "floor_month=1997-11-01 00:00:00", - "floor_month=1997-10-01 00:00:00"); + "PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[FLOOR($0, FLAG(MONTH))]], groups=[{0}], " + + "aggs=[[]], post_projects=[[CAST($0):TIMESTAMP(0) NOT NULL]], sort0=[0], dir0=[DESC], fetch=[3])"; + sql(sql) + .explainContains(explain) + .returnsOrdered("floorOfMonth=1997-12-01 00:00:00", "floorOfMonth=1997-11-01 00:00:00", + "floorOfMonth=1997-10-01 00:00:00") + .queryContains(druidChecker("'queryType':'groupBy'", "'direction':'descending'")); } @Test public void testPushofOrderByYearWithYearMonthExtract() { @@ -1869,19 +1794,20 @@ public class DruidAdapterIT { + "sort1=[1], sort2=[3], sort3=[2], dir0=[DESC], " + "dir1=[ASC], dir2=[DESC], dir3=[ASC], fetch=[3])"; final String expectedDruidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'extraction'," + + "'granularity':'all','dimensions':[{'type':'extraction'," + "'dimension':'__time','outputName':'extract_year'," + "'extractionFn':{'type':'timeFormat','format':'yyyy','timeZone':'UTC'," - + "'locale':'und'}},{'type':'extraction','dimension':'__time'," + + "'locale':'en-US'}},{'type':'extraction','dimension':'__time'," + "'outputName':'extract_month','extractionFn':{'type':'timeFormat'," - + "'format':'M','timeZone':'UTC','locale':'und'}},{'type':'default'," - + "'dimension':'product_id'}],'limitSpec':{'type':'default','limit':3," + + "'format':'M','timeZone':'UTC','locale':'en-US'}},{'type':'default'," + + "'dimension':'product_id','outputName':'product_id','outputType':'STRING'}]," + + "'limitSpec':{'type':'default','limit':3," + "'columns':[{'dimension':'extract_year','direction':'descending'," + "'dimensionOrder':'numeric'},{'dimension':'extract_month'," + "'direction':'ascending','dimensionOrder':'numeric'},{'dimension':'S'," + "'direction':'descending','dimensionOrder':'numeric'}," + "{'dimension':'product_id','direction':'ascending'," - + "'dimensionOrder':'alphanumeric'}]},'filter':{'type':'bound'," + + "'dimensionOrder':'lexicographic'}]},'filter':{'type':'bound'," + "'dimension':'product_id','lower':'1558','lowerStrict':false," + "'ordering':'numeric'},'aggregations':[{'type':'longSum','name':'S'," + "'fieldName':'unit_sales'}]," @@ -1903,26 +1829,12 @@ public class DruidAdapterIT { + "filter=[>=(CAST($1):BIGINT, 1558)], projects=[[EXTRACT(FLAG(YEAR), $0), " + "EXTRACT(FLAG(MONTH), $0), $1, $89]], groups=[{0, 1, 2}], aggs=[[SUM($3)]], " + "sort0=[3], sort1=[1], sort2=[2], dir0=[DESC], dir1=[DESC], dir2=[ASC], fetch=[3])"; - final String expectedDruidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'extraction'," - + "'dimension':'__time','outputName':'extract_year'," - + "'extractionFn':{'type':'timeFormat','format':'yyyy','timeZone':'UTC'," - + "'locale':'und'}},{'type':'extraction','dimension':'__time'," - + "'outputName':'extract_month','extractionFn':{'type':'timeFormat'," - + "'format':'M','timeZone':'UTC','locale':'und'}},{'type':'default'," - + "'dimension':'product_id'}],'limitSpec':{'type':'default','limit':3," - + "'columns':[{'dimension':'S','direction':'descending'," - + "'dimensionOrder':'numeric'},{'dimension':'extract_month'," - + "'direction':'descending','dimensionOrder':'numeric'}," - + "{'dimension':'product_id','direction':'ascending'," - + "'dimensionOrder':'alphanumeric'}]},'filter':{'type':'bound'," - + "'dimension':'product_id','lower':'1558','lowerStrict':false," - + "'ordering':'numeric'},'aggregations':[{'type':'longSum','name':'S'," - + "'fieldName':'unit_sales'}]," - + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; - sql(sqlQuery).explainContains(expectedPlan).queryContains(druidChecker(expectedDruidQuery)) + final String expectedDruidQueryType = "'queryType':'groupBy'"; + sql(sqlQuery) .returnsOrdered("Y=1997; M=12; product_id=1558; S=30", "Y=1997; M=3; product_id=1558; S=29", - "Y=1997; M=5; product_id=1558; S=27"); + "Y=1997; M=5; product_id=1558; S=27") + .explainContains(expectedPlan) + .queryContains(druidChecker(expectedDruidQueryType)); } @Test public void testGroupByTimeSortOverMetrics() { @@ -1930,13 +1842,6 @@ public class DruidAdapterIT { + " cast(floor(\"timestamp\" to month) as timestamp)" + " FROM \"foodmart\" group by floor(\"timestamp\" to month) order by s DESC"; sql(sqlQuery) - .explainContains("PLAN=EnumerableInterpreter\n" - + " BindableSort(sort0=[$1], dir0=[DESC])\n" - + " BindableProject(C=[$1], S=[$2], EXPR$2=[CAST($0):TIMESTAMP(0) NOT NULL])\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[FLOOR($0, " - + "FLAG(MONTH)), $89]], groups=[{0}], aggs=[[COUNT(), SUM($1)]])") - .queryContains(druidChecker("'queryType':'timeseries'")) .returnsOrdered("C=8716; S=26796; EXPR$2=1997-12-01 00:00:00", "C=8231; S=25270; EXPR$2=1997-11-01 00:00:00", "C=7752; S=23763; EXPR$2=1997-07-01 00:00:00", @@ -1948,7 +1853,13 @@ public class DruidAdapterIT { "C=6844; S=20957; EXPR$2=1997-02-01 00:00:00", "C=6662; S=20388; EXPR$2=1997-09-01 00:00:00", "C=6588; S=20179; EXPR$2=1997-04-01 00:00:00", - "C=6478; S=19958; EXPR$2=1997-10-01 00:00:00"); + "C=6478; S=19958; EXPR$2=1997-10-01 00:00:00") + .queryContains(druidChecker("'queryType':'groupBy'")) + .explainContains("PLAN=EnumerableInterpreter\n" + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[FLOOR($0, FLAG(MONTH)), $89]], groups=[{0}], " + + "aggs=[[COUNT(), SUM($1)]], post_projects=[[$1, $2, CAST($0):TIMESTAMP(0) NOT NULL]]," + + " sort0=[1], dir0=[DESC])"); } @Test public void testNumericOrderingOfOrderByOperatorFullTime() { @@ -1957,7 +1868,7 @@ public class DruidAdapterIT { + "\"foodmart\" group by \"timestamp\" order by \"timestamp\" DESC, c DESC, s LIMIT 5"; final String druidSubQuery = "'limitSpec':{'type':'default','limit':5," + "'columns':[{'dimension':'extract','direction':'descending'," - + "'dimensionOrder':'alphanumeric'},{'dimension':'C'," + + "'dimensionOrder':'lexicographic'},{'dimension':'C'," + "'direction':'descending','dimensionOrder':'numeric'},{'dimension':'S'," + "'direction':'ascending','dimensionOrder':'numeric'}]}," + "'aggregations':[{'type':'count','name':'C'},{'type':'longSum'," @@ -1993,7 +1904,7 @@ public class DruidAdapterIT { + "\"foodmart\" group by \"brand_name\" order by \"brand_name\" DESC LIMIT 5"; final String druidSubQuery = "'limitSpec':{'type':'default','limit':5," + "'columns':[{'dimension':'brand_name','direction':'descending'," - + "'dimensionOrder':'alphanumeric'}]}"; + + "'dimensionOrder':'lexicographic'}]}"; sql(sqlQuery).returnsOrdered("brand_name=Washington; C=576; S=1775\nbrand_name=Walrus; C=457;" + " S=1399\nbrand_name=Urban; C=299; S=924\nbrand_name=Tri-State; C=2339; " + "S=7270\nbrand_name=Toucan; C=123; S=380").queryContains(druidChecker(druidSubQuery)); @@ -2002,21 +1913,24 @@ public class DruidAdapterIT { @Test public void testGroupByWeekExtract() { final String sql = "SELECT extract(week from \"timestamp\") from \"foodmart\" where " - + "\"product_id\" = 1558 and extract(week from \"timestamp\") IN (10, 11)group by extract" + + "\"product_id\" = 1558 and extract(week from \"timestamp\") IN (10, 11) group by extract" + "(week from \"timestamp\")"; final String druidQuery = "{'queryType':'groupBy','dataSource':'foodmart'," - + "'granularity':{'type':'all'},'dimensions':[{'type':'extraction'," + + "'granularity':'all','dimensions':[{'type':'extraction'," + "'dimension':'__time','outputName':'extract_week'," + "'extractionFn':{'type':'timeFormat','format':'w','timeZone':'UTC'," - + "'locale':'und'}}],'limitSpec':{'type':'default'}," - + "'filter':{'type':'and','fields':[{'type':'selector'," - + "'dimension':'product_id','value':'1558'},{'type':'or'," - + "'fields':[{'type':'selector','dimension':'__time','value':'10'," + + "'locale':'en-US'}}],'limitSpec':{'type':'default'}," + + "'filter':{'type':'and','fields':[{'type':'bound','dimension':'product_id'," + + "'lower':'1558','lowerStrict':false,'upper':'1558','upperStrict':false," + + "'ordering':'numeric'},{'type':'or'," + + "'fields':[{'type':'bound','dimension':'__time','lower':'10','lowerStrict':false," + + "'upper':'10','upperStrict':false,'ordering':'numeric'," + "'extractionFn':{'type':'timeFormat','format':'w','timeZone':'UTC'," - + "'locale':'und'}},{'type':'selector','dimension':'__time'," - + "'value':'11','extractionFn':{'type':'timeFormat','format':'w'," - + "'timeZone':'UTC','locale':'und'}}]}]}," + + "'locale':'en-US'}},{'type':'bound','dimension':'__time','lower':'11','lowerStrict':false," + + "'upper':'11','upperStrict':false,'ordering':'numeric'," + + "'extractionFn':{'type':'timeFormat','format':'w'," + + "'timeZone':'UTC','locale':'en-US'}}]}]}," + "'aggregations':[]," + "'intervals':['1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z']}"; sql(sql).returnsOrdered("EXPR$0=10\nEXPR$0=11").queryContains(druidChecker(druidQuery)); @@ -2131,94 +2045,82 @@ public class DruidAdapterIT { @Test public void testPlusArithmeticOperation() { final String sqlQuery = "select sum(\"store_sales\") + sum(\"store_cost\") as a, " + "\"store_state\" from \"foodmart\" group by \"store_state\" order by a desc"; - String postAggString = "'postAggregations':[{'type':'arithmetic','name':'postagg#0','fn':'+'," - + "'fields':[{'type':'fieldAccess','name':'','fieldName':'$f1'},{'type':'fieldAccess','" - + "name':'','fieldName':'$f2'}]}]"; + String postAggString = "type':'expression','name':'A','expression':'(\\'$f1\\' + \\'$f2\\')'}]"; final String plan = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " - + "groups=[{63}], aggs=[[SUM($90), SUM($91)]], post_projects=[[+($1, $2), $0]], " - + "sort0=[0], dir0=[DESC]"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$63, $90, $91]], groups=[{0}], " + + "aggs=[[SUM($1), SUM($2)]], post_projects=[[+($1, $2), $0]], sort0=[0], dir0=[DESC])"; sql(sqlQuery, FOODMART) - .explainContains(plan) - .queryContains(druidChecker(postAggString)) .returnsOrdered("A=369117.52790000016; store_state=WA", "A=222698.26509999996; store_state=CA", - "A=199049.57059999998; store_state=OR"); + "A=199049.57059999998; store_state=OR") + .explainContains(plan) + .queryContains(druidChecker(postAggString)); } @Test public void testDivideArithmeticOperation() { final String sqlQuery = "select \"store_state\", sum(\"store_sales\") / sum(\"store_cost\") " + "as a from \"foodmart\" group by \"store_state\" order by a desc"; - String postAggString = "'postAggregations':[{'type':'arithmetic','name':'postagg#0'," - + "'fn':'quotient','fields':[{'type':'fieldAccess','name':'','fieldName':'$f1'}," - + "{'type':'fieldAccess','name':'','fieldName':'$f2'}]}]"; + String postAggString = "[{'type':'expression','name':'A','expression':'(\\'$f1\\' / \\'$f2\\')"; final String plan = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " - + "groups=[{63}], aggs=[[SUM($90), SUM($91)]], post_projects=[[$0, /($1, $2)]], " - + "sort0=[1], dir0=[DESC]"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$63, $90, $91]], groups=[{0}], " + + "aggs=[[SUM($1), SUM($2)]], post_projects=[[$0, /($1, $2)]], sort0=[1], dir0=[DESC])"; sql(sqlQuery, FOODMART) - .explainContains(plan) - .queryContains(druidChecker(postAggString)) .returnsOrdered("store_state=OR; A=2.506091302943239", "store_state=CA; A=2.505379741272971", - "store_state=WA; A=2.5045806163801996"); + "store_state=WA; A=2.5045806163801996") + .explainContains(plan) + .queryContains(druidChecker(postAggString)); } @Test public void testMultiplyArithmeticOperation() { final String sqlQuery = "select \"store_state\", sum(\"store_sales\") * sum(\"store_cost\") " + "as a from \"foodmart\" group by \"store_state\" order by a desc"; - String postAggString = "'postAggregations':[{'type':'arithmetic','name':'postagg#0'," - + "'fn':'*','fields':[{'type':'fieldAccess','name':'','fieldName':'$f1'}," - + "{'type':'fieldAccess','name':'','fieldName':'$f2'}]}]"; + String postAggString = "{'type':'expression','name':'A','expression':'(\\'$f1\\' * \\'$f2\\')'"; final String plan = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " - + "groups=[{63}], aggs=[[SUM($90), SUM($91)]], post_projects=[[$0, *($1, $2)]], " - + "sort0=[1], dir0=[DESC]"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$63, $90, $91]], groups=[{0}], aggs=[[SUM($1)," + + " SUM($2)]], post_projects=[[$0, *($1, $2)]], sort0=[1], dir0=[DESC])"; sql(sqlQuery, FOODMART) - .explainContains(plan) - .queryContains(druidChecker(postAggString)) .returnsOrdered("store_state=WA; A=2.7783838325212463E10", "store_state=CA; A=1.0112000537448784E10", - "store_state=OR; A=8.077425041941243E9"); + "store_state=OR; A=8.077425041941243E9") + .explainContains(plan) + .queryContains(druidChecker(postAggString)); } @Test public void testMinusArithmeticOperation() { final String sqlQuery = "select \"store_state\", sum(\"store_sales\") - sum(\"store_cost\") " + "as a from \"foodmart\" group by \"store_state\" order by a desc"; - String postAggString = "'postAggregations':[{'type':'arithmetic','name':'postagg#0'," - + "'fn':'-','fields':[{'type':'fieldAccess','name':'','fieldName':'$f1'}," - + "{'type':'fieldAccess','name':'','fieldName':'$f2'}]}]"; + String postAggString = "'postAggregations':[{'type':'expression','name':'A'," + + "'expression':'(\\'$f1\\' - \\'$f2\\')'}]"; final String plan = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " - + "groups=[{63}], aggs=[[SUM($90), SUM($91)]], post_projects=[[$0, -($1, $2)]], " - + "sort0=[1], dir0=[DESC]"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$63, $90, $91]], groups=[{0}], aggs=[[SUM($1), " + + "SUM($2)]], post_projects=[[$0, -($1, $2)]], sort0=[1], dir0=[DESC])"; sql(sqlQuery, FOODMART) - .explainContains(plan) - .queryContains(druidChecker(postAggString)) .returnsOrdered("store_state=WA; A=158468.91210000002", "store_state=CA; A=95637.41489999992", - "store_state=OR; A=85504.56939999988"); + "store_state=OR; A=85504.56939999988") + .explainContains(plan) + .queryContains(druidChecker(postAggString)); } @Test public void testConstantPostAggregator() { final String sqlQuery = "select \"store_state\", sum(\"store_sales\") + 100 as a from " + "\"foodmart\" group by \"store_state\" order by a desc"; - String postAggString = "{'type':'constant','name':'','value':100.0}"; + String postAggString = "{'type':'expression','name':'A','expression':'(\\'$f1\\' + 100)'}"; final String plan = "PLAN=EnumerableInterpreter\n" - + " DruidQuery(table=[[foodmart, foodmart]], " - + "intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], " - + "groups=[{63}], aggs=[[SUM($90)]], post_projects=[[$0, +($1, 100)]], " - + "sort0=[1], dir0=[DESC]"; + + " DruidQuery(table=[[foodmart, foodmart]], intervals=[[1900-01-09T00:00:00.000Z/" + + "2992-01-10T00:00:00.000Z]], projects=[[$63, $90]], groups=[{0}], aggs=[[SUM($1)]], " + + "post_projects=[[$0, +($1, 100)]], sort0=[1], dir0=[DESC])"; sql(sqlQuery, FOODMART) - .explainContains(plan) - .queryContains(druidChecker(postAggString)) .returnsOrdered("store_state=WA; A=263893.2200000001", "store_state=CA; A=159267.83999999994", - "store_state=OR; A=142377.06999999992"); + "store_state=OR; A=142377.06999999992") + .explainContains(plan) + .queryContains(druidChecker(postAggString)); } @Test public void testRecursiveArithmeticOperation() { @@ -2226,26 +2128,19 @@ public class DruidAdapterIT { + "(sum(\"store_sales\")-sum(\"store_cost\")) / (count(*) * 3) " + "AS a,sum(\"unit_sales\") AS b, \"store_state\" from \"foodmart\" group " + "by \"store_state\") order by c desc"; - String postAggString = "'postAggregations':[{'type':'arithmetic','name':'postagg#0'," - + "'fn':'*','fields':[{'type':'constant','name':'','value':-1.0},{'type':" - + "'arithmetic','name':'','fn':'+','fields':[{'type':'arithmetic','name':" - + "'','fn':'quotient','fields':[{'type':'arithmetic','name':'','fn':'-'," - + "'fields':[{'type':'fieldAccess','name':'','fieldName':'$f1'},{'type':" - + "'fieldAccess','name':'','fieldName':'$f2'}]},{'type':'arithmetic','name':" - + "'','fn':'*','fields':[{'type':'fieldAccess','name':'','fieldName':'$f3'}," - + "{'type':'constant','name':'','value':3.0}]}]},{'type':'fieldAccess','name'" - + ":'','fieldName':'B'}]}]}]"; + String postAggString = "'postAggregat <TRUNCATED>
