ES|QL: JOIN left field is incompatible with right field of type

[luigidellaquila]

On CSV dataset

from sample_data_t*,client_cidr,languag*
| rename language.name.keyword as message 
| lookup join message_types_lookup on message 
| drop language_code, language.code, @timestamp, country.keyword, `client_ip`, event_duration, *r, language_name, `env`, *v, `client_cidr`, `@timestamp` 
| rename message as message 
| lookup join message_types_lookup on message 
| eval  yjFBNROMv = 4066967889218962785, `message` = true, language.name = "a", language.id = null, `language.name` = 1, type = 1, yHfbOTMUV = "a", `type` = null 
| rename country AS language.name  
| enrich languages_policy on yHfbOTMUV
| rename language_name as message 
| lookup join message_types_lookup on message
| rename type as message 
| lookup join message_types_lookup on message 
| keep `language.name`, lang*, language.name, yHfbOTMUV, `language.id`

The query works fine until you add last KEEP, then it starts failing

{
    "error": {
        "root_cause": [
            {
                "type": "verification_exception",
                "reason": "Found 1 problem\nline 1:733: JOIN left field [message] of type [NULL] is incompatible with right field [message] of type [KEYWORD]"
            }
        ],
        "type": "verification_exception",
        "reason": "Found 1 problem\nline 1:733: JOIN left field [message] of type [NULL] is incompatible with right field [message] of type [KEYWORD]"
    },
    "status": 400
}

The query was generated by GenerativeIT tests, it can probably be simplified a lot

[elasticsearchmachine]

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

[alex-spies]

Hmm, without the keep this should fail just the same. Could be a missing validation.

[luigidellaquila]

Got a similar error, but for ENRICH:

from hos*,languages,message_types | stats  `host_group` = max(language_code), blwldNmdNT = count_distinct(ip1), `type` = max(language_code) by ip0| rename type as language_code | lookup join languages_lookup on language_code | rename language_code AS host_group, `blwldNmdNT` AS XestDuQGgH, host_group AS GrGohjSgZDKC | eval  `XestDuQGgH` = 8114584234002416914, LkJAzHvh = -584594685, `ip0` = "RybGIVMRgk", eLJEZHIT = 922367746, XestDuQGgH = false | where  NOT true AND  NOT false AND true | where true OR  NOT true | eval  eLJEZHIT = true, eLJEZHIT = false, `ip0` = null, LoHDpeHrPhQ = 2106562490, `XestDuQGgH` = null, udcDrTDNWEn = true, btMNxWSTgt = true, KrKFGkPeVf = 5964325812842123566, language_name = null | where  NOT LoHDpeHrPhQ >= 50 OR LkJAzHvh != 50 OR  NOT LkJAzHvh >= 50 OR  NOT false| rename GrGohjSgZDKC as language_code | lookup join languages_lookup on language_code | enrich languages_policy on language_name | enrich languages_policy on language_name | enrich languages_policy on language_name | sort KrKFGkPeVf NULLS LAST, LkJAzHvh NULLS LAST, language_code ASC NULLS FIRST, language_name DESC NULLS FIRST | drop `KrKFGkPeVf` | keep language_code

Unsupported type [null] for enrich matching field [language_name]

most likely the problem is the same

[luigidellaquila]

Hmm, without the keep this should fail just the same. Could be a missing validation.

@alex-spies I'm checking this now, I got confused by that type=null but actually the query should succeed:

| eval ... `type` = null  
| rename country AS language.name   
...
| lookup join message_types_lookup on message      <-- this overwrites "type" with a keyword value
| rename type as message  
| lookup join message_types_lookup on message

[luigidellaquila]

Minimal reproducer:

from languag* 
| eval type = null  
| rename language_name as message 
| lookup join message_types_lookup on message 
| rename type as message 
| lookup join message_types_lookup on message 
| keep `language.name`, lang*, language.name, `language.id`

[alex-spies]

Oh!

| lookup join message_types_lookup on message      <-- this overwrites "type" with a keyword value

I was not aware of this. Then yes, the query should succeed and it looks like we may have wrongly resolved the type attribute for some reason.

Thanks for digging!