JOIN Restrictions
There are certain restrictions around the types used in the JOIN predicates. The following example demonstrates a fallback scenario:
select
p.productnumber foo
from /users/composite/admin/oracle9i/GOSALES1/PRODUCT p
where 1 in (select
c.categoryid
from /users/composite/admin/northwind/Northwind/dbo/Categories c
where c.categoryid AS DECIMAL = p.productnumber)
The fallback reason while running the execution plan for the above query is:
"Unable to retrieve result from data source "/system/runtime/DRE" during query execution. Failed query: "SELECT `R_2`.`foo` FROM `ciscodv.Q-1920181812400727`.`R_2` WHERE 1 IN (SELECT DISTINCT `R_1`.`categoryid` FROM `ciscodv.Q-1920181812400727`.`R_1` WHERE `R_1`.`categoryid` = `R_2`.`foo`)". [data-3923000] - Cause: SYSTEM ERROR: DrillRuntimeException: Join only supports implicit casts between 1. Numeric data (none of types is decimal or both of them are decimal) 2. Varchar, Varbinary data 3. Date, Timestamp data Left type: VARDECIMAL, Right type: INT. Add explicit casts to avoid this error Fragment 1:2 [Error Id: 1854e912-7999-4429-a78a-40f160e81764 on dv-spark-5148.dv.local:3304]
By adding an explicit CAST (as given below), this situation can be avoided and the query will start to use the MPP Engine.
select
p.productnumber foo
from /users/composite/admin/oracle9i/GOSALES1/PRODUCT p
where 1 in (select
c.categoryid
from /users/composite/admin/northwind/Northwind/dbo/Categories c
where CAST(c.categoryid AS DECIMAL) = p.productnumber)