[BUG] Cast_to_boolean behaviour is different in OpenSearch SQL and Spark SQL

[LantaoJin]

What is the bug?
In OpenSearch SQL/PPL V2 engine:
POST /_plugins/_ppl
{
"query" : """
source = opensearch_dashboards_sample_data_flights
| eval a = cast(1 as boolean) -- true
| eval b = cast(2 as boolean) -- true
| eval c = cast(0 as boolean) -- false
| eval d = cast('1' as boolean) -- false
| eval e = cast('2' as boolean) -- false
| eval f = cast('0' as boolean) -- false
| eval g = cast('aa' as boolean) -- false
| fields a,b,c,d,e,f,g | head 1
"""
}
But in Spark and Postgres

select cast(1 as boolean); -- true
select cast(2 as boolean); -- true
select cast(0 as boolean); -- false
select cast('1' as boolean); -- true
select cast('2' as boolean); -- null
select cast('0' as boolean); -- false
select cast('aa' as boolean); -- null;

In V3, what behaviour should we align with? IMO, return null is more reasonable.

[LantaoJin]

What's the expected behaviour for OpenSearch SQL/PPL v3? @penghuo , it might be a breaking change if we not decide to align with V2 behaviour.

[penghuo]

In V3, what behaviour should we align with? IMO, return null is more reasonable.

agree.