I have a query that joins across 3 datasources: a Postgres DB, a set of Parquet files on S3, and an Oracle DB.
This version of the query works (with real names replaced by dummies)
select isp.field1, isp.field2, ie.payloadJson.field3 as field3, k.field4, k.field5
from “postgres-db”.public.“v_isp” isp
left join myspace.myparquetdataset ie on (isp.eventIdentifier = ie.eventIdentifier)
left join “oracle-db”.“myschema”.“mytable” k on (isp.id = k.id)
where
isp.field6 IS NULL
myparquetdataset is a simple dataset that converts a JSON string field using convert_from(payload, ‘JSON’) AS payloadJson
However, both of following variants do not work, but result in an UnsupportedOperationException:
a) Switching the order of the joins.
select isp.field1, isp.field2, ie.jsonPayload.field3 as field3, k.field4, k.field5
from “postgres-db”.public.“v_isp” isp
left join “oracle-db”.“myschema”.“mytable” k on (isp.id = k.id)
left join myspace.mydataset ie on (isp.eventIdentifier = ie.eventIdentifier)
where
isp.field6 IS NULL
b) Removing the where clause:
select isp.field1, isp.field2, ie.jsonPayload.field3 as field3, k.field4, k.field5
from “postgres-db”.public.“v_isp” isp
left join myspace.mydataset ie on (isp.eventIdentifier = ie.eventIdentifier)
left join “oracle-db”.“myschema”.“mytable” k on (isp.id = k.id)
It seems there is little extra info in server.log, only a mention of “foreman-planning failed” - pasted below.
What is causing this strange behavior?
While I have found a working query eventually, it took me two hours to find it, and most importantly, I cannot give this in the hands of non-technical users wit this behavior…
2020-02-18 12:20:39,626 [21b42967-9328-f2d3-188b-579bf2819f00/0:foreman-planning] ERROR c.d.s.commandpool.CommandWrapper - command 21b42967-9328-f2d3-188b-579bf2819f00/0:foreman-planning failed
com.dremio.common.exceptions.UserException: UnsupportedOperationException
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:776) ~[dremio-common-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377]
at com.dremio.exec.planner.sql.SqlExceptionHelper.coerceException(SqlExceptionHelper.java:126)
…