I’m trying to do a simple left join with date ranges but I’m ending up with the following error:
“UNSUPPORTED OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join”
Tried setting Drill option ‘planner.enable_nljoin_for_scalar_only’ to ‘false’ but that doesn’t seem to help.
Are there any known workarounds?
Hey @tenkatim could you share the query profile for the failing query before and after enabling the flag? Would have expected that to work (although not performant/recommended for mid-large datasets).
Sure and thanks for the help…
Profile when ‘false’: 950817ac-5d31-4805-930c-bbf156e869a5_false.zip (3.2 KB)
Profile when ‘true’: b217a68b-2fad-43d5-b305-fcf1b339b91d_true.zip (3.2 KB)
The profiles are slightly edited since I had to remove client names etc. - hence FOOs and BARs.
BTW. How would you define mid-large datasets?
@tenkatim there is no exact number but, since it’s a cartesian join, it’ll get really big really fast.
Looks like the option is not getting picked up. How are you setting the flag? Since Dremio doesn’t support session variables, you’d do this by going to “Admin” > Advanced Settings, adding this flag under “Dremio Support” and clicking Save.
@can Yes, I set the flag like you described.
The result of the following query ‘SELECT name, num_val, string_val, bool_val, float_val FROM sys.options WHERE type in (‘SYSTEM’,‘SESSION’) order by name;’ shows that the flag is ‘false’.
Just to rule out any osx specific stuff and old clutter, I even did a fresh centos install (os + dremio) and the end result is exactly the same.
Glad to see that it worked for me.