apache drill failed to execute SQL such as ' table1 left join table2 on condition 1 or condition 2'

[fengqiyuqingpingzhimo]

Execute the following sql (although it may not make sense): SELECT aa.employee_id FROM cp.employee.json aa left join cp.employee.json bb on aa.employee_id=bb.employee_id or aa.employee_id=bb.position_id ;

I got the results：

I tried to modify the corresponding parameters 'planner.enable_nljoin_for_scalar_only', but it still didn't work.

[cgivre]

I'm not sure what you're trying to do with this query, but I found that rewriting it like this worked:

 SELECT aa.employee_id 
 FROM cp.`employee.json` aa , 
 cp.`employee.json` bb
 WHERE aa.employee_id=bb.employee_id or aa.employee_id=bb.position_id

[fengqiyuqingpingzhimo]

I'm not sure what you're trying to do with this query, but I found that rewriting it like this worked:

 SELECT aa.employee_id 
 FROM cp.`employee.json` aa , 
 cp.`employee.json` bb
 WHERE aa.employee_id=bb.employee_id or aa.employee_id=bb.position_id

Thank you very much for your answer,But when I switched to MySQL, I encountered another problem,I created two storage configurations that point to the same MySQL(name is bb and zz),There is no problem when I use the same storage, such as SQL:

 select * from bb.eladmin.table1 a, bb.eladmin.table2 b where a.id =b.id or a.name=b.name

However, when I use two storage systems, the sql is as follows:

 select * from bb.eladmin.table1 a, zz.eladmin.table2 b where a.id =b.id or a.name=b.name

It still reports errors，Let me set the option 'planner.enable_nljoin_for_scalar_only' to false and try again，This is why？