ES|QL: Potential cycle detected on JOIN planning

[luigidellaquila]

./gradlew :x-pack:plugin:esql:qa:testFixtures:loadCsvSpecData --args="http://elastic-admin:elastic-password@localhost:9200"

from firewall_logs*,languages_lookup 
| where true
| rename language_name as message 
| lookup join message_types_lookup on message 
| sort language_code ASC NULLS LAST, source.IP, destination.IP DESC NULLS LAST 
| where  NOT language_code > 50 OR  NOT false AND false AND  NOT language_code == 50 
| dissect type "%{UbYnrgOqj}" 
| dissect message "%{CFDuYXDQ}" 
| keep source.*, `CFDuYXDQ`, `message`, `destination.IP`, `@timestamp` 
| enrich languages_policy on CFDuYXDQ
| rename message as message 
| lookup join message_types_lookup on message 
| stats  `destination.IP` = count_distinct(@timestamp), `@timestamp` = count_distinct(@timestamp), CFDuYXDQ = count(to_lower(language_name)), sUSfCRAxYb = count_distinct(language_name) by destination.IP

[2025-11-20T09:27:03,629][WARN ][o.e.x.e.a.EsqlResponseListener] [runTask-0] ESQL request failed with status [INTERNAL_SERVER_ERROR]: org.elasticsearch.xpack.esql.core.QlIllegalArgumentException: Potential cycle detected
        at org.elasticsearch.xpack.esql.core.expression.AttributeMap.resolve(AttributeMap.java:299)
        at org.elasticsearch.xpack.esql.optimizer.rules.logical.ReplaceAggregateAggExpressionWithEval.lambda$getCannonical$2(ReplaceAggregateAggExpressionWithEval.java:171)
        at org.elasticsearch.xpack.esql.core.tree.Node.transformUp(Node.java:233)
        at org.elasticsearch.xpack.esql.core.tree.Node.lambda$transformUp$9(Node.java:231)
        at org.elasticsearch.xpack.esql.core.tree.Node.transformChildren(Node.java:256)
        at org.elasticsearch.xpack.esql.core.tree.Node.transformUp(Node.java:231)
        at org.elasticsearch.xpack.esql.optimizer.rules.logical.ReplaceAggregateAggExpres

The problem was introduced by #137511, reverting that PR the query doesn't fail (but does it return correct results? I didn't check)

See also #137909 (comment)

[elasticsearchmachine]

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

[luigidellaquila]

cc @ncordon you recently touched this code, maybe you have a clue

[luigidellaquila]

Smaller reproducer:

from firewall_logs*,languages_lookup 
| rename language_name as message 
| lookup join message_types_lookup on message 
| dissect message "%{a}"
| enrich languages_policy on a 
| stats a = count(to_lower(language_name)), sUSfCRAxYb = count_distinct(language_name) by destination.IP