Cube.js: Automatically use inner joins when filtering

[nabaskes]

Hi, first of all, thanks for building Cube.js and releasing it as open source, it's very cool software.

Is your feature request related to a problem? Please describe.
If you filter on a field in a way that precludes it from being NULL, then there is no functional difference between an inner and a left join on tables in that field. However, there are slight performance benefits to having an inner join here.

Describe the solution you'd like
When joins are added in order to support a filter, or really whenever a joined cube is filtered on, the generated join can be an INNER JOIN rather than LEFT JOIN. I think functionality that handles this case would be appreciated, in lieu of broader capability to influence join type and path.

This probably isn't super important / high priority, I'm just interested whether this in the roadmap. I can also have a go at doing it in a PR if somebody can direct me in the general direction of the relevant code.

Thanks again for your time and expertise!

[paveltiunov]

Hey @nabaskes ! Thanks for posting this one! Yeah. I believe it's viable optimization. Even most of SQL query planners apply this optimization themselves I think it makes sense to have it in Cube.js as well.

[philippefutureboy]

Hey @nabaskes, @paveltiunov!

I'm not sure it's the best of the ideas to implement this Cube.js side - if the engines already do this kind of optimization themselves, why reproduce the optimization on Cube.js's side?
I see this as extra bug surface area and could also reduce the readability and debuggability of the queries when in development mode.
Our data scientist never uses INNER JOIN because of how INNER JOINs can implicitly filter out some records and result in some unintended consequences. Our position within the team at Arthur is to never use them and instead be explicit about our filtering intents.

Food for thought!