ES|QL: IllegalStateException with RENAME x AS y, y AS x

[luigidellaquila]

On CSV test dataset

from airports_not_indexed 
| drop `city_location`, country, city, ab*, `type`, city_location 
| eval  location = null, fJkoOJwx = -1, `scalerank` = "a", OZucUvNbqb = 900014945 
| rename fJkoOJwx AS OZucUvNbqb, `name` AS FXIrumceg, `scalerank` AS `OZucUvNbqb` 
| drop `FXIrumceg`, FXIrumceg 
| sort OZucUvNbqb DESC 
| mv_expand OZucUvNbqb 
| grok OZucUvNbqb "%{WORD:location} %{WORD:sTyuHhEoGUsq} %{WORD:OZucUvNbqb}" 
| sort sTyuHhEoGUsq 
| sort OZucUvNbqb DESC
| rename OZucUvNbqb as message 
| lookup join message_types_lookup on message 
| limit 14037 
| enrich languages_policy on type 
| sort language_name DESC 
| mv_expand `type` 
| rename `type` AS EhWPxhBDo, sTyuHhEoGUsq AS `EhWPxhBDo`, `message` AS `fKlJNGHdRX`
| rename fKlJNGHdRX as message 
| lookup join message_types_lookup on message

{
    "error": {
        "root_cause": [
            {
                "type": "illegal_state_exception",
                "reason": "Found 1 problem\nline 1:774: Plan [Join[LEFT,[message{r}#57],[message{r}#57],[message{f}#70]]] optimized incorrectly due to missing references from left hand side [message{r}#57]"
            }
        ],
        "type": "illegal_state_exception",
        "reason": "Found 1 problem\nline 1:774: Plan [Join[LEFT,[message{r}#57],[message{r}#57],[message{f}#70]]] optimized incorrectly due to missing references from left hand side [message{r}#57]"
    },
    "status": 500
}

See #131508

[elasticsearchmachine]

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

[alex-spies]

Reduced the reproducer to

row message = \"a\" | lookup join message_types_lookup on message | rename `message` AS `fKlJNGHdRX`, fKlJNGHdRX as message | lookup join message_types_lookup on message

Seems to be the re-renaming that we don't deal well with.

[alex-spies]

The culprit is PushDownJoinPastProject, the rename message{r}#1558 AS message#1565 doesn't get propagated into it during the pushdown:

[2025-07-18T11:01:45,188][TRACE][o.e.x.e.o.LogicalPlanOptimizer] [runTask-0] Rule logical.PushDownJoinPastProject applied
Limit[1000[INTEGER],true]                                                         = Limit[1000[INTEGER],true]
\_Join[LEFT,[message{r}#1565],[message{r}#1565],[message{f}#1569]]                ! \_Project[[message{r}#1558 AS message#1565, type{f}#1570]]
  |_EsqlProject[[message{r}#1558 AS message#1565, type{f}#1568]]                  !   \_Join[LEFT,[message{r}#1565],[message{r}#1565],[message{f}#1569]]
  | \_Limit[1000[INTEGER],true]                                                   !     |_Limit[1000[INTEGER],true]
  |   \_Join[LEFT,[message{r}#1558],[message{r}#1558],[message{f}#1567]]          !     | \_Join[LEFT,[message{r}#1558],[message{r}#1558],[message{f}#1567]]
  |     |_Limit[1000[INTEGER],false]                                              !     |   |_Limit[1000[INTEGER],false]
  |     | \_Row[[a[KEYWORD] AS message#1558]]                                     !     |   | \_Row[[a[KEYWORD] AS message#1558]]
  |     \_EsRelation[message_types_lookup][LOOKUP][message{f}#1567, type{f}#1568] !     |   \_EsRelation[message_types_lookup][LOOKUP][message{f}#1567, type{f}#1568]
  \_EsRelation[message_types_lookup][LOOKUP][message{f}#1569, type{f}#1570]       !     \_EsRelation[message_types_lookup][LOOKUP][message{f}#1569, type{f}#1570]

[alex-spies]

Reduced some more:

row message = \"a\" | rename message AS fKlJNGHdRX, fKlJNGHdRX as message | lookup join message_types_lookup on message

[alex-spies]

This is not specific to LOOKUP JOIN. The problem is that in transformDown, we don't perform an update if the result would equal the original node - but equals on expressions doesn't care about NameIds (only semanticEquals does)!

So, whenever we transform a node's expressions and use AttributeMap#resolve (like here in the LU JOIN pushdown) and the resolution ends up having the same name and type, we'll just not perform the resolution.

The scope of this bug is thus much larger, as we perform alias resolution in many places in the code base. It also indicates that Attribute#equals should include the NameId (thus being stronger than semanticEquals), but this isn't a small change.

[alex-spies]

Maybe a quicker solution would be to change the transform methods from checking for equality using equals to just using ==; this means we'd discard irrelevant changes less frequently (causing more work in optimizer passes, and potentially more optimizer passes). This is also not a small fix, but potentially smaller.

[alex-spies]

This is a high prio bug. The end user impact is limited and it's easy to work around that, but it prevents us from unmuting the generative tests because we'll be running into optimized incorrectly due to missing references repeatedly until this is fixed.