[
https://issues.apache.org/jira/browse/DRILL-1622?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jacques Nadeau resolved DRILL-1622.
-----------------------------------
Resolution: Cannot Reproduce
> 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
> Attachments: drillbit.log.se0.dmz, drillbit.log.se1.dmz,
> drillbit.log.se2.dmz, drillbit.log.se3.dmz, drillbit.log.se4.dmz,
> drillbit.out.se0.dmz, drillbit.out.se1.dmz, drillbit.out.se2.dmz,
> drillbit.out.se3.dmz, drillbit.out.se4.dmz
>
>
> 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`
> {code}
> The following query fails:
> {code}
> SELECT ord.`month`, count(*) FROM dfs.views.orderview ord,
> dfs.views.customerview cust group by ord.`month`
> {code}
> {code}
> 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)
