ESQL: Review existing optimizations being applied on LOOKUP JOIN

[costin]

Description

Due to the new nature of LOOKUP JOIN some existing optimizations don't get applied to it. For example:

FROM logs
| LOOKUP JOIN ips
| KEEP <fields from logs>

FROM logs
| LOOKUP JOIN ips
| STATS c = count()

In the queries above the LOOKUP JOIN can be completely removed since it doesn't affect the output.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[alex-spies]

In the queries above the LOOKUP JOIN can be completely removed since it doesn't affect the output.

I'm afraid that's not correct. LOOKUP JOIN does affect the number of rows because there can be multiple matches per input row. Just counting the rows after performing a LOOKUP JOIN is a valid thing to do IMO.

@costin , if you agree, I think this issue can be closed as not planned. We already have #119082 which tracks relevant optimizations - I'll add a note there to make sure there are no other existing optimizations that could or should be carried over to LOOKUP JOIN.