ES|QL] Client side validation bugs

[stratoula]

Describe the bug:

Here we are gathering bugs related to the client side validation of ES|QL. This means things that we are marking as errors when they are not

• Wrapped boolean expression are not always handled with their final type (@qn895 )

• when changing the type of a column, the new type is not taken under consideration

FROM logs-*
| limit 1
| eval now=now()
| eval day_number=DATE_EXTRACT("DAY_OF_WEEK",now)
| eval closing_time=concat(DATE_FORMAT("yyyy-MM-dd", now),case(day_number >= 1 AND day_number <= 4, "23:15", day_number == 5, "02:15"))| eval closing_time = DATE_PARSE("yyyy-MM-ddHH:mm", closing_time)
| eval diff = case(day_number >= 1 AND day_number <= 4, DATE_DIFF("second", now, closing_time), day_number == 5, date_diff("second", now, closing_time+1day))
// diff is now in seconds.
// we still do not have a formatter in ES|QL
// it should be coming soon :tm:
| eval hour = diff / 3600.0 // this creates us the hour handle
| eval minutes = concat("0.",mv_slice(split(hour::string, "."),1))::double * 60 // this creates the minute handle
| eval seconds = concat("0.",mv_slice(split(minutes::string, "."),1))::double * 60 // this creates the minute handle
| eval `Closing in` = CONCAT(mv_slice(split(hour::string, "."),0), "h",mv_slice(split(minutes::string, "."),0), "m",mv_slice(split(seconds::string, "."),0), "s")
| keep `Closing in`

so here it will complain that closing_time is a keyword while it is not as date_parse returns a date

• We aren't catching nested aggregation functions (e.g. from kibana_sample_data_logs | stats avg(to_long(avg(1))) )
• Naming variables with names that are also types (e.g. ip here) wrongly marks it as error

• FROM kibana_sample_data_logs | stats var = percentile(bytes, bytes) should point out that the second parameter should be constant. However, right now it says Argument of [=] must be a constant, received [percentile(bytes,bytes)]

• We don't properly validate columns in the ENRICH ... WITH list: FROM kibana_sample_data_logs | ENRICH my-policy ON agent.keyword WITH var0 = <field-name> gives "Unknown column [var0], Unknown column [event.kind]"

• Wildcards are lenient and as long as there's another index, the query will be considered valid even if a certain wildcard expression turns up 0 indices, this means that FROM index1, indexbla* will return results if index1 exists and indexbla* doesn't return anything. Our client side validation errors out, we should follow ES logic here [ES|QL] match ES logic for validating source existence #191556

• Variables created with inline casting don't get the correct type. row expires = "2024-08-14T01:00:00.000Z" | eval expires_at = expires::datetime | eval days_till = DATE_DIFF("day", now(), expires_at) yields Argument of [date_diff] must be [date], found value [expires_at] type [inlineCast]. [ES|QL] Improve variable and field name handling #195149

• Trailing decimals are dropped from validation messages. E.g. BUCKET(@timestamp, 50., ?t_start, ?t_end) gives this error: Argument of [bucket] must be [integer], found value [50] type [decimal]. It should show 50. instead of 50. [ES|QL] Fix trailing decimals dropped from client side validation messages #196570

• When casting an ip (might happen to other field types) we are marking this as invalid [ES|QL] Fixes inline casting wrong validation #196489

FROM logstash-*
| WHERE `clientip`::string=="214.164.107.243"

• FROM kibana_sample_data_logs | EVAL TRIM(agent.keyword) | EVAL TRIM(TRIM(agent.keyword)) creates an error Argument of [trim] must be [keyword], found value [TRIM(agent.keyword)] type [double]. This is because we are blindly assuming that any bare function variable is of type double. Similarly, we are making other mistakes in detecting the types of variables such as blindly choosing the return type of the first signature of the function in an assignment.

• We don't support dotted field names with separate parts escaped [ES|QL] Improve variable and field name handling #195149

FROM kibana_sample_data_logs | KEEP `geo`.`dest`

[elasticmachine]

Pinging @elastic/kibana-esql (Team:ESQL)

[stratoula]

A regression here when casting

[drewdaemon]

FROM kibana_sample_data_logs | EVAL TRIM(agent.keyword) | EVAL TRIM(TRIM(agent.keyword)) creates an error Argument of [trim] must be [keyword], found value [TRIM(agent.keyword)] type [double]. This is because we are blindly assuming that any bare function variable is of type double. Similarly, we are making other mistakes in detecting the types of variables such as blindly choosing the return type of the first signature of the function in an assignment.