Support user defined functions

[lianghanzhen]

Summary

Currently we need to write different implementation for invertible functions, for example, isnull & isnotnull. Can we create kind of function alias to avoid that? For isnotnull, we can have isnotnull = not(isnull($1)), when we parse the expression isnotnull, it can be rewrited as not(isnull($1)).

[sundy-li]

Yes, it is an easy task.

We can register isnotnullfunction in function factory with wrapper not ( isnull (args) )

[sundy-li]

Let's make it more common way.

    pub fn register_reverse(&mut self, name: &str, desc: FunctionDescription) {
        let case_insensitive_desc = &mut self.case_insensitive_desc;

        let reverse_desc = create_reverse_desc(desc);
        case_insensitive_desc.insert(name.to_lowercase(), reverse_desc);
    }

[lianghanzhen]

We still need to implement the reverse function in order to register in the factory, no?

I was thinking to add something like the following that you don't have to implement the function:

pub fn register_reverse(&mut self, name: &str, substitution: &str)

Also we can provide a CREATE FUNCTION statement to allow users to create their own functions:

CREATE FUNCTION isnotempty AS not(isempty($1))

[sundy-li]

Yes, CREATE Function can be a great feature in the future. But this is custom UDF function belongs to the user.

Currently we are implementing built-in functions which can be used by all users.

[lianghanzhen]

Some built-in functions that return boolean seems redundant implementations to me, which can be simply replaced with an alias.

[lianghanzhen]

I took a deeper look at the code. I agree with you that it's not a good idea to use alias for built-in functions, because built-in functions are very useful to optimise queries, for example, not ( isnull (args) ) can be simplified to isnotnull(args).

On the other hand, it would be a great feature that we allow users to create their own functions based on the built-in ones.

Do you know how to create a database that can only be accessed by specified users?

[sundy-li]

Do you know how to create a database that can only be accessed by specified users?

I think you mean create a function that can only be accessed by specified users, now we do not support create function statement.

[lianghanzhen]

Yes, I plan to add it. We need a table to store all the UDFs which might contain 2 columns: name and substitution.

The statement can be:

CREATE FUNCTION isnotnull = not(isnull('$0'))
or
CREATE FUNCTION isnotnull AS not(isnull('$0'))

[sundy-li]

But CREATE Functione is not just for alias function.
Maybe clickhouse create function using lambda expression is better.

Refer: https://clickhouse.com/docs/zh/sql-reference/statements/create/function/
CREATE FUNCTION linear_equation AS (x, k, b) -> k*x + b;

[sundy-li]

We need support lambda expression at first...

[lianghanzhen]

Thanks for pointing out, I haven't checked the ClickHouse one. Correct me if I'm wrong, the only difference I see between lambda & my proposal is how we specify the params, explicit or implicit. I think the implicit one might be much more friendly for the parser.

[sundy-li]

You can take a try, it's valuable.

[lianghanzhen]

Then back to the question, is there any table/database that can only be accessed by specified users?

[sundy-li]

No, there is not.

[lianghanzhen]

Do you think we can put it in system.functions table? Or is it better to create a new one for UDFs?

[sundy-li]

It's better to store functions as stage.

create function xxx;
show create function xxx;
desc function xxx;

[lianghanzhen]

Well that's a new concept for me. Can you share any documents about it?

[sundy-li]

#3253
#3361

Snowflake:
Console for free: app.snowflake.com

https://docs.snowflake.com/en/sql-reference/sql/create-stage.html
https://docs.snowflake.com/en/sql-reference/sql/create-function.html