Search code examples
hadoopapache-drillmapr

Drill Join between 2 tables on 'Like' Operator returns UNSUPPORTED_OPERATION

I am trying to run one simple query on Apache Drill:

select OTT.Handset, OTT.Handset_OS from datasource.`./OTT_DETAILS_V` as OTT,  datasource.`./OS` as D 
where OTT.Handset_OS like concat('%', D.OS , '%');

The above query tries to get all handsets which has instring of OS from ./OS table. This query works on Oracle but on Drill it fails with below error.

Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join
[Error Id: 6f685286-1b79-4083-8a31-a31788c3c403 on cluster-3:31010] (state=,code=0)

I think this is bug in Drill. Is there any way we can join between 2 tables for 'Like' Operator?

Solution

  • I figured out that join in Drill (and many other databases) needs to have an exact match condition and can have additional like condition. Having like condition alone will throw the above error.