Bug #107287 CASE operator does not follow standard equality

• Submitted: 13 May 2022 10:16
• Modified: 13 May 2022 10:21
• Reporter: Vicent Marti
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.28, 8.0.29, 5.7.38
• OS: Any
• CPU Architecture: Any

[13 May 2022 10:16] Vicent Marti

Description:
From the official MySQL documentation for the CASE operator (https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html#operator_case):

> The first CASE syntax returns the result for the first value=compare_value comparison that is true.

This does not happen in practice. In an expression `SELECT CASE a WHEN b THEN bb ELSE cc`, the `a = b` comparison does not follow the comparison semantics defined in Type Conversion in Expression Evaluation (https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html).

An example of this is that negative signed integrals are coerced into their unsigned forms for comparison, so that `SELECT case -1 when 18446744073709551615 then 1 else 0 end` returns 1, while the (supposedly equivalent) expression `SELECT -1 = 18446744073709551615` returns 0.

How to repeat:
mysql> SELECT case -1 when 18446744073709551615 then 1 else 0 end;

+-----------------------------------------------------+
| case -1 when 18446744073709551615 then 1 else 0 end |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT -1 = 18446744073709551615;

+---------------------------+
| -1 = 18446744073709551615 |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.01 sec)

[13 May 2022 10:21] MySQL Verification Team

Hello Vicent Marti,

Thank you for the report and feedback.

regards,
Umesh

[15 May 2022 9:08] huahua xu

Hi, Vicent Marti

It is indeed an issue.

For the query statement `SELECT case -1 when 18446744073709551615 then 1 else 0 end`, the function Item_func_case is very unwise to build the cmp_items by choosing cmp_item_int.

But, for the query statement `SELECT -1 = 18446744073709551615`, the function Item_func_eq sets the compare function to Arg_comparator::compare_int_signed_unsigned in detail.