ES|QL: columns not projected away despite KEEP

[luigidellaquila]

row foo = 10 
| stats field_1 = max(foo), this = count(*), field_2 = max(foo) by foo  
| rename this AS field_2 
| keep field_1, field_2  
| eval field_2 = 1, field_1 = 1

      foo      |    field_1    |    field_2    
---------------+---------------+---------------
10             |1              |1              

foo shouldn't be there.

Interestingly, if you try to use the wrong column, the query will fail:

row foo = 10 
| stats field_1 = max(foo), this = count(*), field_2 = max(foo) by foo  
| rename this AS field_2 
| keep field_1, field_2  
| eval field_2 = 1, field_1 = 1, bar = foo

{
    "error": {
        "root_cause": [
            {
                "type": "verification_exception",
                "reason": "Found 1 problem\nline 1:176: Unknown column [foo]"
            }
        ],
        "type": "verification_exception",
        "reason": "Found 1 problem\nline 1:176: Unknown column [foo]"
    },
    "status": 400
}

[elasticsearchmachine]

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

[kanoshiou]

Here is a simplified query to reproduce this issue.

row foo = 10 
| stats this = max(foo) by foo 
| keep  this 
| eval this = 2

However, I came across another query that doesn't seem to produce the correct result. I'm unsure whether it was triggered by the same cause.

row foo = 10 
| stats this = max(foo) 
| eval this = 2

               |     this      
---------------+---------------
null           |2                    

[kanoshiou]

After conducting some research, I have concluded that the queries mentioned above are unrelated and I believe the second query deserves a separate issue #126392.

[kanoshiou]

For this query

row foo = 10 
| stats this = max(foo) by foo 
| keep  this 
| eval this = 2

stats this = max(foo) by foo | keep this are initially combined to Aggregate[[foo{r}#12],[MAX(foo{r}#12,true[BOOLEAN]) AS this]], then column this is pruned due to eval this = 2, resulting in the LogicalPlan transforming into Aggregate[[foo{r}#12],[foo{r}#12]].

After the optimizer rules CombineProjections and PruneColumns are applied, column this is removed, while column foo is retained, which serves as the root cause of the issue.