[Good First Issues] StarRocks Function Tasks 2023

[wangsimo0]

Hi Rockstars,

This is a list of proposed function tasks. If you are new to StarRocks or just enthusiastic about building a great query engine, this is the right task list for you.
Also this is an open list, you are welcome to propose more tasks.

For others to get a better understanding of the function, proposed tasks should contain:

• Scenario: please add your scenario as detailed as possible, for example: get the closest timestamped data or the earliest timestamped data in grouped statistics, or to create a candlestick chart of a stock.
• If you have used it in other systems, please share your experience
• Please open an issue that contains the information above and @kateshaowanjou or copy the link in this issue.

Before you submit a pr, please notice that:

• Please @kateshaowanjou to book the issue, and add a comment in the issue you picked, so the issue won't be assigned to others. And
• Always discuss with the community about the design before actually developing, the input/output, the parameters, the function's name... Anything you need to build an elegant function.
• If you meet some trouble during the development, feel free to ask the community.
• If you are busy and not able to accomplish this issue, don't worry, please tell us or we'll reassign the issue to others after 1 month.

StarRocks contains Scalar Functions, Aggregate Functions, Window Functions, and Lambda Functions. The difficulty is Scalar < Aggregate = Window < Lambda.

Date and Time

1. makedate (Level: Easy) --thanks to @zhangruchubaba

   • sr don't support makedate #13051

2. last_day (Level: Easy) --thanks to @vendanner

   • [function] last_day #19548

3. next_day (Level: Easy) --thanks to @mlimwxxnn

   • [function]next_day/previous_day need to be supported. #19553

4. previous_day (Level: Easy) --thanks to @mlimwxxnn

   • [function]next_day/previous_day need to be supported. #19553

5. weekiso (Level: Easy) --thanks to @qmengss

   • week iso related function #20511

6. dayofweekiso (Level: Easy) --thanks to @xuzifu666

   • week iso related function #20511

7. date_diff (Level: Medium) --thanks to @xuzifu666

   • [function] date_diff #23420

8. convert_interval (Level: Easy) @XinzhuangL

   • [function] convert_interval #33893

9. timestampadd, timestampdiff (Level: Easy)

   • Support MILLISECOND unit for TIMESTAMPADD and TIMESTAMPDIFF #24481

Numeric

1. width_bucket (Level: Easy) @TBCCC

   • WIDTH_BUCKET function support #12990

2. sinh, cosh, tanh --- thanks to @tomscut

   • [function] sr already support bunch of trigonometric functions, still need sinh, cosh, tanh #13298

3. bit_shiftleft, bit_shfitright, bit_shiftright_logic --- thanks to @sparklezzz

   • [function] need to support bitwise shift function #13221

Comparison

1. isnotnull (Level: Easy) --thanks to @leoyy0316
   • [function] isnotnull #22702

String

1. split_part : Need to support "-1" index (Level: Easy) --thanks to @leoyy0316

   • Function split_part not support "-1" index  #5992

2. substring_index (Level: Easy) @leiyang0324

   • starrocks don't support substring_index which mysql did #12532

3. format() (Level: Easy)

   • [function] need format to compare two strings #13304

4. lpad : make the third parameter to be an optional parameter, with a default value ' '. (Level: Easy) --thanks to @predator4ann

   • lpad/rpad function support default value #15021

5. rpad : make the third parameter to be an optional parameter, with a default value ' '. (Level: Easy) --thanks to @predator4ann

   • lpad/rpad function support default value #15021

6. regexp_instr (Level: Easy)

   • [function]regexp_instr #19616

7. regexp_extract_all (Level: Easy)

   • [function]regexp_extract_all #19815

8. regexp supports an optional case-sensitive/case-insensitive parameter (Level: Easy) @numbernumberone

   • [Feature] Starrocks regexp function support CASE_INSENSITIVE #14439

9. compress (Level: Easy) @Cai-Yao

   • compress and uncompress function #20507

10. uncompress (Level: Easy) @Cai-Yao

    • compress and uncompress function #20507

11. strcmp() --- thanks to @tomscut

    • [function]strcmp need to be suppported #13246

12. ilike --- thanks to @uncleGen

    • [feature] need ilike #14595

13. translate (Level: Easy)

    • [function]translate #22704

14. oct (Level: Easy)

    • [function]oct and oct_length #22803

15. oct_length (Level: Easy)

    • [function]oct and oct_length #22803

16. strpos (Level: Easy)

    • [function] need strpos #23014

17. url_encode --- thanks to @xuzifu666

    • [feature] url_encode & url_decode #26724

18. url_decode --- thanks to @xuzifu666

    • [feature] url_encode & url_decode #26724

19. url_extract_host --- thanks to @xuzifu666 and @superdiaodiao

    • Need support url_extract_host function in builtfunction #31969

Aggregate

1. count_if (Level: Medium) --- thanks to @silly-carbon

   • support count_if #15063

2. group_concat(distinct) (Level: Medium) @happut

   • support array_agg(distinct) #15064

3. ifs (Level: Medium) @srikker

   • IFS to simplify CASE WHEN #20513

4. medium() (Level: Medium)

   • window function feature request #19632

5. array_agg(distinct) (Level: Medium)

   • support array_agg(distinct) #20519

6. array_unique_agg (Level: Medium)

   • [function] array_unique_agg #22587

7. min_by --- thanks to @ucasfl

   • Is there a function like argMax() argMin() in Clickhouse or min_by() max_by() in presto？ #3288

8. percentile_disc --- thanks to @before-Sunrise

   • The percentile function should be supported #6922

9. boolor_agg

   • Support for BOOLOR_AGG, BOOLXOR_AGG, BOOLAND_AGG aggregate function #22949

10. booland_agg

    • Support for BOOLOR_AGG, BOOLXOR_AGG, BOOLAND_AGG aggregate function #22949

11. boolxor_agg

    • Support for BOOLOR_AGG, BOOLXOR_AGG, BOOLAND_AGG aggregate function #22949

Array

1. array_subscript (Level: Difficult)

   • [function] array_subscript #19660

2. array_repeat (Level: Medium)

   • [function] array_repeat #19658

3. array_flatten (Level: Medium)

   • [function] array_flatten  #19819

Window

1. NTH_VALUE (Level: Medium) --thanks to @vendanner

   • NTH_VALUE windows function support #13021
     The detailed design is in this issue: window function feature request #19632

2. CUME_DIST() (Level: Medium) --thanks to @Moonm3n

   • NTH_VALUE windows function support #13021
     The detailed design is in this issue: window function feature request #19632

3. PERCENT_RANK() (Level: Medium) --thanks to @Moonm3n

   • NTH_VALUE windows function support #13021
     The detailed design is in this issue: window function feature request #19632

4. MEDIUM() (Level: Medium)

   • window function feature request #19632

5. COUNT(DISTINCT) (Level: Medium)

   • window function feature request #19632

Table

1. series_generate (Level: Medium)

   • [table function] series_generate #19729

2. pivot and unpivot (Level: Hard)

   • function: pivot and unpivot #20520

Utility

1. typeof (Level: Easy) @xuzifu666
   • [function] typeof #36245

Datetime (Ultra-easy tasks)

StarRocks supports milliseconds and microseconds for datetime data type. However, there are many datetime-related functions that have not been considered milliseconds or microseconds yet.
Also, it will be greater if you find out more functions that need to be refined.
Here is a checklist from gensrc/script/functions.py according to #17499

• least
• greatest
• year
• month
• quarter
• dayofweek
• to_date
• date
• day
• dayofmonth
• day
• dayofyear
• weekofyear
• week
• hour
• minute
• second
• years_add
• years_sub
• months_add
• months_sub
• add_months
• weeks_add
• weeks_sub
• days_add
• days_sub
• date_add
• date_sub
• adddate
• subdate
• hours_add
• hours_sub
• minutes_add
• minutes_sub
• seconds_add
• seconds_sub
• microseconds_add
• microseconds_sub
• years_diff
• months_diff
• weeks_diff
• days_diff
• hours_diff
• minutes_diff
• seconds_diff
• datediff
• timediff
• now @SilvaXiang
• current_timestamp @SilvaXiang
• localtime @SilvaXiang
• localtimestamp @SilvaXiang
• str_to_date
• date_format
• unix_timestamp
• dayname
• monthname
• convert_tz
• utc_timestamp
• date_trunc
• timestamp @SilvaXiang
• time_slice
• if
• ifnull
• nullif
• coalesce
• array_distinct
• array_sort
• reverse
• array_slice
• array_concat
• arrays_overlap
• array_intersect
• array_slice
• array_sortby

[hzh0425]

Hi, Could you pls assign 'min_by' and 'percentile_disc' to me? thanks~ @wangsimo0

[tomscut]

Hi @wangsimo0 , could you please assign sinh, cosh, tanh, split_part and strcmp() to me? Thank you.

[mchades]

Hi @wangsimo0 ,Could you pls assign makedate and format to me? thx!

[TBCCC]

Hi @wangsimo0 I wanna have a try for WIDTH_BUCKET,Could you pls assign it to me?thx

[selectbook]

Hi @wangsimo0, Could you please assign substring_index to me? thx!

[amorynan]

Hi @wangsimo0 I will try NTH_VALUE , please assign to me , thx!

[vendanner]

Hi @kateshaowanjou, Could you please assign last_day to me? thx!

[zhangruchubaba]

hi @kateshaowanjou Could you please assign makedate to me? thx!

[mlimwxxnn]

Hi @wangsimo0 , Could you please assign next_day and previous_day to me? thx! (both from#19553)

[Tianpingan]

hi @kateshaowanjou Could you please assign isnan to me? thx!

[kateshaowanjou]

Hi @wangsimo0 , Could you please assign next_day and previous_day to me? thx! (both from#19553)

@mlimwxxnn Since they are two different task, I'll assign the next_day to you first, when you finish this one you can take the other one. :)

[TBCCC]

Hi @kateshaowanjou Could pls assign width_bucket to me again？I have commented on that issue before.

[lqkkkkk]

Hi @kateshaowanjou, Could you please assign split_part to me? thx!

[mlimwxxnn]

Hi @kateshaowanjou, Could you please assign previous_day to me?
I have submitted the pr #21024 for next_day .

[predator4ann]

Hi @kateshaowanjou, Could you please assign lpad to me? thx!

[Cai-Yao]

Hi @kateshaowanjou, Could you please assign String: compress and uncompress to me? thx!

[lqkkkkk]

REPEAT IT AGAIN. Hi @kateshaowanjou, Could you please assign split_part to me? thx!

[lqkkkkk]

Hi @kateshaowanjou , can you assign function-array_subscript to me？ I will make it, Thank you.

[kateshaowanjou]

@lqkkkkk There's another person is also interested in split_part. Can you take function-array_subscript instead?

[chenshaojin]

Hi @kateshaowanjou , Could you please assign array_agg(distinct) to me? thx!

[leiyang0324]

@kateshaowanjou Could you please assign substring_index to me? thx!

[kateshaowanjou]

@lqkkkkk Please let me know which one you want to take. Thanks! 😎

[mlimwxxnn]

Hi @kateshaowanjou , Could you please assign count_if to me? thx!

[kateshaowanjou]

@mlimwxxnn OMG, you're so efficient.👍🏻👍🏻👍🏻

[numbernumberone]

Hi @kateshaowanjou , Could you please assign #14439 to me? thx!

[qmengss]

hi @kateshaowanjou ,Please assign #20511 to me,thx!

[kateshaowanjou]

@qmengss There are actually 2 different functions in #20511, weekiso & dayofweekiso. I assigned the weekiso to you.

[Moonm3n]

Hi @kateshaowanjou, could you please assig rpad to me? Thank you!

[kateshaowanjou]

@Moonm3n Sorry currently rpad is taken. Are you willing to take another one?

[Moonm3n]

@Moonm3n Sorry currently rpad is taken. Are you willing to take another one?

Has anyone picked up CUME_DIST yet? If not, I would like to take this issue.

[byrantwithyou]

@kateshaowanjou I would like to pick the PERCENT_RANK issue. Would you assign it to me?

[byrantwithyou]

@kateshaowanjou Could you assign me the array_union_agg(Issue #22587 )? Since the PERCENT_RANK is taken by @Moonm3n (It makes more sense to have one person implemented both PERCENT_RANK and CUME_DIST because they are very similar)

[kateshaowanjou]

@byrantwithyou array_union_agg is assigned to you. Thanks a lot! ❤️

[wuxiaobai24]

@kateshaowanjou I would like to pick the oct and oct_length (issue #22803), Would you assign it to me?

[TBCCC]

Hi @kateshaowanjou Could pls assign array_subscript to me?thx, I've already opened a pr for my last task #22787 .

[kateshaowanjou]

Hi @kateshaowanjou, Could you please assign String: compress and uncompress to me? thx!
@Cai-Yao
Are you working on uncompress too? I tried to reach you, but couldn't find you. Are you in our wechat/slack group?

[vendanner]

Hi @kateshaowanjou Could pls assign NTH_VALUE to me? thx, I've already opened a pr for my last task #23348 .

[hongism]

Hi @kateshaowanjou Could pls assign pivot and unpivot to me? thanks!

[liangjiawei1110]

Hi @kateshaowanjou Could pls assign array_repeat to me?

[kateshaowanjou]

@liangjiawei1110 Can you make a comment under this issue so I can assign it to you? #19658

[SilvaXiang]

Hi @kateshaowanjou Can you assign array_avg task for me?

[kateshaowanjou]

@SilvaXiang Can you make a comment under #17499 so I can assign array_avg to you?

[XinzhuangL]

I am new to the open community and have some knowledge of Java and C++. Can I select a task?

[XinzhuangL]

Is there any easy task you can assign to me? I can't find an unassigned task @kateshaowanjou

[wangsimo0]

Is there any easy task you can assign to me? I can't find an unassigned task @kateshaowanjou

@XinzhuangL hi thanks a lot for your interest! how about this one #33893 ? :)

[XinzhuangL]

@wangsimo0 Thank you and I'd like to try it.

[LiRuihaoA]

hi @wangsimo0, are there any java tasks can assign to me?

[Mr-LiuXu]

hi @kateshaowanjou Could you please assign date_trunc to me? thx!