Better Trino compatibility request

[didip]

• [Feature] Support regexp_split function #47201

Enhancements

First of all, Trino compatibility docs should tell users that to have the same result as Trino, this has to be set: SET global sql_mode='SORT_NULLS_LAST';

The following functionalities are not currently implemented in Trino dialect:

• from_iso8601_timestamp()
• TO_DATE()
• histogram()
• array_agg()
• regexp_split()
• replace()
• prepare_dedup()
• EXECUTE IMMEDIATE
• UUID data type is also missing. e.g. cast(our_table_5.column.value as UUID) = UUID'abc123'
• HyperLogLog data type is missing. e.g. cast(iceberg.our_table_4.total_unique as HyperLogLog)
• Can SR run queries like this? EXECUTE statement1 USING 'abc123-uuid-pointer'

Also a few SQL issues:

• This syntax is broken: SELECT (approx_percentile(cnt, 0.49) + approx_percentile(cnt, 0.51)) / DOUBLE '2.0'. We can easily just say 2.0, but our customers will expect this to work.
• WHERE date between '2023-10-01' and '2023-10-01' breaks the query planner. Preferably the planner should translate that to WHERE date = '2023-10-01'.
• WHERE 1=1 breaks the query planner. Preferably the planner should consider this as no-op.

[winterfreshx]

On the WHERE 1=1, the customer uses it to simply comment / uncomment where clauses before like this WHERE 1=1 AND date=X AND ...

[hackeryang]

The Trino community is trying to support StarRocks connector: trinodb/trino#17329
Thanks to @chenjian2664

[hhhonzik]

For what it's worth, i've implemented compatible HLL functions via Java UDF here

[yangzho12138]

I will try to work on this issue!