You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by GitBox <gi...@apache.org> on 2022/08/15 00:39:04 UTC
[GitHub] [drill] yaozhu opened a new issue, #2623: There are not enough rules to produce a node with desired properties
yaozhu opened a new issue, #2623:
URL: https://github.com/apache/drill/issues/2623
**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 is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [drill] yaozhu commented on issue #2623: There are not enough rules to produce a node with desired properties
Posted by GitBox <gi...@apache.org>.
yaozhu commented on issue #2623:
URL: https://github.com/apache/drill/issues/2623#issuecomment-1221665356
> 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?
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [drill] jnturton commented on issue #2623: There are not enough rules to produce a node with desired properties
Posted by GitBox <gi...@apache.org>.
jnturton commented on issue #2623:
URL: https://github.com/apache/drill/issues/2623#issuecomment-1221765691
> 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?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [drill] yaozhu commented on issue #2623: There are not enough rules to produce a node with desired properties
Posted by GitBox <gi...@apache.org>.
yaozhu commented on issue #2623:
URL: https://github.com/apache/drill/issues/2623#issuecomment-1221964781
> > 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?
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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [drill] jnturton commented on issue #2623: There are not enough rules to produce a node with desired properties
Posted by GitBox <gi...@apache.org>.
jnturton commented on issue #2623:
URL: https://github.com/apache/drill/issues/2623#issuecomment-1222035987
Thanks, you've definitely provided enough high quality information for us to promote this to a Jira issue.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [drill] yaozhu commented on issue #2623: There are not enough rules to produce a node with desired properties
Posted by GitBox <gi...@apache.org>.
yaozhu commented on issue #2623:
URL: https://github.com/apache/drill/issues/2623#issuecomment-1274179042
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: ***@***.***>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [drill] cgivre commented on issue #2623: There are not enough rules to produce a node with desired properties
Posted by GitBox <gi...@apache.org>.
cgivre commented on issue #2623:
URL: https://github.com/apache/drill/issues/2623#issuecomment-1214558608
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 is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org