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-unsubscr...@drill.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
