bug: sugger function cannot found when args incorrect

[sundy-li]

Search before asking

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

Version

main branch

What's Wrong?

I excute sql SELECT IFNULL(NULL); databend prompt me ifnull does not exist

root@localhost:8000/default> SELECT IFNULL(NULL);
error: APIError: ResponseError with 1008: error: 
  --> SQL:1:8
  |
1 | SELECT IFNULL(NULL)
  |        ^^^^^^^^^^^^ function `ifnull` does not exist

How to Reproduce?

No response

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

[dosubot[bot]]

The IFNULL function does not exist in the main branch of Databend because it is rewritten to the COALESCE function during the query planning phase. This transformation is handled internally, so when you execute SELECT IFNULL(NULL);, it should be interpreted as SELECT COALESCE(NULL);. The error you're encountering suggests that this rewriting might not be functioning correctly in your current setup.

Here's the relevant code snippet that performs this rewrite:

("ifnull", &[arg_x, arg_y]) => {
    // Rewrite ifnull(x, y) to coalesce(x, y)
    Some(self.resolve_function(span, "coalesce", vec![], &[arg_x, arg_y]))
}

This ensures that IFNULL is mapped to COALESCE, which is a supported function ^[1].

To continue talking to Dosu, mention @dosu.

[sundy-li]

@dosu

That's right, how to fix it? Can you give me the right codes?

[dosubot[bot]]

To fix the issue with the IFNULL function not being found, you need to ensure that the function is correctly implemented and available in your SQL environment. Based on the provided test cases, the IFNULL function should work as expected in the databend environment.

Here is the correct code for using the IFNULL function:

-- Example usage of IFNULL function
SELECT IFNULL(1, 1); -- Expected output: 1
SELECT IFNULL(2, 1); -- Expected output: 2
SELECT IFNULL(1, 2); -- Expected output: 1
SELECT IFNULL(1, NULL); -- Expected output: 1
SELECT IFNULL(NULL, 1); -- Expected output: 1
SELECT IFNULL('a', 'a'); -- Expected output: a
SELECT IFNULL('a', 'b'); -- Expected output: a
SELECT IFNULL('b', 'a'); -- Expected output: b
SELECT IFNULL('a', NULL); -- Expected output: a
SELECT IFNULL(NULL, 'a'); -- Expected output: a
SELECT IFNULL(NULL, NULL); -- Expected output: NULL

-- Example with table
CREATE TABLE IF NOT EXISTS t(a INT, b INT) ENGINE=Memory;
INSERT INTO t VALUES(0, 0), (0, 1), (1, 0), (1, 1);
SELECT a, b, IFNULL(a, b) FROM t; -- Expected output: 0 0 0, 0 1 0, 1 0 1, 1 1 1
DROP TABLE t;

CREATE TABLE IF NOT EXISTS t(a INT NULL, b INT NULL) ENGINE=Memory;
INSERT INTO t VALUES (0, NULL), (1, NULL), (NULL, 0), (NULL, 1), (NULL, NULL);
SELECT a, b, IFNULL(a, b) FROM t; -- Expected output: 0 NULL 0, 1 NULL 1, NULL 0 0, NULL 1 1, NULL NULL NULL
DROP TABLE t;

In databend, the IFNULL function is internally rewritten to use the COALESCE function, which provides similar functionality. This ensures compatibility and proper execution within the databend environment. Ensure that your SQL environment supports the IFNULL function and that there are no typos or syntax errors in your queries ^[1][2].

To continue talking to Dosu, mention @dosu.

[sundy-li]

@dosu I gave a fix at: https://github.com/datafuselabs/databend/pull/16339

Maybe you can help me review the pr.

[dosubot[bot]]

The specific changes made in the pull request to address the bug related to the "sugger function" not being found when incorrect arguments are provided include the addition of a check to see if the function name is a built-in function or a "sugar function". If the function is not found, the code now attempts to find and suggest a similar function name using the SimSearch engine.

Here is the relevant code snippet:

Expr::FunctionCall {
    span,
    func:
        ASTFunctionCall {
            distinct,
            name,
            args,
            params,
            window,
            lambda,
        },
} => {
    let func_name = normalize_identifier(name, self.name_resolution_ctx).to_string();
    let func_name = func_name.as_str();
    if (!is_builtin_function(func_name) && !Self::all_sugar_functions().contains(&func_name)) {
        if let Some(udf) = self.resolve_udf(*span, func_name, args)? {
            return Ok(udf);
        } else {
            // Function not found, try to find and suggest similar function name.
            let all_funcs = BUILTIN_FUNCTIONS
                .all_function_names()
                .into_iter()
                .chain(AggregateFunctionFactory::instance().registered_names())
                .chain(GENERAL_WINDOW_FUNCTIONS.iter().cloned().map(str::to_string))
                .chain(GENERAL_LAMBDA_FUNCTIONS.iter().cloned().map(str::to_string))
                .chain(GENERAL_SEARCH_FUNCTIONS.iter().cloned().map(str::to_string))
                .chain(ASYNC_FUNCTIONS.iter().cloned().map(str::to_string))
                .chain(
                    Self::all_sugar_functions()
                        .iter()
                        .cloned()
                        .map(str::to_string),
                );
            let mut engine: SimSearch<String> = SimSearch::new();
            for func_name in all_funcs {
                engine.insert(func_name.clone(), &func_name);
            }

This change ensures that if a function is not found, the system will attempt to suggest a similar function name, improving the user experience by providing helpful suggestions ^[1].

To continue talking to Dosu, mention @dosu.