drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jacques Nadeau (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DRILL-1622) Analytic tool generated SQL queries hang or fail
Date Sun, 02 Nov 2014 22:56:33 GMT

     [ https://issues.apache.org/jira/browse/DRILL-1622?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jacques Nadeau updated DRILL-1622:
----------------------------------
    Description: 
Queries generated by Analytic tools can include unnecessary tables and joins. When tracing ODBC calls and executing queries in sqlline the problems are still experienced.

Example query that hangs:  dfs.views .orderview points to CSV files in MapR-FS and dfs.views.customerview points to MapR-DB
{code}
SELECT ord.`month`, count(*) FROM dfs.views.orderview ord, dfs.views.customerview cust where cust.cust_id=ord.cust_id group by ord.`month`

The following query fails:

SELECT ord.`month`, count(*) FROM dfs.views.orderview ord, dfs.views.customerview cust group by ord.`month` 

Query failed: Failure while parsing sql. Node [rel#25065:Subset#7.LOGICAL.ANY([]).[]] could not be implemented; planner state:

Output received below



Root: rel#25065:Subset#7.LOGICAL.ANY([]).[]
Original rel:
AbstractConverter(subset=[rel#25065:Subset#7.LOGICAL.ANY([]).[]], convention=[LOGICAL], DrillDistributionTraitDef=[ANY([])], sort=[[]]): rowcount = 1.7976931348623157E308, cumulative cost = {inf}, id = 25066
  AggregateRel(subset=[rel#25064:Subset#7.NONE.ANY([]).[]], group=[{0}], EXPR$1=[COUNT()]): rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25063
    ProjectRel(subset=[rel#25062:Subset#6.NONE.ANY([]).[]], month=[$1]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25061
      JoinRel(subset=[rel#25060:Subset#5.NONE.ANY([]).[]], condition=[true], joinType=[inner]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25059
        ProjectRel(subset=[rel#25053:Subset#1.NONE.ANY([]).[]], order_id=[CAST(ITEM($1, 0)):BIGINT NOT NULL], month=[CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], purch_date=[CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL], cust_id=[CAST(ITEM($1, 3)):BIGINT NOT NULL], state=[CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], prod_id=[CAST(ITEM($1, 5)):BIGINT NOT NULL], order_total=[CAST(ITEM($1, 6)):INTEGER NOT NULL]): rowcount = 100.0, cumulative cost = {100.0 rows, 700.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25052
          EnumerableTableAccessRel(subset=[rel#25051:Subset#0.ENUMERABLE.ANY([]).[]], table=[[dfs, data, /orders]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25027
        ProjectRel(subset=[rel#25058:Subset#4.NONE.ANY([]).[]], cust_id=[$0], cust_name=[CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], cust_age=[CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], agg_rev=[CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], membership=[CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], address=[CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25057
          ProjectRel(subset=[rel#25056:Subset#3.NONE.ANY([]).[]], cust_id=[CAST($0):BIGINT NOT NULL], cust_name=[CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], cust_age=[CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], agg_rev=[CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], membership=[CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], address=[CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"]): rowcount = 100.0, cumulative cost = {100.0 rows, 600.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25055
            EnumerableTableAccessRel(subset=[rel#25054:Subset#2.ENUMERABLE.ANY([]).[]], table=[[maprdb, customers]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25031

Sets:
Set#0, type: (DrillRecordRow[*, columns])
	rel#25051:Subset#0.ENUMERABLE.ANY([]).[], best=rel#25027, importance=0.5904900000000001
		rel#25027:EnumerableTableAccessRel.ENUMERABLE.ANY([]).[](table=[dfs, data, /orders]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25117:AbstractConverter.ENUMERABLE.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],convention=ENUMERABLE,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=6456.0, cumulative cost={inf}
	rel#25116:Subset#0.LOGICAL.ANY([]).[], best=rel#25137, importance=0.531441
		rel#25118:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=100.0, cumulative cost={inf}
		rel#25137:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`*`]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6.456E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#1, type: RecordType(BIGINT order_id, VARCHAR(1) month, TIMESTAMP(0) purch_date, BIGINT cust_id, VARCHAR(1) state, BIGINT prod_id, INTEGER order_total)
	rel#25053:Subset#1.NONE.ANY([]).[], best=null, importance=0.6561
		rel#25052:ProjectRel.NONE.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],order_id=CAST(ITEM($1, 0)):BIGINT NOT NULL,month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($1, 3)):BIGINT NOT NULL,state=CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($1, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($1, 6)):INTEGER NOT NULL), rowcount=100.0, cumulative cost={inf}
	rel#25114:Subset#1.LOGICAL.ANY([]).[], best=rel#25113, importance=0.6561
		rel#25115:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25053:Subset#1.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25113:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25112:Subset#14.LOGICAL.ANY([]).[],order_id=CAST(ITEM($0, 0)):BIGINT NOT NULL,month=CAST(ITEM($0, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($0, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($0, 3)):BIGINT NOT NULL,state=CAST(ITEM($0, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($0, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($0, 6)):INTEGER NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6484.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25136:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],order_id=CAST(ITEM($1, 0)):BIGINT NOT NULL,month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($1, 3)):BIGINT NOT NULL,state=CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($1, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($1, 6)):INTEGER NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6.4560028E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#2, type: RecordType(ANY row_key, (VARCHAR(1), ANY) MAP address, (VARCHAR(1), ANY) MAP loyalty, (VARCHAR(1), ANY) MAP personal)
	rel#25054:Subset#2.ENUMERABLE.ANY([]).[], best=rel#25031, importance=0.531441
		rel#25031:EnumerableTableAccessRel.ENUMERABLE.ANY([]).[](table=[maprdb, customers]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25121:AbstractConverter.ENUMERABLE.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],convention=ENUMERABLE,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1048576.0, cumulative cost={inf}
	rel#25120:Subset#2.LOGICAL.ANY([]).[], best=rel#25138, importance=0.4782969000000001
		rel#25122:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=100.0, cumulative cost={inf}
		rel#25138:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`*`]]]), rowcount=1048576.0, cumulative cost={1048576.0 Error: exception while executing query: Failure while trying to get next result batch. (state=,code=0)
rows, 4194304.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#3, type: RecordType(BIGINT cust_id, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
	rel#25056:Subset#3.NONE.ANY([]).[], best=null, importance=0.5904900000000001
		rel#25055:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=100.0, cumulative cost={inf}
	rel#25108:Subset#3.LOGICAL.ANY([]).[], best=rel#25107, importance=0.6561
		rel#25109:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25107:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25106:Subset#13.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($1, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($1, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($3, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25135:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#4, type: RecordType(BIGINT cust_id, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
	rel#25058:Subset#4.NONE.ANY([]).[], best=null, importance=0.6561
		rel#25057:ProjectRel.NONE.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],cust_id=$0,cust_name=CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25102:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=100.0, cumulative cost={inf}
	rel#25125:Subset#4.LOGICAL.ANY([]).[], best=rel#25131, importance=0.6561
		rel#25126:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25058:Subset#4.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25131:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25106:Subset#13.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($1, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($1, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($3, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25132:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25134:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25108:Subset#3.LOGICAL.ANY([]).[],cust_id=$0,cust_name=CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194352.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#5, type: RecordType(BIGINT order_id, VARCHAR(1) month, TIMESTAMP(0) purch_date, BIGINT cust_id, VARCHAR(1) state, BIGINT prod_id, INTEGER order_total, BIGINT cust_id0, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
	rel#25060:Subset#5.NONE.ANY([]).[], best=null, importance=0.7290000000000001
		rel#25059:JoinRel.NONE.ANY([]).[](left=rel#25053:Subset#1.NONE.ANY([]).[],right=rel#25058:Subset#4.NONE.ANY([]).[],condition=true,joinType=inner), rowcount=1.7976931348623157E308, cumulative cost={inf}
	rel#25088:Subset#5.LOGICAL.ANY([]).[], best=null, importance=0.81
		rel#25089:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25060:Subset#5.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25133:DrillJoinRel.LOGICAL.ANY([]).[](left=rel#25114:Subset#1.LOGICAL.ANY([]).[],right=rel#25125:Subset#4.LOGICAL.ANY([]).[],condition=true,joinType=inner), rowcount=1048576.0, cumulative cost={inf}
Set#6, type: RecordType(VARCHAR(1) month)
	rel#25062:Subset#6.NONE.ANY([]).[], best=null, importance=0.81
		rel#25061:ProjectRel.NONE.ANY([]).[](child=rel#25060:Subset#5.NONE.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25078:ProjectRel.NONE.ANY([]).[](child=rel#25077:Subset#10.NONE.ANY([]).[],month=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
	rel#25067:Subset#6.LOGICAL.ANY([]).[], best=null, importance=0.9
		rel#25068:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25062:Subset#6.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25081:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25079:Subset#10.LOGICAL.ANY([]).[],month=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25090:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25088:Subset#5.LOGICAL.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
Set#7, type: RecordType(VARCHAR(1) month, BIGINT EXPR$1)
	rel#25064:Subset#7.NONE.ANY([]).[], best=null, importance=0.9
		rel#25063:AggregateRel.NONE.ANY([]).[](child=rel#25062:Subset#6.NONE.ANY([]).[],group={0},EXPR$1=COUNT()), rowcount=1.7976931348623158E307, cumulative cost={inf}
	rel#25065:Subset#7.LOGICAL.ANY([]).[], best=null, importance=1.0
		rel#25066:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25064:Subset#7.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25069:DrillAggregateRel.LOGICAL.ANY([]).[](child=rel#25067:Subset#6.LOGICAL.ANY([]).[],group={0},EXPR$1=COUNT()), rowcount=1.7976931348623158E307, cumulative cost={inf}
Set#8, type: RecordType(VARCHAR(1) month)
	rel#25074:Subset#8.NONE.ANY([]).[], best=null, importance=0.6561
		rel#25070:ProjectRel.NONE.ANY([]).[](child=rel#25053:Subset#1.NONE.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25097:ProjectRel.NONE.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=100.0, cumulative cost={inf}
	rel#25083:Subset#8.LOGICAL.ANY([]).[], best=rel#25101, importance=0.7290000000000001
		rel#25084:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25074:Subset#8.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25101:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25100:Subset#12.LOGICAL.ANY([]).[],month=CAST(ITEM($0, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6460.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25119:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6.4560004E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25128:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25114:Subset#1.LOGICAL.ANY([]).[],month=$1), rowcount=6456.0, cumulative cost={19368.0 rows, 6488.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#9, type: RecordType(BIGINT cust_id)
	rel#25075:Subset#9.NONE.ANY([]).[], best=null, importance=0.6561
		rel#25071:ProjectRel.NONE.ANY([]).[](child=rel#25058:Subset#4.NONE.ANY([]).[],cust_id=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25091:ProjectRel.NONE.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],cust_id=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25092:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=100.0, cumulative cost={inf}
	rel#25085:Subset#9.LOGICAL.ANY([]).[], best=rel#25096, importance=0.7290000000000001
		rel#25086:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25075:Subset#9.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25096:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25095:Subset#11.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 1048580.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25123:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194308.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25124:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25108:Subset#3.LOGICAL.ANY([]).[],cust_id=$0), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194332.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25127:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25125:Subset#4.LOGICAL.ANY([]).[],cust_id=$0), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194332.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#10, type: RecordType(VARCHAR(1) month, BIGINT cust_id)
	rel#25077:Subset#10.NONE.ANY([]).[], best=null, importance=0.7290000000000001
		rel#25076:JoinRel.NONE.ANY([]).[](left=rel#25074:Subset#8.NONE.ANY([]).[],right=rel#25075:Subset#9.NONE.ANY([]).[],condition=true,joinType=inner), rowcount=1.7976931348623157E308, cumulative cost={inf}
	rel#25079:Subset#10.LOGICAL.ANY([]).[], best=null, importance=0.81
		rel#25080:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25077:Subset#10.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25087:DrillJoinRel.LOGICAL.ANY([]).[](left=rel#25083:Subset#8.LOGICAL.ANY([]).[],right=rel#25085:Subset#9.LOGICAL.ANY([]).[],condition=true,joinType=inner), rowcount=1048576.0, cumulative cost={inf}
Set#11, type: RecordType(ANY row_key)
	rel#25095:Subset#11.LOGICAL.ANY([]).[], best=rel#25093, importance=0.6561
		rel#25093:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`row_key`]]]), rowcount=1048576.0, cumulative cost={1048576.0 rows, 1048576.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#12, type: RecordType(ANY columns)
	rel#25100:Subset#12.LOGICAL.ANY([]).[], best=rel#25098, importance=0.6561
		rel#25098:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`columns`[1]]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6456.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#13, type: RecordType(ANY row_key, (VARCHAR(1), ANY) MAP personal, (VARCHAR(1), ANY) MAP loyalty, (VARCHAR(1), ANY) MAP address)
	rel#25106:Subset#13.LOGICAL.ANY([]).[], best=rel#25104, importance=0.5904900000000001
		rel#25104:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`row_key`], SchemaPath [`personal`.`name`], SchemaPath [`personal`.`age`], SchemaPath [`loyalty`.`agg_rev`], SchemaPath [`loyalty`.`membership`], SchemaPath [`address`.`state`]]]), rowcount=1048576.0, cumulative cost={1048576.0 rows, 4194304.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#14, type: RecordType(ANY columns)
	rel#25112:Subset#14.LOGICAL.ANY([]).[], best=rel#25110, importance=0.5904900000000001
		rel#25110:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`columns`[0]], SchemaPath [`columns`[1]], SchemaPath [`columns`[2]], SchemaPath [`columns`[3]], SchemaPath [`columns`[4]], SchemaPath [`columns`[5]], SchemaPath [`columns`[6]]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6456.0 cpu, 0.0 io, 0.0 network, 0.0 memory}

 [8224f29c-1824-45d9-a74b-3d31e48a3419]
{code}


  was:
Queries generated by Analytic tools can include unnecessary tables and joins. When tracing ODBC calls and executing queries in sqlline the problems are still experienced.

Example query that hangs:  dfs.views .orderview points to CSV files in MapR-FS and dfs.views.customerview points to MapR-DB

SELECT ord.`month`, count(*) FROM dfs.views.orderview ord, dfs.views.customerview cust where cust.cust_id=ord.cust_id group by ord.`month`

The following query fails:

SELECT ord.`month`, count(*) FROM dfs.views.orderview ord, dfs.views.customerview cust group by ord.`month` 

Query failed: Failure while parsing sql. Node [rel#25065:Subset#7.LOGICAL.ANY([]).[]] could not be implemented; planner state:

Output received below



Root: rel#25065:Subset#7.LOGICAL.ANY([]).[]
Original rel:
AbstractConverter(subset=[rel#25065:Subset#7.LOGICAL.ANY([]).[]], convention=[LOGICAL], DrillDistributionTraitDef=[ANY([])], sort=[[]]): rowcount = 1.7976931348623157E308, cumulative cost = {inf}, id = 25066
  AggregateRel(subset=[rel#25064:Subset#7.NONE.ANY([]).[]], group=[{0}], EXPR$1=[COUNT()]): rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25063
    ProjectRel(subset=[rel#25062:Subset#6.NONE.ANY([]).[]], month=[$1]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25061
      JoinRel(subset=[rel#25060:Subset#5.NONE.ANY([]).[]], condition=[true], joinType=[inner]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25059
        ProjectRel(subset=[rel#25053:Subset#1.NONE.ANY([]).[]], order_id=[CAST(ITEM($1, 0)):BIGINT NOT NULL], month=[CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], purch_date=[CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL], cust_id=[CAST(ITEM($1, 3)):BIGINT NOT NULL], state=[CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], prod_id=[CAST(ITEM($1, 5)):BIGINT NOT NULL], order_total=[CAST(ITEM($1, 6)):INTEGER NOT NULL]): rowcount = 100.0, cumulative cost = {100.0 rows, 700.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25052
          EnumerableTableAccessRel(subset=[rel#25051:Subset#0.ENUMERABLE.ANY([]).[]], table=[[dfs, data, /orders]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25027
        ProjectRel(subset=[rel#25058:Subset#4.NONE.ANY([]).[]], cust_id=[$0], cust_name=[CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], cust_age=[CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], agg_rev=[CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], membership=[CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], address=[CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25057
          ProjectRel(subset=[rel#25056:Subset#3.NONE.ANY([]).[]], cust_id=[CAST($0):BIGINT NOT NULL], cust_name=[CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], cust_age=[CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], agg_rev=[CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], membership=[CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], address=[CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"]): rowcount = 100.0, cumulative cost = {100.0 rows, 600.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25055
            EnumerableTableAccessRel(subset=[rel#25054:Subset#2.ENUMERABLE.ANY([]).[]], table=[[maprdb, customers]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25031

Sets:
Set#0, type: (DrillRecordRow[*, columns])
	rel#25051:Subset#0.ENUMERABLE.ANY([]).[], best=rel#25027, importance=0.5904900000000001
		rel#25027:EnumerableTableAccessRel.ENUMERABLE.ANY([]).[](table=[dfs, data, /orders]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25117:AbstractConverter.ENUMERABLE.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],convention=ENUMERABLE,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=6456.0, cumulative cost={inf}
	rel#25116:Subset#0.LOGICAL.ANY([]).[], best=rel#25137, importance=0.531441
		rel#25118:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=100.0, cumulative cost={inf}
		rel#25137:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`*`]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6.456E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#1, type: RecordType(BIGINT order_id, VARCHAR(1) month, TIMESTAMP(0) purch_date, BIGINT cust_id, VARCHAR(1) state, BIGINT prod_id, INTEGER order_total)
	rel#25053:Subset#1.NONE.ANY([]).[], best=null, importance=0.6561
		rel#25052:ProjectRel.NONE.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],order_id=CAST(ITEM($1, 0)):BIGINT NOT NULL,month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($1, 3)):BIGINT NOT NULL,state=CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($1, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($1, 6)):INTEGER NOT NULL), rowcount=100.0, cumulative cost={inf}
	rel#25114:Subset#1.LOGICAL.ANY([]).[], best=rel#25113, importance=0.6561
		rel#25115:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25053:Subset#1.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25113:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25112:Subset#14.LOGICAL.ANY([]).[],order_id=CAST(ITEM($0, 0)):BIGINT NOT NULL,month=CAST(ITEM($0, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($0, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($0, 3)):BIGINT NOT NULL,state=CAST(ITEM($0, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($0, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($0, 6)):INTEGER NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6484.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25136:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],order_id=CAST(ITEM($1, 0)):BIGINT NOT NULL,month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($1, 3)):BIGINT NOT NULL,state=CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($1, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($1, 6)):INTEGER NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6.4560028E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#2, type: RecordType(ANY row_key, (VARCHAR(1), ANY) MAP address, (VARCHAR(1), ANY) MAP loyalty, (VARCHAR(1), ANY) MAP personal)
	rel#25054:Subset#2.ENUMERABLE.ANY([]).[], best=rel#25031, importance=0.531441
		rel#25031:EnumerableTableAccessRel.ENUMERABLE.ANY([]).[](table=[maprdb, customers]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25121:AbstractConverter.ENUMERABLE.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],convention=ENUMERABLE,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1048576.0, cumulative cost={inf}
	rel#25120:Subset#2.LOGICAL.ANY([]).[], best=rel#25138, importance=0.4782969000000001
		rel#25122:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=100.0, cumulative cost={inf}
		rel#25138:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`*`]]]), rowcount=1048576.0, cumulative cost={1048576.0 Error: exception while executing query: Failure while trying to get next result batch. (state=,code=0)
rows, 4194304.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#3, type: RecordType(BIGINT cust_id, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
	rel#25056:Subset#3.NONE.ANY([]).[], best=null, importance=0.5904900000000001
		rel#25055:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=100.0, cumulative cost={inf}
	rel#25108:Subset#3.LOGICAL.ANY([]).[], best=rel#25107, importance=0.6561
		rel#25109:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25107:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25106:Subset#13.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($1, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($1, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($3, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25135:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#4, type: RecordType(BIGINT cust_id, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
	rel#25058:Subset#4.NONE.ANY([]).[], best=null, importance=0.6561
		rel#25057:ProjectRel.NONE.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],cust_id=$0,cust_name=CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25102:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=100.0, cumulative cost={inf}
	rel#25125:Subset#4.LOGICAL.ANY([]).[], best=rel#25131, importance=0.6561
		rel#25126:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25058:Subset#4.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25131:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25106:Subset#13.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($1, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($1, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($3, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25132:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25134:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25108:Subset#3.LOGICAL.ANY([]).[],cust_id=$0,cust_name=CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194352.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#5, type: RecordType(BIGINT order_id, VARCHAR(1) month, TIMESTAMP(0) purch_date, BIGINT cust_id, VARCHAR(1) state, BIGINT prod_id, INTEGER order_total, BIGINT cust_id0, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
	rel#25060:Subset#5.NONE.ANY([]).[], best=null, importance=0.7290000000000001
		rel#25059:JoinRel.NONE.ANY([]).[](left=rel#25053:Subset#1.NONE.ANY([]).[],right=rel#25058:Subset#4.NONE.ANY([]).[],condition=true,joinType=inner), rowcount=1.7976931348623157E308, cumulative cost={inf}
	rel#25088:Subset#5.LOGICAL.ANY([]).[], best=null, importance=0.81
		rel#25089:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25060:Subset#5.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25133:DrillJoinRel.LOGICAL.ANY([]).[](left=rel#25114:Subset#1.LOGICAL.ANY([]).[],right=rel#25125:Subset#4.LOGICAL.ANY([]).[],condition=true,joinType=inner), rowcount=1048576.0, cumulative cost={inf}
Set#6, type: RecordType(VARCHAR(1) month)
	rel#25062:Subset#6.NONE.ANY([]).[], best=null, importance=0.81
		rel#25061:ProjectRel.NONE.ANY([]).[](child=rel#25060:Subset#5.NONE.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25078:ProjectRel.NONE.ANY([]).[](child=rel#25077:Subset#10.NONE.ANY([]).[],month=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
	rel#25067:Subset#6.LOGICAL.ANY([]).[], best=null, importance=0.9
		rel#25068:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25062:Subset#6.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25081:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25079:Subset#10.LOGICAL.ANY([]).[],month=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25090:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25088:Subset#5.LOGICAL.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
Set#7, type: RecordType(VARCHAR(1) month, BIGINT EXPR$1)
	rel#25064:Subset#7.NONE.ANY([]).[], best=null, importance=0.9
		rel#25063:AggregateRel.NONE.ANY([]).[](child=rel#25062:Subset#6.NONE.ANY([]).[],group={0},EXPR$1=COUNT()), rowcount=1.7976931348623158E307, cumulative cost={inf}
	rel#25065:Subset#7.LOGICAL.ANY([]).[], best=null, importance=1.0
		rel#25066:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25064:Subset#7.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25069:DrillAggregateRel.LOGICAL.ANY([]).[](child=rel#25067:Subset#6.LOGICAL.ANY([]).[],group={0},EXPR$1=COUNT()), rowcount=1.7976931348623158E307, cumulative cost={inf}
Set#8, type: RecordType(VARCHAR(1) month)
	rel#25074:Subset#8.NONE.ANY([]).[], best=null, importance=0.6561
		rel#25070:ProjectRel.NONE.ANY([]).[](child=rel#25053:Subset#1.NONE.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25097:ProjectRel.NONE.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=100.0, cumulative cost={inf}
	rel#25083:Subset#8.LOGICAL.ANY([]).[], best=rel#25101, importance=0.7290000000000001
		rel#25084:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25074:Subset#8.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25101:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25100:Subset#12.LOGICAL.ANY([]).[],month=CAST(ITEM($0, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6460.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25119:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6.4560004E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25128:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25114:Subset#1.LOGICAL.ANY([]).[],month=$1), rowcount=6456.0, cumulative cost={19368.0 rows, 6488.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#9, type: RecordType(BIGINT cust_id)
	rel#25075:Subset#9.NONE.ANY([]).[], best=null, importance=0.6561
		rel#25071:ProjectRel.NONE.ANY([]).[](child=rel#25058:Subset#4.NONE.ANY([]).[],cust_id=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25091:ProjectRel.NONE.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],cust_id=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25092:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=100.0, cumulative cost={inf}
	rel#25085:Subset#9.LOGICAL.ANY([]).[], best=rel#25096, importance=0.7290000000000001
		rel#25086:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25075:Subset#9.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25096:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25095:Subset#11.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 1048580.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25123:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194308.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25124:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25108:Subset#3.LOGICAL.ANY([]).[],cust_id=$0), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194332.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
		rel#25127:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25125:Subset#4.LOGICAL.ANY([]).[],cust_id=$0), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194332.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#10, type: RecordType(VARCHAR(1) month, BIGINT cust_id)
	rel#25077:Subset#10.NONE.ANY([]).[], best=null, importance=0.7290000000000001
		rel#25076:JoinRel.NONE.ANY([]).[](left=rel#25074:Subset#8.NONE.ANY([]).[],right=rel#25075:Subset#9.NONE.ANY([]).[],condition=true,joinType=inner), rowcount=1.7976931348623157E308, cumulative cost={inf}
	rel#25079:Subset#10.LOGICAL.ANY([]).[], best=null, importance=0.81
		rel#25080:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25077:Subset#10.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
		rel#25087:DrillJoinRel.LOGICAL.ANY([]).[](left=rel#25083:Subset#8.LOGICAL.ANY([]).[],right=rel#25085:Subset#9.LOGICAL.ANY([]).[],condition=true,joinType=inner), rowcount=1048576.0, cumulative cost={inf}
Set#11, type: RecordType(ANY row_key)
	rel#25095:Subset#11.LOGICAL.ANY([]).[], best=rel#25093, importance=0.6561
		rel#25093:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`row_key`]]]), rowcount=1048576.0, cumulative cost={1048576.0 rows, 1048576.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#12, type: RecordType(ANY columns)
	rel#25100:Subset#12.LOGICAL.ANY([]).[], best=rel#25098, importance=0.6561
		rel#25098:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`columns`[1]]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6456.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#13, type: RecordType(ANY row_key, (VARCHAR(1), ANY) MAP personal, (VARCHAR(1), ANY) MAP loyalty, (VARCHAR(1), ANY) MAP address)
	rel#25106:Subset#13.LOGICAL.ANY([]).[], best=rel#25104, importance=0.5904900000000001
		rel#25104:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`row_key`], SchemaPath [`personal`.`name`], SchemaPath [`personal`.`age`], SchemaPath [`loyalty`.`agg_rev`], SchemaPath [`loyalty`.`membership`], SchemaPath [`address`.`state`]]]), rowcount=1048576.0, cumulative cost={1048576.0 rows, 4194304.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
Set#14, type: RecordType(ANY columns)
	rel#25112:Subset#14.LOGICAL.ANY([]).[], best=rel#25110, importance=0.5904900000000001
		rel#25110:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`columns`[0]], SchemaPath [`columns`[1]], SchemaPath [`columns`[2]], SchemaPath [`columns`[3]], SchemaPath [`columns`[4]], SchemaPath [`columns`[5]], SchemaPath [`columns`[6]]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6456.0 cpu, 0.0 io, 0.0 network, 0.0 memory}

 [8224f29c-1824-45d9-a74b-3d31e48a3419]



> Analytic tool generated SQL queries hang or fail
> ------------------------------------------------
>
>                 Key: DRILL-1622
>                 URL: https://issues.apache.org/jira/browse/DRILL-1622
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 0.6.0
>         Environment: CentOS, DFS, Hive, HBase
> MapR 3.1.1
>            Reporter: Andries Engelbrecht
>            Priority: Blocker
>
> Queries generated by Analytic tools can include unnecessary tables and joins. When tracing ODBC calls and executing queries in sqlline the problems are still experienced.
> Example query that hangs:  dfs.views .orderview points to CSV files in MapR-FS and dfs.views.customerview points to MapR-DB
> {code}
> SELECT ord.`month`, count(*) FROM dfs.views.orderview ord, dfs.views.customerview cust where cust.cust_id=ord.cust_id group by ord.`month`
> The following query fails:
> SELECT ord.`month`, count(*) FROM dfs.views.orderview ord, dfs.views.customerview cust group by ord.`month` 
> Query failed: Failure while parsing sql. Node [rel#25065:Subset#7.LOGICAL.ANY([]).[]] could not be implemented; planner state:
> Output received below
> Root: rel#25065:Subset#7.LOGICAL.ANY([]).[]
> Original rel:
> AbstractConverter(subset=[rel#25065:Subset#7.LOGICAL.ANY([]).[]], convention=[LOGICAL], DrillDistributionTraitDef=[ANY([])], sort=[[]]): rowcount = 1.7976931348623157E308, cumulative cost = {inf}, id = 25066
>   AggregateRel(subset=[rel#25064:Subset#7.NONE.ANY([]).[]], group=[{0}], EXPR$1=[COUNT()]): rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25063
>     ProjectRel(subset=[rel#25062:Subset#6.NONE.ANY([]).[]], month=[$1]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25061
>       JoinRel(subset=[rel#25060:Subset#5.NONE.ANY([]).[]], condition=[true], joinType=[inner]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25059
>         ProjectRel(subset=[rel#25053:Subset#1.NONE.ANY([]).[]], order_id=[CAST(ITEM($1, 0)):BIGINT NOT NULL], month=[CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], purch_date=[CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL], cust_id=[CAST(ITEM($1, 3)):BIGINT NOT NULL], state=[CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], prod_id=[CAST(ITEM($1, 5)):BIGINT NOT NULL], order_total=[CAST(ITEM($1, 6)):INTEGER NOT NULL]): rowcount = 100.0, cumulative cost = {100.0 rows, 700.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25052
>           EnumerableTableAccessRel(subset=[rel#25051:Subset#0.ENUMERABLE.ANY([]).[]], table=[[dfs, data, /orders]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25027
>         ProjectRel(subset=[rel#25058:Subset#4.NONE.ANY([]).[]], cust_id=[$0], cust_name=[CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], cust_age=[CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], agg_rev=[CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], membership=[CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], address=[CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25057
>           ProjectRel(subset=[rel#25056:Subset#3.NONE.ANY([]).[]], cust_id=[CAST($0):BIGINT NOT NULL], cust_name=[CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], cust_age=[CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], agg_rev=[CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], membership=[CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], address=[CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"]): rowcount = 100.0, cumulative cost = {100.0 rows, 600.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25055
>             EnumerableTableAccessRel(subset=[rel#25054:Subset#2.ENUMERABLE.ANY([]).[]], table=[[maprdb, customers]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25031
> Sets:
> Set#0, type: (DrillRecordRow[*, columns])
> 	rel#25051:Subset#0.ENUMERABLE.ANY([]).[], best=rel#25027, importance=0.5904900000000001
> 		rel#25027:EnumerableTableAccessRel.ENUMERABLE.ANY([]).[](table=[dfs, data, /orders]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25117:AbstractConverter.ENUMERABLE.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],convention=ENUMERABLE,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=6456.0, cumulative cost={inf}
> 	rel#25116:Subset#0.LOGICAL.ANY([]).[], best=rel#25137, importance=0.531441
> 		rel#25118:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=100.0, cumulative cost={inf}
> 		rel#25137:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`*`]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6.456E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#1, type: RecordType(BIGINT order_id, VARCHAR(1) month, TIMESTAMP(0) purch_date, BIGINT cust_id, VARCHAR(1) state, BIGINT prod_id, INTEGER order_total)
> 	rel#25053:Subset#1.NONE.ANY([]).[], best=null, importance=0.6561
> 		rel#25052:ProjectRel.NONE.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],order_id=CAST(ITEM($1, 0)):BIGINT NOT NULL,month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($1, 3)):BIGINT NOT NULL,state=CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($1, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($1, 6)):INTEGER NOT NULL), rowcount=100.0, cumulative cost={inf}
> 	rel#25114:Subset#1.LOGICAL.ANY([]).[], best=rel#25113, importance=0.6561
> 		rel#25115:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25053:Subset#1.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25113:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25112:Subset#14.LOGICAL.ANY([]).[],order_id=CAST(ITEM($0, 0)):BIGINT NOT NULL,month=CAST(ITEM($0, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($0, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($0, 3)):BIGINT NOT NULL,state=CAST(ITEM($0, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($0, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($0, 6)):INTEGER NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6484.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25136:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],order_id=CAST(ITEM($1, 0)):BIGINT NOT NULL,month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($1, 3)):BIGINT NOT NULL,state=CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($1, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($1, 6)):INTEGER NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6.4560028E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#2, type: RecordType(ANY row_key, (VARCHAR(1), ANY) MAP address, (VARCHAR(1), ANY) MAP loyalty, (VARCHAR(1), ANY) MAP personal)
> 	rel#25054:Subset#2.ENUMERABLE.ANY([]).[], best=rel#25031, importance=0.531441
> 		rel#25031:EnumerableTableAccessRel.ENUMERABLE.ANY([]).[](table=[maprdb, customers]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25121:AbstractConverter.ENUMERABLE.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],convention=ENUMERABLE,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1048576.0, cumulative cost={inf}
> 	rel#25120:Subset#2.LOGICAL.ANY([]).[], best=rel#25138, importance=0.4782969000000001
> 		rel#25122:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=100.0, cumulative cost={inf}
> 		rel#25138:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`*`]]]), rowcount=1048576.0, cumulative cost={1048576.0 Error: exception while executing query: Failure while trying to get next result batch. (state=,code=0)
> rows, 4194304.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#3, type: RecordType(BIGINT cust_id, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
> 	rel#25056:Subset#3.NONE.ANY([]).[], best=null, importance=0.5904900000000001
> 		rel#25055:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=100.0, cumulative cost={inf}
> 	rel#25108:Subset#3.LOGICAL.ANY([]).[], best=rel#25107, importance=0.6561
> 		rel#25109:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25107:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25106:Subset#13.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($1, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($1, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($3, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25135:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#4, type: RecordType(BIGINT cust_id, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
> 	rel#25058:Subset#4.NONE.ANY([]).[], best=null, importance=0.6561
> 		rel#25057:ProjectRel.NONE.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],cust_id=$0,cust_name=CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25102:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=100.0, cumulative cost={inf}
> 	rel#25125:Subset#4.LOGICAL.ANY([]).[], best=rel#25131, importance=0.6561
> 		rel#25126:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25058:Subset#4.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25131:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25106:Subset#13.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($1, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($1, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($3, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25132:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL,cust_name=CAST(CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST(CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST(CAST(ITEM($2, 'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST(CAST(ITEM($1, 'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25134:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25108:Subset#3.LOGICAL.ANY([]).[],cust_id=$0,cust_name=CAST($1):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST($2):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST($3):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST($4):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST($5):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194352.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#5, type: RecordType(BIGINT order_id, VARCHAR(1) month, TIMESTAMP(0) purch_date, BIGINT cust_id, VARCHAR(1) state, BIGINT prod_id, INTEGER order_total, BIGINT cust_id0, VARCHAR(20) cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
> 	rel#25060:Subset#5.NONE.ANY([]).[], best=null, importance=0.7290000000000001
> 		rel#25059:JoinRel.NONE.ANY([]).[](left=rel#25053:Subset#1.NONE.ANY([]).[],right=rel#25058:Subset#4.NONE.ANY([]).[],condition=true,joinType=inner), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 	rel#25088:Subset#5.LOGICAL.ANY([]).[], best=null, importance=0.81
> 		rel#25089:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25060:Subset#5.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25133:DrillJoinRel.LOGICAL.ANY([]).[](left=rel#25114:Subset#1.LOGICAL.ANY([]).[],right=rel#25125:Subset#4.LOGICAL.ANY([]).[],condition=true,joinType=inner), rowcount=1048576.0, cumulative cost={inf}
> Set#6, type: RecordType(VARCHAR(1) month)
> 	rel#25062:Subset#6.NONE.ANY([]).[], best=null, importance=0.81
> 		rel#25061:ProjectRel.NONE.ANY([]).[](child=rel#25060:Subset#5.NONE.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25078:ProjectRel.NONE.ANY([]).[](child=rel#25077:Subset#10.NONE.ANY([]).[],month=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 	rel#25067:Subset#6.LOGICAL.ANY([]).[], best=null, importance=0.9
> 		rel#25068:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25062:Subset#6.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25081:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25079:Subset#10.LOGICAL.ANY([]).[],month=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25090:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25088:Subset#5.LOGICAL.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
> Set#7, type: RecordType(VARCHAR(1) month, BIGINT EXPR$1)
> 	rel#25064:Subset#7.NONE.ANY([]).[], best=null, importance=0.9
> 		rel#25063:AggregateRel.NONE.ANY([]).[](child=rel#25062:Subset#6.NONE.ANY([]).[],group={0},EXPR$1=COUNT()), rowcount=1.7976931348623158E307, cumulative cost={inf}
> 	rel#25065:Subset#7.LOGICAL.ANY([]).[], best=null, importance=1.0
> 		rel#25066:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25064:Subset#7.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25069:DrillAggregateRel.LOGICAL.ANY([]).[](child=rel#25067:Subset#6.LOGICAL.ANY([]).[],group={0},EXPR$1=COUNT()), rowcount=1.7976931348623158E307, cumulative cost={inf}
> Set#8, type: RecordType(VARCHAR(1) month)
> 	rel#25074:Subset#8.NONE.ANY([]).[], best=null, importance=0.6561
> 		rel#25070:ProjectRel.NONE.ANY([]).[](child=rel#25053:Subset#1.NONE.ANY([]).[],month=$1), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25097:ProjectRel.NONE.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=100.0, cumulative cost={inf}
> 	rel#25083:Subset#8.LOGICAL.ANY([]).[], best=rel#25101, importance=0.7290000000000001
> 		rel#25084:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25074:Subset#8.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25101:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25100:Subset#12.LOGICAL.ANY([]).[],month=CAST(ITEM($0, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6460.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25119:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6.4560004E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25128:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25114:Subset#1.LOGICAL.ANY([]).[],month=$1), rowcount=6456.0, cumulative cost={19368.0 rows, 6488.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#9, type: RecordType(BIGINT cust_id)
> 	rel#25075:Subset#9.NONE.ANY([]).[], best=null, importance=0.6561
> 		rel#25071:ProjectRel.NONE.ANY([]).[](child=rel#25058:Subset#4.NONE.ANY([]).[],cust_id=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25091:ProjectRel.NONE.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],cust_id=$0), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25092:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=100.0, cumulative cost={inf}
> 	rel#25085:Subset#9.LOGICAL.ANY([]).[], best=rel#25096, importance=0.7290000000000001
> 		rel#25086:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25075:Subset#9.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25096:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25095:Subset#11.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 1048580.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25123:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194308.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25124:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25108:Subset#3.LOGICAL.ANY([]).[],cust_id=$0), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194332.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25127:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25125:Subset#4.LOGICAL.ANY([]).[],cust_id=$0), rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194332.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#10, type: RecordType(VARCHAR(1) month, BIGINT cust_id)
> 	rel#25077:Subset#10.NONE.ANY([]).[], best=null, importance=0.7290000000000001
> 		rel#25076:JoinRel.NONE.ANY([]).[](left=rel#25074:Subset#8.NONE.ANY([]).[],right=rel#25075:Subset#9.NONE.ANY([]).[],condition=true,joinType=inner), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 	rel#25079:Subset#10.LOGICAL.ANY([]).[], best=null, importance=0.81
> 		rel#25080:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25077:Subset#10.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]), rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25087:DrillJoinRel.LOGICAL.ANY([]).[](left=rel#25083:Subset#8.LOGICAL.ANY([]).[],right=rel#25085:Subset#9.LOGICAL.ANY([]).[],condition=true,joinType=inner), rowcount=1048576.0, cumulative cost={inf}
> Set#11, type: RecordType(ANY row_key)
> 	rel#25095:Subset#11.LOGICAL.ANY([]).[], best=rel#25093, importance=0.6561
> 		rel#25093:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`row_key`]]]), rowcount=1048576.0, cumulative cost={1048576.0 rows, 1048576.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#12, type: RecordType(ANY columns)
> 	rel#25100:Subset#12.LOGICAL.ANY([]).[], best=rel#25098, importance=0.6561
> 		rel#25098:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`columns`[1]]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6456.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#13, type: RecordType(ANY row_key, (VARCHAR(1), ANY) MAP personal, (VARCHAR(1), ANY) MAP loyalty, (VARCHAR(1), ANY) MAP address)
> 	rel#25106:Subset#13.LOGICAL.ANY([]).[], best=rel#25104, importance=0.5904900000000001
> 		rel#25104:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null], columns=[SchemaPath [`row_key`], SchemaPath [`personal`.`name`], SchemaPath [`personal`.`age`], SchemaPath [`loyalty`.`agg_rev`], SchemaPath [`loyalty`.`membership`], SchemaPath [`address`.`state`]]]), rowcount=1048576.0, cumulative cost={1048576.0 rows, 4194304.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#14, type: RecordType(ANY columns)
> 	rel#25112:Subset#14.LOGICAL.ANY([]).[], best=rel#25110, importance=0.5904900000000001
> 		rel#25110:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan [selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`columns`[0]], SchemaPath [`columns`[1]], SchemaPath [`columns`[2]], SchemaPath [`columns`[3]], SchemaPath [`columns`[4]], SchemaPath [`columns`[5]], SchemaPath [`columns`[6]]]]), rowcount=6456.0, cumulative cost={6456.0 rows, 6456.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
>  [8224f29c-1824-45d9-a74b-3d31e48a3419]
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message