Some issues with StarRocks being compatible with Trino

[zenoyang]

Enhancement

Test version: StarRocks 3.1.0

1. rand function

The rand function has two problems:

1. select * from xxx where rand() < 0.1; We tested this query and either returned empty or all data. We expected to return about 1/10 of the data. Inconsistent with Trino behavior;
2. rand(n), when there are parameters, SR represents seed, and Trino represents the output range. The semantics of the two are different;

2. regexp_extract function

In StarRocks, the regexp_extract function returns an empty string when there is no matching content. Trino returns NULL. The two behave inconsistently.

I hope to get everyone’s discussion, feedback and help.

[imay]

@zenoyang thanks for your pointing them out. @wangsimo0 please take a look at these issues.

[wangsimo0]

for the regexp_extract, I'm curious is there a difference between an empty string and null in real world scenario? could you pls give us an example? overall I think we can fix this in trino's compatibility framework without changing sr's build-in funciton's behavior.

[wangsimo0]

for random(n), it seems like a unique function of trino I didn't find any familiar function in other dbms. I think we need to support another function like rand_int(m,n). the input m and n are both integer, and output is also integer.