Add IF(), IFNULL(), NULLIF()

[theodesp]

Those are useful:

https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#function_ifnull
https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#function_if
https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#function_nullif

[theodesp]

Will work on this now

[theodesp]

There might be an issue of determining the Type() of NullIfand If functions.
The case is when you know the result you can return it but in that case, we don't know the result type before the function evaluates.

For example in NullIf:
We need to know if Left and Right are equal so we return sql.Null otherwise we return Left.Type()

In If:
We need to know if Left evaluates to true so we return Left.Type() otherwise we return Right.Type()

What is the recommended approach?

[ajnavarro]

You can check how the type is chosen on MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#function_if

But basically, using IF(expr1,expr2,expr3) as an example:

• If expr2 or expr3 produce a string, the result is a string.
• If expr2 and expr3 are both strings, the result is case-sensitive if either string is case sensitive.
• If expr2 or expr3 produce a floating-point value, the result is a floating-point value.
• If expr2 or expr3 produce an integer, the result is an integer.

[theodesp]

Thank you. The question remains for the NullIf is that how we can check if Left and Right are equal as I can see that the Type() gets called before the function evaluates. I mean that I will have to know the result of the Eval beforehand or return a type that will resolve to either sql.Null or Left.Type()

For example if I just return Left.Type() then if the following call:

select nullif(2,2);

will return 0 instead of NULL

[ajnavarro]

The type selection will be done as I described before, the only difference is that the result will be null. Null is not a type itself. I will open an issue to remove it from types list.

[ajnavarro]

Done in: #555