Complex queries - LEFT JOIN ON WHERE NULL issue

Plugins SQL
1

Hello, i ask for your help with complex queries,

data:
Two indices in elasticsearch

Goal:
I want to select all gateway_names within one index that are not existent in the other.

Approach:
SELECT * FROM index_large a LEFT JOIN index_small e ON (a.gateway_name = e.gateway_name) WHERE index_small .id is NULL

Similiar to (just the filter addition of all the null values):

grafik
grafik802×624 23 KB

Problem:
I do not retain all null-values, but all values. (The filter doesn’t seem to work)

Has someone a different approach or an idea what is wrong with mine?

Documentation of complex queries: https://opendistro.github.io/for-elasticsearch-docs/docs/sql/complex/

Thank you in advance!

Jonas

2

Hi @Jonas , we do have this issue for post-filtering/aggregation after JOIN. The current impl will push down the WHERE conditions to the DSL queries on each side. Here is the issue related for your reference: LEFT JOIN with WHERE doesn't filter the result as expected · Issue #124 · opendistro-for-elasticsearch/sql · GitHub. A complete support for JOIN will be added later. Thanks!

3

Hello, I’ve just faced the same issue. I understand the current limitation. How you recommend to find the difference between two indices A, B. Assuming we care only about id field.
How to find B.id which are absent in A.id ?
I also tried WHERE D.id NOT IТ (select A.id … ) it doesn’t work either.
Thanks