Bug #104912 "Typo spotted" in description of function if.

• Submitted: 11 Sep 2021 3:35
• Modified: 15 Sep 2021 7:16
• Reporter: Jeff Yee
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: all
• OS: Any
• CPU Architecture: Any

[11 Sep 2021 3:35] Jeff Yee

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

If expr1 is TRUE (expr1 <> 0 and expr1 <=> NULL), IF() returns expr2. Otherwise, it returns expr3.

it should be:
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

How to repeat:
see description

Suggested fix:
fix typo

[11 Sep 2021 10:15] MySQL Verification Team

Hello Jeff Yee,

Thank you for the report.

regards,
Umesh

[13 Sep 2021 13:53] Jon Stephens

Hi Jeff,

<=> (NULL-safe equals) is correct; (any value) <> NULL returns NULL. Please see https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to and https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html.

Thanks for using MySQL!

jon.

[13 Sep 2021 14:22] Jon Stephens

After taking a second look, the description is not correct, after all.

However, rather than <=>, the correct operator should be the <=> operator's *negation*, which is IS NOT NULL. Viz.:

mysql> SELECT IF(1,2,3) AS 'One', IF(0,2,3) AS 'Zero', IF(NULL,2,3) AS 'Null';
+-----+------+------+
| One | Zero | Null |
+-----+------+------+
|   2 |    3 |    3 |
+-----+------+------+
1 row in set (0.00 sec)

Updated the description of IF() to reflect the above, in mysqldoc rev 70795.

Thanks for catching this!

jon.

[15 Sep 2021 7:16] Jeff Yee

Thanks for your fast response.
I think we should use 'expr1 <> NULL' to replase 'expr1 IS NOT NULL'.

#1 In Janpanese version,it is:
expr1 が TRUE ( expr1 <> 0 および expr1 <> NULL) の場合、IF() は expr2 を返します。 それ以外の場合は、expr3 を返します。

#2
mysql> select 1 is not null,0 is not null,null is not null,'' is not null;
+---------------+---------------+------------------+----------------+
| 1 is not null | 0 is not null | null is not null | '' is not null |
+---------------+---------------+------------------+----------------+
|             1 |             1 |                0 |              1 |
+---------------+---------------+------------------+----------------+
1 row in set (0.00 sec)

mysql> select if('',2,3);
+------------+
| if('',2,3) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)