[Feature Request]: Builtin Function coalesce()

[dengn]

Is there an existing issue for the same feature request?

• [X] I have checked the existing issues.

Is your feature request related to a problem?

Showcase SQL requires support of this function.

Clickhouse SQL: 

select round(coalesce(sum (if(district_fid = 111,tpi,0)),0),2) as allCityTpi,
	            round(coalesce(sum (if(district_fid = 1,tpi,0)),0),2) as targetDistrictTpi,
	            max(case when district_fid = 111 then fname end) as fstFname,
	            max(case when district_fid =1 then fname end) as secFname from (
	            select t.fdate,t.period ,district_fid,b.fname,tpi from tpi_index.t_tpi_district_tpi t
	            left join tpi_index.t_base_district b on t.district_fid = b.fid
	            where t.district_fid in (111,1)
	            and t.fdate = 20211214
            	and t.period = 128
        	)

Describe the feature you'd like

Follow MySQL 8.0 syntax.

Checks from left to right whether NULL arguments were passed and returns the first non-NULL argument.

Arguments:

• Any number of parameters of a non-compound type. All parameters must be compatible by data type.

Returned values:

• The first non-NULL argument.
• NULL, if all arguments are NULL.

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL

Describe implementation you've considered

No response

Documentation, Adoption, Use Case, Migration Strategy

No response

Additional information

No response

[dengn]

Same as https://github.com/matrixorigin/matrixone/issues/2352. Do we have a limit for the number of arguments? If not, do we consider to add one?

[dengn]

Please refer to the test cases after implementing this function. https://github.com/dengn/mo-tester/blob/main/cases/function/func_coalesce.test

[JinHai-CN]

@ouyuanning will try to rewrite coalesce into case-when expression.

[dengn]

Not implemented in 0.5. Move to 0.6.