[
https://issues.apache.org/jira/browse/DRILL-1514?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14207933#comment-14207933
]
B Anil Kumar commented on DRILL-1514:
-------------------------------------
I forgot to attach the drill plans.
For : *EXPLAIN PLAN FOR SELECT t1.first_name FROM mongo.employee.`empinfo` t1
JOIN mongo.employee.`empinfo` t2 ON t1.`employee_id` = t2.`employee_id`*
{{doesn't work}}
{noformat}
2014-11-12 16:30:36,579 [3e897c1a-9547-4b60-b86e-3f8ee519f663:foreman] DEBUG
o.a.d.e.p.s.h.DefaultSqlHandler - Drill Logical :
DrillScreenRel: rowcount = 19.0, cumulative cost = {115.9 rows, 190055.9 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 61
DrillProjectRel(first_name=[$1]): rowcount = 19.0, cumulative cost = {114.0
rows, 190054.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 60
DrillJoinRel(condition=[=($0, $2)], joinType=[inner]): rowcount = 19.0,
cumulative cost = {95.0 rows, 190050.0 cpu, 0.0 io, 0.0 network, 0.0 memory},
id = 59
DrillProjectRel(employee_id=[$1], first_name=[$0]): rowcount = 19.0,
cumulative cost = {38.0 rows, 46.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
57
DrillScanRel(table=[[mongo, employee, empinfo]],
groupscan=[MongoGroupScan [MongoScanSpec=MongoScanSpec [dbName=employee,
collectionName=empinfo, filters=null], columns=[SchemaPath [`employee_id`],
SchemaPath [`first_name`]]]]): rowcount = 19.0, cumulative cost = {19.0 rows,
38.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 42
DrillProjectRel(*=[$0]): rowcount = 19.0, cumulative cost = {38.0 rows,
190004.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 58
DrillScanRel(table=[[mongo, employee, empinfo]],
groupscan=[MongoGroupScan [MongoScanSpec=MongoScanSpec [dbName=employee,
collectionName=empinfo, filters=null], columns=[SchemaPath [`*`]]]]): rowcount
= 19.0, cumulative cost = {19.0 rows, 190000.0 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 55
2014-11-12 16:30:36,877 [3e897c1a-9547-4b60-b86e-3f8ee519f663:foreman] DEBUG
o.a.d.e.p.s.h.DefaultSqlHandler - Drill Physical :
00-00 Screen: rowcount = 19.0, cumulative cost = {172.9 rows, 561.9 cpu, 0.0
io, 0.0 network, 334.40000000000003 memory}, id = 435
00-01 Project(first_name=[$0]): rowcount = 19.0, cumulative cost = {171.0
rows, 560.0 cpu, 0.0 io, 0.0 network, 334.40000000000003 memory}, id = 434
00-02 Project(first_name=[$1]): rowcount = 19.0, cumulative cost =
{152.0 rows, 556.0 cpu, 0.0 io, 0.0 network, 334.40000000000003 memory}, id =
433
00-03 HashJoin(condition=[=($0, $2)], joinType=[inner]): rowcount =
19.0, cumulative cost = {133.0 rows, 552.0 cpu, 0.0 io, 0.0 network,
334.40000000000003 memory}, id = 432
00-05 Project(employee_id=[$1], first_name=[$0]): rowcount = 19.0,
cumulative cost = {38.0 rows, 46.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
428
00-07 Scan(groupscan=[MongoGroupScan [MongoScanSpec=MongoScanSpec
[dbName=employee, collectionName=empinfo, filters=null], columns=[SchemaPath
[`employee_id`], SchemaPath [`first_name`]]]]): rowcount = 19.0, cumulative
cost = {19.0 rows, 38.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 427
00-04 Project(T0¦¦*=[$0]): rowcount = 19.0, cumulative cost = {57.0
rows, 50.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 431
00-06 Project(T0¦¦*=[$0], T0¦¦employee_id=[$1]): rowcount = 19.0,
cumulative cost = {38.0 rows, 46.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
430
00-08 Scan(groupscan=[MongoGroupScan
[MongoScanSpec=MongoScanSpec [dbName=employee, collectionName=empinfo,
filters=null], columns=[SchemaPath [`*`]]]]): rowcount = 19.0, cumulative cost
= {19.0 rows, 38.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 429
{noformat}
And for : *EXPLAIN PLAN FOR SELECT t2.first_name FROM mongo.employee.`empinfo`
t1 JOIN mongo.employee.`empinfo` t2 ON t1.`employee_id` = t2.`employee_id`*
{{works}}
{noformat}
2014-11-11 22:50:57,860 [702633c6-9754-46e1-9a50-dac7a771422c:foreman] DEBUG
o.a.d.e.p.s.h.DefaultSqlHandler - Drill Logical :
DrillScreenRel: rowcount = 2.0, cumulative cost = {10.2 rows, 22.2 cpu, 0.0 io,
0.0 network, 0.0 memory}, id = 10027
DrillProjectRel(first_name=[$2], employee_id=[$1]): rowcount = 2.0,
cumulative cost = {10.0 rows, 22.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
10026
DrillJoinRel(condition=[=($0, $1)], joinType=[inner]): rowcount = 2.0,
cumulative cost = {8.0 rows, 14.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
10025
DrillScanRel(table=[[mongo, employee, join1]], groupscan=[MongoGroupScan
[MongoScanSpec=MongoScanSpec [dbName=employee, collectionName=join1,
filters=null], columns=[SchemaPath [`employee_id`]]]]): rowcount = 2.0,
cumulative cost = {2.0 rows, 2.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
10011
DrillProjectRel(employee_id=[$1], first_name=[$0]): rowcount = 2.0,
cumulative cost = {4.0 rows, 12.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
10024
DrillScanRel(table=[[mongo, employee, join1]],
groupscan=[MongoGroupScan [MongoScanSpec=MongoScanSpec [dbName=employee,
collectionName=join1, filters=null], columns=[SchemaPath [`employee_id`],
SchemaPath [`first_name`]]]]): rowcount = 2.0, cumulative cost = {2.0 rows, 4.0
cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 10007
2014-11-11 22:50:58,003 [702633c6-9754-46e1-9a50-dac7a771422c:foreman] DEBUG
o.a.d.e.p.s.h.DefaultSqlHandler - Drill Physical :
00-00 Screen: rowcount = 2.0, cumulative cost = {16.2 rows, 86.2 cpu, 0.0
io, 0.0 network, 35.2 memory}, id = 10407
00-01 Project(first_name=[$0], employee_id=[$1]): rowcount = 2.0,
cumulative cost = {16.0 rows, 86.0 cpu, 0.0 io, 0.0 network, 35.2 memory}, id =
10406
00-02 Project(first_name=[$2], employee_id=[$1]): rowcount = 2.0,
cumulative cost = {14.0 rows, 78.0 cpu, 0.0 io, 0.0 network, 35.2 memory}, id =
10405
00-03 HashJoin(condition=[=($0, $1)], joinType=[inner]): rowcount =
2.0, cumulative cost = {12.0 rows, 70.0 cpu, 0.0 io, 0.0 network, 35.2 memory},
id = 10404
00-05 Scan(groupscan=[MongoGroupScan [MongoScanSpec=MongoScanSpec
[dbName=employee, collectionName=join1, filters=null], columns=[SchemaPath
[`employee_id`]]]]): rowcount = 2.0, cumulative cost = {2.0 rows, 2.0 cpu, 0.0
io, 0.0 network, 0.0 memory}, id = 10400
00-04 Project(employee_id0=[$0], first_name=[$1]): rowcount = 2.0,
cumulative cost = {6.0 rows, 20.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
10403
00-06 Project(employee_id=[$1], first_name=[$0]): rowcount = 2.0,
cumulative cost = {4.0 rows, 12.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
10402
00-07 Scan(groupscan=[MongoGroupScan
[MongoScanSpec=MongoScanSpec [dbName=employee, collectionName=join1,
filters=null], columns=[SchemaPath [`employee_id`], SchemaPath
[`first_name`]]]]): rowcount = 2.0, cumulative cost = {2.0 rows, 4.0 cpu, 0.0
io, 0.0 network, 0.0 memory}, id = 10401
{noformat}
> Joins are not working with mongo
> --------------------------------
>
> Key: DRILL-1514
> URL: https://issues.apache.org/jira/browse/DRILL-1514
> Project: Apache Drill
> Issue Type: Bug
> Components: Storage - MongoDB
> Environment: Joins with Mongo storage plugin
> Reporter: B Anil Kumar
> Assignee: B Anil Kumar
>
> As pointed in review board(https://reviews.apache.org/r/25996/), joins are
> not working with mongo storage plugin.
> Few sample queries:
> {noformat}
> 1) SELECT t1.first_name FROM mongo.employee.`empinfo` t1 JOIN
> mongo.employee.`empinfo` t2 ON t1.`employee_id` = t2.`employee_id`
> 2) SELECT t1.first_name, t2.last_name FROM mongo.employee.`empinfo` t1 JOIN
> cp.`employee.json` t2 ON t1.`employee_id` = t2.`employee_id` limit 10
> {noformat}
> The Error is as below:
> {noformat}
> 2014-10-09 11:57:59,721 [d1785c2e-ac11-4f1e-9f7f-1f880aeceb7e:frag:0:0] DEBUG
> o.a.d.e.w.fragment.FragmentExecutor - Error while initializing or executing
> fragment
> java.lang.UnsupportedOperationException: Failure finding function that
> runtime code generation expected. Signature: compare_to(
> BIGINT:OPTIONALVARCHAR:OPTIONAL, ) returns INT:REQUIRED
> at
> org.apache.drill.exec.expr.fn.FunctionGenerationHelper.getFunctionExpression(FunctionGenerationHelper.java:74)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.expr.fn.FunctionGenerationHelper.getComparator(FunctionGenerationHelper.java:45)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.common.ChainedHashTable.setupIsKeyMatchInternal(ChainedHashTable.java:232)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.common.ChainedHashTable.createAndSetupHashTable(ChainedHashTable.java:182)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.join.HashJoinBatch.setupHashTable(HashJoinBatch.java:298)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.join.HashJoinBatch.executeBuildPhase(HashJoinBatch.java:325)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.join.HashJoinBatch.innerNext(HashJoinBatch.java:193)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:105)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:117)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:85)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:75)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext(AbstractSingleRecordBatch.java:50)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:127)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:105)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:117)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:85)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:75)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext(AbstractSingleRecordBatch.java:50)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:127)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:105)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:117)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:67)
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:97)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:57)
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:104)
>
> ~[drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> org.apache.drill.exec.work.WorkManager$RunnableWrapper.run(WorkManager.java:250)
>
> [drill-java-exec-0.7.0-incubating-SNAPSHOT-rebuffed.jar:0.7.0-incubating-SNAPSHOT]
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> [na:1.7.0_60]
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> [na:1.7.0_60]
> at java.lang.Thread.run(Thread.java:745) [na:1.7.0_60]
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
