Bug #114118 Field function return wrongs result if casting fails.

• Submitted: 26 Feb 2024 11:42
• Modified: 26 Feb 2024 12:11
• Reporter: Rahul Malik
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.36
• OS: Any
• CPU Architecture: Any

[26 Feb 2024 11:42] Rahul Malik

Description:
mysql>  select field(0,"1",2,3,0);
+--------------------+
| field(0,"1",2,3,0) |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0,00 sec)

mysql>  select field(0,"x",2,3,0);
+--------------------+
| field(0,"x",2,3,0) |
+--------------------+
|                  1 |
+--------------------+
1 row in set, 1 warning (0,00 sec)

mysql>  select field(0,"1x",2,3,0);
+---------------------+
| field(0,"1x",2,3,0) |
+---------------------+
|                   4 |
+---------------------+
1 row in set, 1 warning (0,00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1x' |
+---------+------+----------------------------------------+
1 row in set (0,00 sec)

mysql>

How to repeat:
select field with a number where casting fails. 

Suggested fix:
IF it fails to cast a string to int. It should exclude that. rather converting it to 0 which results in wrong result.

[26 Feb 2024 12:11] MySQL Verification Team

Hi Mr. Malik,

Thank you for your bug report.

However, this is not a bug ....... 

According to our Reference Manual:

"
If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.
"

Hence, we are dealing here with floating point values. Transforming strings and integers into a floating point types, with values that you provided, will definitely lead to the errors. Hence, either try to use all integers or all strings.

Mixing the types in expressions or functions is prohibited by SQL standard. Hence, MySQL does what it can ......

Not a bug.

[26 Feb 2024 12:16] MySQL Verification Team

Hi,

If you use that function as specified in our Reference Manual, it works just fine:

mysql>  select field(0,1,2,3,0);
+------------------+
| field(0,1,2,3,0) |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

mysql>  select field("0","x","2","3","0");
+----------------------------+
| field("0","x","2","3","0") |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (0.00 sec)

mysql>  select field("0","1x","2","3","0");
+-----------------------------+
| field("0","1x","2","3","0") |
+-----------------------------+
|                           4 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

Not a bug.