Better Trino compatibility request

[didip]

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()
• [x] TO_DATE()
• [ ] histogram()
• [ ] array_agg()
• [ ] regexp_split()
• [x] 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: https://github.com/trinodb/trino/issues/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!