FULL OUTER JOIN is not supported

1

I am testing Dremio and quickly realized that Dremio does not support FULL OUTER JOIN.
It is very common use case when you need to FULL OUTER JOIN results of multiple metrics (measures) sliced by the same dimension (set of attributes).
Do you plan to add support for it?

AFAIK you are utilizing Apache Calcite, which definitely supports FULL OUTER JOIN :wink:

2

@jacek FOJ should be supported, maybe there is an inequality operator with the FOJ? Are you able to send me the profile to validate?

3

Hi Balaji,
you are right that FOJ works in Dremio in most cases.
I found out that it does not work if we generate FOJ with ON TRUE clause, example:

select t1.a, t2.b from t1 full outer join t2 on true;

This happens when two measures are calculated and stored into two cache tables, with empty dimension (slice by).
Such tables can then be joined only with ON TRUE clause.
We can workaround it by converting it into LEFT OUTER UNION RIGHT OUTER or by fetching each table and join it in application.
=> this missing feature is not that critical for us.