drill

drill copied to clipboard

Describe the bug ERROR o.a.d.e.p.s.h.DefaultSqlHandler - There are not enough rules to produce a node with desired properties: convention=PHYSICAL, DrillDistributionTraitDef=SINGLETON([]), sort=[]. Missing conversion is VertexDrel[convention: LOGICAL -> PHYSICAL, DrillDistributionTraitDef: ANY([]) -> SINGLETON([])]

Graphviz: digraph G { root [style=filled,label="Root"]; subgraph cluster10{ label="Set 10 RecordType(INTEGER L_ORDERKEY)"; rel6 [label="rel#6:JdbcTableScan\ntable=[mysql, guihui, lineitem]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel529 [label="rel#529:VertexDrel\ninput=RelSubset#506\nrows=100.0, cost={200.0 rows, 201.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel533 [label="rel#533:AbstractConverter\ninput=RelSubset#532,convention=JDBC.mysql,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] rel534 [label="rel#534:AbstractConverter\ninput=RelSubset#532,convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] rel531 [label="rel#531:JdbcIntermediatePrel\ninput=RelSubset#506\nrows=100.0, cost={200.0 rows, 201.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] subset506 [label="rel#506:Subset#10.JDBC.mysql.ANY([]).[]"] subset530 [label="rel#530:Subset#10.LOGICAL.ANY([]).[]"] subset532 [label="rel#532:Subset#10.PHYSICAL.ANY([]).[]"] } subgraph cluster11{ label="Set 11 RecordType(INTEGER L_ORDERKEY)"; rel507 [label="rel#507:DrillFilterRel\ninput=RelSubset#506,condition==($0, 1)\nrows=15.0, cost={200.0 rows, 561.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel563 [label="rel#563:AbstractConverter\ninput=RelSubset#562,convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=15.0, cost={inf}",shape=box] rel561 [label="rel#561:FilterPrel\ninput=RelSubset#532,condition==($0, 1)\nrows=15.0, cost={300.0 rows, 661.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] subset508 [label="rel#508:Subset#11.LOGICAL.ANY([]).[]"] subset562 [label="rel#562:Subset#11.PHYSICAL.ANY([]).[]"] } subgraph cluster12{ label="Set 12 RecordType(INTEGER O_ORDERKEY)"; rel7 [label="rel#7:JdbcTableScan\ntable=[mysql, guihui, orders]\nrows=100.0, cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel520 [label="rel#520:VertexDrel\ninput=RelSubset#509\nrows=100.0, cost={200.0 rows, 201.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel524 [label="rel#524:AbstractConverter\ninput=RelSubset#523,convention=JDBC.mysql,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] rel525 [label="rel#525:AbstractConverter\ninput=RelSubset#523,convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] rel522 [label="rel#522:JdbcIntermediatePrel\ninput=RelSubset#509\nrows=100.0, cost={200.0 rows, 201.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] subset509 [label="rel#509:Subset#12.JDBC.mysql.ANY([]).[]"] subset521 [label="rel#521:Subset#12.LOGICAL.ANY([]).[]"] subset523 [label="rel#523:Subset#12.PHYSICAL.ANY([]).[]"] } subgraph cluster13{ label="Set 13 RecordType(INTEGER L_ORDERKEY, INTEGER O_ORDERKEY)"; rel510 [label="rel#510:JdbcJoin\nleft=RelSubset#508,right=RelSubset#509,condition==($0, $1),joinType=inner\nrows=100.0, cost={301.0 rows, 663.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel542 [label="rel#542:VertexDrel\ninput=RelSubset#511\nrows=100.0, cost={401.0 rows, 763.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel546 [label="rel#546:AbstractConverter\ninput=RelSubset#545,convention=JDBC.mysql,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] rel547 [label="rel#547:AbstractConverter\ninput=RelSubset#545,convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] rel544 [label="rel#544:JdbcIntermediatePrel\ninput=RelSubset#511\nrows=100.0, cost={401.0 rows, 763.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] subset511 [label="rel#511:Subset#13.JDBC.mysql.ANY([]).[]"] subset543 [label="rel#543:Subset#13.LOGICAL.ANY([]).[]"] subset545 [label="rel#545:Subset#13.PHYSICAL.ANY([]).[]"] } subgraph cluster14{ label="Set 14 RecordType(INTEGER L_ORDERKEY, INTEGER O_ORDERKEY)"; rel512 [label="rel#512:DrillProjectRel\ninput=RelSubset#511,L_ORDERKEY=CAST($0):INTEGER,O_ORDERKEY=$1\nrows=100.0, cost={401.0 rows, 1163.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel559 [label="rel#559:AbstractConverter\ninput=RelSubset#558,convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] rel557 [label="rel#557:ProjectPrel\ninput=RelSubset#545,L_ORDERKEY=CAST($0):INTEGER,O_ORDERKEY=$1\nrows=100.0, cost={501.0 rows, 1263.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] subset513 [label="rel#513:Subset#14.LOGICAL.ANY([]).[]"] subset558 [label="rel#558:Subset#14.PHYSICAL.RANDOM_DISTRIBUTED([]).[]"] } subgraph cluster15{ label="Set 15 RecordType(INTEGER L_ORDERKEY, INTEGER O_ORDERKEY)"; rel514 [label="rel#514:VertexDrel\ninput=RelSubset#513\nrows=100.0, cost={501.0 rows, 1263.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel554 [label="rel#554:AbstractConverter\ninput=RelSubset#553,convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] subset515 [label="rel#515:Subset#15.LOGICAL.ANY([]).[]"] subset553 [label="rel#553:Subset#15.PHYSICAL.SINGLETON([]).[]",color=red] } subgraph cluster16{ label="Set 16 RecordType(INTEGER L_ORDERKEY, INTEGER O_ORDERKEY)"; rel516 [label="rel#516:DrillScreenRel\ninput=RelSubset#515\nrows=100.0, cost={511.0 rows, 1273.0 cpu, 0.0 io, 0.0 network, 0.0 memory}",color=blue,shape=box] rel519 [label="rel#519:AbstractConverter\ninput=RelSubset#518,convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]\nrows=100.0, cost={inf}",shape=box] rel555 [label="rel#555:ScreenPrel\ninput=RelSubset#553\nrows=100.0, cost={inf}",shape=box] subset517 [label="rel#517:Subset#16.LOGICAL.ANY([]).[]"] subset518 [label="rel#518:Subset#16.PHYSICAL.SINGLETON([]).[]"] } root -> subset518; subset506 -> rel6[color=blue]; subset530 -> rel529[color=blue]; rel529 -> subset506[color=blue]; subset506 -> rel533; rel533 -> subset532; subset530 -> rel534; rel534 -> subset532; subset532 -> rel531[color=blue]; rel531 -> subset506[color=blue]; subset508 -> rel507[color=blue]; rel507 -> subset506[color=blue]; subset508 -> rel563; rel563 -> subset562; subset562 -> rel561[color=blue]; rel561 -> subset532[color=blue]; subset509 -> rel7[color=blue]; subset521 -> rel520[color=blue]; rel520 -> subset509[color=blue]; subset509 -> rel524; rel524 -> subset523; subset521 -> rel525; rel525 -> subset523; subset523 -> rel522[color=blue]; rel522 -> subset509[color=blue]; subset511 -> rel510[color=blue]; rel510 -> subset508[color=blue,label="0"]; rel510 -> subset509[color=blue,label="1"]; subset543 -> rel542[color=blue]; rel542 -> subset511[color=blue]; subset511 -> rel546; rel546 -> subset545; subset543 -> rel547; rel547 -> subset545; subset545 -> rel544[color=blue]; rel544 -> subset511[color=blue]; subset513 -> rel512[color=blue]; rel512 -> subset511[color=blue]; subset513 -> rel559; rel559 -> subset558; subset558 -> rel557[color=blue]; rel557 -> subset545[color=blue]; subset515 -> rel514[color=blue]; rel514 -> subset513[color=blue]; subset515 -> rel554; rel554 -> subset553; subset517 -> rel516[color=blue]; rel516 -> subset515[color=blue]; subset517 -> rel519; rel519 -> subset518; subset518 -> rel555; rel555 -> subset553; } 2022-07-08 03:06:52,474 [1d3860bd-8e96-1d99-3b73-378c343f5fc8:foreman] TRACE o.apache.calcite.plan.RelOptPlanner - Caught exception in class=org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer, method=visit org.apache.calcite.plan.RelOptPlanner$CannotPlanException: There are not enough rules to produce a node with desired properties: convention=PHYSICAL, DrillDistributionTraitDef=SINGLETON([]), sort=[]. Missing conversion is VertexDrel[convention: LOGICAL -> PHYSICAL, DrillDistributionTraitDef: ANY([]) -> SINGLETON([])] There is 1 empty subset: rel#553:Subset#15.PHYSICAL.SINGLETON([]).[], the relevant part of the original plan is as follows 514:VertexDrel 512:DrillProjectRel(subset=[rel#513:Subset#14.LOGICAL.ANY([]).[]], L_ORDERKEY=[CAST($0):INTEGER], O_ORDERKEY=[$1]) 510:JdbcJoin(subset=[rel#511:Subset#13.JDBC.mysql.ANY([]).[]], condition=[=($0, $1)], joinType=[inner]) 507:DrillFilterRel(subset=[rel#508:Subset#11.LOGICAL.ANY([]).[]], condition=[=($0, 1)]) 6:JdbcTableScan(subset=[rel#506:Subset#10.JDBC.mysql.ANY([]).[]], table=[[mysql, guihui, lineitem]]) 7:JdbcTableScan(subset=[rel#509:Subset#12.JDBC.mysql.ANY([]).[]], table=[[mysql, guihui, orders]])

To Reproduce Steps to reproduce the behavior:

1. CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL);
2. CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL);
3. select L_ORDERKEY,O_ORDERKEY from lineitem right join orders on L_ORDERKEY = O_ORDERKEY where L_ORDERKEY = 1 ;
4. See error

Expected behavior Execute correctly.

Desktop (please complete the following information):

• OS: [Centos7]
• Version [drill-1.21]

@guihui123456 Thank you for the report. We should have such unit test coverage. It may have been the result of JDBC's unique Calcite custom rules.

@guihui123456 Thank you for the report. We should have such unit test coverage. It may have been the result of JDBC's unique Calcite custom rules.

@luocooong Thanks for your reply, I think it's caused by FilterJoinRule in calcite, but I don't know how to fix it.

@guihui123456 Can you please try with the current master? We updated Calcite and I'm curious as to whether this fixes the issue.

@guihui123456 Can you please try with the current master? We updated Calcite and I'm curious as to whether this fixes the issue.

@cgivre This issue is still in the current master.