[Good First Issue] Support All SQL Functions in Other SQL System

[zclllyybb]

Search before asking

• I had searched in the issues and found no similar issues.

Description

We plan to implement all SQL functions in other famous DBs, like MySQL, PG, Trino, CK, Hive, and more. Facilitate the users to migrate to Doris. They're very suitable for newcomers as your first Doris PR. So here's the list. Feel free to comment to pick anyone! If one is picked, I will tick it.

Part I. Hive

• sinh(from Trino), asinh, atanh, acosh (Easy) @ChenMiaoi
• context_ngrams @noixcn
• factorial (Easy) @Czylm
• str_to_map (another map creator) @CookiePieWw
• levenshtein @lichenran0330

See the newest Hive document for these functions' explanation.

Part II. Trino

• regexp_count, regexp_position, regexp_split
• hamming_distance (better with levenshtein together) @lichenran0330
• human_readable_seconds @ChenMiaoi
• timezone_hour, timezone_minute
• GEO FUNCTIONS
  • ST_GeomFromKML
  • ST_Equals, ST_Relate
  • ST_Intersects, ST_Disjoint, ST_Touches @koi2000
  • ST_Crosses, ST_Overlaps
• ARRAY FUNCTIONS
  • dot_product @meox3259
  • trim_array @ayepei
  • ngrams
  • combinations @delayyyyyyy
  • reduce (lambda function) @cypppper
  • sort (add the three arguments with lambda functor version) (Hard)
• bool_or, bool_and (aggregate function)

More tasks is coming...

Solution

All the guidelines to implement an SQL function are in #48201. Please take a carefully look at!

[noixcn]

I'd like to take context_ngrams, if that's okay.

[cypppper]

I want to take mask_hash

[lichenran0330]

I'd like to take levenshtein, if that's okay.

[Czylm]

I'd like to factorial, if that's okay.

[zclllyybb]

@noixcn @cypppper @lichenran0330 @Czylm done!

[meox3259]

I'd like to take inline, if that's okay.

[koi2000]

I'd like to take soundex, if that's okay.

[CookiePieWw]

I'd like to take str_to_map :)

[zclllyybb]

@meox3259 @koi2000 @cypppper sorry guys, but those functions we decide not to impl. maybe you can choose others!

[koi2000]

I'd like to take length, if that's okay.

[zclllyybb]

I'd like to take length, if that's okay.

removed for it's been found implemented. but I added many others. take a look!

[zclllyybb]

I'd like to take str_to_map :)

assigned!

[zclllyybb]

I'd like to take levenshtein, if that's okay.

would you like to implement hamming_distance together?

[cypppper]

Can I take ARRAY FUNCTIONS::reduce (lambda function) in Trino?

[koi2000]

Can I take ST_Intersects, ST_Disjoint, ST_Touches in GEO FUNCTIONS?

[lichenran0330]

I'd like to take levenshtein, if that's okay.

would you like to implement hamming_distance together?

Sure！

[zclllyybb]

@cypppper @koi2000 @lichenran0330 assigned!

@koi2000 try to understand the library S2 we used to implement geometry functions and choose the proper interface to achieve this.

@cypppper lambda functions are a bit different from normal functions, see https://github.com/apache/doris/pull/40605/files for more details.

[meox3259]

I'd like to take dot_product, if that's okay.

[zclllyybb]

I'd like to take dot_product, if that's okay.

assigned

[ChenMiaoi]

i'd like to take the task like sinh etc.

[zclllyybb]

i'd like to take the task like sinh etc.

assigned

[ayepei]

I'd like to take trim_array, if that's okay.

[zclllyybb]

I'd like to take trim_array, if that's okay.

assigned

[ChenMiaoi]

if no one to do human_readable_seconds function, can I do this?

[delayyyyyyy]

I'd like to take combinations, if that's okay.

[zclllyybb]

@ChenMiaoi @delayyyyyyy assigned

[Kosthi]

I'd like to take ST_GeomFromKML, if that's okay.