Andries Engelbrecht created DRILL-1622:
------------------------------------------
Summary: 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: Execution - Operators, SQL Parser
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
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]
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
