apache
There are not enough rules to produce a node with desired properties
Describe the bug The drill report a exception since a query running, report msg:There are not enough rules to produce a node with desired properties
To Reproduce
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL);
CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL, L_PARTKEY INTEGER NOT NULL, L_SUPPKEY INTEGER NOT NULL, L_LINENUMBER INTEGER NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG CHAR(1) NOT NULL, L_LINESTATUS CHAR(1) NOT NULL, L_SHIPDATE DATE NOT NULL, L_COMMITDATE DATE NOT NULL, L_RECEIPTDATE DATE NOT NULL, L_SHIPINSTRUCT CHAR(25) NOT NULL, L_SHIPMODE CHAR(10) NOT NULL, L_COMMENT VARCHAR(44) NOT NULL);
select L_ORDERKEY,O_ORDERKEY from lineitem right join orders on O_ORDERKEY = L_ORDERKEY where L_ORDERKEY = 1 ;
Expected behavior
The query should be running normally
Screenshots
Error: SYSTEM ERROR: 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#3618:Subset#15.PHYSICAL.SINGLETON([]).[], the relevant part of the original plan is as follows 3579:VertexDrel 3577:DrillProjectRel(subset=[rel#3578:Subset#14.LOGICAL.ANY([]).[]], L_ORDERKEY=[CAST($0):INTEGER], O_ORDERKEY=[$1]) 3575:JdbcJoin(subset=[rel#3576:Subset#13.JDBC.mysql.ANY([]).[]], condition=[=($1, $0)], joinType=[inner]) 3572:DrillFilterRel(subset=[rel#3573:Subset#11.LOGICAL.ANY([]).[]], condition=[=($0, 1)]) 3306:JdbcTableScan(subset=[rel#3571:Subset#10.JDBC.mysql.ANY([]).[]], table=[[mysql, test, lineitem]]) 3307:JdbcTableScan(subset=[rel#3574:Subset#12.JDBC.mysql.ANY([]).[]], table=[[mysql, test, orders]])
Root: rel#3583:Subset#16.PHYSICAL.SINGLETON([]).[] Original rel: LogicalProject(subset=[rel#3356:Subset#4.LOGICAL.ANY([]).[]], L_ORDERKEY=[$0], O_ORDERKEY=[$1]): rowcount = 225.0, cumulative cost = {225.0 rows, 450.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 3354 LogicalFilter(subset=[rel#3353:Subset#3.NONE.ANY([]).[]], condition=[=($0, 1)]): rowcount = 225.0, cumulative cost = {225.0 rows, 1500.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 3352 LogicalJoin(subset=[rel#3351:Subset#2.NONE.ANY([]).[]], condition=[=($1, $0)], joinType=[right]): rowcount = 1500.0, cumulative cost = {1500.0 rows, 0.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 3350 JdbcTableScan(subset=[rel#3348:Subset#0.JDBC.mysql.ANY([]).[]], table=[[mysql, test, lineitem]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 3306 JdbcTableScan(subset=[rel#3349:Subset#1.JDBC.mysql.ANY([]).[]], table=[[mysql, test, orders]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 3307
Additional context Add any other context about the problem here.
This looks like an invalid query. Unless you ran this and selected a default schema first, it is necessary to specify what storage plugin you are creating a table in. IE:
CREATE TABLE mysql.ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
....
Note that this query spells out mysql.ORDERS rather than just ORDERS.
Lastly, what version of Drill are you using?
This looks like an invalid query. Unless you ran this and selected a default schema first, it is necessary to specify what storage plugin you are creating a table in. IE:
CREATE TABLE mysql.ORDERS ( O_ORDERKEY INTEGER NOT NULL, O_CUSTKEY INTEGER NOT NULL, O_ORDERSTATUS CHAR(1) NOT NULL, ....Note that this query spells out
mysql.ORDERSrather than justORDERS.Lastly, what version of Drill are you using?
I have create table xxx in mysql client terminal directly and just run sql on drill client。 The same sql run in mysql client and got a correct result set ，but drill not。 It bother me long time, i was try to read source code to find out way to fix it, but i was failure, and you can use newest version to reproduct issue.
I have create table xxx in mysql client terminal directly and just run sql on drill client。 The same sql run in mysql client and got a correct result set ，but drill not。 It bother me long time, i was try to read source code to find out way to fix it, but i was failure, and you can use newest version to reproduct issue.
Can you post your storage config after redacting any sensitive details? Which JDBC driver are you using? Are you able to run select * from mysql.lineitem limit 10 from Drill successfully?
I have create table xxx in mysql client terminal directly and just run sql on drill client。 The same sql run in mysql client and got a correct result set ，but drill not。 It bother me long time, i was try to read source code to find out way to fix it, but i was failure, and you can use newest version to reproduct issue.
Can you post your storage config after redacting any sensitive details? Which JDBC driver are you using? Are you able to run
select * from mysql.lineitem limit 10from Drill successfully?
first of all ，the columns both in the table lineitem and the table order should be set "not null"
storage config as follows： { "type": "jdbc", "driver": "com.mysql.jdbc.Driver", "url": "jdbc:mysql://...:3306", "username": "root", "password": "********", "writerBatchSize": 10000, "enabled": true }
JDBC driver as follows： mysql-connector-java-8.0.29.jar mysql-connector-java-5.1.49.jar tip: both of jdbc drive can be reproduce issue , whatever use which one;
select * from mysql.lineitem limit 10 could be succeed；
some sql exec phenomenon could help perhaps 1 failure： select L_ORDERKEY,O_ORDERKEY from lineitem right join orders on O_ORDERKEY = L_ORDERKEY where L_ORDERKEY = 1 ; success：select L_ORDERKEY,O_ORDERKEY from lineitem right join orders on cast(O_ORDERKEY as int) = L_ORDERKEY where L_ORDERKEY = 1 ;
some sql exec phenomenon could help perhaps 2 failure： select L_ORDERKEY,O_ORDERKEY from lineitem right join orders on O_ORDERKEY = L_ORDERKEY where L_ORDERKEY = 1 ; success： select L_ORDERKEY,O_ORDERKEY from lineitem right join orders on O_ORDERKEY = L_ORDERKEY ;
3 failure： select L_ORDERKEY,O_ORDERKEY from lineitem right join orders on O_ORDERKEY = L_ORDERKEY where L_ORDERKEY = 1 ; success： select L_ORDERKEY,O_ORDERKEY from lineitem right join orders on O_ORDERKEY = L_ORDERKEY where O_ORDERKEY = 1 ;
i was fouce on calcite program call stack as follow:
createCastRel:720, RelOptUtil (org.apache.calcite.plan) convert:2449, RelBuilder (org.apache.calcite.tools) perform:280, FilterJoinRule (org.apache.calcite.rel.rules) onMatch:384, FilterJoinRule$FilterIntoJoinRule (org.apache.calcite.rel.rules) onMatch:208, VolcanoRuleCall (org.apache.calcite.plan.volcano) findBestExp:633, VolcanoPlanner (org.apache.calcite.plan.volcano) run:327, Programs$RuleSetProgram (org.apache.calcite.tools) transform:405, DefaultSqlHandler (org.apache.drill.exec.planner.sql.handlers) transform:351, DefaultSqlHandler (org.apache.drill.exec.planner.sql.handlers) convertToRawDrel:245, DefaultSqlHandler (org.apache.drill.exec.planner.sql.handlers) convertToDrel:308, DefaultSqlHandler (org.apache.drill.exec.planner.sql.handlers) getPlan:173, DefaultSqlHandler (org.apache.drill.exec.planner.sql.handlers) getQueryPlan:302, DrillSqlWorker (org.apache.drill.exec.planner.sql) getPhysicalPlan:178, DrillSqlWorker (org.apache.drill.exec.planner.sql) convertPlan:143, DrillSqlWorker (org.apache.drill.exec.planner.sql) getPlan:108, DrillSqlWorker (org.apache.drill.exec.planner.sql) runSQL:593, Foreman (org.apache.drill.exec.work.foreman) run:274, Foreman (org.apache.drill.exec.work.foreman) runWorker:1149, ThreadPoolExecutor (java.util.concurrent) run:624, ThreadPoolExecutor$Worker (java.util.concurrent) run:750, Thread (java.lang)
we can track this function ”areRowTypesEqual()“ from progrem call stack as above ，if comment out code ” if (!type1.equals(type2)) { return false; }“ in function ”areRowTypesEqual“ by RelOptUtil.java ,the issue was fixed , but some unit test in calsite can not passd
it just my option ,maybe it was trouble idea in the beginning
Thanks, you've definitely provided enough high quality information for us to promote this to a Jira issue.
Hi Charles : I have create table xxx in mysql client terminal directly and just run sql on drill client。 The same sql run in mysql client got a correct result set ，but drill not。 It bother me long time, i was try to read source code to find out way to fix it, but i was failure, and you can use newest version to reproduct issue.
thanks for reply
At 2022-08-15 10:54:52, "Charles S. Givre" @.***> wrote:
This looks like an invalid query. Unless you ran this and selected a default schema first, it is necessary to specify what storage plugin you are creating a table in. IE:
CREATE TABLE mysql.ORDERS ( O_ORDERKEY INTEGER NOT NULL, O_CUSTKEY INTEGER NOT NULL, O_ORDERSTATUS CHAR(1) NOT NULL, ....
Note that this query spells out mysql.ORDERS rather than just ORDERS.
Lastly, what version of Drill are you using?
— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>